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 - Cr
16、eate Views1. Create a view called music that will select title, classification, name from albums, artists where artists.artno = albums.artno.Show your work below by printing screen.2. Create another view called inventory by executing the script file, crview.Show your work below by printing screen.3.
17、 Information about views can be found in the SYSCAT.VIEWS and SYSCAT.TABLES views. Tables and views for the current user can be shown with a LIST TABLES statement. A TYPE of V is a view. Issue one of the following statements and verify your views are listed. LIST TABLES statement OR Select columns T
18、ABSCHEMA, TABNAME, and TYPE from the SYSCAT.TABLES view with a TABSCHEMA of student OR Select columns VIEWSCHEMA, VIEWNAME from SYSCAT.VIEWS with DEFINER of studentShow your work below by printing screen.Section 4 - Create Alias1. Create an alias called singers for the ARTISTS table, and an alias ca
19、lled emptystock for the REORDER table.Show your work below by printing screen.2. Information about aliases can be found in the SYSCAT.TABLES view. Tables, views, and aliases for the current user can also be shown with the LIST TABLES statement. A TYPE of A is an alias. Issue one of the following sta
20、tements and verify your aliases are listed. LIST TABLES statement OR Select columns TABNAME and TYPE from the SYSCAT.TABLES view with a TABSCHEMA of student.Show your work below by printing screen.Section 5 - Add Referential Integrity1. Alter the ALBUMS table and define referential integrity relatio
21、nships it has with other tables. Add a primary key on the itemno column Add a foreign key called fkartno on the artno column which references a primary key in the ARTISTS table The delete rule between ARTISTS and ALBUMS should be delete cascadeShow your work below by printing screen.2. Now alter the
22、 STOCK table to define its referential integrity relationships, by executing the script file, crri.Show your work below by printing screen.3. Information about referential integrity can be found by accessing the SYSCAT.REFERENCES view. Table dependencies can also be accessed through the SYSCAT.TABLE
23、S view. Enter an interactive SQL statement to select CONSTNAME, TABNAME, REFTABSCHEMA, REFTABNAME, DELETERULE from the SYSCAT.REFERENCES view with a TABSCHEMA of student.Show your work below by printing screen.4. Run a select to retrieve the first 18 characters of TABNAME, and the PARENTS and CHILDR
24、EN columns from SYSCAT.TABLES where the TABSCHEMA is equal to your userid. Order the results by TABNAME and direct your output to a file named richk.file. The SQL can be found in a file named richk.sql. Check your output against the richk.master file. Ensure that there are no differences between you
25、r output and the master output, or the following labs may not work correctly! Show your work below by printing screen.Section 6 - Add Check Constraints1. Alter the STOCK table and add a check constraint to it. The check constraint should have the following characteristics. It should be named cctype.
26、 The business rule it should enforce is to only allow the values of D, C, or R in the type column. Make sure the alphanumeric literals are typed in uppercase letters.Show your work below by printing screen.2. Information about check constraints can be found by accessing the SYSCAT.CHECKS,SYSCAT.COLC
27、HECKS, SYSCAT.TABCONST, and SYSCAT.TABLES views. Issue SQL to: Select CONSTNAME, TABNAME, COLNAME from the SYSCAT.COLCHECKS view. Select CONSTNAME, TABNAME, TYPE from the SYSCAT.TABCONST view. If the TYPE = “K”, then it is a check constraint. If the Type = “P”, then it is a primary key. If the Type
28、= “F”, then it is a foreign key.Show your work below by printing screen.3. To check your work, run a select statement that selects the first 100 characters of the TEXT column from SYSCAT.CHECKS where CONSTNAME = CCTYPE, and direct your output to a file named ckchk.file. The SQL can be found in a fil
29、e named ckchk.sql. Check your output against the ckchk.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.Section 7 - Create a Trigger 1. Create a trigger which has the foll
30、owing characteristics. It should be named reorder It should fire after an update of the qty column on the STOCK table, if the new value of qty is = 5 New should be referenced as n The triggered action should insert the values n.itemno and current timestamp into the REORDER table For each row mode db
31、2sqlShow your work below by printing screen.2. Information about triggers can be found by accessing the SYSCAT.TRIGGERS and SYSCAT.TRIGDEP views. Enter interactive SQL to research the REORDER trigger: Select TRIGNAME, TABNAME, and TRIGEVENT columns from the SYSCAT.TRIGGERS view. TRIGEVENT describes
32、the event that fires the trigger as I for insert, D for delete, or U for update. Select TRIGNAME, BTYPE, BSCHEMA, BNAME columns from SYSCAT.TRIGDEP view. BTYPE and BSCHEMA give the name of the objected depended on by the trigger. BTYPE describes the type of base object as A for alias, F for function
33、 instance, T for table, or V for view.Show your work below by printing screen.Section 8 - OPTIONAL - Working with XML 1. If the SAMPLE database has not yet been create, create it now.Show your work below by printing screen.2. Connect to the SAMPLE database.Show your work below by printing screen.3.
34、Use XQUERY and the db2-fn:xmlcolumn function to retrieve all of the XML documents from the customer tables info column.Show your work below by printing screen.4. Now use XQUERY with SQL and db2-fn:sqlquery function to retrieve all of the XML documents from the customer tables info column.Show your w
35、ork below by printing screen.5. Use XQUERY and the db2-fn:sqlquery function to retrieve, from the INFO column in the CUSTOMER table, all customers where the customer ID (column CID) equals 1002.Show your work below by printing screen.6. Use the DB2 Control Center and the XML Document View to display the CUSTOMER.INFO document in the first row of the table. View the document in both Tree view and Source view.Show your work below by printing screen.
