drop table if exists department;
drop table if exists course;
drop table if exists student;
drop table if exists transcript;

-------------------------------
-- DEFINITION OF UNIV. SCHEMA -   
-------------------------------
-- deptID represents the ID of a department
-- uName represents the name of the unviersity the dept belongs to
create table department( 
    did int not null, 
    dname char(50) not null);

-- cID represents the ID of the course
-- cName represents the name of the course
-- deptID represents the ID of the department offering the course
create table course ( 
    cid char(6) not null, 
    cname char(30) not null, 
    did int not null);

-- sID represents the ID of the student (the student number)
-- sName represents the name of the student
-- address represents the street address (number+street)
-- city represents the city where the student lives
-- dob represents the date of birth of the student
-- prgID represents the program the student is enrolled in
create table student ( 
    sid char(10) not null, 
    sname char(15) not null,
    dob date not null); 

-- sID represents the ID of a student
-- taking a course cID
-- in a semester sem ('w' stands for winter, 's' for summer, 'f' for fall)
-- in a year
-- and the grade it obtained (out of 100)
-- the fact that there is a grade for a student and a course, it means
-- that the student TOOK that course
create table transcript ( 
    sid char(10) not null, 
    cid char(6) not null, 
    sem char(10) not null, 
    year int not null, 
    grade int not null);

-------------------------------
-- SAMPLE DATA IN THE UNIV. DB-   
-------------------------------
--inserts into DEPARTMENT
insert into department values(100,'Mechanical and Industrial Engineering');
insert into department values(101,'Computer Science');
insert into department values(102,'Mathematics');
insert into department values(103,'Biology');
insert into department values(104,'Electrical Engineering');

--inserts into COURSE
insert into course values('MIE100','Dynamics',100);
insert into course values('MIE453','Bioinformatics Systems',100);
insert into course values('CSC180','Intro. to Programing',101);
insert into course values('CSC384','Intro. to AI',101);
insert into course values('MAT250','Linear Programing',102);
insert into course values('BIO100','Intro. to Biology',103);
insert into course values('ECE190','Introductory Electronics',104);
insert into course values('ECE241','Digital Systems',104);
insert into course values('ECE243','Comp Organization',104);

--inserts into STUDENT
insert into student values('01','Homer','1985-01-11');
insert into student values('02','Mike','1986-02-23');
insert into student values('03','Peter','1985-05-01');
insert into student values('04','Marge','1985-06-12');
insert into student values('05','Bart','1985-06-24');
insert into student values('06','Lisa','1986-09-04');
insert into student values('07','Magie','1986-12-03');
insert into student values('08','Rachel','1986-08-24');
insert into student values('09','Ross','1986-06-05');
insert into student values('10','Monica','1985-10-06');
insert into student values('11','Joe','1986-09-26');
insert into student values('12','Richard','1985-01-23');
insert into student values('13','Rose','1986-10-11');
insert into student values('14','David','1986-07-30');
insert into student values('15','Ken','1985-02-18');
insert into student values('16','Albert','1987-04-08');


--inserts into TRANSCRIPT
insert into transcript values('01','MIE100','fall',2003,90);
insert into transcript values('01','MIE453','fall',2005,78);
insert into transcript values('01','CSC180','winter',2004,82);
insert into transcript values('01','BIO100','fall',2003,95);

insert into transcript values('02','MIE100','fall',2003,78);
insert into transcript values('02','MIE453','fall',2005,60);
insert into transcript values('02','MAT250','summer',2004,82);
insert into transcript values('02','ECE241','winter',2003,90);
insert into transcript values('02','ECE243','fall',2004,92);

insert into transcript values('03','MIE100','winter',2003,89);
insert into transcript values('03','CSC180','fall',2003,79);
insert into transcript values('03','CSC384','summer',2004,59);
insert into transcript values('03','CSC384','fall',2004,70);
insert into transcript values('03','ECE190','fall',2003,82);

insert into transcript values('04','MIE100','winter',2004,82);
insert into transcript values('04','ECE190','winter',2004,89);
insert into transcript values('04','BIO100','winter',2004,69);
insert into transcript values('04','MAT250','winter',2004,70);
insert into transcript values('04','ECE243','fall',2004,82);
insert into transcript values('04','ECE241','fall',2005,94);

insert into transcript values('05','MIE100','winter',2004,52);
insert into transcript values('05','ECE190','winter',2004,49);
insert into transcript values('05','BIO100','winter',2004,59);