DBS201 Lab 5 – Database and Table Creation
Purpose:
- To learn to navigate the various screens on the i-series.
- To create a collection (a collection is a database)
- To create tables for your collection using SQL
- To designate one or more attribute(s) as the primary key for a table
Some iSeries Q and A
Q1:How do I get to the i-series Navigator?
Ans:Click on the i-series icon on the desktop.
Click on System I Navigator.
Click on the + sign beside zeus.senecac.on.ca
Q2:How do I get to the “green screen” from the Navigator?
Ans:Right-click on zeus.senecac.on.ca
Click on Display Emulator.
Q3:How do I get to the “Enter SQL Statements” screen from the “System I Main Menu of the “green screen”?
Ans:On the command line type STRSQL and press <Enter>.
Q4:I get an error message that I don’t understand, what should I do?
Ans:Place the cursor on the message and press <F1>. The system will give more informationabout the error.
You should use Internet Explorer for this lab so that all images display
correctly.
Begin by signing on and proceeding to the SQL screen
1.At the SQL screen, Press Shift + F1 and SELECT output value = 1. This screen will allow you to ‘Change Session Attributes’ if they do not currently meet your needs. There are 3 items that you are interested in at this time. The first item is statement processing. The underlined selection should be *RUN. If one of the other choices is underlined, change the attribute to *RUN. The second item is SELECT output. (you arechecking your session attributes to make sure that output will be displayed on your screen rather than being routed to the printer. The third item that you should be concerned with is the .last of the session attributes,’ naming convention ‘. Naming convention should be set to *SQL.. If it is set to *SYS, change to *SQL. Once you have made your changes, press <ENTER> until you return to the SQL screen.
2.You will create a collection or databasesimilar 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 catalog you would get the following message:PAYROLL in *N type *LIB already exists. This happens when another user has already used the name PAYROLL as a database name.
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 WS201A01 account should be PREXA01. A maximum of 10 characters can be used in a collection name.
Type: CREATE COLLECTION PREZxxx (where xxx are the last three characters of your ID) and press Enter.
example: CREATE COLLECTION PREZB17
(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. This is part of Transaction Control Language (TCL).
Now delete your newly created collection with the DROP command as follows: DROP COLLECTION PREXxxx
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.
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.
To create database tables in your account, you use the SQL CREATE TABLE command to create the table and describe its layout. As is shown below, tables that are created require that you list a column name, define its data type, and indicate whether a value must be entered for that attribute, or if it can be left empty.
Column Name / Data Type (and size) / Allow NullsThe Column Name column lists the name of the table attribute(s). The data types that you will most often encounter are listed below:
- INTEGER - uses integers, numbers without a decimal part. You can use the contents of INTEGER fields for calculations.
- DECIMAL(p,q) – Stores a decimal number p digits long with q of those digits being decimal places. For example, DECIMAL(5,2) represents a number with 3 places to the left of the decimal point, and 2 places top the right of the decimal point.
- CHAR - Stores a character string n characters long. You use the CHAR type for fields that contain letters and other special characters and for numbers that will not be used for calculations. For example, since neither the sales rep number nor the customer number will be used in any calculations, it is best to assign both of them the CHAR data type.
- DATE – Stores dates in the form DD-MM-YYYY or MM/DD/YYYY. To enter date data , key in the form of YYYY_MM_DD.
Allowing NULLS means that it is possible for the field to be EMPTY and not destroy the integrity of the database. A NULL does not mean that the value of the field is 0 (zero).
Given below are the SalesRep and Customer tables from the Premiere database. Since we plan to copy data into these tables in a later lab, we need to define the data with respect to column name, data type and nulls.
SalesRep
Sales Rep Number / Last Name / First Name / Street / City / State / Zip Code / Commission / Rate3 / Jones / Mary / 123 Main / Grant / MI / 49219 / $2,150.00 / 0.05
6 / Smith / William / 102 Raymond / Ada / MI / 49441 / $4,912.50 / 0.07
12 / Diaz / Miguel / 419 Harper / Lansing / MI / 49224 / $2,150.00 / 0.05
14 / Phillips / Fred / 22 Castle Blvd. / Detroit / MI / 48777 / $5,500.00 / 0.06
22 / Wong / Terry / 33 Biscayne Blvd. / Smallville / MI / 48777 / $220.00 / 0.05
These are the values that we will use to create the salesrep table:
Column Name / Data Type (and size) / Allow NullsSales_rep_number / Char(2) / Not null
Last_name / Char(15) / Not null
First_name / Char(15) / Not null
Street / Char(15) / Not null
City / Char(15) / Not null
State / Char(2) / Not null
Zip_code / Dec(5,0) / Not null
Commission / Dec(7,2) / Not null
Rate / Dec(3,2)_ / Not null
The command as it is written in SQL is:
CREATE TABLE Prezxxx.Rep
(Sales_rep_numCHAR(2) not null,
Last_NameCHAR(15) not null,
First_NameCHAR(15) not null,
StreetCHAR(15) not null,
CityCHAR(15) not null,
StateCHAR(2) not null,
Zip_CodeDecimal(5,0) not null,
CommissionDECIMAL(7,2) not null,
RateDECIMAL(3,2) not null)
(Note: there is no rule that says you must write the SQL in the format given above. It is simply written that way to make the command more readable.)
Press <Enter>, and the table should be created.
Now, let us look at the Customer table:
Customer
Customer Number / Last Name / First Name / Street / City / State / Zip Code / Balance / Credit Limit / Sales Rep Number124 / Adams / Sally / 481 Oak / Lansing / MI / 49224 / $818.75 / $1,000.00 / 3
256 / Samuels / Ann / 215 Pete / Grant / MI / 49219 / $21.50 / $1,500.00 / 6
311 / Charles / Don / 48 College / Ira / MI / 49034 / $825.75 / $1,000.00 / 12
315 / Daniels / Tom / 914 Cherry / Kent / MI / 48391 / $770.75 / $750.00 / 6
405 / Williams / Al / 519 Watson / Grant / MI / 49219 / $402.75 / $1,500.00 / 12
412 / Adams / Sally / 16 Elm / Lansing / MI / 49224 / $1,817.50 / $2,000.00 / 3
522 / Nelson / Mary / 108 Pine / Ada / MI / 49441 / $98.75 / $1,500.00 / 12
567 / Dinh / Tran / 808 Ridge / Harper / MI / 48421 / $402.40 / $750.00 / 6
587 / Galvez / Mara / 512 Pine / Ada / MI / 49441 / $114.60 / $1,000.00 / 6
622 / Martin / Dan / 419 Chip / Grant / MI / 49219 / $1,045.75 / $1,000.00 / 3
These are the values that we will use to create the customer table:
Column Name / Data Type (and size) / Allow NullsCustomer_number / Char(3) / Not null
Last_name / Char(15) / Not null
First_name / Char(15) / Not null
Street / Char(15) / Not null
City / Char(15) / Not null
State / Char(2) / Not null
Zip_code / Dec(5,0) / Not null
Balance / Dec(6,2) / Not null
Credit_limit / Dec(6,2) / Not null
Sales_rep_number / Char(2) / Not null
And in SQL (on the green screen), the command will be written as follows:
Create table PREZxxx.customer
( customer_number char(3) not null,
last_namechar(15) not null,
first_namechar(15) not null,
streetchar(15) not null,
citychar(15) not null,
statechar(2) not null,
zip_codedec(5,0) not null,
balancedec(6,2) not null,
credit_limitdec(6,2) not null,
sales_rep_numberchar(2) not null)
Press enter, and the table should be created.
5.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.
Drop tables prezxxx.customer, and prezxxx.salesrep.
6. Move your cursor back to the create table statement for salesrep, and press <F9>. The table should reappear. On the line for sales_rep_number, delete ‘not null’ and replace it with the words ‘primary key’. Move to the end of the command and press <enter> again. The table should be recreated; this time with a primary key.
7. Repeat the same procedure for the customer table. That table should be created with a primary key.
8. Once you have created the two tables with a primary key, call me to show me the successful create command on your screen, so I can give you a mark for this lab.