# 
# Type: mysql < deptempl.bat
# 
# Data files are separated by tab
#

create database demo;

use demo;


CREATE TABLE  City ( cityId  CHARACTER(3) NOT NULL, name CHARACTER(25), country CHARACTER(25),PRIMARY KEY ( cityId ) );

CREATE TABLE  Building ( buildingId  INTEGER NOT NULL, address CHARACTER(25), cityId CHARACTER(3), PRIMARY KEY ( buildingId ), FOREIGN KEY (cityId) REFERENCES City (cityId) );

CREATE TABLE  Dept ( deptId  INTEGER NOT NULL, description CHARACTER(25), buildingId INTEGER,PRIMARY KEY ( deptId ), FOREIGN KEY (buildingId) REFERENCES Building (buildingId) );

CREATE TABLE  Employee ( serialNo INTEGER NOT NULL, name  CHARACTER(50), worksIn INTEGER, PRIMARY KEY ( serialNo), FOREIGN KEY (worksIn) REFERENCES Dept (deptId) );



LOAD DATA LOCAL INFILE "city.txt" INTO TABLE City;

LOAD DATA LOCAL INFILE "building.txt" into TABLE Building;

LOAD DATA LOCAL INFILE "dept.txt" into TABLE Dept;

LOAD DATA LOCAL INFILE "employee.txt" into TABLE Employee;
