Introduction to Database Systems

1
The Relational Model
2
Relational Model Concepts

• The relational data model is based on the concept
  of a relation.
• The strength of the relational approach to data
  management comes from the formal foundation
  provided by the theory of relations.
• A Relation is a mathematical concept based on the
  ideas of sets.
• The model was first proposed by Dr. E.F. Codd of
  IBM Research in 1970 in the following paper
• "A Relational Model for Large Shared Data Banks,"
  Communications of the ACM, June 1970.
• The above paper caused a major revolution in the
  field of database management and earned Dr. Codd
  the ACM Turing Award.

3
Relational Database

• Relational database A set of relations
• Relation
• Instance A table with rows and columns
• Cardinality Number of rows
• Degree (or arity) Number of fields (or columns,
  or attributes)
• Schema Specifying name of relation, plus name
  and type of each column
• Students(sid string, name string, login
  string, age integer, gpa real)
• A relation can be viewed as a set of unique rows
  or tuples (i.e., all rows are distinct).
• Commercial DBMSs allow tables to have duplicate
  rows.

4
Example Instance of Students Relation
Students
sid name login age gpa
50000 Dave dave_at_cs 19 3.5
53666 Jones jones_at_cs 18 3.4
53688 Smith smith_at_ee 18 3.2

• Cardinality 3, degree 5, all rows distinct
• Do all columns in a relation instance have to be
  distinct?
• The tuples are not considered to be ordered, even
  though they appear to be in the tabular form.
• All values in a tuple are considered atomic
  (indivisible).
• Each value in a tuple must be from the domain of
  the attribute for that column.

5
Relational Query Languages

• The relational model supports simple, powerful
  querying of data.
• Queries can be written intuitively, and the DBMS
  is responsible for efficient evaluation.
• Precise semantics for relational queries.
• Allows the optimizer to extensively re-order
  operations, and still ensure that the answer is
  correct.

6
The SQL Query Language

• Developed by IBM (system R) in the 1970s
• E.G., to find all 18 year old students, we can
  write
• SELECT
• FROM Students S
• WHERE S.age18

sid name login age gpa
53666 Jones jones_at_cs 18 3.4
53688 Smith smith_at_ee 18 3.2
7
Querying Multiple Relations

• SELECT S.name, E.cid
• FROM Students S, Enrolled E
• WHERE S.sidE.sid AND E.gradeA

name cid
Jones C03
Enrolled
Students
sid cid grade
50000 C01 B
53666 C03 A
53688 C01 B
sid name login age gpa
50000 Dave dave_at_cs 19 3.5
53666 Jones jones_at_cs 18 3.4
53688 Smith smith_at_ee 18 3.2
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.
• CREATE TABLE Students
• (sid CHAR(20), name CHAR(20), login CHAR(10),
  age INTEGER, gpa REAL)
• As another example, the Enrolled table holds
  information about courses that students take.
• CREATE TABLE Enrolled
• (sid CHAR(20), cid CHAR(20), grade CHAR(2))

9
Deleting 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
• No two distinct tuples can have same values in
  all key fields, and
• This is not true for any subset of the key.
• Part 2 false? A superkey.
• If theres gt1 key for a relation, it has several
  candidate keys. One of the candidate keys is
  chosen (by DBA) to be the primary key.
• For example
• sid is a key for Students. (What about name?)
• The set sid, gpa is a superkey.
• The primary key attributes cannot have null
  values (entity integrity).

13
Primary and Candidate Keys in SQL

• Primary keys and candidate keys are specified
  using PRIMARY KEY and UNIQUE, respectively.
• For example
• For a given student and course, there is a single
  grade.
• CREATE TABLE Enrolled
• (sid CHAR(20), cid CHAR(20), grade CHAR(2),
• PRIMARY KEY (sid, cid) )
• 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) )
• Used carelessly, an IC can prevent the storage of
  database instances that arise in practice!

14
Foreign Keys

• 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.
• For example sid of Enrolled is a foreign key
  referring to Students.

Enrolled
Students
sid name login age gpa
50000 Dave dave_at_cs 19 3.5
53666 Jones jones_at_cs 18 3.4
53688 Smith smith_at_ee 18 3.2
sid cid grade
50000 C01 B
53666 C03 A
53688 C01 B
15
Referential Integrity

• Referential integrity constraint
• The value in the foreign key column (or columns)
  FK of the referencing relation R1 can be either
• a value of an existing primary key value of a
  corresponding primary key PK in the referenced
  relation R2, or
• a null.
• In case 2, the FK in R1 should not be a part of
  its own primary key.
• If all foreign key (referential integrity)
  constraints are enforced, referential integrity
  is achieved, i.e., no dangling references.

16
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
sid name login age gpa
50000 Dave dave_at_cs 19 3.5
53666 Jones jones_at_cs 18 3.4
53688 Smith smith_at_ee 18 3.2
sid cid grade
50000 C01 B
50000 C03 A
53688 C01 B
17
Enforcing Referential Integrity

• What if an Enrolled tuple with a non-existent
  student id is inserted? (Reject it!)
• What 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.
• Set the foreign key value in tuples that refer to
  it to a special value null. (It doesn't work in
  this example. Why?)
• Similar if primary key of Students tuple is
  updated.

18
Referential Integrity in SQL

• SQL 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), cid CHAR(20), grade CHAR(2),
• PRIMARY KEY (sid, cid),
• FOREIGN KEY (sid) REFERENCES Students
• ON DELETE CASCADE ON UPDATE SET DEFAULT)

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