Create a logical ERD for each of the problems below using the crowsfoot notation discussed in class. Be sure that each entity has the entity name at the top of the box, the primary key attribute or attributes in the middle of the box, and the non-key attributes in the bottom of the box. Lines should separate each part of the entity box. The ERD should not include any M:N relationships and all attributes should be placed within an entity. Each entity must have a primary key defined. A primary key may consist of one of more attributes. Each relationship should have at least one relationship verb or verb phrase. Please include all required foreign keys and denote the foreign key(s) with the notation (FK) on the ERD. I recommend that you NOT use Visio for this assignment but do make the diagrams readable.

4. Create a database for a company that delivers videos via mail to customers and provides streaming content (a company such as Netflix). While the customer delivery database for Netflix would be quite complex, we are going to design just one small part of it the customer viewing list and DVD queue. This database should keep track of the data required in the columns shown in the Excel Workbook VideoList.xlsx. The worksheet in this workbook (VideoList) contains the data stored for customers video list also called a queue for the DVD delivery part of the application. The video list contains videos that customers want to receive, have received, or have already received and returned. In addition, the list also keeps track of those videos that have been streamed by a customer. A customer of any type is able to stream video content. Sample data is provided in the worksheet to help you understand what needs to be stored for this application. The sample data is brief to limit the complexity of the design. Here is some additional information about the application:

A customer can be of only one customer type at a time. A customer is categorized by type.

A customer creates a queue to keep track of what videos he/she wants to receive in the mail. Most customers have 20-30 videos in their queue at any point in time.

The data values for the status of a video in the queue for a given customer can be as follows:

o Queue: A video is prioritized and waiting in the customer queue until it moves up the queue and is ready to be sent by the company. Each video that has a status of queue must have a number attached. For example, a video for a customer that has a status of Queue-1 means that it is the first video in the queue.

o Returned: A video has been sent to the customer and was returned by the customer.

o Home: A video that is currently in the possession of a customer. A customer may keep a video as long as he/she wants, but customers are only allowed a pre-determined number of videos. This database does not keep track of how many videos a customer is allowed to keep at home; this database only keeps track of the status of videos in a customers list of videos.

o Saved: A video that is not yet released and available for customer use is tracked with a status of Saved. Any saved videos must be prioritized with a number. For example, a video with a status of Saved-1 means that it is the first video in the Saved category for that customer. For example, Customer ID 1234 in the sample data has "The Accidental Tourist in queue with the status of Saved-1.

o Watched: A video that has been streamed and watched is noted with this status. If a video has been streamed and watched, the date that it was watched is stored in the "date video returned" column.

Data in the sample worksheet labeled as null means that the data was not available at that time due to the status of the video. Null data is non-existent. For example, the status of the video Divergent for customerID 1234 indicates that the video is third in the queue waiting to be sent to the customer. Thus, the movie has not yet been sent or returned, so the date that the movie was sent does not exist and the date that it was returned does not exist.

Assume each video has only one video category.

Assume that there is a group of pre-defined video categories that must be stored in the database.

Assume that a customer video rating is an integer and can go from 0 to 5. Assume that it is possible to calculate an average video rating from data in the database. (hint: Any data that can be calculated ("or derived") from the existing data does not have to be stored in the database.)

Assume that it is possible for a customers video rating to be non-existent (null). A customer must enter his/her rating for a particular video. A customer is allowed to enter a video rating for a movie only once there is no time dependent data for a video rating so if a customer enters a new rating it is an update operation rather than an insert operation.

Remember that it is not your job to design any processing for this system. It is not your job to figure out how many videos a customer can have in his/her possession and ensure that the customer has only that quantity of videos. It is not your job to move videos in status from saved to queue when a video becomes available. Your job is to figure out how to store the data with a design that does not contain redundant data. Just design the data storage blueprint, i.e. ERD.