BYGB/ISGB 7973
Assignment 9
Due: November 28
Complete the following exercises from the textbook. (I have added a few
sentences to some problems to clarify the tasks you have to complete.)
Question 4-32.
Figure 4-32 shows a class list for Millennium College. Convert this user view to a set of 3NF relations
using an enterprise key. Assume the following:
•
‰nstructor Name uniquely identifies an instructor. An instructor is assigned a location (e.g.. a room
)
A student has only one major
A course is identified by Course No. Each course has a title.
Question 4-54.
Explore the data included in Table 4-9.
Assume that the primary key of this relation consists of two components: AuthoràID (AID) and Book
number (BNbr). The relation includes data regarding authors, books, and publishers. In addition, it tells
what an individual authoràper book royalty amount is in the case of multi-authored books.
Your task is to covert this tables which shows a hierarchical structure (i.e. each row has sub-rows) into a
first normal form relation and then complete the following tasks
a. Identify the functional dependencies between the attributes.
b. Identify the normal form in which the relation currently is.
c. Identify the errors in the data that have been made possible by its poor structural characteristics.
d. Take the actions (if any) necessary to convert the relation into the third normal form. Identify all
intermediate steps.
Question 4-58
A bus company is responsible for offering public transportation in the suburbs of a large metropolitan
area. The company has significant data management requirements: It needs to keep track of its 150
vehicles, 400 drivers, 60 bus routes, and hundreds of scheduled departures every day. In addition, it is
essential for the company to know which drivers are certified to drive which buses.
The data that the company has available include the following attributes:
RouteID, RouteStartPoint, RouteEndPoint, RouteStandardDrivingTime,
ScheduleDate, ScheduledDepTime, ScheduledArrTime, DriverID, DriverFName, DriverLName,
DateDriverJoinedCompany, DriverDOB, VehicleID, VehicleMake, VehicleModel,
VehiclePassangerCapacity, DriverCertStartDate, DriverCertEndDate.
Sample data for this set of attributes are as follows:
28, Grand Avenue, Madison Street, 38,
{9/12/2018, 8.30, 9.18, 8273, Mary, Smith, 5/2/2007, 3/23/1974, 1123, GreatTrucks, CityCoach,
58, 6/10/2018, 6/9/2019 |
9/12/2018, 9.30, 10.12, 7234, John, Jones, 10/12/2011, 12/15/1991, 5673, GreatTrucks,
CityCoach 2, 62, 4/12/2018, 4/11/2019 |
9/12/2018, 10.30, 11.08, 2343, Pat, Moore, 2/24/1982, 1/19/1958, 4323, PowerTransport,
MidiBus, 32, 8/20/2018, 8/19/2019}
Note that the information for route, there are several runs, each run identified by date and starting time.
In the example above for route 28, three runs are shown.
Based on the facts stated above, consider the flattened table that contains all the attribute listed above.
Give this flattened table a name Schedule so that it becomes a relation in 1NF.
a. Identify the functional dependencies between the attributes of relation Schedule.
(You may want to use diagram similar to that given in figure 4-23, except draw them
vertically rather than horizontally.)
b. Identify primary key for the relation Schedule
c. Is the relation Schedule in 2NF? If nor decompose it into 2NF relations. For each relation identify
the key.
d. Are all the relations defined in Part C in 3NF? If not decompose them into 3NF relations. For each
relation identify the key
e. Are all the relations defined in Part D in BCNF? If not decompose them into BCNF relations. For
each relation identify the key
.

Purchase answer to see full
attachment