oracle数据库 练习一

一、网络服务销售系统E-R图

案例分析：

    

   现有一个关于网络服装销售系统的项目，要求我们开发数据库部分。系统所能达到的功能包括以下几个方面：

 

1.客户注册功能。客户在购物之前必须先注册，然后才能浏览到所以库存服装信息，如款式，价格等。

 

2.顾客可以浏览到库存服装信息，如款式，价格，所剩数量等。

 

3.顾客可以订购自己喜欢的服装，并可以在未付款之前修改自己的选购信息。商家可以根据顾客是否付款，通过顾客提供的通讯地址给顾客邮

寄其所订购的服装。

 

根据案例分析过程提取实体集和它们之间的联系，画出相应的E－R图。（尽情发挥）

二、数据库用户

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的约束，为其余三张表添加相应约束

习题解析：

/*
Navicat Oracle Data Transfer
Oracle Client Version : 11.2.0.1.0


Source Server         : david
Source Server Version : 110200
Source Host           : localhost:1521
Source Schema         : DAVID


Target Server Type    : ORACLE
Target Server Version : 110200
File Encoding         : 65001


Date: 2017-07-20 20:33:24
*/

1.

-- ----------------------------
-- Table structure for BBSUSERS
-- ----------------------------
DROP TABLE "DAVID"."BBSUSERS";
CREATE TABLE "DAVID"."BBSUSERS" (
"USERID" NUMBER(4) NOT NULL ,
"UPASS" VARCHAR2(12 BYTE) NULL ,
"GENDER" CHAR(1 BYTE) DEFAULT 1  NULL ,
"UDATE" DATE DEFAULT sysdate  NULL ,
"USTATE" CHAR(1 BYTE) DEFAULT 0  NULL ,
"UGRADE" CHAR(1 BYTE) DEFAULT 1  NULL ,
"USCORE" NUMBER(10) DEFAULT 20  NULL ,
"EMAIL" VARCHAR2(20 BYTE) NULL 
)
-- ----------------------------
-- Checks structure for table BBSUSERS
-- ----------------------------
ALTER TABLE "DAVID"."BBSUSERS" ADD CHECK ("USERID" IS NOT NULL);
ALTER TABLE "DAVID"."BBSUSERS" ADD CHECK (length(Upass)>6);
ALTER TABLE "DAVID"."BBSUSERS" ADD CHECK (instr(email,'@') > 0);

2.

-- ----------------------------
-- Table structure for BBSREPLY
-- ----------------------------
DROP TABLE "DAVID"."BBSREPLY";
CREATE TABLE "DAVID"."BBSREPLY" (
"RID" NUMBER(4) NOT NULL ,
"RTID" NUMBER(4) NOT NULL ,
"RSID" NUMBER(4) NOT NULL ,
"RUID" NUMBER(4) NOT NULL ,
"RFACE" NUMBER(4) NULL ,
"RCONTENTS" VARCHAR2(30 BYTE) NULL ,
"RTIME" DATE DEFAULT sysdate  NULL ,
"RCLICKCOUNT" NUMBER(4) NULL 
)
-- ----------------------------
-- Checks structure for table BBSREPLY
-- ----------------------------
ALTER TABLE "DAVID"."BBSREPLY" ADD CHECK (length(Rcontents)>6);
ALTER TABLE "DAVID"."BBSREPLY" ADD CHECK ("RID" IS NOT NULL);
ALTER TABLE "DAVID"."BBSREPLY" ADD CHECK ("RTID" IS NOT NULL);
ALTER TABLE "DAVID"."BBSREPLY" ADD CHECK ("RSID" IS NOT NULL);
ALTER TABLE "DAVID"."BBSREPLY" ADD CHECK ("RUID" IS NOT NULL);
-- ----------------------------
-- Foreign Key structure for table "DAVID"."BBSREPLY"
-- ----------------------------
ALTER TABLE "DAVID"."BBSREPLY" ADD FOREIGN KEY ("RSID") REFERENCES "DAVID"."BBSSECTION" ("SID");
ALTER TABLE "DAVID"."BBSREPLY" ADD FOREIGN KEY ("RTID") REFERENCES "DAVID"."BBSTOPIC" ("TID");
ALTER TABLE "DAVID"."BBSREPLY" ADD FOREIGN KEY ("RUID") REFERENCES "DAVID"."BBSUSERS" ("USERID");

3.

-- ----------------------------
-- Table structure for BBSSECTION
-- ----------------------------
DROP TABLE "DAVID"."BBSSECTION";
CREATE TABLE "DAVID"."BBSSECTION" (
"SID" NUMBER(4) NOT NULL ,
"SNAME" VARCHAR2(32 BYTE) NOT NULL ,
"SMASTERID" NUMBER(4) NOT NULL ,
"SPROFILE" VARCHAR2(20 BYTE) NULL ,
"SCLICKCOUNT" NUMBER(4) NULL ,
"STOPICCOUNT" NUMBER(4) NULL 
)
-- ----------------------------
-- Checks structure for table BBSSECTION
-- ----------------------------
ALTER TABLE "DAVID"."BBSSECTION" ADD CHECK ("SID" IS NOT NULL);
ALTER TABLE "DAVID"."BBSSECTION" ADD CHECK ("SNAME" IS NOT NULL);
ALTER TABLE "DAVID"."BBSSECTION" ADD CHECK ("SMASTERID" IS NOT NULL);
-- ----------------------------
-- Foreign Key structure for table "DAVID"."BBSSECTION"
-- ----------------------------
ALTER TABLE "DAVID"."BBSSECTION" ADD FOREIGN KEY ("SMASTERID") REFERENCES "DAVID"."BBSUSERS" ("USERID");

4.

-- ----------------------------
-- Table structure for BBSTOPIC
-- ----------------------------
DROP TABLE "DAVID"."BBSTOPIC";
CREATE TABLE "DAVID"."BBSTOPIC" (
"TID" NUMBER(4) NOT NULL ,
"TSID" NUMBER(4) NOT NULL ,
"TUID" NUMBER(4) NOT NULL ,
"TREPLYCOUNT" NUMBER(4) NULL ,
"TFACE" NUMBER(4) NULL ,
"TTOPIC" VARCHAR2(20 BYTE) NOT NULL ,
"TCONTENTS" VARCHAR2(30 BYTE) NOT NULL ,
"TTIME" DATE DEFAULT sysdate  NULL ,
"TCLICKCOUNT" NUMBER(4) DEFAULT 0  NULL ,
"TSTATE" NUMBER(4) DEFAULT 1  NULL ,
"TLASTREPLY" DATE NULL 
)
-- ----------------------------
-- Checks structure for table BBSTOPIC
-- ----------------------------
ALTER TABLE "DAVID"."BBSTOPIC" ADD CHECK ("TID" IS NOT NULL);
ALTER TABLE "DAVID"."BBSTOPIC" ADD CHECK ("TSID" IS NOT NULL);
ALTER TABLE "DAVID"."BBSTOPIC" ADD CHECK ("TUID" IS NOT NULL);
ALTER TABLE "DAVID"."BBSTOPIC" ADD CHECK ("TTOPIC" IS NOT NULL);
ALTER TABLE "DAVID"."BBSTOPIC" ADD CHECK ("TCONTENTS" IS NOT NULL);
-- ----------------------------
-- Foreign Key structure for table "DAVID"."BBSTOPIC"
-- ----------------------------
ALTER TABLE "DAVID"."BBSTOPIC" ADD FOREIGN KEY ("TSID") REFERENCES "DAVID"."BBSSECTION" ("SID");
ALTER TABLE "DAVID"."BBSTOPIC" ADD FOREIGN KEY ("TUID") REFERENCES "DAVID"."BBSUSERS" ("USERID");

总结：表的创建需要一定的顺序，因为有时候表里面的内容依赖于别的表中的内容，所以需要先创建所需的表，然后再创建表。