Database Assignment Solution: SQL, Relational Algebra & Design

This document provides a complete solution to a database assignment, encompassing multiple key areas. The solution begins with relational algebra expressions, demonstrating proficiency in data retrieval and manipulation. It then proceeds to address SQL queries, offering practical examples of SELECT statements to extract specific information from a database, including queries with JOIN operations, filtering, and aggregation. The assignment further explores database design, including the creation of tables with appropriate constraints. Normalization is addressed, with an analysis of the original relation's anomalies and a proposed 3NF design to eliminate them. Finally, the solution concludes with a conceptual design, outlining the entities and relationships within a database schema. This comprehensive approach demonstrates a strong understanding of database concepts and practical application. The document is contributed by a student to be published on the website Desklib, a platform which provides all the necessary AI based study tools for students.

QUESTION 1 : RELATIONAL ALGEBRA
1. ΠInstructorName, Specialty(INSTRUCTOR)
2. ΠClassName, DescriptionσSuitableFor>60(CLASS)
3. ΠInstructorNameσClassName = “Aqua Deep”˅ClassName=”Aqua Fit”(SESSION x INSTRUCTOR)
4. ΠMemberName, ClassNameσVenueName = “Studio 5”˄DayAndTime=”19/9/2018”(MEMBER∩
PARTICIPANT ∩ SESSION)
5. ΠMemberName(MEMBER∩ PARTICIPANT ∩(σCapacity>30 (SESSION x VENUE)))
6. ΠMemberName, SESSION.*σDayAndTime=”19/9/2018”(MEMBER∩ PARTICIPANT ∩ SESSION)
7. ΠInstructorNameσVenueName=”Studio 1”˄ VenueName=”Studio 2”(SESSION)
8. ΠMemberName(MEMBER∩ PARTICIPANT ∩ (σSuitableFor>60 ˅ VenueName=”Warm Water Pool”
(SESSION x CLASS)))
9. ΠMemberName(MEMBER∩ PARTICIPANT –(σClassName=”Zumba”(SESSION)))
10. ΠMemberName(MEMBER∩ PARTICIPANT ∩ SESSION)
QUESTION 2 : SQL-SELECT QUERIES
1. SELECT FamilyName,Suburb
FROM PATIENT
WHERE State = "SA";
2. SELECT FamilyName,Suburb
FROM PATIENT
WHERE State = "SA" OR State = "NSW"
ORDER BY FamilyName;
3. SELECT p.GivenName,p.Suburb
FROM PATIENT p INNER JOIN ACCOUNT a ON p.PatientID=a.PatientID INNER JOIN DOCTOR d
ON a.ProviderNo=d.ProviderNo
WHERE p.State = "WA" AND (d.Name="Dr Brian" OR d.Name="Dr Barbara");
4. SELECT p.GivenName,p.Suburb
FROM PATIENT p INNER JOIN ACCOUNT a ON p.PatientID=a.PatientID INNER JOIN DOCTOR d
ON a.ProviderNo=d.ProviderNo
WHERE d.Name="Dr Brian" AND d.Name <> "Dr Ima";
5. SELECT COUNT(p.Suburb)
FROM PATIENT p INNER JOIN ACCOUNT a ON p.PatientID=a.PatientID INNER JOIN DOCTOR d
ON a.ProviderNo=d.ProviderNo
GROUP BY a.ProviderNo;

6. SELECT i.Description,a.TreatmentDate
FROM ITEM i INNER JOIN ACCOUNTLINE al ON i.ItemNo=al.ItemNo INNER JOIN ACCOUNT a
ON al.AccountNo=a.AccountNo
WHERE a.PatientID=(SELECT PatientID FROM PATIENT WHERE FamilyName="Eggert" AND
GivenName="Betty");
7. SELECT d.Name,SUM(i.Fee)
FROM DOCTOR d INNER JOIN ACCOUNT a ON d.ProviderNo=a.ProviderNo INNER JOIN
ACCOUNTLINE al ON a.AccountNo=al.AccountNo INNER JOIN ITEM i ON al.ItemNo=i.ItemNo
GROUP BY d.Name
ORDER BY d.Name,a.TreatmentDate;
8. SELECT d.Name
FROM DOCTOR d INNER JOIN ACCOUNT a ON d.ProviderNo=a.ProviderNo INNER JOIN
ACCOUNTLINE al ON a.AccountNo=al.AccountNo INNER JOIN ITEM i ON al.ItemNo=i.ItemNo
WHERE i.Fee>(SELECT AVG(FEE) FROM ITEM)
GROUP BY d.Name;
9. SELECT SUM(i.Fee)
FROM ITEM i INNER JOIN ACCOUNTLINE al ON i.ItemNo=al.ItemNo INNER JOIN ACCOUNT a
ON al.AccountNo=a.AccountNo INNER JOIN PATIENT p ON a.PatientID=p.PatientID
GROUP BY i.Description,p.State
ORDER BY p.State;
10. SELECT p.PatientID,p.FamilyName
FROM PATIENT p INNER JOIN ACCOUNT a ON p.PatientID=a.PatientID;
QUESTION 3 : FURTHER SQL
1. CREATE TABLE INSTRUCTOR(
InstructorName VARCHAR(15),
InstructorEmail VARCHAR(20),
Biography VARCHAR(30),
Specialty VARCHAR(15),
CONSTRAINT PK_Instructor PRIMARY KEY (InstructorName)

);
2. CREATE TABLE TIMETABLE(
SessionID INT,
DayAndTime DATE,
NumberOfPlaces INT,
ClassName VARCHAR(15),
VENUENAME VARCHAR(15),
InstructorName VARCHAR(15),
CONSTRAINT PK_Timetable PRIMARY KEY (SessionID),
CONSTRAINT FK_Instructor FOREIGN KEY (InstructorName) REFERENCES
INSTRUCTOR(InstructorName)
);
3. INSERT INTO INSTRUCTOR(InstructorName,InstructorEmail,Biography,Specialty)
VALUES ("Betty","[e-mail]","Betty Biography","Thighs");
4. ALTER TABLE TIMETABLE
ADD CONSTRAINT CHK_VenueName CHECK (VenueName="Main Group Fitness Studio" OR
VenueName="Indoor Cycle Studio" OR VenueName="Mind and Body Studio" OR
VenueName="25m Lap Pool" OR VenueName="Warm Water Pool");
5. UPDATE TIMETABLE
SET NumberOfPlaces=NumberOfPlaces+5;
QUESTION 4 : NORMALISATION
1. The relation is currently in First normal form. It is so because each row is unique and stores
atomic values but none of the attribute defines the table.
Candidate Keys: {Winner,Dates}
2. Update Anomaly: A player is addressed in multiple rows. Say if the Country of the player is to
be changed then it has to be changed in all the rows the player is listed.
Delete Anomaly: Suppose the committee decides to drop the records of year 2016. Deleting
so would result in losing the details of the players won that year.
3. 3NF:
PLAYER: {Winner,Country,Sponsor}
TOURNAMENT: {Tournament, Venue, Location, Surface}
EVENT: {Event, Dates, Year, Tournament, Winner}
Primary Keys are those which are underlined. Foreign Keys are the bold ones.
4. Handling Update Anomaly: Now since there is only one entry of the player in PLAYER table.
So updating country won’t affect the other information.
Handling Delete Anomaly: Deleting records for the year 2016 will only affect the EVENT table
and rest of the relations will stay intact.
Also, since each table now has a primary key so it is now easy to access the information of a
relation since it has a defining attribute now.
QUESTION 5 : CONCEPTUAL DESIGN

Assumption:
1. For each Table, we have assumed a unique ID as its Primary Key.
2. The shortlist will contain the workerID of the shortlisted member.
3. The attribute status in Request is to check the open or closed status of the request.
4. The status attribute in Swap is to check whether both the member have completed the
services or not.