1 / 41

Conceptual Model Translation

Conceptual Model Translation. Translating an ERD into a relational database schema. Conceptual model translation. Logical level models often require greater detail. Logical model may not support some conceptual constructs: e.g., no direct equivalent of many-to-many in RM

teddy
Download Presentation

Conceptual Model Translation

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. Conceptual ModelTranslation Translating an ERD into a relational database schema SFDV3002

  2. Conceptual model translation • Logical level models often require greater detail. • Logical model may not support some conceptual constructs: • e.g., no direct equivalent of many-to-many in RM • must map conceptual ⇒ logical • Typically ERD ⇒ SQL. • Oracle Designer’s Database Design Transformer. SFDV3002

  3. **substitute your own case study if you wish** Case study: CD/DVD library(see also Example 2–1) MEMBER # MEMBER_NUM * FIRSTNAMES * SURNAME * STREET * SUBURB * CITY o PHONE o EMAIL HIRE # HIRE_NUM * HIRE_DATE * RETURNED to makes consists of is item of RECORDING # RECORDING_ID * TITLE * CHARGE * NUM_COPIES CD * ARTIST * GENRE DVD * DIRECTOR * ZONE SFDV3002

  4. General approach to translation • Generate candidate relations via: • entity translation • subtypes (see SFDV3003) • attribute translation • primary key definition • relationship translation • Normalise candidate relations if necessary, to at least 3NF (see SFDV3003) SFDV3002

  5. Translation rules Entity translation • each ERD entity ⇒ relation (usually) • different ways to handle subtypes (see slides **20–25**) Attribute translation • each ERD attribute ⇒ attribute in corresponding relation Primary key definition • entity unique ID ⇒ PK of corresponding relation Relationship translation • implement relationships through FK placements SFDV3002

  6. Entity and attribute translation • Each entity (usually) ⇒ relation. • entity name ⇒ relation name • Each attribute of entity ⇒ attribute of corresponding relation. • entity attribute name ⇒ relation attribute name SFDV3002

  7. Entity and attribute translation MEMBER # MEMBER_NUM * FIRSTNAMES * SURNAME * STREET * SUBURB * CITY o PHONE o EMAIL HIRE # HIRE_NUM * HIRE_DATE * RETURNED SFDV3002

  8. Dealing with nulls MEMBER # MEMBER_NUM * FIRSTNAMES * SURNAME * STREET * SUBURB * CITY o PHONE o EMAIL indicates nulls not allowed indicates nulls are allowed ⇒ Member(Member_num, Firstnames, Surname,Street, Suburb, City, Phone, Email) SFDV3002

  9. Translating subtypes: Approach 1(D’Orazio & Happel, pp. 143–144) • One relation per subtype entity plus one for supertype entity. • Most flexible. • No wasted space. • Most complex implementation. SFDV3002

  10. Translating subtypes: Approach 1(D’Orazio & Happel, pp. 143–144) RECORDING # RECORDING_ID * TITLE * CHARGE * NUM_COPIES CD * ARTIST * GENRE DVD * DIRECTOR * ZONE ⇒ Recording(Recording_ID, Title, Charge, Num_copies) CD(Recording_ID, Artist, Genre) DVD(Recording_ID, Director, Zone) SFDV3002

  11. Translating subtypes: Approach 2(D’Orazio & Happel, pp. 143–144) • One relation per subtype entity, each includes supertype attributes. • No separate supertype relation. • Less flexible. • Potential for redundant data. • Less complex implementation. • Possible referential integrity and redundancy issues. SFDV3002

  12. Translating subtypes: Approach 2(D’Orazio & Happel, pp. 143–144) RECORDING # RECORDING_ID * TITLE * CHARGE * NUM_COPIES CD * ARTIST * GENRE DVD * DIRECTOR * ZONE ⇒ CD(Recording_ID, Title, Charge, Num_copies, Artist, Genre) DVD(Recording_ID, Title, Charge, Num_copies, Director, Zone) SFDV3002

  13. Translating subtypes: Approach 3(D’Orazio & Happel, pp. 143–144) • Only one relation with attributes from all related super- and subtype entities. • No separate subtype relations. • May need a “type”attribute. • Least flexible. • Unused attribute values. • Simplest implementation. SFDV3002

  14. Translating subtypes: Approach 3(D’Orazio & Happel, pp. 143–144) RECORDING # RECORDING_ID * TITLE * CHARGE * NUM_COPIES CD * ARTIST * GENRE DVD * DIRECTOR * ZONE ⇒ Recording(Recording_ID, Title, Charge, Num_copies, Artist, Genre, Director, Zone) SFDV3002

  15. Primary key definition • Entity unique identifier ⇒ corresponding relation’s PK. SFDV3002

  16. Primary key definition MEMBER # MEMBER_NUM * FIRSTNAMES * SURNAME * STREET * SUBURB * CITY o PHONE o EMAIL HIRE # HIRE_NUM * HIRE_DATE * RETURNED SFDV3002

  17. Relationship translation: One-to-one • Place PK of first relation into second relation as FK, or vice versa, or both. SFDV3002

  18. Relationship translation: One-to-one • If one entity optional and other mandatory, put FK in “optional” relation. Ensures no nulls in FK (see slide **37**) SFDV3002

  19. Relationship translation: One-to-one • If one entity optional and other mandatory, put FK in “optional” relation. FK SFDV3002

  20. Relationship translation: One-to-many • Place PK of “one” relation into “many” relation as FK. SFDV3002

  21. Relationship translation: One-to-many MEMBER # MEMBER_NUM * FIRSTNAMES * SURNAME * STREET * SUBURB * CITY o PHONE o EMAIL HIRE # HIRE_NUM * HIRE_DATE * RETURNED to makes ⇒ Hire(Hire_num, Hire_date, Returned, Member) ⇒ FK Member(Member_num, Firstnames, Surname, Street, Suburb, City, Phone, Email) SFDV3002

  22. Relationship translation: Many-to-many • Invent intermediate “all-key”entity & convert M:N relationship ⇒ two 1:M’s. always many & mandatory SFDV3002

  23. Relationship translation: Many-to-many ⇐ HIRE # HIRE_NUM * HIRE_DATE * RETURNED Hire(Hire_num, Hire_date, Returned, Member) FK ⇐ consists of Recording_Hire(Hire_num, Recording_ID) FK is item of Recording(Recording_ID, Title, Charge, Num_copies, Artist, Genre, Director, Zone) ⇐ RECORDING # RECORDING_ID * TITLE * CHARGE * NUM_COPIES CD * ARTIST * GENRE DVD * DIRECTOR * ZONE SFDV3002

  24. Relationship translation: Unary(D’Orazio & Happel pp. 164–172) • Place FK in relation that refers to its own PK. FK SFDV3002

  25. Relationship translation: Unary(D’Orazio & Happel pp. 164–172) FK SFDV3002

  26. Relationship participation and nulls Relationship participation determines whether FKs can be null: FK in Employeecan be null Position Employee FK in Employeecannot be null Position Employee SFDV3002

  27. Normalization overview • Data redundancy • What it is and why it’s a problem • Update, insert and delete anomalies • Dependencies • Used to identify underlying data dependencies so that we can remove redundancy • Normalisation • Functional and multivalued dependencies

  28. Data redundancy • Values stored repetitively in relations (esp. poorly designed relations) • Potential for anomalous data to be stored This relation associates employees with projects. Assume no nulls are allowed.

  29. How redundancy arises • Ad hoc databases • “flat file” databases • spreadsheets • Poor database design • poor analysis • poorly designed ERDs • Modifications to existing systems • “bolting on” new attributes

  30. 37 35 37 35 Only one value updated: ANOMALY Update anomalies • Each person’s salary is repeated for each project they are involved with. What does this imply when we need to increase someone’s salary? Both values updated: OK 50 48

  31. What happens to (Brown, 20)? ANOMALY Delete anomalies • If a project ends (i.e., is deleted), what happens to the data for employees on that project? Delete project Alpha

  32. Where do we store (Johnson, 36) until then? ANOMALY Insert anomalies • What happens when we hire a new person? (remember, no nulls allowed) Johnson hasn’t yet been assigned to a project, but no nulls allowed

  33. The solution: Normalisation • Breaking up the relation eliminates the worst of the redundancy [see lecture 10 for more details]

  34. But first: Functional dependencies (FDs) • Theoretical basis for normalisation [D’Orazio & Happel pp. 206–209] • Attribute B functionally dependent on (or functionally determined by) attribute (A  B) if each value of A is associated with exactly one value of B • Student ID functionally determines student name, but not vice versa [D’Orazio & Happel fig. 10.2, p. 206] • Car registration functionally determines car owner • Every attribute functionally dependent on PK

  35. Some more examples • student ID  name, address, phone, … • car registration  owner name • what about car registration and VIN? • IRD number  tax payable • product ID + order no  quantity ordered • What about: • home address  student name? • name  birth date? • student ID + name  birth date? rego  VIN VIN  rego

  36. Using functional dependencies • To determine functional dependencies: • need detailed knowledge of the business rules • examine existing data sets, although not always practical when these data sets are large or unknown • Can represent FDs as denoted as Functional Dependency Diagrams (FDDs) [D’Orazio and Happel, Table 10.1, p. 207] • “Bottom-up” approach • E-R conceptual modelling is “top-down” • best used as a design validation tool

  37. Types of functional dependency • Dependencies on more than one attribute • always arise with composite PKs • Example: • year + course code  course coordinator (i.e., course coordinator determined by combination of both a particular year and a course code) Year Course coordinator Course code

  38. Student Name Invoice Date Types of functional dependency • Partial functional dependency • Subset of left hand side determines right hand side • “extra” attributes on LHS are unnecessary Invoice Number Student ID Date of Birth Invoice Total

  39. Types of functional dependency • Transitive dependency • part number determines supplier number • supplier number determines supplier name • therefore, part number alone also determines supplier name • Ideally should not exist within the same relation Part number Part number Supplier number Supplier name Supplier name

  40. Multivalued dependencies • Attribute B multiply dependent on (or multiply determined by) attribute A if for each value of A there can be a set of B values (A B) • year + course code multidetermines lecturer • 2003 + SFDV3003: {Long, Stanger} • 2004 + SFDV3003: {Woodford, Irwin, Stanger} • 2004 + SFDV3004: {Stanger, Edwards} • compare with year + course code  coordinator • home address  student name • but probably not name  birth date   

  41. summary • Redundancy and anomalies • Causes and consequent problems • Functional Dependencies • Partial vs. full dependency • Transitive dependency • Multivalued dependencies

More Related