Exam 7: Logical Database Design

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

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

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

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

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

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

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

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

A relational table in second normal form ____.

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

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 Functional Dependencies Pilot Number $\longrightarrow$ Pilot Name Pilot Number $\longrightarrow$ Date of Birth Pilot Number $\longrightarrow$ Date of Hire Flight Number, Date (of flight) $\longrightarrow$ Departure Time Flight Number, Date (of flight) $\longrightarrow$ Arrival Time Flight Number, Date (of flight) $\longrightarrow$ Pilot Number Flight Number, Date (of flight) $\longrightarrow$ Serial Number Passenger Number $\longrightarrow$ Passenger Name Passenger Number $\longrightarrow$ Address (of passenger) Passenger Number $\longrightarrow$ Telephone Number Flight Number, Date, Passenger Number $\longrightarrow$ Fare Flight Number, Date, Passenger Number $\longrightarrow$ Reservation Date Serial Number $\longrightarrow$ Model Serial Number $\longrightarrow$ Passenger Capacity Serial Number $\longrightarrow$ Year Built Serial Number $\longrightarrow$ Manufacturer Procedure Number $\longrightarrow$ Procedure Name Procedure Number $\longrightarrow$ Frequency Location Name $\longrightarrow$ Address (of maintenance location) Location Name $\longrightarrow$ Telephone Number Location Name $\longrightarrow$ Manager Serial Number, Procedure Number, Location Name, Date $\longrightarrow$ 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

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

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

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 Functional Dependencies Enclosure Number $\rightarrow$ Type Enclosure Number $\longrightarrow$ Location Enclosure Number $\longrightarrow$ Size Enclosure Number $\longrightarrow$ Date Built Animal Number $\longrightarrow$ Species Animal Number $\longrightarrow$ Animal Name Animal Number $\longrightarrow$ Gender Animal Number $\longrightarrow$ Country of Birth Animal Number $\longrightarrow$ Weight Animal Number $\longrightarrow$ Enclosure Number Sponsor Number $\longrightarrow$ Sponsor Name Sponsor Number $\longrightarrow$ Address Sponsor Number $\longrightarrow$ Telephone Sponsor Number, Name $\longrightarrow$ Relationship Sponsor Number, Name $\longrightarrow$ Date of Birth Animal Number, Sponsor Number $\longrightarrow$ Annual Contribution Animal Number, Sponsor Number $\longrightarrow$ Renewal Date Employee Number $\longrightarrow$ Employee Name Employee Number $\longrightarrow$ Title Employee Number $\longrightarrow$ Year Hired Employee Number $\longrightarrow$ Supervisor Number Animal Number, Employee Number, Service Type, Date, Time $\longrightarrow$ Null 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.

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.

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

Data in ____ cannot have transitive dependencies.

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

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

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