Creating the Tables


OK, ready to go? Start SQL*Plus and log on to the database using the SYSTEM/MANAGER ID. You should see the SQL> prompt.

I will walk you through the script lines, pointing out various important processing steps. The script commands and system messages will be shown in bold . At the end, I'll give you the complete script and the output listing showing what the system generated, along with comments to help you follow the flow. For those of you who are wondering whether there's an easier way, the answer is yes. In practice, all of us have a medicine bag of various tools, tricks, and scripts that we create in a text editor and save as a .sql type. Here's an example of saving a script in Notepad. Notice that the file name is in quotes, and it is saved as type "All Files":

graphics/09fig01.gif

These files can then be run from the SQL> prompt: @A:\CREATE_TABLES.SQL . For your own learning, however, I advise you to type the commands manually, get the normal syntax errors, struggle with cleaning them up, and in general become very good at creating SQL*Plus commands. (This exercise will be frustrating at times, but believe me, it will be worth it.)

Getting Started

The first step is to tell Oracle to create an output listing of all commands and system messages. We do this by using two SQL commands SPOOL and SET ECHO :

  1. SPOOL tells Oracle to create a file that will contain every command and system message that is generated when you create the tables.

    Note

    Note that SPOOL cannot create a folder, so if you want to use something like C:\SPOOL_FILES as a folder, you must create the folder first.

  2. SET ECHO ON means that you want to see every system message that appears when you run your commands, including all errors.

So here are the first couple of lines to type in:

 SQL> SET ECHO ON; SQL>SPOOL C:\SPOOL_FILES\WAIVER.LST; 

Creating the Tablespace

Now we create the tablespace, using the ALTER , DROP , CREATE commands we just talked about. Type in the following:

 ALTER TABLESPACE TABLESPACE_FOR_WAIVERS OFFLINE NORMAL      ; DROP TABLESPACE TABLESPACE_FOR_WAIVERS   INCLUDING CONTENTS      ; CREATE TABLESPACE TABLE_SPACE_FOR_WAIVERS   DATAFILE 'D:\TABLESPACES\DATA\TABLESPACE_FOR_WAIVERS'   SIZE 1000M   OFFLUNE   PERMANENT       ; 

"Whoa," you say? Just got a nasty error:

 SQL> ALTER TABLESPACE TABLESPACE_FOR_WAIVERS   2 OFFLUNE; OFFLUNE * ERROR at line 2: ORA-02142: missing or invalid ALTER TABLESPACE option 

You've discovered by now (unless you're a perfect typist) that you cannot backspace when working from the SQL command line. Do you have to retype the entire command? No. SQL*Plus comes with a default editor (you can change it, but we're not going to discuss that right now). If you are running straight SQL commands, you can type them in on the command line and then easily edit them by typing EDIT at the SQL> prompt. This command takes you right to the editor, and the editor (in this example Notepad) shows you your latest SQL command:

graphics/09fig02.gif

Now you can correct the error. In this example OFFLUNE should be OFFLINE . After you correct the error, select File Save , and at the SQL> prompt, just type "RUN", and the ALTER command will fire off:

 SQL>RUN SQL> ALTER TABLESPACE TABLESPACE_FOR_WAIVERS graphics/1_icon.gif 2 OFFLINE;   ALTER TABLESPACE TABLESPACE_FOR_WAIVERS * ERROR at line 1: ORA-00959: tablespace 'TABLESPACE_FOR_WAIVERS' does not exist 

(1) Note that this is the error you want to see.

Now type in the other two commands DROP and CREATE and you should see these messages:

 SQL> DROP TABLESPACE TABLESPACE_FOR_WAIVERS graphics/2_icon.gif 2 INCLUDING CONTENTS;  Tablespace does not exist  . +++++++++++++++++++++++++++++++++++++++++   SQL> RUN   1 CREATE TABLESPACE TABLESPACE_FOR_WAIVERS   2 DATAFILE 'D:\TABLESPACES\DATA\TABLESPACE_FOR_WAIVERS'   3 SIZE 1000M   3 ONLINE   4 PERMANENT  Tablespace created  . graphics/3_icon.gif 

(2) This is what you want to see.

(3) This is the message that you want.

Note

When you drop a tablespace, unfortunately the data file is not deleted. The tablespace name is gone, but if you try to re-create it using the same data file, you will get the following error:

 *               ERROR at line 1:               ORA-01119: error in creating database file 'TABLESPACE_FOR_WAIVERS'               ORA-07399: sfccf: file exists. 

This means that the file named TABLESPACE_FOR_WAIVERS already exists, so you cannot use it to create another tablespace. However, you can use something like TABLESPACE2_FOR_WAIVERS :

 SQL>RUN                    CREATE TABLESPACE TABLESPACE_FOR_WAIVERS                    DATAFILE 'D:\TABLESPACES\DATA\TABLESPACE2_FOR_WAIVERS'                    SIZE 1M                    ONLINE                    PERMANENT  Tablespace created  . 

Or you can delete the file (that is, go to the D:\TABLESPACES\DATA folder and delete the file called TABLESPACE_FOR_WAIVERS ) and then reuse the name.


Note

The editor is fine for straight SQL commands, but not for PL/SQL commands such as loops and procedures. To run PL/SQL, you have to run a script. How do you run a script from the command line? Easyjust type in "@< path > & script name.SQL". For example, to run the script New_Names that is on your C: drive in your SQL_SCRIPTS folder, type in "@C:\SQL_SCRIPTS\NEW_NAMES.SQL" at the SQL> prompt, and SQL will then execute the script. We'll talk about this more in the context of other programming examples throughout the book, but for those of you who are curious , go ahead and write a couple of the commands we're using here, save them as a .sql file, and run them from the SQL command line.


Creating Tables

Now we're ready to create our tables. Remember that because we have constraints in the form of referential integrity built in, the tables must be created in a certain sequence. The error in the preceding section was included to try to make this clear.

First we need the TEST_TYPE_DESC table. To drop and create this table, type in the following commands. Once SQL senses the semicolon, it will run the command:

 DROP TABLE TEST_TYPE_DESC  CASCADE CONSTRAINTS; CREATE TABLE TEST_TYPE_DESC    ( TYPE_ID    NUMBER(6) PRIMARY KEY, TYPE_DESC  VARCHAR2(30) NOT NULL                             ) TABLESPACE TABLESPACE_FOR_WAIVERS                             ; 

Note

CASCADE CONSTRAINTS means that Oracle will "drop" any referential integrity constraints associated with this table. So be very careful with this command. It is normally used when tables are first being created just in case they already exist, most likely from earlier testing. The DROP...CASCADE construction is also often used when we're working with a test database, where the tables are constantly changing.


Next we need the AUTHORS table:

 DROP TABLE AUTHORS   CASCADE CONSTRAINTS; CREATE TABLE AUTHORS             ( AUTHOR_ID       NUMBER(9) PRIMARY KEY, AUTHOR          VARCHAR2(60) DEFAULT 'WAIVER ADMINISTRATION'                                                            )      TABLESPACE TABLESPACE_FOR_WAIVERS  ;  

Then we create the TEST_ID table:

 DROP TABLE TEST_ID CASCADE CONSTRAINTS; CREATE TABLE  TEST_ID           ( TEST_ID       NUMBER(6)    PRIMARY KEY, TEST_NAME     VARCHAR2(40) NOT NULL, DATE_CREATED  DATE         NOT NULL, AUTHOR        NUMBER(9)    REFERENCES AUTHORS(AUTHOR_ID), COMMENTS      VARCHAR2(30), TYPE_ID       NUMBER(6)    REFERENCES TEST_TYPE_DESC(TYPE_ID), PASSING_GRADE NUMBER(2)    NOT NULL, TIME_LIMIT    NUMBER(4,2)  NOT NULL                             ) TABLESPACE TABLESPACE_FOR_WAIVERS                             ; 

Notice that if you try to create a table that references a table that has not been built yet, you get this error:

 SQL> CREATE TABLE TEST_QUESTIONS_LINK   (   2 LINK_TEST_ID       NUMBER(6) REFERENCES TEST_ID(TEST_ID),   3 LINK_QUESTION_ID   NUMBER(6) REFERENCES QUESTIONS(QUESTION_ID),   4 PRIMARY KEY (LINK_TEST_ID, LINK_QUESTION_ID)   5              ); LINK_QUESTION_ID       NUMBER(6) REFERENCES QUESTIONS(QUESTION_ID),                        *  ERROR at line 3:   ORA-00904: invalid column name  

In other words, the QUESTIONS table does not exist yet, and Oracle is already enforcing referential integrity!

Now work on the questions by starting with the QUESTIONS_TYPE_DESC table:

 DROP TABLE QUESTIONS_TYPE_DESC   CASCADE CONSTRAINTS; CREATE TABLE       QUESTIONS_TYPE_DESC             ( QUESTION_TYPE      NUMBER(6) PRIMARY KEY, QUESTION_TYPE_DESC VARCHAR2(40) NOT NULL                                                ) TABLESPACE TABLESPACE_FOR_WAIVERS                                                 ; 

And next create the QUESTIONS table itself:

 DROP TABLE QUESTIONS CASCADE CONSTRAINTS; CREATE TABLE     QUESTIONS      ( QUESTION_ID      NUMBER(6) PRIMARY KEY, QUESTION         VARCHAR2(500) NOT NULL, CORRECT_ANSWER   VARCHAR2(2) NOT NULL, QUESTION_TYPE    NUMBER(6) REFERENCES QUESTIONS_TYPE_DESC(QUESTION_TYPE)                             ) TABLESPACE TABLESPACE_FOR_WAIVERS                              ; 

Next link the QUESTIONS and TEST_ID tables by creating the linking table TEST_QUESTIONS_LINK:

 DROP TABLE TEST_QUESTIONS_LINK CASCADE CONSTRAINTS; CREATE TABLE     TEST_QUESTIONS_LINK   ( LINK_TEST_ID     NUMBER(6) REFERENCES TEST_ID(TEST_ID), LINK_QUESTION_ID NUMBER(6) REFERENCES QUESTIONS(QUESTION_ID), WEIGHT           NUMBER(2,1) DEFAULT 1, PRIMARY KEY (LINK_TEST_ID, LINK_QUESTION_ID)                                     ) TABLESPACE TABLESPACE_FOR_WAIVERS                                     ; 

Now create the last core table, ANSWERS:

 DROP TABLE ANSWERS CASCADE CONSTRAINTS; CREATE TABLE ANSWERS                ( ANSWER_QUESTION_ID NUMBER(6) PRIMARY KEY REFERENCES   QUESTIONS(QUESTION_ID), ANSWER_ID           VARCHAR2(2) NOT NULL, ANSWER              VARCHAR2(30) NOT NULL                                  ) TABLESPACE TABLESPACE_FOR_WAIVERS                                  ; 

Once these base tables have been defined, we can move on to the student and history tables. First create the STUDENTS table:

 DROP TABLE     STUDENTS CASCADE CONSTRAINTS; CREATE TABLE   STUDENTS                 ( STUDENT_ID     NUMBER(9) PRIMARY KEY, F_NAME         ARCHAR2(15) NOT NULL, M_I            VARCHAR2(1), L_NAME         VARCHAR2(20) NOT NULL, SSNUM          NUMBER(9) NOT NULL UNIQUE, B_DATE         DATE, SEX            VARCHAR2(1) CHECK (SEX IN ('M', 'F', 'm', 'f')), STREET1        VARCHAR2(15), STREET2        VARCHAR2(15), TOWN           VARCHAR2(20), STATE          VARCHAR2(2), COUNTRY        VARCHAR2(15) DEFAULT 'UNITED STATES', ZIP            NUMBER(5) NOT NULL, STUDENT_LEVEL  NUMBER(6) NOT NULL, DATE_CREATED   DATE, CREATED_BY     VARCHAR2(20), DEPARTMENT_MAJOR VARCHAR2(10) NOT NULL, EMAIL          VARCHAR2(60)                                           ) TABLESPACE TABLESPACE_FOR_WAIVERS  ;  

Only two more to go! First create the TEST_HISTORY table:

 DROP TABLE  TEST_HISTORY   CASCADE CONSTRAINTS; CREATE TABLE    TEST_HISTORY       ( TEST_ID         NUMBER(6) REFERENCES TEST_ID(TEST_ID), STUDENT_ID      NUMBER(9) REFERENCES STUDENTS(STUDENT_ID), SCORE           NUMBER(3) NOT NULL, DATE_TAKEN      DATE NOT NULL, START_TIME      VARCHAR2(8) NOT NULL, END_TIME        VARCHAR2(8) NOT NULL, LOCATION        VARCHAR2(15) NOT NULL,   PRIMARY KEY (TEST_ID, STUDENT_ID)                               ) TABLESPACE TABLESPACE_FOR_WAIVERS                               ; 

And finally, the last table is STUDENT_ANSWERS_HISTORY:

 DROP TABLE STUDENT_ANSWER_HISTORY  CASCADE CONSTRAINTS; CREATE TABLE  STUDENT_ANSWER_HISTORY    (   STUDENT_ID     NUMBER(9) REFERENCES STUDENTS(STUDENT_ID),   TEST_ID        NUMBER(6) REFERENCES TEST_ID(TEST_ID),   QUESTION_ID    NUMBER(6) REFERENCES QUESTIONS,   STUDENT_ANSWER VARCHAR2(2) NOT NULL                                     ) TABLESPACE TABLESPACE_FOR_WAIVERS                                     ; 


Guerrilla Oracle
Guerrilla Oracle: The Succinct Windows Perspective
ISBN: 0201750775
EAN: 2147483647
Year: 2003
Pages: 84

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net