Tut3 Database Constraints_1

TEMASEK POLYTECHNIC TEMASEK Informatics & IT SCHOOL Database Information Systems (CIM1Z01) AY2008/2009 Oct Semester   Tutorial 3 – Database Constraints Objectives - On completion of this tutorial session, the students will be able to :1.

Discuss how constraints enforce data consistency.

1.

What is a primary key and why is it needed?

2.

What is a composite key?

3.

Why is referential integrity important?

4.

AY06/07 April Semester Main Exam question.

The following tables are part of a company’s database showing the information of salesmen and their sales. The Commission_Rate value is in percentage which means that value 2.5 refers to 2.5%. Salesman Salesman_I d S001 S002 S003

Invoice Invoice_No 1001 1002 1003 1004

Name WL TAN ALAN YEO KELLY TOH

Invoice_Date 12-JAN-06 10-FEB-06 8-JAN-06 25-JAN-06

Commission_Rat e 2.5 5.0 4.5

Salesman_I d S002 S001 S002 S001

Sales_Amoun t 5000.00 2150.00 3800.00 3000.00

Primary keys in the tables are underlined. Salesman_Id in the Invoice table is a foreign key referencing Salesman table. Assume that all integrity constraints are implemented for the Salesman and Invoice tables.

DBIS (CIM1Z01) – Tutorial 3 Database Constraints

1

State whether the following operations will succeed or fail. Briefly explain the outcome of each operation with reference to the relational constraints of a database. DO NOT write SQL statements in your answers. a)

Delete from Invoice where Invoice_No = 1003;

(3 marks)

b)

Insert into Invoice values (1005, ’30-DEC-2005’, ‘S007’, 1500); (3 marks)

c)

Delete from Salesman where Name = ‘WL TAN’;

d)

Insert into Salesman values(‘S003’, ‘JOHN SMITH’, 1.5);

e)

Allan Yeo has resigned from the company and the company has decided to delete all his records from the salesman and the invoice tables. Suggest a way to do the deletion without violating the referential integrity constraint. (3 marks)

(3 marks) (3 marks)

~ End ~

DBIS (CIM1Z01) – Tutorial 3 Database Constraints

2