Transcript Referential Integrity

The relational model
 A data model (in general) : Integrated collection of concepts
for describing data (data requirements).
 Relational model was introduced in 1970 by Dr. E. F. Codd
(of IBM)
 Commercial relational databases began to appear in the
1980s
 Today relational databases have become the dominant
technology for database management .
RDBMS-Introduction

The Relational Database Management System (RDBMS)
has become the dominant DBMS in use today.

All data are logically structured withing relations (tables).

Design methodology will be based on the relational model
4/2/2016
CPSC-4360-01, CPSC-5360-01, Lecture 4
3
A Logical View of Data
 Relational database model’s structural and data
independence enables us to view data logically
rather than physically.
 The logical view allows a simpler file concept of
data storage.
 The use of logically independent tables is easier to
understand.
 Logical simplicity yields simpler and more
effective database design methodologies.
The relational model
 Data is represented in the form of tables, and the model has
3 components.
 Data structure – data are organised in the form of tables
with rows and columns.
 Data manipulation – powerful operations (using the SQL
language) are used to manipulate data stored in the relations
 Data integrity – facilities are included to specify business
rules that maintain the integrity of data when they are
manipulated.
Relational definitions
 A relation is a named, two-dimensional table of data
 Every relation has a unique name, and consists of a set of




named columns and an arbitrary number of unnamed rows
An attribute is a named column of a relation, and every
attribute value is atomic.
Every row is unique, and corresponds to a record that
contains data attributes for a single entity.
The order of the columns is irrelevant.
The order of the rows is irrelevant.
Summary of the Characteristics of a Relational Table
Tuples and attribute
 Attributes of a relation serve as names for the columns of the relation
The rows of a relation, other than the header row containing
The attribute names are called tuples.
 A tuple has one component for each attribute of the relation.
Attribute
Tuple
Title
Star Wars
Year
1977
Length
124
Might
Ducks
Wayne’s
World
1991
104
1992
95
Domains
 Each attribute of a relation is associated with a
particular elementary type called domain.
 The components of any tuple of the relation must
have, in each component, a value that belongs to
the domain of the corresponding column.
 Example:
 with title string is associated
 with year integer is associated
Examples of domain for attributtes
4/2/2016
CPSC-4360-01, CPSC-5360-01, Lecture 4
10
Relational structure
 We can express the structure of a relation by a Tuple,
a shorthand notation
 The name of the relation is followed (in parentheses)
by the names of the attributes of that relation, e.g.:
Staff(staffNO,name,position,salary, branchNO)
Relational Keys
Must be able to store and retrieve a row of data in a relation,
based on some data values stored in that row.
Each record in a table must be unique , so we need a mean
to provide this uniqueness.
 A primary key is an attribute (or combination of attributes)
that uniquely identifies each row in a relation.
 The primary key in the Staff relation is staffNO (this is why it
is underlined) as in:
 Staff(staffNO, name, position, salary, branchNO)
12
Relational Keys
Candidate Key “K”


that record .
Irreducibility : No proper subset of K has the uniqueness
property
Uniqueness : value uniquely identify
Primary key ?
Is “city” a candidate key for the Branch table ?
What about “zipCode”?
© Pearson Education Limited, 2004
13
Composite and foreign keys
A Composite key is a primary key that consists of more than
one attribute.
A Foreign key is used when we must represent the relationship
between two tables and relations
A foreign key is an attribute (possibly composite) in a relation
of a database that serves as the primary key of another
relation in the same database
Foreign keys
Consider the following relations:
EMPLOYEE1(Emp_ID,Name,Dept_Name,Salary)
DEPARTMENT(Dept_Name,Location,Fax)
The attribute Dept_Name is a foreign key in EMPLOYEE1. It allows the
user to associate any employee wit the department they are assigned to.
Some authors show the fact that an attribute is a foreign key by using a
dashed underline.
Role table represents the name of the character
played by an actor in videos.
Is ActorNo a primary key ?
Is catalogNo a primary key ?
4/2/2016
CPSC-4360-01, CPSC-5360-01, Lecture 4
16
4/2/2016
CPSC-4360-01, CPSC-5360-01, Lecture 4
17
More examples)
Primary Key
Foreign Key
(implements 1:N relationship
between customer and order)
Combined, these are a composite
primary key (uniquely identifies the
order line)…individually they are
foreign keys (implement M:N
relationship between order and
product)
Entity integrity
Entity integrity
 In some cases a particular attribute cannot be assigned a
data value, e.g. when there is no applicable data value or the
value is not known when other values are assigned.
 In these situations we can assign a null value to an attribute
(null signifies absence of a value)
 But still primary key values cannot be null – the entity
integrity rule states that “no primary key attribute (or
component of a primary key attribute) may be null
Referential Integrity
 How referential integrity works
 Cascade Update and Delete
Referential Integrity
 Referential integrity controls the links between
records.
 You can set referential integrity when related
fields have the same data type .
22
Referential Integrity
 A Referential Integrity constraint is a rule that maintains
consistency among the rows of two relations – it states that any
foreign key value (on the relation of the many side) MUST match
a primary key value in the relation of the one side. (Or the foreign
key can be null)
 In the following Fig., an arrow has been drawn from each foreign
key to its associated primary key. A referential integrity constraint
must be defined for each of these arrows in the schema
Referential integrity constraints (Pine Valley Furniture)
Referential
integrity
constraints are
drawn via arrows
from dependent to
parent table
Referential integrity
 How do you know if a foreign key is allowed to be null?
 In this example, as each ORDER must have a CUSTOMER
the foreign key of Customer_ID cannot be null on the
ORDER relation
 Whether a foreign key can be null must be specified as a
property of the foreign key attribute when the database is
designed
How referential integrity works
 You can't enter a value in the foreign key field of a table if it
doesn't exist in the primary key of the related table
 For example, you can't assign an order to a customer
who is not in the Customer table
 You can't delete a record from a table if matching records
exist in a related table
 For example, you can't delete an employee if there are
orders assigned to the employee in the Order table
26
How referential integrity works (2)
• You can't change a primary key value for a record
that has related records
• For example, you can't change an employee's ID
in the Employee table if there are orders assigned
to that employee in the Orders table.
Referential integrity
What happens to order data if we choose to delete a
customer who has submitted orders? We may want to
see sales even though we do not care about the customer
anymore.
3 choices are possible:
Restrict – don’t allow delete of “parent” side if related
rows exist in “dependent” side, i.e. prohibit deletion of
the customer until all associated orders are first deleted
Referential integrity
Cascade – automatically delete “dependent” side rows that
correspond with the “parent” side row to be deleted, i.e.
delete the associated orders, in which case we lose not only
the customer but also the sales history
Set-to-Null – set the foreign key in the dependent side to
null if deleting from the parent side - an exception that
says although an order must have a customer_ID value
when the order is created, Customer_ID can become null
later if the associated customer is deleted .
Assertions
Assertion constraints similar to check constraints .
Examples are
 You can assert that the total order amount for a customer
cannot exceed the credit limit, even if the credit is in one
table (customers , perhaps) and the orders belongs to
another (order details ).
 Are business rules such as “A person may purchase a ticket
for the celebrity football game only if that person is a
season-ticket holder”
There are various techniques for defining and enforcing
such rules.
Relational Languages
 Two main languages:
SQL (Structured Query Language), standardized by
ISO.
 QBE (Query-by-Example), alternative graphical
“point-and-click” way of querying database.

© Pearson Education Limited, 2004
31