Find Jobs
Hire Freelancers

Oracle Project: SQL Movie Database

$30-250 USD

Closed
Posted almost 7 years ago

$30-250 USD

Paid on delivery
****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
Project ID: 13746164

About the project

11 proposals
Remote project
Active 7 yrs ago

Looking to make some money?

Benefits of bidding on Freelancer

Set your budget and timeframe
Get paid for your work
Outline your proposal
It's free to sign up and bid on jobs
11 freelancers are bidding on average $150 USD for this job
User Avatar
Hi there, I would like to do this project if given the opportunity. Let me know if you are interested so I can have further details. Thanks.
$100 USD in 3 days
4.9 (101 reviews)
5.5
5.5
User Avatar
A proposal has not yet been provided
$150 USD in 5 days
4.8 (19 reviews)
4.5
4.5
User Avatar
Hi, i have read the project description and i understand you need a database model with test data which is in concept similar to netflix rental model. I have completed several database modeling projects with 100% client satisfaction. i have over 7 years of experience in oracle and mysql databases. i have expertise in data modeling, sql, plsql, schema creation, optimization, administration. i can start this project and complete in couple of weeks or earlier. i have added some of my previous work in the portfolio.
$255 USD in 15 days
5.0 (8 reviews)
3.6
3.6
User Avatar
Hello, I am interested in this assignment, I studied database management as core subject and worked on custom application development, ERP Extensions and Data Warehousing projects. All of these has some level of Database Design and Development. Looking forward to work with you. Regards, Kasyap
$250 USD in 7 days
0.0 (0 reviews)
0.0
0.0
User Avatar
first, I can write any SQL statements and Dml and draw ER diagram in visio with attributes and relationships between entites and primary key and foreign key and constraints example create table customer ( custid number (20) primary key, custname. varchar2 (20) Not null, phone number (30) ) and create rest of tables then determine relationships between them and make any queries may make index on custid, movieid I understand all requirements and I excute all in short time and I can make some examples for Free and print screen and anything you need thanks I working Oracle developer and administration from four years and I have excellent experience in SQL database and Oracle forms and reports
$55 USD in 3 days
5.0 (1 review)
0.0
0.0
User Avatar
I have a degree in Computer Information Systems and did a project similar to this while I was in college. I am experienced with Entity Relationship Diagrams.
$111 USD in 3 days
0.0 (0 reviews)
0.0
0.0
User Avatar
i have good knowledge in dba, and exprince in databases so give me this project and bater responce for me
$111 USD in 5 days
0.0 (0 reviews)
0.0
0.0

About the client

Flag of UNITED STATES
Westford, United States
0.0
0
Member since Apr 16, 2017

Client Verification

Thanks! We’ve emailed you a link to claim your free credit.
Something went wrong while sending your email. Please try again.
Registered Users Total Jobs Posted
Freelancer ® is a registered Trademark of Freelancer Technology Pty Limited (ACN 142 189 759)
Copyright © 2024 Freelancer Technology Pty Limited (ACN 142 189 759)
Loading preview
Permission granted for Geolocation.
Your login session has expired and you have been logged out. Please log in again.