INTERNATIONAL ADVANCED DIPLOMA IN COMPUTER STUDIES
MODULE: DATABASE DESIGN & DEVELOPMENT ASSIGNMENT TITLE: BOAT HIRE COMPANY DATABASE DEVELOPMENT MARCH 2012 Important Notes: Please refer to the Assignment Presentation Requirements for advice on how to set out your assignment. These can be found on the NCC Education Campus . Scroll down the left hand side of the screen until you reach Personal Support. Click on this, and then on Policies and Advice. You will find the Assignment Presentation Requirements under the Advice section. You must familiarise yourself with the NCC Education Academic Dishonesty and Plagiarism Policy and ensure that you acknowledge all the sources which you use in your work. The policy is available on Campus . Follow the instructions above, but click on Policies rather than Advice. You must complete the ‘Statement and Confirmation of Own Work’ . The form is available on the Policies section of Campus . Scroll down the left hand side until you reach Personal Support. Click on this and then click on Policies and Advice. Please make a note of the t he recommended recommended word count. You could lose marks if you write 10% more or less than this. You must submit a paper copy and digital copy (on disk or similarly acceptable medium). Media containing viruses, or media which cannot be run directly, will result in a fail grade being awarded for this module. module. All electronic media will be checked for plagiarism. Marker's comments:
Moderator's comments:
Mark:
Database Design and Development
Moderated Mark:
March 2012
Final Mark:
Final
NCC Education 2011
Introduction This assignment will allow you to demonstrate an understanding of applying data modelling and data analysis techniques to the design and development of database solutions.
The Scenario You have been sub-contracted to design the database system for Cyclades Boat Hire. Cyclades Boat Hire is a medium sized boat hire company based on the island of Paros in Greece. An initial analysis of Cyclades Boat Hire has identified the following requirements: Cyclades Boat Hire rent boats to customers. A rental is recorded as being for a particular vessel for a particular customer. Over time a customer might take out a number of rentals. Vessels are classified by their type (yacht, speedboat, catamaran). Each rental may be for a number of destinations in the islands of Greece. In addition to this a customer might request equipment for a particular rental. Example of Rental Record: Customer No
Customer Name
340 880 990 900 977
Andrew Smith Jagpal Singh Darko Suvin Lee Laurent Simone Bashir
Vessel Reference Number 55 899 099 87 0873
Vessel
Vessel Type
Mythos Julie Silver Bullet Mythos
Yacht Yacht Catamaran Speed Boat Yacht
Example of Rentals and Destinations: Rental Andrew Smith in Mythos Jagpal Singh in Julie Jagpal Singh in Julie Lee Laurent in Bullet Simone Bashir in Mythos
List of available equipment Equipment Rod and Line Air Tanks Keep Net Gas Barbeque
Types of Equipment Fishing Gear Scuba Gear Fishing Equipment Cooking
Study the above detail and then perform the following tasks:
Database Design and Development
March 2012
Final
NCC Education 2011
Task 1 – 20 Marks Draw an Entity Relationship Data Model that describes the content and structure of the data held by Cyclades Boat Hire.
Task 2 – 15 Marks Produce the resulting tables clearly indicating the primary and foreign keys.
Task 3 – 20 Marks Normalise these tables to ensure that all are in BCNF. Show the steps you have taken in normalising the tables.
Task 4 – 15 Marks Using a Database Management System (DBMS) of your choice, set-up all of the above normalised tables, and populate them with well-designed test data (minimum 5 records per table). Provide printouts of all tables. Reasonable assumptions may be made with regard to data.
Task 5 – 20 Marks Set-up and test all of the following queries using Structured Query Language (SQL). Provide printouts of SQL code for each query and the output produced when you run the query in the database you have developed:
Display the names and addresses of the customers of Cyclades Boat Hire and the rentals they have made and for which vessels. For a particular customer show which equipment they have requested for each of their rentals.
Task 6 – 10 Marks Explain any assumptions you have made when analysing, designing and implementing the above database, justify the approach you have taken and explain any alternative approaches you could have taken to any of the above tasks. Discuss any changes you would make to improve your work.
Guidance Consult your tutor if you have any queries regarding the assignment.
Database Design and Development
March 2012
Final
NCC Education 2011
Submission Requirements
Your submission should be in the form of one single word-processed document that includes any necessary diagrams. The document should not exceed 4500 words (+/- 10%) in total (excluding text in any diagrams). You should explain any assumptions you have made, alternative approaches you could have taken to any of the tasks and any changes you would make to improve your work. A digital version must be submitted on disk or similarly acceptable medium. A copy of the developed database should also be included in the same medium.
Warning: All media must be virus free! Media containing viruses, or media which cannot be run directly, will result in a FAIL grade being awarded for this module. You must read and understand NCC Educat ion’s policy on ‘Academic Dishonesty and Plagiarism’. You must complete the ‘Statement and Confirmation of Own Work’ form and attach the completed form to your assignment.
