Ask a question

Chapter 9 Case Assignment: Scenic Routes

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)


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.


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.


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.


No answers ... yet!