postgreSQL

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) * * * 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 * 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.) * */

-------------------------------------------------------------------------------

/* * QUESTION 2 (4 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. * --

/* * 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. * */--------------------------------------------

/* * QUESTION 3 (10 points) * ---------------------- * * Draw the ER diagram for the schema detailed in Question 1 . Pay close attention in properly * defining relationships and cardinalities. You may draw the ER diagram in * any software you prefer,

-------------------------------------------------------------------------------

/* * QUESTION 4 (5 points) * --------------------- * * Draw the ER diagram for the schema detailed in Question 1 using Engineering * notation. Pay close attention in properly defining relationships and * cardinalities.