Relational Database Principles And SQL Assignment

Answer

1. List the name and specialty of all instructors.

Π _{(InstructorName, Secialty)}^(INSTRUCTOR)

1. List the class name and description of all classes suitable for ‘Over 60s’.

Π _{ClassName, Description}(σ_{SuitableFor=’Over 60’s’}^(CLASS))

1. List the names of instructors who run sessions in the classes ‘Aqua Deep’, ‘Aqua Fit’, or both.

Π_{InstructorName, ClassName} (σ_{ClassName=’Aqua Deep’ OR ClassName=’Aqua Fir’ Or ClassName=’both’}(INSTRUCTORE?_{InstructorName= IntructorName} SESSION?_{ClassName=ClassName}CLASS))

1. 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.

Π_{InstructorName, ClassName} (σ_{DayAndTime=’19/9/2018’} (MEMBER ?_{MemberID=MemberID} PARTICIPAT? _{SessionID=SessionID} SESSION ?_{ClassName=ClassName}CLASS))

1. List the names of members who participated in any class with a venue capacity greater than 30.

Π_{InstructorName, ClassName} (σ_Capcity>30 (MEMBER ?_{MemberID=MemberID} PARTICIPAT?_{SessionID=SessionID} SESSION ?_{ClassName=ClassName}CLASS AND SESSION ? _{VenueName=VenueName}Venue

1. 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).

Π * (σ_{DayAndTine=’19/9/2018} ^(SESSION) AND Π_MemberName ^(MEMBER) (MEMBER?_{MemberID=MemberID}PARTICIPANT?_{SessionID=SessionID}SESSION))

1. List the names of any instructors who run sessions in both Studio 1 and Studio 2.

Π_{InstructorName, SessionID}(σ_{SessionID=’Studio 1’ AND SessionID=’Studio 2’} (VENUE ?_{VenueName=VenueName}Session))

1. 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=’Over 60’s ‘ AND Escription=’Warm Water Pool’ OR ‘Both’} (CLASS?_{Classname=Classname} Session?_{SessionID=SessionID} PARTICIPENT ?_{MemberID=MemberID}MEMBER))

1. List the names of the members who have NOT participated in a ‘Zumba’ class.

Π_MemberName (σ_{ClassName=’Zumba’} (CLASS?_{Classname=Classname} Session?_{SessionID=SessionID} PARTICIPENT ?_{MemberID=MemberID}MEMBER))

1. List the names of members who have participated in all of the different classes offered.

Π_MemberName ,_ClassName(CLASS?_{Classname=Classname} Session?_{SessionID=SessionID} PARTICIPENT ?_{MemberID=MemberID}MEMBER))

Question 2: – Select Queries

CREATE TABLE PATIENT(

PatientID NUMBER(10) PRIMARY KEY,

FamilyName VARCHAR2(30) NOT NULL,

GivenName VARCHAR(30) NOT NULL,

Address VARCHAR2(150) NOT NULL,

Suburb VARCHAR(30) NOT NULL,

State  VARCHAR(20) NOT NULL,

PostCode NUMBER(10) NOT NULL

CREATE TABLE DOCTOR

(ProviderNo NUMBER(10) PRIMARY KEY ,

Name VARCHAR(50) NOT NULL

CREATE TABLE ITEM

(ItemNo NUMBER(10) PRIMARY KEY,

Description LONG NOT NULL,

Fee DECIMAL(10,2) NOT NULL

CREATE TABLE ACCOUNT

(AccountNo NUMBER(10) PRIMARY KEY,

ProviderNo NUMBER(10) NOT NULL,

PatientID NUMBER(10) NOT NULL,

TreatmentDate DATE NOT NULL,

FOREIGN KEY (ProviderNo) references DOCTOR(ProviderNo),

FOREIGN KEY (PatientID) references PATIENT(PatientID)

CREATE TABLE ACCOUNTLINE

(AccountNo NUMBER(10) NOT NULL,

ItemNo NUMBER(10) NOT NULL,

PRIMARY KEY (AccountNo, ItemNo),

FOREIGN KEY (AccountNo) references ACCOUNT(AccountNo),

FOREIGN KEY (ItemNo) references ITEM(ItemNo)

Insert data into tables: –

INSERT INTO PATIENT VALUES (1, ‘Eggert’,’Betty’,’US’,’US’,’SA’,89098);

INSERT INTO PATIENT VALUES (2, ‘Robin’,’Betty’,’US’,’US’,’NSW’,89091);

INSERT INTO PATIENT VALUES (3, ‘Caan’,’Betty’,’US’,’US’,’WA’,89092);

INSERT INTO PATIENT VALUES (4, ‘Raan’,’Betty’,’US’,’US’,’SA’,89093);

INSERT INTO PATIENT VALUES (5, ‘Mikka’,’Betty’,’US’,’US’,’WA’,89048);

INSERT INTO DOCTOR VALUES (1,’Dr Brian’);

INSERT INTO DOCTOR VALUES (2,’Dr Barbara’);

INSERT INTO DOCTOR VALUES (3,’Dr Lma’);

INSERT INTO DOCTOR VALUES (4,’Dr Kabin’);

INSERT INTO DOCTOR VALUES (5,’Dr Meena’);

INSERT INTO ITEM VALUES (1, ‘DFGHJ’,78.09);

INSERT INTO ITEM VALUES (2, ‘DFGH’,18.09);

INSERT INTO ITEM VALUES (3, ‘CVBN’,28.09);

INSERT INTO ITEM VALUES (4, ‘ERTY’,38.09);

INSERT INTO ITEM VALUES (5, ‘ASDF’,48.09);

INSERT INTO ACCOUNT VALUES (1,1,1,’01-JUN-2010′);

INSERT INTO ACCOUNT VALUES (2,2,2,’02-JUN-2010′);

INSERT INTO ACCOUNT VALUES (3,3,3,’03-JUN-2010′);

INSERT INTO ACCOUNT VALUES (4,4,4,’04-JUN-2010′);

INSERT INTO ACCOUNT VALUES (5,5,5,’05-JUN-2010′);

INSERT INTO ACCOUNTLINE VALUES (1,1);

INSERT INTO ACCOUNTLINE VALUES (2,2);

INSERT INTO ACCOUNTLINE VALUES (3,3);

INSERT INTO ACCOUNTLINE VALUES (4,4);

INSERT INTO ACCOUNTLINE VALUES (5,5);

Queries: –

1. Family name and suburb of patients who live in the State named ‘SA’.

SELECT FAMILYNAME, SUBURB, STATE FROM PATIENT

WHERE STATE=’SA’;

1. Family name and suburb of patients who live in the State named ‘NSW’ or the State named ‘SA’, in alphabetical order of family name.

SELECT FAMILYNAME, SUBURB, STATE FROM PATIENT

WHERE (STATE=’SA’ )

OR (STATE=’NSW’)

ORDER BY FAMILYNAME;

1. Name and suburb of patients who live in the State named ‘WA’ and have been treated by Dr Brian or Dr Barbara.

SELECT PATIENT.FAMILYNAME, PATIENT.GIVENNAME,

PATIENT.SUBURB, PATIENT.STATE, DOCTOR.NAME

FROM PATIENT, DOCTOR, ACCOUNT

WHERE PATIENT.PATIENTID=ACCOUNT.PATIENTID

AND DOCTOR.PROVIDERNO=ACCOUNT.PROVIDERNO

AND (PATIENT.STATE=’WA’)

AND ((DOCTOR.NAME=’Dr Brian’)

or (DOCTOR.NAME=’Dr Barbara’));

1. Name and suburb of patients treated by Dr Brian but not Dr Ima.

SELECT PATIENT.FAMILYNAME, PATIENT.GIVENNAME,

PATIENT.SUBURB,  DOCTOR.NAME

FROM PATIENT, DOCTOR, ACCOUNT

WHERE PATIENT.PATIENTID=ACCOUNT.PATIENTID

AND DOCTOR.PROVIDERNO=ACCOUNT.PROVIDERNO

AND (DOCTOR.NAME=’Dr Brian’)

AND DOCTOR.NAME NOT IN (SELECT NAME FROM DOCTOR WHERE NAME =’Dr Lma’);

1. Number of different suburbs covered by each doctor.

SELECT  count(PATIENT.SUBURB) as “Number of different suburbs” ,

DOCTOR.NAME as “Doctor name”

FROM PATIENT, DOCTOR, ACCOUNT

WHERE PATIENT.PATIENTID=ACCOUNT.PATIENTID

AND DOCTOR.PROVIDERNO=ACCOUNT.PROVIDERNO

group by DOCTOR.NAME

1. Item Description and the treatment date of all treatments for any patients named Betty Eggert (i.e., Given name is Betty, family name is Eggert)

SELECT ITEM.DESCRIPTION, ACCOUNT.TREATMENTDATE

FROM ITEM, PATIENT, ACCOUNT, ACCOUNTLINE

WHERE PATIENT.PATIENTID=ACCOUNT.PATIENTID

AND ACCOUNTLINE.ACCOUNTNO = ACCOUNT.ACCOUNTNO

AND ITEM.ITEMNO=ACCOUNTLINE.ITEMNO

AND PATIENT.FAMILYNAME=’Eggert’

AND PATIENT.GIVENNAME=’Betty’;

1. The name of each doctor, and the total fees collected from visit to each of them in each year. Your answer should be presented in order of doctor name followed by year.

SELECT DOCTOR.NAME as “Doctor Name”, SUM(ITEM.FEE) as “Total fee”,

EXTRACT(YEAR from ACCOUNT.TREATMENTDATE) as “Year”

FROM DOCTOR, ACCOUNT, ACCOUNTLINE, ITEM

WHERE DOCTOR.PROVIDERNO=ACCOUNT.PROVIDERNO

AND ACCOUNTLINE.ACCOUNTNO = ACCOUNT.ACCOUNTNO

AND ACCOUNTLINE.ITEMNO=ITEM.ITEMNO

group by DOCTOR.NAME, EXTRACT(YEAR from ACCOUNT.TREATMENTDATE)

ORDER BY DOCTOR.NAME, EXTRACT(YEAR from ACCOUNT.TREATMENTDATE);

1. Doctors who have had more than the average number of consultations

There are no Consultation table or any attribute.

1. Total amount of fees collected for each type of consultation in each state, in alphabetical order of state.

There are no Consultation table or any attribute.

1. Patient ID and family name of patients who have had all types of treatments

There is no any treatment table where we find the type of treatments.

Question 3: -Further SQL

1. INSTRUCTOR table

create table INSTRUCTOR

(InstructorName VARCHAR(30) PRIMARY KEY,

InstructorEmail VARCHAR(50) NOT NULL,

Biography LONG NOT NULL,

Specialty VARCHAR(30) NOT NULL

1. TIMETABLE table.

CREATE TABLE TIMETABLE (

SessionID NUMBER(10) PRIMARY KEY,

Day VARCHAR(20) NOT NULL,

Time TIMESTAMP not null,

NumberOfPlaces number(4) not null,

ClassName varchar(30) not null,

VenueName varchar(50) not null,

InstructorName varchar(30) not null,

foreign key (InstructorName) references Instructor(InstructorName)

1. Add data in INSTRUCTOR table

INSERT INTO INSTRUCTOR VALUES (‘Robin1′,’[email protected]‘,’sdfghjk’,’C’);

INSERT INTO INSTRUCTOR VALUES (‘Caan’,’[email protected]‘,’dfgh’,’C++’);

INSERT INTO INSTRUCTOR VALUES (‘Raan’,’[email protected]‘,’dfghj’,’java’);

INSERT INTO INSTRUCTOR VALUES (‘Meena’,’[email protected]‘,’rghjjhbv’,’Database’);

1. Give the SQL to create a constraint to the TIMETABLE table to restrict the possible venues to the following 5: Main Group Fitness Studio, Indoor Cycle Studio, Mind and Body Studio, 25m Lap Pool, Warm Water Pool.

ALTER TABLE TIMETABLE

ADD CONSTRAINT VENUE CHECK (VENUENAME IN (‘Main Group Fitness Studio’,

‘Indoor Cycle Studio’, ‘Mind and Body Studio’, ’25m Lap Pool’, ‘Warm Water Pool’));

1. Give the SQL to record the fact that all the sessions of the CycleMax class have increased their number of places by 5. (Note: you don’t need to add any actual data to run the query, although you may do so if you wish.)

INSERT INTO TIMETABLE VALUES (1, ‘Sunday’,’26-JUN-02 09:39:16.78′,9,’CycleMax’,’25m Lap Pool’, ‘Caan’);

UPDATE TIMETABLE

SET NUMBEROFPLACES =NUMBEROFPLACES+5 WHERE CLASSNAME=’CycleMax’;

select *from timetable;

Question 4: – Normalization

1. What is the candidate key(s) of the relation? What normal form is the relation currently in? Explain your reasoning.

• Candidate key of this relation is (Tournament, Winner)
• This relation is in First normal Form (1NF). Because tis relation Contains Partial and transitive dependency. This relation not contains repeating Groups, so that this relation is in First normal Form (1NF).

1. Explain the problems with the existing design, in terms of the potential modification anomalies that it might exhibit.

• Insert: – if we want to insert the Tournament then we need to add winner data. In this table we observe that each tournament has two events and each event related to a single winner. So we need to add multiple records.
• Update: – if we update any tournament then its cross ponding we need to update that tournament winner, location, year, date, surface etc. may be a tournament have multiple winner or location. So here we need to update multiple records if we want to update a single value.
• Delete: – if we delete a tournament then we also lost the winner, location, venue surface, event data.

1. Convert the relation to a set of relations in at least Third Normal Form (3NF). You only need to show the schema, not the data. Do not create any new attributes. Give each of your new relations an appropriate name. Show all primary keys and foreign key  

1. Explain how your new design addresses the problems you identified in (b). Also demonstrate that your set of relations has the dependency preserving and lossless join properties.

• new design addresses the problems you identified: –

1. Insertion: – here if we want to add data in tournament table then we not need to add data in other table. Just add tournament with proper venue.
2. Updation: – if we want if we want to update Tournament data them we not need to update winner data etc.
3. Deletion: – if we delete the Tournament then we not lost the any other data because all other data store in Different tables. 

• Dependency preserving: –

1. Here Given Relation is (Tournament, Year, Event, Winner, Country, Sponsor, Venue, Dates, Location, and Surface)

1. Function Dependency: –

Tournament -> {Venue, Date}

Venue -> {Location, Surface}

Winner -> {Country, Sponsor}

(Tournament, Winner)-> {Event}

1. Decompose Relations are: –

Tournament (Tournament, Venue, date)

Venue (Venue, location, Surface)

Winner_Info (Winner, Country, Sponsor)

Tornament_Event (Tournament, Winner, Event)

From the above information we get that function dependency is the part of decomposed relation. Hence these set of relation has the dependency preserving.

• lossless join properties: –

1. Here union of all relations attribute is equal to given relation attribute.
2. The intersection of all relations attributes is not null.
3. Here common attribute is a key or a primary key.

The all relation in 3NF follow all the properties of Lossless join so that all relation has lossless join.

Question 5: – Conceptual Design

1.     Assumptions: –

• Member Work for many Services and one service has many member.
• A member request for work many times and each request is related to one and only one member.
• A member participates in many event but some places and an event is related to many members.
• People offer many work and member work for that people zero or many time.
• Work is divided into swap. So that a work has many swap and a swap is related to many works.

References

Chia, K., Seow, E., & Teo, K. (2004). Database. Singapore: Pearson Prentice Hall.

Coronel, C., & Morris, S. (2018). Database Systems. Mason, OH: Cengage Learning US.