1 / 44

IMS1907 Database Systems

IMS1907 Database Systems. Weeks 7 & 8 Data Modelling and Normalisation. Transforming ER Models to Relations. Transforming an ER diagram into normalised relations, and then merging all the relations into one final, consolidated set of relations can be accomplished in four steps

searles
Download Presentation

IMS1907 Database Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. IMS1907 Database Systems Weeks 7 & 8 Data Modelling and Normalisation Monash University 2004

  2. Transforming ER Models to Relations • Transforming an ER diagram into normalised relations, and then merging all the relations into one final, consolidated set of relations can be accomplished in four steps • Represent entities as relations • Represent relationships as relations • Normalise each relation • Merge the relations with a common PK Monash University 2004

  3. Representing Entities as Relations • Each entity in the ER diagram is transformed into a relation CUSTOMER Customer (Customer-no, Name, Address, City, State, Postcode, Discount) Monash University 2004

  4. Multi-valued Attributes as Relations • The multi-valued attribute becomes a relation with a composite key consisting of the primary key of the entity and the multi-valued attribute’s partial identifier EMPLOYEE Emp_ID Skill Customer (Emp_ID, Name, Address) Employee Skill (Emp_ID, Skill) Monash University 2004

  5. MOVIE VIDEOTAPE COPY Representing Weak Entities as Relations • The weak entity becomes a relation with a composite key consisting of the primary key of the base entity and the weak entity’s partial identifier Movie (Movie-ID, Title, Release-date) Videotape Copy (Movie-ID, Copy-No, Condition) Monash University 2004

  6. Representing Relationships as Relations • Binary Relationships (1:N, 1:1) • For 1:N, add the primary key of the entity on the ‘one’ side of the relationship as a foreign key in the relation that is on the ‘many’ side • For 1:1 relationship involving entities A and B, choose from • add the primary key of A as a foreign key of B • add the primary key of B as a foreign key of A • both of the above – at a cost! • use business rules and good sense to guide choice Monash University 2004

  7. Representing Relationships as Relations • Binary Relationships (1:N) CUSTOMER ORDER places Customer (Customer-no, Name, Address, City, State, Postcode, Discount) Order (Order-no, Order-date, Promised-date, Customer-no) Monash University 2004

  8. Representing Relationships as Relations • Binary Relationships (1:1) NURSE CARE CENTRE manages Nurse (Nurse-no, Name, Address, Qualification) Care Centre (Care-Centre-No, Location, Type, Nurse-no) Monash University 2004

  9. Qty_ord PRODUCT ORDER Ordered on Representing Relationships as Relations • Binary and Higher Degree Relationships (M:N) • If we wish to know the quantity of a product on an order, this attribute is an attribute of the relationship ‘ordered on’ Monash University 2004

  10. Representing Relationships as Relations • For M:N, first create a relation for each for each of the entity types, then create a relation for the relationship, with a composite primary key formed from the primary keys of the participating entity types PRODUCT ORDER-LINE ORDER Order (Order-no, Order-date, Promised-date) Order Line (Order-no, Product-no, Quantity-ordered) Product (Product-no, description, (other attributes)) Monash University 2004

  11. Representing Relationships as Relations • Unary or Recursive Relationships (M:N) ITEM is part of Item (Item-no, Name, Cost) Item-Bill (Item-no, Component-no, Quantity) Monash University 2004

  12. Representing Relationships as Relations • Unary or Recursive Relationships (1:N) EMPLOYEE supervises Employee (Emp-id, Name, Birthdate, Manager-id) Monash University 2004

  13. Representing Relationships as Relations • Ternary or Higher Relationships DOCTOR Patient (Patient-id, Patient-name) Doctor (Doctor-id, Doctor-name) Treatment (Treatment-code, Description) PATIENT treats TREATMENT Patient-Treatment (Patient-id, Doctor-id, Treatment-code, Date, Time, Result) Monash University 2004

  14. Representing Relationships as Relations • Supertype-subtype relationships PROPERTY BEACH PROPERTY MOUNTAIN PROPERTY Property (Property-ID, Address, City, Postcode, No-rooms, Rent) Beach (Property-ID, Nearest-beach, Distance-to-beach) Mountain (Property-ID, Skiing) Monash University 2004

  15. Normalisation • Normalisation is a process for converting complex data structures into simple, stable data structures in the form of relations • Data models consisting of normalised relations • are robust, stable flexible and have minimum redundancy • simplify enforcement of referential integrity constraints • make data maintenance easier • are an improved representation of real world objects Monash University 2004

  16. Normalisation • Normalisation ensures that each attribute is attached to the appropriate relation • Each attribute is contained in the relation which represents the real world system object or concept that the attribute describes or is a property of • the attribute Student-name should be in the relation STUDENT which represents the real world object “student” of interest to a student records system Monash University 2004

  17. Normalisation • Originally developed as part of relational database theory • Accomplished in stages, each of which corresponds to a “normal form” • Codd defined first, second and third normal forms • third normal form is adequate for most business applications • Later extensions include Boyce-Codd, 4th, 5th and domain-key normal forms Monash University 2004

  18. Functional Dependency • Central to normalisation and well-structured relations • A functional dependency is a particular relationship between attributes in a relation • For any relation R, with attributes ‘a’ and ‘b’, if for each value of ‘a’ there is only ever one value of attribute ‘b’ associated with it, attribute ‘b’ is said to be functionally dependent on attribute ‘a’ Monash University 2004

  19. Functional Dependency • If for every valid instance of A, that value of A uniquely determines the value of B, B is functionally dependent on A A identifies B A B Emp# Emp-name Emp# Salary Monash University 2004

  20. Normalisation • Normalisation to third normal form is accomplished in three steps each corresponding to a basic normal form • A normal form is a state of a relation that can be determined by applying simple rules concerning dependencies within that relation • Each step of the normalisation process is applied to a single relation in sequence so that the relation is converted to third normal form • All identified relations are normalised Monash University 2004

  21. Steps in Normalisation Unnormalised table Remove repeating groups First Normal Form 1NF Remove partial dependencies Second Normal Form 2NF Remove transitive dependencies Third Normal Form 3NF Monash University 2004

  22. First Normal Form 1NF • A relation is in first normal form if it contains no repeating data • the value of the data at the intersection of each row and column must be single-valued • Remove any repeating groups of attributes to convert a relation to 1NF • key of the removed group will be a composite key Monash University 2004

  23. First Normal Form 1NF • Identify primary key • Identify repeating groups • Remove repeating groups Order (Order#, Date, Customer#, Name, (Item#, Desc, Qty)) Order (Order#, Date, Customer#, Name) Order-Item (Order#, Item#, Desc) Monash University 2004

  24. Second Normal Form 2NF • A relation is in 2NF • if it is in 1NF • no part of the primary key is dependent on any other part of the primary key • no non-key attribute is dependent on only part of the primary key Monash University 2004

  25. Second Normal Form 2NF • Converting a relation to 2NF involves removing partial dependencies • A partial dependency exists • if one part of a composite primary key is dependent on another part of the key • if one or more non-key attributes are dependent on only part of a composite primary key Monash University 2004

  26. Second Normal Form 2NF • Remove any partial dependencies to convert a 1NF relation to 2NF • If the primary key consists of only one attribute then a 1NF relation is automatically in 2NF • If a 1NF relation has no non-key attributes, as long as there are no dependencies between the parts of the primary key, it is automatically in 2NF Monash University 2004

  27. Second Normal Form 2NF • Remove partial dependencies • A non-key attribute cannot be identified by part of a composite key Order (Order#, Item#, Desc, Qty-ordered) Order-Item (Order#, Item#, Qty-ordered) Item (Item#, Desc) Monash University 2004

  28. Dependencies Within the Primary Key • Part of a composite primary key cannot be identified by another part of the primary key DEPT (Dept#, Dept-name, (Emp#, Emp-name)) DEPT (Dept#, Dept-name) DEPT-EMP (Dept#,Emp#, Emp-name) But DEPT-EMP (Dept#,Emp#, Emp-name) Remove Dept# from the key: EMP (Emp# ,Emp-name, Dept#) Monash University 2004

  29. Partial Dependency Anomalies Order-Item • UPDATE - change item-desc in many places • DELETE - data for last item lost when last order for that item is deleted • CREATE - cannot add new item until it is ordered Order# Item# Item-desc Qty 2 27 873 nut 1 28 402 bolt 10 28 873 nut 50 30 495 washer Monash University 2004

  30. Solution to Anomalies – 2NF Order delete last order for item, but item remains Order# Item# Qty 27 873 2 28 402 1 28 873 10 Item 30 495 50 Item# Desc add new item at any time 873 nut change item description in one place only 402 bolt 495 washer Monash University 2004

  31. Third Normal Form 3NF • A relation is in 3NF if it is in 2NF and no transitive dependencies exist • A transitive dependency is a functional dependency between two or more non-key attributes • if a relation has no or only one non-key attribute then by definition, a transitive dependency cannot exist • Remove any transitive dependencies to convert a 2NF relation to 3NF Monash University 2004

  32. Third Normal Form 3NF • Remove Transitive Dependencies • A non-key attribute cannot be identified by another non-key attribute Employee (Emp#, Ename, Dept#, Dname) Employee (Emp#, Ename, Dept#) Department(Dept#, Dname) (look for foreign keys and their attributes) Monash University 2004

  33. Transitive Dependency Anomalies Employee Emp-name Emp# Dept# Dname 10 Smith D5 MIS 20 Jones D7 Finance 25 Smith D7 Finance 30 Black D8 Sales • UPDATE - change dept name in many places • DELETE - data for dept lost when last employee for that dept is deleted • CREATE - cannot add new dept until an employee is allocated to it Monash University 2004

  34. Solution to Anomalies – 3NF Employee delete last emp in dept, but dept remains Emp# Ename Dept# 10 Smith D5 20 Jones D7 25 Smith D7 30 Black D8 Item Dept# Dname add new dept at any time D5 MIS D7 Finance change dept name in one place D8 Sales Monash University 2004

  35. Normalisation to 3NF • A relation is normalised to 3NF if all attributes are fully functionally dependent on the primary key • remove repeating groups • remove partial dependencies • remove transitive dependencies Monash University 2004

  36. Normalisation - Merging Relations • During the normalisation process two or more relations with the same primary key may appear • The set of 3NF relations must not contain any duplicate data • Relations with the same primary key should be merged Monash University 2004

  37. Normalisation of Relations • Synonyms • Two or more attributes may have different names but the same meaning • Either adopt one of the names as a standard or choose a third name STUDENT1 (Student-id, Name, Phone-no) STUDENT2 (VCE-no, Name, Address) STUDENT (Student-id, Name, Address, Phone-no) Monash University 2004

  38. Normalisation of Relations • Homonyms • Two or more attributes may have the same name but different meanings • To resolve the conflict, new attribute names need to be created STUDENT1 (Student-id, Name, Address) STUDENT2 (Student-id , Name, Phone, Address) STUDENT (Student-id, Name, Phone, Campus-address, Permanent-address ) Monash University 2004

  39. Normalisation of Relations • When two 3NF relations are merged, transitive dependencies may result STUDENT1 (Student-id, Major) STUDENT2 (Student-id , Advisor) STUDENT (Student-id, Major, Advisor) But MAJOR ADVISOR (dependency!) STUDENT (Student-id, Major) MAJOR (Major , Advisor) Monash University 2004

  40. Data Structure Diagrams (DSD) • A set of 3NF relations may be converted to a simple diagrammatic form to begin physical database design • The conversion is simple • Draw a named rectangle for each relation • Draw a relationship line between rectangles linked by foreign keys with a “many” cardinality at the foreign key end of the relationship Monash University 2004

  41. Data Structure Diagrams (DSD) • CUSTOMER (Cust#, Cname,Phone number) • SALES ORDER (Sord#, Sord-date, Cust#) • SALES ORDER-ITEM (Sord#, Item#, Qty) • ITEM (Item#, Item-desc) CUSTOMER ITEM SALES ORDER SALES ORDER LINE Monash University 2004

  42. Data Structure Diagrams (DSD) • Eliminate redundant relationships TOUR (Tourcode, ...) DEPARTURE redundant (Tourcode, depdate, ...) BOOKING (Booking#, ... , Tourcode, depdate) Monash University 2004

  43. Detailed Data Modelling - Summary • Detailed data modelling involves • collecting detailed attributes for each entity and relationship identified • converting ER models to relations • normalising the relations • merging relations from each user viewpoint • converting the normalised and merged relations to create a data structure diagram Monash University 2004

  44. References Hoffer, J.A., George, J.F. & Valacich, J.S., (2002), 3rd ed., Modern Systems Analysis and Design, Prentice-Hall, New Jersey, Ch 10,12. Whitten, J.L., Bentley, L.D. & Dittman, K.C., (2001), 5th ed., Systems Analysis and Design Methods, Irwin McGraw-Hill, New York, NY, Ch 7 Monash University 2004

More Related