ICT285 Databases Assignment - January 2020: SQL, Normalization, Design

This document presents a complete solution to the ICT285 Databases assignment from January 2020, covering a range of database concepts. The solution begins with relational algebra, providing multiple expressions to query data from various tables including LECTURER, UNIVERSITY, STUDENT, PARTICIPANT, and LECTURE. Next, the assignment moves to SQL, providing a series of SELECT queries to retrieve and manipulate data from WORK, ARTIST, and TRANSACTION tables, including queries with JOIN operations, WHERE clauses, and aggregate functions. Further SQL questions involve creating and altering tables, and inserting data. The assignment then delves into database normalization, identifying and resolving anomalies in a given database design to achieve second and third normal forms. Finally, a conceptual design is presented, including an Entity-Relationship Diagram (ERD) for a healthcare system, with detailed assumptions and relationships between entities like doctors, patients, appointments, and suppliers, along with a comprehensive bibliography.

Running head: ICT285 DATABASES
ICT285 Databases
Name of the Student
Name of the University
Author’s note:

1ICT285 DATABASES
Table of Contents
Question 1: Relational algebra.........................................................................................................2
Question 2: SQL – SELECT queries...............................................................................................3
Question 3: Further SQL.................................................................................................................7
Question 4: Normalization...............................................................................................................9
Question 5: Conceptual Design.....................................................................................................11
Bibliography:.................................................................................................................................13

2ICT285 DATABASES
Question 1: Relational algebra
a) LecturerName, UniversityName (LECTURER*LECTURER.UniversityName=UNIVERSITY.UniversityName
UNIVERSITY)
b) LecturerName, Description (Topicc=“Biological Diversity” (LECTURER*LECTURER.LecturerName =
LECTURE.LecturerName LECTURE))
c) StudentName (Topicc=“Biological Diversity” OR Topic=“Bugs and Bubs” (STUDENT*Student.StudentNo = PARTICIPANT.
StudentNo PARTICIPANT)*PARTICIPANT.LectureNo=LECTURE.LectureNo LECTURE)
d) StudentName (Country =“Malaysia” (((STUDENT*Student.StudentNo = PARTICIPANT. StudentNo
PARTICIPANT)*PARTICIPANT.LectureNo=LECTURE.LectureNo LECTURE)*LECTURE.LecturerName= LECTURER. LecturerName
LECTURER)* LECTURER .UniversityName =UNIVERSITY.UniversityName UNIVERSITY)
e) StudentName (STUDENT.UniversityName =“Murdoch University” AND Topic=“Biological Diversity”
(((STUDENT*Student.StudentNo = PARTICIPANT. StudentNo PARTICIPANT) *PARTICIPANT.LectureNo=LECTURE.LectureNo
LECTURE) *LECTURE.LecturerName= LECTURER. LecturerName LECTURER)* LECTURER .UniversityName
=UNIVERSITY.UniversityName AND country = “Italy” UNIVERSITY)
f) LectureNo, LectureName, Topic, StudentName ((STUDENT*Student.StudentNo = PARTICIPANT. StudentNo
PARTICIPANT) RIGHT JOIN PARTICIPANT.LectureNo=LECTURE.LectureNo LECTURE)
g) StudentName (Topicc=“Introduction to Biology” AND Topic=“Darwin 101” ((STUDENT*Student.StudentNo =
PARTICIPANT. StudentNo PARTICIPANT)*PARTICIPANT.LectureNo=LECTURE.LectureNo LECTURE))
h)  LecturerName, Biography, UniversityName (Country=“Greece” (LECTURER*LECTURER.UniversityName =
UNIVERSITY.UniversityName UNIVERSITY))

3ICT285 DATABASES
i) StudentName (STUDENT)
MINUS
StudentName (Topicc=“Grassland Cultivation Masterclass” ((STUDENT*Student.StudentNo = PARTICIPANT. StudentNo
PARTICIPANT)*PARTICIPANT.LectureNo=LECTURE.LectureNo LECTURE))
j) StudentName ((count(distinct PARTICIPANT.LectureNo)= (select count(*) from LECTURE) (STUDENT*Student.StudentNo =
PARTICIPANT. StudentNo PARTICIPANT)*PARTICIPANT.LectureNo=LECTURE.LectureNo LECTURE)
Question 2: SQL – SELECT queries
Query 1
Select WORK.*, ARTIST.Name from WORK
inner join ARTIST
on WORK.ArtistID = ARTIST.artistid
where WORK.Description like '%Surrealist%';
Query 2
Select WORK.*, ARTIST.Name from WORK
inner join ARTIST
on WORK.ArtistID = ARTIST.artistid
inner join TRANSACTION
on WORK.WorkID = TRANSACTION.WorkID

4ICT285 DATABASES
Where TRANSACTION.CustomerID = NULL
AND TRANSACTION.AskingPrice > 400;
Query 3
Select work.title from work
where copy = 2;
Query 4
select artistname, (DeceaseDate-birthdate) as AgeDied from artist
where DeceaseDate Is Not Null;
Query 5
select artist.name, count(work.artistid) as NumberOfWorkDone from artist
inner join work
on artist.artistid = work.artistid
group by artist.name
order by AVG(work.workid)
Query 6
Select work.workid, ARTIST.Name from WORK
inner join ARTIST
on WORK.ArtistID = ARTIST.artistid

5ICT285 DATABASES
inner join TRANSACTION
on WORK.WorkID = TRANSACTION.WorkID
Where TRANSACTION.CustomerID IS NOT NULL
AND transaction.salesprice >
(Select avg(t.salesprice) from Transaction t);
Query 7
select sum(t.salesprice) as TotalSalesAmount, count(t.customerid) as TotalSalesCount
from transaction t
group by extract(year from sysdate);
Query 8
select artist.name, count(transaction.customerid) as TotalSoldWork from WORK
inner join ARTIST
on WORK.ArtistID = ARTIST.artistid
inner join TRANSACTION
on WORK.WorkID = TRANSACTION.WorkID
group by artist.name
having count(transaction.customerid) = (
select max(workcount) from (
select t.transactionid, count(t.customerid) as workcount from transaction t

6ICT285 DATABASES
group by t.transactionid
));
Query 9
select SUM(t.salesprice - t.acquisitionprice) as TotalProfit from transaction t;
Query 10
select c.name from customer c
inner join customer_artist_int cai
on c.customerid = cai.customerid
where cai.customerid IN (select c2.customerid from customer c2)
group by c.name, cai.customerid
having COUNT(1) = (Select COUNT(c3.customerid) from customer c3);
Question 3: Further SQL
Question 1
CREATE TABLE PRESENTER
(
PresenterNo number NOT NULL,
PresenterName char (20) NOT NULL,
Biography char (1000)NOT NULL,

7ICT285 DATABASES
InstitutionName char (20)NOT NULL,
CONSTRAINT presenter_pk PRIMARY KEY (PresenterNo)
);
Question 2
CREATE TABLE LECTURE
(
LectureNo number NOT NULL,
LectureName char (50) NOT NULL,
Description varchar (1000)NOT NULL,
Theme char (50)NOT NULL,
Capacity number NOT NULL,
DateAndTime TIMESTAMP NOT NULL,
PresenterNo NUMBER NOT NULL,
CONSTRAINT lecture_no_pk PRIMARY KEY(LectureNo),
CONSTRAINT presenter_no_fk FOREIGN KEY (PresenterNo) REFERENCES
PRESENTER (PresenterNo)
ON DELETE CASCADE
);
Question 3

8ICT285 DATABASES
INSERT INTO PRESENTER VALUES
(1,'Your Name','Your bio','Murdoch University' );
Question 4
ALTER TABLE LECTURE ADD VenueName char(30)
CONSTRAINT venue_name_Check
CHECK (VenueName IN ('Building A','Building B','Building C'));
Question 5
UPDATE LECTURE
SET Capacity = Capacity + 10;
Question 4: Normalization
a) First issues arises with the current design is insertion anomaly. It is not possible to add
a new room unless someone has visited that room for appointment. Same goes for a Podiatrist.
Until someone visits the Podiatrist, his/her data cannot be added to database. Next problem is
with deletion of data. If any record is deleted from database then it will result into deletion of
entire instance. Deletion of visits can result into deletion of all the Podiatrist and Patients who
have visited that room. Next issue with the information update. This is the biggest issue in
database. In case, RoomExt needs to be updated for any particular room then all the records that
has that specific room’s information must be updated. If any instance is not updated, then
database will have incorrect data.

9ICT285 DATABASES
b) As the database does not have any multivalued attribute, it is possible to concentrate
on finding partial dependency that violates rules of second normal form. The partial
dependencies in this database are as following.
StaffNo  Podiatrist
PatNum  PatientName, PatTelNo
RoomNo  RoomExt
In new design, Podiatrist and room details will be stored in separate table. The primary
keys of these two tables will be used in the patient table as foreign keys. Now the database is in
second normal form. However, transitive dependency exists with patient table because of
following dependency.
PatNum, StaffNo  ApptDateTime, RoomNo
PatNum  PatientName, PatTelNo
ApptDateTime  RoomNo
Therefore, in order to resolve this new appointment table can be created. This table will
hold all the appointment related data.
Therefore new database design will look like following.
Staff (StaffNo, Podiatrist)
Patient (PatNum, PatientName, PatTelNo)
Appointment (StaffNo, PatNum, ApptDateTime, RoomNo)
Room (RoomNo, RoomExt)

10ICT285 DATABASES
This design is capable of mitigating all the insert, delete and updated related issues was
present within current design. New Staff, Patient or Room details can be added not worrying
about appointment scheduling. Data can be deleted without affecting other tables. Any non-key
attribute values can be changed in one instance only.
Question 5: Conceptual Design
Figure 1: Conceptual Database Model
(Source: Created by Author)
Assumptions: The assumptions made to complete the entity relationship diagram are as
following.

11ICT285 DATABASES
i. One local doctor will checkup many patients. Each patient must visit a local
doctor before getting an appointment
ii. Patient may or may not have an appointment for further checkup. One patient will
have only one appointment
iii. Appointment can be either outpatient or inpatient appointment
iv. Inpatient may get a bad at the time of appointment
v. Supplier can supply more than one supplies. A supplier may not supply any
product
vi. Many staff can work on many roster periods. Each roster is defined using date and
time of shift.
vii. Charge nurse can be either junior nurse or senior nurse
viii. Each staff will have only one contract
ix. Each staff can have more than one qualification
x. Staff can must have at least one experience
xi. Each patient must have one next of kin