1 / 28

Database Design Methodology: Concepts and Considerations

Learn the design methodology for creating efficient and effective databases, including one-to-one and many-to-many relationship considerations, handling null values, and entity subtypes.

darren
Download Presentation

Database Design Methodology: Concepts and Considerations

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. Chapter 6 Database Design 2: Design Methodology Spring 2006 Concepts of Database Management, 5th Edition, Pratt & Adamski

  2. Exercise #4 0 • Due: Feb 22 for full credit, March 1 for 50% credit • Points: 20 points • Pratt & Adamski (End of Chapters 5 & 6) • Assignments must have cover sheet with your name. • Chapter 5 [pp 170-171]: Answer questions 1, 2, 3 for Premiere Products and questions 2, 4 for Henry Books. Omit 4NF, answer the questions for 3NF. • Chapter 6 pp [223-224]: Answer questions 2, 4 for Premiere Products and questions 1, 2, 3 for Henry Books. Turn in ERD and copies of new tables and attributes. (Alternatively, use DBDL.) Concepts of Database Management, 5th Edition, Pratt & Adamski

  3. One-to-One [1:1] Relationship Considerations • 1:N [maximum cardinality] relationships are the standard. Let’s start by examining how to treat 1:1 relationships • Implementing a one-to-one relationship by simply including the primary key of each table as a foreign key in the other table • A problem is that there is no guarantee that the information will match Concepts of Database Management, 5th Edition, Pratt & Adamski

  4. 1:1 Relationship Considerations (con’t) • One possible solution is to create a single table. OK? • Although workable, two features are not particularly attractive: • Combines columns of two different entities into a single table. Violates “single theme” table notion. • If possible for one entity to exist without the other, then this would cause an insertion/deletion anomaly Concepts of Database Management, 5th Edition, Pratt & Adamski

  5. 1:1 Relationship Considerations (con’t) • Better solution!! • Create two separate tables for Customers and Sales Reps and to include the primary key of one of them as a foreign key in the other • This foreign key would also be designated as an alternate key Concepts of Database Management, 5th Edition, Pratt & Adamski

  6. 1:1 Relationship Considerations Figure 6.20 (6.25) (4th Ed.) Include primary key of each table as foreign key in the other. OVERKILL! Concepts of Database Management, 5th Edition, Pratt & Adamski

  7. 1:1 Relationship Considerations Figure 6.22 Implemented in a single table. Possible Anomalies? Add new Rep: Tyler Harris? What if Tyler has no customers? Concepts of Database Management, 5th Edition, Pratt & Adamski

  8. 1:1 Relationship Considerations Figure 6.23 1:1 relationship implemented by including primary key of one table as foreign key in ONLY the other. Concepts of Database Management, 5th Edition, Pratt & Adamski

  9. Many-to-Many [N:M] Relationship Considerations • Complex issues arise when more than two entities are related in a N:M relationship [binary relationship] • Many-to-many-to-many [N:M:P] relationship – involves multiple entities [tertiary or higher relationships] • Crucial issue in making the determination between a single many-to-many-to-many relationship and two (or three) many-to-many relationships is the independence of each entity. • Birth [Mother, Father, Child] One vs. multiple relations • Parent:Child vs. Mother:Father:Child Concepts of Database Management, 5th Edition, Pratt & Adamski

  10. Future N:M:P slide • Birth [Mother, Father, Child] vs. … Mom Dad Baby Concepts of Database Management, 5th Edition, Pratt & Adamski

  11. Null Values • Null - a special value (not a blank) • Null - actually represents the absence of a value in a field (undefined) • Nulls - used when a value is either unknown or not applicable [“UNK” or “N/A”] • NULL is a SQL reserved word: SELECT * FROM EMP WHERE PHONENO=NULL; Concepts of Database Management, 5th Edition, Pratt & Adamski

  12. Table Split to Avoid Null Values Figure 6.27 Nulls are absence of values Concepts of Database Management, 5th Edition, Pratt & Adamski

  13. Entity Subtypes • Entity subtype – table that is a subtype of another table • Incomplete category – records that do not fall into the subtype • Complete categories – all records fall into the categories Concepts of Database Management, 5th Edition, Pratt & Adamski

  14. Entity Subtypes Incomplete CategoriesFigure 6.29 Concepts of Database Management, 5th Edition, Pratt & Adamski

  15. Student Table Split into Subtypes Helps to Avoid NullsFigure 6.31 Concepts of Database Management, 5th Edition, Pratt & Adamski

  16. Two Entity SubtypesComplete CategoriesFigure 6.33 Concepts of Database Management, 5th Edition, Pratt & Adamski

  17. Entity-Relationship Model[Chen: Extended ERDs] • Various ERD methodologies use different symbols to represent data; non-standardized notation • E.g., Entities are drawn as rectangles • Relationships are drawn as diamondswith lines connecting the entities involved in relationships Concepts of Database Management, 5th Edition, Pratt & Adamski

  18. ERD 1:N RelationshipDiamond SymbolFigure 6.34 Often 1:N is placed inside the Diamond symbol Concepts of Database Management, 5th Edition, Pratt & Adamski

  19. ERD 1:N Relationship Crow’s Foot Symbol Figure 6.42 Concepts of Database Management, 5th Edition, Pratt & Adamski

  20. One-to-Many Relationship (with Property/Attribute Ovals) Figure 6.37 Concepts of Database Management, 5th Edition, Pratt & Adamski

  21. Many-to-Many RelationshipLetter Designators (m:n)Figure 6.35 Concepts of Database Management, 5th Edition, Pratt & Adamski

  22. N:M Relationship with Attributes Figure 6.38 Concepts of Database Management, 5th Edition, Pratt & Adamski

  23. Composite Entity • Composite entity - an entity that exists to implement a many-to-many relationship • A.k.a., Intersection Table or Association Table • Essentially both an entity and a relationship • Represented in an E-R diagram by a diamond within a rectangle Concepts of Database Management, 5th Edition, Pratt & Adamski

  24. Composite Entity [OrderLine] Figure 6.39 Attribute Ovals Concepts of Database Management, 5th Edition, Pratt & Adamski

  25. Complete ER Diagram Figure 6.40 Concepts of Database Management, 5th Edition, Pratt & Adamski

  26. Maximum & MinimumCardinality • Maximum Cardinality – the most items that can appear on either side of a relationship • Minimum Cardinality – the least number of items that must be included in a relationship • An entity in a relationship with minimum cardinality of zero plays an optional role in the relationship • An entity with a minimum cardinality of one plays a mandatory role in the relationship Concepts of Database Management, 5th Edition, Pratt & Adamski

  27. Representing Cardinality Figure 6.43 Concepts of Database Management, 5th Edition, Pratt & Adamski

  28. Summary • Database design is a two-part process (logical & physical) of determining an appropriate database structure to satisfy a given set of requirements • Entity-relationship (ER) model is a method of representing the structure of a database using an ER diagram (ERD): Examples: ERDx & IDEF1X • A database design is represented in a language called Database Design Language (DDL) • One-to-One [1:1] and One-to-Many [1:N] relationships require attention to primary keys • N:M relationships require Composite tables Concepts of Database Management, 5th Edition, Pratt & Adamski

More Related