Exam 8: Physical Database Design

A hashed file cannot also have indexes built over it.

Which of the following is true regarding indexed sequential files and B+-trees?

Transfer time is the time it takes to actually move the data from the disk to primary memory before the seek, head switching, and rotational delay operations take place.

In terms of time, rotational delay in a disk device takes on average roughly _____.

All of the following are factors affecting application and database performance, except ____.

A new primary key attribute can be created for a table in which the primary key is clumsy because it consists of several attributes.

A fixed disk has 100 cylinders. The tracks in each cylinder are numbered 0-7. Both sides of each platter are used for recording data. How many of each of the following are there in the disk: a. Recording surfaces? b. Platters? c. Tracks per recording surface

Using the division remainder algorithm for a hashed file _____.

Consider Table A on the "one side" of a one-to-many relationship and Table B on the "many side" of the relationship. In the storing derived data physical database design technique, the best place to store the derived data from Table B is ____.

In vertical partitioning, each partition must have a copy of the primary key of the table.

All of the following are true about B+-tree indexes and their files, except _____.

Storing repeating groups within a single table is possible and can improve performance in some circumstances.

Denormalization is a physical design technique that does not change the logical design structure.

In an indexed-sequential file, an index is built over the same field on which the file is sequenced.

Consider the following relational database for Grand Travel Airlines. 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. Every passenger who has flown on Grand Travel has a unique passenger 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 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 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. Pilot Pilot Date of Date of Number Name Birth Hire PILOT Relation Flight Departure Arrival Pilot Airplane Number Date Time Time Number Number FLIGHT Relation Passenger Passenger Telephone Number Name Address Number PASSENGER Relation Flight Passenger Reservation Number Date Number Fare Date RESERVATION Relation Airplarne Passenger Year Number Model Capacity Built Manufacturer AIRPLANE Relation Analyze each of the following situations and, using physical database design techniques, state how you would modify the logical design shown to improve performance or otherwise accommodate it. a. There is a frequent need to quickly get detailed information about a flight and about the pilot who flew the plane. b. It turns out that the Departure Time and Arrival Time data for flights is accessed much more frequently and requires much faster response time than the rest of the data in the FLIGHT relation. c. The repeated use of the Flight Number and Date to access flight data is considered clumsy both in the FLIGHT relation and elsewhere where the combination is required as a foreign key. d. When passengers phone in to get information about their flight records, the records are located by asking for the passengers' phone numbers. e. There is a frequent (and obvious) need to be able to quickly find the number of seats that are currently taken for a flight. f. Some people in the airline have to be able to access the pilot data but are not authorized to see the pilots' dates of birth.

A file with a B+-tree index is an indexed-sequential file.

The clustering files physical database design technique ____.

Taking application priorities into account in physical database design is important because ____.

In the adding new tables physical database design technique ____.

Application data requirements that enter into the physical database design process include ____.