G-11 Chapter-3 Note PDF

**Chapter -3** **Database Management Overview** **Introduction to Database Management** Database management is a systematic method for organizing data to facilitate its management and usage. This unit introduces the fundamentals of database modeling and management, covering key concepts such as d...

**Chapter -3** **Database Management Overview** **Introduction to Database Management** Database management is a systematic method for organizing data to facilitate its management and usage. This unit introduces the fundamentals of database modeling and management, covering key concepts such as database types, file-based versus database approaches, relational data models, relational databases, and the use of Microsoft Access for database management. **Data Management Approaches** Data management is crucial in daily operations. Historically, data was managed manually using physical documents. However, as data volumes increased, computer systems became essential for efficient data management. The initial method was file-based data management, which was later succeeded by the database approach to overcome its limitations. **File-Based Data Management** File-based data management involves storing data in separate files without explicit relationships. Each department manages its own files independently, leading to potential issues such as: - **Data Duplication**: Different departments may store the same data, leading to inconsistencies. - **Data Inconsistency**: Changes in one department\'s data may not be communicated to others, resulting in conflicting information. - **Difficulty in Data Retrieval**: Isolated data makes it challenging to retrieve comprehensive information across the organization. ***For example***, if both the human resources and finance departments maintain separate employee records, updates in one department may not reflect in the other, causing discrepancies. **Database Approach to Data Management** The database approach utilizes a **Database Management System (DBMS)** to manage data stored in a centralized repository. A database is a collection of logically related data designed to meet the needs of various user groups and applications within an organization. Databases are vital for maintaining internal records, such as: - Student records in schools - Customer records and sales data in supermarkets - Patient databases in hospitals DBMS software facilitates the addition, modification, deletion, and retrieval of data, acting as an interface between the database and users or applications. The database management process consists of three main components: - The database itself, which stores the actual data. - The DBMS, which manages the database. - Application programs that query the database through the DBMS. **Advantages of the Database Approach** The database approach offers several benefits to organizations, including: - **Data Independence:** A centralized database supports all users, eliminating the need for isolated data management. - **Data Sharing:** Centralized data can be accessed by multiple users, enhancing collaboration. - **Avoiding Data Redundancy:** Centralized storage reduces duplicate data across departments. - **Improved Data Security:** Access to data is restricted to authorized users, ensuring data integrity and confidentiality. **Data Model Overview** - **Data Model**: foundational blueprint guiding data storage within a database. - Specifies entities, attributes, and relationships. - Relevant data focus: e.g., school databases on students/teachers, hospital databases on patients/physicians. - Excludes irrelevant data (e.g., traffic accidents). 3.2.1 Basic Concepts in Data Modeling - **Importance**: Understanding basic concepts is crucial for effective database design. - **Primary Concepts**: - **Entities**: - Definition: Individuals, places, or events for which data is stored. - Examples: - Banks: Data on **customers** and **bank accounts**. - Libraries: Data on **books**, **loans**, and **borrowers**. - Database Implementation: Entities correspond to **tables**. - **Attributes**: - Definition: Properties distinguishing one entity from another. - Examples: - Customer entity attributes: **customer ID**, **name**, **sex**, **address**. - Book entity attributes: **author name**, **book title**, **publication year**. - Significance: Differences in attributes reflect the unique nature of each entity. - **Relationships**: - Definition: Associations between entities. - Example: A customer *has* an account in a bank; the verb 'has' indicates the relationship between customer and bank account entities. **Types of Relationships** - **One-to-One Relationship**: - Definition: An instance of one entity corresponds to a single instance of another entity. - Example: A **country** is associated with one **capital city** and vice versa. - Instance Concept: An instance of an entity is a single occurrence (e.g., "Addis Ababa" as an instance of **City**). - **One-to-Many Relationship**: - Definition: An instance of one entity is connected to multiple instances of another entity. - Example: A **department** (e.g., Computer Science) can associate with many **students**. - **Many-to-Many Relationship**: - Definition: Instances on both sides of the relationship are associated with multiple instances of the other entity. - Example: An **employee** may work on several **projects**, and a **project** may involve multiple **employees**. **Conclusion** - Understanding these concepts and relationships is essential for designing a robust and efficient database. - Accurate representation of data requirements is crucial for organizational effectiveness. ***[3.2.2 Entity Relationship Diagram (ERD)]*** - **Entity Relationship Diagram (ERD)**: A visual representation tool for data models. - **Main Components**: - **Entity**: Represented as a **rectangle**. - **Attribute**: Represented as an **ellipse**. - **Relationship**: Represented as a **line** connecting entities. - **Naming Conventions**: - Names of **entities** and **attributes** are placed inside the **rectangle** and **ellipse**, respectively. - Names of **relationships** are placed directly **on top of the line** connecting entities. - **Example**: - In an ERD showing the association between **student** and **book** entities: - **Student Entity Attributes**: - **student\_id** - **name** - **age** - **address** - **Book Entity Attributes**: - **book\_id** - **book\_title** - **publication\_year** - Relationship: Denoted as **'borrows'**. Activity 3.4 1. **Purpose of ERD**: To visually represent the structure of a database by illustrating the entities, their attributes, and the relationships among them. 2. **ERD Construction**: - **Teacher Entity**: - **teacher\_id** - **name** - **sex** - **specialization** - **Schedule Entity**: - **schedule\_id** - **room** - **period** - **section** 3.2.3 Relational Data Model - **Relational Data Model**: Represents data using **two-dimensional tables** called **relations**. - **Table Structure**: - **Rows**: Also known as **records** or **tuples**, each containing a unique instance of data. - **Columns**: Also known as **attributes** or **fields**, used to describe the properties of the relation. - **Example**: - **Student Relation**: - Attributes include **Student ID**, **Name**, **Sex**, **Grade**, and **Address**. - Sample Records: - ST001: Brook Assefa, M, 12, Kebele 05 - ST002: Chaltu Bayissa, F, 11, Kebele 01 - ST003: Ali Mohammed, M, 10, Kebele 03 - ST004: Tsion Gabissa, F, 10, Kebele 01 - **Conceptual Tools**: The relational data model captures a collection of relations, their attributes, and relationships. - **Example of Relations**: - **Student Relation**: Attributes include **student\_id**, **sex**, **grade**, and **address**. - **Book\_Loan Relation**: Attributes include **book\_id**, **student\_id**, and **loan\_date**. - **Relationships**: - The **Student** and **Book\_Loan** relations are connected via the **student\_id** attribute. - The **Book** relation is related to the **Book\_Loan** relation through the **book\_id** attribute. Summary - ERDs are essential for visualizing database structures, showcasing entities, their attributes, and interconnections. - The relational data model organizes data into tables, facilitating an efficient representation of real-world entities and their attributes, promoting clarity and relational integrity in database management. ### 3.3 Creating Relational Database in Microsoft Access - **Overview of Microsoft Access** - MS Access is used to create and manage databases. - Relations (or entities) are represented as **database tables**. - Attributes are represented as **table fields**. - **Figure 3.14** illustrates the transformation from relations in the relational data model to MS Access database tables. - **Student Entity Structure** - **Attributes**: - **student\_id** - **student\_name** - **Sex** - **Grade** - **Address** - **Student Table Fields**: - **Field Names** and corresponding **Data Types**: - **student\_id**: *Text* - **student\_name**: *Text* - **sex**: *Text* - **grade**: *Number* - **address**: *Text* - **Data Type Importance** - Indicates the type of data to be stored for each field. - Example: - **student\_name** has a *Text* data type. - **grade** has a *Number* data type. ### 3.3.1 Creating a Microsoft Access Database - **Initial Steps**: - Creating a database is the first step when using Microsoft Access. - A **database** is a container for database objects such as: - **Tables** - **Forms** - **Queries** - **Reports** - Different versions of MS Access exist; this guide focuses on **MS Access 2010**. - **Steps to Create a Database**: - Open MS Access Software. - - - Recommended to use descriptive names relevant to the data. - - **Post-Creation Actions**: - After the database is created, a new MS Access window opens for creating database objects. - A **table object** must be created first. - A **table** is defined as a set of fields that store data about a specific entity. ### Practical Activities in MS Access - **Tables for Activities**: - Sample tables provided for practical exercises: #### Student Table Example - **Fields**: - **Student\_ID** \| **Sname** \| **Sex** \| **Age** \| **Grade\_level** - **Sample Data**: - ST0001 \| Brook Assefa \| M \| 17 \| 11 - ST0002 \| Chaltu Bayissa \| F \| 18 \| 12 - ST0003 \| Giday Hagos \| M \| 15 \| 9 - ST0004 \| Nejat Mohammed \| F \| 17 \| 11 - ST0005 \| Hailu Mekonnen \| M \| 16 \| 9 - ST0006 \| Kedir Ali \| M \| 18 \| 12 #### Teacher Table Example - **Fields**: - **Teacher\_ID** \| **tname** \| **sex** \| **age** \| **specialization** - **Sample Data**: - T0001 \| Oljira Kuma \| M \| 42 \| Biology - T0002 \| Hagos Kiros \| F \| 35 \| Chemistry - T0003 \| Rediet Assefa \| F \| 28 \| Mathematics - T0004 \| Ahmed Yusuf \| M \| 27 \| Physics - T0005 \| Delebo Nurye \| M \| 26 \| IT #### Grade Table Example - **Fields**: - **Student ID** \| **Teacher\_ID** \| **subject** \| **grade** - **Sample Data**: - ST0001 \| T0001 \| Biology \| 75 - ST0001 \| T0002 \| Chemistry \| 86 - ST0002 \| T0002 \| Chemistry \| 70 - ST0001 \| T0003 \| Mathematics \| 95 - ST0001 \| T0004 \| Physics \| 65 - ST0003 \| T0002 \| Chemistry \| 82 - ST0002 \| T0003 \| Mathematics \| 78 - ST0001 \| T0005 \| IT \| 90 - ST0002 \| T0001 \| Biology \| 67 ### Summary - **Creating a Relational Database** in Microsoft Access involves understanding the structure of entities and attributes, defining data types for fields, and following specific steps to create a database and its tables. - The practical examples provided help illustrate how to structure data within tables for effective database management. This section outlines key processes and structures essential for creating relational databases using Microsoft Access, focusing on clarity and usability of the database environment. 3.3.2 Creating Table in Microsoft Access - **Table Creation Process:** - Microsoft Access requires defining **table fields** when creating a new table. - **Table fields** are attributes of entities. - One database can contain **multiple tables**. - Each **table name** must be **unique**. - Tables must have at least **two fields**. - Field names must be **unique** within a table. - **Steps to Create a Table in MS Access:** - - - - - Data types may include **text**, **number**, **date/time**, **currency**, etc. - - - **Important Notes on Table Creation:** - Before creating a new table, adding data, or modifying an existing table, ensure the database is open. This is crucial as multiple databases can exist on a single computer. - When defining a field, it is essential to specify the correct **data type** for the field name. This is a form of validation to ensure data correctness. - For example, if the data type is set to **Text**, it will not accept numeric values or any other data type---only text is valid. - To set a field as a **primary key**: - Access the table design view. - Select the desired field. - Click on the **Primary Key** button in the toolbar. - **Steps to Enter Data into a Database Table:** - Ensure the **database** is open. - Click on the **table name** and select **Datasheet View** from the **View** menu (e.g., open the **student table**). - Enter appropriate values for each field in the table. - Input all records as shown in the relevant figure. - **Activity 3.6:** - Create a database named after your **school**. - Within the new database, create the three tables as referenced in the relevant figure. **3.3.3 Creating Query in Microsoft Access** - **Query**: A database object that provides a custom view of data from one or more tables. - Purpose: To search for and compile data based on defined criteria. - **Data Retrieval Methods in MS Access**: - **Graphical Query**: A visual interface for creating queries. - **Structured Query Language (SQL)**: A programming language used for managing and manipulating databases. - **Steps to Create a Query in Microsoft Access**: - - - Options available: **Query Wizard** or **Query Design**. - - - Select one or more tables to include in your query. - Click **OK** to proceed. - - In the Field row, select desired fields (e.g., **sname** and **sex**). - - Select **Datasheet View** from the toolbar to display results. - **Example Result**: If correctly executed, the query will display selected data fields. Activity 3.7 1. **Create Query for Teacher Table**: - Display **teacher name** and **specialization**. - Save as **Teacher\_Specialization**. 2. **Create Query for Student Table**: - Display **student name** and **grade level**. - Save with an appropriate name. **3.3.4 Adding Criteria to Query Design** - **Purpose of Criteria**: To retrieve specific data from a table based on defined conditions. - **Steps to Add Criteria**: 1. **Open Query Design Window**: Start by opening the design window. 2. **Add Table**: Include the relevant table for your query. 3. **Select Fields**: Choose the fields to be displayed in the Query Result Window. 4. **Enter Criteria**: - In the **Criteria row**, input specific selection criteria (e.g., for displaying only Male students, enter **M** under the **Sex** column). - **Important Note on Criteria**: 1. Ensure that the criteria entered match the data format in the table exactly. - Example: If the gender is stored as a single character (e.g., **m**), entering **'Male'** will not yield the correct results. ### 3.3.5 Selecting Data from Multiple Tables - **Relational Database**: Enables retrieval of data from two or more tables. #### Steps to Select Data from Multiple Tables: 1. **Open Query Design Window**: - Initiate the process by accessing the Query Design window. 2. **Add a Table**: - If the **Show Table** window is not visible, click the **Show Table** button on the Toolbar. - A list of available tables will appear in the **Show Table Box**. 3. **Select and Add Table**: - Choose a table from the list. - Click the **Add** button to include the selected table in the Query Design window. 4. **Repeat Table Addition**: - Continue to add more tables by repeating step 3 as necessary. 5. **Establish Relationships**: - Create relationships between tables by dragging the **primary key field** from the Parent table to the corresponding **Child Table**. - This step is critical for linking data accurately across tables. 6. **Select Fields for Query Datasheet View**: - Identify and select specific fields to display in the query results: - Example selections may include: - **Student Name** (fname) from the student table. - **Subject** and **Grade** from the grade table. - **Teacher Name** (tname) from the teacher table. 7. **Save the Query**: - Access the **File menu** and select the **Save** button to save the query. 8. **Enter File Name**: - Provide a name for the new query and click the **OK** button to confirm. 9. **View Query Results**: - Click the **View** button in the Toolbar to display the results of the query. #### Figures: - **Figure 3.21**: Illustrates the Query Design with multiple tables. - **Figure 3.22**: Displays query results derived from multiple tables. ### Activities: 1. **Create Female Students Query**: - Develop a query that lists all female students. - Save the query as **Female\_Students**. 2. **Create Student-Grade Query**: - Formulate a query that extracts data from both the student and grade tables. - Display only: - **Student Name** from the student table. - **Subject** and **Grade** from the grade table. - Save this query as **Student-Grade**. 3. **Create High Scores Query**: - Construct a query to list students who have achieved a score above 85 in all subjects. ### Key Concepts: - **Query Design**: An interface used for creating and modifying database queries. - **Show Table Window**: A dialog that allows users to select tables for queries. - **Primary Key**: A unique identifier for a record in a table, essential for establishing relationships. - **Child Table**: A table that contains foreign keys referencing the primary key of another table (the Parent table). - **Datasheet View**: A format to display query results in a structured layout resembling a spreadsheet. ### Important Terms: - **Relational Database**: A database structured to recognize relationships among stored items of information. - **Query**: A request for data or information from a database table or combination of tables. - **Field**: A specific piece of data; a column in a database table. - **Record**: A complete set of information; a row in a database table. ### Conclusion: Selecting data from multiple tables is a fundamental feature of relational databases, allowing users to effectively query and analyze interconnected datasets. Following the outlined steps ensures accurate data retrieval and relationship management between tables. The practical activities provided reinforce the understanding of query creation and data manipulation within the database context.