Notes

create database CISM

use CISM

create table Designation
(StudId int not null,
EmpId int not null);

create table StudentDetails
(StudId int not null primary key,
StudentName varchar(40),
DepartId int,
CourseId int);

create table EmployeeDetails
(EmpId int not null primary key,
EmployeeName varchar(40),
DepartId int);

create table StudentAttendance
(StudId int not null primary key,
MarkAttendance bit,
SumAttendance int,
TotalLeave int);

create table EmployeeAttendance
(EmpId int not null primary key,
MarkAttendance bit,
SumAttendance int,
TotalLeave int);

create table Department
(DeptId int not null primary key,
DeptName varchar(40),
CourseId int)

create table Course
(CourseID int not null primary key,
CourseName varchar(40),
CourseDuration int,
CourseFee float,
NewCourse varchar(50),
CourseDetails nvarchar,
SubjectId int)


create table Subject
(SubjectId int not null primary key,
SubjectName nvarchar,
SubjectFee float,
DateOfCommencement datetime,
SubjectMarks int,
SubjectPassMarks int)


create table Lib
(RegId int not null primary key,
IssueDate datetime,
ReturnDate datetime,
BookCode int not null unique, ---candidate key
BookStatus bit)

create table BookRegistration
(BookCode int not null primary key,
BookName varchar(40),
Author varchar(40),
Publication varchar(50),
Edition int)

----FK IN DESIGNATION TABLE
alter table Designation
add constraint DesigStud_FK foreign key (StudId) references StudentDetails(StudId);

alter table Designation
add constraint DesigEmp_FK foreign key (EmpId) references EmployeeDetails(EmpId);

alter table Designation
add constraint StudLib_FK foreign key (StudId) references Lib(RegId);

alter table Designation
add constraint EmpLib_FK foreign key (EmpId) references Lib(RegId);


----FK IN StudentDetails TABLE
alter table StudentDetails
add constraint Dept_FK foreign key (DepartId) references Department(DeptId);

alter table StudentDetails
add constraint Course_FK foreign key (CourseId) references Course(CourseID);

alter table StudentDetails
add constraint StudAttend_FK foreign key (StudId) references StudentAttendance(StudId);


----FK IN EmployeeDetails TABLE
alter table EmployeeDetails
add constraint DeptEmp_FK foreign key (DepartId) references Department(DeptID);

alter table EmployeeDetails
add constraint EmpAttend_FK foreign key (EmpId) references EmployeeAttendance(EmpId);

----FK in Department
alter table Department
add constraint CourseDept_FK foreign key (CourseId) references Course(CourseID);

----FK in Course
alter table Course
add constraint Subject_FK foreign key (SubjectId) references Subject(SubjectId);

---FK in BookRegistration
alter table BookRegistration
add constraint BookReg_FK foreign key (BookCode) references Lib(BookCode);