- Create School Database

Create Database School;

use School;

Create Table Department

(

DeptNo    char(4)    Primary Key          ,

DName    varchar(25) Unique  ,

Manager char(4)

);

Create Table Teacher

(

TeacherId   char(4)           Not Null,

Namevarchar(50)               Not Null,

Gender     Char(1)   Not Null Check ( Gender In('M','m','F','f') ) ,

Salary      FLOAT(7,2),

Rank        varchar(30),

HireDate Date,

City        varchar(30),

DeptWork char(4)   References Department(DeptNo)  ,

Primary Key (TeacherId),

Unique (Name)                            

);

ALTER Table Department

Add Foreign Key(Manager) References Teacher (TeacherID)

Create Table CollegeDegree

(

TeacherId       char(4)       Not Null ,

CollegeDegree  varchar(30)  Not Null ,

Foreign Key (TeacherId)References Teacher(TeacherId) ,

Primary Key(TeacherId , CollegeDegree)

);

- Student Table:

Create Table Student

(

StudNo char(10)       Not Null Primary Key,

FName  varchar(25)   Not Null,

LName  varchar(25)   Not Null,

BDate  Date,

Address   varchar(100),

Gender    Char(1) Not NullDefault 'M' ,

Phone     Char(12)                   ,

Check ( Gender In('M','m','F','f') )

);

- Subject Table:

Create Table Subject

(

SubCodechar(10) Not Null,

DeptBelong  char(4)Not Null   References Department(DeptNo) ,

TeacherTaught  char(4)References Teacher(TeacherId) ,

Title varchar(20)     Not Null,

Primary Key(SubCode , DeptBelong)

);

Create Table Study

(

StudNo     char(10) Not Null                                                                              ,

SubCode  char(10) Not Null                                            ,

DeptNo     char(4) Not Null                                           ,

Score      FLOAT(5,2)                                                 ,

Foreign Key (StudNo)  References Student(StudNo)                      ,

Foreign Key (SubCode , DeptNo) References  Subject ( SubCode ,DeptBelong)   ,

Primary Key (StudNo , SubCode , DeptNo )

);