Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Authors
About us
Contact us
Newsletters
Write for us
Daily SQL Articles by email:
Sign up
Back
SQLServerCentral
Register
Home
»
Database Design
»
Relational Theory
»
Movie Rental Database
Movie Rental Database
Post reply
Like
265
Add to Briefcase
Movie Rental Database
View
Options
Author
Message
sasansamani
sasansamani
Posted 4 years ago
#1675178
Say Hey Kid
Group: General Forum Members
Points: 701
Visits: 66
Based on this description did I create the Class Diagram Correctly? Please open the attachment.
General Manager (GM) of Bobcat Movie Rentals has hired your team as consultant to design and implement a
database for the company. Initial meeting with the GM provided following information:
Bobcat Movie Rentals has several stores throughout the USA. For each store, it is essential to record the store’s
telephone number and address that consists of the street, city, state and zip code. Each store is given a store
number, which is unique throughout the company. Each store has employees, which also includes a Store
Manager. The Store Manager at each store is responsible for the day-to-day operation of the store. Data
maintained for each employee includes name, home address, home phone, gender, hire date, position and salary.
Each employee is assigned an employee number, which is unique throughout the company. Each employee is
assigned to one store.
Each store has a stock of movie DVDs. The data stored for each movie DVD includes the catalog number, copy
number, title, category, daily rental charge, cost, status, and the name of the director. The catalog number
uniquely identifies each movie title. In most cases, there are several copies of each movie title at a store, and the
individual DVD copies are identified using the copy number. A movie is assigned a category such as Action,
Drama, Comedy, Horror, Sci-Fi, or Children. The status indicates whether a specific copy of a movie is available
for rent.
Before renting a movie DVD at any store, a customer must register as a member at a store. After registration, a
member is allowed to rent DVDs. The data recorded about each member include the first name, last name, middle
initial, address, phone number and the date on which the member registered at a store. It is essential to keep track
of the store at which the customer registered as a member. Each member is assigned a member number, which is
unique throughout all stores of the company. The data recorded for each rental transaction on a paper form
includes the rental number, the customer’s name and member number, the catalog number and copy number of
each movie DVD rented, actual daily rental charge for each movie DVD, and the dates the DVD(s) is rented out
and returned. The rental number is unique throughout the company. On a rental transaction, a customer can rent
more than one movie titles.
Attachments
Bobcat Movie Rental (1).jpg
(
1.7K views,
70.00 KB
)
50
Quote
Luis Cazares
Luis Cazares
Posted 4 years ago
#1675190
SSC Guru
Group: General Forum Members
Points: 173021
Visits: 23051
I wouldn't say that it's completely right, but it's a nice try.
- The main problem (IMO) is the DVD table. It isn't normalized. I suggest you to create a Titles' catalog and a Categories' catalog (even a Directors' catalog).
- The Rental date should be on the Rental table not on the details.
- CatalogID should be only on the details.
- The customer name is not needed in the Rentals table (that's why you have the CustomerID)
- RentalID can't be the only column in your Rental_Details PK as it needs to have multiple rows with the same RentalID.
- StoreManager is not needed on Employee table.
- You're missing some relations which are indicated by the PKs and FKs (e.g. Store-Customer, Rental_Details-DVD)
- If you're storing address in separate columns for the stores, it would be nice to do the same for employees and customers (it would help for further analysis).
I'm sure there are more improvements that can be done, but this should give you some work to do.
Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?
How to post data/code on a forum to get the best help:
Option 1
/
Option 2
41
Quote
Jeff Moden
Jeff Moden
Posted 4 years ago
#1675227
SSC Guru
Group: General Forum Members
Points: 937741
Visits: 49116
As a bit of a sidebar, if this is a typical problem being taught nowadays, then it's no wonder the industry is in a world of hurt when it comes to database design and application performance.
--Jeff Moden
RBAR
is pronounced ree-bar and is a Modenism for
R
ow-
B
y-
A
gonizing-
R
ow.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
When you put the right degree of spin on it, the number
318
is also a glyph that describes the nature of a DBAs job.
Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
53
Quote
Jeff Moden
Jeff Moden
Posted 4 years ago
#1675228
SSC Guru
Group: General Forum Members
Points: 937741
Visits: 49116
You should also have a status table to provide the DRI necessary for the status of each DVD. Likewise, you should also have a category table for the same reason. There's more to do but with the suggestions of what to you look given on this thread, so far, you should be able to glean some of the other things that need to be done to make this into a properly normalized database with effective DRI.
--Jeff Moden
RBAR
is pronounced ree-bar and is a Modenism for
R
ow-
B
y-
A
gonizing-
R
ow.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
When you put the right degree of spin on it, the number
318
is also a glyph that describes the nature of a DBAs job.
Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
34
Quote
Grant Fritchey
Grant Fritchey
Posted 4 years ago
#1675322
SSC Guru
Group: General Forum Members
Points: 375948
Visits: 34641
I'm with Luis. I don't like the normalization on DVD. I'll go one further, there is a movie (Luis used title) and there is the physical DVD itself. These are two separate entities. If you break it down that way, then I think you can still maintain the DVD as a child of Store. But, if you don't break it down that way, then you need a many to many relationship between DVD/Title and Store because any given store can have multiple copies of the same movie. I'm unclear why Rental and Rental Details are split. Notice how you have columns in Employee and in Customer that are the same. When you have people in a database, it's best to just have a Person table because you're pretty likely to have that kind of commonality. Then you break out separate tables for the types of person. Because, after all, an Employee can still be a Customer.
----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt
The Scary DBA
Author of:
SQL Server 2017 Query Performance Tuning, 5th Edition
and
SQL Server Execution Plans, 3rd Edition
Product Evangelist for
Red Gate Software
53
Quote
MMartin1
MMartin1
Posted 3 years ago
#1758257
One Orange Chip
Group: General Forum Members
Points: 26189
Visits: 2260
Grant Fritchey (4/8/2015)
I'm with Luis. I don't like the normalization on DVD. I'll go one further, there is a movie (Luis used title) and there is the physical DVD itself. These are two separate entities. If you break it down that way, then I think you can still maintain the DVD as a child of Store. But, if you don't break it down that way, then you need a many to many relationship between DVD/Title and Store because any given store can have multiple copies of the same movie. I'm unclear why Rental and Rental Details are split. Notice how you have columns in Employee and in Customer that are the same. When you have people in a database, it's best to just have a Person table because you're pretty likely to have that kind of commonality. Then you break out separate tables for the types of person. Because, after all, an Employee can still be a Customer.
I'll simply add, not to scare the OP, that out in the real world there are DVD's that have a top and bottom... that is two movies on one disk!
----------------------------------------------------
How to post forum questions to get the best help
34
Quote
Go
Post reply
Like
265
Add to Briefcase
Post quoted reply
Permissions
You
can't
post new topics.
You
can't
post topic replies.
You
can't
post new polls.
You
can't
post replies to polls.
You
can't
edit your own topics.
You
can't
delete your own topics.
You
can't
edit other topics.
You
can't
delete other topics.
You
can't
edit your own posts.
You
can't
edit other posts.
You
can't
delete your own posts.
You
can't
delete other posts.
You
can't
post events.
You
can't
edit your own events.
You
can't
edit other events.
You
can't
delete your own events.
You
can't
delete other events.
You
can't
send private messages.
You
can't
send emails.
You
can
read topics.
You
can't
vote in polls.
You
can't
upload attachments.
You
can
download attachments.
You
can't
post HTML code.
You
can't
edit HTML code.
You
can't
post IFCode.
You
can't
post JavaScript.
You
can
post emoticons.
You
can't
post or upload images.
Select a forum
SQL Server 2017
SQL Server 2017 - Administration
SQL Server 2017 - Development
SQL Server 2016
SQL Server 2016 - Administration
SQL Server 2016 - Development and T-SQL
SQL Server 2014
Administration - SQL Server 2014
Development - SQL Server 2014
SQL Server 2012
SQL 2012 - General
SQL Server 2012 - T-SQL
SQL Server vNext
SQL Server 15 - Administration
SQL Server 15 - Development
SQL Server 2008
SQL Server 2008 - General
T-SQL (SS2K8)
June 2007 CTP
Working with Oracle
July CTP
SQL Server Newbies
Security (SS2K8)
SQL Server 2008 High Availability
SQL Server 2008 Administration
Data Corruption (SS2K8 / SS2K8 R2)
SQL Server 2008 Performance Tuning
Cloud Computing
SQL Azure - Development
SQL Azure - Administration
Amazon AWS and other cloud vendors
General Cloud Computing Questions
CosmosDB
Azure Data Lake
Azure Machine Learning
Azure Data Factory
Reporting Services
Reporting Services
Reporting Services 2005 Administration
Reporting Services 2005 Development
Reporting Services 2008/R2 Administration
Reporting Services 2008 Development
SSRS 2012
SSRS 2014
SSRS 2016
Programming
Connecting
General
SMO/RMO/DMO
XML
Service Broker
Powershell
Testing
TFS/Data Dude/DBPro
SSDT
Continuous Integration, Deployment, and Delivery
R Services and R Language
Data Warehousing
Integration Services
Strategies and Ideas
Analysis Services
Data Transformation Services (DTS)
Performance Point
Data Mining
PowerPivot
R language
Machine Learning
Database Design
Disaster Recovery
Design Ideas and Questions
Relational Theory
Hardware
Virtualization
Security and Auditing
SQLServerCentral.com
Anything that is NOT about SQL!
Contests!
Editorials
SQLServerCentral.com Announcements
SQLServerCentral.com Website Issues
Suggestions
Tag Issues with Content
Podcast Feedback
SQLServerCentral.com Test Forum
Articles Requested
SQL Server 2005
Administering
Backups
Business Intelligence
CLR Integration and Programming.
Data Corruption
Development
Working with Oracle
SQL Server 2005 Compact Edition
SQL Server 2005 General Discussion
SQL Server 2005 Security
SQL Server 2005 Strategies
SS2K5 Replication
SQL Server Express
SQL Server 2005 Performance Tuning
SQL Server 2005 Integration Services
T-SQL (SS2K5)
SQL Server Newbies
SQL Server 7,2000
Administration
Backups
Data Corruption
General
Globalization
In The Enterprise
Working with Oracle
Security
Strategies
SQL Server Newbies
Service Packs
SQL Server CE
Performance Tuning
Replication
Sarbanes-Oxley
T-SQL
SQL Server Agent
SQL Server and other platforms
MySQL
Oracle
PostgreSQL
DB2
SQL Server and Sharepoint
Older Versions of SQL (v6.5, v6.0, v4.2)
Older Versions of SQL (v6.5, v6.0, v4.2)
Career
Certification
Employers and Employees
Events
Job Postings
Resumes and Job Hunters
Presentations and Speaking
Retired Members
Testing Center
SQL Server Security Skills
Question of the Day (QOD)
Microsoft Access
Microsoft Access
Products and Books
Third Party Products
CA
SQLCentric
Extreme Technologies.
Innovartis
Embarcadero
SQL Sentry
Sonasoft
Golden Gate Software
Lumigent
Red Gate Software
Quest Software
ApexSQL
Idera
Discussions about Books
Discuss Programming Books
Discuss XML Books
Discuss T-SQL Books
Discuss Data Warehousing Books
Discuss DTS Books
Discuss SQL Server 7.0 Books
Discuss SQL Server 2000 Books
Notification Services
Administration
Article Discussions
Future Versions
SQL 12
Narrow your search by forum
Explore
Home
Latest
Popular
Calendar
Members
Who's on
Moderators
Forums
SQL Server 2017
SQL Server 2017 - Administration
SQL Server 2017 - Development
SQL Server 2016
SQL Server 2016 - Administration
SQL Server 2016 - Development and T-SQL
SQL Server 2014
Administration - SQL Server 2014
Development - SQL Server 2014
SQL Server 2012
SQL 2012 - General
SQL Server 2012 - T-SQL
SQL Server vNext
SQL Server 15 - Administration
SQL Server 15 - Development
SQL Server 2008
SQL Server 2008 - General
T-SQL (SS2K8)
June 2007 CTP
Working with Oracle
July CTP
SQL Server Newbies
Security (SS2K8)
SQL Server 2008 High Availability
SQL Server 2008 Administration
Data Corruption (SS2K8 / SS2K8 R2)
SQL Server 2008 Performance Tuning
Cloud Computing
SQL Azure - Development
SQL Azure - Administration
Amazon AWS and other cloud vendors
General Cloud Computing Questions
CosmosDB
Azure Data Lake
Azure Machine Learning
Azure Data Factory
Reporting Services
Reporting Services
Reporting Services 2005 Administration
Reporting Services 2005 Development
Reporting Services 2008/R2 Administration
Reporting Services 2008 Development
SSRS 2012
SSRS 2014
SSRS 2016
Programming
Connecting
General
SMO/RMO/DMO
XML
Service Broker
Powershell
Testing
TFS/Data Dude/DBPro
SSDT
Continuous Integration, Deployment, and Delivery
R Services and R Language
Data Warehousing
Integration Services
Strategies and Ideas
Analysis Services
Data Transformation Services (DTS)
Performance Point
Data Mining
PowerPivot
R language
Machine Learning
Database Design
Disaster Recovery
Design Ideas and Questions
Relational Theory
Hardware
Virtualization
Security and Auditing
SQLServerCentral.com
Anything that is NOT about SQL!
Contests!
Editorials
SQLServerCentral.com Announcements
SQLServerCentral.com Website Issues
Suggestions
Tag Issues with Content
Podcast Feedback
SQLServerCentral.com Test Forum
Articles Requested
SQL Server 2005
Administering
Backups
Business Intelligence
CLR Integration and Programming.
Data Corruption
Development
Working with Oracle
SQL Server 2005 Compact Edition
SQL Server 2005 General Discussion
SQL Server 2005 Security
SQL Server 2005 Strategies
SS2K5 Replication
SQL Server Express
SQL Server 2005 Performance Tuning
SQL Server 2005 Integration Services
T-SQL (SS2K5)
SQL Server Newbies
SQL Server 7,2000
Administration
Backups
Data Corruption
General
Globalization
In The Enterprise
Working with Oracle
Security
Strategies
SQL Server Newbies
Service Packs
SQL Server CE
Performance Tuning
Replication
Sarbanes-Oxley
T-SQL
SQL Server Agent
SQL Server and other platforms
MySQL
Oracle
PostgreSQL
DB2
SQL Server and Sharepoint
Older Versions of SQL (v6.5, v6.0, v4.2)
Older Versions of SQL (v6.5, v6.0, v4.2)
Career
Certification
Employers and Employees
Events
Job Postings
Resumes and Job Hunters
Presentations and Speaking
Retired Members
Testing Center
SQL Server Security Skills
Question of the Day (QOD)
Microsoft Access
Microsoft Access
Products and Books
Third Party Products
CA
SQLCentric
Extreme Technologies.
Innovartis
Embarcadero
SQL Sentry
Sonasoft
Golden Gate Software
Lumigent
Red Gate Software
Quest Software
ApexSQL
Idera
Discussions about Books
Discuss Programming Books
Discuss XML Books
Discuss T-SQL Books
Discuss Data Warehousing Books
Discuss DTS Books
Discuss SQL Server 7.0 Books
Discuss SQL Server 2000 Books
Notification Services
Administration
Article Discussions
Future Versions
SQL 12
SQLServerCentral
Register
Search
Narrow your search by forum
Unthreaded, ascending
Unthreaded, descending
Subscribe to topic
Print topic
RSS feed
Go to topics forum
Jump to page
Jump to page
Copyright © 2002-2019 Redgate. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.