Chapters 4,5

1
Topics

• Chapters 4,5
• SQL Examples
• Chapter 6
• Relational Algebra
• Relational Calculus
• Chapter 7
• Data Modeling

2
Views

• Logical data independence
• mask changes to the underlying database schema
• View -- a table whose rows are not explicitly
  stored in the database
• create view Sales ( moviename, cost ) as
• select moviename, cost from Rental where Cost lt
  2.00

How is this implemented?
3
Altering

• Drop table somename ________
• restrict delete table unless some view or
  integrity constraint refers to somename
• cascade delete table and referencing views,
  repeating as needed.
• Drop view somename
• Alter Table somename
• Add Column name_and_type
• Drop Column name

4
Updateable

• Tables
• insert into somename values ...
• Views
• some views are updateable, some are not.
• SQL-92 specifies that updates are only allowed on
  views involving a single base table using
  selection and project.
• SQL-99 has updateable views and insertableinto
  views, which could use multiple base tables.

5
Example - Definitions

• Create table Movies
• (id integer,
• moviename char(60),
• cost number (5,2),
• primary key ( id ) )
• create view sales (moviename, cost) as
• select moviename, cost from movies
• where cost lt 2.99
• -- A
• Update Movies set cost 5.00 where id 123
• -- B
• Update Sales set cost 1.99 where moviename
  Star Trek
• -- C
• Update Sales set cost cost 5.00 where cost lt
  1.00

6
Exercise 5.9

• CREATE VIEW DEPT_SUMMARY (D, C, TOTAL_S,
  AVERAGE_S)
• AS SELECT DNO, COUNT(), SUM(SALARY), AVG(SALARY)
• FROM EMPLOYEE
• GROUP BY DNO --- which of the followings is
  allowed?
• --A
• SELECT FROM DEPT_SUMMARY
• --B
• SELECT D, C FROM DEPT_SUMMARY WHERE TOTAL_S gt
  100000
• --C
• SELECT D, AVERAGE_S FROM DEPT_SUMMARY WHERE C gt (
  SELECT C FROM DEPT_SUMMARY WHERE D 4)
• --D
• UPDATE DEPT_SUMMARY SET D 3 WHERE D 4
• --E
• DELETE FROM DEPT_SUMMARY WHERE C gt 4

7
Activity
Product(maker, model, country) PC (model, speed,
ram, hd, cd, price) Laptop(model, speed, ram, hd,
screen, price) Printer (model, color, type, price)
1. What PC models have a speed of at least
1.6? 2. What manufacturers make laptops with a
harddisk of at least 40 Gbyte? 3. List all models
and prices of all products made by manufacturer
B. 4. Find the model numbers of all color laser
printers. 5. What manufacturers make laptops, but
not PCs? 6. What manufacturer makes the fastest
PC?
8
Chapter 6

• Query languages are specialized languages for
  asking questions.
• Relational Algebra
• formal query language for the relational model
• describes step-by-step procedure for computing
  the desired result.
• Relational Calculus
• non-procedural equivalent
• i.e. describe the set of answers without being
  explicit about how they should be computed.

Historically, the relational algebra and
calculus were developed before the SQL language.
In fact, in some ways, SQL is based on concepts
from both the algebra and calculus, as we shall
see.
9
Operations

• Selection ?
• Projection ?
• ? name, rating ( ? rating gt 8 ( S2 ) )
• SQL select name, rating from S2 where rating gt
  8
• (To keep it readable, subscripts wont be used)
• ? name, rating ( ? rating gt 8 ( S2 ) )
• 1) select all tuples from S2 with rating greater
  than 8
• 2) from those tuples, only keep the fields name
  and rating

10
Set Operations

• Union R ? S
• Intersect R ? S
• Set Difference R - S
• Cross-product R ? S

11
Examples
S2 Sid name rating age 28 yuppy 9 35.0 31 lubber 8
55.5 44 guppy 5 35.0 58 rusty 10 35.0
S1 Sid name rating age 22 dustin 7 45.0 31 lubber
8 55.5 58 rusty 10 35.0
R1 Sid bid day 22 101 10/10/96 58 103 11/12/96
12
Joins

• Condition Joins
• R c S ? cond (R ? S)
• Equijoin -- join based upon equality
• if using R.name1 S.name2, then drop S.name2
  from output
• Natural join
• is an equijoin that matches on all fields having
  the same name in R and S

13
Examples

• Section 6.5 has examples to read and understand.
• Q2 Find the names of sailors who have reserved a
  red boat
• ? sname (( ? colorred Boats) Reserves
  Sailors)

14
QPT Query Parse Tree

• Select name from Reserves, Sailors
• where Reserves.id Sailors.id and Reserves.bid
  100 and Sailors.rating gt 5
• ? name (? bid100 ? rating gt 5 ( Reserves
  Sailors)

? name
? bid100 ? rating gt 5
Reserves
Sailors
15
QPT continued

• Representation of nesting of relational algebra
  operations as a directed-graph (dag).
• Source nodes are the relations
• root is the final output
• internal nodes are the relational algebra
  operations
• Relations flow on arcs between nodes

16
Relational Calculus

• The calculus is nonprocedural
• i.e. declarative
• Used by systems to optimize queries.
• Defines set membership requirements for the
  desired resulting set from query.
• Tuple Relational Calculus (TRC)
• t cond(t) where t is a tuple variable
• Domain Relational Calculus (DRC)

17
Operators

• Select
• t cond (t)
• Union
• t r(t) or s(t)
• Difference
• t r(t) and not s(t)
• project product - - Must use quantifiers ?, ?

18
Simple Example

• Q2 Find the names of sailors who have reserved a
  red boat.
• P ?S? Sailors ?R? Reserves (R.sid S.sid ?
  P.sname S.sname ? ?B? Boats ( B.bid R.bid ?
  B.color red))

19
Variants

• TRC - Tuple Relational Calculus
• DRC - Domain Relational Calculus
• A domain variable is a variable that ranges over
  the values in the domain of some attribute.
• Example Find all sailors with a rating above 7
• TRC S S? Sailors ? S.rating gt 7
• DRC (I,N,T,A) (I,N,T,A) ? Sailors ? T gt 7

20
Summary

• The relational model supports algebraic and
  calculus query languages for data manipulation.
• All relational algebra queries can be expressed
  in relational calculus, and vice versa.
• An important criterion for commercial query
  languages is that they should be relationally
  complete in the sense that they can express all
  relational algebra queries.

See 12 Rules
21
Chapter 7 Intro to Data Models

• What are the steps in designing a database?
• Why is the ER model used to create an initial
  design?
• What are the main concepts in the ER model?
• What are guidelines for using the ER model
  effectively?
• How does database design fit within the overall
  design framework for complex software within
  large enterprises?
• What is UML and how is it related to the ER model?

22
Database Design
RequirementsCollection andAnalysis
Database Requirements
Functional Requirements
Conceptual Schema
DBMS independent
DBMS dependent
Logical Model
Security
Internal Representation
Tuning
23
ER Model Building Blocks
Entity Attribute Entity Set (Type) Relationship
(Instance) Relationship Set (Type)
Real world instance/object Property of an entity.
An entity has a value for each of its
attributes Groups of entities of the same type
(same attributes) Association between specific
entities Set of associations between set of
entities

• Types are shown in the ER diagram, not instances

24
Example
25
Examples
SSN
name
Lot
Employees
Dname
DID
Since
budget
Works_in
Departments
Address
Capacity
Locations
26
Notations

• Figure 7.14

27
Design Example
I would like my customers to be able to browse
my catalog of books and place orders over the
Internet.
Create table customers Create table books
Create table orders
28
Summary

• ER Model
• High level conceptual data model
• Attributes
• Simple or atomic
• Composite
• Multivalued
• Stored vs derived
• Constraints
• Cardinality
• Participation