Database
Creation Commands
Return to Home Page
Type in the commands
below into a text file using a text editor (such as Notepad). You need NOT type in the comments on the right hand side (those preceded
with //). These are comments which describe what each command does.
NOTE: The commands in CAPS indicate
reserved words; You do not have to Capitalize. You do NOT have to enter the
comments, although I would suggest you do (for readability). HOWEVER, you MUST
start your program (script) with one comment: YOUR NAME
VERY IMPORTANT
NOTE:
I have intentionally omitted a few
statements, and made some mistakes. They are not major errors. Nothing that
you couldn't (and will not) figure out.
* * *
* * * * *
// Place your real name
and SSN here
CREATE SCHEMA |
|
AUTHORIZATION ______; |
//ç
enter YOUR Username
|
// The following DROP commands are
included because it is assumed that your program will
// will NOT work the first time out, and that you will have to recreate your
tables (which means
// (which means that you need to drop them first).
// Notice also that the ORDER IN WHICH THE TABLES ARE
// DROPPED is also important. Make sure you know why.
DROP TABLE treatment;
|
// |
DROP TABLE prescription;
|
//commands are
included |
DROP TABLE illness;
|
//because it is
assumed that |
DROP TABLE patient;
|
//your script will not
work |
DROP TABLE physician; |
// the first time out |
// Create the Physician table. Don't I
need to specify that field physid should be
// NOT NULL?
CREATE
TABLE physician |
|
|
(
|
CHAR(9), |
|
physname |
CHAR(30) |
NOT NULL, |
specialty |
CHAR(15) |
NOT NULL, |
street |
CHAR(20), |
|
city |
CHAR(20) |
DEFAULT
'Arlington', |
state |
CHAR(2) |
DEFAULT
'TX', |
zip |
CHAR(5), |
|
PRIMARY KEY |
(physid), |
|
FOREIGN KEY (supervisor) |
REFERENCES |
physician
(physid), |
UNIQUE (specialty) ); |
|
|
// View the structure of table
physician
// Create the patient table.
CREATE
TABLE patient |
|
|
(
patid |
CHAR(9), |
|
name |
CHAR(30) |
NOT NULL, |
street |
CHAR(20), |
|
city |
CHAR(20), |
|
state |
CHAR(2) |
DEFAULT
'TX', |
zip |
CHAR(5), |
|
physid |
CHAR(6), |
|
PRIMARY KEY |
(patid), |
|
FOREIGN KEY (physid) |
REFERENCES |
physician
(physid) ); |
// View the structure of table patient
// Create the illness table.
CREATE TABLE illness |
|
|
(
illcode |
CHAR(10), |
|
illname |
CHAR(30) |
NOT NULL, |
UNIQUE (illname) ); |
|
|
// View the structure of table illness
// Create the prescription table.
CREATE TABLE
prescription |
|
|
(
drugcode |
CHAR(9), |
|
drugname |
CHAR(15) |
NOT NULL, |
PRIMARY KEY (drugcode) ); |
|
|
// View the structure of table
prescription
// Create the treatment table.
CREATE TABLE treatment |
|
|
(
patid |
CHAR(9), |
|
illcode |
CHAR(10), |
|
drugcode |
CHAR(5), |
|
treatdate |
DATE |
NOT NULL, |
PRIMARY KEY (patid, illcode, drugcode, treatdate), |
FOREIGN KEY (patid) REFERENCES patient (patid), |
FOREIGN KEY (drugcode) REFERENCES prescription (drugcode) ); |
// View the structure of table
treatment
|