Project Case: Team 8B Mountain View Community Hospital

1
Project Case Team 8BMountain View Community
Hospital

• Team Members
• Jonathan Cooper, Tammy Horne, Leigh Hughes, Lisa
  Kaulfurst, April Prewitt, Carey Stallings, Jon
  Templeton

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

• Question Compatible case tools that can be used
  with this project are the following?
• Answer The solutions are based on SQL Plus,
  Oracle 8i.

4
Project Question 2

• Which case tools are available to be used to do
  the project exercise.

5
Project Answer 2

• CASE tools that are available to be used to do
  the project exercises are
• Visible Analyst
• The Cool Project Suite
• ERD
• Easy Case
• Designer 2000
• Notepad
• Smart Draw
• Microsoft Word

6
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.

7
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, thus
  composite keys would be required.

8
CREATE TABLE EMPLOYEE FOR MVCH

• CREATE TABLE EMPLOYEE_T500
• (EMP_NO NUMBER NOT NULL UNIQUE,
• EMP_NAME VARCHAR2(25),
• CONSTRAINT EMP_PK50 PRIMARY KEY (EMP_NO))

9
CREATE TABLE PHYSICIAN FOR MVCH

• CREATE TABLE PHYSICIAN_T500
• (PHYSICIAN_ID NUMBER NOT NULL UNIQUE,
• PHYSICIAN_NAME VARCHAR2(25),
• CONSTRAINT PHYSICIAN_PK50 PRIMARY KEY
  (PHYSICIAN_ID))

10
CREATE TABLE PERFORMS FOR MVCH

• CREATE TABLE PERFORMS_T500
• (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_PK500 PRIMARY KEY (PATIENT_NO,
  TREATMENT_DATE, TREATMENT_TIME, TREATMENT_NO),
• CONSTRAINT PERFORMS_FK500 FOREIGN KEY
  (PHYSICIAN_ID) REFERENCES PHYSICIAN_T500(PHYSICIAN
  _ID))

11
CREATE TABLE WARD FOR MVCH

• CREATE TABLE WARD_T500
• (WARD_NO VARCHAR2(25) NOT NULL UNIQUE,
• WARD_NAME VARCHAR2(25),
• CONSTRAINT WARD_PK500 PRIMARY KEY (WARD_NO))

12
CREATE TABLE ROOM FOR MVCH

• CREATE TABLE ROOM_T500
• (ROOM_NO NUMBER NOT NULL UNIQUE,
• WARD_NO VARCHAR2(25) NOT NULL UNIQUE,
• CONSTRAINT ROOM_PK500 PRIMARY KEY (ROOM_NO),
• CONSTRAINT ROOM_FK500 FOREIGN KEY (WARD_NO)
  REFERENCES WARD_T500 (WARD_NO))

13
CREATE TABLE BED FOR MVCH

• CREATE TABLE BED_T500
• (BED_NO NUMBER NOT NULL UNIQUE,
• ROOM_NO NUMBER NOT NULL UNIQUE,
• WARD_NO NUMBER NOT NULL UNIQUE,
• CONSTRAINT BED_PK500 PRIMARY KEY (BED_NO,
  ROOM_NO),
• CONSTRAINT BED_FK1_500 FOREIGN KEY (ROOM_NO)
  REFERENCES ROOM_T500 (ROOM_NO),
• CONSTRAINT BED_FK2_500 FOREIGN KEY (BED_NO)
  REFERENCES BED_T500 (BED_NO))

14
CREATE TABLE PATIENT FOR MVCH

• CREATE TABLE PATIENT_T500
• (PATIENT_NO NUMBER NOT NULL,
• PATIENT_NAME VARCHAR2(25),
• PHYSICIAN_ID NUMBER,
• BED_NO NUMBER,
• CONSTRAINT PATIENT_PK500 PRIMARY KEY
  (PATIENT_NO),
• CONSTRAINT PATIENT_FK1_500 FOREIGN KEY
  (PHYSICIAN_ID) REFERENCES PHYSICIAN_T500(PHYSICIAN
  _ID),
• CONSTRAINT PATIENT_FK2_500 FOREIGN KEY (BED_NO)
  REFERENCES BED_T500(BED_NO))

15
CREATE TABLE ASSIGNED FOR MVCH

• CREATE TABLE ASSIGNED_T500
• (WARD_NO VARCHAR2(25) NOT NULL,
• EMP_NO NUMBER NOT NULL,
• HOURS NUMBER,
• CONSTRAINT ASSIGNED_PK500 PRIMARY KEY (WARD_NO,
  EMP_NO),
• CONSTRAINT ASSIGNED_FK1_500 FOREIGN KEY (WARD_NO)
  REFERENCES WARD_T500 (WARD_NO),
• CONSTRAINT ASSIGNED_FK2_500 FOREIGN KEY (EMP_NO)
  REFERENCES EMPLOYEE_T500 (EMP_NO))

16
CREATE TABLE ITEM FOR MVCH

• CREATE TABLE ITEM_T500
• (ITEM_NO NUMBER NOT NULL,
• DESCRIPTION VARCHAR2(25),
• UNIT_COST NUMBER(8,2),
• CONSTRAINT ITEM_PK500 PRIMARY KEY (ITEM_NO))

17
CREATE TABLE CONSUMES FOR MVCH

• CREATE TABLE ROOM_ITEM_T500
• (ITEM_NO NUMBER NOT NULL,
• ROOM_NO NUMBER NOT NULL,
• DATE_T DATE,
• QUANTITY NUMBER,
• CONSTRAINT ROOM_ITEM_PK500 PRIMARY KEY (ITEM_NO,
  ROOM_NO, DATE_T),
• CONSTRAINT ROOM_ITEM_FK1_500 FOREIGN KEY
  (ITEM_NO) REFERENCES ITEM_T500 (ITEM_NO),
• CONSTRAINT ROOM_ITEM_FK2_500 FOREIGN KEY
  (ROOM_NO) REFERENCES ROOM_T500 (ROOM_NO))

18
CREATE TABLE ROOM_ITEM FOR MVCH

• CREATE TABLE ROOM_ITEM_T500
• (ITEM_NO NUMBER NOT NULL,
• ROOM_NO NUMBER NOT NULL,
• DATE_T DATE,
• QUANTITY NUMBER,
• CONSTRAINT ROOM_ITEM_PK500 PRIMARY KEY (ITEM_NO,
  ROOM_NO, DATE_T),
• CONSTRAINT ROOM_ITEM_FK1_500 FOREIGN KEY
  (ITEM_NO) REFERENCES ITEM_T500 (ITEM_NO),
• CONSTRAINT ROOM_ITEM_FK2_500 FOREIGN KEY
  (ROOM_NO) REFERENCES ROOM_T500 (ROOM_NO))

19
CREATE TABLE TREATMENT FOR MVCH

• CREATE TABLE TREATMENT_T500
• (TREATMENT_NO NUMBER NOT NULL,
• TREATMENT_NAME NUMBER NOT NULL,
• CONSTRAINT TREATMENT_PK500 PRIMARY KEY
  (TREATMENT_NO))

20
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.
21
Project Answer 2
ALTER TABLE ROOM_T500 ADD CONSTRAINT ROOM_T500
PRIMARY KEY (ROOM_NO) ALTER TABLE ROOM_T500 ADD
CONSTRAINT ROOM_T500 FOREIGN KEY(WARD_NO) REFERENC
ES WARD_T500(WARD_NO)
22
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, patient part of
your database. Or you may be interested in
working with the vendor, medical/surgical items,
tests, and patients part of your database. Be
prepared to defend the sample test data that you
insert into your database. How do the actual
values you are using help you to test the
functionality of your database?
23
Project Answer 3
Each Table may be populated by using the INSERT
command. If values are entered for each field of
the table, the following command will work
INSERT INTO tablename VALUES (attribute value, )
24
MVCH INSERT EMPLOYEE
INSERT INTO EMPLOYEE_T500 VALUES ('4492',Matt
Edwards') INSERT INTO EMPLOYEE_T500 VALUES ('5549
',Rachele Diemer') INSERT INTO
EMPLOYEE_T500 VALUES ('6543',Sarah
Slatton') INSERT INTO EMPLOYEE_T500 VALUES ('8765
',Sarah Edman') INSERT INTO EMPLOYEE_T500 VALUES
('1987',Ellion Dison')
25
MVCH INSERT PHYSICIAN
INSERT INTO PHYSICIAN_T500 VALUES ('1234',Brad
Pitt') INSERT INTO PHYSICIAN_T500 VALUES ('1235',
Ben Affleck') INSERT INTO PHYSICIAN_T500 VALUES
('1236',Kenny Chesney') INSERT INTO
PHYSICIAN_T500 VALUES ('1237',Tim
McGraw') INSERT INTO PHYSICIAN_T500 VALUES ('1238
',Faith Hill') INSERT INTO PHYSICIAN_T500 VALUES
('1239',Audrey McGraw')
26
MVCH INSERT PERFORMS
INSERT INTO PERFORMS_T500 VALUES ('1010','2-APR-02
','120','3674','1234') INSERT INTO
PERFORMS_T500 VALUES ('1011','4-APR-02','945','330
4','1235') INSERT INTO PERFORMS_T500 VALUES ('101
2','6-APR-02','836','3643','1236') INSERT INTO
PERFORMS_T500 VALUES ('1013','30-MAR-02','745','32
49','1237') INSERT INTO PERFORMS_T500 VALUES ('10
14','28-MAR-02','1015','3456','1238') INSERT
INTO PERFORMS_T500 VALUES ('1015','26-MAR-02','645
','3406','1239')
27
MVCH - INSERT WARD
INSERT INTO WARD_T500 VALUES ('101','Intensive
Care') INSERT INTO WARD_T500 VALUES ('102','Pedia
tric Ward') INSERT INTO WARD_T500 VALUES ('103','
Maternity Ward') INSERT INTO WARD_T500 VALUES ('1
04','Cosmetic Ward') INSERT INTO
WARD_T500 VALUES ('105','Radiology Ward') INSERT
INTO WARD_T500 VALUES ('106','Cardiac Ward')
28
MVCH INSERT ROOM
INSERT INTO ROOM_T500 VALUES ('304','101') INSERT
INTO ROOM_T500 VALUES ('205','102') INSERT INTO
ROOM_T500 VALUES ('402','103') INSERT INTO
ROOM_T500 VALUES ('102','104') INSERT INTO
ROOM_T500 VALUES ('103','105') INSERT INTO
ROOM_T500 VALUES ('501','106')
29
MVCH INSERT BED
INSERT INTO BED_T500 VALUES ('0001','304','101')
INSERT INTO BED_T500 VALUES ('0002','205','102')
INSERT INTO BED_T500 VALUES ('0003','402','103')
INSERT INTO BED_T500 VALUES ('0004','102','104')
INSERT INTO BED_T500 VALUES ('0005','103','105')
INSERT INTO BED_T500 VALUES ('0006','501','106')
30
MVCH INSERT PATIENT
INSERT INTO PATIENT_T500 VALUES ('1010',Kylynne
Edwards','1234','0001') INSERT INTO
PATIENT_T500 VALUES ('1011',Garret
Jackson','1234','0002') INSERT INTO
PATIENT_T500 VALUES ('1012',M.C.
Hammer','1239','0003') INSERT INTO
PATIENT_T500 VALUES ('1013',Shonna
Lanier','1235','0004') INSERT INTO
PATIENT_T500 VALUES ('1014',Nelly
Jones','1235','0005') INSERT INTO
PATIENT_T500 VALUES ('1015','Ozzy
Osbourn','1236','0006')
31
MVCH INSERT ASSIGNED
INSERT INTO ASSIGNED_T500 VALUES ('101','4492','8'
) INSERT INTO ASSIGNED_T500 VALUES ('102','5549',
'8') INSERT INTO ASSIGNED_T500 VALUES ('103','654
3','8') INSERT INTO ASSIGNED_T500 VALUES ('104','
8765','8') INSERT INTO ASSIGNED_T500 VALUES ('105
','1987','8') INSERT INTO ASSIGNED_T500 VALUES ('
106','1812','8')
32
MVCH INSERT ITEM
INSERT INTO ITEM_T500 VALUES ('01','Asprin',9.50)
INSERT INTO ITEM_T500 VALUES ('02','Tylenol',2.50
) INSERT INTO ITEM_T500 VALUES ('03','Bayer',1.50
) INSERT INTO ITEM_T500 VALUES ('04',Novocain,87.
50) INSERT INTO ITEM_T500 VALUES ('05','Icy
Hot',4.95) INSERT INTO ITEM_T500 VALUES ('06','Be
n Gay',3.95)
33
MVCH INSERT CONSUMES
INSERT INTO CONSUMES_T500 VALUES ('01','1010','1-J
AN-01','2') INSERT INTO CONSUMES_T500 VALUES ('02
','1015','2-JAN-01','3') INSERT INTO
CONSUMES_T500 VALUES ('03','1013','3-JAN-01','4')
INSERT INTO CONSUMES_T500 VALUES ('04','1015','4-
JAN-01','5') INSERT INTO CONSUMES_T500 VALUES ('0
5','1010','5-JAN-01','6') INSERT INTO
CONSUMES_T500 VALUES ('06','1012','6-JAN-01','7')
34
MVCH INSERT ROOM_ITEM
INSERT INTO ROOM_ITEM_T500 VALUES ('01','304','1-J
AN-02','2') INSERT INTO ROOM_ITEM_T500 VALUES ('0
2','205','2-JAN-02','3') INSERT INTO
ROOM_ITEM_T500 VALUES ('03','402','3-JAN-02','4')
INSERT INTO ROOM_ITEM_T500 VALUES ('04','102','4-
JAN-02','5') INSERT INTO ROOM_ITEM_T500 VALUES ('
05','103','5-JAN-02','6') INSERT INTO
ROOM_ITEM_T500 VALUES ('06','501','6-JAN-02','7')
35
MVCH INSERT TREATMENT
INSERT INTO TREATMENT_T500 VALUES ('3674','Chemoth
erapy') INSERT INTO TREATMENT_T500 VALUES ('3304'
,'Radiation') INSERT INTO TREATMENT_T500 VALUES (
'3643','Face Lift') INSERT INTO
TREATMENT_T500 VALUES ('3249','Brain
Surgery') INSERT INTO TREATMENT_T500 VALUES ('345
6','Amputation') INSERT INTO TREATMENT_T500 VALUE
S ('3406','Liposuction')
36
Student QuestionsTeam 8B
37
Jonathans QA

• Question
• What requirements must MVCH meet when
• entering data values into a INSERT
• command?

38
Jons QA
Question When using SQL, there are certain
standards that MVCH must adhere to. Name six
benefits of these standards
39
Tammys QA
Question How does data control language commands
help the Database Administrator at MVCH to
control the database?
40
Aprils QA
Question SQL Commands can be classified into 3
types DDL, DML, and DCL. Of the three types
which does MVCH use to create a table and why?
41
Careys QA
Question How does MVCH change table definitions?
42
Lisas QA
Question What would MVCH have to do in order to
drop their Treatment Table?
43
Leighs Question
Question What are the pros and Cons that MVCH
would need to look at when using Dynamic Views?