The Relational Model

1
The Relational Model

• Chapter 3

2
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

3
Relational Database Definitions

• Relational database a set of relations
• 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 type of each column.
• 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).

4
Example Instance of Students Relation

• Cardinality 3, degree 5, all rows distinct

• Do all columns in a relation instance have to
• be distinct?

5
Relational Query Languages

• A major strength of the relational model
  supports simple, powerful querying of data.
• Queries can be written intuitively, and the DBMS
  is responsible for efficient evaluation.
• The key precise semantics for relational
  queries.
• Allows the optimizer to extensively re-order
  operations, and still ensure that the answer does
  not change.

6
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, current standard)
• SQL-99 (major extensions)

7
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))
8
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

• Powerful variants of these commands are
  available more later!

9
Modifying Tuples

• Can modify the column values in an existing row
  using

UPDATE Students S SET S.age S.age
1, S.gpa S.gpa 1 WHERE S.sid
53688 UPDATE Students S SET S.gpa
S.gpa 0.1 WHERE S.gpa gt 3.3
10
Basic SQL Query
SELECT DISTINCT target-list FROM
relation-list WHERE qualification

• relation-list A list of relation names (possibly
  with a range-variable after each name).
• target-list A list of attributes of relations in
  relation-list
• qualification Comparisons (Attr op const or
  Attr1 op Attr2, where op is one of
  ) combined using AND, OR and
  NOT.
• DISTINCT is an optional keyword indicating that
  the answer should not contain duplicates.
  Default is that duplicates are not eliminated!

11
Basic SQL Query
CREATE syntax
CREATE TABLE table_name ( list_of_attributes
attribute_domain options - PRIMARY
KEY (attributes) - FOREIGN KEY (attributes)
REFERENCES (table_name) - ON
DELETE/UPDATE options - CASCADE - NO
ACTION (default) - SET DEFAULT )
12
Basic SQL Query
INSERT syntax
INSERT INTO table _name (attribute_list) VALUE
( value_of_attribute_list)
For example INSERT INTO Students (sid, name,
login, age, gpa) VALUES (53688, Smith,
smith_at_ee, 18, 3.2)
13
Basic SQL Query
DELETE syntax
DELETE FROM table _name Alias_name ( we
mostly use when two tables have
the same attributes
or table_name is very long) WHERE
qualification
For example DELETE FROM Students S WHERE
S.name Smith
14
Basic SQL Query
UPDATE syntax
UPDATE table _name Alias_name SET
adjust_equation_list WHERE qualification
For example UPDATE Students S SET
S.age S.age 1, S.gpa S.gpa 1 WHERE
S.sid 53688
15
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!

16
Key Constraints

• A set of fields is a (candidate) key for a
  relation if
• 1. Two distinct tuples cannot have identical
  values in all the fields of a key, and
• 2. No subset of the set of fields in a key is a
  unique identifier for a tuple
• Part 2 false? 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.

17
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.

18
Primary and Candidate Keys in SQL(Contd.)

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

• 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.

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

• An IC can prevent storage of instances that arise
  in practice!

19
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.

20
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
21
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.

22
Referential Integrity in SQL/92

• SQL/92 supports 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) DEFAULT
9999, cid CHAR(20), grade CHAR(2),
PRIMARY KEY (sid,cid), FOREIGN KEY (sid)
REFERENCES Students ON DELETE CASCADE ON
UPDATE NO ACTION )
23
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.

24
The SQL Query Language

• 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
25
Basic SQL Query
SELECT DISTINCT target-list FROM
relation-list WHERE qualification

• relation-list A list of relation names (possibly
  with a range-variable after each name).
• target-list A list of attributes of relations in
  relation-list
• qualification Comparisons (Attr op const or
  Attr1 op Attr2, where op is one of
  ) combined using AND, OR and
  NOT.
• DISTINCT is an optional keyword indicating that
  the answer should not contain duplicates.
  Default is that duplicates are not eliminated!

26
Example of Conceptual Evaluation
SELECT S.sname FROM Sailors S, Reserves
R WHERE S.sidR.sid AND R.bid103
27
A Note on Range Variables

• Really needed only if the same attribute appears
  twice in the WHERE clause. The previous query
  can also be written as

It is good style,however, to use range variables
always!
SELECT S.sname FROM Sailors S, Reserves
R WHERE S.sidR.sid AND R.bid103
BUT ok here SELECT S.sname FROM Sailors S WHERE
S.sname Smith SELECT sname FROM
Sailors WHERE sname Smith
OR
SELECT sname FROM Sailors, Reserves WHERE
Sailors.sidReserves.sid AND
bid103
28
Querying Multiple Relations

• What does the following query compute?

SELECT S.name, E.cid FROM Students S, Enrolled
E WHERE S.sidE.sid AND E.gradeA
Given the following instance of Enrolled (is this
possible if the DBMS ensures referential
integrity?)
we get
29
Logical DB Design ER to Relational

• Entity sets to tables.

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

• In translating a relationship set to a relation,
  attributes of the relation must include
• Keys for each participating entity set (as
  foreign keys).
• All descriptive attributes.
• Set of non descriptive attributes is a superkey
  for the relation.
• If there are no key constraints, this set of
  attributes is a candidate key.

31
Relationship Sets to Tables (without constraints)
since
name
dname
budget
ssn
lot
did
Works_In
Departments
Employees

• CREATE TABLE Works_In(
• ssn CHAR(11),
• did INTEGER,
• since DATE,
• PRIMARY KEY (ssn, did),
• FOREIGN KEY (ssn) REFERENCES Employees,
• FOREIGN KEY (did) REFERENCES Departments)

32
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
33
Relationship Sets to Tables

• Map relationship to a table
• Note that did is the key now!
• Separate tables for Employees and Departments.
• Since each department has a unique manager, we
  could instead combine Manages and Departments.

since
name
dname
budget
ssn
lot
did
Manages
Departments
Employees
CREATE TABLE Manages( ssn CHAR(11), did
INTEGER, since DATE, PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees,
FOREIGN KEY (did) REFERENCES Departments)
34
Relationship Sets to Tables (cont)Other
alternative for Manages Relation
Idea include the information about the
relationship set in the table corresponding to
the entity set with the key, taking advantage of
the key constraint. e.g. because department has
at most one manager, we can add the key fields of
the Employees tuple denoting the manager and the
since attribute to the Departments tuple.
CREATE TABLE Dept_Mgr( did INTEGER, dname
CHAR(20), budget REAL, ssn CHAR(11),
since DATE, PRIMARY KEY (did), FOREIGN
KEY (ssn) REFERENCES Employees)
35
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
36
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)
37
Participation Constraints in SQL (Cont.)

• Why does the addition of NOT NULL constraints to
  the Manages relation not enforce the constraint
  that each department must have a manager?
• What if anything is achieved by requiring that
  the ssn field of Manages be non-null?

since
name
dname
budget
ssn
lot
did
Manages
Departments
Employees
CREATE TABLE Manages( ssn CHAR(11), NOT
NULL????? did INTEGER, since DATE,
PRIMARY KEY (did), FOREIGN KEY (ssn)
REFERENCES Employees, FOREIGN KEY (did)
REFERENCES Departments)

• The constraint of ssn
• non-null implies that
• for every tuple in Manages
• There should be a manager.
• BUT does not ensure that
• each department has an entry
• in Manages!!!

38
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
39
Translating Weak Entity Sets to Relation

• 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)
40
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)

41
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.

42
Review Binary vs. Ternary Relationships
pname
age

• If each policy is owned by just 1 employee
• Key constraint on Policies would mean policy can
  only cover 1 dependent!
• What are the additional constraints in the 2nd
  diagram?

Dependents
Covers
Bad design
pname
age
Dependents
Purchaser
Better design
43
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)
44
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.

45
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 have are
  enrolled, but not the cids of the courses they
  are enrolled in.

46
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.

47
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

48
Exercise 3.4 p. 84

• What is the difference between a candidate
• key and the primary key for a given relation?
• What is a superkey?

49
Exercise 3.4 p. 84 Solution

• What is the difference between a candidate
• key and the primary key for a given relation?
• ? The primary key is the key selected by the
  DBA from among the group of candidate keys, all
  of which uniquely identify a tuple.
• What is a superkey?
• ? A superkey is a set of attributes that
  contains (perhaps properly) contains a key.

50
Exercise 3.5 p. 84

• Consider the instance of Students relation shown
  in above table
• Give an example of an attribute (or set of
  attributes) that you can deduce is NOT a
  candidate key, based on this instance being
  legal.
• Is there any example of an attribute (or set of
  attributes) that you can deduce is a candidate
  key, based on this instance being legal?

51
Exercise 3.5 p. 84 Solution

• Give an example of an attribute (or set of
  attributes) that you can deduce is NOT a
  candidate key, based on this instance being
  legal.
• Examples of non-candidate keys include the
  following name, age. (Note that gpa can
  NOT be declared a non-candidate key from this
  evidence alone (even though common sense tells us
  that clearly more than one student could have the
  same grade point average)

52
Exercise 3.5 p. 84 Solution(cont.)

• Is there any example of an attribute (or set of
  attributes) that you can deduce is a candidate
  key, based on this instance being legal?
• ? You CANNOT determine a key of a relation given
  only one instance of the relation. The fact that
  the instance is legal is immaterial. A
  candidate key, as defined here, is a key, not
  something that only might be a key. The
  instance shown is just one possible snapshot of
  the relation. At other times, the same relation
  may have an instance (or snapshot) that contains
  totally different set of tuples, and we cannot
  make predictions about those instances based only
  upon the instance that we are given.

53
Exercise 3.7, on P. 84

• Consider the relations Students, Faculty,
  Courses, Rooms, Enrolled, Teaches, and Meets_in
  that are defined below
• Students (sid string, name string, login
  string, age integer, gpa real)
• Faculty (fid string, fname string, sal real)
• Courses (cid sting, cname string, credits
  integer)
• Rooms (rno integer, address string, capacity
  integer)
• Enrolled (sid string, cid string, grade
  string)
• Teaches (fid string, cid string)
• Meets_In (cid string, rno integer, time
  string)
• List all the foreign key constraints among these
  relations.
• Give an example of (plausible) constraint
  involving one or more of these relations that is
  not a primary key or foreign key constraint.

54
Exercise 3.7, P. 84 Solution

• ?There is no reason for a foreign key
• constraint (FKC) on the Students, Faculty,
• Courses, or Rooms relations. These are the most
  basic
• relations and must be free-standing. Special
  care must
• be given to entering data into these base
  relations.
• ? In the Enrolled relation, sid and cid should
  both
• have FKCs placed on them. (Real students must
  be enrolled in real courses.) Also, since real
  teachers must teach real courses, both the fid
  and the cid fields in the Teaches relation should
  have FKCs. Finally Meets_In should place FKCs on
  both the cid and rno fields.

55
Exercise 3.7, P. 84 Solution (Cont.)

• 2. It would probably be wise to enforce a few
  other constraints on this DBMS the length of
  sid, cid, and fid could be standardized
  checksums could be added to these identification
  numbers limits could be placed on size of the
  numbers entered into the credits, capacity, and
  salary fields an enumerated type should be
  assigned to the grade field (preventing a student
  from receiving a grade of G, among other things),
  etc.

56
Exercise 3.9 p. 85.

• Consider the SQL query whose answer is shown
  above
• Modify query such that only the login column is
  included in answer
• If clause WHERE S.gpa 2 is added to original
  query, what is the set of tuples in the answer?

57
Exercise 3.9 p. 85.Solution

• SELECT login
• FROM Students
• WHERE S.age lt 18

2. Mada is omitted
58
(No Transcript)
59
(No Transcript)
60
Exercise 2.3 Solution
61
Translate the previous ER diagram into a
relational schema, and show the SQL statements

• 1. CREATE TABLE PROFESSOR
• (SSN CHAR(11),
• AGE INTEGER,
• RANK CHAR(5),
• SPECIALITY CHAR(15),
• PRIMARY KEY (SSN))
• 2. CREATE TABLE DEPT_RUNS
• (DNO CHAR(5),
• SSN CHAR(11) NOT NULL,
• DNAME CHAR(20),
• OFFICE CHAR(5),
• PRIMARY KEY (DNO),
• FOREIGN KEY (SSN) REFERENCES PROFESSOR)

• 3. CREATE TABLE WORK_DEPT
• (SSN CHAR(11),
• DNO CHAR(5),
• PC_TIME INTEGER,
• PRIMARY KEY (SSN, DNO),
• FOREIGN KEY (SSN)
• REFERENCES PROFESSOR,
• FOREIGN KEY (DNO)
• REFERENCES DEPT_RUNS,
• ON DELETE CASCADE)
• The participation constraint can not be captured
  in the table.

62
Translate the previous ER diagram into a
relational schema, and show the SQL statements
(cont.)

• 4. CREATE TABLE PROJECT_MANAGES
• (PID CHAR(11),
• SSN CHAR(11) NOT NULL,
• SPONSOR CHAR(11),
• START_DATE DATE,
• END_DATE DATE,
• BUDGET INTEGER,
• PRIMARY KEY (PID),
• FOREIGN KEY (SSN) REFERENCES PROFESSOR)

• 5. CREATE TABLE WORK_IN
• (SSN CHAR(11),
• PID CHAR(11),
• PRIMARY KEY (SSN, PID),
• FOREIGN KEY (SSN)
• REFERENCES PROFESSOR,
• FOREIGN KEY (PID)
• REFERENCES ROJECT_MANAGES ,
• ON DELETE CASCADE)
• The participation constraint can not be captured
  in the table.

63
Translate the previous ER diagram into a
relational schema, and show the SQL statements
(cont.)

• 6. CREATE TABLE GRADUATE_MAJOR
• (SSN CHAR(11),
• DNO CHAR(5) NOT NULL,
• AGE INTEGER,
• DEG_PROG CHAR(11),
• NAME CHAR(20),
• ADVISOR_SSN CHAR(11),
• PRIMARY KEY (SSN),
• FOREIGN KEY (ADVISOR_ SSN) REFERENCES
  GRADUATE_MAJOR (SSN)
• FOREIGN KEY (DNO)
• REFERENCES DEPT_RUNS)

• 7. CREATE TABLE WORK_PROJ_SUPERVISES
• (SSN CHAR(11),
• PID CHAR(11),
• PSSN CHAR(11) NOT NULL,
• PRIMARY KEY (SSN, PID),
• FOREIGN KEY (SSN)
• REFERENCES GRADUATE_MAJOR,
• FOREIGN KEY (PID)
• REFERENCES PROJECT_MANAGES ,
• FOREIGN KEY (PSSN)
• REFERENCES PROFESSOR (SSN),
• ON DELETE CASCADE)
• The participation constraint can not be captured
  in the table.