数据库系统-tcg-实验-1-数据库与表的创建.doc

《数据库系统-tcg-实验-1-数据库与表的创建.doc》由会员分享，可在线阅读，更多相关《数据库系统-tcg-实验-1-数据库与表的创建.doc（11页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、数据库原理实验指导书实验一 数据库、表的基本操作一、学时学时二、实验类型验证、设计三、实验目的1.熟练掌握SQL Server 200X的使用方法。2.熟练掌握使用SQL Server 200X的对象资源管理器以及SQL 的DDL完成数据库的创建、删除和连接方法；数据表的建立、删除；表结构的修改。3.加深对表的实体完整性、参照完整性和用户自定义完整性的理解。四、需用仪器、设备微机；Windows2000/xp操作系统；SQL SERVER 200x五、实验准备1 熟悉SQL SERVER 工作环境；2 复习有关数据库、表的建立、修改和删除的SQL语言命令；六、实验方法及步骤 熟悉有关SQL S

2、ERVER的图形界面工作环境（参见后附录2 SQL Server 2005的使用） 创建数据库或连接已建立的数据库 在当前数据库上建立新表 定义表的结构：用命令形式：用SQL SERVER提供的对象资源管理器（企业管理器）以交互方式进行5 将以上表的定义以.文件的形式保存在磁盘上。6 修改表的结构7 删除表的定义七、实验内容 1用企业管理器与SQL语句分别完成Part1 and part 2 of SSD7 exercise 1。2在以下实验中，使用学生-课程数据库，它描述了学生的基本信息、课程的基本信息及学生选修课程的信息。用对象资源管理器（企业管理器）与SQL语句分别完成。（1） 创建学生

3、-课程数据库，数据文件名为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

4、） 删除表SC，利用磁盘上保存的.SQL文件重新创建表SC。（11）备份数据库，再还原。八、实验思考1、 定义表结构是对数据模型的那个要素进行描述，对主键有什么要求？2、修改表结构时，能否修改已定义属性的参数？新增加的属性列能否定义为非空？为什么？附1：实验报告基本格式实验名称：一、实验目的二、实验环境三、实验内容四、实验结果（写出每次实验步骤以及结果）SSD7 exercise 1Please answer the following question(s).Relational DatabasesDesign the following two tables and turn in you

5、r 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 Ia. Identify the keys in your tables as follows. For this exercise, ignore the performance co

6、nsiderations 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

7、 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 submis

8、sion in a file named BookPublisher.html. -Part IIWrite 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.a. Write the DDL statements to create the two tables using the CREATE TABLE statement. b. Be sure t

9、o 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

10、 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

11、 Systems). -Part IIIIn 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

12、 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 M

13、emNo = 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 2Pl

14、ease answer the following question(s).Relation schemas are presented in fig1.SQLRun 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 ta

15、ble, 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 Ye

16、ar 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.

17、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 bo

18、oks 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 borro

19、wed the book titled Pitt Roads. Fig1. an example in 1.2.2 Relational Operations附录2 SQL Server 2005的使用0.启动SQL server 服务：在程序菜单上选Sql server 2005 的配置管理点右键启动 1.在程序菜单中启动SQL sever 2005 express点击启动2.点击“连接对象资源管理器”按钮，连接服务器（如设置开机就连接的无需做此步）连接对象资源管理器按钮注意：服务器可能是soft-c23sqlexpress(下拉菜单去选),c23为机位号；或者用.sql20053.新建数据

20、库，修改相关参数鼠标指向“数据库”，点右键选“新建数据库”输入数据库名修改数据库文件保存路径（在e盘先建好文件夹）4.新建表，选定新建的数据库，指向“表”，点右键，“新建表”，输入各列名及类型，在右属性框修改表名。5.点“新建查询”，在编辑框中输入sql语句，完成建表、查询语句的编辑；点“执行按钮”执行，观察结果。点“存盘”按钮可以对sql脚本存盘。也可通过配置管理器来启动数据库服务器。T-SQL建立数据库：CREATE DATABASE csu_tcg ON PRIMARY ( NAME = csu-tcg, FILENAME =e:MSSQLDATAcsu-tcg.mdf , -文件夹要先

21、建好SIZE = 3072KB , -单位可以M，KFILEGROWTH = 1024KB ) LOG ON ( NAME = Ncsu-tcg_log, FILENAME = Nc: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 来标识批处理的结束*/