Database Design and Implementation Task 2022

[Pick the date]
[Type the document subtitle] |
[TYPE
THE
COMPA
NY
NAME]
Database design and
Implementation (EER
Modeling and SQL)

Table of Contents
Task 2.1...................................................................................................................... 4
Task 2.2.................................................................................................................... 10
Task 2.3 indexes....................................................................................................... 15
Task 2.4: Data Population......................................................................................... 16
Task 2.5: SQL Query writing..................................................................................... 25
Selection of particular table columns....................................................................25
Inner join............................................................................................................... 25
Outer Join.............................................................................................................. 26
Count Mathematical Expression............................................................................27
Sorting................................................................................................................... 27
Condition By “<” and “>” and LIKE.......................................................................29
IN Operator............................................................................................................ 30
Sub Query............................................................................................................. 31
References............................................................................................................... 31

Task 2.1
ER Diagram

NurseStaffP2515985
Fieldname Data type Size Null Constraint
Employee_id number N Primary
DeptID number N FK
nursename varchar2 60 N
nurseposition varchar2 40 N
nurseregistered Char 2 N
nursessn Char 20 N
HphysicianP2515985
Fieldname Data type Size Null Constraint
Employee_id number N Primary
DeptID number N FK
Doctorname varchar2 60 N
Doctorposition varchar2 40 N
Doctorregistered Char 2 N
Doctorssn Char 20 N

HdepartmentP2515985
Fieldname Data type Size Null Constraint
Department_id number N Primary
Departmentname varchar2 60 N
Departmenthead number N
PAppointmentP2515985
Fieldname Data type Size Null Constraint
Appointment_id Number 20 N Primary
Apppatient varchar2 40 N FK
Appprepnurse varchar2 40 N
Appphysician Number N
Appstart_dt_time Date N
Append_dt_time Date N
AppexaminationRoomP2515985 char 2 N CHECK
RoomP2515985

Fieldname Data type Size Null Constraint
RoomP2515985ID Number 20 N Primary
RoomP2515985type varchar2 40 N
floor Number N
block Number N
CheckAvaialability Char 2 N
AffiliatedwithP2515985
Fieldname Data type Size Null Constraint
Physicians Number 20 N FK
Departments varchar2 40 N
Primaryaffiliations char 2 N
HospprocedureP2515985
Fieldname Data type Size Null Constraint
PCode Number 20 N Primary
Pname varchar2 40 N
pCost decimal N

HosptrainedinP2515985
Fieldname Data type Size Null Constraint
tphysician Number 20 N FK
htreatment Number 5 N
tcertificationdate date N
tcertificationexpires date N
HpatientP2515985
Fieldname Data type Size Null Constraint
pssn Number 20 N Primary
Pname varchar2 65 N
Paddress varchar2 165 N
Pphone varchar2 65 N
Pinsuranceid char 15 N
Ppcp number 10 N

HprescribesP2515985
Fieldname Data type Size Null Constraint
HphysicianP2515985 Number 10 N FK
HpatientP2515985 Number 15 N FK
Hmedication Number 5 N
Hdate date N
Happointment char 15 N
Hdose number 10 N
Task 2.2
Create table NurseStaffP2515985