ECET 450: Laboratory 6 Simple Medicine Database - 88372

Solution Posted by
jordanblink

jordanblink

Rating : (19)A+
Solution Detail
Price: $40.00
  • From: Computer Science, Algebra
  • Posted on: Sat 14 Feb, 2015
  • Request id: None
  • Purchased: 0 time(s)
  • Average Rating: No rating
Request Description
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 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 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 Description
Attachments
ECET450.zip
ECET450.zip