Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ---------------------------------------------------------------
- -- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement