数据库建造及数据添加

本次数据库表的构造语句如下

CREATE TABLE UserRegister (
    uid int PRIMARY KEY IDENTITY(1,1),
    email varchar(50) UNIQUE,
    nick nvarchar(20) UNIQUE,
    password char(40),
    realname nvarchar(20),
    description nvarchar(128),
    download_credit int,
    created datetime,
);

create Table Class (
    cid int primary key identity(0,1),
    name nvarchar(20),
    description nvarchar(32),
   -- parent_id int references Class(cid)
    );
INSERT INTO Class(name, description) VALUES('ROOT', 'The root of all class');

create table Question (
    qid int primary key identity(1,1),
    uid int,
    cid int, 
    title nvarchar(64),
    content ntext,
    created datetime,
    views int, --0
    replies int, --0
    --foreign key (uid) references UserRegister (uid),
    --foreign key (cid) references Class(cid)
);

create Table Credit ( 
    uid int,
    cid int,
    value int default 0,
    primary key (uid, cid),
    --foreign key (uid) references UserRegister(uid),
    --foreign key (cid) references Class(cid)
);

create table Answer (
    aid int primary key identity(1,1),
    qid int,
    uid int,
    content ntext,
    created datetime,
    lastmodified datetime,
    credit int default 0,
    best bit,
   -- foreign key (qid) references Question(qid),
    --foreign key (uid) references UserRegister(uid)
);

create table Vote (
    aid int,
    uid int,
    up bit,
    primary key (aid, uid),
   -- foreign key (aid) references Answer(aid),
    --foreign key (uid) references UserRegister(uid)
);

create table Comment (
    comment_id int identity(1, 1) primary key,
    uid int,
    qid int,
    aid int,
    did int, -- resource id 
    content ntext,
    created datetime,
    lastmodified datetime,
   -- foreign key (uid) references UserRegister(uid),
   -- foreign key (qid) references Question(qid),
   -- foreign key (aid) references Answer(aid)
);

create table Document (
    did int primary key not null,
    cid smallint not null,
    uid int not null,
    title nvarchar(50) not null,
    type smallint not null, --0
    authorid int not null,--0
    instid int not null,--0
    postdatetime datetime not null,
    credit smallint not null,--0
    views int not null--random
);

create table DocumentVote (
    did int,
    uid int,
    up bit,
    primary key (did, uid),
   -- foreign key (did) references Document(did),
   -- foreign key (uid) references UserRegister(uid)
);

create table Tag(
    tid int primary key not null,
    name nchar(20) not null,
);

CREATE TABLE UserTagAssociation (
    uid int,
    tid int,
    PRIMARY KEY (uid, tid),
   -- FOREIGN KEY (uid) references UserRegister(uid),
   -- Foreign key (tid) references Tag(tid)
);

create table QuesttionTagAssociation (
    qid int,
    tid int,
    primary key (qid, tid),
   -- foreign key (qid) references Question(qid),
   -- foreign key (tid) references Tag(tid)
);

create table DocumentTagAssociation (
    did int,
    tid int,
    primary key (did, tid),
   -- foreign key (did) references Document(qid),
   -- foreign key (tid) references Tag(tid)
);

其中的表都是按照字面意思，例如Q/A，以及document.Tag是对问题及文献的分类。

部分表中测试数据如下

Credit表

在未更改密码前，“赠送”的测试账号密码一致。

Tag表

Question表