QUESTION 1
This question is set in the context of a small database that stores information about patients, medications, and prescriptions. A partial schema is shown in the figure below:
The tables are populated with the following data:
Query 1:
SELECT PatientLastName, COUNT(*) AS Num FROM Patient pat INNER JOIN Prescription pre ON pat.PatientID = pre.PatientID GROUP BY pat.PatientLastName ORDER BY pat.PatientLastName ASC;
Query 2:
SELECT PatientLastName, COUNT(*) AS Num FROM Patient pat LEFT OUTER JOIN Prescription pre ON pat.PatientID = pre.PatientID GROUP BY pat.PatientLastName ORDER BY pat.PatientLastName ASC;
Query 3:
SELECT PatientLastName, COUNT(pre.MedicationID) AS Num FROM Patient pat LEFT OUTER JOIN Prescription pre ON pat.PatientID = pre.PatientID GROUP BY pat.PatientLastName ORDER BY pat.PatientLastName ASC;
Query 4:
SELECT HourOfDay, COUNT(*) AS NumRegimes FROM AdminTime GROUP BY HourOfDay HAVING COUNT(*) > 1;
Query 5:
SELECT AVG(x.NumRegimes) AS AvgOfNumRegimes FROM (SELECT HourOfDay, COUNT(*) AS NumRegimes FROM AdminTime GROUP BY HourOfDay HAVING COUNT(*) <2 ) AS x;
Query 6:
SELECT COUNT(PatientLastName) AS NumPatientNames FROM Prescription pre INNER JOIN Patient pat ON pre.PatientID = pat.PatientID;
Query 7:
SELECT PatientLastName FROM Patient pat WHERE NOT EXISTS (SELECT * FROM Prescription pre WHERE pre.PatientID = pat.PatientID);
Query 8:
SELECT PatientLastName, PrescriptionID FROM Patient pat LEFT OUTER JOIN Prescription pre ON pat.PatientID = pre.PatientID WHERE PrescriptionID IS NULL;
Question 2:
Draw an ERD for the following situation
A laboratory collects specimens that may later be analysed. For each specimen collected, the database should record a unique SpecNo. It should also specify SpecArea, and SpecCollMethod
A specimen is analysed when a test order is issued. A specimen may not have a test order until after a considerable delay
A test order contains a unique test order number (TONo), TOTestName, TOTestType and TOTestResult
A test order is created for exactly one specimen
The database should keep track of supplies needed for test orders
A test order can use a collection of supplies (0 or more) and a supply can be used on a collection of test orders (0 or more). The Supply entity type contains a unique SuppNo, SuppName, SuppLotNo, and SuppNoInStock
Notes
M:N relationships should be modelled with associative entities
Choose appropriate names for all relationships and entity types based on your common knowledge of test orders and supplies
Use doubled line relationships and rectangles to represent weak entities. Underline identifiers that are likely to become primary keys
QUESTION 3
The following ERD represents a data model for tracking the allocation of laboratory equipment to chemists working on projects.
Convert the ERD into a set of relational schemas. Indicate the functional dependencies, and the PK-FK relationships with arrows. Convert all relations into 3NF. Use this sort of format to represent relations.
Contact us to get any help related to SQL Databases, SQL Queries or other database database related help like:
Database Homework Help
SQL Assignment Help
Database Project Help
contact@codersarts.com