Deck 7: Logical Database Design

Question 1

The term that describes a non-key field of a relational table being dependent on only part of the table's key is ____.

A) full functional dependency
B) determinant
C) partial functional dependency
D) transitive dependency
E) minimum dependency

Answer 1

C

Question 2

Converting a many-to-many binary relationship into a relational database from an E-R diagram ____.

A) can be done by placing the unique identifier of one of the tables into the other
B) can be done by creating a single table that combines the attributes of the two entities
C) requires the creation of an additional table beyond the two that represent the two entities in the relationship
D) a & b above
E) a & c above

Answer 2

C

Question 3

Data normalization ____.

A) precedes physical database design
B) follows physical database design
C) is a physical database design technique
D) includes the creation of indexes among its steps
E) uses as its main technique the conversion of E-R diagrams into relational tables

Answer 3

A

Question 4

Data in ____ cannot have partial functional dependencies but can have transitive dependencies.

A) first normal form
B) second normal form
C) third normal form
D) All of the above.
E) None of the above.

Question 5

The goal of logical database design is ____.

A) to fine tune the database's physical database design
B) to convert databases from a hierarchical or network structure to a relational structure
C) to improve performance
D) to create well-structured tables that properly reflect the company's business environment
E) None of the above.

Question 6

The term that describes one non-key field of a table being dependent on another non-key field of the table is ____.

A) full functional dependency
B) determinant
C) partial functional dependency
D) transitive dependency
E) minimum dependency

Question 7

Converting a one-to-one unary relationship into a relational database from an E-R diagram ____.

A) can be done by adding another copy of the unique identifier of the single entity's table into the same table with a different attribute name
B) can be done by splitting the attributes of the single entity into two tables, each with its own copy of the unique identifier
C) requires the creation of an additional table beyond the one that represents the single entity in the relationship
D) a & b above
E) a & c above

Question 8

Converting a one-to-one binary relationship into a relational database from an E-R diagram ____.

A) can be done by placing the unique identifier of one of the tables into the other
B) can be done by creating a single table that combines the attributes of the two entities
C) requires the creation of an additional table beyond the two that represent the two entities in the relationship
D) a & b above
E) a & c above

Question 9

Converting a one-to-many binary relationship into a relational database from an E-R diagram ____.

A) can be done by placing the unique identifier of one of the tables into the other
B) can be done by creating a single table that combines the attributes of the two entities
C) requires the creation of an additional table beyond the two that represent the two entities in the relationship
D) a & b above
E) a & c above

Question 10

The term that describes an attribute whose value establishes the value of another attribute is ____.

A) full functional dependency
B) determinant
C) partial functional dependency
D) transitive dependency
E) minimum dependency

Question 11

Converting a one-to-many unary relationship into a relational database from an E-R diagram ____.

A) can be done by adding another copy of the unique identifier of the single entity's table into the same table with a different attribute name
B) can be done by splitting the attributes of the single entity into two tables, each with its own copy of the unique identifier
C) requires the creation of an additional table beyond the one that represents the single entity in the relationship
D) a & b above
E) a & c above

Question 12

Converting a many-to-many unary relationship into a relational database from an E-R diagram ____.

A) can be done by adding another copy of the unique identifier of the single entity's table into the same table with a different attribute name
B) can be done by splitting the attributes of the single entity into two tables, each with its own copy of the unique identifier
C) requires the creation of an additional table beyond the one that represents the single entity in the relationship
D) a & b above
E) a & c above

Question 13

Data normalization ____.

A) is capable of creating relational tables that are free of data redundancy
B) is a logical database design technique
C) can be used to check the accuracy of the E-R diagrams to relational tables conversion technique
D) All of the above.
E) None of the above.

Question 14

Logical database design ____.

A) is a part of physical database design
B) includes physical database design as one of its elements
C) results in a set of database tables that contain redundant data
D) results in a set of database tables that are optimized for performance
E) None of the above.

Question 15

Converting a ternary relationship into a relational database from an E-R diagram ____.

A) can be done by placing the unique identifier of one of the tables into the others
B) can be done by creating a single table that combines the attributes of the three entities
C) requires the creation of an additional table beyond the three that represent the three entities in the relationship
D) a & b above
E) a & c above

Question 16

A relational table in first normal form ____.

A) is also in second normal form
B) is also in third normal form
C) has no transitive dependencies
D) has no determinants
E) None of the above.

Question 17

Data in ____ cannot have transitive dependencies.

A) first normal form
B) second normal form
C) third normal form
D) All of the above.
E) None of the above.

Question 18

Data in ____ has only a single attribute value in every attribute position.

A) first normal form
B) second normal form
C) third normal form
D) All of the above.
E) None of the above.

Question 19

Placing the unique identifier of one database table in another existing or new table in the database ____.

A) creates a foreign key
B) may indicate the creation of a one-to-many binary relationship
C) may indicate the creation of a many-to-many binary relationship
D) All of the above.
E) None of the above.

Question 20

Which of the following are logical database design techniques?

A) Conversion of E-R diagrams to relational tables.
B) Data normalization.
C) System development.
D) a & b above
E) All of the above.

Question 21

A simple entity box in an E-R diagram is converted into a relation table in a relational database.

Question 22

Which of the following regarding checking the results of one logical database design technique with another is true?

A) Neither the E-R diagram conversion process result nor the data normalization result can be checked with the other process.
B) The E-R diagram conversion process result and the data normalization result can each be checked with the other process.
C) The result of data normalization is checked with the E-R diagram conversion process.
D) The result of the E-R diagram conversion process is checked with data normalization.
E) None of the above.

Question 23

A many-to-many unary relationship in an E-R diagram requires the creation of a total of three tables in a relational database.

Question 24

A one-to-many unary relationship in an E-R diagram can be handled within a single table in the conversion to a relational database design.

Question 25

A many-to-many binary relationship in an E-R diagram requires the creation of a total of three tables in a relational database.

Question 26

A relational table in second normal form ____.

A) is also in first normal form
B) is also in third normal form
C) has no transitive dependencies
D) has no determinants
E) has no functional dependencies

Question 27

In data normalization terminology, an attribute is functionally dependent on its determinant.

Question 28

A one-to-one unary relationship in an E-R diagram requires the creation of a total of three tables in a relational database.

Question 29

In third normal form, the exception to the rule that a non-key attribute cannot define another non-key attribute is ____.

A) if the defining attribute is a candidate key
B) if the defined attribute is a candidate key
C) if the defining attribute is a foreign key
D) if the defined attribute is a foreign key
E) None of the above.

Question 30

A relational table in third normal form ____.

A) is also in first normal form
B) is also in second normal form
C) has no transitive dependencies
D) All of the above.
E) None of the above.

Question 31

A ternary relationship in an E-R diagram requires the creation of a total of three tables in a relational database.

Question 32

Converting a one-to-one binary relationship in an E-R diagram into a single table in a relational database introduces data redundancy.

Question 33

In converting a one-to-one binary relationship in an E-R diagram into two relational tables, the rule is that the unique identifier of the entity on the "many side" of the one-to-many relationship is placed as a foreign key in the table representing the entity on the "one side."

Question 34

Physical database design is the process of modifying the logical database design to improve performance.

Question 35

The two major logical database design techniques are conversion of E-R diagrams to relational tables and data normalization.

Question 36

A one-to-one binary relationship in an E-R diagram can be converted into either one or two tables in a relational database.

Question 37

A functional dependency is a means of expressing that the value of one particular attribute is associated with several values of another attribute.

Question 38

Data normalization is a methodology for organizing attributes into tables so that redundancy among the non-key attributes is eliminated.

Question 39

The input to the data normalization process is a list of the attributes in the database and a list of the functional dependencies among them.

Question 40

Logical database design is the process of modifying the physical database design to improve performance.

Question 41

In first normal form tables every attribute value is atomic, that is no attribute is multi-valued.

Question 42

Multivalued attributes are not permitted in unnormalized data.

Question 43

Converting from first to second normal form involves subdividing tables to remove partial functional dependencies.

Question 44

First normal form tables are free of data redundancy.

Question 45

The data normalization process is known as a "composition process."

Question 46

A transitive dependency occurs when a nonkey attribute determines another nonkey attribute.

Question 47

In the data normalization process, the database's attributes are subdivided into groups that will eventually form the database's tables.

Question 48

Convert the following Central Zoo entity-relationship diagram into a relational database.

Question 49

In first normal form a single, generally multi-attribute, key is chosen that defines or determines all of the other attributes.

Question 50

In third normal form, all foreign keys appear where needed to logically tie together related tables.

Question 51

Converting from first to second normal form involves subdividing tables to remove transitive dependencies.

Question 52

A partial functional dependency occurs when an attribute of a relational table is dependent on only part of the table's key.

Question 53

A group of tables is said to be in a particular normal form if every table in the group is in that normal form.

Question 54

The determinant of an attribute must always be a single attribute.

Question 55

Grand Travel Airlines has to keep track of its flight and airplane history. A flight is uniquely identified by the combination of a flight number and a date. In addition, every flight has an actual departure time and an actual arrival time. Every passenger who has flown on Grand Travel has a unique passenger number plus their name, address, and telephone number. For a particular passenger who has taken a particular flight, the company wants to keep track of the fare that she paid for it and the date that she made the reservation for it. Clearly, a passenger may have taken many flights (he must have taken at least one to be in the database) and every flight has had many passengers on it.
A pilot is identified by a unique pilot (or employee) number, a name, date of birth, and date of hire. A flight on a particular date has exactly one pilot. Each pilot has typically flown many flights but a pilot may be new to the company, is in training, and has not flown any flights, yet.
Each airplane has a unique serial number, a model, manufacturer name, passenger capacity, and year built. A flight on a particular date used one airplane. Each airplane has flown on many flights and dates, but a new airplane may not have been used at all, yet.
Grand Travel also wants to maintain data about its airplanes' maintenance history. A maintenance procedure has a unique procedure number, a procedure name, and the frequency with which it is to be performed on every airplane. A maintenance location has a unique location name, plus an address, telephone number, and manager. Grand Travel wants to keep track of which airplane had which maintenance procedure performed at which location. For each such event it wants to know the date of the event and the duration.
Attributes
Pilot Number
Pilot Name
Date of Birth
Date of Hire
Flight Number
Date (of flight)
Departure Time
Arrival Time
Passenger Number
Passenger Name
Address
Telephone Number (of passenger)
Fare
Reservation Date
Serial Number
Model
Passenger Capacity
Year Built
Manufacturer
Procedure Number
Procedure Name
Frequency
Location Name
Address
Telephone Number (of maintenance location)
Manager
Date (of maintenance)
Duration
For each of the following tables, first write the table's current normal form (as 1NF, 2NF, or 3NF.) Then, for those tables that are currently in 1NF or 2NF, reconstruct them as well-structured 3NF tables. Primary key attributes are underlined. Do not assume any functional dependencies other than those shown.
a. Flight Number, Date (of flight), Passenger Number, Departure Time, Arrival Time, Passenger Name
b. Flight Number, Date (of flight), Passenger Number, Fare, Reservation Date
c. Flight Number, Date (of flight), Serial Number, Departure Time, Arrival Time, Model, Passenger Capacity
d. Pilot Number, Pilot Name, Date of Birth, Date of Hire
e. Serial Number, Procedure Number, Location Name, Date (of maintenance), Duration, Year Built, Manufacturer, Procedure Name, Frequency

Question 56

Central Zoo wants to maintain information about its animals, the enclosures in which they live, and its zookeepers and the services they perform for the animals. In addition, Central Zoo has a program by which people can be sponsor of animals. Central Zoo wants to track its sponsors, their dependents, and associated data.
Each animal has a unique animal number, species, name (Jumbo, Fred, etc.), gender, country of birth, and weight. Enclosures have a unique enclosure number, type (cage, fenced field, etc.), location, size, and date built. An animal can live in only one enclosure. An enclosure can have several animals in it or it can be currently empty. A zookeeper has a unique employee number, employee name, title, and year hired. Some zookeepers supervise other zookeepers. Every animal has been cared for by at least one and generally many zookeepers; each zookeeper has cared for at least one and generally many animals. Each time a zookeeper performs a specific, significant service for an animal the service type, date, and time are recorded.
A sponsor sponsors at least one and possibly several animals. An animal may have several sponsors or none. A sponsor has a unique sponsor number, a name, address, and telephone number. For each animal that a particular sponsor sponsors, the zoo wants to track the annual sponsorship contribution and renewal date. In addition, Central Zoo wants to keep track of each sponsor's dependents, keeping their name, relationship to the sponsor, and date of birth. A sponsor may have several dependents or none. A dependent is associated with exactly one sponsor.
Attributes
Enclosure Number
Type
Location
Size
Date Built
Animal Number
Species
Animal Name
Gender
Country of Birth
Weight
Sponsor Number
Sponsor Name
Address
Telephone
Annual Contribution
Renewal Date
Employee Number
Employee Name
Title
Year Hired
Service Type
Date
Time
Name
Relationship
Date of Birth
Design a well-structured relational database for this Super Baseball League environment using the data normalization technique. Progress from first to second normal form and then from second to third normal form justifying your design decisions at each step based on the rules of data normalization.

Question 57

Convert the following Grand Travel Airline entity-relationship diagram into a relational database.

Question 58

The data normalization process is progressive. If a group of tables is in second normal form it is also in third normal form.