Transcript Relational Model - University of Hawaii

The Relational Model
• Codd (1970): based on set theory
• Relational model: represents the database
as a collection of relations (a table of
values --> file)
– Domains
– Tuples
– Attributes
– Relations
• Relational model constraints…
4 Relational Model Constraints
• Domain:
– a range of values an attribute can take on
• Key:
– a relation must have a primary key
– unique & time-invariant
– primary key --> name underlined in the relation
schema
• Entity Integrity
– no primary key value can be null
• Referential Integrity
– to maintain consistency among the tuples of two
relations…
Referential Integrity - 1
• Foreign Key:
– a set of attributes FK in relation schema R1 is
a foreign key of R1 if it satisfies the following
two rules:
• the attributes in FK have the same domain as the
primary key attributes PK of another relation
schema R2; the attributes FK are said to reference
or refer to the relation R2.
• a value of FK in a tuple t1 of R1 either occurs as a
value of PK for some tuple t2 in R2 or is null.
– foreign key can refer to its own relation
Referential Integrity - 2
• Referential integrity rule:
– If R2 includes a foreign key matching the
primary key PK of R1, then every value of FK
in R2 must either be equal to the value of PK
in some tuple of R1 or be wholly null. R1 and
R2 are not necessarily distinct.
Relational Data Retrieval
•
•
•
•
•
Results of operations: a new relation
SELECT: show tuples (rows)
PROJECT: show attributes (columns)
JOIN: get data from more than one table
Set: UNION, INTERSECTION,
DIFFERENCE, CARTSIAN PRODUCT
Relational DB Languages
• Standards: SQL1 (1986) ,SQL2 (1992),
SQL3 (1999)
• QBE (Query by Example): vary by
implementations
• ACCESS has both QBE and SQL
• Variations:
- Oracle: SQL*,PL/SQL
- Sybase: ISQL, T-SQL
A Caution About Basics
• Note that the database you are creating in the
Lab sessions has been designed for you.
– This is not a trivial job; it requires an understanding
of database principles and theories
• You will learn simple QBE.
• Basic SQL is easy; but SQL can be a powerful
programming tool which takes time to learn.
• Everyone can use Access but not everyone
can use it correctly.