ECET 450 iLab6

2. Create a script named mdbXXX.sql (where XXX are your initials) that

· Create the tables in 3NF needed to implement your DB schema.

· Your tables must include primary and foreign key constraints.

· Populate the database.

· Create the report that includes all three patients.

· Each student will be required to add an individual query. See your instructor for the specifications of your query.

Patient Doctor Room Admitted Required Medication

-------------------------------------------------------------------- -----------------------------------

1001 Fred Payne 101 MARCH 31, 2004 2 Prozac 3 grams annually

6 Aspirin 1 gram daily

9 Valium 2 grams weekly

3. Deliverables include copies of the following files:

· The Visio ERD file (mdbXXX.vcd)

· The ORACLE script file (mdbXXX.sql)

· The spool output file (mdbXXX.lst)

Development Suggestions for the mdbXXX.sql File

1. Ensure that the entire script is working before you “un-comment” the spool statements.

2. Develop your script one step at a time.

3. Include your personal and program information in the script file comment.

4. Suggested steps in developing the SQL script file:

· SET system parameters

· DROP TABLE firstTable

· CREATE TABLE firstTable

· DESC firstTable

· INSERT VALUES INTO firstTable

· CREATE VIEW if needed

· SELECT * FROM firstTable

· Repeat for secondTable

· Repeat for rest of tables

· JOIN all of the tables to produce the desired Report

· -- SPOOL OFF

Table of Data

ECET 450 Laboratory 6 Page 1 of 2