Transcript Chapter 3 - La Salle University

3
Tables and Their Characteristics
(continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
8
3
Controlled redundancy
– Makes the relational database work
– Tables within the database share common attributes
that enable the tables to be linked together
• Primary key of one table is referenced by foreign key in
another table
– Multiple occurrences of values in a table are not
redundant when they are required to make the
relationship work
– Redundancy exists only when there is unnecessary
duplication of attribute values
• referential integrity - all foreign key values MUST
match existing tuple
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
10
3
Nulls
• Nulls:
–
–
–
–
No data entry
Not permitted in primary key
Should be avoided in other attributes
Can represent
• An unknown attribute value
• A known, but missing, attribute value
• A “not applicable” condition
– Can create problems when functions such as
COUNT, AVERAGE, and SUM are used
– Can create logical problems when relational tables
are linked
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
13
3
Integrity Rules
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
14
3
Keys
• Consists of one or more attributes that determine
other attributes
• Primary key (PK) is an attribute (or a combination of
attributes) that uniquely identifies any given entity
(row)
• Key’s role is based on determination
– If you know the value of attribute A, you can look up
(determine) the value of attribute B
– E.g. Student # is PK for STUDENT. If you know a
student’s student #, you can look up their name, etc in
the STUDENT table
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
15
Keys
3
• A determines B – knowing the value for A means you can
look up (determine) the value for B.
• The attribute B is functionally dependent on A if A
determines B.
• More technically, but less clearly:
– An attribute B is functionally dependent on an attribute A if,
all rows in the table that agree in value for attribute A must
also agree in value for attribute B
• A Primary key should Functionally Determine all of the
other attributes in a table
• How do you know what FDs are true?
– From Business Rules !!!!
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
16
3
Keys
• An attribute that is (all or) part of a key is
known as a key attribute.
• A multi-attribute key is known as a
composite key.
• If the attribute (B) is functionally
dependent on a key (A) but not on any
subset of that key, the attribute (B) is fully
functionally dependent on (A).
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
17
3
Relationships within the Relational
Database
• 1:M relationship
– Relational modeling ideal
– Should be the norm in any relational database design
• M:N relationships
– Cannot be implemented as such in the relational model
– M:N relationships can be changed into two 1:M
relationships
• 1:1 relationship
– May be rare in any relational database design
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
51
3
The 1:M Relationship
• Relational database norm
• Found in any database environment
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
52
3
The 1:M Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
53
3
The 1:M Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
54
3
The 1:M Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
55
3
The 1:M Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
56
3
The 1:1 Relationship
• One entity can be related to only one other
entity, and vice versa
• Sometimes means that entity components
were not defined properly
• Could indicate that two entities actually
belong in the same table
• As rare as 1:1 relationships should be, certain
conditions absolutely require their use
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
57
3
The 1:1 Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
58
3
The 1:1 Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
59
3
The M:N Relationship
• Can be implemented by breaking it up to
produce a set of 1:M relationships
• Can avoid problems inherent to M:N
relationship by creating a composite entity or
bridge entity
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
60
3
The M:N Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
61
3
The M:N Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
62
3
The M:N Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
63
3
The M:N Relationship
• Do not fit naturally into a RDBMS
• Access doesn’t even allow you to specify them
• Can be implemented by breaking it up to produce
a pair of 1:M relationships
• create a composite (or bridge) entity – with
relationships to each of the initially identified
entities
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
64
3
Linking Table
• Implementation of a composite entity
• Yields required M:N to 1:M conversion
• Composite entity table must contain at least
the primary keys of original tables
• Linking table contains multiple occurrences of
the foreign key values
• Additional attributes may be assigned as
needed
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
65
3
The M:N Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
66
3
The M:N Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
67
3
The M:N Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
68
3
The M:N Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
69
3
Codd’s Relational Database Rules
• In 1985, Codd published a list of 12 rules to
define a relational database system
• The reason was the concern that many
vendors were marketing products as
“relational” even though those products did
not meet minimum relational standards
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
75