Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

UFCFU3-15-3

Advanced Databases

2022-23

Section 1: Overview of Assessment

This assignment assesses the following module learning outcomes:

Design and Implement Prototypes of Database Systems that serve the needs of Real World problems with complex data

Critically evaluate database systems as to risk and safety of data stored in them and the way such data is accessed and processed

Critically assess the different database paradigms in addressing efficiency and effectiveness requirements of a problem area

The assignment is worth 60% of the overall marks for the module.

The assignment requires you to design, implement and query a database using two different database models: Relational and NoSQL databases. Details of the task can be found in section 2 below.

Section 2: Task Specification

An estate agent’s business database

Golden Bar Theatre in London maintains a database of ticket sales and a typical sample of the data is shown in the CSV file titled “Golden Bar Theatre Data” that you have been given. The following regulations apply to booking theatre tickets at this venue.

Booking is open to members only. There are four categories of membership, Blue, Bronze, Silver, and Gold. All members can reserve up to two tickets for a particular show. A show is defined by production, date, and time. Silver and Gold members can reserve up to two and three different shows respectively in the same production. All others can only reserve one show per production. The theatre is an open air venue and has just 64 seats, split in eight rows (A-H) of eight seats (1-8) each. It runs a short season in the months of July and August only.

Task1

Normalize the data given in the CSV file accompanying this spec. This should be completed to 3^rd Normal Form. For each normal form you should write the rule that the data must obey to conform to this normal form. Identify Primary Keys and Foreign Keys for each of the resulting tables in every phase (Normal Form) of the process. Please show all entities (tables) for each Normal Form.

(7 marks)

Using the normalised data, create a fully annotate ER Diagram, showing entities, attributes, Relationships, participation, cardinality, Foreign and Primary keys. (6 marks)

Implement the SQL Database as shown in the ERD and populate with the data provided. Show evidence screenshots of the scripts used to create the tables, populate the data and the actual populated tables. (7 marks)

Complete the following five Queries. Queries should be designed to be as efficient as possible reducing the number of join operations where possible (4 marks each – 20 marks in total):

· List the customer’s name and number, along with the total number of seats that they have reserved, for all customers with bookings up to the 25^th of July 2023 inclusive. The listing must be in ascending order according to the total number of tickets.

· List the productions with most reservations (irrespective of date) for shows after the 5^th of July 2023.

· List the top three cities (customer residency) with most seat reservations (irrespective of production), in ascending order.

· Considering that the theatre is quite small, with a capacity of 64 seats only, identify the top three shows (production, date, and time) that are reserved close to capacity, along with the total number of seats reserved.

· List all customers that have reserved seats for at least one show. Order the listing according to membership category (starting with Gold and ending in Blue) and within each category in descending order of numbers of seats.

Task2

Using the original unnormalized data, build a NoSQL Database Model. Use Mongo DB, or another NoSQL database available at UWE, to implement the NoSQL model, and populate the database with data from the original collection provided in the CSV file. Please show the data model that you have derived, scripts that you have used in the implementation (screenshots), the results of individual scripts having run successfully, and the fully populated database. (20 marks)

Provide answers to the queries defined in Task1, using your NOSQL database. (20 marks)

Task3

Discuss the topic of personal data and its protection, how it relates to this case and what does the legislation in the UK require the theatre company to do to protect the data. What could be the repercussions if they fail?  600 words maximum (20 marks)

Section 3: Deliverables

One folder in zip format (only) must be uploaded via the relevant link on the module’s space on Blackboard. The link will be available two weeks before the due date and will be communicated to students via an email announcement.

The folder must contain three pdf files in a zipped folder

· Each pdf file will provide evidence of completing the requirements of one of the three tasks
· For Tasks 1 & 2 screenshots with explanatory labels /notes will be used to evidence all steps taken in the design, implementation, and querying of the database. All entities and attributes will need to be clearly visible for every stage of the design. The code will need to be visible ready to run on the system. For results, all lines of the output of every query need to be provided.

· No software is required to be submitted.

Section 4: Marking Criteria

Tasks 1 & Task 2 – Full marks will be given where all the following have been met:

· The database design is clearly shown with all the required detail presented in the diagrams.

· Entities and attributes are clearly labelled.

· Data integrity references are introduced where appropriate.

· Relevant data is entered and appropriately verified.

· All syntax is error free.

· Queries are structured in a way that supports efficiency of operations

· Results obtained are accurate.

· Marks will be awarded proportionately where you have partially met the requirements of the task

Task 3 - Full marks will be given for the short essay

· Demonstrating thorough understanding of the challenges that need to be addressed.

· Confirming Knowledge of the benefits to be accrued by the proposed solution.

· Analysing the impact of any compromise that you might propose.

Section 5: Feedback mechanisms

Written feedback will be provided on blackboard along with the marks for the submitted work on 5 August 2023.