Happy cruise lines er diagram

Assignment Grading Rubric

Course: IT234 Unit: 5 Points: 60

Copyright Kaplan University

Assignment 5 Outcomes addressed in this activity:

Design relational databases by converting entity-relationship diagrams into relational tables Perform the data normalization process Describe a variety of physical database design techniques ranging from adding indexes to

denormalization Course Outcomes: IT234-2: Analyze the systemic approaches that businesses use to acquire, store, and present data and transform it into information used to support business operations and decision making. Part 1 – Logical Design Disclaimer: The organizations and characters depicted in this exercise are fictional. Any resemblance to real organizations or individuals is purely coincidental. Video Centers of Europe, Ltd. is a chain of movie DVD rental stores. It must maintain data on the DVDs it has for rent, the movies recorded on the DVDs, its customers, and the actual rental. Each DVD for rent has a unique serial number. Movie titles and customer numbers are also unique identifiers. Assume that each movie has exactly one “star.” Note the difference in the year that the movie was originally filmed and the date that a DVD—an actual disk—was manufactured. Some of the attributes and functional dependencies in this environment are as follows: Attributes

DVD Number Manufacture Date Movie Title Star Year Filmed Length [in minutes] Customer Number Customer Name Customer Address Rental Date Return Date Fee Paid

Functional Dependencies DVDNumber MovieTitle DVDNumber Star DVDNumber ManufactureDate MovieTitle Star MovieTitle Length MovieTitle YearFilmed CustomerNumber CustomerName CustomerNumber CustomerAddress

Assignment Grading Rubric

Course: IT234 Unit: 5 Points: 60

Copyright Kaplan University

DVDNumber, CustomerNumber, RentalDate ReturnDate, FeePaid Source: Gillenson, M. (2011). Fundamentals of database management systems, second edition. Hoboken, NJ: John Wiley & Sons. Using the attributes and functional dependencies above, consider the following; For each of the following tables (a,b,c,d,e), 1. First write the table's current normal form (as 1NF, 2 NF, or 3 NF). 2. Then, take those tables that are currently in 1 NF or 2 NF and reconstruct them as well structured 3 NF tables. Primary key attributes are underlined. Do not assume any functional dependencies other than those shown.

a. Movie Title, Star, Length, Year Filmed b. DVD Number, Customer Number, Rental Date, Customer Name, Return Date, Fee Paid c. DVD Number, Manufacture Date, Movie Title, Star d. Movie Title, Customer Number, Star, Length, Customer Name, Customer Address e. DVD Number, Customer Number, Rental Date, Return Date, Fee Paid

Part 2 – Physical Design Consider the following relational database for Happy Cruise Lines. It keeps track of ships, cruises, ports, and passengers. A “cruise” is a particular sailing of a ship on a particular date. For example, the seven-day journey of the ship Pride of Tampa that leaves on June 13, 2003, and is a cruise. Note the following facts about this environment:

Both ship number and ship name are unique in the SHIP Table. A ship goes on many cruises over time. A cruise is associated with a single ship. A port is identified by the combination of port name and country. As indicated by the VISIT Table, a cruise includes visits to several ports and a port is typically included

in several cruises. Both Passenger Number and Social Security Number are unique in the PASSENGER Table. A

particular person has a single Passenger Number that is used for all of the cruises that she takes. The VOYAGE Table indicates that a person can take many cruises and a cruise, of course, has many

passengers.

Assignment Grading Rubric

Course: IT234 Unit: 5 Points: 60

Copyright Kaplan University

Source: Gillenson, M. (2011). Fundamentals of database management systems, second edition. Hoboken, NJ: John Wiley & Sons. Analyze each of the following situations and, using the physical database design techniques discussed in this unit, indicate how you would modify the logical design shown to improve performance or otherwise accommodate it.

a. There is a need to list cruises by cruise number but there is also a periodic need to list all of the cruises in order by start date.

b. There is a frequent need to quickly retrieve the data about a cruise together with the data about the ship used on the cruise.

c. There is a frequent need to quickly retrieve cruise data based on departure date. d. Data about passengers from California must be accessed quickly and much more frequently than data

about passengers from anywhere else. e. There is a frequent need to quickly retrieve a list of the port managers of the ports at which the ship on

any particular cruise will stop. f. There is a frequent need to quickly find the total number of passengers who were on any particular

cruise. g. There is a frequent need to find the start and end dates of cruises as quickly as possible.

Assignment Grading Rubric

Course: IT234 Unit: 5 Points: 60

Copyright Kaplan University

h. There is a frequent need to find cruise data based on ship name. Hint: The Ship Name attribute is unique.

Directions: For this Assignment you will examine the methodology and considerations necessary for completion of a Logical and Physical Design. For both part 1 and part 2, examine the exhibits provided. Using your knowledge of the design process evaluate the exhibits and answer the questions provided. In a word document, copy each question and then directly underneath it provide your answer. Format the word document for maximum readability and clarity. Use a consistent font throughout. Arial 11, is preferred. Directions for Submitting Your Assignment: Compose your Assignment in a Microsoft Word document and save it as IT 234 YourName_Unit_5.doc and submit it to the Dropbox for Unit 5. Written Assignment Requirements: Written work should be free of spelling, grammar, and APA errors. Points deducted from the grade for each writing, spelling, or grammar error are at your instructor’s discretion. Please be sure to download the file “Writing Center Resources” from Doc Sharing to assist you with meeting APA expectations. Review the grading rubric below before beginning this activity. Assignment 5 grading rubric = 60 points

Assignment Requirements Points Possible

Points Earned

1. Each of the table's current normal form (as 1NF, 2 NF, or 3 NF) is indicated.

0–5

2. Each of the five tables has been properly reconstructed as well structured 3 NF tables.

0–15

3. A physical design solution is provided for the problem -- There is a need to list cruises by cruise number but there is also a periodic need to list all of the cruises in order by start date.

0–5

4. A physical design solution is provided for the problem -- There is a frequent need to quickly

0–5

Assignment Grading Rubric

Course: IT234 Unit: 5 Points: 60

Copyright Kaplan University

retrieve the data about a cruise together with the data about the ship used on the cruise.

5. A physical design solution is provided for the problem -- There is a frequent need to quickly retrieve cruise data based on departure date.

0–5

6. A physical design solution is provided for the problem -- Data about passengers from California must be accessed quickly and much more frequently than data about passengers from anywhere else.

0–5

7. A physical design solution is provided for the problem -- There is a frequent need to quickly retrieve a list of the port managers of the ports at which the ship on any particular cruise will stop.

0–5

8. A physical design solution is provided for the problem -- There is a frequent need to quickly find the total number of passengers who were on any particular cruise.

0– 5

9. A physical design solution is provided for the problem -- There is a frequent need to find the start and end dates of cruises as quickly as possible.

0– 5

10. A physical design solution is provided for the problem -- There is a frequent need to find cruise data based on ship name.

0– 5

Total (Sum of all points)

0–60

Less deduction taken for spelling, grammar and APA errors. Plagiarism is totally unacceptable.

Adjusted Total Points