HOMEWORK ASSIGNMENT 3

docx

School

Columbia University *

*We aren’t endorsed by this school

Course

5310

Subject

Computer Science

Date

Feb 20, 2024

Type

docx

Pages

Uploaded by DeaconExploration13239

------------------------------------------------------------------------------- ------------------------------------------------------------------------------- -- HOMEWORK ASSIGNMENT 3 /* * NOTES: * * - Type your SQL statements between the START and END tags for each * question. Do not alter this template .sql file in any way other than * adding your answers. Do not delete the START/END tags. The .sql file * you submit will be validated before grading and will not be graded if * it fails validation due to any alteration of the commented sections. * * - Our course is using PostgreSQL which has been preinstalled for you in * Codio. We grade your assignments in PostgreSQL. You risk losing points * if you prepare your SQL queries for a different database system * (MySQL, MS SQL Server, Oracle, etc). * * - It is highly recommended that you insert additional, appropriate data * to test the results of your queries. You do not need to include your * sample data in your answers. * * - Make sure you test each one of your answers. If a query returns an * error it will earn no points. * * - In your CREATE TABLE statements you must provide data types, * primary/foreign keys and integrity constraints (if applicable). * * - You may expand your answers in as many lines as you need between the * START/END tags. * */ ------------------------------------------------------------------------------- /* * QUESTION 1 (6 points: 1 point for each table plus 1 point for correct order

* of execution) * ---------------------------------------------------------------------------- * * You are tasked to create a simplified database of a hospital. Provide the * SQL statements that create the five tables with data types of your design. * Implement integrity constraints (primary/foreign keys, NOT NULL) as needed. * Note: since underlining is not supported in this file format, primary keys * for each relation below are shown within '*'. * * clinics (*clinic_id*, address_id, name) * patients (*patient_id*, address_id, name, gender, dob) * addresses (*address_id*, street, city, state, zip_code, country) * examinations (*patient_id*, *doctor_id*, *exam_date*, exam_cost, exam_notes) * doctors (*doctor_id*, address_id, clinic_id, name, specialty) * * * Type the CREATE TABLE statements in the order they have to be executed so * that there is no error in PostgreSQL. Expand the space between the START/END * tags to fit all of your CREATE TABLE statements. * * IMPORTANT: Make sure to implement the schema with exactly the provided * relation and attribute names. Do not rename relations or * attributes. * * * Attribute Descriptions: * ----------------------- * * clinics * ------- * clinic_id: unique ID for clinics (PK) * address_id: unique ID for addresses, this is the address of the clinic * name: the clinic's name (i.e. "Brooklyn Methodist Hospital", etc.) * * patients * -------- * patient_id: unique ID for patients (PK) * address_id: unique ID for addresses, this is a patient's home address * name: full name for a patient * gender: the gender that a patient identifies as * dob: the date of birth of a patient * * addresses * ---------

* address_id: unique ID for addresses (PK) * street: street name, number and apartment (if applicable) * city: city name * state: state name, not state abbreviation * zip_code: zip code * country: country name * * examinations * ------------ * patient_id: unique ID for patients (PK) * doctor_id: unique ID for doctors (PK) * exam_date: date and time of a patient examination by a doctor (PK) * exam_cost: cost of examination * exam_notes: notes on the examination (if any) * * doctors * ------- * doctor_id: unique ID for doctors (PK) * address_id: unique ID for addresses, this is a doctor's home address * clinic_id: unique ID for clinics * name: full name for a doctor * specialty: doctor specialty (i.e. pediatrician, ophthalmologist, etc.) * */ -- START ANSWER 1 -- CREATE TABLE addresses( address_id SERIAL PRIMARY KEY, street VARCHAR(200) NOT NULL, city VARCHAR(100) NOT NULL, state VARCHAR(100) NOT NULL, zip_code VARCHAR(10) NOT NULL, country VARCHAR(100) NOT NULL) ; CREATE TABLE clinics( clinic_id SERIAL PRIMARY KEY, address_id INTEGER REFERENCES addresses(address_id) ON DELETE CASCADE, name VARCHAR(200) NOT NULL); CREATE TABLE patients( patient_id SERIAL PRIMARY KEY, address_id INTEGER REFERENCES addresses(address_id) ON DELETE CASCADE,

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

name VARCHAR(200) NOT NULL, gender VARCHAR(50) NOT NULL, dob DATE NOT NULL); CREATE TABLE doctors( doctor_id SERIAL PRIMARY KEY, address_id INTEGER REFERENCES addresses(address_id) ON DELETE CASCADE, clinic_id INTEGER REFERENCES clinics(clinic_id) ON DELETE CASCADE, name VARCHAR(200) NOT NULL, specialty VARCHAR(200) NOT NULL); CREATE TABLE examinations( patient_id INTEGER REFERENCES patients(patient_id) ON DELETE CASCADE, doctor_id INTEGER REFERENCES doctors(doctor_id) ON DELETE CASCADE, exam_date TIMESTAMP NOT NULL, exam_cost DECIMAL(10,2) NOT NULL, exam_notes TEXT, PRIMARY KEY(patient_id, doctor_id, exam_date)); -- END ANSWER 1 -- ------------------------------------------------------------------------------- /* * QUESTION 2 (2 points) * --------------------- * * Provide detailed reasoning on your selection of each one of the data types * above as well as your implementation of any/all integrity constraints. Include * any additional assumptions you made beyond the provided schema description. * Explain relationships and cardinalities. Type your answers as plain text * within the START/END tags. Expand your answer in as many lines as you need. * */ -- START ANSWER 2 -- /* * We will check for reasonable data types based on the answers shown above. We * will also check that primary and foreign keys have been properly defined as * well as some basic implementation of NOT NULL, as needed. More important, * we want to see well-defined reasoning on the data types, integrity constraints * and established relationships and cardinalities. Insufficient/short answers * will not receive full credit.

*/ /* Data Type Selection: SERIAL: Used for address_id, clinic_id, patient_id, and doctor_id. T hese are primary keys that need to be unique and auto-incrementing. SERIAL in PostgreSQL represents an auto-incrementing integer. VARCHAR: For textual fields like street, city, state, country, name, and specialty. The length parameters (like 255 or 100) provide a maximum number of characters, ensuring ample space to store various string values. DATE: Used solely for dob, as it explicitly represents a date. TIMESTAMP: Used for exam_date, as it represents not just a date but also the time. DECIMAL: Used for exam_cost to maintain precision, ensuring the stored value is for currency or another quantity requiring exact values. TEXT: For exam_notes, which could contain longer texts, thus the TEXT data type is apt. Integrity Constraints: PRIMARY KEY: Used to uniquely identify each row in a table, like address_id, clinic_id, patient_id, and doctor_id. REFERENCES and ON DELETE CASCADE: These establish foreign key relationships. For instance, clinic_id in the doctors table is a foreign key from the clinics table. ON DELETE CASCADE ensures, for example, when an address is deleted, all doctors, patients, or clinics referencing that address are also deleted, maintaining data integrity. NOT NULL: Ensures fields like name, street, city, etc., always have a value and aren't left empty. Relationships and Cardinalities:

addresses with other tables: One-to-many. For example, one address can be the address for multiple doctors, but each doctor has only one address. doctors with examinations: One-to-many. A doctor can conduct multiple examinations, but each examination is conducted by only one doctor. patients with examinations: One-to-many. A patient can have multiple examinations, but each examination pertains to only one patient. Additional Assumptions: Names aren't unique: In this schema, the names of doctors and patients are not set as unique, implying that there can be doctors or patients with the same name. -- END ANSWER 2 -- ------------------------------------------------------------------------------- /* * QUESTION 3 (6 points) * ---------------------- * * Draw the ER diagram for the schema detailed in Question 1 using the "textbook" * notation with the lines, rectangles and diamonds presented in our slides and * textbook. Pay close attention and properly define relationships and * cardinalities. The free educational version of Lucidchart is sufficient and * preferred. Hand drawn diagrams will not be accepted. Upload the ER diagram as * a separate file. * * Note: The position of lines/connectors matters!! Lines MUST start from and * end to relevant attributes. * */ -- No START/END tags here. Your answer is a separate PDF submitted along with -- this SQL file. ------------------------------------------------------------------------------- /*

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

* QUESTION 4 (4 points) * ---------------------- * * Draw the ER diagram for the schema detailed in Question 1 using the "engineering" * notation with the Crow's foot connectors and three-column tables (contraints, * attribute names, and data types). Pay close attention and properly define * relationships and cardinalities. The free educational version of Lucidchart is * sufficient and preferred. You DO NOT need to / SHOULD NOT automatically generate * the diagram, you can draw it manually as you did on the question above. Hand * drawn diagrams will not be accepted. Upload the ER diagram as a separate file. * * Note: The position of lines/connectors matters!! Lines MUST start from and * end to relevant attributes. * */ -- No START/END tags here. Your answer is a separate PDF submitted along with -- this SQL file. ------------------------------------------------------------------------------- /* * QUESTION 5 (4 points) * --------------------- * * Provide the SQL statement that returns the doctor name, specialty and total * number of patients they have examined in the last six months (as of the date * the query is ran). * * Reminder: It is highly recommended that you insert appropriate sample data * to test the results of your queries. Also, you cannot create a view or * answer a question with multiple queries unless stated. This is applies to * all questions below. * */ -- START ANSWER 5 -- SELECT d.name AS doctor_name, d.specialty, COUNT(e.patient_id) AS total_patients_examined

FROM doctors d JOIN examinations e ON d.doctor_id = e.doctor_id WHERE e.exam_date BETWEEN CURRENT_DATE - INTERVAL '6 months' AND CURRENT_DATE GROUP BY d.name, d.specialty; -- END ANSWER 5 -- ------------------------------------------------------------------------------- /* * QUESTION 6 (3 points) * --------------------- * * Provide the SQL statement that calculates the total value of the cost of * examinations at each clinic and returns the clinic name and total cost in * descending order. You may assume that a doctor can only work at one clinic. * * */ -- START ANSWER 6 -- SELECT c.name AS clinic_name, SUM(e.exam_cost) AS total_cost FROM clinics c JOIN doctors d ON c.clinic_id = d.clinic_id JOIN examinations e ON d.doctor_id = e.doctor_id GROUP BY c.name ORDER BY total_cost DESC; -- END ANSWER 6 --

------------------------------------------------------------------------------- /* * QUESTION 7 (2 points) * --------------------- * * Provide the SQL statement that returns the medical specialty and average * exam cost for each specialty. * * */ -- START ANSWER 7 -- SELECT d.specialty, AVG(e.exam_cost) AS average_exam_cost FROM doctors d JOIN examinations e ON d.doctor_id = e.doctor_id GROUP BY d.specialty; -- END ANSWER 7 -- -------------------------------------------------------------------------------

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

HOMEWORK ASSIGNMENT 3

Related Documents