1

Semester
2, 2022

ICT285 Databases
Assignment 1

Umer
Semester 2, 2022
 2

Table of Contents

ICT285 Databases......................................................................................................................................1
Question 1: Relational algebra (20 marks).........................................................................................3
Question 2: SQL – SELECT queries (20 marks)................................................................................5
Question 3: Further SQL (15 marks)...................................................................................................9
Question 4: Normalization (20 marks)...............................................................................................10
1-What are the specific problems associated with the current design and why do they arise?
............................................................................................................................................................11
2-How would you change the current design and how does your new design address the
problems you have identified with the current design..................................................................12
Fig1: Change the current design....................................................................................................12
Question 5: Conceptual Design (25 marks)......................................................................................12
Fig2: ER Diagram Of Terrific Airline...............................................................................................14
 3

Question 1: Relational algebra (20 marks)

a. List the quantity of parts supplied on JobNo 4745.

 Quantity (σ JobNo = “4745”(SUPPLY))

b. List the weight of the Part named “Left-handed screwdriver”.

 Weight (σ PartName = “Left-handed screwdriver”(PART))

c. List the Project name and Part name of any Parts where fewer than 500 of the part

has been supplied to a particular project.

JobName, PartName (σ Quantity < 500 ((PART * part.PNo = supply.PNo

SUPPLY)*supply.JobNo = project.JobNo PROJECT))

d. List the name of any Suppliers who have supplied Parts to the Project called “New

Academic Building” or to the Project called “Removal of Asbestos”.

 SupplierName (σ JobName = “New Academic Building” OR JobName=

“RemovalofAsbestos”((PROJECT*p.JobNo=supply.JobNoSUPPLY)*supply.SNo=s.S

NoSUPPLIER))

e. List the name of any Suppliers who have supplied Parts to the Project called “New

Academic Building” and to the Project called “Removal of Asbestos”.

 SupplierName (σ JobName = “New Academic Building” OR JobName=

“RemovalofAsbestos”((PROJECT*p.JobNo=supply.JobNoSUPPLY)*supply.SNo=s.S

NoSUPPLIER))

(same as question d)

f. List the names of Suppliers who have supplied the Part with the name “Fufful

Valve”.

SupplierName (σ PartName = “Fufful Valve” ((PART *p.PNo=supply.PNoSUPPLY)*

supply.SNo = s.SNoSUPPLIER))
 4

g. List the names of Projects that have had Parts supplied by “Insightly Co#” or a

Supplier located in Dubai.

JobName (σ PartName = “Insightly Co#” OR Country = “Dubai” (((PART *p.PNO =

supply.PNo.

h. List the details of any supplier who has supplied parts to a project that stared in

2020 and (the project is) located in Singapore.

σ StartYear=’2020’ AND Country=’Singapore’ (PROJECT) -> R1

SNo (R1 ⋈ R1.JobNo = SUPPLY.JobNo SUPPLY) -> R2

SupplierName, City (R2 ⋈ R2.SNo = SUPPLIER.SNo SUPPLIER)

i. List the name of any part that was not used on a project that commenced in 2020.

σ StartYear=’2020’ (PROJECT) -> R1

 PNo (R1 ⋈ R1.JobNo = SUPPLY.JobNo SUPPLY) -> R2

σ PNo is null (PART LEFT JOIN PART.PNo = R2.PNo R2) -> R3

 PartName (R3)

j. List the name of any part that has been supplied to all projects that commenced in

2020.

σ StartYear=’2020’ (PROJECT) -> R1

 PNo (R1 ⋈ R1.JobNo = SUPPLY.JobNo SUPPLY) -> R2

 PartName (R2 ⋈ R2.PNo = PART.PNo PART)

 5

Question 2: SQL – SELECT queries (20 marks)

a. List the details of any works of art (including the Artist who created the

work) that have more than three copies recorded in the database.

SELECT TITLE,FIRSTNAME, LASTNAME

FROM dtoohey.work W, dtoohey.artist A WHERE A.artistid = A.artistid AND

A.artistid in ( SELECT A.artistid FROM dtoohey.work W GROUP BY W.artistid

HAVING count(*) > 3);

b. List the details of any work of art (including the Artist who created the

work) that has a Surrealist style.

SELECT title ,firstname,lastname FROM dtoohey.work W , dtoohey.artist A

where A.artistid= W.artistid AND description = 'Surrealist style';

c. List the details of the works of art (including the Artist who created the work,

and the acquisition and asking price details) currently held in the gallery (i.e.,

works of art that have not been sold).

SELECT title ,firstname,lastname, acquisitionprice, askingprice FROM

dtoohey.work W, dtoohey.artist A, dtoohey.trans T WHERE A.artistid= W.artistid

AND W.workID = T.workID

AND datesold is null;

d. List the sales for each customer (i.e., when a customer purchases a work of

art from the Gallery, a transaction line is created. For a purchase, there will
 6

be values in the DateSold and SalesPrice columns). The query should

include the details of the customer, the transaction and the work of art

purchased.

SELECT firstname, lastname, transactionID, title FROM dtoohey.work W,

dtoohey.trans T, dtoohey.customer C WHERE W.workid= T.workid AND

T.customerid= C.customerid AND datesold is not null;

e. List the names of the deceased artists and the number of years of age they

were when they died (for example, an artist born in 1950 and deceased in

2001 has an age of 51).

SELECT firstname, lastname, DATEDECEASED-

DATEOFBIRTH as AGE from dtoohey.artist

where DATEDECEASED is not null;

f. The sum of the acquisition price of works of art for each year (for example, if

there were two works of art purchased for $1500 and $1000 in 2019, and one

work of art purchased for $500 in 2020, then the sums would be $2500 and

$500, for 2019 and 2020 respectively).

SELECT SUM(ACQUISITIONPRICE) AS TOTAL ,EXTRACT(YEAR FROM

dateacquired) AS YEAR from dtoohey.trans GROUP BY EXTRACT(YEAR

FROM dateacquired) ORDER BY YEAR asc;

g. Calculate the profit made on works of art that have been sold (i.e., the

profit/loss on an individual work of art is the difference between the

 7

acquisition price and the sales price).

SELECT TITLE, SALESPRICE-ACQUISITIONPRICE AS PROFIT FROM

dtoohey.work W, dtoohey.trans T WHERE W.workid = T.workid AND datesold

is not null;

h. Which artist has had the most works of art sold, and how many of the

artist’s works have been sold?

SELECT C.firstname,C.lastname,A.numberartsold

FROM (select W.artistid , count(*) AS numberartsold

FROM dtoohey.work W, dtoohey.trans T, dtoohey.artist C WHERE

W.workID=T.workID AND C.artistid=W.artistid and datesold is not null

GROUP BY W.artistid ) A, (SELECT max(count(*)) AS numberartsold

FROM dtoohey.work W, dtoohey.trans T, dtoohey.artist C WHERE

W.workID=T.workID AND C.artistid=W.artistid AND datesold is not null

GROUP BY W.artistid ) B, dtoohey.artist C WHERE A.artistid=C.artistid AND

b.numberartsold=A.numberartsold;

i. Sales of which artist’s works have resulted in the highest average profit (i.e.,

the average of the profits made on each sale of works by an artist), and

what is that amount?

j.

SELECT c.firstname, c.lastname,a.profit

FROM (SELECT a.artistid, avg(salesprice-

acquisitionprice) AS profit

FROM dtoohey.trans t, dtoohey.work w ,

dtoohey.artist a WHERE t.workid=w.workid

 8

AND w.artistid=a.artistid

AND datesold is not null

GROUP BY a.artistid)a,

SELECT max(avg(salesprice-acquisitionprice)) AS

maxaveprofit FROM dtoohey.trans t, dtoohey.work

w, dtoohey.artist a WHERE t.workid=w.workid

AND w.artistid=a.artistid

AND datesold isnot null

GROUP BY a.artistid)b,dtoohey.artist c WHERE a.profit

= b.maxaveprofit AND c.artistid = a.artistid;

k. Customer name of any customers who have an interest in ALL artists.

SELECT C.firstname, C.lastname FROM

SELECT c.customerid, count(*) AS noofinterest FROM

dtoohey.customer C, dtoohey.customer_artist_int I,

dtoohey.artist A

WHERE A.artistid= I.artistid AND

I.customerid=C.customerid GROUP BY

C.customerid

)Z, ( SELECT max(count(*)) AS countmostinterest FROM dtoohey.customer

C ,dtoohey.customer_artist_int I, dtoohey.artist A

WHERE A.artistid= I.artistid AND I.customerid=C.customerid

GROUP BY C.customerid ) total , dtoohey.customer C

WHERE Z.customerid=C.customerid AND

total.countmostinterest=Z.noofinterest;
 9

Question 3: Further SQL (15 marks)

a. Provide ALL of the SQL statements required to insert the details of the following:

A customer, John Smith, of 47 Moodle Street, Highwater, WA, 6709, Australia (email:

[email protected]) has sold a work of art called “Gorillas in the Mist” by the

renowned French artist, Gallic Symbol (b. 1972) to the Gallery (i.e., the Gallery

purchased it from him). It is a unique Watercolour on Paper and is 45 * 35cm

signed by the Artist. The purchase price was $4600 and the transaction took place on

27th July 2020. (7 marks)

1-INSERT INTO customer VALUES (1000,'Smith','John','47 Moodle

Street','Highwater','WA','6709','Australia','','','[email protected]');

2-INSERT INTO work VALUES (999, 'Gorillas in the Mist', 'Unique','Watercolour on

Paper','45 * 35cm signed by the Artist','100');

3-INSERT INTO artist VALUES (100,'Symbol','Gallic','French',1972,'');

4-INSERT INTO trans VALUES (500,'27/07/2020','4600','','','',1000, 999);

b.
 10

CREATE TABLE MATCH (MatchID int PRIMARY KEY, "Date" date not null, "Result"

CHAR(1) not null, check ("Result"='L' OR "Result"='W' OR "Result"='D') ,

HomeTeamID VARCHAR2(5), FOREIGN KEY (HomeTeamID) REFERENCES

TEAM (TEAMID), AwayTeamID VARCHAR(5), FOREIGN KEY (AwayTeamID)

REFERENCES TEAM (TEAMID));

c. Your match table must also include record the scores of both teams in the match.

Provide the SQL to amend the original table design to allow for this change

in requirement. It is most unlikely (impossible) that a team would ever score

more than 999 goals in a match.

ALTER table MATCH ADD Scores int CONSTRAINT CheckScores check (Scores <

999);

Question 4: Normalization (20 marks)

The act of organizing your data and tables so they can be added and modified effectively is

known as database normalization, or just normalization as it is more frequently known. It is

used for data modeling or database design.

It is anything that a person personally performs as opposed to a system or a tool. Database

administrators and developers frequently carry it out.

Any relational database that stores data in tables that are connected to one another can be

used for this. As a result, normalization in a DBMS (Database Management System) can be
 11

carried out in any type of database, including Oracle, Microsoft SQL Server, MySQL,

Postgre SQL, and others. Starting with a general concept of the data you wish to save, you

perform the normalization process by applying specific rules to it to transform it into a more

useful form.

1-What are the specific problems associated with the current design and why do they

arise?

1-Insertion anomaly

For instance, while adding a new patient to the table, we must additionally include the

item and the doctor's name in addition to the patient's information. In that situation, the

database might mistakenly identify another doctor if we input "Leafthy" rather than

"Leafhy."

2-Deletion anomaly

If we need to remove the patient records for "REARDON" and "SOAPIER," item "A012"

will be permanently lost and there will be no way to get it back.

3-Update anomaly

For example, if we need to change the patient's name from "LEMOS" to "LEMOSS," we

must update both of the patient's records rather than simply one because we have two

entries for that patient. These abnormalities must be immediately fixed because they

lead to database discrepancies.

 12

2-How would you change the current design and how does your new design address

the problems you have identified with the current design

Fig1: Change the current design

Question 5: Conceptual Design (25 marks)

13
 14

Fig2: ER Diagram Of Terrific Airline.

This is the ERD I developed for Terrific Airlines.

I've made the following assumptions for the ERD: There will be a choice of three payment

methods under flight reservations: credit card, cash, or cheque. This makes it possible to

connect to the credit payments table more easily in the future and to add more payment

methods.