Chapter 2: Introduction to Relational Model

1
Chapter 2 Introduction to Relational Model

• Structure of Relational Databases
• Fundamental Relational-Algebra-Operations

2
Example of the instructor Relation
attributes (or columns)
tuples (or rows)
3
Attribute Types

• A relation is represented as a table. The term
  attribute (??) refers to a column of a table.
• ???????????, ???????.
• The set of allowed values for each attribute is
  called the domain of the attribute
• Attribute values are (normally) required to be
  atomic that is, indivisible (???????)
• multivalued attribute values are not atomic (see
  page 1.17)
• For example author Smith, Jones, Jones,
  Frick
• composite attribute values are not atomic
• For example publisher (McGraw-Hill, New
  York), (Oxford, London)
• The special value null is a member of every
  domain, which signifies that the value is unknown
  or does not exist.
• The null value causes complications in the
  definition of many operations, and will be
  discussed later.

4
Relation Schema and Instance

• A1, A2, , An are attributes
• R (A1, A2, , An ) is a relation schema
• Example
• instructor (ID, name, dept_name,
  salary)
• Formally, given sets D1, D2, . Dn, a relation r
  is a subset of D1 x D2 x x DnThus,
  a relation is a set of n-tuples (a1, a2, , an)
  where each ai ? Di
• Example
• D1 a, b, c, D2 1, 2, D1XD2 (a, 1), (a,
  2), (b, 1), (b, 2), (c, 1), (c, 2)
• The current values (relation instance) of a
  relation are specified by a table
• An element t of r is a tuple, represented by a
  row in a table.

5
Relations are Unordered

• Order of tuples is irrelevant (tuples may be
  stored in an arbitrary order)
• ????????physical level, ?logical level
• ?query?????????
• Example instructor relation with unordered
  tuples

6
Database

• A database consists of multiple relations
• Information about an enterprise is broken up into
  parts, where each relation storing one part of
  the information
• The university database example
• instructor (ID, name, dept_name, salary)
• department (dept_name, building, budget)
• student (ID, name, dept_name, tot_cred)
• course (course_id, title, dept_name, credits)
• prereq (course_id, prereq_id)
• Bad design (c.f. page 1.15) univ
  (instructor_ID, name, dept_name, salary,
  student_Id, ..)Normalization theory (Chapter 8)
  deals with how to design good relational
  schemas.

7
Keys

• Let K ? R
• K is a superkey of R if values for K are
  sufficient to identify a unique tuple of each
  possible relation r(R)
• Example ID, name, and ID,dept_name are
  all superkeys of instructor. (see page 2.5)
• Superkey K is a candidate key if K is
  minimalExample ID , name are both
  candidate keys for Instructor
• One of the candidate keys is selected to be the
  primary key.
• which one? ?????????,???????.
• Another example ??, ??,??,??, ??,
  ?????)?????candidate key, ? ????primary key.
• ??
• Key????????????
• ?????????????????????.,
• ?????dept_name

SK
CK PK
8
Foreign Keys

• Foreign key constraint Value in one relation
  must appear in another
• Referencing relation e.g., instructor
• Referenced relation e.g., department
• Will discuss this again in Chapter 3 and Chapter
  4.

• department

• instructor

9
Schema Diagram for University Database
10
Relational Query Languages

• Language in which user requests information from
  the database.
• Categories of languages
• Procedural
• non-procedural, or declarative
• Pure languages
• Relational algebra procedural
• Tuple (Domain) relational calculus declarative
• Algebra is based on operators.
• Example of arithmetic algebra 1 53
• How to write a query
• Determine which relations to use
• Determine which operators to use

11
Relational Algebra

• Relational operators
• select ?
• project ?
• Natural join
• Cartesian product x
• union ?
• Intersection ?
• set difference
• The operators take one or two relations as
  inputs and produce a new relation as a result.

12
Selection of tuples

• Relation r

• Selection
• s ???(r)
• Select tuples with
• AB and D gt 5
• s AB D gt 5 (r)

13
Selection of Columns (Attributes)

• Relation r

• Projection
• ? ???? (r)
• Select columns A and C
• ? A, C (r)
• -gt duplicates are removed

14
More Examples

• Return those instructors whose salaries are more
  than 85000. (see page 2.5, page2.6)
• s salarygt85000 (instructor)
• Output the attributes ID and Salary of
  instructors.
• ? ID, salary (instructor)
• Find the ID and salary for those instructors
• who have salary greater than 85000.
• ? ID, salary (s salarygt85000 (instructor))
• lt- composition

15
Joining two relations Cartesian Product

• Relations r, s

• r x s

• Example instructor X department gt 127 84
  tuples
• (see page 2.8) lt-???????dept_name???!

16
Joining two relations Natural Join

• Let r and s be relations on schemas R and S
  respectively. Then, the natural join of
  relations r and s is a relation on schema R ? S
  obtained as follows
• Consider each pair of tuples tr from r and ts
  from s.
• If tr and ts have the same value on each of the
  attributes in R ? S, add a tuple t to the
  result, where
• t has the same value as tr on r
• t has the same value as ts on s
• Example
• R (A, B, C, D)
• S (E, B, D)
• Result schema (A, B, C, D, E)
• r s is defined as ?r.A, r.B, r.C, r.D,
  s.E (?r.B s.B ? r.D s.D (r x s))

17
Natural Join Example

• Relations r, s

18
Example

• Instructor department (c.f., page 2.8)

• Example Output the attributes ID and building of
  instructors.
• ? ID, building (Instructor department)

19
Union of two relations

• Relations r, s

• r ? s

• Find the names of instructors and students.
• ? name (instructor) ? ? name (student)

20
Set difference of two relations

• Relations r, s

• r s

• Find the departments which do not hire
  instructors.
• ? dept _ name (department) ? dept _ name
  (instructor)

21
Set Intersection of two relations

• Relation r, s
• r ? s

• Find the instructors who are also students.
• ? name (instructor) ? ? name (student)