Solved

Design Database In MS SQL

Posted on 2011-02-25
16
308 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 500 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 29

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
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 500 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 500 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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 29

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now