Database Design and Implementation Task 2022
Added on 2022-09-06
31 Pages3280 Words19 Views
|
|
|
[Pick the date]
[Type the document subtitle] |
[TYPE
THE
COMPA
NY
NAME]
Database design and
Implementation (EER
Modeling and SQL)
[Type the document subtitle] |
[TYPE
THE
COMPA
NY
NAME]
Database design and
Implementation (EER
Modeling and SQL)
![Database Design and Implementation Task 2022_1](/_next/image/?url=https%3A%2F%2Fdesklib.com%2Fmedia%2Fimages%2Fjn%2Fe5422e157c1449d4b0a253b8e6d771b2.jpg&w=3840&q=10)
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...................................................................................................................... 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
![Database Design and Implementation Task 2022_2](/_next/image/?url=https%3A%2F%2Fdesklib.com%2Fmedia%2Fimages%2Fjt%2Fb339c7ef803746a69281eb5d5a0aac5c.jpg&w=3840&q=10)
Task 2.1
ER Diagram
ER Diagram
![Database Design and Implementation Task 2022_3](/_next/image/?url=https%3A%2F%2Fdesklib.com%2Fmedia%2Fimages%2Fvg%2Fc10f569d8fc2411e90c7b82c5f72dcaf.jpg&w=3840&q=10)
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
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
![Database Design and Implementation Task 2022_4](/_next/image/?url=https%3A%2F%2Fdesklib.com%2Fmedia%2Fimages%2Fhw%2F6fdccc5a7eb24819b4158113bc4948c9.jpg&w=3840&q=10)
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
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
![Database Design and Implementation Task 2022_5](/_next/image/?url=https%3A%2F%2Fdesklib.com%2Fmedia%2Fimages%2Fgd%2Fa2fdba7e1c274d06b7e5090cecb67c2f.jpg&w=3840&q=10)
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
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
![Database Design and Implementation Task 2022_6](/_next/image/?url=https%3A%2F%2Fdesklib.com%2Fmedia%2Fimages%2Fma%2Fad00a5edf4b1478d8ee05385812a96d9.jpg&w=3840&q=10)
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
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
![Database Design and Implementation Task 2022_7](/_next/image/?url=https%3A%2F%2Fdesklib.com%2Fmedia%2Fimages%2Fqu%2F32b298f2ef15429f85fa30580349d1ce.jpg&w=3840&q=10)
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
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
![Database Design and Implementation Task 2022_8](/_next/image/?url=https%3A%2F%2Fdesklib.com%2Fmedia%2Fimages%2Fue%2F68f7edc45fb348b6925a5dbb7e17b84a.jpg&w=3840&q=10)
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents