Database Normalization and Aggregation Concepts
Understanding the advantages and disadvantages of database normalization, the concept of aggregation in the ER model, and examples of creating ER diagrams using aggregation rules with related entities. Explore the benefits of smaller databases and better performance through normalization, and how aggregation simplifies representing complex relationships in databases.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Lecture 8 Normalization Aggregation Dr. Haider A. Haddad Class 3 1
Advantages of normalization 1. Smaller database: By eliminating duplicate data, you will be able to reduce the overall size of the database. 2. Better performance: a. Narrow tables: Having more fine tables allows your tables to have less columns and allows you to fit more records per data page. b. Fewer indexes per table: mean faster maintenance tasks such as index rebuilds. c. Only join tables that you need. Disadvantages of normalization 1. More tables to join: By spreading out your data into more tables, you increase the need to join tables. 2. Tables contain codes instead of real data: Repeated data is stored as codes rather than meaningful data. Therefore, there is always a need to go to the lookup table for the value. 3. Data model is difficult to query against: The data model is optimized for applications, not for add querying. 6
Aggregation Aggregation : Aggregation in ER model is used to represent relationship among relationship .Aggregation is basically treating a relationship and all its participating entities, relations and attributes as a single entity , so that to link with some other entity. 7
Hours Name SSN Number Employee Project Work Uses 5 tables can represent the diagram Type Machinery Example :E-R diagram with aggregation 8
Example 2 The aggregation ER model comparison of entities EMPLOYEE(EmpId , Name) , PROGECT (projectId , Hours) and relation ASSIGNNED is linked with SOFTWARE (Sid , Sname) entity .The employee uses software for the purpose of development of project . Draw the ER-Diagram to represent it? 9
Example 3 From the tables below use by applying the aggregation rule. Draw an Er diagram for the aggregation relationship and the associated entity set ? 1) 2) 3) 4) 5) CUSTOMER(Cno, Name , Address) LOAN (Loan-no , Amount) BORROWER(Loan-no , Cno) EMP (Emp-no, Ename) LOAN-OFFICER (Cno, Loan-no ,Emp-no ) 10
Assignment 8 11
