代写Advanced Database Management Systems Assignment Spring 2024代写留学生SQL 程序
- 首页 >> C/C++编程Advanced Database Management Systems
Assignment
Spring 2024
|
Words: |
1600 |
|
Weighting: |
40% |
|
Learning Outcomes assessed: |
1 and 2 |
|
Submission date: |
26 April 2024 (11:59pm UK time) |
|
Module Leader: |
Gary Budgen |
|
Instructions to candidates: • Please refer to the Assignment Presentation Requirements for advice on how to set out your assignment. • You must familiarise yourself with the Academic Dishonesty and Plagiarism Policy relating to this programme and ensure that you acknowledge all the sources which you use in your work. The policy is available to access through your programme handbook. • You must complete the Statement and Confirmation of Own Work , which is available in the Additional Materials section of the VLE and at the end of this assignment. • Please make a note of the recommended word count. You could lose marks if you write 10% more or less than this. • You must submit a digital copy of this online examination through the VLE virtual learning environment. Media containing viruses, corrupted media, or media which cannot be run directly, will result in a fail grade being awarded for this module. • Diagrams may be word processed or drawn by hand and then uploaded using a scanner or a clear photograph if necessary. • Manipulating the word count through hiding text in diagrams could be penalised. • All electronic media will be checked for plagiarism. |
|
Assignment Guidance
The learning outcomes assessed in this assignment are as follows:
|
LO |
Learning Outcomes |
Assessment Criteria |
|
Practical Skills 1
Practical Skills 2 |
Demonstrate a practical understanding of SQL. Design and implement a database system for a given business scenario. |
- Shape for ER diagram - Relationships for ER diagram - Correct use of notation (UML, SQL, Data Dictionary) - Appropriate screen dumps - Presentation of report |
• It is the student’s responsibility to familiarise themselves with the Academic Dishonesty and Plagiarism policy contained in the Programme Handbook.
• Any assignment submitted after the submission deadline, without prior approval, will be given 0% and the student will be referred.
• The late assignment submission may be marked as a referral attempt, but only a maximum mark of 40% can be awarded for that particular assessment.
• If a student requests an extension to the official submission date this must be done in writing to the Programme Manager at[email protected] at least five full UK working days before the official submission date. This request must be accompanied by supporting evidence.
• This assignment is worth 40% of the overall final mark for the module.
• Students should write no more than 1600 words (+/- 10%) for this assignment.
• Students are encouraged to read widely in preparing for the assignment, making reference to articles in academic journals and other relevant sources.
• All references should be cited in text and included in a reference section at the end of the report using the Harvard Referencing Scheme.
• The Statement and Confirmation of Own Work must be completed and submitted with the assignment.
• This assignment must be submitted by the due date and time as given on the front of this assignment.
• Please refer to the Assessment Criteria contained in the Programme Handbook which shows how the level of marking relates to your standard of work.
Case Study: Hyde Travel
Background on Hyde Travel
Hyde Travel is a medium sized travel company based in Southampton, England.
They have employed you to build a fully working database system to handle their core business which is concerned with the following areas:
• Customer records
• Booking Records for Trips
• Trips and the components of trips
• Vessels
Background on how the company organises its work:
Hyde Travel operates holiday breaks in Europe, travelling from the UK to destinations in France, Germany, Belgium and the Netherlands. Customers make bookings in their own name. They can book places for up to 10 passengers.
A particular trip will be made up of several components. Any component, such as a river cruise, could involve a visit to one or more places. A component is usually assigned a vessel (e.g. river ship, train, coach). Some vessels are used on more than one component. Some components use more than one vessel.
Please Note: The data shown in the assignment is not necessarily normalised, and that it is the candidate’s task to organise the data in the most optimal way possible.
For example, the paper records shown below will not necessarily map directly to data base tables. The candidate is expected to use these samples of data as a starting point for their own normalisation and optimisation of Hyde Travel system.
Please Note: the data shown is only examples and represent a fraction of what the entire data set would be for the organisation.
Assignment
You are required to produce a report that addresses all NINE (9) tasks:
Task 1 (20 marks)
Create an ER diagram (using UML notation) of the Hyde Travel system. Please state any assumptions that you make.
Task 2 (10 marks)
Carry out normalisation for at least TWO(2) of the sample documents. Critically assess the normalisation you have undertaken and discuss how it has helped to produce a set of relations for the scenario. You should discuss the process of normalisation in general and also the specific way it has been applied in your work.
Task 3 (5 marks)
Create an entity listing for the Hyde Travel system. You should show all the entities you have identified, their attributes and identify primary and foreign keys.
Task 4 (10 marks)
Create the relevant SQL CREATE TABLE statements for implementing the Hyde Travel system in a DBMS of your choice (not MS Access). You should provide screen dumps of both the scripts and the implemented tables to show that the create statements have worked.
Task 5 (4 marks)
Populate the database with the data shown in the case study.
Discuss the population of the database tables for the Hyde Travel system (order of population, issues and resolutions and SQL used) supported by appropriate screen dumps.
You may also populate the database with additional data.
Task 6 (16 marks)
Create the following queries. You should provide a screen dump to show the query being run and the output of the query.
a) Show all customers and their bookings.
b) Show the components for all trips.
c) Show all places that customer Colin Robinson will visit.
d) Show all the customers who have booked trips involving the MS Serenity. e) Show all the places that will be visited on 2nd June 2022.
f) Show all the places that are visited by standard coaches.
g) Show all the bookings that are for less than 3 passengers.
h) Show how many components the MS Serenity will be used for in June 2022.
Task 7 (5 marks)
Create a set of screen dumps showing all the data in each table - the data must be ordered by the primary key.
Task 8 (15 marks)
Critically assess the issues with transaction management for the new database system. You should consider support for transactions, concurrency control and recovery will be required for the database you have developed.
Task 9 (10 marks)
Discuss how you have used the case study and documents to identify the requirements of the business. Produce a reflective overview of how the database and queries you have created have met these requirements . Some of the issues you could discuss include how the data model reflects the structure of data used by the business; the utility of the queries you have created; and how all the parts of the assignment constitute a usable system. You should also reflect on any future improvements that could help the business.
The remaining 5 Marks will be awarded for the presentation of the report.