Relational Algebra and SQL Queries

This assignment content includes questions on relational algebra, SQL SELECT queries, further SQL, normalization, and conceptual design. The relational algebra questions involve querying instructor names, class descriptions, and venue information. The SQL SELECT queries cover various scenarios such as filtering by state, joining tables, grouping results, and calculating aggregates. The further SQL questions include creating a table, adding a constraint, updating data, and handling anomalies during normalization. Finally, the conceptual design assumes unique IDs as primary keys for each table.

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.