Transcript The Relational Model

Introduction to Relations
Relational Model : Topic #1
Introduction to Database Systems
1
Why Study the Relational Model?

Data Model : Math abstraction + operations

Most widely used data model.
– IBM, Informix, Microsoft, Oracle, Sybase,

And it’s really simple, yet powerful
Introduction to Database Systems
2
Relational Database: Definitions
Relational database: a set of relations.
 Relation: made up of 2 parts:

– Schema : specifies name of relation, plus name and
type of each column. (meta-information)

E.g. Students(sid: string, name: string, login: string,
age: integer, gpa: real)
– Instance : a table, with rows and columns. #rows =
cardinality, #fields = degree / arity

A relation instance is a set of rows or tuples.
(i.e., all rows are distinct)
Introduction to Database Systems
3
Example Instance of Students Relation
sid
53666
53688
53650
name
login
Jones jones@cs
Smith smith@eecs
Smith smith@math
age
18
18
19
gpa
3.4
3.2
3.8
 Cardinality = 3, degree = 5 , all rows distinct
 Do all column values in a relation instance have to
be distinct?
Introduction to Database Systems
4
Integrity Constraints (ICs)

IC: condition that must be true for any
instance of the database.
– ICs are specified when schema is defined (by
whom?)
– ICs are checked when relations are modified

A legal instance of a database is one that
satisfies all 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!
Introduction to Database Systems
5
Primary Key for a Relation

A set of fields is a key if :
1. no two tuples can have same values in all key
fields, and
2. this is not true for any subset of the key
– Part 2 false? A superkey.
– If there’s >1 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.
Introduction to Database Systems
6
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.)

E.g. sid is a foreign key referring to Students:
– Enrolled(sid: string, cid: string, grade: string)
– All foreign key constraints are enforced? referential
integrity, i.e., no dangling references.
– Name a data model w/o referential integrity!
Introduction to Database Systems
7
Where do ICs Come From?
Based on semantics of the real-world
enterprise
 Can check a database instance to see if an IC
is violated, but can NEVER infer an IC by
looking at an instance:

– IC is a statement about all possible instances!
– From example: name is not a key, but DBA says
that sid is a key.

Key and foreign key ICs are the most
common; more general ICs supported too.
Introduction to Database Systems
8
Formal Relational Query Languages
Two mathematical Query Languages form the
basis for “real” languages (e.g. SQL), and for
implementation:
 Relational Algebra: More operational, very
useful for representing execution plans.
 Relational Calculus: Lets users describe what
they want, rather than how to compute it.
(Non-operational, declarative.)
Understanding Algebra & Calculus is key to
understanding SQL, query processing!
Introduction to Database Systems
9
Relational Query Languages
Query languages: allow manipulation and retrieval of
data from a database.
 Relational model supports simple, powerful query
languages

– strong formal foundation based on logic
– allows for much optimization

Query Languages != programming languages!
– query languages not “Turing complete”
– QL’s not intended to be used for complex calculations
– QL’s convenient for accessing large data sets (unlike PL’s!)
Introduction to Database Systems
10