DB1.sql

CREATE TABLE [Teachers] (
	[Id] INT NOT NULL,
	[Teacher] NVARCHAR(255),
	PRIMARY KEY([Id])
);
GO

CREATE TABLE [Subjects] (
	[Id] INT NOT NULL,
	[Subject] NVARCHAR(255),
	PRIMARY KEY([Id])
);
GO

CREATE TABLE [Students] (
	[Id] INT NOT NULL,
	[Student] NVARCHAR(255),
	PRIMARY KEY([Id])
);
GO

CREATE TABLE [StudentSubject] (
	[StudentId] INT,
	[SubjectId] INT,
);
GO

CREATE TABLE [TeacherSubject] (
	[TeacherId] INT NOT NULL UNIQUE,
	[SubjectId] INT NOT NULL UNIQUE,
);
GO

ALTER TABLE [StudentSubject]
ADD FOREIGN KEY([StudentId]) REFERENCES [Students]([Id])
ON UPDATE NO ACTION ON DELETE NO ACTION;
GO
ALTER TABLE [TeacherSubject]
ADD FOREIGN KEY([TeacherId]) REFERENCES [Teachers]([Id])
ON UPDATE NO ACTION ON DELETE NO ACTION;
GO
ALTER TABLE [StudentSubject]
ADD FOREIGN KEY([SubjectId]) REFERENCES [Subjects]([Id])
ON UPDATE NO ACTION ON DELETE NO ACTION;
GO
ALTER TABLE [TeacherSubject]
ADD FOREIGN KEY([SubjectId]) REFERENCES [Subjects]([Id])
ON UPDATE NO ACTION ON DELETE NO ACTION;
GO


INSERT INTO Teachers (Id,Teacher) VALUES ('01','Arkady Viktorovich')
INSERT INTO Teachers (Id,Teacher) VALUES ('02','Arestarkh Gennadievich')
INSERT INTO Teachers (Id,Teacher) VALUES ('03','Lyudmila Petrovna')
INSERT INTO Teachers (Id,Teacher) VALUES ('04','Marya Ivanovna')
INSERT INTO Teachers (Id,Teacher) VALUES ('05','Florina Lvovna')
SELECT * FROM Teachers

INSERT INTO Subjects (Id,Subject) VALUES ('20','Physics')
INSERT INTO Subjects (Id,Subject) VALUES ('21','Russian')
INSERT INTO Subjects (Id,Subject) VALUES ('22','History')
INSERT INTO Subjects (Id,Subject) VALUES ('23','Algebra')
INSERT INTO Subjects (Id,Subject) VALUES ('24','Biology')
SELECT * FROM Subjects

INSERT INTO Students (Id,Student) VALUES ('10',N'Раиса')
INSERT INTO Students (Id,Student) VALUES ('11',N'Иннокентий')
INSERT INTO Students (Id,Student) VALUES ('12',N'Аркадий')
INSERT INTO Students (Id,Student) VALUES ('13',N'Зинаида')
INSERT INTO Students (Id,Student) VALUES ('14',N'Валера')
SELECT * FROM Students

INSERT INTO TeacherSubject (TeacherId,SubjectId) VALUES ('01','20')
INSERT INTO TeacherSubject (TeacherId,SubjectId) VALUES ('02','22')
INSERT INTO TeacherSubject (TeacherId,SubjectId) VALUES ('03','21')
INSERT INTO TeacherSubject (TeacherId,SubjectId) VALUES ('04','23')
INSERT INTO TeacherSubject (TeacherId,SubjectId) VALUES ('05','24')
SELECT * FROM TeacherSubject

INSERT INTO StudentSubject (StudentId,SubjectId) VALUES ('10','20')
INSERT INTO StudentSubject (StudentId,SubjectId) VALUES ('10','21')
INSERT INTO StudentSubject (StudentId,SubjectId) VALUES ('11','21')
INSERT INTO StudentSubject (StudentId,SubjectId) VALUES ('11','22')
INSERT INTO StudentSubject (StudentId,SubjectId) VALUES ('11','23')
INSERT INTO StudentSubject (StudentId,SubjectId) VALUES ('11','24')
INSERT INTO StudentSubject (StudentId,SubjectId) VALUES ('12','23')
INSERT INTO StudentSubject (StudentId,SubjectId) VALUES ('13','21')
INSERT INTO StudentSubject (StudentId,SubjectId) VALUES ('14','24')
SELECT * FROM StudentSubject


--DROP TABLE [dbo].[TeacherSubject];
--DROP TABLE [dbo].[StudentSubject];
--DROP TABLE [dbo].[Students];
--DROP TABLE [dbo].[Teachers];
--DROP TABLE [dbo].[Subjects];