Question

Develop SQL code that would create the database files corresponding to your relational schema for the Mountain View Community Hospital case study created in Phase 2 of the class project.

Write the SQL statements for creating the tables, specifying data types and field lengths, establishing primary keys and foreign keys, and implementing other constraints you identified.

Write the SQL statements that create the indexes. This is optional.

You should execute your SQL code on a DBMS (Oracle 11g or Oracle 12c) to test its correctness and gain the hands on experience, which is the main objective of this project.

Note: The primary key (PK) of each relation is underlined. Foreign keys are shown in italics, rather than with a double underline. PKs that are both a primary key and a foreign key are noted in Italics with an underline. Relation names are shown in bold.

PERSON (PersonID, PersonName, PersonStrAddress, PersonCity, PersonState, PersonZip, PersonHomePhone, PersonWorkPhone, PersonDOB, PersonEmail, IsPhysician, IsEmployee, IsVolunteer, IsPatient)

VOLUNTEER (VolID, HadFelony, FelonyExplanation, VECLastName, VECFirstName, VECRelationship, VECAddress, VECPhone, VEmployer, VEmployerAddr, VEmployPosition, VEmployStartDate, VEmployEndDate, HadMVCHService, HadVolExp, WhyVolunteer)

VOL MVCH SERVICE (VMSID, MVCHServiceInfo, VolID)
VOL REF INFO (VRIID, VRILastName, VRIFirstName, VRIRelationship, VRIPhone, VRIAddress, VRICity, VRIState, VRIZip, VolID) VOL EXPERIENCE (VEID, VolunteerExpInfo, VolID)
VOL LANGUAGE (VLID, Language, VolID)
VOL SKILL (VSID, Skill, VolID)
VOL INTEREST (VIID, Interest, VolID)
VOL AVAILABILITY (VAID, DayOfWeek, PortionOfDay, VolID)
VOL SERV HISTORY (VSHID, ServiceBeginDate, ServiceEndDate, ServiceHrsWorked, UnitName, VolID, PhysicianID, EmpID) PHYSICIAN (PhysicianID, DEANo, PagerNo, Specialty)
PHYSICIAN DX (PDID, DiagnosisDate, DiagnosisTime, PhysicianID, PatientID, DiagnosisCode)
DIAGNOSIS (DiagnosisCode, DiagnosisName)

PATIENT (PatientID, ContactDate, ECLastName, ECFirstName, ECRelationship, ECAddress, ECPhone, CompanyName, PolicyNo, GroupNo, CompanyPhone, SubLastName, SubFirstName, SubRelationship, SubAddress, SubPhone, IsOutpatient, IsInpatient, AdmitPhys, ReferPhys)

RESIDENT (RPatientID, DateAdmitted, DateDischarged, BedNo, RoomNo) OUTPATIENT (OPatientID)
VISIT (VisitNo, VisitDate, VisitTime, VisitReason, OPatientID)
BED (RoomNo, BedNo)

ROOM (RoomNo, CCUnitName)
CC ASSIGNMENT (CCAID, AssignStart, AssignEnd, HrsWorked, CCUnitName, NurseID) CARE CENTER (CCUnitName, DayInCharge, NightInCharge)

RN (RNID)

LPN (LPNID, Supervisor)

NURSE (NurseID, CertDegree, StateLicenseNo, NurseSpecialty, NurseType)

FIELD CERTIFICATION (FCID, FCDescription, NurseID)

EMPLOYEE (EmpID, DateHired, EmpType)

STAFF (StaffID, JobClass, UnitName)

TECHNICIAN (TechnicianID, UnitName)

TECHNICIAN SKILL (TSID, TechnicianID, TSSkill)

WORK UNIT (UnitName, FacilityID, Floor, UnitType)

FACILITY (FacilityID, FacilityName)

DIAGNOSTIC UNIT (DXUnitName)

TREATMENT (TrtCode, TrtName, DXUnitName)

TREATMENT ORDER (TOID, TrtDate, TrtTime, Results, TrtCode, OrderID,)

ORDER (OrderID, OrderDate, OrderTime, PatientID, PhysicianID, ItemNo)

ITEM (ItemNo, ItemDesc, ItemUnitCost)

INVENTORY (InvID, ItemNo, VendorID)

VENDOR (VendorID, VendorName)

ITEM BILLING (IBID, StartDate, EndDate, Cost, ItemNo, RoomNo, PatientID)

ITEM CONSUMPTION (ICID, ConsumeDate, ConsumeTime, ConsumeQty, ItemNo, PatientID)

ASSESSMENT (AssessmentID, AssessmentDate, AssessmentTime, Comments, PatientWeight, PatientBP, PatientPulse, PatientTemperature, PatientID, NurseID)

SCHEDULE (ScheduleID, SchedBegin, SchedEnd, FacilityID, PhysicianID)