RESOLVING MANY TO MANY Course Section Instructor Student Registration Note that

Resolving many to many course section instructor

This preview shows page 66 - 77 out of 272 pages.

RESOLVING MANY-TO-MANY Course Section Instructor Student Registration Note that Linking Tables must have many-to-many Cardinality on them
Image of page 66
DESIGN PROBLEMS 67 Are there problems here? emp_no name rank salary proj_no descr s_date 1234 Joe Engr 60K QZY3345 NH 3 generation 03/10/89 2345 Jack App 48K XTR7789 Waste disposal 07/09/88 2345 Jack App 48K QZY3345 NH 3 generation 01/12/88 3456 Jenny Engr 60K PPV5678 Sludge treatment 07/07/93 3456 Jenny Engr 60K XTR7789 Waste disposal 07/17/93 4567 Judy Sr. Engr 66K YTZ8992 Sludge floatation 01/10/90 4567 Judy Sr. Engr 66K PPV5678 Sludge treatment 04/05/89 4567 Judy Sr. Engr 66K PSP0089 N 2 O generation 09/11/91
Image of page 67
ANOMALIES Update Anomaly What if the name of the project PPV5678 is changed to Sludge disposal? Insertion Anomaly What if a new project is negotiated when there is no employee assigned to it yet? What if a new employee joins who is not yet assigned to any project? Deletion Anomaly What if Judy resigns from her job? What happens to the Sludge Floatation and N 2 O generation projects? 68
Image of page 68
ALTERNATIVE DESIGN 69 Employee Table Project Table Works_on Table emp_no name rank salary 1234 Joe Engr 60K 2345 Jack App 48K 3456 Jenny Engr 60K 4567 Judy Sr. Engr 66K proj_no descr QZY3345 CO generation XTR7789 Waste disposal PPV5678 Sludge treatment YTZ8992 Sludge floatation PSP0089 NO generation emp_no proj_no s_date 1234 QZY3345 03/10/89 2345 XTR7789 07/09/88 2345 QZY3345 01/12/88 3456 PPV5678 07/07/93 3456 XTR7789 07/17/93 4567 YTZ8992 01/10/90 4567 PPV5678 04/05/89 4567 PSP0089 09/11/91
Image of page 69
NORMALIZATION Normalization is a process that enables a database designer to eliminate design problems by decomposing (or reorganizing) the existing table structures The benefi ts of Normalization are, a model that better represents the actual business situation a model with less redundancy in the data a model which allows easy update of data a model that allows for accurate query results 70
Image of page 70
NORMALIZATION (CONT’D) Normalization is a step-by-step process has several levels of normalization first , second , and third levels are what we will consider If a data model has passed the first level , we say that it is in first normal form If it has passed the second level , second normal form Etc.
Image of page 71
NORMALIZATION (CONT’D) Normalization Example A business keeps data on its employees ID, first name, last name, rank, department, salary Rank is a number (e.g., 1 for lowest, 5 for highest) An employee’s salary depends only on the rank and department of the employee Employees can take extra training courses, and this data is stored as well Course ID, course description, completion date
Image of page 72
NORMALIZATION (CONT’D) Data Model #1
Image of page 73
NORMALIZATION (CONT’D) First Normal Form (1NF) A model is in 1NF if, for every instance of an entity, each attribute of the instance is completely determined by the instance’s PK In other words, 1NF holds when there are no instance attributes that have multiple values To put an ERD into 1NF, add another entity and a 1:M relationship to it
Image of page 74
NORMALIZATION (CONT’D) Data Model #1 Data Model #2 (in 1NF)
Image of page 75
NORMALIZATION (CONT’D) Second Normal Form (2NF) A model is in 2NF if it is in 1NF, and for every instance of an entity having a PK
Image of page 76
Image of page 77

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture