Hermit's Free Library Database Design Cases
Hermit's Free Library Database Design Cases
This is the fourth in a series of database design case studies. The basic example uses three tables, and an extended version utilizes four tables. It is recommended to begin with the first case study, which illustrates a two-table database.
Last summer Richard and Nancy launched a canoe and kayak rental business on Lake Willoughby. The response they received was very positive and they intend to expand the business in their second season.
In their first year of operation they manually recorded reservations on a calendar. This system was inefficient and sometimes resulted in double-bookings.
This season they want to computerize their reservations and they are considering engaging you to create a database application for them in MS-Access. The computerized system would be used to track customers, an expanded fleet of rental boats, and summer reservations.
They want a system that can furnish
The inventory of boats needs to include the following information:
Create a suitable database design and make a diagram showing all tables, fields (including keys), and relationships between the tables.
The first step in database design is to determine all the necessary fields and segregate them into groups that mirror the natural order of the real world. In the basic design we have Customers, Boats, and Reservations.
When the fields have been grouped into their natural order, each group represents a table.
Each record in a table must be uniquely identified by its value in the table's key field.
The next step is to identify the relationships between the tables. In this case we have a many-to-many relationship between Customers and Boats. That is to say that Customers may be rent many boats, and Boats can be rented to many Customers.
When two tables have a many-to-many relationship we create a third table, often referred to as a lien table because it completes the many-to-many relationship.
Notice that the relationship between both many-to-many tables and the lien is a one-to-many relationship.
As with all one-to-many relationships, we place values from the "one" table's key field into a field of the related records in the "many" table. So in this example the primary keys from both "one" tables (Customers and Boats) are secreted into the "many" table (Reservations) as foreign keys.
The final result is a relational database model. When a database model satisfies all the technical requirements it is said to be rationalized.
The objective of rationalizing a database structure is to eliminate redundancy in order to save disk space and to simplify updates by insuring that every piece of information exists in only one place. The only fields that repeat are the foreign keys.
Every rational database model must obey these four rules and pass these three tests.
Here the reservation database has been re-designed to include invoicing. This invoicing system can generate a single invoice for group rentals of multiple boats on the same occasion. The same database design principles apply as before.
