数据库系统原理实验一：学生信息管理系统数据库结构搭建和表的创建_sql学生信息管理系统

实验目的与要求

1．熟悉SQL Server2012的图形界面工作环境
2. 理解几个主要系统数据库
3. 掌握SQL Server Management Studio和Transact-SQL语句创建数据库的方法；
4. 理解数据库文件和文件组，掌握文件和文件组的两种增加、删除、修改文件和文件组的方法。
5. 掌握分别用SQL Server Management Studio和Transact-SQL语句两种方式修改数据库，包括（删除数据库，分离数据库，附加数据库等）
6. 掌握使用SQL Server Management Studio和T-SQL语言创建表、修改表已经删除表。
7. 掌握创建、删除和修改约束的方法。
8. 了解如何让查看表的定义、数据的依赖关系。

实验原理与内容

1. 数据库的组织结构

1）逻辑存储结构
SQL Server数据库由一个表集合组成。这些表包含结构化的数据以及为支持对数据 的操作额保证数据的完整性而定义的其他对象，如视图、索引、存储过程、用户定义函数和触发器等，它们够成了数据库的逻辑存储结构，如图1-1所示。
2）物理存储结构
数据库的物理存储结构是说明包含数据库对象的数据库文件是如何在磁盘上存储的。创建数据库实际上就是在硬盘上创建数据库文件的过程，数据库文件是存放数据库数据和数据库对象的文件。
在SQL Server数据库中存在主要数据文件、次要数据文件和事务日志3种数据库文件。主要数据文件（简称主文件）包含数据库的启动信息并用于存储数据，文件扩展名为.mdf。每个数据库必须且只有一个主文件，该文件存放两种对象，即用户对象和系统对象。用户对象包含表、视图及存储过程等，用于保存用户输入的数据；系统对象有表名、数据库用户帐号、索引地址等维护数据库工作所需要的信息。系统表应该保存在主文件中，而用户数据可移动到次要数据文件中。
次要数据文件主要存储用户数据，文件的扩展名为.ndf。如果主文件包含了数据库中所有数据，则可以不要次要文件，但如果数据库非常大（如超出了单个windows文件的最大值），就应该有多个次要数据文件来辅助存放用户数据，但不能存放系统对象。使用次要数据文件可以让数据库文件不断得到扩充，并可以通过将不同的文件存放到不同的磁盘空间中实现同时对多个磁盘进行访问，从而提高数据存储的并发性。

图1-1

SQL Server主要通过事务日志文件来实现数据的恢复，文件扩展名为.ldf。数据库必须至少包含一个事务日志文件，每个日志文件仅能被一个数据库所拥有，数据和日志信息不应混在一起。
3）数据库文件组
为了方便数据的分配、放置和管理，SQL Server允许对文件进行分组处理， 在同一个组中的文件共同组成一个文件组。例如，可以分别在三个硬盘驱动器上创建三个数据文件Data1.ndf、Data2.ndf和Data3.ndf，并将它们指派到文件组Filegroup中，然后在Filegroup上创建一个表，这样对表中数据的查询将分散到三个磁盘上，从而查询性能得到改善。此外，将维护要求相近的表和索引分配到相同的文件组中，备份时仅备份文件组，而非数据庞大的整个数据库，能提高系统的可维护性。再有，对于不允许修改的表（如历史数据），可以将它们置于文件组中（主文件除外）然后将这些文件组标记为只读，从而起到保护数据的目的。
文件和文件组的设计遵循如下规则：一个文件或文件组不能由多个数据库使 用；一个文件只能是一个文件组的成员；事务日志文件不能属于任何文件组。

2. 数据库的分离和附加

数据库的分离和附加是一对互逆的操作。分离数据库是指将数据库从SQL Server实例中删除，即从数据库的逻辑结构中删除，但并不会将其物理文件（数据文件和日志文件）从磁盘上删除，之后，就可以使用这些文件将数据库附加到任何SQL Server实例，包括分离该数据库的服务器。当需要将数据库物理文件从当前位置移到另一磁盘或存储路径下时，可先分离再附加数据库。通常，附加数据库时会将数据库重置为它分离或复制时的状态。

在进行数据库分离操作时，要注意以下几点：
（1）不能对数据库对象进行更新操作和运行任务，用户也不能连接在该数据库上。
（2）在移动数据库之前，最好为数据库做一个完整的备份。
（3）确保数据库要移动的目标位置及将来数据增长能有足够的空间。

3. 数据库表的创建，修改和删除

（1）基本表的创建
定义基本表
CREATE TABLE <表名>
(<列名> <数据类型>[ <列级完整性约束条件> ]
[,<列名> <数据类型>[ <列级完整性约束条件>] ]
…
[,<表级完整性约束条件> ] );
<表名>：所要定义的基本表的名字
<列名>：组成该表的各个属性（列）
<列级完整性约束条件>：涉及相应属性列的完整性约束条件
<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件
如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。

（2）基本表的修改
ALTER TABLE <表名>
[ ADD[COLUMN] <新列名> <数据类型> [ 完整性约束 ] ]
[ ADD <表级完整性约束>]
[ DROP [ COLUMN ] <列名> [CASCADE| RESTRICT] ]
[ DROP CONSTRAINT<完整性约束名>[ RESTRICT | CASCADE ] ]
[ALTER COLUMN <列名><数据类型> ] ;
<表名>是要修改的基本表
ADD子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件
DROP COLUMN子句用于删除表中的列
如果指定了CASCADE短语，则自动删除引用了该列的其他对象
如果指定了RESTRICT短语，则如果该列被其他对象引用，关系数据库管理系统将拒绝删除该列
DROP CONSTRAINT子句用于删除指定的完整性约束条件
ALTER COLUMN子句用于修改原有的列定义，包括修改列名和数据类型
（3）基本表的删除
DROP TABLE <表名>［RESTRICT| CASCADE］;
RESTRICT：删除表是有限制的。
欲删除的基本表不能被其他表的约束所引用
如果存在依赖该表的对象，则此表不能被删除
CASCADE：删除该表没有限制。
在删除基本表的同时，相关的依赖对象一起删除

实验设备与软件环境

1.Windows XP Professional SP3/Windows7
2.SQL Server 2012

实验过程与结果（可贴图）

1. 根据学生信息管理系统创建相关的数据库
   1.1 使用SQL Server Management Studio创建数据库，要求创建主数据文件，次要数据文件，日志文件等。文件的具体参数组别自己决定，但要阐述设置该参数的理由。
   

PRIMARY是指关联的文件组，定义主文件。
主数据文件的初始大小为5的理由：主文件必须至少是 5 MB 才能容纳 model 数据库的副本。
增量，可以用% 或者mb，但是不能超过最大。
最大大小为增长无上限，就是不设定文件的上限，直到磁盘满。

1.2 使用SQL Server Management Studio删除1.1创建的数据库

1.3 使用Transact-SQL语句创建数据库，要求创建主数据文件，次要数据文件，日志文件等。文件的具体参数组别自己决定，但至少包含3个数据文件，文件的增量要包含具体数字和百分比两种形式。阐述设置相关参数的理由。

增量均较小，防止空间的浪费
1.4 设计一种情景，阐述需要修改数据库的数据文件和日志文件，并修改它们。
需要修改次要数据文件和日志文件的增量和最大文件大小限制时，如下图：

需要修改日志文件的初始大小时，如下图：

用代码实现数据库的数据文件和日志文件的修改，如下图

2. 数据库表初步设计及实现

2.1 根据3.1的二维表，在数据服务器上用Transact-SQL语句创建相关的表。至少包含以下3个表以及合理的相关属性：学生表（至少含8个属性），班级表（至少含6个属性）和宿舍表（至少含5个属性）。三个表中属性的数据类型，包含数字型（整型和浮点型），字符串性（可变和不可变），日期型，货币等不同数据类型。

数据字典可以参考如下：
学生表：Student
属性 类型 完整性 备注
Sno Char(11) 外主键 学生的学号
Sname varchar（50） 非空 学生的姓名
Cno Char(10) 外键 班级编号
Birthday Data 允许为空 出生日期
Gender Char(2) 男/女 性别
Age tinyint 根据出生日期计算 年龄
Telephone Char(11) 唯一，不允许为空 手机号
Email varchar（50） 唯一，不允许为空 email
班级表：Class
属性 类型 完整性 备注
Cno Char（10） 外主键 班级编号
Cname varchar（50） 唯一，非空 班级名称
Sdeptname Char（30） 非空 专业名称
Cnum Char（10） 非空 辅导员编号
Counselor Char（8） 非空 辅导员姓名
Ctel Char（11） 允许为空 辅导员电话
宿舍表：Dorm
属性 类型 完整性 备注
Dno Char（10） 主键 宿舍编号
Dname varchar（50） 唯一，非空 宿舍名称
Sno Char（11） 外键 学生学号
Dnum Tinyint 非空
取值范围(0-12) 床位号
Maxsize Tinyint 非空
取值范围(0-12) 几人间的宿舍
Accommodation money 非空 住宿费
Degree Numerice(8,2) 默认（0.0） 用电度数
Price Numerice(8,2) 非空 电费

答：
CREATE TABLE Student
(Sno CHAR(11) PRIMARY KEY,
Sname VARCHAR(20) NOT NULL,
Cno CHAR(10),
Birthday Date,
Gender CHAR(2) check(Gender=‘男’ or Gender=‘女’),
Age TINYINT,
TeLephone CHAR(11) UNIQUE NOT NULL,
Email VARCHAR(50) UNIQUE NOT NULL
)

CREATE TABLE Class
(Cno CHAR(10) PRIMARY KEY,
Cname varchar(50) unique NOT NULL,
Sdeptname char(30) NOT NULL,
Cnum char(10) NOT NULL,
Counselor CHAR(8) NOT NULL,
Ctel char(11) NULL
)

alter table Student add FOREIGN KEY (Cno) REFERENCES Class(Cno);

CREATE TABLE Dorm
(Dno CHAR(10) PRIMARY KEY,
Dname VARCHAR(50) UNIQUE NOT NULL,
Sno CHAR(11) FOREIGN KEY REFERENCES Student,
Dnum TINYINT NOT NULL CHECK(Dnum > 0 AND Dnum < 12),
Maxsize TINYINT NOT NULL CHECK(Maxsize > 1 AND Maxsize <12),
Accomodation MONEY NOT NULL,
Degree NUMERIC(8,2) DEFAULT 0.0,
Price NUMERIC(8,2) NOT NULL
)

2.2 考虑表相互间的约束条件和本身字段的约束条件，并实现
每个学生都有对应的班级：
alter table Student add FOREIGN KEY (Cno) REFERENCES Class(Cno);

2.3 设计某种情景，要修改和删除表里面的约束条件，并实现
删除约束：

添加约束：约束性别选项

2.4 三个表中，任选其中一个表，根据某种业务逻辑，创建索引
创建学生名字按字典部首为顺序的非聚集索引：

2.5 使用SQL Server Management Studio，每个表至少输入3条有效数据

2.6 请用两种不同方法，实现删除学生表和对应宿舍表中的数据（提示：级联删除）
学生表和宿舍表表格初始数据：

方法1：级联删除

如下两图，学生表和对应宿舍表中的第一行数据已经删除

方法2：联合查询删除

此时查找出学号为102的学生的宿舍号为1132（先假设一个学生配备一个宿舍号），接下来进行删除

2.7 请实现完全删除学生表和宿舍表（包含表数据及其定义）

3. 实现数据库的分离和附加

3.1 使用SQL Server Management Studio分离1.3所创建的数据库

需要勾选以上两个选项。
3.2 附加分离的数据库

选择添加后确定即可

操作异常问题与解决方案

1.直接删除基本表时出现报错，因为该基本表被一个 FOREIGN KEY 约束引用。
解决方案：先删除该基本表与其他表的约束后再对表进行删除
2.我在分离数据库时出现了报错。
解决方案：分离数据库时要勾选删除和更新两个选项。

实验总结

通过这次实验我掌握了创建数据库、删除数据库、分离数据库以及附加数据库的方法，能够理解数据库文件和文件组及其增加、删除、修改文件和文件组的方法，能够熟练地建立、添加、删除、查找、更新数据库表等，充分了解并能够应用了删除数据库表中的数据的两种方法（级联删除和联合查询删除）。同时在实验中了解了操作不当、代码出错的各种原因，以及通过实验报告的编写，增加了我对数据库这门课程的了解以及实践能力和文档编写能力。