SQL DATABASE tinman.cs.gsu.edu #RoniBombardero

---------------------------------------------------------------
-- Grade Book Database; Create Tables Script
-- by RONI BOMBARDERO
-- SQL DATA
---------------------------------------------------------------
drop table catalog cascade constraints;
create table catalog (
  cno       varchar2(7) not null,
  ctitle    varchar2(50),
  authority varchar2(15) not NULL,
  primary key (cno));

drop table students cascade constraints;
create table students (
  sid       varchar2(9) not null,
  fname     varchar2(20),
  lname     varchar2(20) not null,
  minit     varchar2(2),
  authority varchar2(15),
  primary key (sid,authority));

drop table courses cascade constraints;
create table courses (
  term      varchar2(10) not null,
  lineno    number(4) not null,
  cno       varchar2(7) not null,
  a         number(2) check(a > 0),
  b         number(2) check(b > 0),
  c         number(2) check(c > 0),
  d         number(2) check(d > 0),
  authority varchar2(15) not NULL,
  primary key (term,lineno),
  foreign key (cno) references catalog);

drop table components cascade constraints;
create table components (
  term      varchar2(10) not null,
  lineno    number(4) not null check(lineno >= 1000),
  compname  varchar2(15) not null,
  maxpoints number(4) check(maxpoints >= 0),
  weight    number(2) check(weight>=0),
  authority varchar2(15) not NULL,
  primary key (term,lineno,compname),
  foreign key (term,lineno) references courses);

drop table enrolls cascade constraints;
create table enrolls (
  sid       varchar2(9) not null,
  term      varchar2(10) not null,
  lineno    number(4) not null,
  authority varchar2(15) not NULL,
  primary key (sid,term,lineno),
  foreign key (sid,authority) references students,
  foreign key (term,lineno) references courses);

drop table scores cascade constraints;
create table scores (
  sid       varchar2(9) not null,
  term      varchar2(10) not null,
  lineno    number(4) not null,
  compname  varchar2(15) not null,
  points    number(4) check(points >= 0),
  authority varchar2(15) not NULL,
  primary key (sid,term,lineno,compname),
  foreign key (sid,term,lineno) references enrolls,
  foreign key (term,lineno,compname) references components);

drop table whoami cascade constraints;
create table whoami (
   sid        varchar(10),
   userid     varchar2(15),
   password   varchar2(15),
   lastaccess date,
   u_access   varchar(30),
   authority  varchar2(15),
   primary key (sid) );

insert into whoami values(1111,'raj','r123',null,null,'Teacher');


drop table teachers cascade constraints;
create table teachers (
   sid 		varchar2(9) not NULL,
   fname	varchar2(20),
   lname	varchar2(20) not NULL,
   minit	char,
   primary key (sid));

insert into teachers values ('1111','Raj','Sunderraman',NULL);

---------------------------------------------------------------
-- Grade Book Database: Insert Rows
-- Chapter 2; Oracle Programming -- A Primer
--            by R. Sunderraman
---------------------------------------------------------------
insert into catalog values
  ('csc226','Introduction to Programming I','teacher');
insert into catalog values
  ('csc227','Introduction to Programming II','teacher');
insert into catalog values
  ('csc343','Assembly Programming','teacher');
insert into catalog values
  ('csc481','Automata and Formal Languages','teacher');
insert into catalog values
  ('csc498','Introduction to Database Systems','teacher');
insert into catalog values
  ('csc880','Deductive Databases and Logic Programming','teacher');

insert into students values
  ('1111','Nandita','Rajshekhar','K','teacher');
insert into students values
  ('2222','Sydney','Corn','A','teacher');
insert into students values
  ('3333','Susan','Williams','B','teacher');
insert into students values
  ('4444','Naveen','Rajshekhar','B','teacher');
insert into students values
  ('5555','Elad','Yam','G','teacher');
insert into students values
  ('6666','Lincoln','Herring','F','teacher');
insert into students values
  ('7777','Louis','Toyama','F','teacher');
insert into students values
  ('9999','Hung','Nguyen','Q.','teacher');

insert into courses values
  ('f96',1031,'csc226',90,80,65,50,'teacher');
insert into courses values
  ('f96',1032,'csc226',90,80,65,50,'teacher');
insert into courses values
  ('sp97',1031,'csc227',90,80,65,50,'teacher');

insert into components values
  ('f96',1031,'exam1',100,30,'teacher');
insert into components values
  ('f96',1031,'quizzes',80,20,'teacher');
insert into components values
  ('f96',1031,'final',100,50,'teacher');
insert into components values
  ('f96',1032,'programs',400,40,'teacher');
insert into components values
  ('f96',1032,'midterm',100,20,'teacher');
insert into components values
  ('f96',1032,'final',100,40,'teacher');
insert into components values
  ('sp97',1031,'paper',100,50,'teacher');
insert into components values
  ('sp97',1031,'project',100,50,'teacher');

insert into enrolls values
  ('1111','f96',1031,'teacher');
insert into enrolls values
  ('2222','f96',1031,'teacher');
insert into enrolls values
  ('4444','f96',1031,'teacher');
insert into enrolls values
  ('1111','f96',1032,'teacher');
insert into enrolls values
  ('2222','f96',1032,'teacher');
insert into enrolls values
  ('3333','f96',1032,'teacher');
insert into enrolls values
  ('5555','sp97',1031,'teacher');
insert into enrolls values
  ('6666','sp97',1031,'teacher');
insert into enrolls values
  ('7777','sp97',1031,'teacher');

insert into scores values
  ('1111','f96',1031,'exam1',90,'teacher');
insert into scores values
  ('1111','f96',1031,'quizzes',75,'teacher');
insert into scores values
  ('1111','f96',1031,'final',95,'teacher');
insert into scores values
  ('2222','f96',1031,'exam1',70,'teacher');
insert into scores values
  ('2222','f96',1031,'quizzes',40,'teacher');
insert into scores values
  ('2222','f96',1031,'final',82,'teacher');
insert into scores values
  ('4444','f96',1031,'quizzes',71,'teacher');
insert into scores values
  ('4444','f96',1031,'final',74,'teacher');
insert into scores values
  ('1111','f96',1032,'programs',400,'teacher');
insert into scores values
  ('1111','f96',1032,'midterm',95,'teacher');
insert into scores values
  ('1111','f96',1032,'final',99,'teacher');
insert into scores values
  ('2222','f96',1032,'programs',340,'teacher');
insert into scores values
  ('2222','f96',1032,'midterm',65,'teacher');
insert into scores values
  ('2222','f96',1032,'final',95,'teacher');
insert into scores values
  ('3333','f96',1032,'programs',380,'teacher');
insert into scores values
  ('3333','f96',1032,'midterm',75,'teacher');
insert into scores values
  ('3333','f96',1032,'final',88,'teacher');
insert into scores values
  ('5555','sp97',1031,'paper',80,'teacher');
insert into scores values
  ('5555','sp97',1031,'project',90,'teacher');
insert into scores values
  ('6666','sp97',1031,'paper',80,'teacher');
insert into scores values
  ('6666','sp97',1031,'project',85,'teacher');
commit;