concepts of database management, fifth edition chapter 6: database design 2: design methodology
TRANSCRIPT
![Page 1: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/1.jpg)
Concepts of Database Concepts of Database Management, Fifth Management, Fifth
EditionEdition
Chapter 6: Chapter 6: Database Design 2:Database Design 2:Design MethodologyDesign Methodology
![Page 2: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/2.jpg)
2Concepts of Database Management, 5th Edition
Objectives Discuss the general process and goals of
database design
Define user views and explain their function
Define database design language and use it to document database designs
Create an entity-relationship diagram to visually represent a database design
Present a methodology for database design at the information level and view examples illustrating this methodology
![Page 3: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/3.jpg)
3Concepts of Database Management, 5th Edition
Objectives
Explain the physical-level design process
Discuss top-down and bottom-up approaches to database design and examine the advantages and disadvantages of both methods
Use a survey form to obtain information from users prior to beginning the database design process
Review existing documents to obtain information prior to beginning the database design
![Page 4: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/4.jpg)
4Concepts of Database Management, 5th Edition
Objectives Discuss special issues related to implementing
one-to-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
![Page 5: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/5.jpg)
5Concepts of Database Management, 5th Edition
Database Design Information-level Design – building a database that
satisfies the organization’s requirements as cleanly as possible
Physical-level design – designers transform the information-level design into a design for the specific DBMS used by the organization
User Views – requirements necessary to support a particular user’s operations
Cumulative design – supports all user views encountered so far in the design process
![Page 6: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/6.jpg)
6Concepts of Database Management, 5th Edition
Information-Level Design Methodology
Information-level Design Methodology
Represent user view as collection of tables
Normalize these tables
Identify all keys
Merge the result into design
![Page 7: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/7.jpg)
7Concepts of Database Management, 5th Edition
Represent User View as Collection of Tables
Step 1: Determine entities involved and create separate table for each type
Step 2: Determine primary key for each table
Step 3: Determine properties for each entities
Step 4: Determine relationships among entities
![Page 8: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/8.jpg)
8Concepts of Database Management, 5th Edition
Types of Relationships
One-to-Many Relationships – created by including the primary key of the “one” table as a foreign key in the “many” table
Many-to-Many Relationships – created with a new table whose primary key is the combination of the primary keys of the original tables
One-to-One Relationship – created with a single relationship from one record in a table to a single record in another table
![Page 9: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/9.jpg)
9Concepts of Database Management, 5th Edition
Normalize the Tables
Represent all keys
Primary, alternate, secondary, foreign
Database Design Language (DBDL)
Mechanism for representing tables and keys
DBDL Example
Figure 6.1
![Page 10: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/10.jpg)
10Concepts of Database Management, 5th Edition
Types of Primary Keys Three types of primary keys used in database
design
1. Natural key (logical key or intelligent key) – consists of a column that uniquely identifies an entity
2. Artificial key – column created for an entity to serve solely as the primary key; visible to users
3. Surrogate key (synthetic key) – system-generated primary key usually hidden from users
![Page 11: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/11.jpg)
11Concepts of Database Management, 5th Edition
DBDL Notation
Table name followed by columns in parentheses
Primary key column(s) underlined
AK identifies alternate keys
SK identifies secondary keys
FK identifies foreign keys
![Page 12: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/12.jpg)
12Concepts of Database Management, 5th Edition
Entity-Relationship Diagrams
Diagram that visually represents database structure
Rectangle represents each entity in the E-R diagram
Primary key for each entity appears above the line in the rectangle for each entry
![Page 13: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/13.jpg)
13Concepts of Database Management, 5th Edition
Entity-Relationship Diagrams
Other columns that comprise each entity appear below the line within each rectangle
The letters AK, SK, and FK appear in parentheses following the alternate key, secondary key, and foreign key respectively
For each foreign key, there is a line leading from the rectangle that corresponds to the table being identified to the rectangle that corresponds to the table containing the foreign key
![Page 14: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/14.jpg)
14Concepts of Database Management, 5th Edition
Figure 6.2: Entity-Relationship Diagrams
![Page 15: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/15.jpg)
15Concepts of Database Management, 5th Edition
Merge the Result into the Design
As soon as Steps 1 through 3 for a given user view have been completed, the results can be merged into the cumulative design
If the working view is the first user view, the cumulative design will be identical to the design for the first user
Otherwise, all the tables for this user with those that are currently in the cumulative design
![Page 16: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/16.jpg)
16Concepts of Database Management, 5th Edition
Merge the Result into the Design (con’t)
Next, you combine tables that have the same primary key to form a new table
New table has the same primary key as those tables you have combined
New table also contains all the columns from both tables
In the case of duplicate columns, you remove all but one copy of the column
![Page 17: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/17.jpg)
17Concepts of Database Management, 5th Edition
Figure 6.4:User View Examples
View #1: Sales Rep View
Rep (RepNum, LastName, FirstName, Street, City, State, Zip, Commission, Rate)
![Page 18: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/18.jpg)
18Concepts of Database Management, 5th Edition
View #2: Customer View
Figure 6.5:User View Examples
![Page 19: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/19.jpg)
19Concepts of Database Management, 5th Edition
Entities
Independent entity - an entity that does not require a relationship to another entity for identification
Dependent entity - an entity that does require a relationship to another entity for identification
![Page 20: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/20.jpg)
20Concepts of Database Management, 5th Edition
Physical-Level Design
Undertaken after information-level design completion
Most DBMSs support primary, candidate, secondary, and foreign keys
DB programmers must include logic to ensure the uniqueness of primary keys and enforce other conditions
![Page 21: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/21.jpg)
21Concepts of Database Management, 5th Edition
Top-Down vs. Bottom-Up
Bottom-up
Design starts at low level
Specific user requirements drive design process
Top-down
Begins with general database that models overall enterprise
Refines the model until design is achieved
![Page 22: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/22.jpg)
22Concepts of Database Management, 5th Edition
Survey Form
Used to collect information from users
Must contain particular elements
Entity information
Attribute information
Relationships
Functional dependencies
Processing information
![Page 23: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/23.jpg)
23Concepts of Database Management, 5th Edition
Existing Documents
Aid in collecting user requirements
Collect information similar to that collected with survey forms
Entity information
Attribute information
Relationships
Functional dependencies
Processing information
![Page 24: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/24.jpg)
24Concepts of Database Management, 5th Edition
Figure 6.14: Example Invoice
![Page 25: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/25.jpg)
25Concepts of Database Management, 5th Edition
Figure 6.15: List of Possible Attributes
![Page 26: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/26.jpg)
26Concepts of Database Management, 5th Edition
Figure 6.16:Tentative List of Functional Dependencies
![Page 27: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/27.jpg)
27Concepts of Database Management, 5th Edition
Figure 6.17:Revised List of
Functional Dependencies
![Page 28: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/28.jpg)
28Concepts of Database Management, 5th Edition
Figure 6.18:Tentative List of Entities
![Page 29: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/29.jpg)
29Concepts of Database Management, 5th Edition
Figure 6.19:Expanded List of Entities
![Page 30: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/30.jpg)
30Concepts of Database Management, 5th Edition
One-to-One Relationship Considerations
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
![Page 31: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/31.jpg)
31Concepts of Database Management, 5th Edition
One-to-One Relationship Considerations (con’t)
One possible solution is to create a single table
Although workable, two features are not particularly attractive:
1. Combines columns of two different entities into a single table
2. If possible for one entity to exist without the other
![Page 32: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/32.jpg)
32Concepts of Database Management, 5th Edition
One-to-One 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
![Page 33: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/33.jpg)
33Concepts of Database Management, 5th Edition
Figure 6.20:1:1 Relationship Considerations
Include primary key of each table as foreign key in the other
![Page 34: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/34.jpg)
34Concepts of Database Management, 5th Edition
Figure 6.21:1:1 Relationship Considerations
Implementation when information does not match
![Page 35: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/35.jpg)
35Concepts of Database Management, 5th Edition
Figure 6.22:1:1 Relationship Considerations
Implemented in a single table
![Page 36: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/36.jpg)
36Concepts of Database Management, 5th Edition
Figure 6.23:
1:1 Relationship Considerations
1:1 relationship implemented by including primary key of one table as foreign key (and alternative key) in the other
![Page 37: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/37.jpg)
37Concepts of Database Management, 5th Edition
Many-to-Many Relationship Considerations
Complex issues arise when more than two entities are related in a many-to-many relationship
Many-to-many-to-many relationship – involves multiple entities
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
![Page 38: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/38.jpg)
38Concepts of Database Management, 5th Edition
Figure 6.24: M:M Relationship Considerations
Sample Sales Data
![Page 39: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/39.jpg)
39Concepts of Database Management, 5th Edition
Figure 6.25:M:M Relationship Considerations
Result obtained by splitting Sales table into three tables
![Page 40: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/40.jpg)
40Concepts of Database Management, 5th Edition
Figure 6.26:M:M Relationship Considerations
Result obtained by joining three tables--2 rows are in error. Must be converted to 4NF
![Page 41: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/41.jpg)
41Concepts of Database Management, 5th Edition
Nulls
Null - a special value
Null - actually represents the absence of a value in a field
Nulls - used when a value is either unknown or inapplicable
![Page 42: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/42.jpg)
42Concepts of Database Management, 5th Edition
Nulls are absence of values
Figure 6.27: Table SplitTo Avoid Nulls
![Page 43: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/43.jpg)
43Concepts of Database Management, 5th Edition
Figure 6.28: Sample DBDL
![Page 44: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/44.jpg)
44Concepts of Database Management, 5th Edition
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
![Page 45: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/45.jpg)
45Concepts of Database Management, 5th Edition
Figure 6.29: Entity Subtypes
![Page 46: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/46.jpg)
46Concepts of Database Management, 5th Edition
Figure 6.32:Two Entity Subtypes—Incomplete Categories
![Page 47: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/47.jpg)
47Concepts of Database Management, 5th Edition
Figure 6.33: Two Entity Subtypes—Complete Categories
![Page 48: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/48.jpg)
48Concepts of Database Management, 5th Edition
Avoiding Problems with 3NF When Merging Tables
When combining third normal form tables, the result need not be in third normal form
To avoid the problem of creating a table that is not in third normal form, be cautious when representing user views
If you always attempt to determine whether determinants exist and include them in the tables, the problem is often avoided
![Page 49: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/49.jpg)
49Concepts of Database Management, 5th Edition
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
![Page 50: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/50.jpg)
50Concepts of Database Management, 5th Edition
Figure 6.34:One-to-many relationship
![Page 51: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/51.jpg)
51Concepts of Database Management, 5th Edition
Figure 6.35:Many-to-Many Relationship
![Page 52: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/52.jpg)
52Concepts of Database Management, 5th Edition
Figure 6.36:Many-to-Many Relationship
![Page 53: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/53.jpg)
53Concepts of Database Management, 5th Edition
Figure 6.37:One-to-Many Relationship
![Page 54: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/54.jpg)
54Concepts of Database Management, 5th Edition
Figure 6.38: Many-to-Many Relationship with Attributes
![Page 55: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/55.jpg)
55Concepts of Database Management, 5th Edition
Composite Entity
Composite entity - an entity that exists to implement a many-to-many relationship
Essentially both an entity and a relationship
Represented in an E-R diagram by a diamond within a rectangle
![Page 56: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/56.jpg)
56Concepts of Database Management, 5th Edition
Figure 6.39:Composite Entity
![Page 57: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/57.jpg)
57Concepts of Database Management, 5th Edition
Figure 6.40: Complete E-R Diagram
![Page 58: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/58.jpg)
58Concepts of Database Management, 5th Edition
E-R Diagram with an Existence Dependency and Weak Entity
Existence dependency - when the existence of one entity depends on the existence of another related entity
Indicate an existence dependency by placing an “E” in the relationship diamond
Weak entity - an entity that depends on another entity for its own existence
A double rectangle encloses a weak entity
![Page 59: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/59.jpg)
59Concepts of Database Management, 5th Edition
Figure 6.41: E-R Diagram with an Existence Dependency and Weak Entity
![Page 60: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/60.jpg)
60Concepts of Database Management, 5th Edition
Cardinality
Cardinality - the 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
![Page 61: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/61.jpg)
61Concepts of Database Management, 5th Edition
Figure 6.43: E-R Diagram that Represents Cardinality
![Page 62: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/62.jpg)
62Concepts of Database Management, 5th Edition
Summary Database design is a two-part process of
determining an appropriate database structure to satisfy a given set of requirements
A user view is the set of necessary requirements to support a particular user’s operations
A database design is represented in a language called Database Design Language (DBDL)
Designs can be represented pictorially using entity-relationship (E-R) diagrams
![Page 63: Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f265503460f94c3c9a4/html5/thumbnails/63.jpg)
63Concepts of Database Management, 5th Edition
Summary
Advantages to both top-down and bottom-up approaches
Survey form is useful for documenting the information gathered for database design process
One-to-One and One-to-Many relationships require attention to primary keys
Entity-relationship (E-R) model is a method of representing the structure of a database using an E-R diagram