Project Case: Team 8B Mountain View Community Hospital - PowerPoint PPT Presentation

1 / 42

About This Presentation

Title:

Project Case: Team 8B Mountain View Community Hospital

Description:

Use the SQL data model constructed for MVCH in chapter 4 to complete the project ... '1014','Justin Timberlake','1235','0005'); INSERT INTO PATIENT_T650. VALUES ... – PowerPoint PPT presentation

Number of Views:2872

Avg rating:3.0/5.0

Slides: 43

Provided by: Gra7155

Category:

more less

Transcript and Presenter's Notes

Title: Project Case: Team 8B Mountain View Community Hospital

1
Project Case Team 8BMountain View
CommunityHospital

Team Members
Erika Pohl
Theron Bunting
James Churchwell
Jovita Simon
Heath Smith

2
Introduction

Use the SQL data model constructed for MVCH in
chapter 4 to complete the project questions and
project exercises.

3
Project Question 1

What version of SQL will you use to do the
project exercises?

4
Project Question 1Answer

The project exercises will be done in SQL version
9i.

5
Project Question 2

Which CASE tools are available to be used to do
the project exercises?

6
Project Question 2Answer

Visible Analyst
ERD
Easy Case
Designer 2000
Notepad
Smart Draw
Microsoft Word

7
Project Exercise 1

Create in SQL the Mountain View Community
Hospital database for the conceptual data model
you have constructed in the previous chapters.
Use the information provided in the PROJECT CASE
sections at the end of Chapters 3, 4, 5, and 6 to
help you choose column data types, lengths,
index, etc.

8
MVCH

The CREATE TABLE commands here are presented as
simple table means, which provide clarity. There
are parameter issues not considered here, such as
TABLESPACE, STORAGE, and additional data types
for attributes. Data types defining attributes
have been set to a minimum.
Occasionally, table definitions can depend on
the values that are to be assigned. In this case,
a unique number for each room is assumed. The
rooms may be numbered within each ward, this
composite keys would be required.

9
MVCH CREATE TABLE EMPLOYEE

CREATE TABLE EMPLOYEE_T650
(EMP_NO NUMBER NOT NULL,
EMP_NAME VARCHAR2(25),
CONSTRAINT EMP_PK5 PRIMARY KEY (EMP_NO))

10
MVCH CREATE TABLE PHYSICIAN

CREATE TABLE PHYSICIAN_T650
(PHYSICIAN_ID NUMBER NOT NULL,
PHYSICIAN_NAME VARCHAR2(25),
CONSTRAINT PHYSICIAN_PK5 PRIMARY KEY
(PHYSICIAN_ID))

11
MVCH CREATE TABLE PERFORMS

CREATE TABLE PERFORMS_T650
(PATIENT_NO NUMBER NOT NULL,
TREATMENT_DATE DATE NOT NULL,
TREATMENT_TIME NUMBER NOT NULL,
TREATMENT_NO NUMBER NOT NULL,
PHYSICIAN_ID NUMBER,
CONSTRAINT VISIT_PK5 PRIMARY KEY (PATIENT_NO,
TREATMENT_DATE, TREATMENT_TIME, TREATMENT_NO),
CONSTRAINT PERFORMS_FK5 FOREIGN KEY
(PHYSICIAN_ID) REFERENCES
PHYSICIAN_T5 (PHYSICIAN_ID))

12
MVCH CREATE TABLE WARD

CREATE TABLE WARD_T650
(WARD_NO VARCHAR2(25) NOT NULL,
WARD_NAME VARCHAR2(25),
CONSTRAINT WARD_PK5 PRIMARY KEY (WARD_NO))

13
MVCH CREATE TABLE ROOM

CREATE TABLE ROOM_T650
(ROOM_NO NUMBER NOT NULL,
WARD_NO VARCHAR2(25) NOT NULL,
CONSTRAINT ROOM_PK5 PRIMARY KEY
(ROOM_NO),
CONSTRAINT ROOM_FK5 FOREIGN KEY
(WARD_NO) REFERENCES WARD_T5
(WARD_NO))

14
MVCH CREATE TABLE BED

CREATE TABLE BED_T650
(BED_NO NUMBER NOT NULL,
ROOM_NO NUMBER NOT NULL,
WARD_NO NUMBER NOT NULL,
CONSTRAINT BED_PK5 PRIMARY KEY
(BED_NO, ROOM_NO),
CONSTRAINT BED_FK1_5 FOREIGN KEY (ROOM_NO)
REFERENCES ROOM_T5 (ROOM_NO),
CONSTRAINT BED_FK2_5 FOREIGN KEY (BED_NO)
REFERENCES BED_T5 (BED_NO))

15
MVCH CREATE TABLE PATEINT

CREATE TABLE PATIENT_T650
(PATIENT_NO NUMBER NOT NULL,
PATIENT_NAME VARCHAR2(25),
PHYSICIAN_ID NUMBER,
BED_NO NUMBER,
CONSTRAINT PATIENT_PK5 PRIMARY KEY (PATIENT_NO),
CONSTRAINT PATIENT_FK1_5 FOREIGN KEY
(PHYSICIAN_ID) REFERENCES PHYSICIAN_T5(PHYSICIAN_I
D),
CONSTRAINT PATIENT_FK2_5 FOREIGN KEY (BED_NO)
REFERENCES BED_T5(BED_NO))

16
MVCH CREATE TABLE ASSIGNED

CREATE TABLE ASSIGNED_T650
(WARD_NO VARCHAR2(25) NOT NULL,
EMP_NO NUMBER NOT NULL,
HOURS NUMBER,
CONSTRAINT ASSIGNED_PK5 PRIMARY KEY
(WARD_NO, EMP_NO),
CONSTRAINT ASSIGNED_FK1_5 FOREIGN KEY
(WARD_NO) REFERENCES WARD_T5 (WARD_NO),
CONSTRAINT ASSIGNED_FK2_5 FOREIGN KEY
(EMP_NO) REFERENCES EMPLOYEE_T5
(EMP_NO))

17
MVCH CREATE TABLE ITEM

CREATE TABLE ITEM_T650
(ITEM_NO NUMBER NOT NULL,
DESCRIPTION VARCHAR2(25),
UNIT_COST NUMBER(8,2),
CONSTRAINT ITEM_PK5 PRIMARY KEY
(ITEM_NO))

18
MVCH CREATE TABLE CONSUMES

CREATE TABLE CONSUMES_T650
(ITEM_NO NUMBER NOT NULL,
PATIENT_NO NUMBER NOT NULL,
DATE_T DATE NOT NULL,
QUANTITY NUMBER,
CONSTRAINT CONSUMES_PK5 PRIMARY
KEY (ITEM_NO, PATIENT_NO, DATE_T),
CONSTRAINT CONSUMES_FK1_5 FOREIGN KEY
(ITEM_NO) REFERENCES ITEM_T5(ITEM_NO),
CONSTRAINT CONSUMES_FK2_5 FOREIGN KEY
(PATIENT_NO) REFERENCES
PATIENT_T5(PATIENT_NO))

19
MVCH CREATE TABLE ROOM_ITEM

CREATE TABLE ROOM_ITEM_T650
(ITEM_NO NUMBER NOT NULL,
ROOM_NO NUMBER NOT NULL,
DATE_T DATE,
QUANTITY NUMBER,
CONSTRAINT ROOM_ITEM_PK5 PRIMARY
KEY (ITEM_NO, ROOM_NO, DATE_T),
CONSTRAINT ROOM_ITEM_FK1_5 FOREIGN
KEY (ITEM_NO) REFERENCES ITEM_T5
(ITEM_NO),
CONSTRAINT ROOM_ITEM_FK2_5 FOREIGN
KEY (ROOM_NO) REFERENCES ROOM_T5
(ROOM_NO))

20
MVCH CREATE TABLE TREATMENT

CREATE TABLE TREATMENT_T650
(TREATMENT_NO NUMBER NOT NULL,
TREATMENT_NAME VARCHAR2(25)
NOT NULL,
CONSTRAINT TREATMENT_PK5
PRIMARY KEY (TREATMENT_NO))

21
Project Exercise 2

If you did not remember to establish primary and
foreign keys in the preceding questions, create
SQL assertions necessary to accomplish that.

22
Project Answer 2

ALTER TABLE ROOM_T650
ADD CONSTRAINT ROOM_T500
PRIMARY KEY (ROOM_NO)
ALTER TABLE ROOM_T650
ADD CONSTRAINT ROOM_T500
FOREIGN KEY (WARD_NO)
REFERENCES
WARD_T500(WARD_NO)

23
Project Exercise 3

Select a portion of your database and populate it
with sample data. For example, you may want to
work with the staff, care-center, or patient part
of your database. Or you may be interested in
working with the vendor, medical/surgical items,
tests, and patient, part of your database. Be
prepared to defend the sample data that you
insert into your database. How do the actual
values you are using help you to test the
functionality of your database?

24
Project Exercise 3

Each Table may be populated by using the INSERT
command. If vales are entered for each field of
the table, the following command will work
INSERT INTO database
VALUES (attribute value, )

25
MVCH INSERT EMPLOYEE