SOLUTION AVAILABLE AT: http://libraay.com/downloads/ecet-450-laboratory-6-simple-medicine-database/

Problem:

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

SOLUTION AVAILABLE AT: http://libraay.com/downloads/ecet-450-laboratory-6-simple-medicine-database/