Solving SQL Queries Using Schema Diagram And Tables

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

Recent Posts

See All