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

/*