INF3707 Database Design and Implementation

INF3707 EXAM PACK
2025

QUESTIONS AND
ANSWERS
FOR ASSISTANCE CONTACT
EMAIL:

, lOMoARcPSD|50013335




Assignment 2 Total marks Due date
60
Note that this is a COMPULSORY assignment!
To do this assignment, you need access to the JustLee books database

Question 1: Chapter 5
Currently, the contents of the Category column in the BOOKS table are the actual names 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 CATEGORY table should be as
follows:

Column Name Datatype Width Constraints
CATCODE VARCHAR2 3 PRIMARY KEY
CATDESC VARCHAR2 11 NOT NULL

The data for the CATEGORY table is as follows:
CATCODE CATDESC
BUS BUSINESS
CHN CHILDREN
COK COOKING
COM COMPUTER
3+FAL FAMILY LIFE
FIT FITNESS
SEH SELF HELP
LIT LITERATURE

1.1 Create the CATEGORY table (3 marks).

CREATE TABLE category (
catcode VARCHAR2(3),
catdesc VARCHAR2(11) NOT NULL,
CONSTRAINT category_code_pk PRIMARY KEY (catcode) );

1.2 Populate the Category table with the given data. Save the changes permanently (8
marks)

INSERT INTO category
VALUES ('BUS', 'BUSINESS');

INSERT INTO category
VALUES ('CHN', 'CHILDREN');

INSERT INTO category
VALUES ('COK', 'COOKING');

INSERT INTO category
VALUES ('COM', 'COMPUTER');

2

, lOMoARcPSD|50013335




INF3707

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;

1.3 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. (4 marks)

ALTER TABLE books
ADD (catcode VARCHAR2(3),
CONSTRAINT books_catcode_fk FOREIGN KEY (catcode)
REFERENCES category(catcode));

1.4 Set the Catcode values for the rows in the BOOKS table based on each book’s current
Category value. (8 marks)

UPDATE books
SET catcode = 'BUS'
WHERE category = 'BUSINESS';

UPDATE books
SET catcode = 'CHN'
WHERE category = 'CHILDREN';

UPDATE books
SET catcode = 'COK'
WHERE category = 'COOKING';

UPDATE books
SET catcode = 'COM'
WHERE category = 'COMPUTER';

UPDATE books
SET catcode = 'FAL'
WHERE category = 'FAMILY LIFE';

UPDATE books
SET catcode = 'FIT'
WHERE category = 'FITNESS';

UPDATE books
SET catcode = 'SEH'
3

, lOMoARcPSD|50013335




WHERE category = 'SELF HELP';

UPDATE books
SET catcode = 'LIT'
WHERE category = 'LITERATURE';

COMMIT;

Or

UPDATE BOOKS
SET BOOKS.CATCODE = (SELECT CATEGORY.CATCODE
FROM CATEGORY
WHERE CATEGORY.CATDESC = BOOKS.CATEGORY)
WHERE CATCODE IS NULL;

1.5 Verify that the correct categories have been assigned in the BOOKS table, and save
changes permanently. (1 marks)

Select * from Books;

1.6 Delete the Category column from the BOOKS table. (1 marks)

ALTER TABLE books
DROP column category;


Question 2 Chapter 8
Give SQL statements for the following requests.

2.1 Which customers live in Georgia or New Jersey? Put the results in ascending order by last
name. List each customer’s customer number, last name and the state. (5 marks)

Answer;
SELECT customer#, lastname, state
FROM customers
WHERE state = 'GA' or state = 'NJ' // (Note that Georgia and New Jersey are
not cities)
ORDER BY lastname;

Or

SELECT customer#, lastname, state
FROM customers
WHERE state IN ('GA', 'NJ')
ORDER BY lastname;


2.2 List authors whose last name contains the letter pattern “IN”. Put the results in order of last
name, then first name. List each author’s last name and first name. (5 marks)

Answer
SELECT lname, fname
FROM author
4