The Relational Model

1
The Relational Model

• Chapter 3

2
Objectives

• Representing data using the relational model.
• Expressing integrity constraints on data.
• Creating, modifying, destroying, altering, and
  querying relations.
• Creating, modifying, destroying, altering, and
  querying relation instances using SQL.
• Obtaining a relational database design from an ER
  diagram.
• Introducing views.

3
Why Study the Relational Model?

• Most widely used model.
• Vendors IBM, Informix, Microsoft, Oracle,
  Sybase, etc.
• Legacy systems in older models
• E.g., IBMs IMS
• Recent competitor object-oriented model
• ObjectStore, Versant, Ontos
• A synthesis emerging object-relational model
• Informix Universal Server, UniSQL, O2, Oracle, DB2

4
Relational Database Concepts

• Relation made up of 2 parts
• Instance a table, with rows and columns. Rows
  cardinality, fields degree / arity.
• Schema specifies name of relation, plus name
  and domain (type) of each column (attribute).
• E.g., Students(sid string, name string, login
  string,
• age integer, gpa
  real).
• Can think of a relation as a set of rows or
  tuples (i.e., all rows are distinct), where each
  tuple has the same arity as the relation schema.
• Relational database a set of relations, each
  with distinct name.
• Relational DB schema set of schemas of relations
  in the DB.
• Relational DB instance set of relation instances
  in the DB.

5
Example Instance of Students Relation

• Cardinality 3, arity 5, all rows distinct.
• Commercial systems allow duplicates.
• Order of attributes may or may not matter!
• Do all columns in a relation instance have to
• be distinct? Depends on whether they are
  ordered or not.

6
Relational Query Languages

• A major strength of the relational model
  supports simple, powerful querying of data.
• Queries can be written intuitively (i.e.
  declaratively), and the DBMS is responsible for
  efficient evaluation.
• Users tell the DBMS what to do, and the DBMS
  figures out how to do it and does it efficiently!
• The key precise semantics for relational
  queries.
• Allows the optimizer to extensively re-order
  operations, and still ensure that the answer does
  not change.

7
The SQL Query Language

• Developed by IBM (system R) in the 1970s.
• Need for a standard since it is used by many
  vendors.
• Standards
• SQL-86
• SQL-89 (minor revision)
• SQL-92 (major revision Triggers and objects)
• SQL-99 (major extensions Datawarehousing
  current standard)

8
Creating Relations in SQL

• Creates the Students relation. Observe
  that the type (domain) of each field
  is specified, and enforced by the DBMS
  whenever tuples are added or modified.
• As another example, the Enrolled table holds
  information about courses that students
  take.

CREATE TABLE Students (sid CHAR(20), name
CHAR(20), login CHAR(10), age INTEGER,
gpa REAL)
CREATE TABLE Enrolled (sid CHAR(20), cid
CHAR(20), grade CHAR(2))
9
Destroying and Altering Relations
DROP TABLE Students

• Destroys the relation Students. The schema
  information and the tuples are deleted.

ALTER TABLE Students ADD COLUMN firstYear
integer

• The schema of Students is altered by adding a new
  field every tuple in the current instance is
  extended with a null value in the new field.

10
Adding and Deleting Tuples

• Can insert a single tuple using

INSERT INTO Students (sid, name, login, age,
gpa) VALUES (53688, Smith, smith_at_ee, 18, 3.2)

• Can delete all tuples satisfying some condition
  (e.g., name Smith)

DELETE FROM Students S WHERE S.name Smith
11
Integrity Constraints (ICs)

• IC condition that must be true for any instance
  of the database e.g., domain constraints.
• ICs are specified when schema is defined.
• ICs are checked when relations are modified.
• A legal instance of a relation is one that
  satisfies all specified ICs.
• DBMS should not allow illegal instances.
• If the DBMS checks ICs, stored data is more
  faithful to real-world meaning.
• Avoids data entry errors, too!

12
Primary Key Constraints

• A set of fields is a key for a relation if
• 1. No two distinct tuples can have same values in
  all key fields, and
• 2. This is not true for any subset of the key.
• If part 2 of this definition is false, then we
  have a superkey.
• If theres gt1 key for a relation, one of the keys
  is chosen (by DBA) to be the primary key.
• E.g., sid is a key for Students. (What about
  name?) The set sid, gpa is a superkey.

13
Primary and Candidate Keys in SQL

• Possibly many candidate keys (specified using
  UNIQUE), one of which is chosen as the primary
  key.

CREATE TABLE Enrolled (sid CHAR(20) cid
CHAR(20), grade CHAR(2), PRIMARY KEY
(sid,cid) )

• For a given student and course, there is a
  single grade. vs. Students can take only one
  course, and receive a single grade for that
  course further, no two students in a course
  receive the same grade.
• Used carelessly, an IC can prevent the storage of
  database instances that arise in practice!

CREATE TABLE Enrolled (sid CHAR(20) cid
CHAR(20), grade CHAR(2), PRIMARY KEY
(sid), UNIQUE (cid, grade) )
14
Foreign Keys, Referential Integrity

• Foreign key Set of fields in one relation that
  is used to refer to a tuple in another
  relation. (Must correspond to primary key of the
  second relation.) Like a logical pointer.
• E.g. sid is a foreign key referring to Students
• Enrolled(sid string, cid string, grade string)
• If all foreign key constraints are enforced,
  referential integrity is achieved, i.e., no
  dangling references.
• Can you name a data model w/o referential
  integrity?
• Links in HTML!

15
Foreign Keys in SQL

• Only students listed in the Students relation
  should be allowed to enroll for courses.

CREATE TABLE Enrolled (sid CHAR(20), cid
CHAR(20), grade CHAR(2), PRIMARY KEY
(sid,cid), FOREIGN KEY (sid) REFERENCES
Students )
Enrolled
Students
16
Enforcing Referential Integrity

• Consider Students and Enrolled sid in Enrolled
  is a foreign key that references Students.
• What should be done if an Enrolled tuple with a
  non-existent student id is inserted? (Reject
  it!)
• What should be done if a Students tuple is
  deleted?
• Also delete all Enrolled tuples that refer to it.
• Disallow deletion of a Students tuple that is
  referred to.
• Set sid in Enrolled tuples that refer to it to a
  default sid.
• (In SQL, also Set sid in Enrolled tuples
  that refer to it to a special value null,
  denoting unknown or inapplicable.)
• Similar if primary key of Students tuple is
  updated.

17
Referential Integrity in SQL

• SQL/92 and SQL1999 support all 4 options on
  deletes and updates.
• Default is NO ACTION (delete/update is
  rejected)
• CASCADE (also delete all tuples that refer to
  deleted tuple)
• SET NULL / SET DEFAULT (sets foreign key value
  of referencing tuple)

CREATE TABLE Enrolled (sid CHAR(20), cid
CHAR(20), grade CHAR(2), PRIMARY KEY
(sid,cid), FOREIGN KEY (sid) REFERENCES
Students ON DELETE CASCADE ON UPDATE SET NULL)
18
Where do ICs Come From?

• ICs are based upon the semantics of the
  real-world enterprise that is being described in
  the database relations.
• We can check a database instance to see if an IC
  is violated, but we can NEVER infer that an IC is
  true by looking at an instance.
• An IC is a statement about all possible
  instances!
• From example, we know name is not a key, but the
  assertion that sid is a key is given to us.
• Key and foreign key ICs are the most common more
  general ICs supported too.

19
Transactions and Constraints

• A transaction program is a sequence of queries,
  inserts, deletes, etc that access the DB.
• When should constraints be checked within a
  transactions?
• Immediately check the constraint
• Defer the constraint checking at a later time
  point
• SQL allows two constraint modes.
• SET CONSTRAINT MyConstraint IMMEDIATE
• SET CONSTRAINT MyConstraint DEFERRED
• ICs are immediate by default deferred ICs are
  checked at commit time

20
Querying Relational Data in SQL

• To find all 18 year old students, we can write

SELECT FROM Students S WHERE S.age18

• To find just names and logins, replace the first
  line

SELECT S.name, S.login
21
Querying Relational Data in SQL (Contd)

• We can also combine info from two relations.

SELECT S.name, E.cid FROM Students S, Enrolled
E WHERE S.sidE.sid AND E.gradeA
Given the following instance of Enrolled
we get
22
Logical DB Design ER to Relational

• The ER model represent the initial, high-level
  database design.
• The task is to generate a relational database
  schema that is as close as possible to the ER
  model.
• The mapping is approximate since it is hard to
  translate all the constraints of the ER model
  into an efficient logical (relational) model.

23
Entity Sets to Tables

• Each entity attribute becomes an attribute of the
  table.
• Domain constraints become appropriate SQL types.
• The primary key of the entity set become the
  primary key of the table.

CREATE TABLE Employees
(ssn CHAR(11), name
CHAR(20), lot INTEGER,
PRIMARY KEY (ssn))
24
Relationship Sets to Tables

• In translating a relationship set (without
  constraints) to a relation, attributes of the
  relation must include
• Keys for each participating entity set (as
  foreign keys).
• This set of attributes forms a superkey for the
  relation.
• All descriptive attributes.

CREATE TABLE Works_In( ssn CHAR(1), did
INTEGER, since DATE, PRIMARY KEY (ssn,
did), FOREIGN KEY (ssn) REFERENCES
Employees, FOREIGN KEY (did)
REFERENCES Departments)
25
Relationship Sets to Tables (Contd)

• In translating a looping relationship set
  (without constraints) to a relation, attributes
  of the relation must include
• Keys built by concatenating the role indicators
  and the primary key of the participating entity
  set (as foreign keys).
• This set of attributes forms a superkey for the
  relation.
• All descriptive attributes.
• Explicit naming of the referenced key.

CREATE TABLE Reports_to( supervisor_ssn
CHAR(11), subordinate_ssn CHAR(11), PRIMARY
KEY (supervisor_ssn,
subordinate_ssn), FOREIGN KEY (supervisor_ssn)
REFERENCES Employees(ssn), FOREIGN KEY
(subordinate_ssn) REFERENCES
Employees(ssn))
26
Review Key Constraints

• Each dept has at most one manager, according to
  the key constraint on Manages.

budget
did
Departments
Translation to relational model?
Many-to-Many
1-to-1
1-to Many
Many-to-1
27
Translating ER Diagrams with Key Constraints
CREATE TABLE Manages( ssn CHAR(11), did
INTEGER, since DATE, PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees,
FOREIGN KEY (did) REFERENCES Departments)

• Map relationship to a table
• Note that did is the key now!
• Separate tables for Employees and Departments.
• 2nd solution Since each department has a unique
  manager, we could instead combine Manages and
  Departments.
• (The general case??)

CREATE TABLE Dept_Mgr( did INTEGER, dname
CHAR(20), budget REAL, ssn CHAR(11),
since DATE, PRIMARY KEY (did), FOREIGN
KEY (ssn) REFERENCES Employees)
28
Review Participation Constraints

• Does every department have a manager?
• If so, this is a participation constraint the
  participation of Departments in Manages is said
  to be total (vs. partial).
• Every did value in Departments table must appear
  in a row of the Manages table (with a non-null
  ssn value!)

since
since
name
name
dname
dname
lot
budget
did
budget
did
ssn
Departments
Employees
Manages
Works_In
since
29
Participation Constraints in SQL

• We can capture participation constraints
  involving one entity set in a binary
  relationship, but little else (without resorting
  to CHECK constraints).

CREATE TABLE Dept_Mgr( did INTEGER, dname
CHAR(20), budget REAL, ssn CHAR(11) NOT
NULL, since DATE, PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees, ON
DELETE NO ACTION)
30
Review Weak Entities

• A weak entity can be identified uniquely only by
  considering the primary key of another (owner)
  entity.
• Owner entity set and weak entity set must
  participate in a one-to-many relationship set (1
  owner, many weak entities).
• Weak entity set must have total participation in
  this identifying relationship set.

name
cost
pname
age
ssn
lot
Dependents
Policy
Employees
31
Translating Weak Entity Sets

• Weak entity set and identifying relationship set
  are translated into a single table.
• When the owner entity is deleted, all owned weak
  entities must also be deleted.

CREATE TABLE Dep_Policy ( pname CHAR(20),
age INTEGER, cost REAL, ssn CHAR(11) NOT
NULL, PRIMARY KEY (pname, ssn), FOREIGN
KEY (ssn) REFERENCES Employees, ON DELETE
CASCADE)
32
Review ISA Hierarchies
name
ssn
lot
Employees
hours_worked
hourly_wages
ISA

• As in C, or other PLs, attributes are
  inherited.
• If we declare A ISA B, every A entity is also
  considered to be a B entity.

contractid
Contract_Emps
Hourly_Emps

• Overlap constraints Can Joe be an Hourly_Emps
  as well as a Contract_Emps entity?
  (Allowed/disallowed)
• Covering constraints Does every Employees
  entity also have to be an Hourly_Emps or a
  Contract_Emps entity? (Yes/no)

33
Translating ISA Hierarchies to Relations

• General approach
• 3 relations Employees, Hourly_Emps and
  Contract_Emps.
• Hourly_Emps Every employee is recorded in
  Employees. For hourly emps, extra info recorded
  in Hourly_Emps (hourly_wages, hours_worked, ssn)
  must delete Hourly_Emps tuple if referenced
  Employees tuple is deleted).
• Queries involving all employees easy, those
  involving just Hourly_Emps require a join to get
  some attributes.
• Alternative Just Hourly_Emps and Contract_Emps.
• Hourly_Emps ssn, name, lot, hourly_wages,
  hours_worked.
• Each employee must be in one of these two
  subclasses.
• Overlap/covering constraints expressed in SQL
  only via assertions. (More on assertions later.)
  

34
Review Binary vs. Ternary Relationships
pname
age
Dependents
Covers

• What are the additional constraints in the 2nd
  diagram?

Bad design
pname
age
Dependents
Purchaser
Better design
35
Binary vs. Ternary Relationships (Contd.)
CREATE TABLE Policies ( policyid INTEGER,
cost REAL, ssn CHAR(11) NOT NULL,
PRIMARY KEY (policyid). FOREIGN KEY (ssn)
REFERENCES Employees, ON DELETE CASCADE)

• The key constraints allow us to combine Purchaser
  with Policies and Beneficiary with Dependents.
• Participation constraints lead to NOT NULL
  constraints.
• What if Policies is a weak entity set?

CREATE TABLE Dependents ( pname CHAR(20),
age INTEGER, policyid INTEGER, PRIMARY
KEY (pname, policyid). FOREIGN KEY (policyid)
REFERENCES Policies, ON DELETE CASCADE)
36
Views

• A view is just a relation, but we store a
  definition, rather than a set of tuples.

CREATE VIEW YoungActiveStudents (name,
grade) AS SELECT S.name, E.grade FROM
Students S, Enrolled E WHERE S.sid E.sid and
S.agelt21

• Views can be dropped using the DROP VIEW command.
• How to handle DROP TABLE if theres a view on the
  table?
• DROP TABLE command has options to let the user
  specify this RESTRICT / CASCADE.

37
Views and Security

• Views can be used to present necessary
  information (or a summary), while hiding details
  in underlying relation(s).
• Given YoungStudents, but not Students or
  Enrolled, we can find students s who are
  enrolled, but not the cids of the courses they
  are enrolled in.

38
Relational Model Summary

• A tabular representation of data.
• Simple and intuitive, currently the most widely
  used.
• Integrity constraints can be specified by the
  DBA, based on application semantics. DBMS checks
  for violations.
• Two important ICs primary and foreign keys
• In addition, we always have domain constraints.
• Powerful and natural query languages exist.
• Rules to translate ER to relational model