Question 1

20 Marks

Study the scenario and complete the question that follows:

Pretoria Movie rental

A Pretoria based movie rental store owner would like a database created for her growing business. She would like to have a record of the details for her clients. Some of the details to be captured for her clients would include a unique client identifier, first name, surname, email address, phone number. She also wants to store her movies in an organized format. The details are movie identifier, movie name, director, distributor, category, year of release, rental cost, rental duration. Each movie can have only one category assigned to it. For every movie rented, the owner wants to know for how long it will be rented for, who is renting it and the rental cost

Draw a complete Entity Relationship Diagram for the given scenario.

Question 2

30 Marks

Study the scenario and complete the question(s) that follow(s):

Pretoria Movie rental

A Pretoria based movie rental store owner would like a database created for her growing business. She would like to have a record of the details for her clients. Some of the details to be captured for her clients would include a unique client identifier, first name, surname, email address, phone number. She also wants to store her movies in an organized format. The details are movie identifier, movie name, director, distributor, category, year of release, rental cost, rental duration. Each movie can have only one category assigned to it. For every movie rented, the owner wants to know for how long it will be rented for, who is renting it and the rental cost.

2.1

a. Define with the aid of examples partial & transitive dependencies.

(20 Marks)

b. Identify 1 partial dependency & 1 transitive dependency in the scenario above.(5 Marks)

c. List all relations that are in 3rd Normal Form.

(5 Marks)

[Sub Total 30 Marks]

Question 3

50 Marks

With reference to the Question 2 scenario, complete the questions that follow:

3.1 Do the following

a. Create the relevant tables using SQL. Note: Marks would be awarded for the correct creation of one (1) table. Include all SQL query only because screenshots of queries would be awarded zero.

(20 Marks)

b. When the store rents a movie for which it has only one copy, the owners want to ensure prompt return of that movie so other customers can rent it. Write the SQL query for a report that displays the names of customers who have checked out a movie for which the store has only one copy to show the owners the database can track such customers.

(30 Marks)

[Sub Total 50 Marks]