staff (ID, fname, lname, role) patient (pID,pFname, pLname, bdate, address, phon
ID: 3597512 • Letter: S
Question
staff (ID, fname, lname, role) patient (pID,pFname, pLname, bdate, address, phone) appointment (aptiD, patientID, staffID, aptDate, aptTime) procedures (procNum, pName, price) aptDetail (aptID, _procNo) The dental practice has a number of staff with different care-giving roles (role) such as ‘dentist', 'hygienist' and 'dental nurse. Also stored in the staff table are: a unique staff id (ID) and the staff's name (fname and 1name). Details stored on patients in the patient table are: a unique ID (pID), name (pFname and pLname), birth date (bdate), address (address) and phone number (phone). Each patient may have zero or more appointments listed in the appointment table. Each appointment has a unique ID (aptID). Also stored is the patient ID (patientID), the ID of the staff member booked for the appointment (staffID), and the date and time of the appointment (aptDate and aptTime). A staff member may have many appointments. The foreign keys in the table are patientID and staffID. The dental practice stores a list of the procedures it carries out (e.g., named as cleaning, filling,'extraction, etc.), and their cost, in the procedures table. Details stored are a unique procedure number (procNum), the name of procedure (pName) and the price of the procedure (price). Details of the procedures that each patient receive, per appointment, are recorded in the aptDetail table by listing the appointment ID (aptID) and the procedure number (procNo) for one or more proceedures. The foreign keys in the table are aptID and procNo. Q1 COMPULSORY: Using the dental practice relational schema, write SQL code and queries to satisfy the following information needs (a) (b) (c) (d) (e) (f) (g) (h) Choosing suitable data types, write the SQL code required to create the appointment table. You may assume that the tables stafr and patient already exist. (5) List the names of staff who have the role of "dental nurse' in the practice. (5) Using a subquery approach, list the date and time of patient Sorcha Doyle's appointment(s). (5) Find the name and price of the most expensive procedure in the practice. (5) Using the explicit join syntax, list the names of all patients who have appointments with the dentist 'Owen Smyth'. (5) Using the implicit join syntax, find the total cost of the procedures that were carried out on patient 'Sean Bohan'. (5) For each staff member who has more than one appointment, list the staff name, their role and the number of appointments they have. (5) Find all patients (list their names) who have no appointments scheduled. (5)Explanation / Answer
SQL Queries
a)
# Create table command is used to create table ‘appointment’. ‘aptID’ is set a primary key. Attribute ‘patientID’, ‘staffID’ are declared as foreign key referring relation ‘patient’ and ‘staff’ respectively.
CREATE TABLE appointment
(
aptID INTEGER,
patientID VARCHAR(10),
staffID VARCHAR(10),
aptDate DATE,
aptTime TIME,
PRIMARY KEY (aptID),
FOREIGN KEY (patientID) REFERENCES patient (pID),
FOREIGN KEY (staffID) REFERENCES staff(ID)
);
b)
# Attribute ‘fname’ and ‘lname’ are selected from the ‘staff’ relation on condition where role of the staff is ‘dental nurse’.
SELECT fname, lname
FROM staff
WHERE role = ‘dental nurse’;
c)
# Attribute 'aptDate' and 'aptTime' is selected from the appointment relation. In the where clause a subquery is used to get the patientID of patient name 'sorcha doyle' from the 'patient' relation, which is matched with 'patientID' attribute of 'appointment' table.
SELECT aptDate, aptTime
FROM appointment
WHERE patientID IN (SELECT pID FROM patient WHERE pFname = ‘Sorcha’ AND pLname = ‘Doyle’);
d)
# name and price of the procedure is selected from the 'procedures' relation. In where clause price of the procedure is matched with the maximum price of the procedure (max price is fetched using a sub query).
SELECT pName, price
FROM procedures
WHERE price = (SELECT MAX(price) FROM procedures);
e)
# Join is used among the 3 relation namely 'staff', 'patient', 'appointment' based on common attribute of two tables.
SELECT pFname, pLname
FROM patient AS p
INNER JOIN appointment AS a
ON p.pID = a.patientID
INNER JOIN staff AS s
ON s.ID = a.staffID
WHERE a.role = ‘dentist’ AND fname = ‘ Owen’ AND fname = ‘Smyth’;
f)
# Four table namely 'procedures', 'aptDetail', 'appointment', 'patient' are joined implicitly. In the where clause name of the patient is matched with the given name.
SELECT SUM(price)
FROM procedures, aptDetail, appointment, patient
WHERE patient.pFname = ‘Sean’ AND pLname = ‘Bohan’ ;
g)
# Aggregation COUNT is used to get the count of appointment. Group by is used on fname, lname and role attribute of staff relation. Having clause is used to filter the count > 1.
SELECT fname, lname, role, COUNT(aptID) AS total_appointment
FROM staff INNER JOIN appointment
ON ID = staffID
GROUP BY fname, lname, role
HAVING COUNT(aptID) > 1
h)
# Patient name is selected. In where clause pID of patient table is matched with patientID available in the appointment table using a sub query.
SELECT pFname, pLname
FROM patient
WHERE pID NOT IN (SELECT DISTINCT patientID FROM appointment) ;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.