The Relational Model

1
The Relational Model

• Description of relations
• Domain constraints
• SQL-92
• Creating relations
• Modifying relations
• Integrity constraints
• Key constraints
• Foreign key constraints
• General constraints
• Basic queries
• Translating the ER model to the relational model
  and SQL-92
• Views

2
Introduction

• A DB is a collection of relations.
• A relation is a table with rows and columns
  (records and fields).
• A relation consists of a schema and an instance.
• Schema describes the column headings.
• Specifies the relations name,
• the name of each field (column / attribute),
• the domain of each field.
• Instance is a particular table (a set of
  records).
• An instance is a set of tuples (rows).
• No two rows are identical, because a relation
  instance is a set (but note that implementation
  may differ from theory).

3
Domain constraints

• Domain constraints specify the domain of each
  field.
• They are part of a relation schema
• They specify that each instances values must be
  from the domain associated with the column.
• The domain of a field is analogous to a type.
• Formal description
• Let R(f1D1, , fnDn) be a relation schema with
  n fields (fi), and for each fi, 1 ? i ? n let
  Domi be the set of values associated with Di. An
  instance of R that satisfies the domain
  constraints is a set of tuples f1d1, ,
  fndn d1 ? Dom1, , dn ? Domn
• Other terminology
• The degree (or arity) of a relation is the number
  of fields.
• The cardinality of a relation instance is the
  number of tuples in it.

4
SQL-92

• SQL Structured Query Language.
• The query language of the System-R DBMS developed
  by IBM.
• The most widely used relational DB language.
• First standard developed by ANSI in 1986, called
  SQL-86.
• ANSI American National Standards Institute.
• Minor revision in 1989 (SQL-89).
• Major revision in 1992 (SQL-92).
• SQL-92 a collaboration of ANSI and ISO.
• ISO International Standards Organization.
• Actual implementations (e.g. SQL Server) may
  differ from SQL-92.

5
Creating a Relation in SQL

• The subset of SQL responsible for creation,
  modification and deletion of tables is the DDL.
• To create a table
• CREATE TABLE TableName
• (fieldName1 DOMAIN
• fieldNamen DOMAIN)
• To insert a tuple
• INSERT
• INTO TableName (fldNm1, , fldNmn)
• VALUES (v1, , vn)
• The list of column names in the INTO clause is
  optional the values must then be in the
  appropriate order.

6
Modifying a Relation in SQL

• To delete tuple
• DELETE
• FROM TableName Identifier
• WHERE Condition
• To delete all the tuples in a table
• DELETE
• FROM TableName
• To modify tuples
• UPDATE TableName Identifier
• SET Identifier.fieldName value
• WHERE Condition
• Note that the WHERE clause is evaluated first to
  determine which rows the SET clause applies to.
• To delete a table
• DROP TABLE TableName

7
Other Modifications

• To insert multiple tuples use a query to return
  the values required for the tables attributes.
• INSERT INTO Person (
• SELECT E.SIN, E.fn, E.ln
• FROM Employee E
• WHERE fn Smith)
• Tables attributes can be modified
• adding columns
• ALTER TABLE Person
• ADD age
• or deleting columns
• ALTER TABLE Person
• DROP age

8
Integrity Constraints

• An integrity constraint restricts the data that
  can be stored.
• This prevents invalid data being added to the DB
• e.g. different people with the same SIN.
• When a DB schema is defined the integrity
  constraints must also be defined.
• When a DB is run the DBMS will check that each
  update does not violate an integrity constraint.
• Domain constraints are a type of integrity
  constraint and are specified when tables are
  created.
• Other integrity constraints
• Key constraints
• Foreign key constraints
• General constraints

9
Key Constraints

• A key constraint states that a subset of the
  fields in a relation is unique.
• To satisfy a key constraint the subset should be
  minimal.
• It uniquely identifies the tuple.
• It, therefore, is a candidate key
• Every relation has a candidate key because a
  relation is a set.
• A relation may have more than one candidate key.
• One candidate key can be identified as the
  primary key and used to refer to the tuple.

10
Key Constraints in SQL

• Specifying key constraints in SQL
• CREATE TABLE TableName
• (fldNm1 DOMAIN
• fldNmn DOMAIN
• UNIQUE (fldNmi, fldNmj)
• CONSTRAINT Id PRIMARY KEY
• (fldNmk, fldNml))
• The UNIQUE constraint specifies a candidate key.
• The PRIMARY KEY constraint specifies the primary
  key of the relation and gives it an identifier so
  that it may be identified if violated.

11
Foreign Key Constraints

• Called foreign key constraint because it
  references the primary key of another relation.
• Two relations may be linked so that if the data
  in one changes the other must be checked (and
  maybe modified too).
• e.g. entities that have total participation in a
  relationship.
• One relation (the one with the foreign key)
  references the other.
• The foreign key in the referencing relation must
  match the primary key in the referenced relation.
• Must have the same number of columns.
• The column types must be compatible.
• The column names may be different.
• Whenever a tuple in the referencing relation is
  changed (or added) the referenced relation is
  checked to ensure that the foreign key exists in
  it.

12
Foreign Keys in SQL

• Specifying foreign key constraints in SQL
• CREATE TABLE TableName
• (fldNm1 DOMAIN
• fldNmn DOMAIN
• PRIMARY KEY (fldNmk, fldNml)
• FOREIGN KEY (fldNmi) REFERENCES
• RefTableName)
• Note that these constraints have not been named
  (using the CONSTRAINT keyword).
• The table noted as RefTableName is the referenced
  table.

13
General Constraints

• Domain, primary key and foreign key constraints
  are fundamental.
• A DB may also require other constraints on data.
• We may want to limit domain values (e.g. age
  limits).
• More complex constraints that involve multiple
  attributes may be required.
• Currently systems (such as SQL) support two kinds
  of constraints.
• Table constraints.
• Associated with a single table and checked when
  the table is modified.
• Assertions
• Involve several tables and are checked when any
  one of these is modified.
• We will look at these in SQL later in the course.

14
Enforcing Integrity Constraints

• What happens when a constraint is violated?
• Primary key constraint
• The transaction is rejected.
• Foreign key constraint insertions or updates in
  the referring table
• Reject the transaction if the foreign key does
  not exist in the referenced table.
• Foreign key constraint deletions or updates in
  the referenced table
• If a tuple in the referenced table is deleted
  what happens to the tuples in the referencing
  table with that foreign key?
• Delete all such tuples.
• Reject the original deletion.
• Set the foreign key attributes to some default
  value.
• Set the foreign key attributes to null
• If a tuple in the referenced table is updated
  (changed) the same options as noted above are
  available.

15
Introduction to Queries

• A query is a question about data in a DB. The
  answer consists of a relation containing the
  result.
• The basic structure of an SQL query is
• SELECT tbl.col1, , tbl.coln
• FROM tbl1, , tblm WHERE Condition1, ,
  conditioni
• For example
• SELECT C.fName, C.lName
• FROM Customer C
• WHERE C.income gt 40000
• The select clause identifies which columns of
  which tables are to be returned.
• The from clause identifies the tables to be
  queried.
• The where clause identifies conditions that
  tuples to be selected must meet.
• We will study queries in more detail later.

16
Logical DB Design

• The ER model is a high level model for
  representing the initial design.
• An ER design can be translated into a relational
  schema (and therefore SQL).
• Each entity and relationship can be represented
  by a unique table
• Strong entity sets
• one column for each attribute
• each row represents an entity
• the domains of attributes and the tables primary
  key should be known.
• Example
• CREATE TABLE Customer
• (fNAme CHAR(20)
• lName CHAR(20)
• sin CHAR(11)
• income INTEGER
• PRIMARY KEY (sin))

17
Relationship Sets to Tables

• The attributes of a relationship set include
• The primary keys of the participating entity
  sets.
• The descriptive attributes of the relationship
  set.
• The primary key of a relationship set is
• The non-descriptive attributes.
• This assumes no key constraints (i.e. many
  many).
• The attributes of the entities involved in the
  relationship should be declared as foreign keys.
• Example
• CREATE TABLE LivesAt
• (sin CHAR(11)
• city CHAR(20)
• street CHAR(11)
• number INTEGER
• PRIMARY KEY (sin, city, street, number)
• FOREIGN KEY (sin) REFERENCES Customer
• FOREIGN KEY (city, street, number)
  REFERENCES Address)

18
Translating Relationship Sets with Key Constraints

• Determine attributes for the table as for a
  relationship set with no key constraints
• Primary Key
• Given that the relationship set involves n entity
  sets and some m of these (where m ? n) have key
  constraints.
• The primary key for any one of the m entity sets
  is a candidate key for the relationship set.
• Select one to be the primary key.
• Example
• CREATE TABLE Owns
• (sin CHAR(11)
• policyNo INTEGER NOT NULL
• PRIMARY KEY (sin)
• FOREIGN KEY (sin) REFERENCES Customer
• FOREIGN KEY (policyNo) REFERENCES Policy)
• Note that even though PolicyNo is not part of the
  primary key of the table it should still be
  declared as a foreign key.

19
Is a Table Necessary for a Relationship Set?

• It may not be necessary to create a table for a
  relationship set.
• If the relationship is M M a table is required
  why?
• If there are key constraints on the relationship
  a table is not required.
• The information about the relationship set can be
  included in the table representing the entity
  that provided the primary key.
• If the entity has partial participation in the
  relationship.
• The fields corresponding to the other attributes
  of the relationship will sometimes be made null.
• This wastes space.
• The key attributes from the other entities should
  be specified as foreign keys.

20
Omitting a Table for a Relationship Set

• Example (omitting Owns and Monitors)
• CREATE TABLE Customer
• (fNAme CHAR(20)
• lName CHAR(20)
• sin CHAR(11)
• income INTEGER
• policyNo INTEGER
• e_sin CHAR(11)
• PRIMARY KEY (sin)
• FOREIGN KEY (policyNO) REFERENCES Policy)
• Participation constraints
• Best reflected using the above approach for
  translating relationship sets.
• Declare attribute on which there is a foreign key
  constraint as not null.
• Thus each tuple of the entity must have a
  relationship with the other entity.
• To reflect participation constraints on a
  relationship with no key constraints is more
  complicated and requires assertions.

21
Translating Weak Entity Sets

• A weak entity set has the following
  characteristics
• Total participation.
• Key constraint.
• Only has a partial key.
• In addition, we should require that on deleting
  an owner entity the corresponding weak entities
  should be deleted.
• Example
• CREATE TABLE Dependent
• (d_fNAme CHAR(20)
• dob DATE
• age INTEGER
• income INTEGER
• sin CHAR(11)
• PRIMARY KEY (sin, d_fNAme, age)
• FOREIGN KEY (sin) REFERENCES Customer
• ON DELETE CASCADE)
• Note that because sin is part of the primary key
  it cannot be null.

22
Translating Class Hierarchies

• There are two basic approaches to translating
  class hierarchies to tables.
• The first approach is to create tables for the
  superclass and each subclass
• The superclass table contains attributes of the
  superclass entity.
• The subclass tables contain their attributes and
  the key attributes of the superclass.
• When a superclass tuple is deleted the delete
  should be cascaded to the subclasses.
• This approach is always applicable.
• The second approach is to create tables for the
  subclasses only.
• Subclass tables contain their attributes and all
  the attributes of the superclass.
• This approach is only possible when there are no
  entities in the superclass that are not also
  members of (at least) one of the subclasses.
• Overlap and coverage constraints have to be
  expressed using assertions.

23
Translating Aggregation

• Aggregation involves creating an entity from two
  entities and their relationship.
• This aggregate entity has a relationship with
  another entity. This relationship has the
  following attributes
• The key attributes of the normal entity.
• The key attributes of the relationship in the
  aggregate entity.
• Descriptive attributes of itself.
• If the aggregation has total participation in the
  relationship the relation for the relationship
  between the two entities in the aggregation can
  be dropped.
• Consider the insurance example
• The Owns relation has a primary key of sin and
  has attributes
• policyNo
• sin
• The monitors relation will have attributes for
  sin and e_sin and a primary key of sin.
• Adding policyNo as an attribute of Monitors
  allows Owns to be dropped.

24
Introduction to Views

• Views are tables not explicitly stored in a DB.
• Views are computed as needed from a view
  definition.
• Example
• CREATE VIEW Customer_Fee
• (sin, fName, fee)
• AS SELECT C.sin, C.fName, P.fee
• FROM Customer C, Policy P
• WHERE C.policyNo P.policyNo
• Once defined, view may be referred to in the same
  way as a table.
• Views serve a number of purposes
• Convenience they allow users to access the data
  they require without referring to a number of
  tables.
• Security users can only see appropriate data.
• Independence if the logical schema of a DB is
  changed a view can be re-defined so that users
  are not aware of the changes.

25
Updates on Views

• An update refers to adding, deleting or changing
  data in a table.
• While views may be treated as base tables updates
  using views may cause problems.
• If a row is deleted from a view it is unclear how
  the deletions should be reflected in the base
  tables.
• Inserting a row in a view may result in adding
  nulls to base tables for attributes not shown in
  the view.
• In particular, if the view does not include key
  attributes, this may even result in the view not
  being correctly updated.
• The same problems apply to modifications.
• In SQL, updates on views are generally limited to
  views that are defined on a single base table.

26
Insurance Example
Dependent
depends
Customer
Policy
Employee
Address
27
Example Insurance Sales

• customer
• fName
• lName
• SIN
• income
• address
• city
• street
• number
• category
• dependent
• d_fName
• DOB
• age
• income

• policy
• policyNo
• startDate
• endDate
• coverage
• fee
• employee
• e_SIN
• e_fName
• e_lName
• phone