Database Tutorial

The Video store is composed of ten tables. Some of the tables have been simplified to limit the scope of labs. However, you have two infrastructure tables:

• COMMON_LOOKUP
• SYSTEM_USER

You need to populate the data in the infrastructure tables before you enter information in the other eight tables that support the Video Store Database. There are three use cases for entering data into the eight tables, the use cases are:

• Entering, changing, and querying the ITEM table data.
• Entering, changing, and querying member accounts, which inserts data into the MEMBER, CONTACT, ADDRESS, STREET_ADDRESS, and TELEPHONE tables.
• Entering, changing, and querying the entry of rentals and rental items in the RENTAL and RENTAL_ITEM tables.

You need to learn how to read the following ERD well enough to find the three use cases. Click on the reduced ERD to see the drawing as a full scale image.

The following describes the infrastructure and video store use cases from the least dependent to the most dependent:

• Infrastructure Rule for SYSTEM_USER table:

  Insert rows into the SYSTEM_USER table. When there are no rows in the table, you must insert a row that uses the COMMON_LOOKUP_ID value of the first row as both the CREATED_BY and LAST_UPDATED_BY values. After inserting at least one row in the table, a subsequent row may be inserted with either the COMMON_LOOKUP_ID value of the first row or a subsequent row. That requirement occurs because the foreign key constraints on the CREATED_BY and LAST_UPDATED_BY columns requires that you insert a value already found in the list of COMMON_LOOKUP_ID column values.

• Infrastructure Rule for COMMON_LOOKUP table:

  Inserting rows into the COMMON_LOOKUP table requirers that there must be at least one row in the SYSTEM_USER table because the foreign key constraints impose the same type of restriction as those in the SYSTEM_USER table. Therefore, you can only insert a row in the COMMON_LOOKUP table after you have insert rows in the SYSTEM_USER table.

• Video Store Rule for ITEM table:

  Inserting rows into the ITEM table has two dependencies. They are:

  • You must have rows in the SYSTEM_USER table to find and copy a valid primary key value in the SYSTEM_USER_ID column, which you can then insert into the CREATED_BY and LAST_UPDATED_BY columns as a valid foreign key value.
  • You must have rows in the COMMON_LOOKUP table to find and copy a valid primary key value in the COMMON_LOOKUP_ID column, which you can then insert into the MEMBER_TYPE and CREDIT_CARD_TYPE columns as valid foreign key values.
• Video Store Rule for MEMBER, CONTACT, ADDRESS, STREET_ADDRESS, and TELEPHONE tables:

  Inserting rows into the MEMBER, CONTACT, ADDRESS, STREET_ADDRESS, and TELEPHONE tables is a workflow because it has a series of INSERT statements that interdependent. Each of the tables individually also has dependencies on both of the SYSTEM_USER and COMMON_LOOKUP infrastructure tables. The interdependent rules are:

  1. You must first insert a row in the MEMBER table. You call the .NEXTVAL pseudocolumn for the MEMBER_S1 sequence value to generate a MEMBER_ID column value as the primary key column.
  2. After inserting into the MEMBER table, you insert a row into the CONTACT table by using:
     • The .NEXTVAL pseudocolumn for the CONTACT_S1 sequence value to generate a CONTACT_ID column value as the primary key column value of the CONTACT table.
     • The .CURRVAL pseudocolumn of the MEMBER_S1 sequence value to generate a MEMBER_ID column value as the foreign key value from the MEMBER table.
     • You insert into the CREATED_BY and LAST_UPDATED_BY columns a valid foreign key value derived from the SYSTEM_USER_ID column of the SYSTEM_USER table.
     • You insert into the CONTACT_TYPE column a valid foreign key value derived from the COMMON_LOOKUP_ID column of the COMMON_LOOKUP table.
  3. After inserting into the CONTACT table, you insert a row into either the ADDRESS or TELEPHONE tables because they both have the same dependencies level in the use case. However, the ADDRESS table has a subsequent dependent table, which is the STREET_ADDRESS table. As a rule, you follow the patch with the most dependents. That means you should now insert a row into the ADDRESS table by using:
     • The .NEXTVAL pseudocolumn for the ADDRESS_S1 sequence value to generate a ADDRESS_ID column value as the primary key column value of the ADDRESS table.
     • The .CURRVAL pseudocolumn of the CONTACT_S1 sequence value to generate a CONTACT_ID column value as the foreign key value from the CONTACT table.
     • You insert into the CREATED_BY and LAST_UPDATED_BY columns a valid foreign key value derived from the SYSTEM_USER_ID column of the SYSTEM_USER table.
     • You insert into the CONTACT_TYPE column a valid foreign key value derived from the COMMON_LOOKUP_ID column of the COMMON_LOOKUP table.

  4. After inserting into the ADDRESS table, you insert a row into either the STREET_ADDRESS table by using:
     • The .NEXTVAL pseudocolumn for the STREET_ADDRESS_S1 sequence value to generate a STREET_ADDRESS_ID column value as the primary key column value of the STREET_ADDRESS table.
     • The .CURRVAL pseudocolumn of the ADDRESS_S1 sequence value to generate a ADDRESS_ID column value as the foreign key value from the ADDRESS table.
     • You insert into the CREATED_BY and LAST_UPDATED_BY columns a valid foreign key value derived from the SYSTEM_USER_ID column of the SYSTEM_USER table.
     • You insert into the CONTACT_TYPE column a valid foreign key value derived from the COMMON_LOOKUP_ID column of the COMMON_LOOKUP table.

  5. After inserting into the STREET_ADDRESS table, you insert a row into either the TELEPHONE table by using:
     • The .NEXTVAL pseudocolumn for the TELEPHONE_S1 sequence value to generate a TELEPHONE_ID column value as the primary key column value of the TELEPHONE table.
     • The .CURRVAL pseudocolumn of the CONTACT_S1 sequence value to generate a CONTACT_ID column value as the foreign key value from the CONTACT table.
     • You insert into the CREATED_BY and LAST_UPDATED_BY columns a valid foreign key value derived from the SYSTEM_USER_ID column of the SYSTEM_USER table.
     • You insert into the CONTACT_TYPE column a valid foreign key value derived from the COMMON_LOOKUP_ID column of the COMMON_LOOKUP table.

• Video Store Rule for RENTAL and RENTAL_ITEM tables:

  Inserting rows into the RENTAL and RENTAL_ITEM tables is a workflow because it has a series of INSERT statements that interdependent. Each of the tables individually also has dependencies on both of the SYSTEM_USER and COMMON_LOOKUP infrastructure tables. The interdependent rules are:

  1. You must first insert a row in the RENTAL table. You call the .NEXTVAL pseudocolumn for the RENTAL_S1 sequence value to generate a RENTAL_ID column value as the primary key column.

     The RENTAL table insert requires that you have the following:

     • You must have rows in the CONTACT table to find and copy a valid primary key value in the CONTACT_ID column, which you can then insert into the CUSTOMER_ID column as a valid foreign key value.
     • You must have rows in the SYSTEM_USER table to find and copy a valid primary key value in the SYSTEM_USER_ID column, which you can then insert into the CREATED_BY and LAST_UPDATED_BY columns as a valid foreign key value.
     • You must have rows in the COMMON_LOOKUP table to find and copy a valid primary key value in the COMMON_LOOKUP_ID column, which you can then insert into the MEMBER_TYPE and CREDIT_CARD_TYPE columns as valid foreign key values.
  2. After inserting into the RENTAL table, you insert a row into the RENTAL_ITEM table by using:
     • The .NEXTVAL pseudocolumn for the RENTAL_ITEM_S1 sequence value to generate a RENTAL_ITEM_ID column value as the primary key column value of the RENTAL_ITEM table.
     • The .CURRVAL pseudocolumn of the RENTAL_S1 sequence value to generate a RENTAL_ID column value as the foreign key value from the RENTAL table.
     • You must have rows in the SYSTEM_USER table to find and copy a valid primary key value in the SYSTEM_USER_ID column, which you can then insert into the CREATED_BY and LAST_UPDATED_BY columns as a valid foreign key value.
     • You must have rows in the COMMON_LOOKUP table to find and copy a valid primary key value in the COMMON_LOOKUP_ID column, which you can then insert into the MEMBER_TYPE and CREDIT_CARD_TYPE columns as valid foreign key values.