12.a.    Write the script that would create the tables.

 

CREATE SCHEMA  

AUTHORIZATION yourname;    

 

CREATE TABLE department       

(  dept_ID      CHAR (9),  

   dept_name    CHAR (20)  NOT NULL,

   PRIMARY KEY (dept_ID)); 

 

CREATE TABLE professors    

(  prof_ID      CHAR (8),          

   prof_name    CHAR (20)  NOT NULL,

   PRIMARY KEY (prof_ID),

   FOREIGN KEY (dept_ID) REFERENCES department (dept_ID));

                                                          

CREATE TABLE courses           

(  course_ID    CHAR (8),  

   course_name  CHAR (20)   NOT NULL,       

   PRIMARY KEY (course_ID));

 

CREATE TABLE teach

(  prof_ID      CHAR (8),  

   course_ID    CHAR (8),

   PRIMARY KEY (prof_ID, course_ID),      

   FOREIGN KEY (course_ID) REFERENCES courses (course_ID) );

   FOREIGN KEY (prof_ID) REFERENCES professors (prof_ID),

   FOREIGN KEY (course_ID) REFERENCES courses (course_ID) );

 

CREATE TABLE classes  

( class_ID     CHAR (9), 

  course_ID    CHAR (8)    NOT NULL,

  semester     CHAR (6)    NOT NULL,

  PRIMARY KEY (class_ID),         

   FOREIGN KEY (course_ID) REFERENCES courses (course_ID) );