Question

Answer each of the following questions using the Colonial Adventure Tours data shown in Figures 1-4 through 1-8 in Chapter 1. No computer work is required.

Using the types of entities found in the Colonial Adventure Tours database (trips, guides, customers, and reservations), create an example of a table that is in first normal form but not in second normal form, and an example of a table that is in second normal form but not in third normal form. In each case, justify your answers and show how to convert to the higher forms.

Step-by-step solution
Step 1 of 3
COLONIAL ADVENTURE TOURS DATABASE :

CAT is a small business that organizes tours to New England. Likewise the TAL Distributors, CAT has decided to store its data in a database. So has developed the COLONIAL ADVENTURE TOURS DATABASE that avoids the data loss and to ensure that the data is current and accurate.

Step 2 of 3
Refer to Fig. 1-4 through Fig. 1-8 in Chapter 1 for the related database and the tables as TRIP, GUIDE, CUSTOMER, RESERVATION.

• It is stated that an example table is to be created such that it is in the first normal form but not in the second normal form. And another example that is in the second normal form but not in the third normal form.

First Normal Form of Normalization :

A relation is said to be in the first normal form if and only if the domain of each attribute contains only atomic or unique values, and that the value of each attribute contains only a single value from the respective domain.

Second Normal Form of Normalization :

A table or in general a relation is said to be in a second normal form if and only if it is in the first normal form and that no non-key column is dependent on only a portion of the primary key.

Third Normal Form of Normalization :

A table is said to be in the third normal form if and only if it is in the second normal form and if the only determinants it contains are the candidate keys.

Step 3 of 3
Based on the tables that has been provided it is clear that many number of solutions are possible for the creation of the sample tables based on the requirements, but for example, considering one possible solution :

• 1NF but not 2NF :

TRIPGUIDES (TRIP_ID, GUIDE_NUM, TRIP_NAME)

Here, the table consists of three columns as the TRIP_ID, GUIDE_NUM, TRIP_NAME where the id and the number serves as the primary keys for the table. These generally have only the unique values based on the id.

Conversion to 2NF :

TRIP (TRIP_ID, TRIP_NAME)

TRIPGUIDES (TRIP_ID, GUIDE_NUM)

Now when observed, the base table has been divided into two based on the keys so that the data could be mutually dependant on only the primary key itself but not on the other columns. And that both the tables are in the 1NF.

• 2NF but not 3NF :

RESERVATION (RESERVATION_ID, TRIP_ID, OWNER_NUM,

LAST_NAME, FIRST_NAME)

Here, the table RESERVATION is built with the columns RESERVATION_ID, TRIP_ID, OWNER_NUM, LAST_NAME, FIRST_NAME of which the RESERVATION_ID has the primary key.

Conversion to 3NF :

OWNER (OWNER_NUM, LAST_NAME, FIRST_NAME)

RESERVATION (RESERVATION_ID, TRIP_ID, OWNER_NUM)

The respective table has been converted to the 3NF such that the two tables will have the separate keys or the columns but the data would be related.

Answer 1

Answer:In this scenario, an example of a table that is in first normal form but not in second normal form is the TRIPGUIDES table, which contains the columns TRIP_ID, GUIDE_NUM, and TRIP_NAME. This table is in first normal form because the domain of each attribute contains only atomic or unique values, and each attribute contains only a single value from the respective domain. However, the table is not in second normal form because the non-key column TRIP_NAME is dependent on only a portion of the primary key (TRIP_ID).

To convert the TRIPGUIDES table to second normal form, the table can be split into two tables: TRIP and TRIPGUIDES. The TRIP table would contain the columns TRIP_ID and TRIP_NAME, and the TRIPGUIDES table would contain the columns TRIP_ID and GUIDE_NUM. This way, the data in the TRIPGUIDES table would only be dependent on the primary key (TRIP_ID) and not on any non-key columns.

An example of a table that is in second normal form but not in third normal form is the RESERVATION table, which contains the columns RESERVATION_ID, TRIP_ID, OWNER_NUM, LAST_NAME, and FIRST_NAME. This table is in second normal form because it is in first normal form and no non-key columns are dependent on only a portion of the primary key (RESERVATION_ID). However, the table is not in third normal form because the non-key columns LAST_NAME and FIRST_NAME are dependent on the non-primary key column OWNER_NUM, which is not a candidate key.

To convert the RESERVATION table to third normal form, the table can be split into two tables: OWNER and RESERVATION. The OWNER table would contain the columns OWNER_NUM, LAST_NAME, and FIRST_NAME, and the RESERVATION table would contain the columns RESERVATION_ID, TRIP_ID, and OWNER_NUM. This way, the data in the RESERVATION table would only be dependent on the candidate keys (RESERVATION_ID and OWNER_NUM) and not on any non-key columns.

Step-by-step explanation: