SQL statement
jontha11
week6lab.zipdocuments--ECET450_W6_iLab_instruction.docx
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 |
ECET 450 Laboratory 6 Page 2 of 2
mdbJWG.sql
/* * Jonathan Gilchrist * *Description: Lab6 * */ -- Patient table create table patient( patient_name varchar(30) constraint pk_patient_patient_name primary key, patient_id number(4), date_admitted date); insert into patient values ( 'Fred', 1001, '30-MAR-07'); insert into patient values ( 'Sam', 1005, '02-Apr-07'); insert into patient values ( 'Betty', 1010, '11-May-07'); --Patient info table create table patient_info( Patient_room# number(3), DR_name varchar(30), patient_name varchar(30) constraint patient_name_fk references patient(patient_name)); insert into patient_info values( 101,'Payne', 'Fred'); insert into patient_info values( 150,'Acres', 'Sam'); insert into patient_info values( 222,'Itchi', 'Betty'); --medical schedule table create table medical_sched( med_name varchar(30), dosage_tablet varchar(10), tablet_qty number(3), frequency varchar(20), patient_name varchar(30) constraint patient_name_med_fk references patient(patient_name)); insert into medical_sched values('prozac', '3 grams',2, 'anually', 'Fred'); insert into medical_sched values('asprin', '1 gram',6, 'daily', 'Fred'); insert into medical_sched values('valium', '2 grams',9, 'weekly', 'Fred'); insert into medical_sched values('Spam', '3 grams',1, '2x daily', 'Sam'); insert into medical_sched values('prozac', '6 grams',2, 'daily', 'Sam'); insert into medical_sched values('Spam', '4 grams',1, '2x daily', 'Betty'); insert into medical_sched values('asprin', '3 grams',6, 'daily', 'Betty'); insert into medical_sched values('valium', '1 gram',9, 'weekly', 'Betty'); insert into medical_sched values('sugar water', '1 gram',11, '2x daily', 'Betty'); --JOIN the tables SELECT p.patient_name, p.patient_id,p.date_admitted,i.patient_room#, i.DR_name, m.med_name,m.dosage_tablet, m.tablet_qty, m.frequency FROM patient p INNER JOIN patient_info i ON p.patient_name=i.patient_name INNER JOIN medical_sched m ON i.patient_name = m.patient_name; --code for the final display Column p.patient_name HEADING 'Patient Doctor Room Admitted'; Column p.patient_id HEADING 'Patient Doctor Room Admitted'; Column i.DR_name HEADING 'Patient Doctor Room Admitted'; Column i.patient_room# HEADING 'Patient Doctor Room Admitted'; Column p.date_admitted HEADING 'Patient Doctor Room Admitted'; Column m.tablet_qty HEADING 'Required Medication'; Column m.med_name HEADING ' Required Medication'; Column m.dosage_tablet HEADING 'Required Medication'; Column m.frequency HEADING ' Required Medication'; SELECT p.patient_id ||' '||p.patient_name ||' '||i.DR_name ||' '||i.patient_room#||' '|| p.date_admitted as p.patient_name , m.tablet_qty||' '||m.med_name||' '||m.dosage_tablet||' '||m.frequency as m.tablet_qty FROM patient p INNER JOIN patient_info i ON p.patient_name=i.patient_name INNER JOIN medical_sched m ON i.patient_name = m.patient_name;
Archive created by free jZip.url
[InternetShortcut] URL=http://www.jzip.com/archive_link
