--
-- 	Database Table Creation
--
--		This file will create the tables for use with the book 
--  Database Management Systems by Raghu Ramakrishnan and Johannes Gehrke.
--  It is run automatically by the installation script.
--
--	Version 0.1.0.0 2002/04/05 by: David Warden.
--	Copyright (C) 2002 McGraw-Hill Companies Inc. All Rights Reserved.
--
--  First drop any existing tables. Any errors are ignored.
--
drop table if exists enrolled ;
drop table if exists class ;
drop table if exists faculty ;
drop table if exists student;
--
-- Now, add each table.
--
create table student(
	snum DECIMAL(9,0) primary key,
	sname VARCHAR(30),
	major VARCHAR(25),
	standing VARCHAR(2),
	age DECIMAL(3,0)
	) ENGINE=InnoDB;
create table faculty(
	fid DECIMAL(9,0) primary key,
	fname VARCHAR(30),
	deptid DECIMAL(2,0)
	)ENGINE=InnoDB;
create table class(
	name VARCHAR(40) primary key,
	meets_at VARCHAR(20),
	room VARCHAR(10),
	fid DECIMAL(9,0),
	foreign key(fid) references faculty(fid)
	)ENGINE=InnoDB;
create table enrolled(
	snum DECIMAL(9,0),
	cname VARCHAR(40),
	primary key(snum,cname),
	foreign key(snum) references student(snum),
	foreign key(cname) references class(name)
	)ENGINE=InnoDB;

LOAD DATA LOCAL INFILE 'student.txt' INTO TABLE student FIELDS TERMINATED BY ',' ;
LOAD DATA LOCAL INFILE 'faculty.txt' INTO TABLE faculty FIELDS TERMINATED BY ',' ;
LOAD DATA LOCAL INFILE 'class.txt' INTO TABLE class FIELDS TERMINATED BY ',' ;
source enrolled.sql;