Scribd
Upload
73 views19 pages

Relational Model-2

Relational databases have constraints that define rules and relationships between data. There are three main types of constraints: key constraints define unique identifiers for each tuple, entity integrity constraints prevent null values in primary keys, and referential integrity constraints define relationships between tuples in different tables through foreign keys. Maintaining constraints is important for data consistency and integrity within a relational database.

Uploaded by

Devika Harikrishnan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
73 views19 pages

Relational Model-2

Relational databases have constraints that define rules and relationships between data. There are three main types of constraints: key constraints define unique identifiers for each tuple, entity integrity constraints prevent null values in primary keys, and referential integrity constraints define relationships between tuples in different tables through foreign keys. Maintaining constraints is important for data consistency and integrity within a relational database.

Uploaded by

Devika Harikrishnan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 19

Relational Database Constraints

Slide 5- 1
Characteristics Of Relations

• Values in a tuple:
– All values are considered atomic (indivisible).
– Each value in a tuple must be from the domain of
the attribute for that column
• If tuple t = <v1, v2, …, vn> is a tuple (row) in
the relation state r of R(A1, A2, …, An)
• Then each vi must be a value from dom(Ai)

– A special null value is used to represent values


that are unknown or inapplicable to certain
tuples.
Slide 5- 2
Characteristics Of Relations

• Notation:
– We refer to component values of a tuple t
by:
• t[Ai] or t.Ai
• This is the value vi of attribute Ai for tuple t
– Similarly, t[Au, Av, ..., Aw] refers to the
subtuple of t containing the values of
attributes Au, Av, ..., Aw, respectively in t

Slide 5- 3
Relational Integrity Constraints

• Constraints are conditions that must hold on all valid


relation states.
• There are three main types of constraints in the
relational model:
– Key constraints
– Entity integrity constraints
– Referential integrity constraints
• Another implicit constraint is the domain constraint
– Every value in a tuple must be from the domain of its
attribute (or it could be null, if allowed for that attribute)

Slide 5- 4
Key Constraints

• Superkey of R:
– Is a set of attributes SK of R with the following condition:
• No two tuples in any valid relation state r(R) will have the
same value for SK
• That is, for any distinct tuples t1 and t2 in r(R), t1[SK] 
t2[SK]
• This condition must hold in any valid state r(R)
• Key of R:
– A "minimal" superkey
– That is, a key is a superkey K such that removal of any
attribute from K results in a set of attributes that is not a
superkey (does not possess the superkey uniqueness
property)

Slide 5- 5
Key Constraints (continued)

• Example: Consider the CAR relation schema:


– CAR(State, Reg#, SerialNo, Make, Model, Year)
– CAR has two keys:
• Key1 = {State, Reg#}
• Key2 = {SerialNo}
– Both are also superkeys of CAR
– {SerialNo, Make} is a superkey but not a key.
• In general:
– Any key is a superkey (but not vice versa)
– Any set of attributes that includes a key is a superkey
– A minimal superkey is also a key

Slide 5- 6
Key Constraints (continued)

• If a relation has several candidate keys, one is chosen


arbitrarily to be the primary key.
– The primary key attributes are underlined.
• Example: Consider the CAR relation schema:
– CAR(State, Reg#, SerialNo, Make, Model, Year)
– We chose SerialNo as the primary key
• The primary key value is used to uniquely identify each
tuple in a relation
– Provides the tuple identity
• Also used to reference the tuple from another tuple
– General rule: Choose as primary key the smallest of the
candidate keys (in terms of size)
– Not always applicable – choice is sometimes subjective

Slide 5- 7
CAR table with two candidate keys –
LicenseNumber chosen as Primary Key

Slide 5- 8
Relational Database Schema

• Relational Database Schema:


– A set S of relation schemas that belong to the
same database.
– S is the name of the whole database
schema
– S = {R1, R2, ..., Rn}
– R1, R2, …, Rn are the names of the individual
relation schemas within the database S
• Following slide shows a COMPANY database
schema with 6 relation schemas

Slide 5- 9
COMPANY Database Schema

Slide 5- 10
Entity Integrity

• Entity Integrity:
– The primary key attributes PK of each relation
schema R in S cannot have null values in any tuple of
r(R).
• This is because primary key values are used to identify the
individual tuples.
• t[PK]  null for any tuple t in r(R)
• If PK has several attributes, null is not allowed in any of
these attributes
– Note: Other attributes of R may be constrained to
disallow null values, even though they are not
members of the primary key.
Slide 5- 11
Referential Integrity

• A constraint involving two relations


– The previous constraints involve a single
relation.
• Used to specify a relationship among tuples in
two relations:
– The referencing relation and the
referenced relation.

Slide 5- 12
Referential Integrity

• Tuples in the referencing relation R1 have


attributes FK (called foreign key attributes)
that reference the primary key attributes PK of
the referenced relation R2.
– A tuple t1 in R1 is said to reference a tuple
t2 in R2 if t1[FK] = t2[PK].
• A referential integrity constraint can be displayed
in a relational database schema as a directed arc
from R1.FK to R2.

Slide 5- 13
Referential Integrity (or foreign key)
Constraint

• Statement of the constraint


– The value in the foreign key column (or
columns) FK of the the referencing relation
R1 can be either:
• (1) a value of an existing primary key value
of a corresponding primary key PK in the
referenced relation R2, or
• (2) a null.
• In case (2), the FK in R1 should not be a part of
its own primary key.

Slide 5- 14
Displaying a relational database schema and
its constraints

• Each relation schema can be displayed as a row of


attribute names
• The name of the relation is written above the attribute
names
• The primary key attribute (or attributes) will be
underlined
• A foreign key (referential integrity) constraints is
displayed as a directed arc (arrow) from the foreign key
attributes to the referenced table
– Can also point the the primary key of the referenced
relation for clarity
• Next slide shows the COMPANY relational schema
diagram

Slide 5- 15
Referential Integrity Constraints for COMPANY database

Slide 5- 16
Other Types of Constraints

• Semantic Integrity Constraints:


– based on application semantics and cannot be
expressed by the model per se
– Example: “the max. no. of hours per
employee for all projects he or she works on
is 56 hrs per week”
• A constraint specification language may have
to be used to express these
• SQL-99 allows triggers and ASSERTIONS to
express for some of these

Slide 5- 17
Populated database state

• Each relation will have many tuples in its current relation


state
• The relational database state is a union of all the
individual relation states
• Whenever the database is changed, a new state arises
• Basic operations for changing the database:
– INSERT a new tuple in a relation
– DELETE an existing tuple from a relation
– MODIFY an attribute of an existing tuple
• Next slide shows an example state for the COMPANY
database
Slide 5- 18
Populated database state for COMPANY

Slide 5- 19

You might also like