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.