April 7, 2015 at 5:18 pm
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.
April 7, 2015 at 5:51 pm
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.
April 7, 2015 at 10:33 pm
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
Change is inevitable... Change for the better is not.
April 7, 2015 at 10:44 pm
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
Change is inevitable... Change for the better is not.
April 8, 2015 at 4:39 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 3, 2016 at 2:25 pm
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!
----------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply