CS609

1

• CS609
• Relational model, relational algebra, SQL

2
Relational Model

• Most popular
• simplest
• most uniform data structures
• most formal
• files mathematical foundation

3
Relational Model  (1970  E.F. Codd)

• A data model
•         types         relationships        
  constraints         basic operations
• relational model - files mathematical
  foundation (set theory)
•         Relation - Table         Rows -
  Tuples         Attributes - Columns
• Domain data type of a column

4
 Relations

• TUPLE
• no composite values (all values are atomic)
• no multivalued attributes (First Normal Form)
• RELATION Characteristics 
• Set of tuples - not ordered
• Values within tuples are ordered
• ATTRIBUTES
•  Ai is an attribute with a domain dom(Ai)
•  degree of a relation - the number of attributes
• RELATION SCHEMA R(A1, A2,...,An)
• includes relation name R and list (set) of
  attributes Ai
• Fig 7.5

5
Glossary

• DOMAIN D
• specify data type, format for each domain
• is of data-type (or format)
• set of atomic values (relational model
  requirement)
• TUPLE
• no composite values (all values are atomic)
• no multivalued attributes (First Normal Form)

6
Relations

• RELATION (instance) r( R )
• current relation state  
• set of n-tuples (where n is a number of
  attributes)
• r t1, t2,...,tm each n-tuple t is an ordered
  list on n values
• t ltv1, v2,...vngt where vi is an element of
  dom(Ai) or null
• tAi is the value vi for attribute Ai of tuple
  t
• r( R ) is a subset of dom(A1) X dom(A2) X...X
  dom(An)
• the tuples themselves are not ordered

7
Relations

• relation intension refers to the schema, relation
  extension to the state
• all tuples within a relation are distinct
• Fig 7.6

8
Constraints

• DOMAIN CONSTRAINTS
• value of each attribute must be an atomic
  (single) value from the domain for that attribute
  (1st normal form - 1NF)
• no composite attributes

9
Constraints

• KEY CONSTRAINTS
• no two tuples can have the same combination of
  values for all their values (uniqueness
  constraint). Therefore, all elements are
  distinct.
• superkey sk
• any set of attributes with property
• no two tuples from the relation have the same
  combination of values for those attributes
• t1sk ! t2sk
• every relation has at least one superkey, what
  is it?

10
Constraints 

• key is a minimal superkey if 
• we cannot remove any attribute and still have
  uniqueness constraint hold
• candidate key is any one of the keys
• primary key PK is designated candidate key
  (underlined)

11
Constraints

• ENTITY INTEGRITY CONSTRAINTS
• no primary key value can be null

12
Constraints

• REFERENTIAL INTEGRITY CONSTRAINTS
• specified between two relations
• maintain the consistency among tuples in two
  relations
• a tuple in one relation that refers to another
  relation must refer to an existing tuple in that
  relation
• if one relation refers another, then the primary
  key of the referred relation is a foreign key FK
  in the referring relation.
• t1PK t2FK
• Fig 7.7

13
Relations

• RELATIONAL DATABASE SCHEMA
• a set of relation schemas and a set of
  referential integrity constraints
• S R1, R2,..., Rm and a set of integrity
  constraints IC
• RELATIONAL DATABASE INSTANCE DB
• DB r1, r2,...rm

14
Relational Rules - Summary

• 1.  1NF
• 2.  Access rows by content only (can't say 3rd
  row), unique rows
• 3. key constraints (all tuples distinct)
• Use primary key to uniquely identify each row
• entity constraints - no primary key null
• Additional constraints (other textbooks)
• domain constraint - defines domain values
• referential constraint - if a tuple refers to
  another tuple, it must be an existing tuples
• semantic constraint - defines rules for
  relationships

15
Notation for Relational Model

• Relational algebra (algebraic notation) and
  relational calculus - logical notation
• used to demonstrate potential for query language
  of relational DB
• algebra and calculus equivalent in expressive
  power

16
Relational Algebra Review

• http//cs.ua.edu/457/Notes/RelAlg.htm

17
Operations

• Set theoretic ops
• union, intersection, difference, cartesian
  product
• Native relational ops
• project, select, division, join - equi, theta,
  natural join
• R / S - result is those rows t such that for
  every row s in S, if concatenate t and s, can be
  found in R
• Other ops
• outer joins - left, right, aggregate (set)
  functions, grouping

18
Operations contd

• Precedence of relational algebra ops  (in order
  of highest)
• Project
• Select
• cartesian product
• join divide
• Minus
• Union
• intersection

19
Complete Set

• Can express any query (except aggregate
  functions) with the complete set
• Select s
• Project p
• Cartesian product X
• Union U
• Difference -
•     R/S R - ((R X S) - R)
• given R(A1...An,B1...Bm) S(B1...Bm)
• (pA1...AnR) - pA1...An((pA1...AnR X S) - R)
• R S (RUS)-((R-S) U (S-R))

20
The CAP database

• CAP CUSTOMERS, AGENTS, PRODUCTS, ORDERS
• CUSTOMERS(cid, cname, city, discnt)
• AGENTS(aid, aname, city, percent)
• PRODUCTS(pid, pname, city, quantity, price)
• ORDERS(ordno, month, cid, aid, pid, qty, dollars)

21
Queries

• Get customer Ids for customers who order product
  p01.
• Get customer names for customers who order
  product Pencil.
• Get all pairs of cids such that the corresponding
  customers are collocated (at the same location).
• Get customer names for customers who order all
  products.
• Get customer names for customers who order all
  products priced at less than 1.00.

22
DBMS market share

• According to Gartner and IDC DB market was 14B
  in 2005
• Oracle 48 market share
• IBM DB2 22 market share
• MS SQL Server 15
• Linux DB servers and SQL Server strongest
  momentum

23
(No Transcript)
24
Query Language

• SQL or SEQUEL - (Structured English Query
  Language)
• High-level DB language created at IBM  in 1970's
          DB system products using SQL released in
  early 80's
• Provides DML and DDL
• Standard SQL
• SQL-86, SQL-89 - minimal standard, still
  incompatibility product features
• ANSI/ISO - (SQL-92) SQL2 - tried to avoid
  incompatibility problems
• X/Open standard - extensions plus portability
• SQL-3, SQL1999, SQL-99
• Current standard SQL2003, SQL200n     
•                           

25
SQL

• Subselect - formal definition
•         Select expr , expr         From
  tablename alias , tablename alias        
  Where search_condition         Group By col
  , col         Having search_condition
• Full Select - formal definition
•      Subselect Union all subselect   Order
  By result_col asc desc , result_col asc
  desc

26
SQL Review

• SQL

27
Sample SQL queries

• Get customer identifiers for customers who have
  ordered at least twice through agent a01.
• For each product that has been ordered, get the
  product number and the total quantity. The output
  should be displayed in decreasing
  "total-quantity" order.
• Reduce the commission of agents in Tuscaloosa by
  50.
• Delete all the orders involving agents in
  Tuscaloosa.

28
Power/Weakness of SQL Chapter 3.11

•  SQL summary
• user specifies what is desired rather than how
• (e.g.  no need to write loop statement to
  traverse tuples)
• no implied ordering
• query optimizer decides how
• desired result described in one statement
• Avoids procedural complexities (non-procedural)
• A language with the power of relational algebra
• defined as relationally complete (Codd)

29
Weakness of SQL

• 1) Too many equivalent forms
• e.g.  in, any, exists             not in, ltgt
  all
• 2) No non-procedural language can have Turing
  power (computational complete), e.g. perform any
  computational procedure that can be specified in
  algorithmic terms
• Not true of SQL only relationally complete

30
Weakness

• 3) Some capabilities missing
• Selects cannot be arbitrarily nested like
  relational algebra
• loss of expressive power of a relational algebra
  expression
• 4) Cannot nest set functions
• e.g. Average of total dollar sales
• 5) No statistical functions (besides avg)
• e.g.  variance, mode

31
Weakness

• 6) Can find reports SQL can't create - need
  embedded SQL
• e.g.  categorize total sales based on size of
  sale group by 0-499, 500-999, etc.
• 7) No Transitive closure - arbitrary number
• managers of managers
• 8) Limited by Boolean conditions
• Cannot retrieve 5 out of 6
• Cannot list 20 agents with largest sales
• No distance to keywords
• Are 1-8 still true of ORACLE?

32
Strengths

• While subselects cannot be arbitrarily nested -
  have added power in search (where) condition
• Other strengths of SQL?