Database Design 2:

1
Chapter 6

• Database Design 2
• Design Methodology

2
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

3
Objectives

• Explain the physical-level design process
• Examine some alternative approaches to
  entity-relationship diagrams
• 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

4
Objectives

• Review existing documents to obtain information
  from users prior to beginning the database design
  process
• 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

5
Database Design

• User Views
• Requirements necessary to support a particular
  users operations
• Information-level Design Methodology
• Represent user view as collection of tables
• Normalize these tables
• Identify all keys
• Merge the result into design

6
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

7
Normalize the Tables

• Represent all keys
• Primary, alternate, secondary, foreign
• Database Design Language (DBDL)
• Mechanism for representing tables and keys

8
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

9
Entity-Relationship Diagrams Figure 6.2
10
Merge the Result into the Design Figure 6.3
11
User View Examples
View 1 Sales Rep View
Rep (RepNum, LastName, FirstName, Street, City,
State, Zip, Commission, Rate)
12
User View Examples (cont.)Figure 6.5
View 2 Customer View
13
User View Examples (cont.)Figure 6.6
View 3 Part View
14
User View Examples (cont.)Figure 6.8
View 4 Order View
15
Second Set User View Examples
View 1 Publisher View
Publisher (PublisherCode, PublisherName, City)
SK PublisherName
16
Second Set User View Examples (cont.)
View 2 Branch View
Publisher (PublisherCode, PublisherName, City)
SK PublisherName Branch (BranchNum,
BranchName, BranchLocation, NumEmployees)
SK BranchName
17
Second Set User View Examples (cont.)Figure
6.11
View 3 Book View
18
Second Set User View Examples (cont.)Figure
6.12
View 4 Author View
19
Second Set User View Examples (cont.)Figure
6.13
View 5 and View 6 Inventory View
20
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

21
ERD Relationship AlternativeFigure 6.14
22
Symbols for Columns Figure 6.15
23
Composite Entity Figure 6.16
24
Crows Foot Symbol Figure 6.17
25
Representing Cardinality Figure 6.18
26
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

27
Survey Form

• Used to collect information from users
• Must contain particular elements
• Entity information
• Attribute information
• Relationships
• Functional dependencies
• Processing information

28
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

29
11 Relationship Considerations Figure 6.25
Include primary key of each table as foreign
key in the other
30
11 Relationship Considerations Figure 6.26
Implementation when information does not match.
31
11 Relationship Considerations Figure 6.27
Implemented in a single table.
32
11 Relationship Considerations Figure 6.28
11 relationship implemented by including primary
key of one table as foreign key (and alternative
key) in the other.
33
MM Relationship Considerations Figure 6.29
Sample Sales Data
34
MM Relationship Considerations Figure 6.30
Result obtained by splitting Sales table into
three tables
35
MM Relationship Considerations Figure 6.31
Result obtained by joining three tables--2 rows
are in error. Must be converted to 4NF.
36
Table Split to Avoid Nulls Figure 6.32
Nulls are absence of values
37
Entity Subtypes Figure 6.34
38
Student Table Split to Avoid Nulls Figure 6.36
39
Two Entity SubtypesIncomplete Categories
Figure 6.37
40
Two Entity SubtypesComplete Categories Figure
6.38