The movies are rented out in stores and there are several stores. Each store has a unique distributor that supplies the store with tapes. A distributor may supply more than one store. Each distributor has a name, an address, and a phone number. Each store has a name, an address, and a phone number. For each employee we must keep the following information: working store, a name, a supervisor, an address, a phone number, SIN (social insurance number) and the date when the employee was hired. For each customer we have to keep the following information: a name, an address, and a phone number (if any). For each rental, we must keep track of which employee served the customer, which movie and which copy (i.e. type) the customer rented, information about payment, 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 accepted the payment (does not have to be the same employee who rented the tape), the type of payment (i.e. cash, check, 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 check has not cleared yet). About each tape we have to keep information in what condition the tape is and what movie is on the tape. About each movie we have to keep its title, director's name, simple description, the name of a (single) major star, the movie's rating (use numbers 1-5).

Table Description:

1. DISTRIBUTOR - Details for Distributors

Constraints: DistributorID Primary key

2. STORE -Details of Store

Constraints: StoreID Primary key, DistributorID foreign key.

3. EMPLOYEE - Employee details

Constraints: EmployeeSIN Primary key,

STOREID  foreign key,

 MGRSIN foreign key from Employee table EmployeeSIN,

4. CUSTOMER - Customer details 

Constraints - CUSTID Primary key,

5. MOVIE - Movie Details

Constraints- MovieID primary key

6. STORE_MOVIE - The movies, the store is having.

MovieId and StoreID both Primary Key

 MOVIEID foreign key,

7. TAPE - 

 Constraints- 

 PRIMARY KEY - TAPEID, MOVIEID, STOREID,

 FOREIGN KEY - (MOVIEID, STOREID) in Store_Movie 

8. PAYMENT_STATUS

Constraints - PSTATUSID Primary Key,

 9. PAYMENT_TYPE

 Constraints - PTID Primary Key,

 10. PAYMENT

Constraints - PAYID Primary Key,

 EMPSIN Foreign Key,

 CUSTID Foreign Key,

 PSTATUSID Foreign Key,

 PTID Foreign Key,

 11. RENTAL_STATUS

 Constraints - RSTATUSID Primary Key,

 12. MOVIE_RENTAL - 

Constraints - PRIMARY KEY (TAPEID, MOVIEID, STOREID, RDTTM), 

EMPSIN, CUSTID , TAPEID , STOREID, PAYID, MOVIEID foreign key

RSTATUSID Foreign key 

13. DEBIT_CARD

 Constraints - PAYID primary key and foreign key,

 

14. CREDIT_CARD

 PAYID Primary Key and foreign key,

15. CHECK

 Constraint -PayID primary key and foreign key,

 Tasks:

1. Make an ER Diagram on any of the online tools like smartdraw, lucid chart etc.
2. Make the above defined tables with proper keys and relationships in MS Access
3. Add the data in the tables. (atleast 20 rows in each table)
4. Make any 10 queries from the above tables in MS Access (at least 2 queries should be from joining atleast 2 tables). Queries should make sense.
5. Make a form for atleast 3 tables you feel most important.
6. Make a discharge report for the patient discharged in last 7 days.
7. Make a report for the doctors group by doctor on call and regular doctor