The Relational Model

Chapter 3

Outline

Concepts

Table, Row, Column Header, Column Type :: Relation, Tuple, Attribute, Domain

domain
specialized data type, set of atomic values (indivisible to relational model)
relation schema
R(A₁,A₂,…,An), relation name and list of attributes (each of which has a name and a domain)
attribute
the name of a role played by a domain in that relation
relation (relation state)
r(R), a set of tuples. Each tuple is an ordered list of values, corresponding with the domain of their attribute, and representing a fact. A relation state is a subset of the Cartesian product of the domains defining the relation schema.
characteristics of relations

Notation

Constraints and schemas

Inherent model-based constraints or implicit constraints – already discussed. Schema-based constraints or explicit constrants – expressed by the schema, written in the DDL, enforced by the DBMS. Application-based, semantic constraints, or business rules – other than the above; typically enforced by the application.

domain constraints
within a tuple the value of each attribute must be an element from the domain of that attribute
key constraints
a subset of attributes called a superkey (SK), that specify uniqueness on subtuples of their relation (for any distinct tuples t₁ and t₂ that are members of a relation r(R), t₁[SK]≠t₂[SK]). A key (K) is a minimal superkey. In notation, the primary key is usually underlined. The remaining candidate keys are not and are called unique keys.
 
null constraints
applied to attributes, specifying that their values must be NOT NULL
relational database schema
A set of relational schemas and a set of integrity constraints. Also, a relational database state is a set of relation states (that satisfy the integrity constraints).
entity integrity constraint
no primary key value can be NULL
foreign key
a set of attributes from one relation schema that have the same domains as, and refer to, the primary key of a second relation schema
referential integrity constraint
One relation schema R₁has a foreign key FK referencing a second relation schema R₂ with primary key PK, and if for a tuple t₁ ∈ r₁ that t₁[FK] ≠ ω, t₂[PK] ∈ r₂. A foreign key must match its primary key unless it's NULL.
 
other constraints
semantic integrity constraints; enforced by applications, or by the DBMS if it supports a constraint specification language with features like CREATE TRIGGER or CREATE ASSERTION.

Updates, transactions, constraint violations

retrieval
A function taking a relational algebra expression, returning a result relation
insert
Adds a new tuple to a relation. Can potentially violate any constraint.
delete
Remove a tuple from a relation. Can violate referential integrity.
update
Modify a tuple in a relation. Depending on what's modified, can violate whatever the previous two can.
Transactions

Your Questions

relational constraints, domain constraints, a lot of definitions to take in.
superkey? (versus primary key, candidate key)
foreign key
atomic attributes
“it seems very difficult to maintain referential integrity” you said it

* are key values in a database used like key values in a hash table?
as an associative array, yes. as an actual hash table, also yes

examples of modifications SQL and RDBMSs do not implement the relational model! (they approximate it)

Questions


 
 
 
 
 
 
Which is a plausible candidate key for the Course relation?
  1. <Credit_hours, Department>
  2. <Course_name, Course_number>
  3. <Department>
 

 
 
 
 
 
 

Constraints

Assume the database is in a consistent state. Which of these modifications will definitely violate a referential integrity constraint?
  1. INSERT INTO Project VALUES ('Take over world', NULL, 'Everywhere', 5);
  2. INSERT INTO Works_on VALUES ('078-05-1120', 14, 30);
    INSERT INTO Works_on VALUES ('078-05-1120', 14, 18);
  3. DELETE FROM Project WHERE Pnumber = 14;
    INSERT INTO Works_on VALUES ('078-05-1120', 14, 30);
 

 
 
 
 
 
 
← Main Page