Oracle Project: SQL Movie Database

****PLEASE SEE THE ATTACHED WORD DOCUMENT FOR FULL DETAIL OF THE PROJECT!**** **Default Project Description** Design a database for a video rental business such as Netflix.com. The store rents movies -DVDs online. In order to rent a movie, a person must be enrolled at the online store. There are two different membership programs. As quoted from *NetFlix*?: "With *NetFlix* you can rent as many DVDs from the comfort of your home and have them delivered to your door in about 1 business day. There are no late fees and no due dates, and shipping is free both ways. Programs start at $11.99 plus any applicable tax. With our most popular program, 3-at-a-time, you can rent as many DVDs as you want for just $17.99 a month. You keep a revolving library of up to 3 DVDs at a time and can exchange them for new available DVDs as often as you like. … Our 2-at-a-time program (limit 4 DVDs per month) is $11.99 plus any applicable tax per month." *(From [login to view URL])* Best, Dreck ## Deliverables **Objective** Create an initial design for the database schema for an online DVD rental business that is similar to the business pioneered by *NetFlix*?. Map your conceptual design into Oracle and implement the database schema. We will call this *NetFlix*? subset the *default term project*. **Deliverables and Due Date** I strongly urge you to submit the following daily, iterative deliverables for progress updates. * Deliverable 1: A conceptual ERD or EERD. This conceptual ERD may include many to many relationships and need not include attributes. Please use Microsoft Visio for this first delivery--NO OTHER CONCEPTUAL TOOL DESIGN IS ACCEPTABLE. * Deliverable 2: A logical ERD or EERD with all attributes, primary, and foreign keys, with all entities normalized to BCNF. The logical ERD should have no many-to-many relationships, and should show all primary and foreign key constraints. * Deliverable 3: An incremental implementation of your term project, whereby you provide the tables and data which are enough to answer two required queries. * Deliverable 4: Another incremental implementation of the project, whereby you provide the tables and data which are enough to answer to additional required queries. * Deliverable 5: The final submission the entire project. Upon acceptance of the project, the coder will have 7 days ( one week) to return the project. The time is nonnegotiable. **Default Project Description** Design a database for a video rental business such as Netflix.com. The store rents movies -DVDs online. In order to rent a movie, a person must be enrolled at the online store. There are two different membership programs. As quoted from *NetFlix*?: "With *NetFlix* you can rent as many DVDs from the comfort of your home and have them delivered to your door in about 1 business day. There are no late fees and no due dates, and shipping is free both ways. Programs start at $11.99 plus any applicable tax. With our most popular program, 3-at-a-time, you can rent as many DVDs as you want for just $17.99 a month. You keep a revolving library of up to 3 DVDs at a time and can exchange them for new available DVDs as often as you like. … Our 2-at-a-time program (limit 4 DVDs per month) is $11.99 plus any applicable tax per month." *(From [login to view URL])* Below is an example use case to aid in understanding the Netflix operation: 1. A customer signs up for the 3-at-a-time program. 2. The same customer adds 10 movies to their queue. 3. Netflix mails to the customer the first three movies in their queue. 4. The customer watches and returns the first movie to Netflix. 5. Netflix mails out the next movie in the queue to the customer, which is the fourth movie added in Step #3. 6. The customer closes their account, but only returns two of the three movies the customer has at home. 7. Netflix charges the customer $25 for the missing movie. This use case is not exhaustive. There will be elements in the database mentioned in the Netflix specification, but not mentioned in this use case. The database will store membership information for each person, the movies she/he rented, movies in the queue to be rented, when were these movies returned, and so on. The rental history is used for two purposes: * To give employees a basis to work from when they are asked what movie the customer has rented out or if it was lost in mail * To determine if the movie was never mailed back by the customer The database that you design must support all the usual value chain operations occurring at a video rental e-store. You will design the Entity-Relationship model (ERD) for this database, and its corresponding relational model. It is not possible to attempt a full production-capable database for a large enterprise such as NetFlix in the time period afforded by this project; A general guideline is that to effectively create this design for this project, the design should have between 15 and 20 entities (tables). This number is not exact and will vary according to your specific implementation. Example entities include Customer, Movie, and Rental, and Queue. **Project Scope and Requirements** You are expected to **write SQL statements for 15 situations** (use cases). Ten of these fifteen situations are described below. The remaining five you will devise yourself, based on reasonable business operations. Each solution should be DML (SELECT, INSERT, UPDATE, or DELETE) statements or sequences of DML statements. You should place a COMMIT statement at the end of the sequences of DML statements which update the data in your schema. Some of the situations below will require more than one SQL statement. A. ***Required ten situations.*** Below are ten required situations to use toward the total of fifteen. The schema design you create must allow all ten of these required situations to be addressed in full. 1) Find the titles of movies that are made by director "Spielberg" or by "Night Shyamalan." (a query) 2) Customer X wants a list of the titles of all the DVD's that he/she has never rented. (a query) 3) List the names of all movies that are currently sold out. A movie is sold out if all copies of the movie are currently rented and not yet returned. (a query) 4) Find the titles of the movie(s) that have been rented by the most number of different people. This query should not count multiple rentals from the same person; we are only interested in movies that have been rented by the most number of *different* people. (a query) 5) Add $25 to the account balance of a customer of your choice who has dropped the program, but not yet returned a rented DVD. (This is a transaction. It is not a late fee but a DVD replacement fee.) (a transaction) 6) Add a new customer to the database and sign them up for the $11.95 two-at-a-time program. (a transaction) 7) Switch a customer from the two-at-a-time program to the three-at-a-time program. (a transaction) 8) Cancel a customer from the two-at-a-time program. (a transaction) 9) Add a movie to a customer's queue so that the newly added movie will be the next movie the customer receives. (a transaction) 10) Identify all customers who have currently rented more than one copy of a title. (a query) B. ***Additional five required student-defined situations.*** As indicated above, you will identify **five** more situations and provide SQL statements for them as you did for the first **ten** that were provided, for a total of **fifteen** SQL statements or sequences of SQL statements. For each transaction you implement, the situation should require you to *update* *at least two tables* in your schema. For each query you implement, the situation should require you to *join at least two tables* in your schema. You should place a COMMIT statement at the end of the sequences of DML statements which update the data in your schema. Your logical ERD will be mapped to a relational database schema through the use of SQL. The schema should contain tables, primary and foreign keys, at least one index, and optionally triggers and stored procedures. The primary and foreign keys will help enforce the relationships indicated in the logical ERD, and help enforce referential integrity. The tables need to be filled with some fictional data. Make sure you integrate sample data from the situations above, for example, 'Spielberg' should exist in your database. Some tables may just need a few rows. Each table should need no more than 15 rows to effectively demonstrate the correctness of the queries, triggers, and stored procedures. You *may* need to be creative when inserting the data so that the queries return reasonable results. Make sure you assign a genre to the DVD movies. A non-exhaustive, example list of genres is given below: * Action * Sci-Fi * Comedy * Drama * Miscellaneous * Horror * Thriller * Action * Romantic * Anime * Documentary * Adult * Foreigh Make sure you have at least one of the three kinds of conceptual relationships in the business model -- M:N, 1:M, and 1:1. When you map the conceptual model to the logical model and create the schema, you will map each many-to-many relationship to two one to many relationships. Focus on the key value chain tables required for the situations above and related situations. Do not include subschemas for credit card processing, accounting, human relations, marketing, or the many other functional areas of a large enterprise. Concentrate on the value chain operations, which are those involved in the basic processing of customer activities - the activities that directly provide value to the customer. Create at least one aptly chosen index, along with a reason as to why that index is reasonable and beneficial in the context of your overall design (be specific). Thank you very much for your time and consideration ladies and gents! Best, Dreck