Question

Part A

The table below lists customer/car hires data. Each customer may hire cars from various outlets throughout Singapore. A car is registered at a particular outlet and can be hired out to a customer on a given date.

carReg	make	model	custNo	custName	hireDate	outletNo	outletLoc
M565 0GD	Ford	Escort	C100	Smith, J	14/5/18	01	Queenstown
M565 0GD	Ford	Escort	C201	Hen, P	15/5/18	01	Queenstown
N734 TPR	Nissan	Sunny	C100	Smith, J	16/5/18	01	Queenstown
M134 BRP	Ford	Escort	C313	Blatt, O	14/5/18	02	Tampines
M134 BRP	Ford	Escort	C100	Smith, J	20/5/18	02	Tampines
M611 0PQ	Nissan	Sunny	C295	Pen, T	20/5/18	02	Tampines

Instructions:

1. The data in the table is susceptible to update anomalies. Provide examples of how insertion, deletion, and modification anomalies could occur on this table.
2. Describe and illustrate the process of normalization by converting Table 1 to Third Normal Form (3NF) relations. Identify the primary and foreign keys in your 3NF relations.

Part B

The following tables form part of a database held in a Relational Database Management System for a printing company that handles printing jobs for book publishers:

Publisher                        (pubID, pubName, street, city, postcode, telNo, creditCode)

              BookJob                          (jobID, pubID, jobDate, description, jobType)

              PurchaseOrder               (jobID, poID, poDate)

              POItem                            (jobID, poID, itemID, quantity)

              Item                                  (itemID, description, onHand, price)

Where:

Publisher                          contains publisher details and pubID is the key.

BookJob                            contains details of the printing jobs (books or part books) and jobID is the key.

PurchaseOrder                 A printing job requires the use of materials, such as paper and ink, which are assigned to a job via purchase orders. This table contains details of the purchase orders for each job and the key is jobID/poID. Each printing job may have several purchase orders assigned to it.

POItem                              Each purchase order (PO) may contain several PO items. This table contains details of the PO items and jobID/poID/itemID form the key.

Item                                   contains details of the materials which appear in POItem, and the key is itemID.

Instructions:

Formulate the following queries using SQL:

(i)                         List all publishers in alphabetical order of name.

(ii)                        List all printing jobs for the publisher ‘Gold Press’.

(iii)                      List the names and phone numbers of all publisher who have a rush job (jobType = ‘R’).

(iv)                       List the dates of all the purchase orders for the publisher ‘PressMan’.

(v)                        How many publisher fall into each credit code category?

(vi)                       List all job type’s with at least three printing jobs.

(vii)                     List the average price of all items.

(viii) List all items with a price below the average price of an item.

Answer 1

Part A

i)Anomalies

insertion anomaly ---- If a new outlet is opened and there is no customer hires and does car registration, carReg is null which violates entity integrity constraint as carReg is primary key.

updation ---- If an outlet location is changed it should be updated in all concerned rows (redundant data) . If not done on all rows, inconsistency exists in the database.

deletion ------ If the customer C201 is deleted from the database, all the associated data carReg, make, model will also get deleted.

ii)Normalization

1NF(All attributes should be atomic)

CustomerCarHire(carReg, make, model, custNo, custName, hireDate, outletNo, outletLoc)

Composite Primary key --- carReg, custNo

2NF(remove partial dependencies)

Partial Functional dependencies

carReg -> make, model, hireDate, custNo, outletNo, outletLoc

custNo -> custName

tables in 2NF

CarRegistration(carReg , make, model, hireDate, custNo, outletNo, outletLoc)

Customer(custNo , custName)

3NF(remove transitive dependencies)

transitive dependency:

outletNo -> outletLoc

Tables in 3NF

CarRegistration(carReg , make, model, hireDate, custNo, outletNo)

Customer(custNo , custName)

Outlet(outletNo , outletLoc)

bold attributes are primary keys and italicized are foreign keys.

Part B

(i)

Select * from Publisher order by pubName;

(ii)   

Select BookJob.* from BookJob ,Publisher where BookJob.pubID = Publisher.pubID and pubName = 'Gold Press';

(iii)   

Select pubName, telNo from BookJob ,Publisher where BookJob.pubID = Publisher.pubID and jobType = 'R';

(iv)   

Select poDate from PurchaseOrder ,BookJob, Publisher where pubName ='PressMan';

(v)

Select Count(pubId) from Publisher group by creditCode ;

(vi)   

Select jobType from BookJob group by jobType having description LIKE '%Printing%' and Count(jobId) >= 3;

(vii)   

Select Avg(Price) from Item;

(viii)

Select * from Item where price <(Select Avg(Price) from Item);

Do ask if any doubt. Please up-vote.