Homework

Homework Questions? Ask a Tutor for Answers ASAP

Ask an Expert,
Get an Answer ASAP!

Homework

ObjectiveCreate an initial design database schema

Customer Question
Objective Create an initial design for...
Objective
Create an initial design for the database schema for an online DVD rental business that is similar to the DVD rental portion of the business pioneered by NetFlix®. Map your conceptual design into your approved DBMS, and implement the database schema. We will call this NetFlix® subset the classic default term project.
Default Project Description
Design a database for a DVD rental business such as Netflix.com. The store rents DVDs online. The customer business model is illustrated below.
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 http://www.netflix.com)
Below is an example use case to aid in understanding the Netflix operation: 1. Acustomersignsupforthe3-at-a-timeprogram.
2. Thesamecustomeradds10moviestotheirqueue.
3. Netflix mails to the customer the first three movies in their queue.

Copyright 2008‐2015 Boston University. All Rights Reserved.
Page 1 of 6
4. ThecustomerwatchesandreturnsthefirstmovietoNetflix.
5. Netflixmailsoutthenextmovieinthequeuetothecustomer,whichisthefourthmovie
added to the queue in Step #3.
6. Thecustomerclosestheiraccount,butonlyreturnstwoofthethreemoviesthecustomer
has at home.
7. Netflixchargesthecustomer$25forthemissingmovie.
This use case is not exhaustive. There will be elements in your 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 course; such a database would have a few hundred tables. A general guideline is that to effectively create this design for this course, the logical ERD should have between 10 and 20 entities. This number is ***** exact and will vary according to your specific implementation. Example entities include Customer, Movie, Rental, and Queue.
Note that this implementation need not provide for streaming or downloadable movie content.
Use Cases
Below are 10 use-cases. A complete schema design will allow all ten of these use cases to be addressed in full. For use cases where information is requested, provide a single query to retrieve the information. For use cases that require data modification, develop a stored procedure that performs the modifications, and invoke the store procedure. The store procedures you create should be designed to be reusable, by making use of one or more parameters. For example, the stored procedure for use case #***** should use a parameter to specify the particular customer’s queue that will be modified.
Page 2 of 6
1) A customer requests the titles of all movies that are directed by "George Lucas" or by “Rich Christiano”. Write a single query that retrieves this information.
2) Management requests the names of all currently active customers, as well as the name of the current plan in which each of these customers is enrolled. Write a single query that retrieves this information.
3) A customer wants to add a movie to their queue so that the newly added movie will be the next movie they receive. Develop a parameterized stored procedure that accomplishes this, then invoke the stored procedure for a customer of your choosing.
4) A customer requests the titles of all the DVD’s that he or she has not rented. Write a single query that retrieves this information for a customer of your choosing.
5) A customer cancels their membership and does not return a rented DVD, necessitating that a $25 DVD replacement fee be charged to their account. Develop a parameterized stored procedure that accomplishes this, then invoke the stored procedure for a customer of your choosing.
6) A customer enrolled in the two-at-a-time plan cancels their membership. When a customer cancels their membership, they become inactive, but their DVD queue and rental history remains in the database, in the event they return as a customer. Develop a parameterized stored procedure that accomplishes this, then invoke the stored procedure for a customer of your choosing.
7) Management requests 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. Write a single query that retrieves this information for management.
8) Management requests identification of the plan with the most customer enrollees, and for that plan, the name, number of DVDs allowed at one time, and the number of customer enrollees. Write a single query that retrieves this information for management.
9) Management requests the names of all customers, and for each customer, the titles of the movies that they rented multiple times. For each title, management would like to see the number of times it was rented by the customer, only including titles that the customer rented more than once. If a customer has no rentals, or did not rent any movies multiple times, management does not want to see them in the list. Write a single query that retrieves this information for management.
10) Management requests the titles of all movies, and for each movie, the number of different customers that rented the movie. They would like the list to be ordered from the highest number of different rentals to the lowest number. Multiple rentals of the same movie by the same customer only count as one unique rental. Management is interested in the number of different customers that rented the movie, but not whether the same customer rented the same movie more than once. Write a single query that retrieves this information for management.
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, indexes, 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. Barring extraordinary circumstances, 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.
Page 3 of 6
Make sure you assign a genre to the DVD movies. A non-exhaustive, example list of genres is given below:
 Thriller
 Sci-Fi
 Comedy
 Drama
 Miscellaneous
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.
Submitted: 2 years ago.Category: Homework
Show More
Show Less
Ask Your Own Homework Question
Customer reply replied 2 years ago
Please i need this ASAP! I can pay extra for a swift response
Answered in 3 days by:
5/28/2015
Tutor: Mr. Gregory White, Professor replied 2 years ago
Mr. Gregory White
Category: Homework
Satisfied Customers: 5,240
Experience: M.A., M.S. Education / Educational Administration
Verified
Hello, my name is Greg.
Is there any other information you can send to see if I can assist on it this for you? If you have any documents you can upload, you can do so to mediafire.com or box.com and share the link here with us.
If I had a model and could provide that as a model (would have to check files to see if I have one), would that be sufficient or are you seeking a fully written new model document?
Ask Your Own Homework Question
Customer reply replied 2 years ago
Hi,
am actually requesting a fully written new model document including the entities and the attributes as well. and also the relationships.
Ask Scott Your Own Question
Scott
Scott
Scott, MIT Graduate
Category: Homework
Satisfied Customers: 3,040
3,040 Satisfied Customers
Experience: MIT Graduate (Math, Programming, Science, and Music)

Scott is online now

A new question is answered every 9 seconds

How JustAnswer works:

  • Ask an ExpertExperts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional AnswerVia email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction GuaranteeRate the answer you receive.

JustAnswer in the News:

Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.

What Customers are Saying:

Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help.

Mary C.Freshfield, Liverpool, UK

This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!!

AlexLos Angeles, CA

Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult.

GPHesperia, CA

I couldn't be more satisfied! This is the site I will always come to when I need a second opinion.

JustinKernersville, NC

Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around.

EstherWoodstock, NY

Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know.

RobinElkton, Maryland

He answered my question promptly and gave me accurate, detailed information. If all of your experts are half as good, you have a great thing going here.

DianeDallas, TX

< Previous | Next >

Meet the Experts:

LogicPro

LogicPro

Engineer

5,672 satisfied customers

Expert in Java C++ C C# VB Javascript Design SQL HTML

Manal Elkhoshkhany

Manal Elkhoshkhany

Tutor

4,549 satisfied customers

More than 5000 online tutoring sessions.

Linda_us

Linda_us

Finance, Accounts & Homework Tutor

3,138 satisfied customers

Post Graduate Diploma in Management (MBA)

Chris M.

Chris M.

M.S.W. Social Work

2,635 satisfied customers

Master's Degree, strong math and writing skills, experience in one-on-one tutoring (college English)

F. Naz

F. Naz

Chartered Accountant

2,158 satisfied customers

Experience with chartered accountancy

Bizhelp

Bizhelp

CPA

1,887 satisfied customers

Bachelors Degree and CPA with Accounting work experience

Seanna

Seanna

Tutor

1,781 satisfied customers

3,000+ satisfied customers, all topics, A+ work

< Previous | Next >

Related Homework Questions
I would ask this in homework but I think this is out of
Hi,I would ask this in homework but I think this is out of their area of expertise. How much to answer these 40 electrician trade school questions from Penn foster. This is their NEC graded project wh… read more
Dr. Donna Kakonge
Dr. Donna Kakonge
Doctor of Education
181 satisfied customers
I need help implementing expectation maximization algorithm
I need help implementing expectation maximization algorithm in python … read more
Dr. Donna Kakonge
Dr. Donna Kakonge
Doctor of Education
181 satisfied customers
Section 1 – Objectives: Provide a brief summary of the
Section 1 – Objectives: Provide a brief summary of the project. • Section 2 – Task 1: Basic Web Tracking Mechanism: Summarize your efforts to investigate web tracking, including the screenshots you to… read more
Dr. Donna Kakonge
Dr. Donna Kakonge
Doctor of Education
181 satisfied customers
I have 3 more if you want them, i have to pick a heroine,
i have 3 more if you want them, i have to pick a heroine, and do these assignments, let me know, and let me know what its worth, when this one is done, i will send you the other two, and pay seperatel… read more
O. Adames
O. Adames
Bachelor\u0027s Degree
77 satisfied customers
I need help with advanced macroeconomics. I'm having trouble
I'm having trouble with the IS-MP-PC model, showing what would happen if the FFR would be increased, and also if there were to be a tax reform. … read more
Dr. Donna Kakonge
Dr. Donna Kakonge
Doctor of Education
181 satisfied customers
Is there any help for Linux beginners testout chapter exams.
is there any help for Linux beginners testout chapter exams … read more
Dr. Donna Kakonge
Dr. Donna Kakonge
Doctor of Education
181 satisfied customers
With a minimum word count of 130: Do you believe business
With a minimum word count of 130: Do you believe business enterprise should be regulated by the federal government or that the marketplace should determine price? Explain your response.… read more
Experienced Teacher
Experienced Teacher
Bachelor\u0027s Degree
394 satisfied customers
Prepare 10 column work sheet for fiscal year 2005 starting
prepare 10 column work sheet for fiscal year 2005 starting with unadjust trial balance including adjustments on additional facts?… read more
Manal Elkhoshkhany
Manal Elkhoshkhany
Tutor
Foundation classes for MBA
4,549 satisfied customers
Need an SQL query written that will create a row for every
Need an SQL query written that will create a row for every possible date. I need it to take all invoices, payments and exchange rates and create a row for each record so that it can be filtered by exa… read more
The-PC-Guy
The-PC-Guy
Bachelor\u0027s Degree
628 satisfied customers
We have a project that is due October 29, I have scanned the
Angela, We have a project that is due October 29, I have scanned the info for it but I don't know how to get them to you. Could you please let me know how I can get them to you? Here is the file : dow… read more
Seanna
Seanna
Tutor
1,781 satisfied customers
For LogicPro only: I need to develop a code using SQL for a
For LogicPro only: I need to develop a code using SQL for a cookbook database. Below are the requirements:Provide the SQL CREATE TABLE commands for the five tables in your cookbook database. Include P… read more
LogicPro
LogicPro
Engineer
Bachelor of Technology
5,672 satisfied customers
I need some one to answere a couple excel question. Excel
I need some one to answere a couple excel question … read more
Jason Jones
Jason Jones
AAS Information Technology
6,316 satisfied customers
Program 1) A small company is moving all its employee data
Program 1) A small company is moving all its employee data to a computer. To make a smooth transition, a software program will permit the data entry personnel to enter data into a file. Design a progr… read more
LogicPro
LogicPro
Engineer
Bachelor of Technology
5,672 satisfied customers
Paying a large tip to secure a better table in a restaurant
Paying a large tip to secure a better table in a restaurant is the same as offering a bribe in business to secure a contract." Defend or oppose this statement.… read more
KARRY
KARRY
Master's Degree
92 satisfied customers
For a list of numbers entered by the user and terminated by
For a list of numbers entered by the user and terminated by 0. Write a program to find the sum of the positive numbers and the sum of the negative numbers. Needs to be done in Raptor Program.… read more
LogicPro
LogicPro
Engineer
Bachelor of Technology
5,672 satisfied customers
Need a biography on Woodrow to include the following info:
Angela, Need a biography on Woodrow Wilson to include the following info: 1. Term of presidency and date of service, date of birth, date of death, birthplace, and family history 2. Political affiliati… read more
Seanna
Seanna
Tutor
1,781 satisfied customers
While arguing with his neighbor, pulls out a Swiss Army
While arguing with his neighbor, Sam pulls out a Swiss Army knife with the intent of merely frightening him. Sam accidentally hits his neighbor's arm with the knife, resulting in a slight nick. Unbekn… read more
RobertJDFL
RobertJDFL
Attorney
Juris Doctorate
5 satisfied customers
Paul's missionary work created a controversy in the church.
Paul's missionary work created a controversy in the church. what was the nature of the Jew-Gentile controversy and how did the church handle it?… read more
Academic Tutor
Academic Tutor
Master\u0027s Degree
380 satisfied customers
Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Show MoreShow Less

Ask Your Question

x