Chapter 6
Interrelation constraint
- Column values are limited by comparison to values in other columns in other tables
How do you transform a data model into a database design?
1.Replace entities and attributes with tables and columns 2.Represent relationships and maximum cardinalities by placing foreign keys 3.Represent minimum cardinality by constraining values of primary and foreign keys
Which of the following is not true about representing subtypes in a relational database design?
All attributes of the supertype are added to the subtype relations.
For the M-O (parent mandatory, child optional) case, what action(s) should be taken to ensure minimum cardinality is maintained?
Both Define referential integrity constraint from child to parent and Make foreign key NOT NULL are correct
Intrarelation constraint
Column values are limited by comparison to values in other columns in the same table
Domain constraint
Column values must be in a given set of specific values
Range constraint
Column values must be within a given range of values
Which of the following is not true about surrogate keys?
They are non-unique within a table.
Which of the following is not true of recursive relationships?
When the relationship is 1:N, a new table must be defined to represent the relationship.
The binary constraint must not indicates that ________.
a binary relationship includes value combinations that must not occur in a ternary relationship
Many-to-many relationships are represented by ________.
an intersection table which has 1:N relationships with the two tableS
Null status
status indicates whether or not the value of the column can be NULL Primary Keys can not be null but alternate keys can
1:1 Strong Entity Relationships
•Place the PK of one table in the other table. •Enforce maximum cardinality by defining the FK as unique (or as an alternate key).
association table
-Has all the characteristics of an intersection table -PLUS it has one or more columns of attributes specific to the associations of the other two entities
•An intersection table
-Holds the relationships between two strong entities in an N:M relationship -Contains only the primary keys of the two entities: •As a composite primary key •As foreign keys
Ternary and higher-order relationships may be constrained by the binary relationship that comprise them-
MUST constraint—requires that one entity must be combined with another entity in the ternary (or higher-order) relationship -MUST NOT constraint—requires that certain combinations of two entities are not allowed to occur in the ternary (or higher-order) relationship -MUST COVER constraint—a binary relationship specifies all combinations of two entities that must appear in the ternary (or higher-order) relationship
A referential integrity constraint policy that insures that all rows containing a foreign key value in a table are eliminated from the table when the row containing the corresponding primary key value in a parent table is eliminated from the database is called ________.
cascading deletes
A referential integrity constraint policy that insures that foreign key values in a table are correctly maintained when there is a change to the primary key value in the parent table is called ________.
cascading updates
The first step in transforming an extended E-R model into a relational database design is to ________.
create a table for each entity
T/F Data types are consistent across all DBMS products.
false
Which of the following is not true of a MUST constraint?
it can be expressed directly in a relational model.
M-M relationship
parent mandatory and child mandatory
M-O relationship
parent mandatory and child optional
O-M relationship
parent optional and child mandatory
O-O relationship
parent optional and child optional
dashed line
relationship between two strong entities
solid line
relationships between ID-dependent entities
In relational database design, ID-dependent entities are not used to ________.
represent relationships where the child identifier does not include the key of the parent
Four uses for ID-dependent entities include all of the following except ________.
representing intersection relationships
In many-to-many relationships between strong entities in a relational database design, which of the following is not true?
the intersection table is ID-dependent on one of the parents
When representing a 1:1 relationship in a relational database design, ________.
the key of either table may be placed as a foreign key into the other
To represent a one-to-many relationship in a relational database design, ________.
the key of the parent is placed as a foreign key into the child
default value
the value supplied by the DBMS when a new row is created
T/F A data constraint is a limitation on data values.
true
T/F A surrogate key is appropriate when the primary key of a table contains a lengthy text field.
true
T/F In a relational database design, all relationships are expressed by creating a foreign key.
true
T/F The ideal primary key is short, numeric, and fixed.
true
N:M Strong Entity Relationships
•Create an intersection table that stores data about the corresponding rows from each entity. •The intersection table consists only of the primary keys of each table which form a composite primary key. •Each table's primary key becomes a foreign key linking back to the intersection table.
1:N Strong Entity Relationships
•Place the primary key of the table on the one side of the relationship (parent) into the table on the many side of the relationship (child) as the foreign key.