SQL Advanced Challenge Currently, the contents of the Category column in the BOO

Question

SQL

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 other problems that might occur, the database designers have decided to create a CATEGORY table containing a code and description for each category. The structure for the CATEGORY table should be as follows: Width Column Name CATCODE CATDESC Datatype VARCHAR2 Constraints PRIMARY KEY NOT NULL VARCHAR2 The data for the CATEGORY table is as follows: CATCODE CATDESC BUS BUSINESS CHN CHILDREN COK COOKING COM COMPUTER FAL FAMILY LIFE FIT FITNESS SEH SELF HELP LITERATURE LIT Required: Create the CATEGORY table and populate it with the given data. Save the changes permanently. Add a column to the BOOKS table called Catcode. Add a FOREIGN KEY constraint that requires all category codes entered in the BOOKS table to already exist in the CATEGORY table. Set the Catcode values for the existing rows in the BOOKS table, based on each book's current Category value. Verify that the correct categories have been assigned in the BOOKS table, and save the changes permanently. Delete the Category column from the BOOKS table. Only save the changes here Don't worry about verifying

Explanation / Answer

Answer)

Creating CATEGORY table:

create table CATEGORY (CATCODE varchar2(3) PRIMARY KEY, CATDESC varchar2(11) not null);

Adding values to the table:

insert into CATEGORY values('BUS','BUSINESS');

insert into CATEGORY values('CHN','CHILDREN');

insert into CATEGORY values('COK','COOKING');

insert into CATEGORY values('COM','COMPUTER');

insert into CATEGORY values('FAL','FAMILY LIFE');

insert into CATEGORY values('FIT','FITNESS');

insert into CATEGORY values('SEH','SELF HELP');

insert into CATEGORY values('LIT','LITERATURE');

Adding a column to Books table called CATCODE:

ALTER TABLE BOOKS

ADD Catcode varchar2(3);

Adding foreign keys between the two tables:

ALTER TABLE Books

ADD FOREIGN KEY (Catcode) REFERENCES CATEGORY(Catcode);

Dropping the Column CATCODE from table Books:

ALTER TABLE Books

DROP COLUMN Catcode;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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