Normalization Assignment

MIS 533
Spring 2003

This assignment can be completed by 2-member teams. 

There are two parts to this assignment. 

I) The first part will involve a table that is partially of your own design.  Use a report such as a sales summary, a telephone bill, a credit card invoice, etc. to build a single table.  Extract the fields represented on the report and build a single table from these fields.  Next, identify the dependencies between these fields. Finally, if appropriate, normalize the table into BCNF. (50%)

II)  For the following description of an airline reservation system, identify the functional dependencies between each field and build normalized tables based on these dependencies.  (50%)

Symba Airlines is a new airline that seeks to hit the market strong because of its advanced reservation system.  Based on the success of Southwest, Symba will be taking a similar approach with regards to fun flights, unassigned seating, and standardized aircraft.  Some of the information that are of particular importance for the reservation system include the following:

• Flight information such as a flight number, the origin city, the destination city, scheduled departure time, and scheduled arrival time need to be recorded.  All of their flights are non-stop, so multi-leg hops do not need to be accounted for in this system.
• A specific flight will occur on a specific day and have a specific crew assigned to the flight.  In particular, the crew information that needs to be recorded included the employee ID and the employees role on the flight (e.g., captain, flight attendant, etc.).
• Specific pieces of equipment (i.e., planes) will need to be accounted for.  Information such as the aircraft's Federal ID, model, capacity, and next-scheduled maintenance will need to be recorded.
• The maintenance record for an aircraft will include a unique maintenance ID, the date of the maintenance, a description of the maintenance, aircraft identification, and the employee who worked on the aircraft.
• Employee information will include their ID, name, cell/office phone, and title.
• Information about customers includes their ID, name, phone number, and mailing address.
• Reservations made by customers are recorded with a reservation number, the flight information, customer information, the date, the fare, the payment method (if using a credit card, the CC number and expiration date should be recorded). 

In the process of completing this assignment, perform the following steps for each of the two problems discussed above.

1. List the fields in one table horizontally across the page and add some data (of your own design) underneath each field (see Table 5-3 for an example).  You only need to include as much data as needed to understand the relationships between the fields (i.e., you only need as many records as it takes to understand the dependencies between the fields). 
2. Make sure that the initial table is in 1NF.  At this stage, it is probably still a good idea to maintain some of your example data in the table.  Make certain that you identify the primary key for each [mega]table.
3. Identify the functional dependencies that exist between the fields.  At this stage, you will want to list the fields in a format similar to what is shown on the in class examples and in the textbook (Figure 5-23).  You can probably drop the data from the tables (unless it helps you to understand the functional dependencies).  This also implies that you will need to identify and display the candidate keys.  When defining candidate keys, make certain that you think about how to truly make each key unique
4. Convert the tables into higher levels of normalization.  All tables should be normalized to at least BCNF to complete the process (if you don't bring a table to a higher level of normalization, explain why this was not done).  Show all steps.  It is probably a good idea to include an explanation of your rationale for the actions taken at each step of the process (i.e., similar to the examples provided in class exercises).

Throughout the normalization process, make certain that you identify the primary key by underlining the field(s) that constitutes the primary key (when you build new tables, also italicize any foreign keys that you identify -- this will help you verify that you are completing the normalization process properly).  It is also important that you show and explain your work and the logic that helps me to understand why you made the changes that you made for each stage of normalization (see the examples).  Make certain that you show the progression from lower levels of normalization to the higher levels.  In other words, do not jump from an unnormalized table to the highest level of normalization (I want to see the logic and stages that you progressed through in completing the project).

What to turn in: Turn in documentation showing your work for each step of the process.  In other words, show each table at each stage of normalization. Make certain that you label the stage of the process that you are showing.  In addition, label each table and provide a description of the assumptions that you made in bringing the table(s) to the specified level of normalization. It is critical that you show not only the tables, but also the description of your assumptions and related logic.  All elements should be neatly typed on plain white paper.  You should include a cover sheet that includes your name, the assignment number, and the date.  I find it useful to use a spreadsheet to document these processes because you can easily copy and paste cells representing fields; however, use whatever documentation tools that you find useful for this exercise.    Staple all components together.

You will be graded on the correctness, completeness, and quality of your effort.  Spelling, punctuation, grammar, neatness, and all other elements related to good written communication will be considered in assigning grades.