二、数据库用户
1.创建一个名为DAVID的用户,口令为davidpass,并授予其连接数据库和创建表对象的权限。同时授予其访问另一用户MARTIN的表TEST的权限.
[注]作业要求
(1)作业以sql脚本的方式上交,形式为班级名+学生姓名+章节号,如:“35张三5.sql”.
(2)对于题目中未给出的数据对象结构及数据,要创建数据对象并给出测试数据,保证代码可直接执行.
三、创建数据表
1.创建如下数据库表 bbsUsers、bbsTopic、bbsReply、bbsSection
表 | 表 名 | 作 用 | 备 注 |
用户表 | bbsUsers | 存储注册的用户信息 | 用户注册后才能发贴和回贴 |
主贴表 | bbsTopic | 存储主贴的内容 | 因主贴和跟贴有些内容不同,因此分为两张表 |
跟贴表 | bbsReply | 存储跟贴的内容 | |
版块表 | bbsSection | 存储版块的信息 | 包括版块名、版主等信息 |
各表的结构如下:
表 名 | bbsSection (版块表) | 主 键 | SID | |
列 名 | 数据类型 | 长度 | 是否允许为空 | 描 述 |
SID | int | 4 | 不允许 | 版块编号,自动增长 |
Sname | varchar2 | 32 | 不允许 | 版块名称 |
SmasterID | int | 4 | 不允许 | 版主的用户ID,外键;引用用户表bbsUsers的UID |
Sprofile | varchar2 | 20 | 允许 | 版面简介 |
SclickCount | int | 4 | 允许 | 点击率 |
StopicCount | int | 4 | 允许 | 发贴数 |
表名 | bbsReply(回贴表) | 主键 | RID | ||
列名 | 数据类型 | 长度 | 是否允许为空 | 默认值 | 描述 |
RID | int | 4 | 不允许 |
| 自动编号, 贴子编号 |
RtID | int | 4 | 不允许 |
| 主贴ID;外键,引用bbsTopic表的主键TID |
RsID | int | 4 | 不允许 |
| 版块ID;外键,引用bbsSection表的主键SID |
RuID | int | 4 | 不允许 |
| 回贴人ID, 外键,引用bbsUsers表的主键UID |
Rface | int | 4 | 允许 |
| 回贴表情 |
Rcontents | varchar2 | 30 | 不允许 |
| 正文,必须大于6个字符 |
Rtime | date | 8 | 允许 | 当天 | 回贴时间 |
RclickCount | int | 4 | 允许 |
| 点击数 |
表 名 | bbsTopic(主贴表) | 主 键 | TID | ||
列 名 | 数据类型 | 长 度 | 是否允许为空 | 默认值 | 描 述 |
TID | int | 4 | 不允许 |
| 贴子编号,自动增长 |
TsID | int | 4 | 不允许 |
| 版块编号;外键,引用bbsSection表的主键SID |
TuID | int | 4 | 不允许 |
| 发贴人ID;外键,引用bbsUsers表的主键UID |
TreplyCount | int | 4 | 允许 |
| 回复数量 |
Tface | int | 4 | 允许 |
| 发贴表情 |
Ttopic | varchar2 | 20 | 不允许 |
| 标题 |
Tcontents | varchar2 | 30 | 不允许 |
| 正文,必须大于6个字符 |
Ttime | datetime | 8 | 允许 | 当天 | 发贴时间 |
TclickCount | int | 4 | 允许 | 0 | 点击数 |
Tstate | int | 4 | 不允许 | 1 | 状态,例如是否被锁,是否为精华贴 |
TlastReply | date | 8 | 允许 |
| 最后回复时间,必须晚于发贴时间 |
2、添加约束(bbsUsers)
2-1、非空约束。某列是否允许为空,该约束已在建表时指定(NULL)。
2-2、主键约束。UID列为主键。
2-3、默认约束。
初始密码:默认为6个“8”即888888。
性别:默认为1(男)。
注册日期:默认为当前日期。
用户状态:默认为0(离线)。
用户级别:默认为1(星级)。
用户积分:默认为20点。
2-4、检查约束。
密码:至少6位。
电子邮件:必须包含“@”字符。
2-5、外键约束。本表无外键。
2-6、参照bbsUsers的约束,为其余三张表添加相应约束
----------------------------------------答案------------------------------------
create user david identified by davidpass ;
create user david identified by davidpass ;
grant connect,create table to david;
grant select on martin.test to david;
grant resource to david;
--用户表
create table bbsUsers(
userid number(4) not null primary key,
Upass varchar2(12) check(length(Upass)>6),
gender char(1) default 1,
Udate date default sysdate,
Ustate char(1) default 0,
Ugrade char(1) default 1,
Uscore number(10) default 20,
email varchar2(20) check(instr(email,'@') > 0)
)
--主贴表
create table bbsTopic(
TID number(4) not null,
TsID number(4) not null,
TuID number(4) not null,
TreplyCount number(4),
Tface number(4),
Ttopic varchar2(20) not null,
Tcontents varchar2(30) not null,
Ttime date default sysdate,
TclickCount number(4) default 0,
Tstate number(4) default 1,
TlastReply date,
constraint fk_tsid_uid foreign key(TsID) references bbsSection(Sid),
constraint fk_tuid_uid foreign key(TuID) references bbsUsers(Userid)
)
--主贴表主键约束
alter table bbsTopic add constraint pkktid primary key(TID)
--版块表
create table bbsSection(
Sid number(4) not null primary key,
Sname varchar2(32) not null,
SmasterID number(4) not null,
Sprofile varchar2(20),
SclickCount number(4),
StopicCount number(4),
constraint fk_masterid_uid foreign key(SmasterID) references bbsUsers(userid)
)
--回贴表
create table bbsReply(
RID number(4) not null primary key,
RtID number(4) not null,
RsID number(4) not null,
RuID number(4) not null,
Rface number(4),
Rcontents varchar2(30),
Rtime date default sysdate,
RclickCount number(4),
constraint fk_rtid_tid foreign key(RtID) references bbsTopic(TID),
constraint fk_rsid_sid foreign key(RsID) references bbsSection(Sid),
constraint fk_ruid_uid foreign key(RuID) references bbsUsers(userid)
)
alter table bbsReply add constraint ck_content check(length(Rcontents)>6)
--用户表自增实现
create sequence u_seq increment by 1 start with 1 nomaxvalue nocycle nocache;
create or replace trigger tribbsuser before insert on bbsUsers for each row when (new.userid is null)
begin
select u_seq.nextval into :new.userid from dual;
end tribbsuser;
insert into bbsUsers(Upass,gender,Udate,Ustate,Ugrade,Uscore,Email) values('12343434',1,sysdate,1,0,20,'strong@126.com')
select * from bbsUsers
--主贴表自增实现
create sequence topic_seq increment by 1 start with 1 nomaxvalue nocycle nocache;
create or replace trigger tribbstopic before insert on bbsTopic for each row when (new.TID is null)
begin
select topic_seq.nextval into :new.TID from dual;
end tribbstopic;
--版块表自增实现
create sequence section_seq increment by 1 start with 1 nomaxvalue nocycle nocache;
create or replace trigger tribbssec before insert on bbsSection for each row when (new.Sid is null)
begin
select topic_seq.nextval into :new.Sid from dual;
end tribbssec;
--回贴表自增实现
create sequence reply_seq increment by 1 start with 1 nomaxvalue nocycle nocache;
create or replace trigger tribbsreply before insert on bbsReply for each row when (new.RID is null)
begin
select topic_seq.nextval into :new.RID from dual;
end tribbsreply;