1 / 21

Database Design Using Entity-Relationship Models

Database Design Using Entity-Relationship Models. Mapping E-R models into relations Four common data structures. Mapping E-R Models to Tables. We may not be familiar with all notations There may be E-R constructs that cannot be translated directly Multi-valued attributes

placido
Download Presentation

Database Design Using Entity-Relationship Models

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 Design Using Entity-Relationship Models Mapping E-R models into relations Four common data structures

  2. Mapping E-R Models to Tables • We may not be familiar with all notations • There may be E-R constructs that cannot be translated directly • Multi-valued attributes • Ternary relationships • We may need to change the data model • Change a relationship into an entity • Change an entity into an attribute • Collapse two entities • Make a strong entity weak

  3. Basic ConversionRules • A database conforming to an E-R diagram can be represented by a collection of tables. • Converting an E-R diagram to a table format is the basis for deriving a relational database design from an E-R diagram. • Primary keys allow entities and relationships to be expressed uniformly as tables. • For each entity and many-to-many relationship there is a unique table, named after it. • Each table has columns (generally corresponding to attributes) with unique names.

  4. Representing Entities as Tables • A strong entity set reduces to a table with the same attributes CUSTOMER (CustNumber, CustName, Address, City, State, Zip, ContactName, PhoneNumber)

  5. Representing Entities • We then may need to normalize the table if it is not in DK/NF • De-normalize? CUSTOMER (CustNumber, CustName, Address, City, State, Zip, ContactName, PhoneNumber) CUSTOMER (CustomerNumber, Address, Zip, ContactName) ZIP-TABLE (Zip, City, State) CONTACT (ContactName, PhoneNumber) Interrelation (integrity) constraints: CUSTOMER[Zip] Í ZIP-TABLE[Zip] CUSTOMER[ContactName] Í CONTACT[ContactName]

  6. Representing Weak Entities • Weak entity sets must be documented by referential integrity constraints • An ID-dependent weak entity set becomes a table that includes a column for the primary key of the identifying strong entity • The table corresponding to the weak relationship is redundant

  7. Foreign key! Representing HAS-A Relationships • 1:1 - Place key of one relation into other • Perhaps they should be combined! • 1:N - Place key of parent into child

  8. Intersection relation Error! Representing HAS-A Relationships • M:N - Build a table with columns for primary keys of two participating entity sets • May add descriptive attributes of relationship set

  9. Recursive Relationships • Same as non-recursive relationships • One participant instead of two • E.g., 1:1 • Remember two alternatives • Foreign key can have NULL values (when?)

  10. Recursive Relationships • What about 1:N and M:N recursive? • Give an example of each, not from book :) • How many tables do you end up with, in each case? • How is this different from the non-recursive case? • What is the domain of the foreign key?

  11. Higher-order Relationships • Treat as combination of binary relationships • Binary constraints must be enforced with business rules • Types of constraints: • MUST (ORDER:CUSTOMER:SALESPERSON) • ORDER(OrdNo, CustNo, SalespersonNo, ...) • CUSTOMER(CustNo, SalespersonNo, ...) • SALESPERSON(SalespersonNo, ...) • MUST NOT (PRESCRIPTION:DRUG:PATIENT) • MUST COVER (AUTO:REPAIR:TASK)

  12. Representing IS-A Relationships • Specialization method • Form a table for each sub-type entity • No table for generalized (super-type) entity • Common attributes are repeated

  13. Usually same key Need this? Representing IS-A Relationships • Generalization method • Form a table for the super-type entity • Form a table for each sub-type entity (include primary key of generalized entity set, 1:1) • Common attributes are inherited

  14. Trees (hierarchies) • Nodes are entities • Only 1:N relationships (branches) • Each child has a unique parent • Root: unique node without parent • Siblings: children sharing parent • Obvious relational representation Root

  15. Simple Networks • Only 1:N relationships • But a child can have more than one parent (of different types) • Obvious relational representation

  16. Complex Networks • A child can have multiple parents of the same type • At least one N:M relationship • Need intersection relations(s)

  17. Bills of Materials • A special case of networks • M:N recursive relationships • Foreign keys in intersection relation have same domain

  18. Example: Practice!...

  19. Another Example: Overhead • EMPLOYEE: SSN, Fname, Minit, Lname, BirthDate, Address, JobTypeSECRETARY: SSN, TypingSpeedTECHNICIAN: SSN, TGradeENGINEER: SSN, EngType • EMPLOYEE: SSN, Fname, Minit, Lname, BirthDate, Address, JobType, TypingSpeed, Tgrade, EngType

  20. Another Example: Overhead • CAR: VehicleID, LicensePlateNo, Price MaxSpeed, NoOfPassengersTRUCK: VehicleID, LicensePlateNo, Price, NoOfAxles, Tonnage • PART: PartNo, Description, MFlag, DrawingNo, ManufactureDate, BatchNo, PFlag, SupplierName, ListPrice

  21. Another Example: Overhead • PERSON: SSN, Fname, Minit, Lname, BDate, No, Street, AptNo, City, State, ZipEMPLOYEE: SSN, Salary, Rank, Office, PhoneSTUDENT: SSN, Class, GflagGRAD_DEGREES: SSN, Year, Degree, CollegeINSTRUCTOR_RESEARCHER: SSN

More Related