关于创建知识共享系统数据库的一些操作

创建sharing数据库

create database sharing
on primary
(
   name = 'sharing',
   filename = 'E:\SQL Server\sharing.mdf',
   size = 10mb,
   filegrowth = 5mb
) 
log on 
(
  name = 'sharing_log',
  filename = 'E:\SQL Server\Class.ldf',
  size = 5mb,
  filegrowth = 10% 
)

创建用户表

create table UserInfo
(
    UserName char(11) not null primary key,
	PassWord varchar(32) not null,
	UserGender bit default(0),
	UserEmail varchar(20),
	UserPhone char(11),
	UserInterest  nvarchar(30),
	UserMoney varchar(10)
)

创建基础课程表和高级课程表

create table BasicCourse
(
   BasicId  int not null primary key identity(1,1),
   CourseName  nvarchar(10)
)
create table SeniorCourse
(
   SeniorId  int not null primary key identity(1,1),
   SeniorName  nvarchar(10)
)

创建初级与高级课程练习表

create table BasicExercise
(
    UserName char(11) not null primary key,
	foreign key ( UserName) references UserInfo(UserName),
	WrongId  int not null,  
	foreign key (WrongId) references BasicCourse(BasicId),
	Basicnumber  int
)
create table SeniorExercise
(
    UserName char(11) not null primary key,
	foreign key ( UserName) references UserInfo(UserName),
	WrongId  int not null,  
	foreign key (WrongId) references SeniorCourse(SeniorId),
	Seniornumber  int
)

创建天梯系统

create table Ladder
(
     UserName char(11) not null primary key,
	 foreign key ( UserName) references UserInfo(UserName),
     score   int 
)

创建一些具体的科目

create table Clanguage
(
    ClangId  int not null primary key identity(1,1),
	CQuestion nvarchar(50),
	CAnswer   nvarchar(20),
	BasicId int not null,
    foreign key(BasicId) references BasicCourse(BasicId)
)
create table DataBaseSystem
(
    DataId  int not null primary key identity(1,1),
	DataQuestion nvarchar(50),
	DataAnswer   nvarchar(20),
	BasicId int not null,
    foreign key(BasicId) references BasicCourse(BasicId)
)
create table Java
(
    JavaId  int not null primary key identity(1,1),
	JavaQuestion nvarchar(50),
	JavaAnswer   nvarchar(20),
	BasicId int not null,
    foreign key(BasicId) references BasicCourse(BasicId)
)
create table Web
(
    WeblangId  int not null primary key identity(1,1),
	WebQuestion nvarchar(50),
	WebAnswer   nvarchar(20),
	BasicId int not null,
    foreign key(BasicId) references BasicCourse(BasicId)
)
create table Python
(
    PythonId  int not null primary key identity(1,1),
	PythonQuestion nvarchar(50),
	PythonAnswer   nvarchar(20),
	BasicId int not null,
    foreign key(BasicId) references BasicCourse(BasicId)
)
create table Programmer
(
    ProgrammerId  int not null primary key identity(1,1),
	ProgrammerQuestion nvarchar(50),
	ProgrammerAnswer   nvarchar(20),
	BasicId int not null,
    foreign key(BasicId) references BasicCourse(BasicId)
)

具体的E-R图