Part 1 – Database Design (20%)From the problem statement below you should create a Logical design for the proposed database.Films are rented out in shops and there are several shops.

| June 14, 2018


Part 1 – Database Design (20%)
From the problem statement below you should create a Logical design for the proposed database.

Films are rented out in shops and there are several shops. Each shop has a name, an address, and a phone number. For each employee we must keep the following information: the shop where the employee works, a name, a supervisor, an address, a phone number, NIN (National Insurance Number) and the DATE when the employee was hired and a date when the employee left the company. For each customer we have to keep the following information: a customer id, a name, an address, and a phone number (if any). Before a customer can rent a DVD, they have to register at one of the shops. Once a customer has registered they can take a DVD from any of the shops but they must take the DVD back to the same shop. If they do not take the DVD back to the same shop, they will incur a fine. For each rental, we must keep track of which employee served the customer, which film and which copy (identified by a unique DVD ID) the customer rented, information about payments, the DATE and the time of the rental, the status (rented, returned in time, returned late), the rate (i.e. the price), and if applicable, due DATE and overdue charges. About the payment we have to keep which of the employees received the returned DVD (does NOT have to be the same employee who lent the DVD), the type of payment (i.e. cash, cheque, credit card, direct debit – for each type you must provide for relevant information to be kept, e.g. credit card number if credit card is used), the amount of the payment, DATE + time of the payment, payment status (completed if cash or the money have been received, approved if debit or credit card go through, pending if the cheque has NOT cleared yet). About each DVD we have to keep information in what condition the DVD is and what film is on the DVD. So that customers can search for films on the company web site we keep information about each film. We have to keep its title, the names of directors, a simple description, the name of one or more actor/actress and their role in the film (this could be lead or supporting actor/actress), the film’s rating (e.g. U, PG, 12A etc.).

Part 2 – Implementation of the following queries (40%)

1.Display the name of each shop and the total number of customers per shop; order by customers from highest to lowest.    (2)
2.Display the name of each shop, the total money earned per shop (AS Takings) and the total number of films rented per shop (AS Transactions).    (2)
3.Write a query to display the total number of transactions attributed to all employees, giving employee number and employee name (this includes payments and returns).    (6)
4.Write a function to get the payment sales figures from any given shop. The shop should be searched for by name.    (7)
5.Create a view table of sales figures for each individual shop (displaying the shop by name), the date the sales figures were created and the manager of the shop (displaying the shop by name).    (5)
6.Write a query to find the top sales person in the company. Display the employee name, the shop where he/she is employed and the value of sales. Do not include overdue charges.    (8)
7.Display the name of the top sales person within each shop. Display the employee name, the shop where he/she is employed and the value of sales for that person.    (10)

Written Component – Approximately 3000 words (40%)

1.    Discuss and evaluate alternative RDBMS (15%) – 1125 Words

2.    Discuss the data storage requirements for Big Data (25%) – 1875 Words
Within this question discuss five of the following subject areas:

Volumes of data
Types of data
Sources of data
Data transactions
Data storage
Tools required
Security
Data recovery


Order your essay today and save 20% with the discount code: ESSAYHELP
Order your essay today and save 20% with the discount code: ESSAYHELPOrder Now