-- MySQL specific script

-- Create a new database named 'college'
create database if not exists  college;
-- change to the currently created database
use college;
-- Create tables for college schema using the InnoDB Engine
--
-- Students table
create table student(
       id    int unsigned	not null,
       fname varchar(25) 	not null,
       lname varchar(25)	not null,
       primary key(id)
)engine=InnoDB;
--
-- Profesors table
create table professor(
       id    int unsigned	not null,
       fname varchar(25)	not null,
       lname varchar(25)	not null,
       primary key(id) 		
)engine=InnoDB;
--
-- Courses table
create table course(
       cprefix	char(4)		not null,
       cnum	int unsigned 	not null,
       cname	varchar(20)	not null,
       cdesc	varchar(100) 	not null,
       primary key(cprefix, cnum)
)engine=InnoDB;
--
-- Courses that students are enrolled
create table takes(
       sid	int unsigned		not null,
       cprefix	char(4)		not null,
       cnum	int unsigned		not null,
       sect	varchar(5) 		not null,
       semester varchar(10)	not null,
       year	int unsigned		not null,
       primary key(sid, cprefix, cnum, sect, semester, year),
       foreign key(sid) references student(id) on delete cascade,
       foreign key(cprefix, cnum) references course(cprefix, cnum) on delete cascade
)engine=InnoDB;
--
-- Which profesor is giving the course
create table teaches(
       pid	int unsigned		not null,
       cprefix char(4)		not null,
       cnum    int unsigned		not null,
       sect     varchar(5)		not null,
       semester varchar(10)	not null,
       year    int unsigned	not null,
       primary key(pid, cprefix, cnum, sect, semester, year),
       foreign key(pid) references professor(id) on delete cascade,
       foreign key(cprefix, cnum) references course(cprefix, cnum) on delete cascade
)engine=InnoDB;