Comprehensive Study Notes on Database Management Systems and Database Management Systems

Fundamentals of Databases and Manual Systems

A database is defined as an organized collection of related information, such as the accounting details of a company. The primary purpose of a database is to provide a mechanism for the easy and fast storage and retrieval of information. Traditionally, databases were maintained manually through physical records and filing systems. Examples of manual databases include a phonebook, student registers, patient information at a hospital, library catalogues, and national voter information records. In a manual system, data is often stored in physical locations like a filing cabinet with many drawers, where each drawer might contain different files for specific items like marks, attendance, and personal details.

Characteristics and Limitations of Manual Databases

The manual storage and management of information present several significant disadvantages which necessitated the development of computerized systems. Firstly, data is often duplicated across different files or locations, leading to redundancy. Secondly, manual retrieval of records is extremely time-consuming compared to automated systems. Thirdly, manual systems often suffer from inconsistency of data, where different records for the same entity may not match. Finally, manual data is difficult to share among different users or departments, as physical access to the original paper record is typically required.

Introduction to Database Management Systems (DBMS)

Database Management Systems (DBMS), or computerized database systems, were developed to make the storage and retrieval of information faster and easier. In a computerized database, data is stored in a structured and organized manner, allowing for much faster retrieval than manual systems. Popular examples of database programs include Microsoft Access, Corel Projects (also referred to in logs as Coralel Projects and Corel Prg Project), Lotus Approach, Oracle, and Structural Query Language (SQL). These systems offer significant advantages over manual databases, including faster storage and organization, reduced physical space requirements (replacing large filing cabinets), increased security of data, and a reduction in data duplication.

Despite their advantages, DBMS also possess certain disadvantages. They are more time-consuming to design initially than manual databases and require initial training for users. Furthermore, specific hardware and software are required to run these programs, and they can be expensive to purchase and maintain. However, they provide enhanced features like ad hoc situation management using queries, standardized data forms that reduce updating errors, and the ability to present multiple views of data through report features.

Core Database Science: Entities, Attributes, and Tuples

Databases are designed to contain data about things in the real world. An entity represents a type of item about which data is stored. Entities can be physical objects, such as a house or a car; events, such as a house sale or a car service; or concepts, such as a customer transaction or an order. Every entity has specific attributes, which are identifying properties. For example, a "House" entity could have attributes such as "Address" and "Year Built."

In technical database terminology, the terms entity, attribute, and tuple are used to describe these concepts abstractly. In practice, when working with a database program, an entity is represented by a table, an attribute is represented by a field, and a tuple is represented by a record. A record (or tuple) consists of the combined information of all attributes for a single entity, forming a single row in a table. A table is a collection of these records arranged in rows and columns.

Relational Databases and Microsoft Access 2003

A relational database is a system that contains more than one table, where the tables share data by having a link or relationship between them. In the context of relational databases, tables are sometimes referred to as relations, and records are referred to as tuples. Microsoft Access 2003 is a flexible relational database program suitable for both simple and complex tasks. It allows for various components of a database to be linked together by setting relationships among them. Common methods to launch Microsoft Access 2003 include navigating through the Start menu (Start > Microsoft Office > Microsoft Access 2003) or double-clicking the program icon on the desktop.

Data Types and Field Properties in MS Access

When designing a table in Access, it is essential to set a field type or data type for every field to dictate what kind of information can be stored. Common data types include:

• Text: Used for alphanumeric characters (letters or numbers) up to $255$ characters, such as names and addresses.

• Memo: Used for large blocks of text and notes, storing up to $65536$ characters.

• Number: Stores numeric values with or without decimal places, such as age or quantity.

• Currency: Specifically for money values like salary or price.

• Date/Time: For dates and times, such as date of birth or purchase date.

• AutoNumber: Automatically generates unique numbers, often used for ID numbers.

• Yes/No: Used for boolean values, such as "Passed" or "Available."

Additional properties include the Field Description, which provides a text explanation that appears in the status bar when a user selects the field, and Field Size, which determines the maximum amount of information (in characters) a field can store. For instance, a field for "Age" requires less space than one for an "Address."

Detailed Specifications for Number Data Types

For fields set to the Number data type, Access allows for specific field sizes that determine the range and precision of the data stored. The default setting is Long Integer. The options include:

• Byte: Stores integer values from $0$ to $255$ without decimal places.

• Integer: Stores integer values between $-32\,768$ and $32\,767$.

• Long Integer: Stores integer values from approximately $-2 \times 10^9$ to $+2 \times 10^9$ without decimals. Decimal inputs are rounded to the nearest whole number.

• Decimal: Stores numbers from $-9.999 \times 10^{27}$ to $+9.999 \times 10^{27}$.

• Single: Stores numbers from $-3.4 \times 10^{38}$ to $+3.4 \times 10^{38}$ with decimal places.

• Double: Stores numbers from $-1.797 \times 10^{308}$ to $+1.797 \times 10^{308}$ with decimal places.

Establishing Database Relationships

Database relationships show how tables are connected to each other using primary keys and foreign keys. This organization reduces repetition and ensures data consistency. There are three main types of relationships:

1. One-to-One: A single record in Table A is associated with exactly one record in Table B.

2. One-to-Many: A single record in Table A can be related to many records in Table B, but a record in Table B relates to only one in Table A (e.g., one student enrolled in many subjects).

3. Many-to-Many: Multiple records in Table A are related to multiple records in Table B, often requiring a third linking table (e.g., students and clubs).

To create a relationship in Access: Open the Database Tools tab, click Relationships, add the relevant tables, and drag the primary key from one table to the common field (foreign key) in the other. It is often necessary to select "Enforce Referential Integrity" to maintain data accuracy.

Query Types and Functions in Data Retrieval

A query is a database command or request used to retrieve specific data from one or more tables. Queries allow users to focus on relevant information without searching through entire tables manually. Basic retrieval is often done using the SELECT statement, while the WHERE clause filters results based on conditions. For example: SELECT Name FROM students WHERE Age > 12; displays the names of students older than $12$.

Major types of queries include:

• Select Query: Retrieves data and displays it in a structured result set of rows and columns.

• Action Query: Performs operations that change data, such as adding, deleting, or modifying records.

• Parameter Query: Prompts the user to enter specific criteria (e.g., "Enter Grade Level") before running, making the query interactive.

• Make-table Query: Creates a new table based on the results of a query.

• Append Query: Adds records from one table into an existing table.

• Delete Query: Removes records from a table.

• Cross-tab Query: Summarizes data in a row-and-column format to perform calculations like sums or averages for analysis.

• Total Query: A select query that uses aggregate functions, like the Sum function, to group and summarize data (e.g., total sales per product).

Advanced Data Manipulation: Sorting and Filtering

Sorting data involves arranging records in a specific order. Ascending order organizes data from smallest to largest, earliest to latest, or $A$ to $Z$. Descending order organizes data from largest to smallest, latest to earliest, or $Z$ to $A$. Sorting helps users quickly locate information, such as sorting students alphabetically or products by price.

Filtering data involves displaying only those records that meet specific criteria, temporarily hiding the rest. Criteria utilize conditional operators such as equals ($=$), greater than ($>$), or less than ($<$). For instance, a teacher might filter a database to show only students who scored above $70$ percent.

Calculated fields can also be created within queries to perform mathematical operations, such as multiplying quantity by unit price to find a total cost. Text queries allow for the combination of text from separate fields, such as using the ampersand (&) to join a "First Name" and "Last Name" field into a single "Full Name" field.

Data Integration and Output: Mail Merge and Importing

Database Mail Merge is the process of combining a main document (like a letter or certificate) with data from a database to automatically create multiple personalized documents. The main document contains fixed text and placeholders (fields) linked to the database records. This increases efficiency and reduces errors when producing large volumes of documents.

Importing data involves transferring information from a different format, such as a Word table, into Microsoft Access. For successful importing, the Word data should be organized with clear headings and a table format. This process saves time by eliminating the need to retype data and allows information from various sources to be integrated into a single, structured system for better analysis.