I have a couple of questions. I am creating a table in SQL Server 2014. The tabl
ID: 672875 • Letter: I
Question
I have a couple of questions.
I am creating a table in SQL Server 2014. The table is labeled tblRental and the columns are MovieID, CustomerID, and DueDate. The instructions say that the tblRental is a composite key that includes MovieID and CustomerID. It also says that MovieID and CustomerID are foreign keys in tblRental, each referencing their own tables. How can the table be a composite key and how do I write this?
CREATE TABLE tblRental (
MovieID int NOT NULL,
CustomerID int NOT NULL,
DueDate DATE NOT NULL
PRIMARY KEY (MovieID, CustomerID));
If I write the code this way, then I am not referencing that MovieID is the primary key in the tblDVD table and that the CustomerID is the primary key in the tblCustomer table.
The second part to this is that there is no data for tblRental, so I need to use the keys that are created to create 10 rentals to 6 different customers. Why do I have to use the keys to create these? Can’t I just create them in the tblCustomer table and tblDVD table?
Explanation / Answer
The tableRental has a composity key as primary key. The composite key means it contains two or more fields in the table. In tblRental, primary key is a
composite key of two fields movieId and customerID. It means the primary key of tblRental is a
composite key. That means any row in the tblRental is unique only with the combination of MovieID and CustomerID
For a give movieID ,there can be multiple CustomerIDs attached but there is only one combination of MovieID and CustomerID.
PRIMARY KEY (MovieID, CustomerID) - it's correct way to define composite primary key.
In the above compoiste key both MovieID and CustomerID are foreign keys.
MovieID is the primary key in tblDVD table and CustomerID is primary key in the tblCustomer table.
b)The second part to this is that there is no data for tblRental, so I need to use the keys that are created to create 10 rentals to 6 different customers. Why do I have to use the keys to create these? Can’t I just create them in the tblCustomer table and tblDVD table?
If there is no data in the tblMovie and tblCustomer tables, then we can not populate tblRental as it has foreign keys
referencing these two tables. While populating tblRental, we need to use MovieID from tblDVD and CustomerID from tblCustomer as both are foreign keys.
Before populating tblRental, you need populate tblDVD and tblCustomers first.
Q) Can’t I just create them in the tblCustomer table and tblDVD table?
If just create entries in tblCustomer and tblDVD table, they contain information related to that table only.
It means tblCustomer contains all the customer information and tblDVD contains all the information related to movies.
But in both these tables there is no mapping saying that a particular customer 'X' rented a movie 'Y'.
This mapping is achieved by tblRental through composite key. tblRental mapping says that customer
X took movie Y for rent. So we through this combination who rented which movie and what is due date for the customer to return DVD
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.