Task

Create a new database that will be used to keep track of information for a film rental store. The database will keep track of films, film actors, stores, customers, addresses, city, film inventory, film rentals, and staff information. An Excel spreadsheet Assignment$1$ Data $.$ xlsx containing the data to be stored at creation $($initial load$)$ is provided. Import each sheet of the spreadsheet into a new relation in your database. Please review the data first and ensure that you understand what each column represents before you start creating your database.

There are $10$ tables: Actor, Address, City, Country, Film, FilmActor, Inventory, Rental, Staff, and Store. Each table must have a primary key, relationships between tables must be defined, and relationships must enforce referential integrity. The following guidelines are given about the tables.

a$.$ Actors are identified by the actor$\_$id and have a first name and a last name

b$.$ An address is identified by the address$\_$id$.$ An address has a street address, district, postal code, phone, and city where it is located. Several addresses can be in the same city. The address table stores the addresses of all Customers, Stores, Staffs.

c$.$ Each city is located in a country and has a city name. city$\_$id is the primary key.

d$.$ Customers have a customer$\_$id$,$ first$\_$name, last$\_$name, email address, an indicator for whether they are active or not, and a date they were added to the system. Customers are registered at a particular store and many customers can be registered at one store.

e$.$ For each film, we keep track of the film$\_$id$,$ title, description, release year, rental duration $($number of days for which it can be rented$),$ the rental rate, the length of the film $($in minutes$),$ the cost of replacing it if lost, and a rating. The rating can only be one of G$,$ PG$,$ PG$-13,$ R$,$ or NC$-17.$

f$.$ A film consists of several actors and actors can be part of many films. Film actors show a list of films and the actors in the film. The primary key for this table is composite, consisting of film$\_$id and actor$\_$id$.$

g$.$ An inventory is a film that exists at a store for borrowing. For each inventory, we keep track of the film$\_$id and the store$\_$id where the inventory is available. Inventory$\_$id is the primary key.

h$.$ A rental is identified by a rental$\_$id and we keep track of the rental date, the inventory borrowed, the customer who made the rental, the return date, the staff who last updated the rental record,

$1$

the rental amount paid, and the date of the last$\_$update to the rental. A customer can make

several rentals and a staff may be involved with several rentals.