Lab 3: Constraints (Summer, 2012)
Instructions for Lab Requirements begin on page 9
FAQs
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 information about the error.
Q5: ADDLIBLE à Why do I have to re-add my schema every time I start a new session on the “Green Screen”?
Ans: This happens because the list is cleared and reset to a default list at the start of a new session.
*******************************************************************************************************
SOME HOUSEKEEPING
To add a collection to your collection list on the “green screen”
Start the terminal emulator (“green screen”) and go to SQL (STRSQL).
Using the green screen you will not have access to your PREKxxx schema. You must add the schema each time you start a new session on the green screen using the ADDLIBLE command.
Exit the SQL screen by pressing F3
Type the command ADDLIBLE PRExxxx and Press ENTER.
You should receive a message stating the collection list has been changed.
- go back into SQL by entering the command STRSQL.
Note: You must add your schema every time you start a new session on the “green screen.
The implementation of a database covers three steps to be completed in the following order:
1. Create the collection (schema) and create the tables (see lab 2)
2. Add the constraints
a) primary keys (another way to deal with primary keys is to add them AFTER a table has been created and provides the database with entity integrity)
b) foreign key(s) (which establishes a 1:M relationship between two tables and provides the database with referential integrity)
c) check constraint (which imposes conditions upon the data that can be entered in a specifc column
d) unique constraint (a condition on a column of data that does not allow data to be duplicated.)
e) Not null with default
3. Add the data to the tables
CONSTRAINTS
A. FOREIGN KEY CONSTRAINT:
1. How To Establish 1:M relationships between tables using Foreign Keys.
2. The Syntax of the command to identify the Foreign Key.
3. How the database preserves referential integrity using Foreign Keys.
B. CHECK CONSTRAINT:
1. How to Add a CHECK constraint
C. UNIQUE CONSTRAINT:
1. How to Add a UNIQUE constraint
D. Adding data to tables and what it means if the data is not accepted.
A.1. 1:M relationships between tables using foreign keys:
è Definition: A Foreign Key is a field on one table that is the Primary Key of another table.
- The relationship between SALESREP and CUSTOMER is 1:M.
- An examination of the data in the customer table (Premiere database) shows that a particular salesrep can have many customers but,
- each particular customer has one and only one salesrep.
The Foreign Key field is always placed on the "many" table when there is a 1-to-many relationship between 2 tables..
A2) Syntax of the command that sets up the Foreign Key (after the table has already been created)
For example, a Family Doctor has many Patients but a particular patient has one-and-only-one Family Doctor. The PATIENT table will have the Foreign Key.
Here is the example:
ALTER TABLE PRExxxx..PATIENT
ADD CONSTRAINT PREKA11.Doctor_FK
FOREIGN KEY (Doctor_Number)
REFERENCES DOCTOR(Doctor_Number)
Note that in DB2, every constraint must have a name (as in prexxxx.doctor_fk)
Using the above example as your guide, write theSQL script that create s the Foreign Key to relate the CUSTOMER and SALESREP tables. (just keep in mind the idea that the item that you are defining as the foreign key refers to the primary key of another table)
Alter table PRExxxx.Customer
Add constraint prexxxx.customer_FK
Foreign key (sales_rep_number)
References prexxxx.salesrep(sales_rep_number)
Press Enter and read the message above the command line to verify that the constraint has been created.
A3) How the DBMS preserves referential integrity using Foreign Keys.
è Definition: Referential Integrity: When we want to add a new Customer, we must specify a Salesrep_Number of a Salesrep that already exists on the SALESREP table.
We cannot “refer” to a Salesrep that does not exist!
Let’s try to add a new Customer. The database should refuse to add the new row.
Customer
Customer Number / Last Name / First Name / Street / City / State / Zip Code / Balance / Credit Limit / Sales Rep Number /888 / Anapurna / Raj / 201 Pond St / Toronto / ON / 11111 / 2000 / 3000 / 99 /
Remember that text and character data are enclosed by single quotes; interger and decimal data are not.
The script is written as follows:
INSERT INTO PRExxxx.CUSTOMER
VALUES
(‘888’, ‘Anapurna’, ‘Raj’, ‘201 Pond Street’, ‘Toronto’, ‘ON’, 11111, 2000, 3000. ‘99’)
Look at the data for the new salesrep:
SalesRep
Sales Rep Number / Last Name / First Name / Street / City / State / Zip Code / Commission / Rate /99 / Langer / Libby / 1York
St / Toronto / ON / 11111 / 4444 / 0.03 /
It looks like we must enter Salesrep number ‘99’ to the SALESREP table before we can add Customer ‘888’ to the Customer table. Because of the Foreign Key constraint, the database will not allow us to add a Customer who references a Salesrep who does not exist on the SALESREP table.
If we add Salesrep ‘99’ to the SALESREP table, THEN the INSERT into CUSTOMER command that failed before should work
Insert the record for Sales Rep 99 into the table:
Insert into PRExxxx.salesrep
Values
(‘99’, ‘Langer’, ‘Libby’, ‘1York St’, ‘Toronto’, ‘ON’, 11111, 4444, 0.03)
Now we’ll try the INSERT INTO CUSTOMER that failed before.
Retrieve the statement you had used to try to insert a row in the CUSTOMER table (use F9). Press Enter to run the statement. You should not get an error this time and a row should have been added to your CUSTOMER table.
As you can see the Foreign Key Constraint performs 2 functions:
i. The Foreign Key links the two tables together
ii. The Foreign Key preserves referential integrity between the 2 tables.
B1) Adding a CHECK constraint.
A check constraint establishes a rule about values that are allowed in a particular field. For example, we could establish a check constraint that allows only the values ‘M’ or ‘F’ for a gender column.
Let's add a check constraint to the SALESREP table. We could have done this when we created the table but because the table already exists we will use the ALTER TABLE command.
Example 1:Here is the syntax for a typical CHECK constraint – a range check:
ALTER TABLE PRExxxx.EMPLOYEE
ADD CONSTRAINT PRExxxx.Salary_CK
CHECK (Salary Between 20000 and 40000)
If the user attempts to change his friend’s salary to 50000, the database will refuse to make the change because it is not in the range of acceptable values. (Note: 20000 and 40000 would be accepted.)
Example 2: Another type of check constraint ensures that the field value is in a list.
ALTER TABLE PRExxxx.STUDENT
ADD CONSTRAINT PRExxxx.Grade_CK
CHECK (Grade IN (‘A’, ‘B’, ‘C’, ‘D’, ‘F’))
In this case if the user enters a Grade of ‘E’ , the database will refuse to make the change because it is not in the list of acceptable values.
.Let’s try one constraint based on a LIST :
Field Name: Rate
Table Name: SALESREP
Constraint Type: CHECK
Constraint Name: Rate_CK
Constraint Condition: RATE must equal 0.05 or 0.06 or 0.07 or 0.15
Remember: The IN keyword is used for a list of values. Write the constraint here:
______
______
Make sure that you receive the completion message "Alter completed for table SALESREP in PREKA12".
Let’s try a constraint based on a RANGE:
Field Name: Balance
Table Name: CUSTOMER
Constraint Type: CHECK
Constraint Name: Balance_CK
Constraint Condition: Balance must be greater than 0 and less than 2001
Hint: We could use (BALANCE > 0 AND BALANCE < 2001)
We could also use the BETWEEN 1 AND 2000
Write the constraint here
______
C1. Adding a UNIQUE constraint
A UNIQUE constraint on a column permits a value to occur only once in that column in the entire table.
Example:
Assume that a city can have only one sales rep assigned. The values in the city column in the SALESREP table must be therefore be unique. We must prevent the user from assigning more than one Salesrep to any particular city.
Field Name: City
Table Name: SALESREP
Constraint Type: UNIQUE
Constraint Name: City_UN
Constraint Condition: A city can be assigned to one and only one Salesrep
ALTER TABLE PRExxxx.SALESREP
ADD CONSTRAINT PRExxxx.City_UN
UNIQUE (City)
Once again, make sure you receive the "Alter completed" message.
Let’s write the UNIQUE constraint for an imaginary EMPLOYEE table:
Field Name: Ontario_Medicare_Num
Table Name: EMPLOYEE
Constraint Type: UNIQUE
Constraint Name: Ontario_Medicare_Num_UN
Constraint Condition: Any particular Medicare Number can occur only once on the table.
______
______
Required in this Lab:
1. Sign in to the system ‘green screen’ using the Emulator
2. Add your PRExxxx collection
3. Confirm that you have a Customer table, and a Salesrep table.
4. Add the following constraints:
a) Foreign key – the constraint showing referential integrity between the customer and sales rep tables
b) Check constraint – the customer credit limit must fall between $500 and $2000
c) Check constraint –a salesrep may earn a 3%, 5% or 7& commission only
d) Unique constraint -Salesreps – no two reps can have the same first name.
5. Insert the data from pages 3,4 of this lab first into the customer table, and then into the salesrep table.
6. If the data is NOT accepted into the customer table, try inserting data into the salesrep table and THEN into the customer table. (i.e., data must go into the parent table before it will be accepted by the child table, and you can’t enter data for a rep that doesn’t exist)
7. Display your tables (use the select statement)
8. Display your constraints (use the select statement)
a). You can view your list of CHECK constraints using the GUI interface by double clicking on the SYSCHKCST table. Try it now. On the green screen it is:
SELECT Check_Clause
FROM PREKxxx.SYSCHKCST
b)You can view your list of KEY and UNIQUE constraints using:
SELECT CONSTRAINT_NAME
FROM PREKxxx.SYSKEYCST
9. Return to the page that lists ‘system attribites (Shift_F1) and change the display attribute to PRINT
10. Run your select statements again for:
a) The customer table
b) The salesrep table
c) The check constraints
d) The key constraints
11. Print these 4 files and hand in .
Each lab should include student name and lab number
********************************************************************************************************
SQL Then Design: Lab03zz.doc / Design Then SQL: Lab08zz.doc / No Prompting: Lab03NP.doc
Page 1 of 8