Develop SQL code that would create the database files corresponding to your rela

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)

Explanation / Answer

  Functional dependies of project assignment2 part 1

CREATE TABLE Person(PersonId number(10),personName varchar(255),personstreetaddress varchar(255),personcity varchar(255),personstate varchar(255),personzip number(10),personhomephone number(12),personworkphone number(12),persondob date,personemail varchar(255),isphysician boolean,isemployee boolean,ispatient boolean,isvolunteer boolean, Primary key (PersonId));

create table VOLUNTEER (VolID number(10), HadFelony varchar(255), FelonyExplanation varchar(255), VECLastName varchar(255), VECFirstName varchar(255), VECRelationship varhchar(255), VECAddress varchar(255), VECPhone number(12), VEmployer varchar(255), VEmployerAddr varchar(255), VEmployPosition varchar(255), VEmployStartDate date, VEmployEndDate date, HadMVCHService varchar(255), HadVolExp varchar(255), WhyVolunteer varchar(255),primary key (volid));

create table VOLMVCHSERVICE (VMSID number, MVCHServiceInfo varchar(255),VolID number(10),primary key(vmsid),foreign key(volId) references volunteer(volId))

create table VOLREFINFO (VRIID number(10), VRILastName varchar(255), VRIFirstName varchar(255), VRIRelationship varchar(255), VRIPhone number(12), VRIAddress varchar(255), VRICity varchar(255), VRIState varchar(255), VRIZip number(10), VolID number(10),primary key(vriid),foreign key(volid) references volunteer(volid))

create table VOLEXPERIENCE (VEID number(10), VolunteerExpInfo varchar(255), VolID number(10),primary key(veid),foreign       key(volid) references volunteer(volid))

create table VOLLANGUAGE (VLID number(10), Language varchar(255), VolID number(10),primary key(vlid),foreign key(volid) references volunteer(volid))

create table VOLSKILL (VSID number(10), Skill varchar(255), VolID number(10),primary key(vsid),foreign key(volid) references volunteer(volid))

create table VOLINTEREST (VIID number(10), Interest varchar(255), VolID number(10),primary key(viid),foreign key(volid) references volunteer(volid))

create table VOLAVAILABILITY (VAID number(10), DayOfWeek varchar(255), PortionOfDay varchar(255), VolID number(10),primary key(vaid),foreign key(volid) references volunteer(volid))

create table EMPLOYEE (EmpID number(10), DateHired date, EmpType varchar(255),primary key(empid))

            Functional dependies of project assignment2 part 2

CREATE TABLE Person(PersonId number(10),personName varchar(255),personstreetaddress varchar(255),personcity varchar(255),personstate varchar(255),personzip number(10),personhomephone number(12),personworkphone number(12),persondob date,personemail varchar(255),isphysician boolean,isemployee boolean,ispatient boolean,isvolunteer boolean, Primary key (PersonId));

create table PHYSICIAN (PhysicianID number(10), DEANo number(10), PagerNo number(10), Specialty varchar(255),primary key(physicianid))

create table DIAGNOSIS (DiagnosisCode number(10), DiagnosisName varchar(255))

create table PHYSICIANDX (PDID number(10), DiagnosisDate date, DiagnosisTime varchar(255), PhysicianID number(10), PatientID number(10), DiagnosisCode number(10),primary key(pdid),foreign key(physicianid) references physician(physicianid),foreign key(patientid) references patient(patientid),foreign key(diagnosiscode) references diagnosis(diagnosiscode))

create table PATIENT (PatientID number(10), ContactDate date, ECLastName varchar(255), ECFirstName varchar(255), ECRelationship varchar(255), ECAddress varchar(255), ECPhone number(10), CompanyName varchar(255), PolicyNo number(10), GroupNo number(10), CompanyPhone number(10), SubLastName varchar(255), SubFirstName varchar(255), SubRelationship varchar(255), SubAddress varchar(255), SubPhone number(10), IsOutpatient boolean, IsInpatient boolean, AdmitPhys number(10), ReferPhys number(10),primary     key(patientid),foreign key(admitphys) references physician(physicianid),foreign key(referphys) references physician(physicianid) )

create table OUTPATIENT (OPatientID number(10) ,foreign key(opatientid) references patient(patientid))

create table VISIT (VisitNo number(10), VisitDate date , VisitTime varchar(255), VisitReason varchar(255), OPatientID number(10),foreign key(opatientid) references outpatient(opatientid))

create table BED ( RoomNo number(10), BedNo number(10),primary key(roomno,bedno))

create table EMPLOYEE (EmpID number(10), DateHired date, EmpType varchar(255),foreign key(empid) references person(personid))

create table NURSE (NurseID number(10), CertDegree varchar(255), StateLicenseNo number(10), NurseSpecialty varchar(255), NurseType varchar(255),foreign key(nurseid) references employee(employeeid))

create table RN (RNID number(10),primary key(rnid) foreign key(rnid) references nurse(nurseid))

create table LPN (LPNID number(10), Supervisor varchar(255),primary key(lpnid) , foreign key(lpnid) references nurse(nurseid))

create table CARECENTER (CCUnitName varchar(255), DayInCharge number(10), NightInCharge number(10), foreign key(dayincharge) references rn(rnid),foreign key(nightincharge) references rn(rnid))

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.