数据库系统设计实验报告 自己做的超完整 - 图文

《数据库管理与开发》实验报告

课 程 号：B0900990-0

实验项目：数据库设计、创建，表及各种对象的创建、管理与应用 学 号 姓 名 Xxx 同组人学号姓名 实验 地点 评分 时间 2011年11-12月 无 实验要求： 按时完成实验；实验内容和过程记录完整；回答问题完整、正确；实验报告的撰写认真、格式符合要求。 一、实验目的 1．理解并掌握数据库设计的概念、方法和步骤。 2．了解需求分析的内容。 3．重点运用ER模型进行概念设计。 4．将ER模型转换为关系模型。 5．掌握SQL Server的数据库的创建与管理。 6．掌握SQL Server表的概念、设计、创建与管理。 7．全面掌握Transcat-SQL命令。 8．掌握安全性的概念，创建登录、用户、角色，授予相应的权限。 9．掌握视图、存储过程、触发器等的创建与应用。 二、实验设备（环境）及要求 PC、 Windows XP/2003、SQL Server 2005 三、实验内容及要求 实验项目：网上购物系统数据库 (一) 简要的系统需求分析 网上购物系统是面向广大的消费者,由于不同的人群对于商品的需求不同,网上的购物系统应该具备自己的特色同时应该适应不同人群的需要。 网上购物系统主要包括了客户从登陆到网站，然后挑选自己喜欢的商品的，到最后购物的过程，然后形成一个产品的订单，要产生订单就必然会涉及到价格，这里面对不同类型的VIP会员会有不同的折扣。 该系统的基本业务是用户登录、浏览商品然后加入购物车、形成订单、处理订单、以及之后的客户评论模块。 计算机需要完成的基本功能是验证用户登录，检验其用户名和密码是否正确；当用户点击提交到购物车时要把用户提交的物品放入购物车中；在购物车中还要计算好每一件商品的价格和总额；用户填写订单的整个过程以及包括把订单提交给管理员来出来订单；能够提交客户对服务或者商品的质量等进行的评论。 信息系统需要处理的信息有把数据库跟前台紧密的联系起来，包括后台中的客户表、商品表、订单明细表、评论表等信息。 客户 信 息 管理 登录 管理员 判断身份 客户 商品信息管理 订单信息管理 评论信息管理 查看商品信息 查看个人信息 购买商品评论信息 (二) 概念设计 设计系统的概念模型，采用ER模型。 概念设计是把用户的需求进行综合，归纳与抽象，统一到一个整体概念结构中，形成数据的概念模型，主要是用E-R模型来实现的。 下面就来显示上面各实体以及实体之间的关系的E-R模型。 1.客户：客户号、密码、姓名、性别、单位、电子信箱、身份证号，VIP等级 客户名 VIP等级 身份账号 客户 密码 电子邮箱 单位 姓名 性别 2.VIP会员表：会员等级、会员折扣，这一个表相当于与客户表进行了合并了。 VIP会员 会员等级 会员折扣 3. 商品：商品号、商品名称、类别号、商品描述、单位、规格、单价、其他 商品号 商品名称 类别号 其他 VIP会员 商品描述 单价 单位 规格 4．生产厂商表：厂商号，生产厂商名、地址、电话 5.商品类别：类别号、类别名称 类别号 6.地址：地址号、地址名称 地址号 地址 地址名称 电话 生产厂房 地址 厂商号 生产厂商名 商品类别 类别名称 7.订单：订单号、客户号、商品号、收货地址、订货日期、订货数量、单价、发票号码、订单金额、发货日期 发货日期 订单号 订单金额 客户号 订单 发票号码 商品号 单价 订货地址 收货地址 订货数量 8.供应：商品号、厂商号，实际库存量，最低库存量 商品号 厂商号 供应 实际库存量 最低库存量 9.客户类型：客户号、客户类型 客户类型 客户类型 客户号 10.评论：评论号、客户号、商品号、客户邮箱、评论内容、评论时间。 客户邮箱 评论号 评论 评论内容 客户号 评论时间 商品号 11.客户地址表：客户号、客户地址 客户号 12.上面的各个实体和关系之间的关系图: 地址 客户类型 n 所在 1 评论 1 m n 属于 供应 客户 m 商品 n n m n n n 订单 生产厂商 属于 分类 1 1 VIP会员 商品类别 实体间关系说明： （1）、一个客户类型可以拥有多个客户。 （2）、一个客户可以有多个地址。 （3）、VIP会员中有多个客户。 （4）、一个客户可以对多个商品进行评论。 （5）、一个客户可以对多个商品下订单。 （6）、一个商品类别中包含有多种商品。 （7）、一种商品可以由多个生产厂商生产，同时一个生产厂商可以生产多种商品。 (三) 逻辑设计 将ER模型为关系模型，指出每个关系的主键、外键和必要的约束。 1.为了简化进行的分析： 为了减少系统开销，将客户类型表删除，其主要信息转换成客户表的客户类型属性；客户类型有两种：普通和VIP，若为普通，则在VIP等级中取值0。 地址表也删除，其主要信息放在客户地址表中。 为了减少订单的冗余，将订单表分解为： 订单总表：订单号、客户号、收货地址、订货日期、订单状态、发票号码、订单金额、发货日期 订单明细表：订单号、商品号、订货数量、单价、订货日期 2.优化之后得到的关系模型为： (1).客户（客户号、密码、姓名、性别、单位、电子信箱、身份证号，VIP类别，客户地址） 客户地址 客户地址

(2).商品(商品号、商品名称、类别号、商品描述、单位、规格、单价、其他) (3).生产厂商表（厂商号，生产厂商名、地址、电话） (4).商品类别（类别号、类别名称） (5).订单总表（订单号、客户号、收货地址、订货日期、订单状态、发票号码、订单金额、发货日期） (6).订单明细表（订单号、商品号、订货数量、单价、订货日期） (7).供应（商品号、厂商号，实际库存量，最低库存量） (8).评论（评论号、客户号、商品号、客户邮箱、评论内容、评论时间） (四) 写出数据库的物理设计 表的结构设计等。 1、 客户表 序号 字段名称 字段类型 字段长度 备注 1 客户号 Varchar 20 主键 2 密码 Varchar 20 不可空 3 姓名 Varchar 25 不可空 4 性别 Varchar 10 5 单位 Varchar 100 6 电子邮箱 Varchar 20 7 身份证号 Bigint 不可空 8 VIP类型 Varchar 10 不可空 9 客户地址 Varchar 50 2、 商品表 序号 字段名称 字段类型 字段长度 备注 1 商品号 Varchar 20 主键 2 商品名称 Varchar 30 不可空 3 类别号 Varchar 20 不可空 4 商品描述 Varchar 100 5 单位 Varchar 30 不可空 6 规格 Varchar 20 7 单价 Int 不可空 8 其他 Varchar 50 3、 生产厂商表 序号 字段名称 字段类型 字段长度 备注 1 厂商号 Varchar 20 主键 2 生产厂商名 Varchar 30 不可空 3 地 Varchar 40 不可空 4 电话 Varchar 20 不可空 4、 商品类别表 序号 字段名称 字段类型 字段长度 备注 1 2 类别号 类别名 Varchar Varchar 20 40 主键 不可空 5、 订单总表 序号 字段名称 1 订单号 2 客户号 3 收货地址 4 订货日期 5 订单状态 6 发票号码 7 订单总额 8 发货日期 6、 订单明细表 序号 字段名称 1 订单号 2 商品号 3 订货数量 4 单价 5 订货日期 7、 供应表 序号 字段名称 1 商品号 2 厂商号 3 4 8、 评论表 序号 1 2 3 4 5 6 字段类型 Varchar Varchar Varchar Date Varchar Varchar Int Date 字段长度 20 20 50 20 20 备注 主键 不可空 不可空 不可空 不可空 不可空 不可空 字段类型 Varchar Varchar Int Varchar Date 字段长度 20 20 20 备注 主键 不可空 不可空 不可空 不可空 字段类型 Varchar Varchar 实际库存量 bigInt 最低库存量 bigInt 字段长度 20 2 备注 主键 主键 不可空 不可空 字段名称 评论号 客户号 商品号 客户邮箱 评论内容 评论时间 字段类型 Varchar Varchar Varchar Varchar Varchar Date 字段长度 20 20 20 20 100 备注 主键 不可空 不可空 不可空 不可空 不可空 (五)T-SQL命令方式创建数据库 1、创建数据库。 （1）、用T-SQL命令创建了指定多个数据文件和事务日志，名为OnlineShopping的数据库。如下图所示： （2）、全部选中之后然后点击“执行”，就会执行相应的语句，并在命令窗口显示“命令已经成功完成”。如下图所示： （3）、然后刷新左边的数据库，就会出现我们所建立的OnlineShopping数据库。 （4）、然后找到E盘的OnlineShopping文件夹，点击进入之后就会看到包含我们所建立的数据文件和事务日志文件。说明创建数据库成功。 2.在数据库中建表。 (1)、创建“客户表”。 （2）、创建“商品表”。 （3）、创建“生产厂商表”。

（4）、创建“商品类别表”。 （5）、创建“订单总表”。 （6）、创建“订单明细表”。 （7）、创建“供应表”。注意这个表有点特殊的是：由两个属性共同的作为主键，要用CONSTRAIT 主键名 PRIMARY KEY（属性A,属性B） （8）、创建“评论表”。 （9）、表全部创建完成之后，刷新数据库，可以看到这些表。 3、在数据库中创建索引。 说明：索引包含“唯一性索引”，“主键索引”，“聚集索引”。 因为生成的表的时候系统自动的为每一个表设置了“主键索引”如图所示， “聚集索引”是指表中的各记录的物理顺序与键值的逻辑顺序一致。一张表中只能有一个“聚集索引”。而系统中的这个主键索引也是聚集索引，所以不能再对表格创建聚集索引。 所以我下面创建的是唯一性索引，全部都是非聚集索引。 （1）、在“客户表”中创建了一个按“身份证号”列建立的唯一索引“Customer”。 说明：这里创建的是唯一索引，唯一索引的含义是对于表中的任何两行记录来说，索引键的值都各不相同。并且要注意，如果表中一个字段或者多个字段的组合在多行记录中具有NULL值，则不能将这个字段或者字段组合作为唯一索引键。因为对于每一个表的主键系统都自动的设置了相应的索引，在“客户表”中，身份证号是绝对不能相同的，所以可以设置为唯一索引键。 （2）、在“商品表”中创建了一个按“单价”列建立的非聚集索引“Goods”。 （3）、在“生产厂商表”中创建了一个按“电话”列建立的非聚集索引“Supply”。 （4）、在“商品类别表”中创建了一个按“类别名”列建立的非聚集索引“Type”。 （5）、在“订单总表”中创建了一个按“客户号”列建立的非聚集索引“Order1”，注意Order是关键词，不能够做为普通的名字使用。 在“订单总表”中创建了按“发票号码”列建立的一个唯一性索引“Order2”。 （6）、在“订单明细表”中创建了按“商品号”列建立的一个非聚集索引“Order1”。 （7）、在“供应表”中创建了按“厂商品号”列建立的一个非聚集索引“supply”。 （8）、在“评论表”中创建了按“客户邮箱”列建立的一个非聚集索引“评论”。 （9）检验索引已经成功创建。

4、约束的创建。 说明：约束是SQL Server提供的自动保持数据库中的数据完整性的一种机制。它包含6种形式，分别是“主键约束”，“外键约束”，“唯一性约束”，“检查约束”，“默认约束”，“非空值约束”。 因为“主键约束”和“非空值约束”是在创建表的时候就已经创建了的，所以下面就不再讨论这两个。 一般而言，“外键约束”也是在创建表的时候就直接创建的，但是我这里没有创建，还可以用更改表的方法来创建，即利用“ALTER TABLE ￥￥ ADD”语句创建。 唯一约束指的是一个或者多个列的组合的值具有唯一性，它和主键索引的唯一区别就是可以为NULL。因为我在设置表的时候把那些属性均置为“不可为空”。所以在该数据库中建立不了唯一约束。 4.1外键约束的创建。 （1）、为“商品表”创建外键“类别号”。 （2）为“订单总表”创建外键“客户号”。 （3）为“订单明细表”创建外键“商品号”。 （4）、为“供应表”创建了外键“商品号”和“厂商号”。 （5）、为“评论表”创建外键“客户号”，“商品号”。 4.2检查约束的创建。 （1）、为“客户表”创建的性别的范围定义的检查约束。 （2）为“生产厂商表”创建的规定电话的格式的检查约束。 （3）为“订单总表”创建的订单状态的范围的检查约束。 （4）、为“订单总表”创建的订单日期在订货日期之前的约束。 （5）、为“订单总表”创建的订单日期在当前日期之前的约束。 （6）、为“订单明细表”创建的订货数量必须>5的约束。 因为之前在定义表的时候把列名写错了，所以现在修改“订单数量”为“订货数量”。 然后再次执行检查约束： （7）、为“供应表”创建的最低库存量必须>10的约束。 （8）、为“评论表”创建的评论时间必须为当前时间的约束。 (9)、验证这些约束已经成功建立。

(六) 关系图设置显示、存储。 1、找到数据库关系图并右击新建数据库关系图。 2、出现下面的界面的时候就把所有的表格全部的添加。 3、系统会自动的生成所有表格的关系图，如下图所示： (七) 利用T-SQL命令增加、删除、修改数据。 1、对“客户表”的增加、删除、修改数据。 （1）、增加三行数据。 然后查看客户表： （2）删除客户号为‘002’的那一行数据。 删除了之后的客户表为： （3）修改客户号为‘001’那一行的电子邮箱和VIP类型。 先前的数据： 修改数据的代码： 修改之后的数据： 2、对“商品表”进行的增加、删除、修改数据。 说明：因为在商品表中的商品类别号是外键，外键或者是“商品类别表”中类别号对应的值，或者是空值，我现在增加的时候增加的不是空值，所以必须得保证商品类别表中有数据。下面是对商品类别表增加的数据： （1）增加两行数据。 查看表中数据： （2）修改第三行的数据。 修改之后： （3）删除第二行数据。 删除之后表中的数据： 3、对“生产厂商表”的增加、删除、修改数据。 （1）增加数据。 因为我输入的电话形式违反了约束中的电话的形式，所以插入不了。 （2）修改其中数据。

（3）删除其中的数据。 删除之后的数据： 4、对“订单总表”进行的增加、删除、修改数据。 （1）增加数据。 在插入数据时一定要注意约束，例如订单状态就是有一个范围的约束，还有订货日期一定要在发货日期之前，否则会报错，例如下图： 修改之后就可以插入数据了： 再次插入数据： 显示表中数据： （2）修改数据，修改客户号为‘003’的收获地址和订单状态。 修改之后为： （3）删除数据，订单总额>10000并且<50000的数据。 也就是删除了三行数据： 5、对“订单明细表”进行的增加、删除、修改数据。 （1）增加数据，注意订单数量必须在10之上，因为有约束的限制。 增加之后表中的数据： （2）修改数据。 修改后订货数量变成25。 （3）删除数据。 修改后D001被删除。 6、对“供应表”的增加、删除、修改数据。 （1）增加数据。 增加之后表中的数据为： （2）修改数据。 商品号为001的商品的最低库存量变成20： （3）删除数据。 那么商品号为002的商品被删除： 7、对“评论表”的增加、删除、修改数据。 （1）增加数据。

（2）修改数据。 （3）删除数据。 9、 其他的数据添加不是使用的SQL语句，最后每个表的信息如下图： 客户表： 商品表： 生产厂商表： 商品类别表： 订单总表： 订单明细表： 供应表： 评论表： (八) 写出T-SQL查询。 1、按照某一客户的其中几个字段的信息： 2、查询前4行的数据。 3、查询的性别和VIP类型的组合 下面是不允许有重复的行。 下面是允许有重复的行出现： 4、COUNT在查询中的三种方式： 查询全部的行数。 查询的VIP地址的个数，只要一行中不是空值就会计数。 有重复的： 没有重复的： 5、查询得到的结果放在另一个表当中。 然后可以找到“客户VIP”这个表： 6、WHERE后有条件运算符的查询。 （1）BETWEEN AND。

（2）EXISTS。查询的是在订单明细表中存在的商品的信息。 （3）IN。后面跟的是子查询。查询的是在订单明细表中存在的商品的信息。 IN。后面跟的是单值集合。 （4）LIKE。 下面是单个匹配： 下面是多个匹配： 7、使用多表查询。 （1）笛卡尔积。生产厂商表有6行数据，商品类别表有5行数据。总共会有30行数据。 （2）内部链接。在客户表和订单总表中客户号相同的客户信息。 （3）外部链接。 主要是要注意客户号为006的客户后面的订单信息没有，因为在订单总表中没有其信息，但是因为是左连接，所以在客户表中所有的信息都会显示。 8、查询得到的结果作为视图显示。 而且必须要注意的是：CREATE VIEW 必须查询批次中的第一个语句。所以之前不能有任何的语句。 然后查看视图V1 10、 将查询的结果排序输出，按照降序输出。 11、 对查询的结果分组统计并筛选。 12、 嵌套查询。 （1）单值嵌套查询： （2）多值嵌套查询。 (3)嵌套查询中使用IN (九)创建登录，授予角色。 1、SQL Server验证的创建用户user1 的用户登录，登录密码是123. 然后可以通过这个用户和账号登录到SQL Servlet数据库中，如下图所示：

创建了用户名为user3的用户登录，密码是‘456‘。 创建了一个用户名为user2的登录，密码是“123”。 2、计算机windows 验证的创建用户登录。 首先我创建了一个新的账户名为“藤卓依” 然后通过计算机验证创建了一个登录。 3、添加角色，因为固定服务器角色是不能进行添加、删除或修改等操作的，所以只能将用户登录添加为固定服务器角色的成员。 （1）为user1添加为固定服务器角色sysadmin的成员。 然后点击开“安全性”中的sysadmin角色成员中包含了user1. 因为为user1添加了系统管理员sysadmin的角色，所以用可以进行所有的操作，具有所有的权限。 （2）为user3添加了数据库创建者的角色，可以对数据库进行创建修改和删除等操作。 然后点击安全性的登录名，可以看到有我们所创建的user1和user3。 然后再打开安全性下的服务器角色中的dbcreator，可以看到其中包含了user3. 为user2添加了数据库创建者的角色，可以对数据库进行创建修改和删除等操作。 (十)创建数据库用户、角色、架构，授予相应权限。更改拥有者，收回权限等。 1、创建数据库用户。 说明：因为user1是sysadmin角色，相当于管理员。 在user1登录用户下创建了一个user2数据库用户。 然后我们可以看到在数据库的安全性下存在user2用户，如下图所示： 又创建了一个USER user3，由下图可以看到： 2、添加数据库角色成员。 在db_ddladmin数据库角色中添加了user2，首先所有的数据库用户都具有public的角色，也就是说user2还具有了删除的功能。 为在db_datareader角色中添加了user3. 下图表示了添加成功。 然后此时以user2的身份登录，点击数据库OnlineShopping的时候会出现下面的对话框，说明虽然他们具有对数据的创建数据库，但是还没有访问数据库的权限。 然后以user3的身份登录，我们可以打开数据库查看其中的内容，但是当我想要向其中的订单明细表添加数据时，出现下面的对话框，说明只能查看，不能插入。 3、创建架构，为user2，user3授予创建架构的权限。 4、然后以user3的身份登录，然后user3就创建了一个架构SC1。 但是因为我们只授予了user3创建架构的权限，所以它只能创建架构，而不能创建表格还有其他任何的操作，如上图所示。 5.然后我们在user1下，对user3授予创建表格的权限，然后再授予查询、删除、修改表格的权限。

注意：只能在master 数据库中授予CREATE DATABASE 权限。 然后再user3登录的界面中检验一下： 注意：指定的架构名称\不存在，或者您没有使用该名称的权限。 6、更改架构的所有者，将架构SC1的所有者由user3更改为DBO. 然后再在user3登录的界面检验是否还能够在SC1架构下添加表此时已经无法建表。 然后再管理员的权限下可以创建表格。 7、禁止与撤销权限。 (十一)创建视图. 说明：创建视图的前提是要有create view 的权限，并且还要拥有架构的所有权。 1、首先授予创建视图的权限给user3。 然后把SC1架构还给user3。 但是还有一个问题就是user3只拥有SC1架构，只能对SC1架构下创建的表进行操作，不能对DBO架构下的表操作。所以我们下面是在user1下创建的视图。 2、创建一个视图V2，刷新后可以看到dbo.V2视图。 查看视图dbo.V2。 3、创建了一个较复杂的视图V3。在这个视图中，显示的是客户号和客户号在这个表中重复的次数。 查看视图 4、创建一个实际库存量和最低库存量的差额小于10的商品。 查看视图V4。 (十二)创建存储过程。 1.创建存储过程P1。 执行存储过程： 2、创建了一个存储过程P2。 执行存储过程： 3、创建添加数据的存储过程，带参数的存储过程。

本文来源：https://www.bwwdw.com/article/dt7o.html