
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".
| 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 |

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.