Mountain View Community Hospital Team 8b

1
Mountain View Community HospitalTeam 8b

• Presented by Robert McCaa
• Hinali Desai
• John Hines
• Talvickeo Banks

2
Introduction

• Use SQL data model constructed for MVCH in
  Chapter 4 to complete the project exercises.

3
Project Question 1

• What version of SQL will you use to do the
  project exercises?
• Answer
• The solutions are based on SQLPlus, Oracle 9i.

4
Project Question 2

• What CASE tools will be available to be used to
  do the exercises in this project?
• Answer
• Visible Analyst
• The Cool Product Suite
• Designer/2000
• Easy Case

5
Project Exercise 1

• Create in SQL the MVCH 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, indexes, etc.

6
Project Exercise 1 Answer

• The CREATE TABLE commands suggested here are
  simple versions, based on the ERD solution for
  Chapter 3 and do not contain examples of setting
  additional parameters such as TABLESPACE,
  STORAGE, PCTFREE, or PCTUSED, which are optional,
  but would be used in defining tables for
  production systems in Oracle 9i. Only a minimal
  set of attributes has been defined. Students
  should be able to identify many additional
  relevant attributes form the case material. In
  some cases, the appropriateness of the table
  definitions depends on the values that will be
  assigned. For example, a unique number for each
  bed is assumed here. A designer might elect to
  number beds within each room, in which case,
  composite keys would be needed.

7
CREATE TABLE EMPLOYEE_50000

• CREATE TABLE EMPLOYEE_T50000
• (EMP_NO NUMBER NOT NULL,
• EMP_NAME VARCHAR2(25),
• CONSTRAINT EMP_PK PRIMARY KEY (EMP_NO))

8
CREATE TABLE PHYSICIAN_T50000

• CREATE TABLE PHYSICIAN_T50000
• (PHYSICIAN_ID NUMBER NOT NULL,
• PHYSICIAN_NAME VARCHAR2(25),
• CONSTRAINT PHYSICIAN_PK PRIMARY KEY
  (PHYSICIAN_ID))

9
CREATE TABLE PERFORMS_T50000

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

10
CREATE TABLE WARD_T50000

• CREATE TABLE WARD_T50000
• (WARD_NO VARCHAR2(25) NOT NULL,
• WARD_NAME VARCHAR2(25),
• CONSTRAINT WARD_PK PRIMARY KEY (WARD_NO))

11
CREATE TABLE ROOM_T50000

• CREATE TABLE ROOM_T50000
• (ROOM_NO NUMBER NOT NULL,
• WARD_NO VARCHAR2(25) NOT NULL,
• CONSTRAINT ROOM_PK PRIMARY KEY (ROOM_NO),
• CONSTRAINT ROOM_FK50 FOREIGN KEY (WARD_NO)
  REFERENCES WARD_T50000 (WARD_NO))

12
CREATE TABLE BED_T50000

• CREATE TABLE BED_T50000
• (BED_NO NUMBER(6) NOT NULL,
• ROOM_NO NUMBER(6) NOT NULL,
• WARD_NO NUMBER(6) NOT NULL,
• CONSTRAINT BED_PK PRIMARY KEY (BED_NO, ROOM_NO),
• CONSTRAINT BED_FK50 FOREIGN KEY (ROOM_NO)
  REFERENCES ROOM_T50000 (ROOM_NO))

13
CREATE TABLE PATIENT_T50000

• CREATE TABLE PATIENT_T50000
• (PATIENT_NO NUMBER NOT NULL,
• PATIENT_NAME VARCHAR2(25),
• PHYSICIAN_ID NUMBER,
• BED_NO NUMBER,
• CONSTRAINT PATIENT_PK PRIMARY KEY (PATIENT_NO),
• CONSTRAINT PATIENT_FK1 FOREIGN KEY (PHYSICIAN_ID)
  REFERENCES PHYSICIAN_T50000(PHYSICIAN_ID))

14
CREATE TABLE ASSIGNED_T50000

• CREATE TABLE ASSIGNED_T50000
• (WARD_NO VARCHAR2(25) NOT NULL,
• EMP_NO NUMBER NOT NULL,
• HOURS NUMBER,
• CONSTRAINT ASSIGNED_PK PRIMARY KEY (WARD_NO,
  EMP_NO),
• CONSTRAINT ASSIGNED_FK1 FOREIGN KEY (WARD_NO)
  REFERENCES WARD_T50000 (WARD_NO),
• CONSTRAINT ASSIGNED_FK2 FOREIGN KEY (EMP_NO)
  REFERENCES EMPLOYEE_T50000 (EMP_NO))

15
CREATE TABLE ITEM_T50000

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

16
CREATE TABLE CONSUMES_T50000

• CREATE TABLE CONSUMES_T50000
• (ITEM_NO NUMBER NOT NULL,
• PATIENT_NO NUMBER NOT NULL,
• DATE_T DATE NOT NULL,
• QUANTITY NUMBER,
• CONSTRAINT CONSUMES_PK PRIMARY KEY (ITEM_NO,
  PATIENT_NO, DATE_T),
• CONSTRAINT CONSUMES_FK1 FOREIGN KEY (ITEM_NO)
  REFERENCES ITEM_T50000(ITEM_NO),
• CONSTRAINT CONSUMES_FK2 FOREIGN KEY (PATIENT_NO)
  REFERENCES PATIENT_T50000(PATIENT_NO))

17
CREATE TABLE ROOM_ITEM_T50000

• CREATE TABLE ROOM_ITEM_T50000
• (ITEM_NO NUMBER NOT NULL,
• ROOM_NO NUMBER NOT NULL,
• DATE_T DATE,
• QUANTITY NUMBER,
• CONSTRAINT ROOM_ITEM_PK PRIMARY KEY (ITEM_NO,
  ROOM_NO, DATE_T),
• CONSTRAINT ROOM_ITEM_FK1 FOREIGN KEY (ITEM_NO)
  REFERENCES ITEM_T50000(ITEM_NO),
• CONSTRAINT ROOM_ITEM_FK2 FOREIGN KEY (ROOM_NO)
  REFERENCES ROOM_T50000(ROOM_NO))

18
CREATE TABLE TREATMENT_T50000

• CREATE TABLE TREATMENT_T50000
• (TREATMENT_NO NUMBER NOT NULL,
• TREATMENT_NAME VARCHAR2(25) NOT NULL,
• CONSTRAINT TREATMENT_PK PRIMARY KEY
  (TREATMENT_NO))

19
Project Exercise 2

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

20
Project Answer 2

• ALTER TABLE ROOM_T
• ADD CONSTRAINT ROOM_FK FOREIGN KEY(ROOM_NO)
• ALTER TABLE ROOM_T
• ALL CONSTRAINTS ROOM_FK FOREIGN KEY(WARD_NO)
• REFERENCES WARD_T(WARD_NO)

21
Project Exercise 3

• Select at least a portion of you database and
  populate it with sample data. For example, you
  may wish to work with the staff, care center,
  patient part of you database. Of you may be
  interested in working with part of you 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 you database?

22
Project Exercise 3 contd
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 database VALUES (attribute
values)
23
ASSIGNED_T50000

• INSERT INTO ASSIGNED_T50000
• VALUES
• ('101','4492','8')
• INSERT INTO ASSIGNED_T50000
• VALUES
• ('102','5549','8')
• INSERT INTO ASSIGNED_T50000
• VALUES
• ('103','6543','8')
• INSERT INTO ASSIGNED_T50000
• VALUES
• ('104','8765','8')
• INSERT INTO ASSIGNED_T50000
• VALUES
• ('105','1987','8')
• INSERT INTO ASSIGNED_T50000
• VALUES
• ('106','1812','8')

24
CONSUMES_T50000

• INSERT INTO CONSUMES_T50000
• VALUES
• ('01','1010','1-JAN-01','2')
• INSERT INTO CONSUMES_T50000
• VALUES
• ('02','1015','2-JAN-01','3')
• INSERT INTO CONSUMES_T50000
• VALUES
• ('03','1013','3-JAN-01','4')
• INSERT INTO CONSUMES_T50000
• VALUES
• ('04','1015','4-JAN-01','5')
• INSERT INTO CONSUMES_T50000
• VALUES
• ('05','1010','5-JAN-01','6')
• INSERT INTO CONSUMES_T50000
• VALUES
• ('06','1012','6-JAN-01','7')

25
ITEM_T50000

• INSERT INTO ITEM_T50000
• VALUES
• ('01','Viagra',9.50)
• INSERT INTO ITEM_T50000
• VALUES
• ('02','Valtrex',2.50)
• INSERT INTO ITEM_T50000
• VALUES
• ('03','Asprin',1.50)
• INSERT INTO ITEM_T50000
• VALUES
• ('04','Novacaine',87.50)
• INSERT INTO ITEM_T50000
• VALUES
• ('05','Midol',4.95)
• INSERT INTO ITEM_T50000
• VALUES
• ('06','Ben Gay',3.95)

26
PERFORMS_T50000

• INSERT INTO PERFORMS_T50000
• VALUES
• ('1010','2-APR-01','120','3674','1234')
• INSERT INTO PERFORMS_T50000
• VALUES
• ('1011','4-APR-01','945','3304','1235')
• INSERT INTO PERFORMS_T50000
• VALUES
• ('1012','6-APR-01','836','3643','1236')
• INSERT INTO PERFORMS_T50000
• VALUES
• ('1013','30-MAR-01','745','3249','1237')
• INSERT INTO PERFORMS_T50000
• VALUES
• ('1014','28-MAR-01','1015','3456','1238')
• INSERT INTO PERFORMS_T50000
• VALUES
• ('1015','26-MAR-01','645','3406','1239')

27
ROOM_T50000

• INSERT INTO ROOM_T50000
• VALUES
• ('304','101')
• INSERT INTO ROOM_T50000
• VALUES
• ('205','102')
• INSERT INTO ROOM_T50000
• VALUES
• ('402','103')
• INSERT INTO ROOM_T50000
• VALUES
• ('102','104')
• INSERT INTO ROOM_T50000
• VALUES
• ('103','105')
• INSERT INTO ROOM_T50000
• VALUES
• ('501','106')

28
TREATMENT_T50000

• INSERT INTO TREATMENT_T50000
• VALUES
• ('3674','Chemotherapy')
• INSERT INTO TREATMENT_T50000
• VALUES
• ('3304','Breast Enhancement')
• INSERT INTO TREATMENT_T50000
• VALUES
• ('3643','Face Lift')
• INSERT INTO TREATMENT_T50000
• VALUES
• ('3249','Brain Surgery')
• INSERT INTO TREATMENT_T50000
• VALUES
• ('3456','Amputation')
• INSERT INTO TREATMENT_T50000
• VALUES
• ('3406','Liposuction')

29
BED_T50000

• INSERT INTO BED_T50000
• VALUES
• ('0001','304','101')
• INSERT INTO BED_T50000
• VALUES
• ('0002','205','102')
• INSERT INTO BED_T50000
• VALUES
• ('0003','402','103')
• INSERT INTO BED_T50000
• VALUES
• ('0004','102','104')
• INSERT INTO BED_T50000
• VALUES
• ('0005','103','105')
• INSERT INTO BED_T50000
• VALUES
• ('0006','501','106')

30
EMPLOYEE_T50000

• INSERT INTO EMPLOYEE_T50000
• VALUES
• ('4492','Matt Edwards')
• INSERT INTO EMPLOYEE_T50000
• VALUES
• ('5549','Rachele Diemer')
• INSERT INTO EMPLOYEE_T50000
• VALUES
• ('6543','Sarah Slatton')
• INSERT INTO EMPLOYEE_T50000
• VALUES
• ('8765','Sarah Edman')
• INSERT INTO EMPLOYEE_T50000
• VALUES
• ('1987','Ellion Dison')
• INSERT INTO EMPLOYEE_T50000
• VALUES
• ('1812','Danual House')

31
PATIENT_T50000

• INSERT INTO PATIENT_T50000
• VALUES
• ('1010','Kylynne Edwards','1234','0001')
• INSERT INTO PATIENT_T50000
• VALUES
• ('1011','Garret Jackson','1234','0002')
• INSERT INTO PATIENT_T50000
• VALUES
• ('1012','M.C. Hammer','1239','0003')
• INSERT INTO PATIENT_T50000
• VALUES
• ('1013','Shonna Lanier','1235','0004')
• INSERT INTO PATIENT_T50000
• VALUES
• ('1014','Nelly Jones','1235','0005')
• INSERT INTO PATIENT_T50000
• VALUES
• ('1015','Ozzy Osbourn','1236','0006')

32
PHYSICIAN_T50000

• INSERT INTO PHYSICIAN_T50000
• VALUES
• ('1234','Brad Pitt')
• INSERT INTO PHYSICIAN_T50000
• VALUES
• ('1235','Ben Affleck')
• INSERT INTO PHYSICIAN_T50000
• VALUES
• ('1236','Kenny Chesney')
• INSERT INTO PHYSICIAN_T50000
• VALUES
• ('1237','Steven Segall')
• INSERT INTO PHYSICIAN_T50000
• VALUES
• ('1238','Tim McGraw')
• INSERT INTO PHYSICIAN_T50000
• VALUES
• ('1239','Faith Hill')

33
ROOM_ITEM_T50000

• INSERT INTO ROOM_ITEM_T50000
• VALUES
• ('01','304','1-JAN-01','2')
• INSERT INTO ROOM_ITEM_T50000
• VALUES
• ('02','205','2-JAN-01','3')
• INSERT INTO ROOM_ITEM_T50000
• VALUES
• ('03','402','3-JAN-01','4')
• INSERT INTO ROOM_ITEM_T50000
• VALUES
• ('04','102','4-JAN-01','5')
• INSERT INTO ROOM_ITEM_T50000
• VALUES
• ('05','103','5-JAN-01','6')
• INSERT INTO ROOM_ITEM_T50000
• VALUES
• ('06','501','6-JAN-01','7')

34
WARD_T50000

• INSERT INTO WARD_T50000
• VALUES
• ('101','Intensive Care')
• INSERT INTO WARD_T50000
• VALUES
• ('102','Pediatric Ward')
• INSERT INTO WARD_T50000
• VALUES
• ('103','Maternity Ward')
• INSERT INTO WARD_T50000
• VALUES
• ('104','Cosmetic Ward')
• INSERT INTO WARD_T50000
• VALUES
• ('105','Radiology Ward')
• INSERT INTO WARD_T50000
• VALUES
• ('106','Cardiac Ward')

35
Student Questions
36
Roberts Question and Answer

• What command would MVCH use if a problem arose
  where they needed to delete the table customer
  database?

37
Hinalis Question and Answer

• Why would MVCH use a Relational Database
  Management System as their data management
  system?

38
Johns Question and Answer

• How would MVCH benefit from using data control
  language for regular database maintenance?
•  

39
Vicks Question and Answer

• In MVCH, what is the importance of using the
  INSERT Command with Nulls?

40
Now Actual Hands-On Demonstration of SQL