Databases Design Final Exam

1.  When all attributes are single-valued, the database model is said to conform to:
1st Normal Form (*)
2.  To convert an entity with a multi valued attribute to 1st Normal Form, we create an additional entity and relate it to the original entity with a 1:1 relationship. True or False?
False (*)
3.  When data is stored in more than one place in a database, the database violates the rules of ___________.
Normalization (*)
4.  An entity ORDER has the attributes Order ID, Order Date, Product id, Customer ID. This entity is in 1st Normal Form. True or False?

False (*)
5.  Which of the following would be suitable UIDs for the entity EMPLOYEE: (Choose Two)
(Choose all correct answers)
Social Security Number (*)
Employee ID (*)
6.  A unique identifier can only be made up of one attribute. True or False?
False (*)
7.  A UID can be made up from the following: (Choose Two)
(Choose all correct answers)
Attributes (*)
Relationships (*)
8.  An entity can only have one Primary UID. True or False?

True (*)
9.  What is the rule of Second Normal Form?

All non-UID attributes must be dependent upon the entire UID. (*)
10.  To resolve a 2nd Normal Form violation, we:

Move the attribute that violates 2nd Normal Form to a new entity with a relationship to the original entity. (*)
11.  Examine the following entity and decide which attribute breaks the 2nd Normal Form rule:
ENTITY: RECEIPT
ATTRIBUTES:
   #CUSTOMER ID
   #STORE ID
   STORE LOCATION
   DATE

STORE LOCATION (*)
12.  Examine the following Entity and decide which rule of Normal Form is being violated:
ENTITY: CLIENT ORDER
ATTRIBUTES:
    # CLIENT ID
    # ORDER ID
    FIRST NAME
    LAST NAME
    ORDER DATE
    CITY
    ZIP CODE
2nd Normal Form. (*)
13.  Examine the following Entity and decide which rule of Normal Form is being violated:
ENTITY: CLIENT
ATTRIBUTES:
    # CLIENT ID
    FIRST NAME
    LAST NAME
    ORDER ID
    STREET
    ZIP CODE
1st Normal Form. (*)
14.  As a database designer, you do not need to worry about where in the datamodel you store a particular attribute; as long as you get it onto the ERD, your job is done. True or False?
False (*)
15.  When any attribute in an entity is dependent on any other non-UID attribute in that entity, this is known as:
Transitive dependency (*)
16.  Cascading UIDs are a feature often found in what type of Relationship?
Heirarchical Relationship (*)
17.  A particular problem may be solved using either a Recursive Relationship or a Hierarchical Relationship, though not at the same time. True or False?
True (*)
18.  A Hierarchical relationship is a series of relationships that reflect entities organized into successive levels. True or False?

True (*)
19.  A relationship between an entity and itself is called a/an:

Recursive Relationship (*)
20.  A Recursive Relationship is represented on an ERD by a/an:
Pig's Ear (*)
21.  Which of the following would best be represented by an arc?
DELIVERY ADDRESS (Home, Office) (*)

22.  Which of the following can be added to a relationship?
An arc can be assigned (*)
23.  To visually represent exclusivity between two or more relationships in an ERD you would most likely use an ________.
Arc (*)
24.  Every business has restrictions on which attribute values and which relationships are allowed. These are known as:
Constraints. (*)

Section 8
25.  When a relationship may or may not be transferable, depending on time, this is know as a/an:
Conditional Non-transferable Relationship. (*)
26.  All systems must have an entity called WEEK with a holiday attribute so that you know when to give employees a holiday. True or False?
False (*)
27.  You must make sure all entities of a proposed system can fit onto one diagram. It is not allowed to break up a data model into more than one diagram. True or False?

False (*)

28.  Which of the following statements are true for ERD's to enhance their readability. (Choose Two)

(Choose all correct answers)
Avoid crossing one relationship line with another. (*)
It is OK to break down a large ERD into subsets of the overall picture. By doing so, you end up with more than one ERD that, taken together, documents the entire system. (*)
29.  You are doing a data model for a computer sales company where the price of postage depends upon the day of the week that goods are shipped. So shipping is more expensive if the customer wants a delivery to take place on a Saturday or Sunday. What would be the best way to model this?
Use a Delivery Day entity, which holds prices against week days, and ensure the we also have an attribute for the Requested Delivery Day in the Order Entity. (*)
30.  What is the function of logging or journaling in conceptual data models?

Allows you to track the history of attribute values, relationships, and/or entire entities (*)
31.  Modeling historical data can produce a unique identifier that includes a date. True or False?
True (*)
32.  Which of the following scenarios should be modeled so that historical data is kept? (Choose two)
(Choose all correct answers)
CUSTOMER and PAYMENTS (*)
CUSTOMER and ORDERS (*)
Section 9
33.  When mapping supertypes, relationships at the supertype level transform as usual. Relationships at the subtype level are implemented as foreign keys, but the foreign key columns all become optional. True or False?
True (*)
34.  Which of the following is a valid reason for considering a Subtype Implementation?

Business functionality, business rules, access paths, and frequency of access are all very different between the subtypes. (*)
35.  In a physical data model, an attribute becomes a _____________.
Column (*)
36.  Attributes become columns in a database table. True or False?
True (*)
37.  In a physical data model, a relationship is represented as a combination of: (Choose Two)
(Choose all correct answers)
Primary Key or Unique Key (*)
Foreign Key (*)
38.  The Physical model is created by transforming which of the following models?
Conceptual (*)
39.  Column integrity refers to
Columns always containing values consistent with the defined data format (*)
40.  The explanation below is an example of which 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 (*)
41.  The explanation below is an example of which 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 (*)
42.  A foreign key always refers to a primary key in the same table. True or False?

False (*)
43.  The explanation below is an example of which constraint type?
A primary key must be unique, and no part of the primary key can be null.

Entity integrity (*)
44.  An Arc is transformed to the physical model by adding a foeign Key for every relationship in the Arc. True or False?
True (*)
45.  One-to-Many Optional to Mandatory becomes a _______________ on the Master table.
Optional Foreign Key (*)
46.  It is possible to implement non-transferability via a simple Foreign Key Relationship. True or False?

False (*)
47.  One-to-One relationships are transformed into Foreign Keys in the tables created at either end of that relationship. True or False?
False (*)
48.  One-to-One relationships are transformed into Check Constraints in the tables created at either end of that relationship. True or False?
False (*)
Section 10
49.  System Documentation is developed right at the end once the system has gone live and users have been using it for a little while. You are more likely to get it correct that way. True or False?
False (*)
50.  During which phases of the System Development Life Cycle would you test the system before rolling it out to the users?
Transition and Production (*)