CREATE DATABASE [StudentsDB]
GO

use [StudentsDB]
GO

CREATE TABLE [dbo].[Students] (
	[sid] [int],
	[sname] [char] (100),
	[level] [char] (100),
	[age] [real],
	PRIMARY KEY (sid) 
) 
GO

CREATE TABLE [dbo].[Faculty] (
	[fid] [int],
	[fname] [char] (100),
	PRIMARY KEY (fid) 
) 
GO


CREATE TABLE [dbo].[Class] (
	[cid] [int],
	[topic] [char] (100),
	[room]  [char] (100),
	[fid]   [int],
	PRIMARY KEY (cid) 
) 
GO


CREATE TABLE [dbo].[Enrolled] (
	[sid] [int],
	[cid] [int],
	FOREIGN KEY (sid) REFERENCES Students,
	FOREIGN KEY (cid) REFERENCES Class
) 
GO


INSERT INTO Students
VALUES (10,'John', 'JR', 18)
GO

INSERT INTO Students
VALUES (11,'George', 'JR', 18)
GO


INSERT INTO Students
VALUES (13,'Andy', 'JR', 25)
GO


INSERT INTO Students
VALUES (12,'Ken', 'M', 19)
GO


INSERT INTO Students
VALUES (14,'Paul', 'M', 30)
GO


INSERT INTO Students
VALUES (15,'Chris', 'AD', 25)
GO

INSERT INTO Students
VALUES (16,'Andy', 'AD', 16)
GO



INSERT INTO Faculty
VALUES (100, 'Stuart')
GO


INSERT INTO Faculty
VALUES (101, 'Walter')
GO


INSERT INTO Faculty
VALUES (102, 'Max')
GO

INSERT INTO Faculty
VALUES (103, 'Christopher')
GO






INSERT INTO Class
VALUES (1,'Maths', 'R1', 100)
GO

INSERT INTO Class
VALUES (2,'Maths', 'R2', 100)
GO

INSERT INTO Class
VALUES (3,'History', 'R4', 101)
GO


INSERT INTO Class
VALUES (4,'Physics', 'R3', 102)
GO

INSERT INTO Class
VALUES (5,'Chemistry', 'R2', 103)
GO

INSERT INTO Class
VALUES (6,'Chemistry', 'R4', 102)
GO





INSERT INTO Enrolled
VALUES (10,1)
GO

INSERT INTO Enrolled
VALUES (10,3)
GO


INSERT INTO Enrolled
VALUES (10,4)
GO


INSERT INTO Enrolled
VALUES (11,1)
GO

INSERT INTO Enrolled
VALUES (11,5)
GO


--INSERT INTO Enrolled
--VALUES (13,2)
--GO

INSERT INTO Enrolled
VALUES (13,4)
GO


INSERT INTO Enrolled
VALUES (14,1)
GO

INSERT INTO Enrolled
VALUES (14,3)
GO

INSERT INTO Enrolled
VALUES (15,3)
GO


INSERT INTO Enrolled
VALUES (15,4)
GO

INSERT INTO Enrolled
VALUES (15,5)
GO



INSERT INTO Enrolled
VALUES (16,3)
GO


--INSERT INTO Enrolled
--VALUES (16,4)
--GO

INSERT INTO Enrolled
VALUES (16,5)
GO