create database Education

create table CLG
(	 clg# int not null IDENTITY(1,1) primary key,
	clgname nvarchar(20)  null,
	city nvarchar(20)  null default 'Semnan',
	p# int null );

create table [STUD]
( 	 s# int not null IDENTITY(1,1) primary key,
	sname nvarchar(20)  null,
	city nvarchar(20)  null,
	[avg] float null,
	clg# int not null references CLG(clg#),
	CHECK ([avg]>=0 AND [avg]<=20)   );

create table [PROF]
( 	 p# int not null IDENTITY(1,1) primary key,
	pname nvarchar(20)  null,
	grade nvarchar(20)  null,
	clg# int not null references CLG(clg#),
	CHECK (grade IN (N'مربی', N'استادیار', N'دانشیار', N'استاد کامل'))    );

create table [CRS]
( 	 c# int not null IDENTITY(1,1) primary key,
	cname nvarchar(20)  null,
	unit int  null,
	clg# int not null references CLG(clg#),
	CHECK (unit>=1 AND unit<=4)    );

create table [REG]
( 	term int not null,
	score float null,
	s# int not null references STUD(s#),
	c# int not null references CRS(c#),
	p# int null references PROF(p#),
	primary key(s#,c#,term),	
	CHECK (score>=0 AND score<=20));
alter table [CLG]
add constraint clg_prof foreign key (p#) references  PROF(p#)