Design Database In MS SQL

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.
CindyApplication Systems Analyst IIAsked:
Who is Participating?
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
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
 
Olaf DoschkeSoftware DeveloperCommented:
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
 
CindyApplication Systems Analyst IIAuthor Commented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
>> 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
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
>> 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
 
Rajkumar GsSoftware EngineerCommented:
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
 
Rajkumar GsSoftware EngineerCommented:

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
 
Rajkumar GsSoftware EngineerCommented:
>> Add a new customer to the database

INSERT Query with sample data

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

Raj
0
 
Rajkumar GsSoftware EngineerCommented:
>> 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
 
CindyApplication Systems Analyst IIAuthor Commented:
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
 
Rajkumar GsSoftware EngineerCommented:
>> 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
 
Rajkumar GsSoftware EngineerCommented:

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

Raj
0
 
CindyApplication Systems Analyst IIAuthor Commented:
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
 
Rajkumar GsSoftware EngineerCommented:
@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
 
Rajkumar GsSoftware EngineerCommented:
Oops! My typing speed caused this issue!!!!

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

Raj
0
 
Olaf DoschkeSoftware DeveloperCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.