1 / 28

Database Systems

Database Systems. Conceptual to Relational Modeling II Lecture # 11 Feb 25 th 2011. ER Model => Relational Model. Step 1: Regular Entities (and Single-valued attributes). Step 2: Many-to-Many Relationships Step 3: Many-to-One Relationships Step 4: One-to-One Relationships

valentine-reilly
Download Presentation

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. Database Systems Conceptual to Relational Modeling II Lecture # 11 Feb 25th 2011

  2. ER Model => Relational Model • Step 1: Regular Entities (and Single-valued attributes). • Step 2: Many-to-Many Relationships • Step 3: Many-to-One Relationships • Step 4: One-to-One Relationships • Step 5: n-ary (m-way) relationships • Step 6: Weak Entities • Step 7: Multi-valued Attributes • Step 8: Entity Subtypes and Super-types • Step 9: Additional Constraints • These steps may not be followed in the above sequence. You may have to do step 6 before step 2, and so on.

  3. An Example

  4. Step 1: Regular Entities • Each regular entity type maps into a base table. • Each simple attribute of the entity type maps to an attribute of the table. • Each composite attribute will be broken into many simple attributes. • We’ll treat Multi-valued attributes later. • Derived attributes, by definition, can be derived, and therefore are not necessary to be represented. • The primary key of the entity type maps to the primary key of the table.

  5. Step 1: Example Department {D#, Dname, City}; Employee {E#, First, MI, Last, Salary, Manager}; Supplier {S#, Sname, City, Status}; Part {P#, Pname, Color, Weight, City}; Project {J#, Jname, City}; • Primary keys are underlined. • How about Employee Name and Phone?

  6. Step 2: M-N Relationships • Each Many-to-Many relationship type maps into a table. • The primary key of this table is the combination of the primary keys of the participating entity types. • These are also foreign keys. • Attributes of the relationship type maps to attributes of the table, similar to those of regular entity types.

  7. Step 2: Example Proj_Work{E#, J#} Foreign key {E#} References Employee, Foreign key {J#} References Project; SP {S#, P#, QTY} Foreign key {S#} References Supplier, Foreign key {P#} References Part; Contain {Cont_P#, Comp_P#, QTY} Foreign key {Cont_P#} References Part, Foreign key {Comp_P#} References Part;

  8. Step 3: M-1Relationships • For each regular Many-to-One relationship type, • introduce a foreign key in the table on the “many” side • that references the table on the “one” side. • Do not need to introduce a separate table. • We’ll treat the identifying relationship of each weak entity type later.

  9. Step 3: Example Employee {Emp#, First, MI, Last, Salary, Manager, D#} Foreign key {D#} references Department; Project {J#, Jname, City, PrjMgr_E#} Foreign key {PrjMgr_E#} references Employee; • Do we need tables for: • Employee Prj_Manage Project • Employee Work_In Department

  10. Step 4: 1-1 Relationships • We treat One-to-One relationship similarly as we treat Many-to-One relationship. • We prefer to extend the entity type that has a “mandatory” participation in the relationship.

  11. Step 4: Example For Manage Relationship: Department {Dept#, Dname, City, Manager_E#} Foreign key {Manager_E#} references Employee; • However, both M-1 and 1-1 relationships can be treated as special cases of M-M relationships, so a separate table could be created for each relationship, no matter whether it’s M-M, M-1, or 1-1.

  12. Step 5: M-Way relationships • The primary key of this table is the combination of the primary keys of the participating entity types. • These are also foreign keys. • Attributes of the relationship type maps to attributes of the table, similar to those of regular entity types.

  13. Step 5: Example SPJ {S#, P#, J#, QTY}

  14. Step 6: Weak Entities • The identifying relationship of a weak entity is of course a Many-to-One relationship, • The weak entity type must have a “mandatory” participation in its identifying relationship. • Each weak entity type maps into a base table. • Attributes are treated as usual. • Introduce a foreign key that references the primary key of its identifying entity type. • The primary key is the primary key of its identifying entity type plus its own partial key.

  15. Step 6: Example Dependent {E#, Dname, Gender} Foreign key {E#} References Employee;

  16. Step 7: Multi-valued Attributes • Each multi-valued attribute maps into a separate table. • The table has an attribute for each simple attribute of the multi-valued attribute. • Include also an attribute for the primary key of the entity or relationship type that the attribute belongs to. • This is also a foreign key. • The primary key of this table is the combination of all the attributes if the multi-valued attribute is simple. • If the multi-valued attribute is composite, the primary key of this table may be a combination of some of the attributes.

  17. Step 7: Example Emp_Phone {Emp#, Phone} Foreign key {Emp#} References Employee; E.g. Customer has Cust#, name, multiple Credit Cards that have Card # and Expiration Customer {Cust#, name}; Credit_Cards {Cust#, Card#, Expiration} Foreign key {Cust#} References Customer;

  18. Step 8: Entity Subtypes/Supertypes • The supertype maps into a base table. • Each subtype maps into another base table. • The primary key is the same as the primary key of its supertype. • This is also a foreign key. • Treat attributes that belongs to the subtype only as usual. • The primary key of the root entity propagates down a hierarchy of entity types.

  19. Step 8: Example Employee {E#, First, MI, Last, Salary, Manager, D#} Foreign key {D#} references Department; Programmer {E#, Language} Foreign key {E#} references Employee; SYS_Prog {E#, OS} Foreign key {E#} references Programmer; APP_Prog {E#, Web_Based} Foreign key {E#} references Programmer;

  20. The Relational Schema (So far) Department {D#, Dname, City, Manager_E#} Foreign key {Manager_E#} references Employee; Employee {E#, First, MI, Last, Salary, Manager, Dept#} Foreign key {D#} references Department; Supplier {S#, Sname, City, Status}; Part {P#, Pname, Color, Weight, City}; Project {J#, Jname, City, Prj_Mgr_E#} Foreign key {Prj_Mgr_E#} references Employee; Proj_Work {E#, J#} Foreign key {E#} References Employee, Foreign key {J#} References Project;

  21. The Relational Schema (Cont.) SP {S#, P#, QTY} Foreign key {S#} References Supplier, Foreign key {P#} References Part; Contain {Cont_P#, Comp_P#, QTY} Foreign key {Cont_P#} References Part, Foreign key {Comp_P#} References Part; SPJ {S#, P#, J#, QTY} Foreign key {S#} References Supplier, Foreign key {P#} References Part, Foreign key {J#} References Project; Dependent {E#, Dname#, Gender} Foreign key {E#} References Employee;

  22. The Relational Schema (Cont.) Emp_Phone {E#, Phone} Foreign key {E#} References Employee; Programmer {E#, Language} Foreign key {E#} references Employee; SYS_Prog {E#, OS} Foreign key {E#} references Programmer; APP_Prog {E#, Web_Based} Foreign key {E#} references Programmer; • Totally 14 tables.

  23. SUPPLIER – PART DB QTY SUPPLY

  24. Additional Constraints However, we haven’t treated the additional constraints. • Weight is a real number in [0, 10000]. • QTY is a real number in [0, 60000]. • QTY must be entered for SP. • Color must be one of ('Red', 'Green', 'Blue', 'Yellow', 'White', 'Black', 'Other'). • Sname does not have duplicates and must be entered. • Suppliers in London must have status 20. • No suppliers with status less than 20 can supply any part in a quantity greater than 500.

  25. Step 9: Additional Constraints Supplier {S#, Sname, City, Status} Constraint NOT( CITY='London' AND STATUS <> 20), Constraint Sname UNIQUE NOT NULL; Part {P#, Pname, Color, Weight, City} Constraint Color IN ('Red', 'Green', 'Blue', 'Yellow', 'White', 'Black', 'Other'), Constraint Weight >= 0 AND Weight <= 10000;

  26. Data Modeling Tools A number of popular tools that cover conceptual modeling and mapping into relational schema design. Examples: ERWin, S- Designer (Enterprise Application Suite), ER- Studio, etc. POSITIVES: serves as documentation of application requirements, easy user interface - mostly graphics editor support

  27. Problems with Current Modeling Tools • DIAGRAMMING • Poor conceptual meaningful notation. • To avoid the problem of layout algorithms and aesthetics of diagrams, they prefer boxes and lines and do nothing more than represent (primary-foreign key) relationships among resulting tables.(a few exceptions) • METHODOLGY • lack of built-in methodology support. • poor tradeoff analysis or user-driven design preferences. • poor design verification and suggestions for improvement.

  28. Some of the Currently Available Automated Database Design Tools

More Related