use master
go
print 'Creating the "demo" database'
create database demo on default = 10
go
set dateformat mdy
go
use demo
go
begin
	create table City
	(cityId char(3) not null,
	name char(25),
	country char(25) not null,
	PRIMARY KEY(cityId))
end
go
grant select on City to public
go
execute sp_primarykey City, cityId
go
begin
	create table Building
	(buildingId int not null,
	address char(25),
	cityId char(3) not null,
	PRIMARY KEY(buildingId))
	/*FOREIGN KEY(cityId) REFERENCES City(cityId))*/
end
go
grant select on Building to public
go
execute sp_primarykey Building, buildingId
go
begin
	create table Dept
	(deptId int not null,
	description char(25),
	buildingId int,
	PRIMARY KEY(deptId))
	/* FOREIGN KEY(buildingId) REFERENCES Building(buildingId)) */
end
go
grant select on Dept to public
go
execute sp_primarykey Dept, deptId
go
begin
	create table Employee
	(serialNo int not null,
	name char(50),
	worksIn int not null,
	PRIMARY KEY(serialNo))
	/* FOREIGN KEY(worksIn) REFERENCES Dept(deptId)) */
end
go
grant select on Employee to public
go
execute sp_primarykey Employee, serialNo
go
execute sp_foreignkey Building, City, cityId
go
execute sp_foreignkey Dept, Building, buildingId
go
execute sp_foreignkey Employee, Dept, worksIn
go
insert Building
values(1, '1150 Eglinton Ave East', 'TOR')
go
insert Building
values(2, '895 Don Mills Road', 'DM')
go
insert Building
values(3, '3600 Steeles Ave East', 'MKM')
go
insert Building
values(4, '8200 Warden Ave', 'MKM')
go
insert City
values('TOR', 'Toronto', 'Canada')
go
insert City
values('DM', 'Don Mills', 'Canada')
go
insert City
values('MKM', 'Markham', 'Canada')
go
insert Dept
values(543, 'Architecture', 1)
go
insert Dept
values(123, 'XML Tools', 1)
go
insert Dept
values(478, 'VisualAge for Java', 2)
go
insert Dept
values(789, 'DB2 UDB', 3)
go
insert Employee
values(35678, 'Dave Streeter',   543)
go
insert Employee
values(23679, 'Harm Sluiman',    543)
go
insert Employee
values(43999, 'Christina Lau',   543)
go
insert Employee
values(40578, 'Frank Budinsky',  543)
go
insert Employee
values(45678, 'Mike Starkey',    543)
go
insert Employee
values(67890, 'Ed Merks',        123)
go
insert Employee
values(30290, 'Karen McCulloch', 123)
go
insert Employee
values(52302, 'David Lauzon',    123)
go
insert Employee
values(51197, 'Keith Chong',       123)
go
insert Employee
values(67889, 'Craig Salter',    123)
go
insert Employee
values(38930, 'Peter Spung',     478)
go
insert Employee
values(74902, 'Paul Buck',       478)
go
