Project Case: Team 8B Mountain View Community Hospital

1
Project Case Team 8BMountain View Community
Hospital

• Team Members
• Rachel Lee
• Nickie Wooten
• Jacob Cook
• Jon Templeton
• Richie Crawford

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 What version of SQL will you use to do
  the project exercises?
• 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

• A person can use case tools that are compatible
  with this project which are the following
• (1) Visible Analyst, (2) the Cool Product
  Suite, (3) Easy Case, (4) Microsoft Word, (5)
  ERD, (6) Notepad, (7) Smart Draw, and also (8)
  the Designer 2000 are all real good to use.

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
MVCH

• MVCH has followed a series of steps for designing
  the columns needed for each table.
• A minimum of appropriate datatypes have been
  assigned to the attributes.
• Appropriate null values have been assigned to
  the columns.
• A column control of UNIQUE has been established
  for each column. The columns specified as
  PRIMARY KEY have been assigned NOT NULL.
  Relevant column constraints have been applied.

9
MVCH

• Steps Continued
• All primary key-foreign key mates have been
  established. Column constraint REFERENCES have
  been applied.
• Appropriate indexes have been assigned. These
  are EMP_NAME and PHYSICIAN_NAME.

10
MVCH

• When column constraints are applied, MVCH
  uses primary and foreign key given names. For
  example, the primary key constraint is named
  PHYSICIAN_PK in the CREATE TABLE PHYSICIAN_T.
  This allows one to easily identify the primary
  key constraint. Otherwise, if there is no
  constraint name, a system identifier would be
  assigned automatically, making it difficult to
  read.

11
MVCH

• Take notice that UNIT_COST is not listed in
  table CONSUMES, so one would need to reference
  to ITEM_NO in table ITEM. This enables each
  patient to be charged the same price for each
  item.

12
CREATE TABLE EMPLOYEE FOR MVCH

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

13
CREATE TABLE PHYSICIAN FOR MVCH

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

14
CREATE TABLE PERFORMS FOR MVCH

• CREATE TABLE PERFORMS_T50
• (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_PK50 PRIMARY KEY (PATIENT_NO,
  TREATMENT_DATE, TREATMENT_TIME, TREATMENT_NO),
• CONSTRAINT PERFORMS_FK50 FOREIGN KEY
  (PHYSICIAN_ID) REFERENCES PHYSICIAN_T50
  (PHYSICIAN_ID))

15
CREATE TABLE WARD FOR MVCH

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

16
CREATE TABLE ROOM FOR MVCH

• CREATE TABLE ROOM_T50
• (ROOM_NO NUMBER NOT NULL UNIQUE,
• WARD_NO VARCHAR2(25) NOT NULL UNIQUE,
• CONSTRAINT ROOM_PK50 PRIMARY KEY (ROOM_NO),
• CONSTRAINT ROOM_FK50 FOREIGN KEY (WARD_NO)
  REFERENCES WARD_T50 (WARD_NO))

17
CREATE TABLE BED FOR MVCH

• CREATE TABLE BED_T50
• (BED_NO NUMBER NOT NULL UNIQUE,
• ROOM_NO NUMBER NOT NULL UNIQUE,
• WARD_NO NUMBER NOT NULL UNIQUE,
• CONSTRAINT BED_PK50 PRIMARY KEY (BED_NO,
  ROOM_NO),
• CONSTRAINT BED_FK1_50 FOREIGN KEY (ROOM_NO)
  REFERENCES ROOM_T50 (ROOM_NO),
• CONSTRAINT BED_FK2_50 FOREIGN KEY (BED_NO)
  REFERENCES BED_T50 (BED_NO))

18
CREATE TABLE PATIENT FOR MVCH

• CREATE TABLE PATIENT_T50
• (PATIENT_NO NUMBER NOT NULL,
• PATIENT_NAME VARCHAR2(25),
• PHYSICIAN_ID NUMBER,
• BED_NO NUMBER,
• CONSTRAINT PATIENT_PK50 PRIMARY KEY (PATIENT_NO),
• CONSTRAINT PATIENT_FK1_50 FOREIGN KEY
  (PHYSICIAN_ID) REFERENCES PHYSICIAN_T50(PHYSICIAN_
  ID),
• CONSTRAINT PATIENT_FK2_50 FOREIGN KEY (BED_NO)
  REFERENCES BED_T50(BED_NO))

19
CREATE TABLE ASSIGNED FOR MVCH

• CREATE TABLE ASSIGNED_T50
• (WARD_NO VARCHAR2(25) NOT NULL,
• EMP_NO NUMBER NOT NULL,
• HOURS NUMBER,
• CONSTRAINT ASSIGNED_PK50 PRIMARY KEY (WARD_NO,
  EMP_NO),
• CONSTRAINT ASSIGNED_FK1_50 FOREIGN KEY (WARD_NO)
  REFERENCES WARD_T50 (WARD_NO),
• CONSTRAINT ASSIGNED_FK2_50 FOREIGN KEY (EMP_NO)
  REFERENCES EMPLOYEE_T50 (EMP_NO))

20
CREATE TABLE ITEM FOR MVCH

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

21
CREATE TABLE CONSUMES FOR MVCH

• CREATE TABLE ROOM_ITEM_T50
• (ITEM_NO NUMBER NOT NULL,
• ROOM_NO NUMBER NOT NULL,
• DATE_T DATE,
• QUANTITY NUMBER,
• CONSTRAINT ROOM_ITEM_PK50 PRIMARY KEY (ITEM_NO,
  ROOM_NO, DATE_T),
• CONSTRAINT ROOM_ITEM_FK1_50 FOREIGN KEY (ITEM_NO)
  REFERENCES ITEM_T50 (ITEM_NO),
• CONSTRAINT ROOM_ITEM_FK2_50 FOREIGN KEY (ROOM_NO)
  REFERENCES ROOM_T50 (ROOM_NO))

22
CREATE TABLE ROOM_ITEM FOR MVCH

• CREATE TABLE ROOM_ITEM_T50
• (ITEM_NO NUMBER NOT NULL,
• ROOM_NO NUMBER NOT NULL,
• DATE_T DATE,
• QUANTITY NUMBER,
• CONSTRAINT ROOM_ITEM_PK50 PRIMARY KEY (ITEM_NO,
  ROOM_NO, DATE_T),
• CONSTRAINT ROOM_ITEM_FK1_50 FOREIGN KEY (ITEM_NO)
  REFERENCES ITEM_T50 (ITEM_NO),
• CONSTRAINT ROOM_ITEM_FK2_50 FOREIGN KEY (ROOM_NO)
  REFERENCES ROOM_T50 (ROOM_NO))

23
CREATE TABLE TREATMENT FOR MVCH

• CREATE TABLE TREATMENT_T50
• (TREATMENT_NO NUMBER NOT NULL,
• TREATMENT_NAME NUMBER NOT NULL,
• CONSTRAINT TREATMENT_PK50 PRIMARY KEY
  (TREATMENT_NO))

24
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.
25
Project Answer 2

• ALTER TABLE ROOM_T50
• ADD CONSTRAINT ROOM_T50 PRIMARY KEY (ROOM_NO)
• ALTER TABLE ROOM_T50
• ADD CONSTRAINT TOOM_T50 FOREIGN KEY(WARD_NO)
• REFERENCES WARD_T50(WARD_NO)

26
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?
27
ANSWER
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, )
28
MVCH INSERT EMPLOYEE
INSERT INTO EMPLOYEE_T50 VALUES ('4492','Rachel
Lee') INSERT INTO EMPLOYEE_T50 VALUES ('5549','Ni
cki Wooten') INSERT INTO EMPLOYEE_T50 VALUES ('65
43','Jacob Cook') INSERT INTO EMPLOYEE_T50 VALUES
('8765','Jon Templeton') INSERT INTO
EMPLOYEE_T50 VALUES ('1987','Richie Crawford')
29
MVCH INSERT PHYSICIAN
INSERT INTO PHYSICIAN_T50 VALUES ('1234','Britney
Spears') INSERT INTO PHYSICIAN_T50 VALUES ('1235'
,'Janet Jackson') INSERT INTO PHYSICIAN_T50 VALUE
S ('1236','Ric Flair') INSERT INTO
PHYSICIAN_T50 VALUES ('1237','Will
Smith') INSERT INTO PHYSICIAN_T50 VALUES ('1238',
'Evander Hollifield') INSERT INTO
PHYSICIAN_T50 VALUES ('1239','Reggie White')
30
MVCH INSERT PERFORMS
INSERT INTO PERFORMS_T50 VALUES ('1010','2-APR-02'
,'120','3674','1234') INSERT INTO
PERFORMS_T50 VALUES ('1011','4-APR-02','945','3304
','1235') INSERT INTO PERFORMS_T50 VALUES ('1012'
,'6-APR-02','836','3643','1236') INSERT INTO
PERFORMS_T50 VALUES ('1013','30-MAR-02','745','324
9','1237') INSERT INTO PERFORMS_T50 VALUES ('1014
','28-MAR-02','1015','3456','1238') INSERT INTO
PERFORMS_T50 VALUES ('1015','26-MAR-02','645','340
6','1239')
31
MVCH - INSERT WARD
INSERT INTO WARD_T50 VALUES ('101','Intensive
Care') INSERT INTO WARD_T50 VALUES ('102','Pediat
ric Ward') INSERT INTO WARD_T50 VALUES ('103','Ma
ternity Ward') INSERT INTO WARD_T50 VALUES ('104'
,'Cosmetic Ward') INSERT INTO WARD_T50 VALUES ('1
05','Radiology Ward') INSERT INTO
WARD_T50 VALUES ('106','Cardiac Ward')
32
MVCH INSERT ROOM
INSERT INTO ROOM_T50 VALUES ('304','101') INSERT
INTO ROOM_T50 VALUES ('205','102') INSERT INTO
ROOM_T50 VALUES ('402','103') INSERT INTO
ROOM_T50 VALUES ('102','104') INSERT INTO
ROOM_T50 VALUES ('103','105') INSERT INTO
ROOM_T50 VALUES ('501','106')
33
MVCH INSERT BED
INSERT INTO BED_T50 VALUES ('0001','304','101') I
NSERT INTO BED_T50 VALUES ('0002','205','102') IN
SERT INTO BED_T50 VALUES ('0003','402','103') INS
ERT INTO BED_T50 VALUES ('0004','102','104') INSE
RT INTO BED_T50 VALUES ('0005','103','105') INSER
T INTO BED_T50 VALUES ('0006','501','106')
34
MVCH INSERT PATIENT
INSERT INTO PATIENT_T50 VALUES ('1010','John
Doe','1234','0001') INSERT INTO
PATIENT_T50 VALUES ('1011','Jane
Doe','1234','0002') INSERT INTO
PATIENT_T50 VALUES ('1012','Ice
Cube','1239','0003') INSERT INTO
PATIENT_T50 VALUES ('1013','Christina
Aguilera','1235','0004') INSERT INTO
PATIENT_T50 VALUES ('1014','Justin
Timberlake','1235','0005') INSERT INTO
PATIENT_T50 VALUES ('1015','Ozzy
Osbourn','1236','0006')
35
MVCH INSERT ASSIGNED
INSERT INTO ASSIGNED_T50 VALUES ('101','4492','8')
INSERT INTO ASSIGNED_T50 VALUES ('102','5549','8
') INSERT INTO ASSIGNED_T50 VALUES ('103','6543',
'8') INSERT INTO ASSIGNED_T50 VALUES ('104','8765
','8') INSERT INTO ASSIGNED_T50 VALUES ('105','19
87','8') INSERT INTO ASSIGNED_T50 VALUES ('106','
1812','8')
36
MVCH INSERT ITEM
INSERT INTO ITEM_T50 VALUES ('01','Asprin',9.50)
INSERT INTO ITEM_T50 VALUES ('02','Tylenol',2.50)
INSERT INTO ITEM_T50 VALUES ('03','Bayer',1.50)
INSERT INTO ITEM_T50 VALUES ('04','Novacaine',87.5
0) INSERT INTO ITEM_T50 VALUES ('05','Icy
Hot',4.95) INSERT INTO ITEM_T50 VALUES ('06','Ben
Gay',3.95)
37
MVCH INSERT CONSUMES
INSERT INTO CONSUMES_T50 VALUES ('01','1010','1-JA
N-01','2') INSERT INTO CONSUMES_T50 VALUES ('02',
'1015','2-JAN-01','3') INSERT INTO
CONSUMES_T50 VALUES ('03','1013','3-JAN-01','4')
INSERT INTO CONSUMES_T50 VALUES ('04','1015','4-JA
N-01','5') INSERT INTO CONSUMES_T50 VALUES ('05',
'1010','5-JAN-01','6') INSERT INTO
CONSUMES_T50 VALUES ('06','1012','6-JAN-01','7')
38
MVCH INSERT ROOM_ITEM
INSERT INTO ROOM_ITEM_T50 VALUES ('01','304','1-JA
N-02','2') INSERT INTO ROOM_ITEM_T50 VALUES ('02'
,'205','2-JAN-02','3') INSERT INTO
ROOM_ITEM_T50 VALUES ('03','402','3-JAN-02','4')
INSERT INTO ROOM_ITEM_T50 VALUES ('04','102','4-JA
N-02','5') INSERT INTO ROOM_ITEM_T50 VALUES ('05'
,'103','5-JAN-02','6') INSERT INTO
ROOM_ITEM_T50 VALUES ('06','501','6-JAN-02','7')
39
MVCH INSERT TREATMENT
INSERT INTO TREATMENT_T50 VALUES ('3674','Chemothe
rapy') INSERT INTO TREATMENT_T50 VALUES ('3304','
Radiation') INSERT INTO TREATMENT_T50 VALUES ('36
43','Face Lift') INSERT INTO TREATMENT_T50 VALUES
('3249','Brain Surgery') INSERT INTO
TREATMENT_T50 VALUES ('3456','Amputation') INSERT
INTO TREATMENT_T50 VALUES ('3406','Liposuction')
40
Student Questions
41
Jons Question
When using SQL, there are certain standards that
MVCH must adhere to. Name six benefits of these
standards
42
If the Mountain View Community Hospital entered
the wrong price for ITEM_No 04, in the table
ITEM_T50, how could they fix it?
Richies Question
43
Nickies Question

• Suppose the MVCH wants to change the structure of
  their room to include a their Room Type to have
  Single or Double Room. What commands would they
  need to alter the table structure? REFER to pg
  109 in SQL book.

44
Rachels Question When column constraints
are applied, how does MVCH name the tables
primary keys and foreign keys. Give an example
from a CREATE TABLE given.
45
Jacobs Question

• What are the original purposes of the SQL
  standards that MVCH would use?