Project Instructions

Return to Home Page 

1. Make sure you are familiar with Oracle System (see the Tutorial at the Supplementary Materials Page).
    

2. Create your command file.
   
   Use notepad (Recommended) or a text editor to create a file. For the initial project, I called mine medical.sql but you can call yours anything you want. If you do call it medical.sql, then you can follow the instructions given below exactly. Otherwise you will have to make some substitutions.
   
   NOTE: You can use a word processing package such as Word, BUT YOU MUST THEN SAVE IT IN ASCII FORMAT. You can NOT  save it as a regular Word File (*.doc). When you save the file, you must use the commands:
   
               File → Save As → Save as type → Plain Text (*.txt)
    

3. Enter in the Commands to create the Database.

   For the initial Project, I give you all of the commands (although I have intentionally included a few minor errors). The commands are available from the Creating the Database Page. I would also suggest that you compare your commands with the complete ERD, which I also give you at the Complete ERD Page.
   
   Note that the command I gave you in the script assumes that you will make errors. The DROP command you see assumes that you are rerunning the program a second (or third) time. You can only DROP a table if it exists. The first time out, of course, it doesn't, so you will get an error message. If you did create it, but the program still has some bugs, you will have to delete (drop) it and then recreate it (More on this in the lab).
    

4. Create the Database.

   Because these instructions are somewhat lengthy I have put them in a separate page. Click here to see them.
    

5. Keep debugging until the program executes properly
   
   How will you know if it was executed properly? You will notice that after each create statement, I have included the command DESCRIBE (this will show you each table's structure). If the table's structure is displayed, that means it has executed properly. If it does not display (you get the output "Table does not exist", that means that something went wrong.
    

6. Save the Database.
   
   Enter the command:
   
   SQL> commit;
   
   Why this command?  Even though you have created the tables necessary, that doesn't mean that they are permanent (i.e., once you are done with your session, the tables will disappear). The commit command makes them permanent.
    

7. Get the data file.
   
   For the initial project, I have created all of the data to be inserted into your tables.  I am going to provide you with a script (schema) that will insert the data into your program. IT WILL ONLY WORK IF YOU USE THE SAME TABLE NAMES AS GIVEN IN THE SCRIPT, AND IT WILL ONLY WORK PROPERLY IF YOU USE THE SAME DATA TYPES. You’re on your own for any editing needed.
   
   For the final/Extra Credit Project, you will, of course, have to create your own data.
   
   More information can be found at the Inserting Project Data Page.
    

8. Once you have correctly inserted all of the data, enter the command:
   
   SQL> commit;
   
   Once again, this will make the data in the tables permanent.
    

9. Prepare your Answer file(s)

   Because these instructions are again somewhat lengthy I have put them in a separate page. Click here to see them.
    

10. Answer all of the part 1 questions (See questions 1 page).
    
    This sounds obvious enough, but there is more to it than just that. THE QUERIES NEEDED FOR SHOULD BE PLACED IN A SEPARATE FILE(S).
    
    Why? When you go to turn in your answers, you will have to rerun all of your queries. If you don't have them in a separate file, you will have to re-enter each one (a very time consuming process). 
    
    Let's go over the procedures necessary, using question 1 as an example. Question 1 is: "Get a List of all of Dr. Smith’s Patients". This is a very simple query:

Now that all of your queries are running correctly, you need to turn them in. You are going to create a file which will have all of your output written to it (let's call the file answers1.out, although, again, this is an arbitrary file name). Simply follow the commands below:

SQL> spool answers1.out;
SQL> @medical.sql;
SQL> @q1.sql;
SQL> @q2.sql;
    .  .  .  .   .   .
SQL> @qn.sql;
SQL> spool off;

You have just created a file called answers1.out that contains all of the output that appeared on your screen. The spool command creates the file and duplicates the output to the file; the spool off command stops sending the output to the file.

In part two of the assignment, you are going to make physical changes to the database. DO NOT ATTEMPT PART II UNTIL AFTER YOU HAVE SUBMITTED PART I of the assignment.

Once again, it behooves you to put each command in a separate file. Once you are sure they work, spool your output to a file (as above) and mail the spooled file to Mr. Dash.  

I (sincerely) hope that each of you all get full points for your efforts. I am giving you three weeks AFTER the Lab to complete the assignment (that is MORE than enough time). DON'T wait until the night before the assignment is due to complete it; chances are extremely good that you won’t make it. START NOW. Remember that I am always available during office hours and by appointment.

  This page was last updated on 08/24/03.