Term
| What are the 3 major tasks for transforming a data model into a database design? |
|
Definition
1) Replacing entities and attributes with tables and columns. [easy]
2) Representing relationships and maximum cardinalities by placing foreign keys. [easy]
3) Representing minimum cardinality by defining actions to constrain activities on values of primary and foreign keys. [difficult] |
|
|
Term
What is a Surrogate Key?
What are its advantages? |
|
Definition
A surrogate key is a DBMS-supplied, unique identifier of each row of a table; used as the primary key of relation.
It is created when a row is created, it never changes, and it is destroyed when the row is deleted.
Advantages: They are the best possible primary keys because they are designed to be short, numeric, and fixed.
|
|
|
Term
| When should you use a surrogate key? |
|
Definition
"When the identifier is not short, numberic, or fixed (i.e., EmployeeName, Email), consider using another candidate key as the primary key.
If there are no candidate keys, or if none of them is any better, consider using a surrogate key." p.194 |
|
|
Term
What is a domain constraint?
Give an example. |
|
Definition
A domain is defined as the set of all unique values permitted for an attribute.
A domin constraint limit column values to a particular set of values.
EXAMPLE: EMPLOYEE.EmpCode could be limited to one of these values ['New Hire', 'Hourly', 'Salary', 'Part Time']
|
|
|
Term
What is a range constraint?
Give an example. |
|
Definition
Range Constraints limit values to a particular interval of values.
EXAMPLE: EMPLOYEE.HireDate could be limited to dates between January 1, 3000, and December 31, 3012. |
|
|
Term
What is an intrarelational constraint?
Give an example. |
|
Definition
An intrarelational constraint limits a column's values in comparison with other columns in the same table.
EXAMPLE: The constraint that EMPLOYEE.ReviewDate be at least 3 months after EMPLOYEE.HireDate is an intrarelational constraint. |
|
|
Term
What is an interrelational constraint?
Give an example. |
|
Definition
An interrelational constraint limits a column's values in comparison with other columns in other tables.
Example: In the CUSTOMER table, CUSTOMER.name must not be equal to BAD_CUSTOMER.Name, where BAD_CUSTOMER is a table that contains a list of bad customers |
|
|
Term
Describe 2 ways to represent a 1:1 strong entity relationship.
Give an example other than the one listed in the book. |
|
Definition
1) You can place the primary key of the first table in the second table as a foreign key, or,
2) You can place the primary key of the second table in the first table as a foreign key.
Example:
[image]
|
|
|
Term
Describe how to represent a 1:N strong entity relationship.
Give an example other than the one listed in the book. |
|
Definition
A 1:N relationship between the entities is represented by placing the primary key of the table on the one side, into the table on the many side as the foreign key.
"Place the primary key of the parent in the child as a foreign key"
EXAMPLE:
[image]
|
|
|
Term
Describe how to represent a N:M strong entity relationship.
Give an example other than the one listed in the book. |
|
Definition
To represent a N:M strong entity relationship, create a third table, called an intersection table. It holds only the primary keys of the two tables as foreign keys, and this combination of keys serves as the composite primary key of the intersection table itself.
(Note: Supposed to say "EmployeeNumber," Not EmployeeName in the int table!)
EXAMPLE:
[image] |
|
|
Term
What is an intersection table?
Why is it necessary? |
|
Definition
A table (relation) used to represent a many-to-many relationship. It contains the primary keys of the tables in the relationship.
INT's are necessary because N:M relationships, between strong, entities have no other way to show direct representation.
|
|
|
Term
Describe how to represent a multivalued attribute entity relationship.
Give an example other than one in this chapter. |
|
Definition
A multi-valued attribute is an attribute where for each instance of an entity, there could be more than one value.
Here, a PLANET_EXPRESS_EMPLOYEE has a multivalued composite (Contact, KillAllHumans, etc.) that is represented by the ID-dependent entity INTERESTS. Here the EmployeeNumber is both a Primary Key and a Foreign Key. Like all ID-dependent tables, INTERESTS must have a parent row in PLANET_EXPRESS_EMPLOYEE; however, a PLANET_EXPRESS_EMPLOYEE may not share all of the attributes in INTERESTS.
[image] |
|
|
Term
| Explain the need for each of the actions in Figure 6-28(a). |
|
Definition
|
|
Term
| Explain the need for each of the actions in Figure 6-28(b). |
|
Definition
|
|
Term
| State which of the actions in Figure 6-28 must be applied for M-O relationships, O-M relationships, and M-M relationships. |
|
Definition
|
|
Term
What is a trigger?
How can a trigger be used to enforce required children? |
|
Definition
A trigger is a module of code that is invoked by the DBMS when a specific event occurs.
To enforce required children, you would need to write a trigger to do the required action (such as a cascading update) when a specific event occurs (such as modifying a foreign key in the parent). |
|
|
