ECET 450: Laboratory 6 Simple Medicine Database


This laboratory exercise involves a complete development of a relational database.  You are to do the following:

  1. Use Visio to create the appropriate diagrams.
  2. Create a script named 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 (vcd)
  • The ORACLE script file (sql)
  • The spool output file (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

Patient

Name

Patient

Number

Medication

Name

No. of

Tablets

Dosage/

Tablet

Frequency Admitted Rm. Dr.
Fred 1001 Prozac 2 3 grams annually 30-MAR-07 101 Payne
Fred 1001 Aspirin 6 1 gram daily 30-MAR-07 101 Payne
Fred 1001 Valium 9 2 grams weekly 30-MAR-07 101 Payne
Sam 1005 Spam 1 3 grams 2 x daily 02-APR-07 150 Acres
Sam 1005 Prozac 2 6 grams daily 02-APR-07 150 Acres
Betty 1010 Spam 1 4 grams daily 11-MAY-07 222 Itchi
Betty 1010 Aspirin 6 3 grams daily 11-MAY-07 222 Itchi
Betty 1010 Valium 9 1 gram weekly 11-MAY-07 222 Itchi
Betty 1010 Sugar Water 11 1 gram monthly 11-MAY-07 222 Itchi

Leave a Reply

Your email address will not be published. Required fields are marked *



  • File Format: .sql, .lst (Output file), .vsd