Transcript Pratt and Adamski Chapter 6

6
Chapter 6
Database Design 2:
Design Methodology
Spring 2006
Concepts of Database Management, 5th Edition, Pratt & Adamski
1
Exercise #4






6
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
2
One-to-One [1:1] Relationship
Considerations
6
 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
3
1:1 Relationship Considerations
(con’t)

6
One possible solution is to create a single
table. OK?

Although workable, two features are not
particularly attractive:
1.
Combines columns of two different entities into a
single table. Violates “single theme” table notion.
2.
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
4
1:1 Relationship
Considerations (con’t)

6
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
5
1:1 Relationship Considerations
Figure 6.20 (6.25) (4th Ed.)
6
Include primary key of each table as
foreign key in the other. OVERKILL!
Concepts of Database Management, 5th Edition, Pratt & Adamski
6
1:1 Relationship Considerations
Figure 6.22
6
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
7
1:1 Relationship Considerations
Figure 6.23
6
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
8
Many-to-Many [N:M]
Relationship Considerations
6

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) manyto-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
9
6
Future N:M:P slide
 Birth
[Mother, Father, Child] vs. …
Mom
Dad
Baby
Concepts of Database Management, 5th Edition, Pratt & Adamski
10
6
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
11
Table Split to Avoid Null Values
Figure 6.27
6
Nulls are absence
of values
Concepts of Database Management, 5th Edition, Pratt & Adamski
12
6
Entity Subtypes
subtype – table that is a subtype of
another table
 Entity
category – records that do not fall
into the subtype
 Incomplete
 Complete
categories – all records fall into the
categories
Concepts of Database Management, 5th Edition, Pratt & Adamski
13
Entity Subtypes
Incomplete Categories
Figure 6.29
Concepts of Database Management, 5th Edition, Pratt & Adamski
6
14
Student Table Split into Subtypes
Helps to Avoid Nulls
Figure 6.31
Concepts of Database Management, 5th Edition, Pratt & Adamski
6
15
Two Entity Subtypes
Complete Categories
Figure 6.33
Concepts of Database Management, 5th Edition, Pratt & Adamski
6
16
Entity-Relationship Model
[Chen: Extended ERDs]
6
 Various
ERD methodologies use different
symbols to represent data; non-standardized
notation
 E.g.,
Entities are drawn as rectangles
 Relationships
are drawn as diamonds with
lines connecting the entities involved in
relationships
Concepts of Database Management, 5th Edition, Pratt & Adamski
17
ERD 1:N Relationship
Diamond Symbol
Figure 6.34
6
Often 1:N is
placed inside the
Diamond symbol
Concepts of Database Management, 5th Edition, Pratt & Adamski
18
ERD 1:N Relationship
Crow’s Foot Symbol
Figure 6.42
Concepts of Database Management, 5th Edition, Pratt & Adamski
6
19
One-to-Many Relationship
(with Property/Attribute Ovals)
Figure 6.37
Concepts of Database Management, 5th Edition, Pratt & Adamski
6
20
Many-to-Many Relationship
Letter Designators (m:n)
Figure 6.35
Concepts of Database Management, 5th Edition, Pratt & Adamski
6
21
N:M Relationship with Attributes
Figure 6.38
Concepts of Database Management, 5th Edition, Pratt & Adamski
6
22
6
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
23
Composite Entity [OrderLine]
Figure 6.39
6
Attribute Ovals
Concepts of Database Management, 5th Edition, Pratt & Adamski
24
Complete ER Diagram
Figure 6.40
Concepts of Database Management, 5th Edition, Pratt & Adamski
6
25
Maximum & Minimum
Cardinality
6
Cardinality – the most items that
can appear on either side of a relationship
 Maximum
Cardinality – the least number of
items that must be included in a relationship
 Minimum
 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
26
Representing Cardinality
Figure 6.43
Concepts of Database Management, 5th Edition, Pratt & Adamski
6
27
6
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
28