Transcript Lecture 21

603 Database Systems
Senior Lecturer: Laurie Webster II,
M.S.S.E.,M.S.E.E., M.S.BME, Ph.D., P.E.
Lecture 21
A First Course in Database Systems
SQL
Union, Intersection, and Difference in SQL:
Union  
Intersection  
Difference  Words like UNION are used between two queries, and
those queries must be parenthesized.
SQL
Suppose we wanted the names and addresses of all
female move stars who are also movie executives with
a net worth over $10,000,000.
Using the following two relations:
MovieStar ( name, address, gender, birthdate)
MovieExec ( name, address, cert#, netWorth)
Develop the SQL query!
SQL
Using the following two relations:
MovieStar ( name, address, gender, birthdate)
MovieExec ( name, address, cert#, netWorth)
1)
2)
3)
4)
5)
6)
7)
(SELECT name, address
FROM MovieStar
WHERE gender = ‘F’)
INTERSECT
(SELECT name, address
FROM MovieExec
WHERE netWorth > 10000000 ) ;
SQL
1)
2)
3)
(SELECT name, address
FROM MovieStar
WHERE gender = ‘F’)

yield a relation whose schema is (name, address) and
whose tuples are the names and addresses of all female
movie stars.
SQL
5)
6)
7)
(SELECT name, address
FROM MovieExec
WHERE netWorth > 10000000 ) ;

produce the set of “rich” executives, those with net
worth over $10,000,000.
This query also yields a relation whose schema has the
attributes name and address only.
SQL
Since the two schemas are the same, we can intersect
them, and we do so with the operator of line 4).
We can take the difference of two sets of persons, each
selected from a relation. The query
(SELECT name, address, FROM MovieStar)
EXCEPT
(SELECT name, address, FROM MovieExec) ;
==> names and addresses of movie stars who are not
also movie executives, regardless of gender or net
worth.
SQL
Set Operations UNION in SQL:
Make a list of all project numbers for projects that involve an
employee whose last name is ‘Smith’, either as a worker or as a
manager of the department that controls the project.
SELECT PNUMBER
FROM
PROJECT, DEPARTMENT, EMPLOYEE
WHERE
DNUM=DNUMBER AND MGRSSN=SSN AND
LNAME=‘Smith’)
UNION
SELECT PNUMBER
FROM
PROJECT, WORKS_ON, EMPLOYEE
WHERE
PNUMBER=PNO AND ESSN=SSN AND
SQL
First SELECT retrieves projects that involves a
“Smith’ as a manager of the department that controls
the project
Second SELECT retrieves projects that involves a
‘Smith’ as a worker on the project
UNION operation on the two SELECTs is result
desired
The Deductive Databases
Some query languages for the relational model resemble a
logic more than they do the algebra that we introduced earlier.
The logic-based languages appear to be difficult for many
programmers to grasp.
We will discuss Deductive Databases , which is the
intersection of databases, logic, and artificial intelligence or
knowledge bases.
Deductive Databases
A deductive database system is a database system that
includes capabilities to define (deductive) rules, which can
deduce or infer additional information from the facts that
are stored in a database.
Because part of the theoretical foundation for some deductive
database systems is mathematical logic, such rules are often
referred to as logical databases.
Deductive Databases
In a deductive database system, we typically specify rules
through a declarative language - a language in which we
specify what to achieve rather than how to achieve it.
An inference engine ( or deduction mechanism) within the
system can deduce new facts from the database by
interpreting these rules.
The model for deductive databases is closely related to the
relational data model, and particularly to the domain
relational calculus formalism.
Deductive Databases
Deductive Database is also related to the field of logic
programming and the Prolog language.
The deductive database work based on logic has used Prolog
(Programming in Logic) as a starting point.
A variation of Prolog called Datalog is used to define rules
declaratively in conjunction with an existing set of relations,
which are themselves treated as literals in the language.
The basic constructs of logic programming: terms, statements
Deductive Databases
There are three basic statements in logic programming:
1)
2)
3)
facts
rules
queries
Facts
The simplest kind of statement is called a fact
Fact ==> stating that a relationship holds
between objects
Deductive Databases
Fact Example:
father ( abraham, isaac).
==> Abraham is the father of Isaac, or that the
relation father holds between the
individuals named abraham and isaac.
Another name for relationship is predicate.
Deductive Databases
Names of individuals are know as atoms.
Similarly plus (2,3,5) ==> expresses the relationship
that 2 plus 3 is 5.
plus is a predicate!
The familiar plus relationship can be realized via a set
of facts that defines the addition table.
Deductive Databases
An initial segment of the addition table is:
plus(0,0,0). plus(0,1,1). plus (0,2,2). plus(0,3,3).
plus(1,0,1). plus(1,1,2). plus(1,2,3). plus (1,3,4).
Notice the period after each sentence.
The table above is a segment of the definition of plus!
Deductive Databases
Syntactic conventions for logic programming:
1) case convention
Names of both predicates and atoms in facts
begin with a lowercase letter (italicized when
they appear in running text).
2) program
A finite set of facts constitutes a program.
This is the simplest form of a logic program.
Deductive Databases
Syntactic conventions for logic programming:
2) program (continued)
A set of facts is also a description of a situation.
This insight is the basis of database
programming !
Deductive Databases
Example Database of family relationships from the
Bible:
father(terach, abraham).
male(terach).
father(terach, nachor).
male(abraham).
father(terach, haran).
male(nachor).
father(abraham, isaac).
male(haran).
father(haran, lot).
male(isaac).
father(haran, milcah).
male(lot).
father(haran, yiscah).
female(sarah).
female(milcah).
mother( sarah, isaac).
female(yiscah).
Deductive Databases
father(terach, abraham).
mother( sarah, isaac).
male(terach).
female(yiscah).
The predicates father, mother, male, female express the
obvious relationships.
Deductive Databases
A second form of statement in a logic program is a
query.
Queries are a means of retrieving information from a
logic program.
A query asks whether a certain relation holds
between objects.
Deductive Databases
Query Example:
father (abraham, isaac)?
==> asks whether the father
relation holds between
abraham and isaac.
Deductive Databases
father (abraham, isaac)?
Given the facts presented earlier the
answer to this query is YES!
Note: statement of fact or question
SQL
Next Lecture
MORE Deductive
Databases