courses         dbo             user table            
 coursesoffered  dbo             user table            
 enroll_period   dbo             user table            
 enrollment      dbo             user table            
 faculty         dbo             user table            
 grades          dbo             user table            
 prereq          dbo             user table            
 recordnotes     dbo             user table            
 security        dbo             user table            
 students        dbo             user table            
 


create table courses
(courseno varchar(3) primary key,
name varchar(30) null,
description text null)



create table coursesoffered
(cid numeric(5,0) primary key,
courseno varchar(3) null,
sectiono varchar(3) null,
year varchar (4) null,
quarter (10) null,
url varchar(100) null,
daytime varchar(50) null,
room varchar (20) null,                                                       
building varchar (20) null,
fssn varchar(9) null
constraints fssn_fk foreign key fssn
references faculty(fssn)
constraints ccid_fk foreign key cid
references grades(cid))



create table grades
(ssn varchar(9) primary key,
cid numeric(5,0) primary key,
grades char(1) null,
gpa float null
constraints ssn_fk foreign key ssn
references student(ssn)
constraint cid_fk foreign key cid
references coursesoffered(cid))



create table students
(ssn varchar(9) primary key, 
fname varchar(20) null,           
lname varchar(20) null,           
midinit char(1) null,           
email varchar(50) null,           
address1 varchar(50) null,          
phone varchar(20) null,           
gender char(1) null,           
admit_qtr varchar(15) null,           
admit_year varchar(4) null,           
address2 varchar(50) null,           
city varchar(50) null,           
state varchar(2) null,           
zip varchar(10)
constraint essn_fk foreign key
references enrollment(ssn))           
	


create table faculty                                      
(fssn varchar(9) primary key,           
 fname varchar(20) null,
 lname varchar(20) null,           
 midinit char(1) null,           
 url varchar(50) null,           
 address1 varchar(50) null,           
 address2 varchar(50) null,           
 city varchar(50) null,           
 state char(2) null,           
 zip varchar(10) null,           
 phone varchar(20) null,           
 gender varchar(20) null,           
 specialty varchar(20) null,           
 dept varchar(30) null,          
 email varchar(50) null)         
	  

                       
create table enroll_period
(quarter varchar(10) not null,           
 year varchar(4) not null,           
 description varchar(50) null,          
 status varchar(10) null,
 primary key(quarter, year))           


	
create table security
(ssn varchar(9) primary key
 username varchar(20) null,           
 password varchar(20) null,           
 status varchar(20) null)           
     
	
create table recordnotes
(ssn varchar(9) not null,
fssn varchar(9) not null,
cid numeric(6) not null,
notes text,
primary key(ssn))


create table enrollment
(ssn varchar(9) not null,
cid numeric(6) not null,
status varchar(10),
primary key(ssn,cid)
constraint enssn_fk foreign key(ssn)
references students(ssn),
constraint encid_fk foreign key(cid)
references coursesoffered(cid))


create table prereq
(courseno varchar(3) not null,
precourseno varchar(3) not null,
primary key(courseno, precourseno))