《DB2实验报告.doc》由会员分享，可在线阅读，更多相关《DB2实验报告.doc（27页珍藏版）》请在金锄头文库上搜索。
1、吉林大学DB2实验报告班级： 姓名：学号：姓名学号实验项目3. Create objects实验性质演示性实验 验证性实验 操作性实验 综合性实验实验地点机器编号指导教师实验时间年 月 日 时 分一、实验综述1. 实验目的及要求This exercise is an online lab which creates objects used in a database.At the end of the lab, students should be able to: Create tables Add referential integrity constraints to a table
2、Create indexes Add check constraints to a table Create views Add triggers to a table Create an alias Access System Catalog information about objects Retrieve an XML document2. 实验设备、软件PC,windowsXP Professional,DB29Express-c二、实验过程（实验步骤、记录、数据、分析）Section 1 - Create Tables1. Create your ARTISTS table. Th
3、e table should have the following characteristics. Table schema of student Table Name of artists Regular data to go in table space dms01 Indexes to go in table space dms02 Long data to go in table space dms03 The column artno should be defined as a primary key Columns defined like the following: ( a
4、rtno smallint not null, name varchar (50), classification char (1) not null, bio clob (100K) logged compact, picture blob (500k) not logged compact)Show your work below by printing screen.手动创建表空间dms01,dms02,dms03，如下：执行SQL语句结果如下：2. Verify the creation of your ARTISTS table and the table description.
5、Important: Ensure the definition of your table is correct. It could cause unexpected failures in later labs if your definitions are not done correctly.Show your work below by printing screen.3. You will create the remainder of the tables required using a script file called crtables. For Windows, it
6、is located in the C:labfilescf23 directory.Examine the DB2 statements in the file and answer the following questions. In which table space will both the indexes and data for table STOCK be placed? For CONCERTS? For REORDER?4. Execute the crtables script to create the tables.Show your work below by p
7、rinting screen5. Verify that all tables (ALBUMS, STOCK, CONCERTS, and REORDER) were created successfully.Show your work below by printing screen.6. Information about each column in a table can be found by accessing the SYSCAT.COLUMNS view. Enter an interactive SQL statement to select the columns TAB
8、NAME, COLNAME, and TYPENAME from SYSCAT.COLUMNS for all tables with the TABSCHEMA of student, order the data by TABNAME and COLNO, and pipe the output to a file named tabchk.file.Show your sql statements and your work below by printing screen for the last step.7. Check your output against the tabchk
9、.master file. Ensure that there are no differences between your output and the master output or the following labs may not work correctly!Show your work below by printing screen.8. Information about a tables definition to a table space can be found by accessing the SYSCAT.TABLES view. Enter an SQL s
10、tatement to select the columns TABNAME, TBSPACE, and INDEX_TBSPACE from SYSCAT.TABLES for all tables with the TABSCHEMA of student, order the data by TABNAME, and pipe the output to a file named tbschk.file.Show your sql statements and your work below by printing screen for the last step.9. Check yo
11、ur output against the tbschk.master file. Ensure that there are no differences between your output and the master output, or the following labs may not work correctly!Show your work below by printing screen.10. Grant select privilege on tables that are owned by student to public by executing a scrip
12、t file, grants (an operating system command file) for local administration. Show your work below by printing screen.Section 2 - Create Indexes 1. Create an index called ITEM on the ITEMNO column in STOCK table.Show your work below by printing screen.2. Create a unique index called ITEMNO on the ITEM
13、NO column in ALBUMS table. Show your work below by printing screen.3. Select the information from the catalog tables about your indexes. The columns you should select are the first 18 characters of TABNAME, UNIQUERULE, the first 18 characters of INDNAME, and the first 30 characters of COLNAMES from
14、SYSCAT.INDEXES, where INDSCHEMA is student, order by TABNAME and INDNAME. If UNIQUERULE = U, then only unique values are allowed. If UNIQUERULE = D, then duplicate values are allowed. If UNIQUERULE = P, then it is a Primary key. Show your work and your new indexes below by printing screen.4. Use the
15、 select you executed in the previous step and route the output to indchk.file. Check your results against indchk.master. Ensure that there are no differences between your output and the master output, or the following labs may not work correctly!Show your work below by printing screen.Section 3 - Create Views1. Create a view called music that will select title, classification, name from albums, artists where artists.artno
