Concepts of Database Management 8th Edition Chapter 6 Solutions
- Slides: 29
Download presentation
Concepts of Database Management Sixth Edition Chapter 6 – Part 2 Database Design 2: Design Method
Objectives • Discuss special issues related to implementing oneto-one relationships and many-to-many relationships involving more than two entities • Discuss entity subtypes and their relationships to nulls • Learn how to avoid potential problems when merging third normal form relations • Examine the entity-relationship model for representing and designing databases Concepts of Database Management 2
One-to-One Relationship Considerations • Simply including the primary key of each table as a foreign key in the other table – No guarantee that the information will match • One solution: create a single table – Workable, but not the best solution • Better solution – Create separate tables for customers and sales reps – Include the primary key of one of them as a foreign key in the other Concepts of Database Management 3
One-to-One Relationship Considerations (continued) FIGURE 6 -23: One-to-one relationship implemented by including the primary key of one table as the foreign key (and alternate key) in the other table Concepts of Database Management 4
Many-to-Many Relationship Considerations • Complex issues arise when more than two entities are related in a many-to-many relationship • Many-to-many relationship: involves multiple entities • Deciding between a single many-to-many relationship and two (or three) many-to-many relationships – Crucial issue: independence Concepts of Database Management 5
Many-to-Many Relationship Considerations (continued) FIGURE 6 -25: Result obtained by splitting the Sales table into three tables Concepts of Database Management 6
Many-to-Many Relationship Considerations (continued) FIGURE 6 -26: Result obtained by joining three tables—the second and third rows are in error! Concepts of Database Management 7
Nulls and Entity Subtypes • Null – Special value – Represents absence of a value in a field – Used when a value is unknown or inapplicable • Splitting tables to avoid use of null values • Entity subtype: table that is a subtype of another table Concepts of Database Management 8
Nulls and Entity Subtypes (continued) FIGURE 6 -27: Student table split to avoid use of null values Concepts of Database Management 9
Nulls and Entity Subtypes (continued) • Subtype called a category in IDEF 1 X terminology • Incomplete category: records that do not fall into the subtype • Complete categories: all records fall into the categories Concepts of Database Management 10
Nulls and Entity Subtypes (continued) FIGURE 6 -29: Entity subtype in an E-R diagram Concepts of Database Management 11
Nulls and Entity Subtypes (continued) FIGURE 6 -32: Two entity subtypes—incomplete categories Concepts of Database Management 12
Nulls and Entity Subtypes (continued) FIGURE 6 -33: Two entity subtypes—complete categories Concepts of Database Management 13
Avoiding Problems with Third Normal Form When Merging Tables • When combining third normal form tables, the result might not be in third normal form • Be cautious when representing user views • Always attempt to determine whether determinants exist and include them in tables Concepts of Database Management 14
The Entity-Relationship Model • An approach to representing data in a database • Entities are drawn as rectangles • Relationships are drawn as diamonds with lines connecting the entities involved in relationships • Composite entity: exists to implement a many-tomany relationship • Existence dependency: existence of one entity depends on the existence of another related entity • Weak entity: depends on another entity for its own existence Concepts of Database Management 15
The Entity-Relationship Model (continued) FIGURE 6 -34: One-to-many relationship Concepts of Database Management 16
The Entity-Relationship Model (continued) FIGURE 6 -35: Many-to-many relationship Concepts of Database Management 17
The Entity-Relationship Model (continued) FIGURE 6 -36: Many-to-many relationship Concepts of Database Management 18
The Entity-Relationship Model (continued) FIGURE 6 -37: One-to-many relationship with attributes added Concepts of Database Management 19
The Entity-Relationship Model (continued) FIGURE 6 -38: Many-to-many relationship with attributes Concepts of Database Management 20
The Entity-Relationship Model (continued) FIGURE 6 -39: Composite entity Concepts of Database Management 21
The Entity-Relationship Model (continued) FIGURE 6 -40: Complete E-R diagram for the Premiere Products database Concepts of Database Management 22
The Entity-Relationship Model (continued) FIGURE 6 -41: E-R diagram with an existence dependency and a weak entity Concepts of Database Management 23
The Entity-Relationship Model (continued) • Cardinality: 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 24
The Entity-Relationship Model (continued) FIGURE 6 -43: E-R diagram that represents cardinality Concepts of Database Management 25
Summary • Database design is a two-part process: informationlevel design (not dependent on a particular DBMS), and physical-level design (appropriate for the particular DBMS being used) • User view: set of necessary requirements to support a particular user's operations • Information-level design steps for each user view: represent the user view as a collection of tables, normalize these tables, represent all keys (primary, alternate, secondary, and foreign), and merge the results into the cumulative design Concepts of Database Management 26
Summary (continued) • Database design is represented in Database Design Language (DBDL) • Designs can be represented visually using entityrelationship (E-R) diagrams • Physical-level design process consists of creating a table for each entity in the DBDL design • Design method presented in this chapter is bottomup • Survey form useful for documenting the information gathered for database design process Concepts of Database Management 27
Summary (continued) • To obtain information from existing documents, list all attributes present in the documents, identify potential functional dependencies, make a tentative list of tables, and use the functional dependencies to refine the list • To implement a one-to-one relationship, include primary key of one table in the other table as a foreign key and indicate the foreign key as an alternate key Concepts of Database Management 28
Summary (continued) • If a table's primary key consists of three (or more) columns, determine whethere are independent relationships between pairs of these columns • If a table contains columns that can be null and the nulls mean that the column is inapplicable for some rows, you can split the table, placing the null column(s) in separate tables • The result of merging third normal form tables may not be in third normal form • Entity-relationship (E-R) model represents the structure of a database using an E-R diagram Concepts of Database Management 29
Concepts of Database Management 8th Edition Chapter 6 Solutions
Source: https://slidetodoc.com/concepts-of-database-management-sixth-edition-chapter-6-2/