Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Design Database In MS SQL

Posted on 2011-02-25
16
Medium Priority
?
321 Views
Last Modified: 2012-05-11
I am designing a database based on netflix could someone give me idea of what the entities and attributes of the table would be?  How would I set up it according to specifics below. Thanks.

The cudstomer signs up for one of the program (3 at a time program)
The customer will be able to add to their movie que
Then requests movies from their que
customer watches and returns movie to netflix
netflix mails out additinal movies to customer
customer closes account and does not return movie and is charged a few.
0
Comment
Question by:alpha100
  • 11
  • 3
  • 2
16 Comments
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 2000 total points
ID: 34985862
By reading your question - I feel, you need these much tables. It is clear about foreign key required from the below table structures - Go through and let me know.

Table1 - tblCustomer
Columns - CustomerID (INT),  CustomerName (VARCHAR(50)), Status (BIT)

Table2 - tblProgram
Columns - ProgramID (INT), ProgramName (VARCHAR(50)), Status (BIT)

Table3 - tblCustomerPrograms
Columns - CustomerProgramsID (INT), CustomerID (INT), ProgramID (INT), Status (BIT)

Table4 - tblMovies
Columns - MovieID (INT), MovieName (VARCHAR(50)), Status (BIT)

Table5 - tblCustomerMovieRequests
Columns - CustomerMovieRequestsID (INT), RequestDate (DATETIME), ReturnDate (DATETIME), CustomerID (INT), MovieID (INT), IsReturned (BIT), MovieCharge (DECIMAL(18,2))

Raj
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 34986353
alpha100, you're just describing workflows, not entities you want to model. These workflows imply certain data to be stored, but also business logic how to process the data.

At one point you say "returns movie to netflix". Well, netflix is video on demand isn't it? Or are you talking about snail mail, DVDs here? Then what's surely missing in Raj's design is a table for adresses and tables maintinaing where to a dvd has been sent and whether it got there and you got a receipt and wheter it was returned.

Entities you have are of course Programs, the Customers subscribing to one of them, and your Movies in the first place. Then you have Orders (the Request Queue of a Customer). You may have data related to USPS or whatever you choose to snail mail DVDs. And you have additional data to each entity, eg CustomerAdresses, should perhaps be stored seperate, if it's not jus email adress. And Orderstatus reflecting what happend with an order, sent, returned, closed. Adn some log tables what happend with the data, who rocessed requests, sent DVDs etc.

About your workflows: You have Employees, which whould have their login into your app, that may not mean additional tables, but I'd suggest loggin what who did in the data. You may need to determine what's legel there. Also you need some process for billing customers quitting their subscription for not returned DVDs. Return of DVDs should trigger other DVDs to be sent out processing the request queue. You can have data about similar moviews for recommendations and send out these to customers, especially if their queue get's low.

Don't forget the complaint department and data about it.

Overall, you can start with a simple structure like Raj gives you, but actually there is much more to do in my oppinion and it goes beyond the normal limits of a forum thread to deliver a database model for video rental.

Bye, Olaf.
0
 

Author Comment

by:alpha100
ID: 34990250
I have set up  my table and entered the data.  How do I perform the following transactions

List the names of all movies that are currently sold out.  A movie is sold out if all copeis of the move are currently rented and not yet returned.

Add a new customer to the database and sign them up for the a program  for a specific price.  This is suppose to be a transction.    What is the Sql script for each of this processes?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 2000 total points
ID: 34990343
>> List the names of all movies that are currently sold out

(Based on the table structure above)

SELECT
c.CustomerName As Customer,
m.MovieName As Movie,
r.RequestDate As SoldDate,
r.MovieCharge As Amount
FROM tblMovies m INNER JOIN tblCustomerMovieRequests r ON m.MovieID = r.MovieID
INNER JOIN tblCustomer c ON c.CustomerID = r.CustomerID
WHERE ISNULL(IsReturned, 0) = 0 AND MovieCharge IS NOT NULL

Raj
0
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 2000 total points
ID: 34990349
>> Add a new customer to the database and sign them up for the a program  for a specific price.

You need to ihclude one more column for the "specific price"

Table3 - tblCustomerPrograms
Columns - CustomerProgramsID (INT), CustomerID (INT), ProgramID (INT), Price (DECIMAL(18,2)), Status (BIT)

Raj
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34990379
One correction

You need to set the primary keys of all tables IDENTITY property - This will automatically increment the value from 1

CREATE Table tblCustomer
(
CustomerID INT IDENTITY(1,1),
CustomerName VARCHAR(50),
Status BIT
)

CREATE Table  tblProgram
(
ProgramID INT IDENTITY(1,1), ProgramName VARCHAR(50),
Status BIT
)

CREATE Table tblCustomerPrograms
(
CustomerProgramsID INT IDENTITY(1,1), CustomerID INT, ProgramID INT,
Price DECIMAL(18,2), Status BIT
)

CREATE Table tblMovies
(
MovieID INT IDENTITY(1,1), MovieName VARCHAR(50),
Status BIT
)


CREATE Table tblCustomerMovieRequests
(
CustomerMovieRequestsID INT IDENTITY(1,1), RequestDate DATETIME,
ReturnDate DATETIME,
CustomerID INT,
MovieID INT, IsReturned BIT,
MovieCharge DECIMAL(18,2)
)

Raj
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34990389

One more correction in table structure including DEFAULT values and NOT NULL

CREATE Table tblCustomer
(
CustomerID INT IDENTITY(1,1),
CustomerName VARCHAR(50) NOT NULL,
Status BIT DEFAULT(1)
)

CREATE Table  tblProgram
(
ProgramID INT IDENTITY(1,1), ProgramName VARCHAR(50) NOT NULL,
Status BIT DEFAULT(1)
)

CREATE Table tblCustomerPrograms
(
CustomerProgramsID INT IDENTITY(1,1), CustomerID INT NOT NULL,
ProgramID INT NOT NULL,
Price DECIMAL(18,2), Status BIT DEFAULT(1)
)

CREATE Table tblMovies
(
MovieID INT IDENTITY(1,1), MovieName VARCHAR(50) NOT NULL,
Status BIT DEFAULT(1)
)


CREATE Table tblCustomerMovieRequests
(
CustomerMovieRequestsID INT IDENTITY(1,1), RequestDate DATETIME NOT NULL,
ReturnDate DATETIME,
CustomerID INT NOT NULL,
MovieID INT NOT NULL, IsReturned BIT DEFAULT(0),
MovieCharge DECIMAL(18,2)
)

Raj
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34990392
>> Add a new customer to the database

INSERT Query with sample data

INSERT INTO tblCustomer (CustomerName) VALUES
('alpha100')

Raj
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34990399
>> and sign them up for the a program  for a specific price

Assuming in GUI, end-user is selecting  Customer & Program

CustomerID & ProgramID need to pass to query

INSERT INTO  tblCustomerPrograms (CustomerID, ProgramID, Price) VALUES
(1, 100, 2000)

Raj
0
 

Author Comment

by:alpha100
ID: 34991413
Raj, thanks so much for your assistance.  I have several more questions.  So status is at least one of the foreign keys in all tables exept the customer table?  Thanks.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34991429
>> So status is at least one of the foreign keys in all tables exept the customer table?

I am clear what you meant here ?

If you meant the 'Status' column - It is to say whether the record is in active or deleted status
Status = 1 (Active)
Status = 0 (Deleted)

Raj
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34991436

You need to set FOREIGN KEYS for
tblCustomerPrograms.ProgramID -> tblProgram.ProgramID
tblCustomerMovieRequests.CustomerID -> tblCustomer.CustomerID
tblCustomerMovieRequests.MovieID -> tblMovies.MovieID

Raj
0
 

Author Comment

by:alpha100
ID: 34991459
Can you assist me with the sql queries for the questions below:

Find the titles of movies that are made by director "Spielberg" or by“Night Shyamalan.” (a query)

Customer X wants a list of the titles of all the DVD’s that he/she has never rented. (a query)

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)

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)

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)

Add a new customer to the database and sign them up for the $11.95 two-at-a-time program. (a transaction)

Switch a customer from the two-at-a-time program to the three-at-a- time program. (a transaction)

Cancel a customer from the two-at-a-time program. (a transaction)

Add a movie to a customer's queue so that the newly added movie will be the next movie the customer receives. (a transaction)

Identify all customers who have currently rented more than one copy of
a title. (a query)
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34991492
@alpha100,

We are allowed to give answers to homework questions!

Try it yourself and come up with your query. (It will only help you to learn) Then we can assist you on that.

I request you to close this question as I believe you got the answer to this question (database structure) and open a new question.

Regards
Raj
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34991495
Oops! My typing speed caused this issue!!!!

I meant
We are NOT allowed to give answers to homework questions!

Raj
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 35010022
Well, Raj,

this whole thread expands to building a whole application, at least the database layer. How much provision do you pay, alpha100?

Bye, Olaf.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question