Databases Assignment 1: SQL, Normalization, and Conceptual Design

This document provides a comprehensive solution to ICT285 Databases Assignment 1, addressing key concepts in relational databases and SQL. The assignment begins with relational algebra queries, demonstrating the retrieval of specific data from database tables using operators like projection and selection. It then moves on to SQL queries, illustrating the practical application of SQL to retrieve, filter, and manipulate data. The solution also includes SQL code for creating tables, including primary and foreign keys, and implementing constraints and triggers. Furthermore, the document covers database normalization, explaining the different normal forms and how to transform a database schema to achieve a higher level of normalization, reducing data redundancy and improving data integrity. Finally, the assignment concludes with a conceptual design, creating an ER diagram for a garden share website, detailing the entities, attributes, and relationships within the database, providing a complete overview of database design principles.

Question 1: Relational algebra (korth, 2010)
a. List the name and specialty of all instructors.
πInstructorName, Specialty(INSTRUCTOR)
b. List the class name and description of all classes suitable for ‘Over 60s’.
πClassName, Description(σ SuitableFor>=60(CLASS))
c. List the names of instructors who run sessions in the classes ‘Aqua Deep’, ‘Aqua Fit’, or
both.
π InstructorName(σ ClassName=‘Aqua Deep’∨ ClassName= ‘Aqua Fit’(CLASS) ✶SESSION
✶INSTRUCTOR)
d. List the names of all members who participated in a class held in Studio 5 on 19/9/2018,
and the name of the class they participated in.
πMemberName ∪ πClassName(σVenueName=’Studio 5’ ∧ DayAndTime= ’19/9/2018’(VENUE) ✶SESSION
✶PARTICIPANT ✶MEMBER)
e. List the names of members who participated in any class with a venue capacity greater
than 30.
π MemberName(σ Capacity>30(VENUE) ✶SESSION✶PARTICIPANT✶MEMBER)
f. List the details of all sessions running on 19/9/2018, and the names of the members who
have signed up for each of them (if any).
σ DayAndTime= ’19/9/2018’(SESSION) ∪ πMemberName(σ DayAndTime= ’19/9/2018’ (SESSION)
✶PARTICIPANT✶MEMBER)
g. List the names of any instructors who run sessions in both Studio 1 and Studio 2.
π MemberName(σVenueName=’Studio 1’ ∧ VenueName=’Studio 2’(SESSION) ✶PARTICIPANT
✶MEMBER)
h. List the names of members who have either participated in classes for the Over 60s,
classes held in the Warm Water Pool, or both.
π MemberName(σ SuitableFor>60(CLASS) ✶SESSION✶PARTICIPANT✶MEMBER) ∪ π
MemberName(σVenueName=’Warm Water Pool’ (SESSION)✶PARTICIPANT✶MEMBER)
i. List the names of the members who have NOT participated in a ‘Zumba’ class.
π MemberName(σ ClassName!=‘Zumba’(SESSION)✶PARTICIPANT✶MEMBER)
j. List the names of members who have participated in all of the different classes offered.
π MemberName (SESSION)✶PARTICIPANT✶MEMBER
Question 2: SQL Select queries (korth, 2010)

a. SELECT f.FamilyName, f.Suburb from dtoohey.PATIENT f where f.State='SA';
b. SELECT f.FamilyName, f.Suburb from dtoohey.PATIENT f where f.State='SA'or
f.State='NSW' ORDER BY f.FamilyName;

c. SELECT p.GivenName, p.Suburb from dtoohey.PATIENT p, dtoohey.DOCTOR d,
dtoohey.ACCOUNT a where f.State='WA' and (d.name='Dr Brian' or d.name='Dr
Barbara') and p.PatientID=a.PatientID and d.ProviderName=a.ProvierName;
d. SELECT p.GivenName, p.Suburb from dtoohey.PATIENT p, dtoohey.DOCTOR d,
dtoohey.ACCOUNT a where d.name='Dr Brian' and d.name!='Dr Ima' and
p.PatientID=a.PatientID and d.ProviderNo=a.ProviderNo;

e. select d.name, p.suburb from dtoohey.patient p, dtoohey.doctor d where d.providerNo
IN(select a.providerNo from dtoohey.account a, dtoohey.doctor d where
d.providerNo=a.providerNo);

f. select i.Description, a.TreatmentDate from dtoohey.item i, dtoohey.account a,
dtoohey.patient p, dtoohey.accountline al where p.FamilyName='Eggert' and
p.GivenName='Betty' and p.PatientID=a.PatientID and a.AccountNo=al.AccountNo and
i.ItemNo=al.ItemNo;

g. select d.name, To_char(a.TreatmentDate, 'YYYY') as "Year Of Treatments", Sum(i.fee)
as "Total Fees" from dtoohey.doctor d, dtoohey.item i, dtoohey.account a,
dtoohey.accountline al where d.ProviderNo=a.ProviderNo and

a.AccountNo=al.AccountNo and i.ItemNo=al.ItemNo group by
d.Name,To_char(a.TreatmentDate, 'YYYY') order by d.Name;
h. select d.Name from dtoohey.Doctor d, dtoohey.account a, dtoohey.item i,
dtoohey.accountline al where d.providerNo=a.providerNo and
a.AccountNo=al.AccountNo and al.itemNo=i.itemNo group by d.Name having
count(*)<(select avg(AccountNo) from dtoohey.account);

i. select p.state, i.description, Sum(i.fee) as "Total Fees" from dtoohey.patient p,
dtoohey.item i, dtoohey.account a, dtoohey.accountline al where p.PatientID=a.PatientID
and a.AccountNo=al.AccountNo and i.ItemNo=al.ItemNo group by p.state, i.description
Order by p.state ;

j. select p.patientID, p.familyName from dtoohey.patient p, dtoohey.account a,
dtoohey.item i, dtoohey.accountline al where p.PatientID=a.PatientID and
a.AccountNo=al.AccountNo and al.itemNo=i.itemNo group by p.patientID,
p.familyName having count(*)=(select count(*) from dtoohey.item);

Question 3: Further SQL (Hernandez, 2013)
a. SQL for creation of INSTRUCTOR table with Primary Key
CREATE TABLE INSTRUCTOR (InstructorName varchar2(30) primary key,

InstructorEmail varchar2(40) not null,
Biography varchar2(70) not null,
Specialty varchar2(50) not null
);
b. SQL for creation of TIMETABLE table with Primary Key and Foreign Key with appropriate
data types and deletion Constraint.
CREATE TABLE TIMETABLE (SessionID number(20) primary key ,
DayAndTime date NOT NULL,
NumberOfPlaces number(20) NOT NULL,
ClassName varchar2(30) NOT NULL,
VenueName varchar2(50) NOT NULL,
InstructorName varchar2(30) NOT NULL,
FOREIGN KEY(InstructorName) REFERENCES INSTRUCTOR(InstructorName) ON DELETE
CASCADE);
c. Insertion of Self record in INSTRUCTOR table.
Insert into INSTRUCTOR values (‘John’, ‘[e-mail]’, ’ Fitness’,’ Certified Strength and
Conditioning Specialist’);

d. Create constraint for instructor table to resist the insertion for class name given below.
ALTER TABLE TIMETABLE
ADD CONSTRAINT venue_chk CHECK ( VenueName NOT IN (
'Main Group Fitness Studio',
'Indoor Cycle Studio',
'Mind and Body Studio',
'25m Lap Pool',
'Warm Water Pool'
) );
e. Sql for fact number of places increased by 5 if class for sessions is CycleMax.
CREATE OR REPLACE TRIGGER increased_by
before INSERT on TIMETABLE
for each row
BEGIN
if :new.ClassName ='CycleMax' then
insert into TIMETABLE
values(:new.SessionId, :new.DayAndTime,:new.NumberOfPlaces+5,:new.ClassName, :new.VenueName,
:new.InstructorName);
else
insert into TIMETABLE
values(:new.SessionId, :new.DayAndTime,:new.NumberOfPlaces,:new.ClassName, :new.VenueName, :n
ew.InstructorName);
END IF;
END;
/
Question 4: Normalization (Hernandez, 2013)