Section 11 Database Design

INTRODUCTION TO RELATIONAL DATABASE CONCEPTS
Column integrity refers to
Columns always containing values consistent with the defined data format
          
One or more columns in a primary key can be null. True or False?
False
          
The explanation below is an example of what constraint type:
If the value in the balance column of the ACCOUNTS table is below 100, we must send a letter to the account owner which will require extra programming to enforce.
User-defined integrity
          
Identify all of the correct statements that complete this sentence: A primary key is ...
A single column that uniquely identifies each row in a table
A set of columns that uniquely identifies each row in a table
A set of columns and keys in a single table that uniquely identifies each row in a single table
          
The explanation below defines which constraint type:
A primary key must be unique, and no part of the primary key can be null.
Entity integrity
          
A foreign key always refers to a primary key in the same table. True or False?
False
          
The explanation below defines which constraint type:
A column must contain only values consistent with the defined data format of the column
Column integrity
          
Foreign keys cannot be null when
It is part of a primary key
          
The explanation below is an example of what constraint type:
The value in the dept_no column of the EMPLOYEES table must match a value in the dept_no column in the
DEPARTMENTS table.
Referential integrity
          
A table does not have to have a primary key. True or False?
True


BASIC MAPPING: THE TRANSFORMATION PROCESS
In an Oracle database, why would the following table name not be allowed 'EMPLOYEE JOBS'?
You cannot have spaces between words in a table name
          
In a physical data model, an entity becomes a _____________.
Table
          
The transformation from an ER diagram to a physical design involves changing terminology. Entities in the ER diagram become __________ , and attributes become ____________.
Tables, Columns
          
In a physical data model, a relationship is represented as a:
Foreign Key
          
Attributes become columns in a database table. True or False?
True
          
The transformation from an ER diagram to a physical design involves changing terminology. Relationships in the ER diagram become __________ , and primary unique identifiers become ____________.
Foreign keys, primary keys
          
Why would this table name NOT work in an Oracle database?
2007_EMPLOYEES
Table names must start with an alphabetic character
          

RELATIONSHIP MAPPING
Relationships on an ERD can only be transformed into UIDs in the physical model?
False       

One-to-One relationships are transformed into Foreign Keys in the tables created at either end of that relationship?
False
          
One-to-Many Optional to Mandatory becomes a _______________ on the Master table.
Nothing (There are no new columns created on the Master table)
          
What do you create when you transform a many to many relationship from your ER diagram into a physical design?
Intersection table
          
A barrred Relationship will result in a Foreign Key column that also is part of:
The Primary Key
          
Two entities A and B have an optional (A) to Mandatory (B) One-to-One relationship. When they are transformed, the Foreign Key(s) is placed on:
The table B
          

SUBTYPE MAPPING
When mapping supertypes, relationships at the supertype level transform as usual. Relationships at subtype level are implemented as foreign keys, but the foreign key columns all become optional. True or False?  
True
          
When translating an arc relationship to a physical design, you must turn the arc relationships into foreign keys. What additional step must you take with the created foreign keys to ensure the exclusivity principle of arc relationships? (Assume that you are implementing an Exclusive Design)
Make all relationships optional
Create an additional check constraint to verify that one foreign key is populated and the others are not
          
Which of the following are reasons you should consider when using a Subtype Implementation?
When the common access paths for the subtypes are different.
Most of the relationships are at the subtype level
          
The "Arc Implementation" is a synonym for what type of implementation?
Supertype and Subtype Implementation