Chapter 9 Case Assignment: Scenic Routes

Question

Chapter 9 Case Assignment: Scenic Routes

Assignment: Read the "Scenic Routes" Case Study on p. 397 (also listed below). Then answer the following tasks (not those in the book) and upload in the link below.

Please label your answers clearly with the related question number. Use the Entity-Relationship Diagrams file, Cardinality file and Explanation of 3NF Table Design file links for reference to complete the Case Study (in this week's block)

Case:

Scenic Routes operates a bus company that specializes in travelling on secondary roads, rather than Interstate highways. Their slogan is: “It Takes a Little Longer, But It’s Scenic.” The firm needs to update its passenger reservation system.

Background

Data items must include reservation number, Route Number, Date, Origin, Destinations, Departure Time, Arrival Time, Passenger Name, and Seat Number. For example, Route 97 leaves Monroe, VA, daily at 8:00 a.m. and arrives in Spencer, VA, 100 miles away, at 11:00 a.m. Scenic wants to use an alphabetic reservation code, similar to the codes that airlines use.

Tasks

During the systems design phase, you mentioned normalization to your team leader. She would like you to explain the basics of normalization in plain English to help her understand the data design tasks. Write at least a full paragraph with several sentences explaining this concept thoroughly.

Review the summary of the case on page 397 and draw and ERD with cardinality notation for the Reservation system.

Assume the entities include Passenger, Reservation, and Route.
Create 3NF table designs for the system.
Use sample data to populate the fields for three records on all tables.

Answer 1

Normalization is a technique used to ensure data is stored in the most efficient way possible. By efficient, we mean both without redundancy and dependency. There are many ways of storing information when designing a system, and it can both expedite the entire process, or hinder development. Before we see what a normalized system looks like, lets take a look at a not normalized one:

| ReservationNumber | RouteNumber | Date | Origin | Destination |

| 1 | 97 | 2022-01-19 | Monroe, VA | Spencer, VA |

| 2 | 97 | 2022-01-20 | Monroe, VA | Spencer, VA |

| 3 | 98 | 2022-01-19 | Spencer, VA | Monroe, VA |

This table might look very well organized at first, but notice we have some repeated pieces of information. We are storing the information about the Route 97 twice, for reservation 1 and 2. For such a small sample like this, it might not seem like a big deal, but if we have 1 million reservations every year, we end up storing hundreds of thousands of repeated data, which costs a lot of money.

Now, how can we fix this? Normalization. First, let us divide this table into smaller tables. These tables need to have some relation to each other, and these columns are called Foreign Keys.

Table 1

| ReservationNumber | RouteNumber | Date |

| 1 | 97 | 2022-01-19 |

| 2 | 97 | 2022-01-20 |

| 3 | 98 | 2022-01-19 |

Table 2

| RouteNumber | Origin | Destination |

| 97 | Monroe, VA | Spencer, VA |

| 98 | Spencer, VA | Monroe, VA |

Now, we do not have any repeated information. With table one, we know which route the reservation is taking. We can then use the Route Number, which is Table 2's Foreign Key, to get more information about that specific route.

Now, let's tackle the problem itself. The system must be in 3NF form, and the entities are Passenger, Reservation, and Route. The data items are also given.

Entities: Passenger, Reservation, Route

Data: Reservation number, Route Number, Date, Origin, Destinations, Departure Time, Arrival Time, Passenger Name, and Seat Number

Each entity should have its own table, and we can split data accordingly, without worrying about normalization at first:

Passenger (Passenger Name, Reservation number, route number)

Reservation (Reservation number, date, route number, Departure Time, Arrival Time, Seat Number)

Route (Route Number, Origin, Destination)

To make things easier, I will label each information piece as an alphabetical letter:

Passenger {A} (A, B)

Reservation {B} (B, D, C, E, F, G)

Route {C} (C, H, I)

For this format to be in 3NF, it must first be in 2NF. For it to be in 2NF, it must be in 1NF.

1NF: No field can have multiple values. So, if a passenger has multiple reservations, the row cannot look like this

Name | Reservation

Jon | 34, 35, 36

It must be like this:

Name | Reservation

Jon | 34

Jon | 35

Jon | 36

Our table is already like that.

2NF: No relation can have partial dependency. Our current table already achieves that. The goal here is to avoid redundancy of storing repeated information over and over.

3NF: We should not have transitive dependencies. That means that, no information should be dependent on non-prime attributes, that is, attributes that are not our keys.

For example, if we had no Route table, and instead stored all in the Passenger table, that would violate 3NF. In this case, origin and destination would depend on the route number, which is not a primary key of this table.

If any of the conditions is violated, there are formulas to convert a table and make it be in the proper NF format. But, in general, I find it simpler to make tables that adhere up to 3NF to begin with. Remember, if you table avoids data repetition and redundancy, you are most likely in 3NF form. But always double check to make sure.