--创建数据库

create database Information

go

--使用数据库

use Information

go

--创建表

create table Student

(

 Sno nvarchar(50) primary key not null,

 Sname nvarchar(50) not null,

 Ssex bit not null,

 Sbirthday datetime,

 Class nvarchar(50),

)

create table Course

(

    Cno nvarchar(50) primary key not null,

    Cname nvarchar(50) not null,

    Tno nvarchar(50) not null,

)

create table Score

(

    Sno nvarchar(50) not null,

    Cno nvarchar(50) not null,

    Degree decimal(4,1),

)

create table Teacher

(

    Tno nvarchar(50) primary key not null,

    Tname nvarchar(50) not null,

    Tsex bit not null,

    Tbirthday datetime,

    Prof nvarchar(50),

    Depart nvarchar(50) not null,

)

--填入数据  Student

insert into Student values('','曾华','','1977-09-01','')

insert into Student values('','匡明','','1975-10-02','')

insert into Student values('','王丽','','1976-01-23','')

insert into Student values('','李军','','1976-02-20','')

insert into Student values('','王芳','','1975-02-10','')

insert into Student values('','陆君','','1974-06-03','')

--填入数据  Course

insert into Course values('3-105','计算机导论','')

insert into Course values('3-245','操作系统','')

insert into Course values('6-166','数字电路','')

insert into Course values('9-888','高等数学','')

--填入数据  Score

insert into Score values('','3-245','')

insert into Score values('','3-245','')

insert into Score values('','3-245','')

insert into Score values('','3-105','')

insert into Score values('','3-105','')

insert into Score values('','3-105','')

insert into Score values('','3-105','')

insert into Score values('','3-105','')

insert into Score values('','3-105','')

insert into Score values('','6-166','')

insert into Score values('','6-166','')

insert into Score values('','6-166','')

--填入数据 Teacher

insert into Teacher values('','李诚','','1958-12-02','副教授','计算机系')

insert into Teacher values('','张旭','','1969-03-12','讲师','电子工程系')

insert into Teacher values('','王萍','','1972-05-05','助教','计算机系')

insert into Teacher values('','刘冰','','1977-08-14','助教','电子工程系')

--主外键关系

--如表A中的Ids是主键，要约束表B中的Aid列，那么语句应该是：

--alter table B add constraint A_B_Ids foreign key(Aid)  references A(Ids) 

--Student 中的Sno    约束      Score  中的  Sno

alter table Score add constraint Student_Score_Sno foreign key(Sno) references Student(Sno)

--Course 中的 Cno     约束     Score  中的  Cno

alter table Score add constraint Course_Score_Cno foreign key(Cno) references Course(Cno)

--Teacher 中的 Tno     约束     Course  中的   Tno

alter table Course add constraint Teacher_Course_Tno foreign key(Tno) references Teacher(Tno)