Transcript Database Management System - The Institute of Finance

Database Management
System
Module 3:
Complex Constraints



In this we specify complex integrity
constraints included in SQL.
It relates to integrity constraints.
Number of constraints are involved.



Constraints over single a table
Domain Constraints
Assertions
Constraints over single a table


It is possible to specify complex
constraints over a single table using
table constraints, which have the
form CHECK conditional-expression
Example to ensure that rating must be
an integer in the range 1 to 10 we
could use the following statements
Constraints over single a table
CREATE TABLE Sailors (
sid INTEGER,
sname CHAR (10),
rating INTEGER,
age REAL,
PRIMARY KEY (sid),
CHECK (rating >=1 AND rating <= 10)
)

Domain Constraint

A user can define a new domain using
CREATE DOMAIN statement which
make use of CHECK constraints
CREATE DOMAIN ratingval INTEGER
DEFAULT 0
CHECK (VALUE>=1 AND VALUE<=10 )
Assertions: ICs over Several
Tables



Table constraints are associated with a
single table.
Table constraints hold only if the
associated table is nonempty.
When a constraints involves two or
more tables, the constraint mechanism
is sometimes cumbersome and not
quite desired.
Assertions


Suppose that we wish to enforce the
constraint that the number of boats
plus the number of sailors should be
less than 100 (condition required for
small sailing club)
This can be solved by creating assertion
as follows:
Assertions
CREATE ASSERTION smallClub
CHECK ((SELECT COUNT (S.sid) FROM
Sailors S)+ (SELECT COUNT (B.sid)
FROM Boats B) < 100)
Triggers


A trigger is a procedure that is
automatically invoked by the DBMS in
response to specified changes to the
database, and is typically specified by
the DBA.
A database that has a set of associated
triggers is called an active database
Triggers

A trigger description contains three
parts



Event – A change to the database that
activates the triggers.
Condition – A query or test that is run
when the trigger is activated.
Action – A procedure that is executed
when the trigger is activated and its
condition is true.
Trigger: Example


The trigger called init.count initializes a
counter variable before every execution
of an INSERT statement that adds
tuples to the Students relation.
Another trigger called incr.count
increments the counter for each
inserted tuple that satisfies the
condition age < 18
Trigger: Example
CREATE TRIGGER init.count BEFORE
INSERT ON Students
DECLARE
count INTEGER;
BEGIN
count :=0;
END
Trigger: Example
CREATE TRIGGER init.count AFTER
INSERT ON Students
WHEN (new.age < 18)
FOR EACH ROW
BEGIN
count :=count + 1;
END
Functional Dependencies



A functional dependency is a
relationship of one attribute or field in a
record to another.
In a database, we often have the case
where one field defines the other.
For example, we can say that Social
Security Number (SSN) defines a name.
Functional Dependencies


This means that if I have a database
with SSNs and names, and if I know
someone's SSN, then I can find their
name.
By using the word “defines” we are
saying that for every SSN we will have
one and only one name.
Functional Dependencies


And hence we can come to a conclusion
that we have defined name as being
functionally dependent on SSN.
The idea of a functional dependency is
to define one field as an anchor (link)
from which one can always find a single
value for another field.
Functional Dependencies

Another example, suppose that a
company assigned each employee a
unique employee number. Each
employee has a number and a name.
Names might be the same for two
different employees, but their employee
numbers would always be different and
unique because the company defined
them that way.
Functional Dependencies
We write a functional dependency (FD)
connection with an arrow:
SSN → Name
Or
EmpNo → Name
 The expression SSN → Name is read
"SSN defines Name" or "SSN implies
Name"

Functional Dependencies

Consider the table below:
Functional Dependencies (FD)


You have two people named Fred! It is
usual that Name will not be unique and
it is commonplace for two people to
have the same name.
However, no two people have the same
EmpNo and for each EmpNo, there is a
Name.
Functional Dependencies (FD)


The FD X → Y means that for every
occurrence of X you will get the same
value of Y.
consider another example
Functional Dependencies (FD)



Here, we will define two FDs: SSN → Name
and School → Location.
Further, we will define this FD: SSN →
School.
First, have we violated any FDs with our
data? Because all SSNs are unique, there
cannot be a FD violation of SSN → Name.
Why? Because a FD X → Y says that given
some value for X, you always get the same Y.
Because the X's are unique, you will always
get the same value. The same comment is
true for SSN → School.
Functional Dependencies (FD)
Note:
 If we define a functional dependency X
→ Y and we define a functional
dependency Y → Z, then we know by
inference that X → Z.
 We can define SSN → School. We also
can define School → Location, so we
can infer that SSN → Location.
Functional Dependencies (FD)

The inference we have illustrated is
called the transitivity rule of FD
inference. Transitivity rule:Given X → Y
Given Y → Z
Then X → Z
Functional Dependencies (FD)


A primary key constraint is a special
case of an FD.
The attributes in the key play the role
of X, and the set of all attributes in the
relation plays the role of Y.
QUERY-BY-EXAMPLE (QBE)
QUERY-BY-EXAMPLE (QBE)


Query-by-Example (QBE) is another
language for querying (and, like SQL,
for creating and modifying) relational
data.
It is different from SQL, and from most
other database query languages, in
having a graphical user interface that
allows users to write queries by creating
example tables on the screen.
QUERY-BY-EXAMPLE (QBE)


QBE is especially suited for queries that
are not too complex and can be
expressed in terms of a few tables.
QBE, like SQL, was developed at IBM
and QBE is an IBM trademark, but a
number of other companies sell QBElike interfaces, including Paradox.
QUERY-BY-EXAMPLE (QBE)

Some systems, such as Microsoft
Access, offer partial support for formbased queries and reflect the influence
of QBE.
BASIC QBE QUERIES

For instance if we want to print all fields
in some relation, we can place P. under
the name of the relation. This notation
is like the SELECT * convention in SQL.
It is equivalent to placing a P. in every
field:
BASIC QBE QUERIES


Selections are expressed by placing a
constant in some field:
Placing a constant, say 10, in a column
is the same as placing the condition
=10.
BASIC QBE QUERIES

This query is very similar in form to the
equivalent DRC query
{I,N, 10,A | I,N, 10,A ∈ Sailors}