Complete development of a relational database you are to do

Question

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 keyconstraints.
• 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