DBS201 Lab 4 Database and Table Creation

DBS201 Lab 4 – Database and Table Creation

<!doctype html public "-//w3c//dtd html 4.0 transitional//en"<div class=Section1<div class="MsoTitle">Purpose:

To create a collection (or database)
To create tables for your collection using SQL
To designate an attribute(s) as the primary key for a table

</div<div class="MsoNormal">

</div<div class="MsoNormal">You should be signed on and in SQL.</div>

1. Sign on and Press Shift + F1 and make sure the select output value is 1 (check your session attributes to make sure that output will be displayed on your screen rather than being routed to the printer).

2.You will create a collection or database similar to the Premiere collection. On the iSeries platform a collection is similar to a library. All collection or library names must be unique. (If you attempt to create a collection with the name PAYROLL and it is already defined in the system , you would get the following message:PAYROLL in *N type *LIB already exists. You would also need proper authority in order to see this PAYROLL collection that someone else has created.)

Because many students will use similar collection or library names, you are to include the last three characters of your ID and shorten the PREMIERE name to ensure the collection name will be unique. For example, the collection name used by the owner of the DC201A01 account should be PREMA01AB. A maximum of 10 characters can be used in a collection name.

Type:

CREATE COLLECTION PREMxxxAB(where xxx are the last three characters of your ID) and press Enter.

(The creation of your collection may take several minutes. Some of the time used in creating a collection is devoted to the creation and set-up of some special objects called a journal and journal receiver. These objects record the before-image and after-image of any record that is changed in the collection. This allows recovery of the database due to hardware or software failures and provides an audit trail when investigating unauthorized changes to tables.)

If you want to delete your newly created collection, you would use the DROP command as follows:

DROP COLLECTION PREMxxxAB

<div class="MsoNormal">Again, you may have a bit of a wait. A message should be displayed. Write it below.
</div<div class="MsoNormal">______</div>

<div class="MsoNormal">The message is waiting for you to reply with either an I or a C.</div<div class="MsoNormal">Enter an I to indicate you want to continue to remove your collection(remember you can place your cursor on the message and use the function key F1 to get helpful information on the options). If you had entered a C the DROP action would be cancelled.</div>

<div class="MsoNormal">3. Now recreate the collection by moving the cursor up to the CREATE COLLECTION statement and pressing F9 to recall your previous statement to create your collection and re-execute the command.</div>

<div class="MsoNormal">4. You are now to create two tables similar to the ones in the PREMIERE collection. Both SALESREP and CUSTOMER need to have the same column definitions in your collection as the ones in the PREMIERE collection. This will make copying of the data easier in a later lab.</div>

Type CREATE TABLE PREMxxxAB.CUSTOMER (where xxx is from your ID) and press F4.

<div class="MsoFooter" style="tab-stops:.5in"</div<div class="MsoNormal">TAB to the first Column input field. The first column in the customer table is to be Customer_Number. Type Customer_Number and then use the Tab key to advance to the Type parameter. Press F1 to determine the six numeric data types that can be used here.

List these data types below:</div>

d. ______e.______f.______
However you are not to use a numeric data type for customer number because this field should not be used in any calculations. Most primary key fields are defined as character data so they cannot mistakenly be used in arithmetic expressions.

Enter CHAR for the column data type and a length of 3. Do not press enter until you have entered all the columns and selected the primary key!

The last column (Nulls) uses a number to specify an option. What does a ‘3’ in the Nulls column represent?

______

Unless otherwise specified, what are the defaults for the following datatypes:
Char ______Date______Numeric ______

If you want to specify a different default, you can press the Function key that allows you to display more attributes. What is this key: ______Tryusing this key.

Define the following fieldsas character fields using the information listed below:

Name Length

Last_Name 15
First_Name 15

<div class="MsoNormal">StreetName 16</div>

<div class="MsoNormal">State 2</div>

<div class="MsoNormal">Sales_Rep_Number 2</div<div class="MsoNormal">

You are to create the numeric column definitions next. You will not be able to fit all these columns on one screen. What keys are used:to insert a line ______, and to see the new line ______.</div>

Two of the data-types to be used are Numeric and Decimal.Numeric is Zoned Decimal and Decimal is Packed Decimal. We will use the data-type of Decimal because less storage space is required and arithmetic operations execute faster using packed decimal fields.

Use the information listed below to define these numeric fields:

Name Type Length Scale (# of Decimal Positions)

<div class="MsoNormal">Zip_Code Decimal 5 0</div>

<div class="MsoNormal">Balance Decimal 6 2</div>

<div class="MsoNormal">Credit_Limit Decimal 5 0</div<div class="MsoNormal">

Every table must have a primary key. You are now to specify the primary key for the Customer table. You do this by defining a primary key constraint on the Customer table. A constraint restricts values that can be entered into this table.</div>

Tab down to the Table constraint parameter, change the 'N' to 'Y' and press Enter.

Table CONSTRAINT ...... Y

<div class="MsoNormal">Now press enter to Add the constraint.Identify the constraint type as Primary Key and press the Enter key. You may now name the constraint and choose the column for this constraint.</div<div class="MsoNormal">Name this constraint Customer_ PK (the convention used for constraint names is the table name plus PK for primary key).</div<div class="MsoBodyText"> Either type in the correct column name or use F4 to locate the column.</div<div class="MsoNormal">Press Enter until you receive a message stating that the table has been created.</div<div class="MsoNormal">If you have errors, they will be highlighted and you must correct them. An example of an error would be an incorrect spelling of your collection name. Write down the SQL statement that created the table.</div>

______

Note: You have now created a table in your PREMxxxABcollection. You will not be able to find this collection by prompting a FROM FILES clause for the SELECT statement. This is because the collection is not part of your library list of collections. If you need to refer to this table you must qualify the table name with the collection name as shown: PREMxxxAB.Customer

5. Now delete the table by using the DROP command. (don't worry!) Write down the SQL statement that deleted the table:

______

If you had incorrectly defined a table (for example, you forgot to include a column) and you have not yet entered any data, it would be easy to delete the table, retrieve the successfully run CREATE TABLE statement, make any necessary corrections and rerun the statement.

Retrieve the statement used to create the CUSTOMER table.

Assume that you want to make changes to the definition of the column named STREETNAME that has a column length of 16. Let's change the name of this column to STREET and the length to 15.

Recreate the Customer table with the adjusted STREET column definition

<div class="MsoNormal"</div<div class="MsoNormal">6. The SALESREP table needs to be created also.</div<div class="MsoNormal">Use the following information to create the SALESREP table. You must also specify a primary key constraint on the column of Sales_Rep_Number.</div>

COLUMN DATA_TYPE LENGTH NUMERIC_SCALE

CITY CHAR 15

COMMISSION DECIMAL 7 2

FIRST_NAME CHAR 15

LAST_NAME CHAR 15

RATE DECIMAL 3 2

SALES_REP_NUMBER CHAR 2

STATE CHAR 2

STREET CHAR 15

ZIP_CODE DECIMAL 5 0

7. You now are to place some data in each table. You are to add a row to the Customer table containing your name and a row to the SalesRep table containing your instructor’s name:

Type INSERT INTO PREMxxxAB.CUSTOMER (where xxx are the last 3 characters of your id) and press F4.

<div class="MsoNormal">Accept the defaults on the first screen and press Enter.</div<div class="MsoNormal">On the second screen enter all the values contained in the screenshot below but substitute your name for LAST_NAME and FIRST_NAME.</div>

Column Value

Customer_Number ‘888’
Last_Name ‘your name’
First_Name ‘your name 2’

<div class="MsoNormal">StreetName </div> ‘200 Pond St.’

<div class="MsoNormal">City</div> ‘Toronot’

<div class="MsoNormal">State ‘On.’</div>

<div class="MsoNormal">Sales_Rep_Number ‘99’

<div class="MsoNormal">Zip_Code 11111</div>

<div class="MsoNormal">Balance2000</div>

<div class="MsoNormal">Credit_Limit 3000

Press Enter to submit.

Now enter a SALESREP row using your instructor’s name and a Sales_Rep_Number of ‘99’ and the information shown in the screenshot below.

Column Value

CITY ‘Toronto’

COMMISSION 44444

FIRST_NAME ‘InstName1’

LAST_NAME ‘InstName2’

RATE .15

SALES_REP_NUMBER ‘99’

STATE ‘ON.’

STREET ‘1 Seneca@York’

ZIP_CODE 11111

To hand-in: Run SELECT statements to display the contents of the tables you created today and print. Don't forget to change session defaults to send the results to the printer rather than the screen. Hand in the 2 printouts.

Lab 4Summary

Collection names must be unique on the iSeries platform.
A CREATE command produces a new collection, table or other types of SQL object. A Primary Key constraint should always be specified for a table.
Function keys make it easier to enter SQL code: F4 to prompt for long column names and save on typing, F15 to split a line when more room is needed and F6 to insert a line.

Deletion of collections, tables and other SQL objects is achieved with a DROP command.

There are many choices for a column data-type. Char, Decimal and Numeric are some of the more commonly used selections.