CIS4365: Database Applications
Fall, 2017

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

DESCRIBE patient;

// 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

DESCRIBE patient;

// Create the illness table.

CREATE TABLE illness
(   illcode CHAR(10),
    illname CHAR(30) NOT NULL,
    UNIQUE (illname) );

// View the structure of table illness

DESCRIBE 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

DESCRIBE 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

DESCRIBE treatment;