Currently, the contents of the Category column in the BOOKS table are the actual
ID: 675283 • Letter: C
Question
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:
1. Create the CATEGORY table and populate it with the given data. Save the changes
2. permanently.
3. Add a column to the BOOKS table called Catcode.
4. Add a FOREIGN KEY constraint that requires all category codes entered in the BOOKS table to already exist in the CATEGORY table.
5. Set the Catcode values for existing rows in the BOOKS table, based on each book's current Category value.
6. Verify that the correct categories have been assigned in the BOOKS table, and save the changes permanently.
7. Delete the Category column from the BOOKS table
SQL DOC
VALUES ('F100','FIELDS','OSCAR');
INSERT INTO AUTHOR
VALUES ('W110','WILKINSON','ANTHONY');
CREATE TABLE Books
(ISBN VARCHAR2(10),
Title VARCHAR2(30),
PubDate DATE,
PubID NUMBER (2),
Cost NUMBER (5,2),
Retail NUMBER (5,2),
Discount NUMBER (4,2),
Category VARCHAR2(12),
CONSTRAINT books_isbn_pk PRIMARY KEY(isbn),
CONSTRAINT books_pubid_fk FOREIGN KEY (pubid)
REFERENCES publisher (pubid));
INSERT INTO BOOKS
VALUES ('1059831198','BODYBUILD IN 10 MINUTES A DAY','21-JAN-05',4,18.75,30.95, NULL, 'FITNESS');
INSERT INTO BOOKS
VALUES ('0401140733','REVENGE OF MICKEY','14-DEC-05',1,14.20,22.00, NULL, 'FAMILY LIFE');
INSERT INTO BOOKS
VALUES ('4981341710','BUILDING A CAR WITH TOOTHPICKS','18-MAR-06',2,37.80,59.95, 3.00, 'CHILDREN');
INSERT INTO BOOKS
VALUES ('8843172113','DATABASE IMPLEMENTATION','04-JUN-03',3,31.40,55.95, NULL, 'COMPUTER');
INSERT INTO BOOKS
VALUES ('3437212490','COOKING WITH MUSHROOMS','28-FEB-04',4,12.50,19.95, NULL, 'COOKING');
INSERT INTO BOOKS
VALUES ('3957136468','HOLY GRAIL OF ORACLE','31-DEC-05',3,47.25,75.95, 3.80, 'COMPUTER');
INSERT INTO BOOKS
VALUES ('1915762492','HANDCRANKED COMPUTERS','21-JAN-05',3,21.80,25.00, NULL, 'COMPUTER');
INSERT INTO BOOKS
VALUES ('9959789321','E-BUSINESS THE EASY WAY','01-MAR-06',2,37.90,54.50, NULL, 'COMPUTER');
INSERT INTO BOOKS
VALUES ('2491748320','PAINLESS CHILD-REARING','17-JUL-04',5,48.00,89.95, 4.50, 'FAMILY LIFE');
INSERT INTO BOOKS
VALUES ('0299282519','THE WOK WAY TO COOK','11-SEP-04',4,19.00,28.75, NULL, 'COOKING');
INSERT INTO BOOKS
VALUES ('8117949391','BIG BEAR AND LITTLE DOVE','08-NOV-05',5,5.32,8.95, NULL, 'CHILDREN');
INSERT INTO BOOKS
VALUES ('0132149871','HOW TO GET FASTER PIZZA','11-NOV-06',4,17.85,29.95, 1.50, 'SELF HELP');
INSERT INTO BOOKS
VALUES ('9247381001','HOW TO MANAGE THE MANAGER','09-MAY-03',1,15.40,31.95, NULL, 'BUSINESS');
INSERT INTO BOOKS
VALUES ('2147428890','SHORTEST POEMS','01-MAY-05',5,21.85,39.95, NULL, 'LITERATURE');
CREATE TABLE ORDERITEMS
( Order# NUMBER(4),
Item# NUMBER(2),
ISBN VARCHAR2(10),
Quantity NUMBER(3) NOT NULL,
PaidEach NUMBER(5,2) NOT NULL,
CONSTRAINT orderitems_pk PRIMARY KEY (order#, item#),
CONSTRAINT orderitems_order#_fk FOREIGN KEY (order#)
REFERENCES orders (order#) ,
CONSTRAINT orderitems_isbn_fk FOREIGN KEY (isbn)
REFERENCES books (isbn) ,
CONSTRAINT oderitems_quantity_ck CHECK (quantity > 0) );
INSERT INTO ORDERITEMS
VALUES (1000,1,'3437212490',1,19.95);
INSERT INTO ORDERITEMS
VALUES (1001,1,'9247381001',1,31.95);
INSERT INTO ORDERITEMS
VALUES (1001,2,'2491748320',1,85.45);
INSERT INTO ORDERITEMS
VALUES (1002,1,'8843172113',2,55.95);
INSERT INTO ORDERITEMS
VALUES (1003,1,'8843172113',1,55.95);
INSERT INTO ORDERITEMS
VALUES (1003,2,'1059831198',1,30.95);
INSERT INTO ORDERITEMS
VALUES (1003,3,'3437212490',1,19.95);
INSERT INTO ORDERITEMS
VALUES (1004,1,'2491748320',2,85.45);
INSERT INTO ORDERITEMS
VALUES (1005,1,'2147428890',1,39.95);
INSERT INTO ORDERITEMS
VALUES (1006,1,'9959789321',1,54.50);
INSERT INTO ORDERITEMS
VALUES (1007,1,'3957136468',3,72.15);
INSERT INTO ORDERITEMS
VALUES (1007,2,'9959789321',1,54.50);
INSERT INTO ORDERITEMS
VALUES (1007,3,'8117949391',1,8.95);
INSERT INTO ORDERITEMS
VALUES (1007,4,'8843172113',1,55.95);
INSERT INTO ORDERITEMS
VALUES (1008,1,'3437212490',2,19.95);
INSERT INTO ORDERITEMS
VALUES (1009,1,'3437212490',1,19.95);
INSERT INTO ORDERITEMS
VALUES (1009,2,'0401140733',1,22.00);
INSERT INTO ORDERITEMS
VALUES (1010,1,'8843172113',1,55.95);
INSERT INTO ORDERITEMS
VALUES (1011,1,'2491748320',1,85.45);
INSERT INTO ORDERITEMS
VALUES (1012,1,'8117949391',1,8.95);
INSERT INTO ORDERITEMS
VALUES (1012,2,'1915762492',2,25.00);
INSERT INTO ORDERITEMS
VALUES (1012,3,'2491748320',1,85.45);
INSERT INTO ORDERITEMS
VALUES (1012,4,'0401140733',1,22.00);
INSERT INTO ORDERITEMS
VALUES (1013,1,'8843172113',1,55.95);
INSERT INTO ORDERITEMS
VALUES (1014,1,'0401140733',2,22.00);
INSERT INTO ORDERITEMS
VALUES (1015,1,'3437212490',1,19.95);
INSERT INTO ORDERITEMS
VALUES (1016,1,'2491748320',1,85.45);
INSERT INTO ORDERITEMS
VALUES (1017,1,'8117949391',2,8.95);
INSERT INTO ORDERITEMS
VALUES (1018,1,'3437212490',1,19.95);
INSERT INTO ORDERITEMS
VALUES (1018,2,'8843172113',1,55.95);
INSERT INTO ORDERITEMS
VALUES (1019,1,'0401140733',1,22.00);
INSERT INTO ORDERITEMS
VALUES (1020,1,'3437212490',1,19.95);
CREATE TABLE BOOKAUTHOR
(ISBN VARCHAR2(10),
AuthorID VARCHAR2(4),
CONSTRAINT bookauthor_pk PRIMARY KEY (isbn, authorid),
CONSTRAINT bookauthor_isbn_fk FOREIGN KEY (isbn)
REFERENCES books (isbn),
CONSTRAINT bookauthor_authorid_fk FOREIGN KEY (authorid)
REFERENCES author (authorid));
INSERT INTO BOOKAUTHOR
VALUES ('1059831198','S100');
INSERT INTO BOOKAUTHOR
VALUES ('1059831198','P100');
INSERT INTO BOOKAUTHOR
VALUES ('0401140733','J100');
INSERT INTO BOOKAUTHOR
VALUES ('4981341710','K100');
INSERT INTO BOOKAUTHOR
VALUES ('8843172113','P105');
INSERT INTO BOOKAUTHOR
VALUES ('8843172113','A100');
INSERT INTO BOOKAUTHOR
VALUES ('8843172113','A105');
INSERT INTO BOOKAUTHOR
VALUES ('3437212490','B100');
INSERT INTO BOOKAUTHOR
VALUES ('3957136468','A100');
INSERT INTO BOOKAUTHOR
VALUES ('1915762492','W100');
INSERT INTO BOOKAUTHOR
VALUES ('1915762492','W105');
INSERT INTO BOOKAUTHOR
VALUES ('9959789321','J100');
INSERT INTO BOOKAUTHOR
VALUES ('2491748320','R100');
INSERT INTO BOOKAUTHOR
VALUES ('2491748320','F100');
INSERT INTO BOOKAUTHOR
VALUES ('2491748320','B100');
INSERT INTO BOOKAUTHOR
VALUES ('0299282519','S100');
INSERT INTO BOOKAUTHOR
VALUES ('8117949391','R100');
INSERT INTO BOOKAUTHOR
VALUES ('0132149871','S100');
INSERT INTO BOOKAUTHOR
VALUES ('9247381001','W100');
INSERT INTO BOOKAUTHOR
VALUES ('2147428890','W105');
CREATE TABLE promotion
(Gift varchar2(15),
Minretail number(5,2),
Maxretail number(5,2));
INSERT into promotion
VALUES ('BOOKMARKER', 0, 12);
INSERT into promotion
VALUES ('BOOK LABELS', 12.01, 25);
INSERT into promotion
VALUES ('BOOK COVER', 25.01, 56);
INSERT into promotion
VALUES ('FREE SHIPPING', 56.01, 999.99);
CREATE TABLE acctmanager
(amid CHAR(4),
amfirst VARCHAR2(12) NOT NULL,
amlast VARCHAR2(12) NOT NULL,
amedate DATE DEFAULT SYSDATE,
amsal NUMBER(8,2),
amcomm NUMBER(7,2) DEFAULT 0,
region CHAR(2),
CONSTRAINT acctmanager_amid_pk PRIMARY KEY (amid),
CONSTRAINT acctmanager_region_ck
CHECK (region IN ('N', 'NW', 'NE', 'S', 'SE', 'SW', 'W', 'E')));
CREATE TABLE acctbonus
(amid CHAR(4),
amsal NUMBER(8,2),
region CHAR(2),
CONSTRAINT acctbonus_amid_pk PRIMARY KEY (amid));
COMMIT;
Width Column Name CATCODE CATDESC Datatype VARCHAR2 VARCHAR2 Constraints PRIMARY KEY NOT NUEL 3 The data for the CATEGORY table is as follows CATCODE BUS CLIN COK COM FAL FIT CATDESC BUSINESS CHEDREN COOKING COMPUTER FAMILY LIFE FITNESS SELF HELP LITERATURE SEH LITExplanation / Answer
CREATE TABLE CATEGORY
(
CATCODE VARCHAR2(3) PRIMARY KEY,
CATDESC VARCHAR2(11) NOT NULL
);
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');
COMMIT;
ALTER TABLE BOOKS ADD COLUMN CATCODE VARCHAR2(3);
ALTER TABLE BOOKS ADD CONSTRAINT FK_CATCODE
FOREIGN KEY (CATCODE) REFERENCES CATEGORY(CATCODE);
UPDATE BOOKS
SET CATCODE = (SELECT CATCODE FROM CATEGORY C WHERE C.CATDESC = CATEGORY);
SELECT * FROM BOOKS;
COMMIT;
ALTER TABLE BOOKS DROP COLUMN CATEGORY;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.