Advanced Challenge:
Currently, the contents of the Category column in the BOOKS table are the actual name for each category. This structure presents a problem if one user enters COMPUTER for the Computer category and another user enters COMPUTERS. To avoid this and the other problems that might occur, the database designers have decided to create a CATEGORY table containing a code and description of each category. The structure for the CATEGORY table should be as follows:
|
Column Name
|
Datatype
|
Width
|
Constrains
|
|
CATCODE
|
VARCHAR2
|
3
|
PRIMARY KEY
|
|
CATDESC
|
VARCAR2
|
11
|
NOT NULL
|
The data of the CATEGORY table is as follows:
|
CATCODE
|
CATDESC
|
|
BUS
|
BUSINESS
|
|
CHN
|
CHILDREN
|
|
COK
|
COOKING
|
|
COM
|
COMPUTER
|
|
FAL
|
FAMILY LIFE
|
|
FIT
|
FITNESS
|
|
SHE
|
SELF HELP
|
|
LIT
|
LITERATURE
|
Required:
1. Create the CATEGORY table and populate it with the given data. Save the changes permanently.
2. Add a column to the BOOKS table called Catcod.
3. Add a FOREIGN KEY constraint that requires all category codes entered in the BOOKS table, based on each book's current Category value.
4. Verify that the correct categories have been assigned in the BOOKS table, and save the changes permanently.
5. Delete the Category column from the BOOKS table.