1) Create Oracle database tables using SQL Data Definition Language (DDL) for each table listed in the metadata of Assignment 1. You may need to use a combination of DROP TABLE, CREATE TABLE, and ALTER TABLE SQL statements. Make sure that entity and referential integrity are enforced by declaring a primary key for each table (these may be composite keys) and declaring all appropriate foreign keys. Your CREATE TABLE and ALTER TABLE statements (if desired) must show integrity constraints, as appropriate, for NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, REFERENCES, and CHECK constraints. Be sure to save your SQL script file used to create these tables with a .sql extension and your output SPOOL file with a .lst or .txt extension. You should rerun and test your SQL script file until it runs without any errors (this is why youll want to include DROP TABLE statements). Submit your SPOOL file showing that all SQL in your SQL script file worked properly.

Tables	Clomns	Keys	Comments
Movies	Movie_id Number(5) Title Varchar2(30) Description Varchar2(100) Released_by Number(3) Released_on Date	Movie_id is primary key . Released_by is a foreign key reference to company_id in Companies	This table is used to store movie data related to movie itself, such as title or content description. Production companies release their movies on given days. The companies are referenced by their ids. Actors played in movie cannot store here because movie and actors are M:N relationship. Need additional associative entity (actor_in_movies) to establish two 1:N relationships to deal with the case. Movies to actor_in_movies Actors to actor_in_movies Awards and movies have the same relationship (M:N) as above. Movie_awarded working the same way as actor_in_movie.
Companies	Company_id Number(3) Name Varchar2(30) Description Varchar2(100)	Company_id is primary key of the table	This table is used to store data related to production company.
Producers	Producer_id Number(5) Name Varchar2(30) Company_id Number(3)	Producer_id is primary key Company_id is a foreign key to Companies	This entity stores movie producer data.
Movie_producers	Movie_producer_id Number(5) Movie_id Number(5) Producer_id Number(5)		Associative entity to resolve N:M between movies and producers
Awards	Award_id Number(3) Award Varchar2(30) Award_description Varchar2(100)	Award_id is primary key
Movie_awards	Movie_award_id Number(5) Movie_id Number(5) Award_id Number(3) Awarded_date Date	Movie_award_id is primary key Movie_id id foreign key to movie_id in Movies. Award_id is a foreign key to Awards.	Associative entity to resolve N:M between movies and awards Movie_id and Award_id are foreign key to reference to the attributes in corresponding parent entities.
Actors	Actor_id Number(5) Name Varchar2(30 Gender Char(1) Contact Varchar2(30	Actor_id is primary key
Actor_in_movies	Actor_in_movie_id Number(5) Actor_id Number(5) Movie_id Number(5)	Actor_in_movie__id Is primary key Actor_id is foreign key to reference to Actors Movie_id is foreign key to reference to Movies	Associative entity to resolve N:M between movies and actors. Actor_id and Movie_id are foreign key to reference to the attributes in corresponding parent entities.
Distributed_movie_list	Distribution_id Number(8) Movie_id Number(5) Distributor_id Number(3) Distribute_type Varchar2(10) Inventory_quantity Number(3) Unit_price number(8,2)	Distribution_id is primary key Distributor_id is foreign key to reference to Movie_distributors Movie_id is foreign key to reference to Movies Note: you should consider use check constriant on Type_distributed To limit values as DVD or VEDIO.	Distributed_movie_list stores data for every movie available on the market (must be in movies entity ) based their released type (DVD or Vedio). The movies in any types are distributed by a distributor listed in distributors. Quantity of each available type of movies are stored based on type and distributors. Movie_id is a foreign key that references movies.
Movie_Distributors	Distributor_id Number(3) Company_name Varchar2(30) Location Varchar2(30) Contact Varchar2(20)	Distributor_id is primary key	Distributor data is stored in this entity
Movie_rental_stores	Store_id Number(4) Location Varchar2(50) Name Varchar2(30)	Store_id is primary key of the table
Orders	Order_id Number(8) Store_id Number(4) Description Varchar2(30) Total_items Number(3) Total_payment Number(8,2) Tax Number(6.2) Order_status Varchar(22) Ordering_date Date Order_completed_date Date	Order_id is primary key Store_id is foreign key referencing to movie_rent_stores	Any orders for DVD or VEDIO should be stored here and each record is uniquely identified by order_id.
Order_items	Item_id Number(10) Order_id Number(8) distribution_id Number(8) number_of_items Number(3) item_unit_price Number(5) item_sub_total Number(10)	Item_id is primary key Order_id is a foreign key to reference to Orders Distribution_id_id is a foreign key to reference to distributed_movie_list	Item_id identifies each item in a particular order. Any item ordered should be in an order. Any items ordered must be available in distribution_list. Order items are listed in order_items entity that must references order_id in orders and also it should reference distribution_list Multiple copies of the same item should be only in one record. Order_id is a foreign key that references orders. distribution_id is a foreign key that references distributed_movie_list.
Customers	Customer_id Number(5) Store_id Number(4) Firstname Varchar2(20) Lastname Varchar2(20) Gender Char(1) Address Varchar2(50) Card_approved Char(1) Card_approved_date Date Phone_number Varchar2(10) Card_number Number(10) Rent_limit Number(2) Overdue_notified Char(1)	Customer_id is primary key Store_id is a foreign key to reference to Movie_rent_stores.	Store data for movie renting customers
Movie_renting_list	Title_id Number(5) Movie_id Number(5) Store_id Number(4) Number_in_store Number(2) Available_for_rent Char(1) Rent_unit_price Number(5,2) Type_distributed Varchar2(10) Overdue_unit_fee Number(5,2)	Title_id is primary key Movie_id is foreign key to Movies Store_id is a foreign key to Stores	The movies should be available (listed in movies) Movie_id is foreign key references to movies The movies should be also in store, meaning purchased. This is better controlled at application level. Not in database level so we do not add any additional relationship to other related entity, such as order_items
movie_copies	Copy_id Number(5) Title_id Number(5) Available Char(1)	Copy_id is primary key Title_id is a foreign key to movie_renting_list	Each renting title may have one or more copies. This entity is a child of renting_titles to record each copy of the given title of renting movie. Title_id is foreign key that references renting_titles. Movie availability should be always checked.
Movie_rent_records	Rent_record_id Number(8) Customer_id Number(5) Copy_id Number(5) Rented_date Date Returned_date Date Rent_fee Number(5,2) Overdue_fee Number(5,2) Overdue_days Number(2)	Rent_record_id is primary key Customer_id is a foreign key to Customers Copy_id is a foreign key to reference movie_copies.	customers and title_copies have M:N relationship. Therefore, movie_renting is used to resolve the issue to create two 1:N relationships, respectively.

Figure 2. The entity groups that store the data based on business processes. The chart is identical to the one above. Movies Distribution Movie Rental released by tide id Movie Distibulo Companies movie rental stores category 0tders company id description store id (FK store id ompany-name Meleased on Movie actors number store store description available for rent actor id description movie id (FK total items overdue unit total payment role movie awards movie procuders order status movie award Ordering date move copies order completed date movie. movie id(FK distributor id FK copy award FK award date distribute type 0rder items movie went vecords customerid inventory amount gender renue cord id store id customer id (FK) distibution (FKO gender producer id retumed date address rent fee award description company number ot items id (FK) card number item.uni,price card approved approved data overdue moffed reruimt Figure 2. The entity groups that store the data based on business processes. The chart is identical to the one above. Movies Distribution Movie Rental released by tide id Movie Distibulo Companies movie rental stores category 0tders company id description store id (FK store id ompany-name Meleased on Movie actors number store store description available for rent actor id description movie id (FK total items overdue unit total payment role movie awards movie procuders order status movie award Ordering date move copies order completed date movie. movie id(FK distributor id FK copy award FK award date distribute type 0rder items movie went vecords customerid inventory amount gender renue cord id store id customer id (FK) distibution (FKO gender producer id retumed date address rent fee award description company number ot items id (FK) card number item.uni,price card approved approved data overdue moffed reruimt