Management Information Systems (33:623:370) 
Professor Eckstein
Solution to Buses In-Class Exercise (former Midterm Exam Problem)

Part (a):

KINDOFBUS(Manufacturer, Model, MaintIntervalMiles, MaintIntervalDays,
                        OilGrade, OilCapacity)

BUS(LicensePlate, Manufacturer, Model, DateAcquired,
         DateManufactured, MilesWhenAcquired)
    (Manufacturer, Model) foreign key to KINDOFBUS

MAINTENANCE(LicensePlate, Mileage, MaintenanceDateAndTime, Description)
    LicensePlate foreign key to BUS

DRIVER(DriversLicense, FirstName, LastName, MiddleNameOrIinitial,
                BirthDate, CellPhone, Phone, LastTrainingDate)

DISPATCH(LicensePlate, DispatchTime, ReturnTime,
                     DispatchMiles, ReturnMiles, DriversLicense, Comments)
    LicensePlate foreign key to BUS
    DriversLicense foreign key to DRIVER

Note that there are other primary key combinations possible for MAINTENANCE and DISPATCH, or you could always just make up a synthetic key.  You could also use a synthetic key for KINDOFBUS.  Take note of the way we annotate a composite foreign key "(Manufacturer, Model) foreign key to KINDOFBUS".

Part (b):

Field Datatype Bytes Used
LicensePlate Text(8) 8
DispatchTime Date/Time 8
ReturnTime Date/Time 8
DispatchMiles Long Integer 4
ReturnMiles Long Inteer 4
DriversLicense Text(15) 15
Comments Text(255) 255
Total   302

So, each record in the dispatch tables uses 302 bytes.  We then calculate

  120  buses
´ 2  records/(bus day)
´ 330  days/year
´ 5  years
=  396,000  records
´ 302  bytes/record
=  119,592,000  bytes
÷ 10242  bytes/MB
= 114.1  MB

Part (c):

Instead of the single table DISPATCH from part (a), we have

DISPATCH(LicensePlate, DispatchTime, ReturnTime,
                     DispatchMiles, ReturnMiles, DriversLicense)
    LicensePlate foreign key to BUS
    DriversLicense foreign key to DRIVER

COMMENT(LicensePlate, DispatchTime, Comments)
    (LicensePlate, DispatchTime) foreign key to DISPATCH

Again, you could use various alternative primary key combinations, or a synthetic key. 

The size of each record in the DISPATCH table now shrinks 255 bytes, because the Comments field is gone.  So each record takes 302 – 255 = 47 bytes.  So the space for DISPATCH is now

   396,000  records
´ 47  bytes/record
=  18,612,000  bytes
÷ 10242  bytes/MB
= 17.8  MB

The number of bytes per record in COMMENT is

Field Datatype Bytes Used
LicensePlate Text(8) 8
DispatchTime Date/Time 8
Comments Text(255) 255
Total   271

and the COMMENT table has about 5% ´ 396,000 = 19,800 records.  Thus the space for COMMENT is

   19,800  records
´ 271  bytes/record
=  5,365,800  bytes
÷ 10242  bytes/MB
= 5.1  MB

So the total space taken is 17.8 + 5.1 = 22.9 MB.  The space saved is 114.1 – 22.9 = 91.2 MB.