2022年数据库系统-tcg-实验--数据库与表的创建参考 .pdf

数据库原理实验指导书实验一数据库、表的基本操作一、学时学时二、实验类型验证、设计三、实验目的1.熟练掌握 SQL Server 200X的使用方法。2.熟练掌握使用 SQL Server 200X的对象资源管理器以及SQL 的 DDL 完成数据库的创建、删除和连接方法；数据表的建立、删除；表结构的修改。3.加深对表的实体完整性、参照完整性和用户自定义完整性的理解。四、需用仪器、设备微机；Windows2000/xp 操作系统；SQL SERVER 200 x五、实验准备1 熟悉 SQL SERVER 工作环境；2 复习有关数据库、表的建立、修改和删除的SQL 语言命令；六、实验方法及步骤 熟悉有关 SQL SERVER的图形界面工作环境（参见后附录2 SQL Server 2005的使用）创建数据库或连接已建立的数据库 在当前数据库上建立新表 定义表的结构：用命令形式：用 SQL SERVER 提供的对象资源管理器（企业管理器）以交互方式进行5 将以上表的定义以.文件的形式保存在磁盘上。6 修改表的结构7 删除表的定义七、实验内容1用企业管理器与SQL 语句分别完成 Part1 and part 2 of SSD7 exercise 1。名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 11 页 -2在以下实验中，使用学生-课程数据库，它描述了学生的基本信息、课程的基本信息及学生选修课程的信息。用对象资源管理器（企业管理器）与SQL 语句分别完成。（1）创建学生-课程数据库，数据文件名为student_data、大小 10M，日志文件名为student_log、大小 5M 的新数据库，该数据库名为student。（2）创建学生关系表 S：学号姓名性别年龄所在系SnoSnameSsexSagesdept（3）创建课程关系表 C：课程号课程名先行课学分CnoCnameCpnoccredit（4）创建学生-课程表 SC：学号课程号成绩SnoCnograde（5）将以上创建表 S、C、SC 的 SQL 命令以.SQL 文件的形式保存在磁盘上。在表中加入至少 4 个元组，第一个为本人信息。（6）在表 S 上增加“出生日期”属性列。（7）删除表 S 的“年龄”属性列。（10）删除表 SC，利用磁盘上保存的.SQL 文件重新创建表 SC。（11）备份数据库，再还原。八、实验思考1、定义表结构是对数据模型的那个要素进行描述，对主键有什么要求？2、修改表结构时，能否修改已定义属性的参数？新增加的属性列能否定义为非空？为什么？附 1：实验报告基本格式实验名称：一、实验目的二、实验环境三、实验内容四、实验结果（写出每次实验步骤以及结果）名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 11 页 -SSD7 exercise 1 Please answer the following question(s).Relational Databases Design the following two tables and turn in your submissions in an HTML file named BookPublisher.html.Author is Primary key.BOOK:Author Title ISBN PublisherID Edition Date of Publication Price Book Description PUBLISHER:PublisherID Name Address -Part I a.Identify the keys in your tables as follows.For this exercise,ignore the performance considerations while choosing your keys.b.For each table,indicate the Primary Key(s)(PKs).If there is no PK,state so.Fully justify your answer.c.For each table,indicate the Alternate Key(s)(AKs).If there is no AK,state so.Fully justify your answer.d.For each table,identify the Foreign Key(s)(FKs)and the integrity constraints.Fully justify your answer.e.List all columns of your tables,and the domains(the data types and formats)of these columns.Fully justify your answer.In an HTML table,list at least four rows with valid values in each of the tables.Turn in your submission in a file named BookPublisher.html.-Part II Write SQL statements as specified below.You may find this help on setting up your DBMS useful.Submit your SQL statements in a file named createDB.sql.名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 11 页 -a.Write the DDL statements to create the two tables using the CREATE TABLE statement.b.Be sure to identify your primary keys and alternate keys in the statement.c.Write the INSERT statements to insert the four rows into each table.d.Write the SQL statement to retrieve the title and price of all books published by either of two publishers(say Addison Wesley and McGraw Hill).e.In the file Rel-ops.txt,list which relational operations you used,from among the select/project/join operations,in order to perform this query.Explain the role of each operation in your query.f.Write the SQL statement to retrieve the Publisher name of a particular book title(say Fundamentals of Database Systems).-Part III In the file named part3.txt,provide answers to the following questions.In 1.2.2 Relational Operations,there was an example of the set difference operation:the result of r-s was provided to you.Provide the result of the set difference operation s-r.In the class notes,the following UNION example was discussed.Give a prosaic description of the results that will be retrieved by replacing UNION with EXCEPT in the following query:(SELECT MemNo FROM MEMBER,BOOK WHERE MemNo=BorrowerMemNo AND CallNumber=QA76.9.D26C66 )UNION (SELECT MemNo FROM MEMBER,BOOK WHERE MemNo=BorrowerMemNo AND CallNumber=QA76.9.D7E53 );Your submission for this exercise should consist of the following files:BookPublisher.html and createDB.sql,Rel-ops.txt,and part3.txt.To help yourself do your best on this assessment,consult this general list of grading guidelines.Exercise 2 名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 11 页 -Please answer the following question(s).Relation schemas are presented in fig1.SQL Run the SQL script given to you to create a Library database.Note that each row in the Book table denotes a book copy.Thus,if the library carries three copies of the title DBMS,there will be three rows in the Book table,one for each copy.Write the SQL statements to do the following against the database(Note:You must express your query in a single SQL statement for each of the following.However,that statement could have sub-queries.):a.List the titles of all books written by Churchill,along with their Year of Publication.b.Retrieve the titles of all books borrowed by members whose first name is John or Susan.c.List the names and IDs of all members who have borrowed the Iliad and the Odysseyboth books.d.List the names and IDs of all the members who have borrowed all titles written by Collins.Assume that a member may have borrowed multiple copies of the same title.e.Find the phone numbers of all members who have borrowed a book written by an author whose last name is Tanenbaum.f.Find those members who have borrowed more than three books and list their names,IDs,and the number of books they borrowed.Sort the results in descending order based on the number of books borrowed.g.List all members who have not borrowed any book.h.List in alphabetical order the first names of all the members who are residents of Pittsburgh(Phone numbers starting with 412)and who have not borrowed the book titled Pitt Roads.Fig1.an example in 1.2.2 Relational Operations 名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 11 页 -附录 2 SQL Server 2005的使用0.启动 SQL server 服务：在程序菜单上选Sql server 2005 的配置管理点右键启动名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 11 页 -1.在程序菜单中启动 SQL sever 2005 express 2.点击“连接对象资源管理器”按钮，连接服务器（如设置开机就连接的无需做此步）点击启动连接对象资源管理器按钮注 意：服 务 器 可 能 是soft-c23sqlexpress(下拉菜单去选),c23 为机位号；或者用.sql2005名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 11 页 -3.新建数据库，修改相关参数鼠标指向“数据库”，点右键选“新建数据库”修 改 数 据库 文件 保 存路径（在 e盘先建好文件夹）输入数据库名名师资料总结-精品资料欢迎下载-名师精心整理-第 8 页，共 11 页 -4.新建表，选定新建的数据库，指向“表”，点右键，“新建表”，输入各列名及类型，在右属性框修改表名。名师资料总结-精品资料欢迎下载-名师精心整理-第 9 页，共 11 页 -5.点“新建查询”，在编辑框中输入sql 语句，完成建表、查询语句的编辑；点“执行按钮”执行，观察结果。点“存盘”按钮可以对sql 脚本存盘。也可通过配置管理器来启动数据库服务器。名师资料总结-精品资料欢迎下载-名师精心整理-第 10 页，共 11 页 -T-SQL建立数据库：CREATE DATABASE csu_tcg ONPRIMARY(NAME=csu-tcg,FILENAME=e:MSSQLDATAcsu-tcg.mdf,-文件夹要先建好SIZE=3072KB,-单位可以 M，K FILEGROWTH=1024KB)LOG ON(NAME=N csu-tcg_log,FILENAME=N c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAcsu-tcg_log.ldf,SIZE=1MB,FILEGROWTH=10%)GO /*go为事务提交一段SQL 脚本（是隐形事务）的结束标志符。SQL Server 应用程序可将多条Transact-SQL 语句作为一个批处理发给SQL Server 去执行。在此批处理中的语句编译成一个执行计划。程序员在 SQL Server 实用工具中执行特定语句，或生成Transact-SQL 语句脚本在SQL Server 实用工具中运行，用GO 来标识批处理的结束*/名师资料总结-精品资料欢迎下载-名师精心整理-第 11 页，共 11 页 -