TYPE ANSWER!!!! Please 66. The following tables provide a very small portion of

Question

TYPE ANSWER!!!! Please

66. The following tables provide a very small portion of the data that will be kept in the database. These data need to be inserted into the database for testing purposes. Write the INSERT commands necessary to place the following data in the tables that were created in problem 65.

Note: Based on the referential integrity constraints, you must identify a correct sequence in which to insert the data into the tables. The order listed above will not work because it shows inserting rows into DETAILRENTAL before the corresponding rows have been inserted into VIDEO. Due to referential integrity constraints, the rows must be inserted in the table contributing it's PK as a FK before the related rows can be inserted into the table containing the FK.

This was question 65.

EliteVideo is a startup company providing concierge DVD kiosk service in upscale neighborhoods. EliteVideo can own several copies (VIDEO) of each movie (MOVIE). For example, the store may have ten copies of the movie “Titanic”. “Titanic” would be one MOVIE and each copy would be a VIDEO. A rental transaction (RENTAL) involves one or more videos being rented to a member (MEMBERSHIP). A video can be rented many times over its lifetime, therefore, there is a M:N relationship between RENTAL and VIDEO. DETAILRENTAL is the bridge table to resolve this relationship. The complete ERD is shown in the figure below.

Write the SQL code to create the table structures for the entities shown in the ERD above. The structures should contain the attributes specified in the ERD. Use data types that would be appropriate for data that will need to be stored in each attribute. Enforce primary key and foreign key constraints as indicated by the ERD. Choose either Microsoft Access or MySQL and indicate your choice in the document. Write SQL statements using the syntax and data types appropriate for the DBMS you choose.

Explanation / Answer

Ok. I am writing the sequence in which the data must be inserted in to the given tables in order to maintain the referential integrity.

1) First of all, fill the MEMBERSHIP table where MEM_NUM is the primary key.

INSERT INTO MEMBERSHIP VALUES (102, 'Tami', 'Dawson', '2632 Takli Circle', 'Norene', 'TN', 37136, 11);
... and so on for other rows

2) Then RENTAL table can be filled up. Here, RENT_NUM is the primary key and MEM_NUM is the foreign key which refers to the MEMBERSHIP table, which we have filled in

the first step.

INSERT INTO RENTAL VALUES (1001, 01-MAR-13, 103);
... similarly for other rows

3) Now, DETAILRENTAL does not contain a primary key with a single column. Here RENT_NUM and VID_NUM both columns combined make the primary key for the table. Where

RENT_NUM and VID_NUM are the primary keys of the tables RENTAL and VIDEO respectively.

We have already inserted the data into the RENTAL table in step 2, but VIDEO table has not been filled up yet. But the VIDEO table contains the MOVIE_NUM as the

foreign key referencing the MOVIE table. In turn, MOVIE table also contains a foreign key PRICE_CODE which refers to the table PRICE.

So, next we will have to insert the data in to the table PRICE.

INSERT INTO PRICE VALUES (1, 'Standard', 2, 1);
... and so on for other rows

4) Now, data can be inserted in to the table MOVIE

INSERT INTO MOVIE VALUES (1234, 'The Cesar Family Christmas', 2011, 39.95, 'FAMILY', 2);
... do the same for other rows

5) Next insert the data in to the table VIDEO

INSERT INTO VIDEO VALUES (54321, 18-JUN-12, 1234)
... similarly for other rows

6) At last, the data can be inserted in to the table DETAILRENTAL

INSERT INTO DETAILRENTAL (RENT_NUM, VID_NUM, DETAIL_FEE, DETAIL_DUEDATE, DETAIL_RETURNDATE) VALUES (1001, 34342, 2, 04-MAR-13, 02-MAR-13);
INSERT INTO DETAILRENTAL VALUES (1001, 61353, 2, 04-MAR-13, 03-MAR-13, 1);

Note: In 6th step, I have written two INSERT statement, first one with the coulmn names, as first row in the DETAILRENTAL table does not contain any value for the last

column with the name DETAIL_DAILYLATEFEE. So, you have to specify the column names you want to insert values into.
Otherwise, if you are inserting values in to all the columns, you can skip the column names from the INSERT statement. I have followed the same syntax in all the

steps.

Hence, at last sequence of insertion in to the tables can be summarized as follows:

1) MEMBERSHIP
2) RENTAL
3) PRICE
4) MOVIE
5) VIDEO
6) DETAILRENTAL

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.