P06 - UniversityDB.sql

CREATE TABLE Subjects(
	[SubjectID] INT PRIMARY KEY IDENTITY NOT NULL,
	[SubjectName] NVARCHAR(60) NOT NULL
)
CREATE TABLE Majors(
	[MajorID] INT PRIMARY KEY IDENTITY NOT NULL,
	[Name] NVARCHAR(50) NOT NULL
)
CREATE TABLE Students(
	[StudentID] INT PRIMARY KEY IDENTITY NOT NULL,
	[StudentNumber] VARCHAR(10) NOT NULL,
	[StudentName] NVARCHAR(60) NOT NULL,
	[MajorID] INT FOREIGN KEY REFERENCES [Majors]([MajorID])
)
CREATE TABLE Payments(
	[PaymentID] INT PRIMARY KEY IDENTITY NOT NULL,
	[PaymentDate] DATE NOT NULL,
	[PaymentAmount] DECIMAL(6,2) NOT NULL,
	[StudentID] INT FOREIGN KEY REFERENCES [Students]([StudentID])
)
CREATE TABLE Agenda(
	[StudentID] INT FOREIGN KEY REFERENCES [Students]([StudentID]),
	[SubjectID] INT FOREIGN KEY REFERENCES [Subjects]([SubjectID]),
	PRIMARY KEY([StudentID],[SubjectID])
)