ICT285 Databases Assignment 1: SQL, Relational Algebra - Murdoch Uni

This document presents a comprehensive solution to a Databases assignment, focusing on relational algebra and SQL. It includes solutions to questions involving relational algebra expressions for querying a database, SQL queries for creating and manipulating database tables, and database normalization to 3NF. The assignment covers creating tables, inserting data, adding constraints, and addressing dependencies within a database schema. An ER diagram illustrating the relationships between entities in a database is also included. Desklib offers a variety of resources including past papers and solved assignments to aid students in their studies.

Question 1: Relational algebra:
a)
π InstructorName, Specialty (INSTRUCTOR)
b)
π ClassName, Description (σ (SuitableFor=’Over 60s’) CLASS )
c)
π InstructorName (σ (classname=’Aqua Deep’ or classname=’Aqua Fit’ ) SESSION )
d) ClassName, VenueName
π MemberName (σ (DayAndTime=’19/9/2018’ and VenueName=’Studio 5’ ) ) PARTICIPANT⋈MEMBER ⋈
SESSION
e)
π MemberName (σ (Capacity>30) ) PARTICIPANT⋈MEMBER ⋈ SESSION⋈ VENUE
f)
π MemberName (σ (DayAndTime=’19/9/2018’) ) PARTICIPANT⋈MEMBER ⋈ SESSION
g)
π InstructorName (σ (VenueName=’Studio 1’ or VenueName=’Studio 2’ ) SESSION )
h)
π MemberName (σ (VenueName=’Warm Water Pool’ or SuitableFor=’Over 60s’ ) )
PARTICIPANT⋈MEMBER ⋈ SESSION ⋈ CLASS
i)
π MemberName (σ (ClassName<>’Zumba’ )) PARTICIPANT⋈MEMBER ⋈ SESSION
j)
π MemberName PARTICIPANT⋈MEMBER ⋈ SESSION

Question 3:
a) SQL for creating Instructor Table
CREATE TABLE INSTRUCTOR
( InstructorName varchar2(50) not null,
InstructorEmail varchar2(50) not null,
Biography varchar2(200),
Specialty varchar2(50),
CONSTRAINT INSTRUCTOR_pk PRIMARY KEY (InstructorName)
);
b) SQL for Creating TIMETABLE table:
CREATE TABLE TIMETABLE
( SessionID numeric(10) not null,
DayAndTime Date not null,
NumberOfPlaces numeric(10) not null,
ClassName varchar2(50) not null,
VenueName varchar2(200),
InstructorName varchar2(50),
CONSTRAINT TIMETABLE_pk PRIMARY KEY (SessionID),
CONSTRAINT fk_INSTRUCTOR FOREIGN KEY (InstructorName)
REFERENCES INSTRUCTOR(InstructorName)
);

c) Insert Query on instructor Table:
INSERT INTO INSTRUCTOR VALUES ('john peterson','[e-mail]','I am student
and working on Database','Database');
d) Adding constraint on TIMETABLE:
ALTER TABLE TIMETABLE
MODIFY (VenueName varchar2(200) NOT NULL CONSTRAINT VENUE_CONSTRAINT
CHECK (VenueName IN ('Main Group Fitness Studio', 'Indoor Cycle Studio', 'Mind and
Body Studio', '25m Lap Pool', 'Warm Water Pool')));

e) CycleMax places to 5:
INSERT INTO TIMETABLE values (1,TO_DATE('2019/09/19',
'yyyy/mm/dd'),5,'CycleMax','Indoor Cycle Studio','john peterson');
Question 4:
a)
The candidate keys in the given relationship is Tournament, Year and Event. The given relationship
is in UNF (Unnormalized form and it is a heap). The given relation has different type of
dependencies which make it UNF. The dependencies are partial dependency, Transitive dependency
and Functional Dependency.
b)
The following diagram shows the issues exists in the given relation, the relation has various
dependencies hence which creates delete/ update anomalies in the relation and it must be converted
into 3 NF to remove these dependencies.

c)
The above mentioned dependencies are removed and the relation is designed in 3 NF form by
creating tables corresponding to dependencies:
d)
Seperate tables are created for the dependencies which solved the issues of anomalies as the
separate table does not have redundancies and are in 3NF hence in this way the issue is resolved.

Question 5:
ER diagram:
Assumptions made:
1) One member can participate in zero or many events and one event can have one or more sign up.
2) One member can post many request and similarly one request can have many swap entries.