INTRODUCTION TO DATABASES
YEAR 1 – COURSEWORK 2 (60%)
Assignment hand-out: Week 1
Hand-in: Week 12/commencing 14th April
Feedback: You will receive feedback within 20 working days of the submission deadline.
Staff Responsible for Assignment:
Overview
This assignment consists of a video screencast assessing your practical skills in the design and implementation of database systems. The mark achieved in this assignment will contribute to 60% of the overall module mark.
Learning Outcomes Covered by the Assignment
Successful students will be able to:
- Apply techniques to design and implement SQL to meet a real-world scenario.
- Communicate and present information to justify database design decisions and demonstrate SQL query execution.
Description & Deliverables
Working individually, you are required to design a database capable of meeting a business’ needs, implement your design using either SQL Server OR MySQL, test your design using Jupyter Notebooks and provide a walkthrough demonstration of your solution and underpinning code base via a recorded video screencast.
You will submit 1 artefact for this assignment: A video submission.
Business Scenario and Business Processes
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 customer.
- 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 port.
- the charter id, start date and duration of each charter.
- The visit id, expected date of arrival and duration of stay at each port visited.
An unstructured Excel file of the data to be entered into the database should be downloaded from Blackboard.
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 visited.
- list the total length of stay, between two given dates, of yachts in the fleet in each port.
- 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 stay.
- 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 not available.
Tasks and Deliverables
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 save your code and use constraints, default values, ON DELETE clauses, etc as appropriate for the business scenario.
Task Three: Implement your database using either SQL Server OR MySQL OR Azure Data Studio and populate it with the data provided in the sample data file.
Task Four: Create a Jupyter Notebook and connect it to your database. Develop SQL code to test that your database supports each of the Business Processes given in the previous section. Use the templates provided in labs to write Python code and combine it with the SQL code in the connected Jupyter Notebook.
Task Five: You are required to produce a video screencast, lasting no more than eighteen minutes, which provides a walkthrough demonstration of:
- your database design accompanied by a review of the SQL code base,
- execution of your code to build and populate the database in SQL Server,
- execution and output of the SQL queries in your connected Jupyter Notebook to demonstrate that your database supports each of the Business Processes given.
A detailed guide, outlining what the video screencast needs to contain, can be found in the Coursework folder in Blackboard. Video Screencasts that go beyond eighteen minutes will be penalised according to the University framework of penalties (a summary of this is available on the next page). An unstructured Excel file of the data to be entered into the database can also be found on Blackboard.
Please read the remaining pages of this specification document for information on the submission process and the assessment criteria.
Submission Requirements
A submission area for the Video Screencast Submission is provided in the Coursework folder in Blackboard.
Video Screencast Submission (Time Limit 18 Minutes)
Detailed guidelines on the required content, structure and production of the video screencast are provided in the Coursework Assignment folder in Blackboard. Please ensure the filename of the video screencast submitted is given as Surname_BCode (e.g., ‘Burns_B00123456.mp4’).
Required student declaration
All students are required to attach the following declaration to the front of all submitted reports/Videos.
“I declare that this is all my own work. Any material I have referred to has been accurately referenced and any contribution of Artificial Intelligence technology has been fully acknowledged. I have read the University’s policy on academic misconduct and understand the different forms of academic misconduct. If it is shown that material has been falsified, plagiarised, or I have otherwise attempted to obtain an unfair advantage for myself or others, I understand that I may face sanctions in accordance with the policies and procedures of the University. A mark of zero may be awarded and the reason for that mark will be recorded on my file.”
Penalties for exceeding the word limit
Where submitted work exceeds the agreed assessment limit, a margin of up to +10% of the work limit will be allowed without any penalty of mark deduction.
If the work submitted is significantly in excess of the specified limit (+10%), there is no expectation that staff will assess the piece beyond the limit or provide feedback on work beyond this point. Markers will indicate the point at which the limit is reached and where they have stopped marking.
A mark will be awarded only for the content submitted up to this point. No additional deduction or penalty will be applied to the overall mark awarded. The student is self-penalising as work will not be considered/marked.
Disciplinary procedures due to Academic Misconduct may be invoked if the word count/workload has been deliberately and significantly falsified.
Notes on Submission:
- Students should ensure that the submitted video screencasts conform to mp4 format. Corrupted files will be treated as a non-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:
https://www.ulster.ac.uk/student/exams/cheating-and-plagiarism
- Late submission, with the exemption of those supported by prior submission of an EC1 form, will not be marked and will be considered non-submissions.
Assessment Criteria
Marks will be awarded based upon an assessment of the evidence provided within the recorded video screencast according to the criteria in pages 6-8. While the assessment elements in the following table total to 100% of this coursework mark, it is important to note that the mark for this coursework contributes to 60% of the overall module mark.
