DBS201 Lab 7

Purpose - In this lab you will learn how :

-To delete a View

-To add calculated columns to a View (or Table).

-to give others access to your database (also known as authority or permissions). This is often the responsibility of the Database Administrator (DBA) so consider yourself the DBA of your database!

-To have the system insert default values.

*** Reminder: In general each lab builds on the work done in the previous lab. It is important that you complete each lab whether or not you do it in time for marks or not!

  1. In Lab 6you created a view called Order_Info which produced a report that looks like the following:

    ....+....1....+....2....+....3....+....4....+....5....+....6....+....7..
  1. It would be nice if we could see a total value for each order line. You have learned in the normalization process that we do NOT store calculated values. Instead of taking up valuable storage space, we can create computed fields using SQL whenever we need them. Unfortunately, we cannot alter a view once it has been created. Because our VIEW does not actually contain data, we can simply delete it and recreate it to suit our new requirements. To delete a View, use the DROP VIEW command. Delete the Order_Info view now.
  2. Find and retrieve your previous CREATE VIEW command. Prompt the command. Our calculated field is a new column in our view; therefore, we will add this new column by placing our cursor after the last existing column (QUOTED_PRICE), inserting a comma then F6 to add a line. Our new column will be number ordered multiplied by the quoted price. Press F4 and select Number_Ordered, enter a *, then prompt again and select Quoted_Price.
  3. We must give our new column a name, call it TOTAL. Therefore, add to the end of the selected columns AS TOTAL. Your screen should look like this:
    Specify CREATE VIEW Statement

Type subselect information. Press F4 for a list.

FROM tables ...... PREMIERE.ORDERLINE, PREMIERE.ORDERS

SELECT columns . . . . . PREMIERE.ORDERS.CUSTOMER_NUMBER,

PREMIERE.ORDERS.ORDER_NUMBER,

PREMIERE.ORDERLINE.PART_NUMBER,

PREMIERE.ORDERLINE.NUMBER_ORDERED,

PREMIERE.ORDERLINE.QUOTED_PRICE,

PREMIERE.ORDERLINE.NUMBER_ORDERED *

PREMIERE.ORDERLINE.QUOTED_PRICE as total

WHERE conditions . . . . PREMIERE.ORDERLINE.ORDER_NUMBER =

PREMIERE.ORDERS.ORDER_NUMBER

GROUP BY columns . . . .

More...

Type choice, press Enter.

DISTINCT rows in result table ...... N Y=Yes, N=No

5.Press enter to execute the create command. Run a Select statement to view the data. If you have a small monitor you may not be able to see the Total column. Use F20 to shift the screen. Our column names are taking up too much room. If we shorten them we can get our whole report on one screen.

6.Let’s recreate the view again. This time we will shorten some of our column names using the AS keyword. Change the selection of columns adding the AS keyword for the order number, customer number, part number and number ordered columns as follows:
SELECT columns . . . . . PREMIERE.ORDERS. CUSTOMER_NUMBER as CUST_NO,

PREMIERE.ORDERS. ORDER _NUMBER as ORD_NO,

PREMIERE.ORDERLINE.PART_NUMBER as PART_NO,

PREMIERE.ORDERLINE.NUMBER_ORDERED as QTY,

PREMIERE.ORDERLINE.QUOTED_PRICE,

PREMIERE.ORDERLINE.NUMBER_ORDERED *

PREMIERE.ORDERLINE.QUOTED_PRICE as total
7.Run a select statement again. How does the report look now?
Note that if your report is very long, then you have probably forgotten to use the WHERE clause to match up your files!!

Part B:

  1. Create the following tables in your PREMxxxAB collection. (Don’t forget the constraints!)

Column / Data
Type / Length / PK /

FK

/ NOT
NULL / Unique / Check / Default

Table: Student

Campus_no / Numeric / 1 / Y
Stud_no / Numeric / 3 / Y
Stud_lname / Char / 15
Stud_fname / Char / 15
Stud_pgm / Char / 3 / Pgm(pgm_id)
Locker_no / Numeric / 3 / Y

Table: Pgm

Pgm_id / Char / 3 / Y / CPA, CPD
Pgm_name / Char / 40 / Y
No_semesters / Numeric / 1 / Y / > = 0 / 6

2.Insert the following data into your tables:

Student

1 / 123 / Jane / Wong / CPD / 900
1 / 456 / David / Kasim / CPA / 905

Pgm

CPD / Computer Programming / 4
CPA / Computer Programming and Analysis

If you had trouble inserting the last row, read on! If you had used the INSERT command and prompted, the prompted command expects you to enter a value for each column. But we wanted the system to insert the default value for No_Semesters. We cannot use prompting in this situation.

Instead we must use the INSERT command and specify exactly which fields we wish to supply values for. Enter the command as follows:
INSERT INTO PREMxxxAB.PGM (Pgm_id, Pgm_name)
VALUES(‘CPA’, ‘Computer Programming and Analysis’)
Now run a select command on the PGM file. What is listed as the No of Semesters for the CPA program?

3.What happens if you enter the following data into the Student table?
(1, 321, ‘Joe’ , ‘Chen’, ‘CPC’, 906)
(1, 654, ‘Cindy’, ‘Wong’, ‘CPA’, 905)

If you did NOT have trouble inserting these 2 records, there is a problem with your constraints on this table!
Part C. Permissions (using the GUI interface, iSeries Navigator)

(Make sure you are using iSeries Navigator)

To give permission to use your database to another user, you must give permission for both the collection AND the tables and/or views. To change the permissions on the collection, right click on the collection name and select Permissions. Click Add and enter your instructor’s userid in the input field at the top of the window. Click OK. You should then see your instructor’s userid added to the list under the Name column. Click OK again.
Lydia Li’s user ID: Lydia_Li

  1. Repeat this process for each of your 2 tables. You may be asked to signon again!

To obtain a mark for this lab, you must:

  1. Grant permission to your instructorfor your collection PREMxxxAB.
  2. Grant permission to your instructor for the Student and Pgm tables.
  3. Grant your instructor permission to the Order_Info view from Part A, Step 7. As well, print a report of the data in this view and hand it in.

The report should look like this:

Note to Instructors: You should be able to add the Students collection to your list of collections to be viewed. Then you should be able to view their Student and Pgm tables as well as the Order_Info view. If you have an authority error, then the student has not given the correct permission. They must give permission to both the collection and the tables/views within the collection.