Database Systems

LSIS $5451$

Homework $1-$ Databases and the Relational Model

Due: $6$ PM$,02/01/2024$

$***$Do not include any personal identifiable information in your submission$***$

The Task

Asake, a movie rental company has reached out to you to help create a solution to keep track of

their operations and inventory.

$1.$ Create a sample list of actors and the films they starred in and a second list of customers

and the films they rented. Each list should have at least six records. The first list should have

the name of the actor, the title of the film, a description of the film, the year it was released,

the rating $($e$.$g$.,$ rated G$,$ rated PG$,$ etc.$)$ and the rental price per day. The second list should

contain the name of the customer, their date of birth, and any contact data you think should

be recorded. The second list should also include film rental information such as the start

and end date of the rental. $[10$ points$]$

$2.$ Using the data in the lists, explain how insert, update, & delete modification problems could

occur. $[10$ points$]$

$3.$ How many themes do you think exists in each of the lists? $[5$ points$]$

$4.$ Split the lists into tables such that each table has a single theme. Make sure that all the

tables are linked using one or more columns to show the relationship between the themes.

$[15$ points$]$

$5.$ State assumptions about functional dependencies among the columns. Justify your

assumptions based on the data and what you know about film rental or product rental more

generally. $[15$ points$]$

$6.$ Using the functional dependencies, update the design of your tables in question $4$ to be in

$3$NF$.$ Explain the steps you took and clearly identify the primary keys in each normalized

table. $[10$ points$]$

$7.$ Using the updated tables, answer the following:

a$.$ Is there a composite primary key? Is yes, identify the composite primary key$($s$)$ and

explain why there is a need for a composite key$($s$)$ in those instances. $[8$ points$]$

b$.$ Did you need to use a surrogate key in your tables? Explain why or why not.

$[17$points$]$

c$.$ Does any of your tables have a candidate key? If yes, which table$($s$)$ and what is$/$are

the candidate key$/$s$?$ If no$,$ why? $[10$ points$]$

d$.$ Identify the foreign keys in your tables. Using one of the foreign keys, explain how

the referential integrity rule applies to the table. $[10$ points$]$