--part 1

--To create new database we use CREATE 
--CREATE DATABASE DATABASE_NAME
CREATE DATABASE StudentsDB
USE StudentsDB

--CREATE STUDENTS INFO TABLE
CREATE TABLE StudentsInfo
(
	studentID int identity(1,1) primary key,
	student_FN varchar(50),
	student_LN varchar(50),
	class varchar(20),
	DOB datetime,
	address varchar(50),
	telephone varchar(50)
)
create table Courses
(
	courseId int identity(1,1) primary key,
	courseName varchar(50),
	room varchar(50),
	roomCode varchar(50),
	capacity int,
	AKTS int
)
CREATE TABLE Teachers
(
	teacherId int identity(1,1) primary key,
	teacherName varchar(50),
	fileNo varchar(20)
)
CREATE TABLE Departments
(
	deptId	int identity(1,1) primary key,
	deptName varchar(50),
	deptCode varchar(50),
	deptHead varchar(50)
)



--part 2



Alter table Courses
Add deptID_fk int

Alter table Courses
Add constraint cs_fk Foreign Key(deptID_fk) 
references Departments (deptId)


Alter table StudentsInfo
Add deptID_fk int

Alter table StudentsInfo
Add constraint st_fk Foreign Key(deptID_fk) 
references Departments (deptId)

Alter table Teachers
Add deptID_fk int

Alter table Teachers
Add constraint TCH_fk Foreign Key(deptID_fk) 
references Departments (deptId)


Create table Class(
classID int Primary key,
courseID int,
studentID int,
teacherID int,
room nvarchar(10),
classTime nvarchar(10),
constraint course_fk foreign Key(courseID) 
references Courses(courseId),
constraint std_fk foreign key (StudentID)
references StudentsInfo(studentID),
constraint tchr_fk foreign key (teacherID)
references Teachers(teacherId)	
)