📖 MySQL Sample Database Design - Marina Services
A database at a marina services company must support the following requirements.
We'll start by identifying the fields and functional dependencies.
For each MARINA, list the number, name, address, city, state and zip code.
- Fields
- MARINA_NUM, MARINA_NAME, MARINA_ADDRESS, MARINA_CITY, MARINA_STATE, MARINA_ZIP
- Functional Dependencies
- MARINA_NUM => MARINA_NAME, MARINA_ADDRESS, MARINA_CITY, MARINA_STATE, MARINA_ZIP
For each BOAT OWNER, list the number, last name, first name, address, city, state, and zip code.
- Fields
- OWNER_NUM, OWNER_LASTNAME, OWNER_FIRSTNAME, OWNER_ADDRESS, OWNER_CITY, OWNER_STATE, OWNER_ZIP
- Functional Dependencies
- OWNER_NUM => OWNER_LASTNAME, OWNER_FIRSTNAME, OWNER_ADDRESS, OWNER_CITY, OWNER_STATE, OWNER_ZIP
For each marina, list all the SLIPS in the marina. For each slip, list the length of the slip, the annual rental fee, the name and type of the boat occupying the slip, and the boat owner's number, last name and first name.
- Fields
- MARINA_NUM, SLIP_NUM, SLIP_LENGTH, RENTAL_FEE, BOAT_NAME, BOAT_TYPE, OWNER_NUM, OWNER_LASTNAME, OWNER_FIRSTNAME
- Functional Dependencies
- MARINA_NUM, SLIP_NUM => SLIP_LENGTH, RENTAL_FEE, BOAT_NAME, BOAT_TYPE
- OWNER_NUM => OWNER_LASTNAME, OWNER_FIRSTNAME
For each possible SERVICE CATEGORY, list the category number and description. In addition, for each service request in a category, list the marina number and slip number for the boat receiving the service, the estimated hours for the service, the hours already spent on the service and the next date that is scheduled for the the particular service.
- Fields
- SERVICE_CAT_NUM, SERVICE_CAT_DESC, SERVICE_REQ_NUM, MARINA_NUM, SLIP_NUM, EST_HOURS, ACTUAL_HOURS, SCHED_DATE
- Functional Dependencies
- SERVICE_CAT_NUM => SERVICE_CAT_DESC
- SERVICE_REQ_NUM => MARINA_NUM, SLIP_NUM, EST_HOURS, ACTUAL_HOURS, SCHED_DATE
For each SERVICE REQUEST, list the marina number, slip number, category description, description of the particular service and a description of the current status of the service.
- Fields
- SERVICE_REQ_NUM, MARINA_NUM, SLIP_NUM, SERVICE_CAT_DESC, SERVICE_DESC, SERVICE_STATUS
- Functional Dependencies
- SERVICE_REQ_NUM => MARINA_NUM, SLIP_NUM, SERVICE_CAT_DESC, SERVICE_DESC, SERVICE_STATUS
TABLES
Now we can start identifying our tables. It looks like we have five main entities we are dealing with: MARINA, OWNER, SLIP, SERVICE_CATEGORY and SERVICE_REQUEST.
Based on the requirements listed in the list above, we can now identify the necessary tables and fields. Notice, that in each case, I am just copying the fields we identified above and selecting a primary key.
MARINA (MARINA_NUM, MARINA_NAME, MARINA_ADDRESS, MARINA_CITY, MARINA_STATE, MARINA_ZIP.
OWNER (OWNER_NUM, OWNER_LASTNAME, OWNER_FIRSTNAME, OWNER_ADDRESS, OWNER_CITY, OWNER_STATE, OWNER_ZIP)
SLIP (MARINA_NUM, SLIP_NUM,SLIP_LENGTH, RENTAL_FEE, BOAT_NAME, BOAT_TYPE, OWNER_NUM, OWNER_LASTNAME, OWNER_FIRSTNAME)
SERVICE CATEGORY (SERVICE_CAT_NUM, SERVICE_CAT_DESC, SERVICE_REQ_NUM, MARINA_NUM, SLIP_NUM, EST_HOURS, ACTUAL_HOURS, SCHED_DATE)
SERVICE REQUEST (SERVICE_REQ_NUM, MARINA_NUM, SLIP_NUM, SERVICE_CAT_DESC, SERVICE_DESC, SERVICE_STATUS)
Normal Forms
This is our first take on the tables, based on the requirements and is not normalized. So now, we need to go through the normalization process.
- First Normal Form (1NF)
- Remove repeating groups to a new table and identify the primary key.
The SERVICE CATEGORY table has a repeating group - the service request. In this case, we already have a service request table, so we can just take out the fields that are dependent on SERVICE_REQ_NUM and add them to the SERVICE REQUEST table, if they are not already there. We identified in our functional dependencies that MARINA_NUM, SLIP_NUM, EST_HOURS, ACTUAL_HOURS, SCHED_DATE were all dependent on SERVICE_REQ_NUM. The SERVICE REQUEST table already has MARINA_NUM and SLIP_NUM, so we just need to move EST_HOURS, ACTUAL_HOURS and SCHED_DATE to the SERVICE REQUEST table. We must also add SERVICE_CAT_NUM to the SERVICE REQUEST table to link it back to the SERVICE CATEGORY table (foreign key). Now the SERVICE CATEGORY and SERVICE REQUEST tables look like this: (Note that the MARINA, OWNER, and SLIP tables did not change)
SERVICE CATEGORY (SERVICE_CAT_NUM, SERVICE_CAT_DESC )
SERVICE REQUEST (SERVICE_REQ_NUM, MARINA_NUM, SLIP_NUM, SERVICE_CAT_DESC, SERVICE_DESC, SERVICE_STATUS, EST_HOURS, ACTUAL_HOURS, SCHED_DATE)
- Second Normal Form (2NF)
- Move fields that are dependent on only a portion of the primary key to a new table.
We only have one table with a compound key - the SLIP table. However, looking at the functional dependencies we identified, it appears that all of the fields are dependent on both the MARINA_NUM and the SLIP_NUM. Therefore, we do not need to make any changes for this step.
- Third Normal Form (3NF)
- Remove fields that are dependent on a non-key attribute.
Our functional dependencies can help us with this step! If we look at the MARINA table, we see that all of the fields there are dependent on MARINA_NUM, so no changes are needed to it. The same is true for the OWNER table and OWNER_NUM.
However, when we get to the SLIP table, we see two sets of dependencies
MARINA_NUM, SLIP_NUM => SLIP_LENGTH, RENTAL_FEE, BOAT_NAME, BOAT_TYPE
OWNER_NUM => OWNER_LASTNAME, OWNER_FIRSTNAME
The first group is ok, since those fields are dependent on the primary key.
However, the other one means the table is not normalized. Let's look at the dependencies on OWNER_NUM. Since OWNER_LASTNAME, and OWNER_FIRSTNAME are already in the OWNER table, we can simply remove them from the SLIP table so it now looks like this:
SLIP (MARINA_NUM, SLIP_NUM, SLIP_LENGTH, RENTAL_FEE, BOAT_NAME, BOAT_TYPE, OWNER_NUM)
Moving to the SERVICE CATEGORY table, we see that we had originally identified two sets of functional dependencies, one on SERVICE_CAT_NUM and one on SERVICE_REQ_NUM. However, we already moved all the fields dependent on SERVICE_REQ_NUM to the SERVICE REQUEST table in our first normal form because they were part of a repeating group, so no further changes are necessary.
Our final table is the SERVICE REQUEST table. We didn't identify any functional dependencies other than those on the primary key originally, but we do have a relationship between the SERVICE_CAT_NUM and the SERVICE_CAT_DESC. Since SERVICE_CAT_NUM is the primary key of the SERVICE CATEGORY table, we should probably use it in place of SERVICE_CAT_DESC in the SERVICE REQUEST table so it can function more efficiently as a foreign key.
Final Design
Our final database tables are shown below.
MARINA (MARINA_NUM, MARINA_NAME, MARINA_ADDRESS, MARINA_CITY, MARINA_STATE, MARINA_ZIP.
OWNER (OWNER_NUM, OWNER_LASTNAME, OWNER_FIRSTNAME, OWNER_ADDRESS, OWNER_CITY, OWNER_STATE, OWNER_ZIP)
SLIP (MARINA_NUM, SLIP_NUM, SLIP_LENGTH, RENTAL_FEE, BOAT_NAME, BOAT_TYPE, OWNER_NUM)
SERVICE CATEGORY (SERVICE_CAT_NUM, SERVICE_CAT_DESC )
SERVICE REQUEST (SERVICE_REQ_NUM, MARINA_NUM, SLIP_NUM, SERVICE_CAT_NUM, SERVICE_DESC, SERVICE_STATUS, EST_HOURS, ACTUAL_HOURS, SCHED_DATE)
EER Diagram
We have 5 entities.
MARINA, OWNER, SLIP, SERVICE CATEGORY and SERVICE REQUEST. There are one-to-many relationships from MARINA to SLIP (one marina has many slips), OWNER to SLIP (an owner could own more than one slip), SERVICE CATEGORY to SERVICE REQUEST (many service requests within a service category), and SLIP to SERVICE REQUEST (a slip could have more than one service request outstanding - i.e. a mechanical problem and routine maintenance).
As a result, our EER diagram would look like this.
