Database Systems (COM116 – CRN 72590 & 72591)

Submission Deadline: before midnight on Friday 1st May 2020 (Week 12)

ASSIGNMENT SPECIFICATION

 Submission Deadline: before midnight on Friday 1st May 2020 (Week 12)

Description:

This assignment contributes 70% of the overall module mark. It requires you to design a database capable of meeting the business needs of a yacht charter company, implement and test your design using MySQL, and provide a walk through demonstration of your solution and underpinning code base via a recorded vodcast.

Outline Business Scenario:

A yacht charter company requires a web-based system to manage aspects of its holiday charter business.

There are a number of customers, each of whom may book a number of holiday charters, with each charter booked by an individual customer. A holiday charter may involve one yacht only, but each yacht may be involved in many holiday charters. A holiday charter may visit several ports and each port will be visited by many holiday charters. Most ports have several yachts based in them (although a few smaller ports have no yachts based in them) with each yacht based in just a single home port.

Information to be held and manipulated include:

  • the name, nationality, email address, phone number and id number of each
  • the name, type, model, home port, number of berths and cost of hire per day of each yacht.
  • the name, phone number, email address and number of docking places at each
  • the charter id, start date and duration of each charter, and the visit id, expected date of arrival and duration of stay at each port

 Business processes to be supported include the ability to:

  • add details of a new customer together with the start date and duration of the charter they have booked, but without specifying the yacht to be used or the ports to be
  • list the total length of stay, between two given dates, of yachts in the fleet in each
  • get a list of yachts (by name) visiting their home port between two given dates, together with the date of arrival and the length of the
  • list the ports visited by a given customer together with the date of arrival and length of stay, ordered by date.
  • remove a yacht temporarily for a period of time (e.g., for servicing) and get a list of yachts that are notavailable,

 An unstructured file of sample data in Excel format is available for download from Blackboard.

Tasks:

Task One: Noting any simplifying assumptions you make, design a relational database schema capable of supporting the given business scenario and storing the data provided in the sample data file.

Task Two: Write sql code to implement your database design. You should document your code and use constraints, default values, ON DELETE clauses, etc as appropriate for the business scenario.

Task Three: Implement your database using MySQL and populate it with the data provided in the sample data file.

Task Four: Develop sql code to test that your database supports each of the business processes given above.

Task Five: You are required to produce a vodcast, lasting no more than five minutes, which provides a walkthrough demonstration of:

  • your database design accompanied by a review of the sql codebase,
  • execution of your code to build and populate the database inmySQL,
  • execution of the sql queries to demonstrate that your database supports each of the business processes

The vodcast is the primary element from which the main learning outcomes will be assessed. A detailed guide, outlining what the vodcast needs to contain, can be found in the Coursework Assignment folder in Blackboard. Vodcasts that go beyond five minutes will be penalized.

Submission Requirements:

Two separate submission areas (1. Report Submission and 2. Vodcast Submission) are provided for the Assignment in the Coursework Assignment folder in Blackboard.

1. Report Submission:

A template detailing the required content, structure and production of the report is provided in the Coursework Assignment folder in Blackboard. Please ensure the submitted report is in Word format (.docx file extension) with the filename given as Surname_BCode (e.g.,

‘Corr_B00123456.docx’).

2. Vodcast Submission:

Detailed guidelines on the required content, structure and production of the vodcast are provided in the Coursework Assignment folder in Blackboard. Please ensure the filename of the submitted vodcast is given as Surname_BCode (e.g., ‘Corr_B00123456.mp4’).

NOTE:

  • Students should ensure that the submitted report can be opened and edited using Microsoft Word and that Screencasts conform to mp4 format. Corrupted files will be treated as anon-submission.
  • This is an individual assignment and all work submitted must be your own. Plagiarism will not be tolerated and will be dealt with according to University policy –http://www.ulster.ac.uk/academicservices/student/plagiarism.pdf
  • Late submission, with the exemption of those supported by prior submission of an EC1 form, will be awarded a mark of0%.

Assessment Criteria:

Marks will be awarded based upon an assessment of the evidence provided within the report and recorded vodcast according to the following criteria.

Database Design [20%] Clear demonstration of knowledge and understanding of main principles and concepts in database design including Entity Relationship Diagrams, Normalisation, recognition of any assumptions made, etc
Database Implementation [20%] Correctness and completeness of sql including appropriate use of datatypes, constraints, etc.

Use of comments, code layout and overall readability.

Business Process Queries [20%] Correctness and completeness of measures taken to satisfy given business processes.

Extent to which business processes are successfully demonstrated.

Analysis and Interpretation [20%] Clear evidence of use of analytical and interpretative skills in evaluating outcomes and making judgements, including awareness of limitations.
Presentation of Work [20%] Clarity and structure of presentation, good use of English and appropriate use of technical language.

 

Leave A Comment