One More Normal Form

1
One More Normal Form

• Consider the dependencies
• Product Company
• Company, State Product
• Is it in BCNF?

2
Multivalued Dependencies (and one last normal
form)
Name SSN Phone
Number Course
Fred 123-321-99 (206)
572-4312 CSE-444 Fred
123-321-99 [phone] CSE-341 Fred
123-321-99 [phone]
CSE-444 Fred 123-321-99
[phone] CSE-341
The multivalued dependencies are
Name, SSN Phone Number
Name, SSN
Course 4th Normal form replace FD by MVD.
3
Querying the Database

• How do we specify what we want from our database?
• Find all the employees who earn more than
  50,000 and pay taxes in New Jersey.
• We design high-level query languages
• SQL (used everywhere)
• Datalog (used by theoreticians and their
  students)
• Relational algebra a basic set of operations on
  relations that provide the basic principles.

4
Relational Algebra at a Glance

• Operators sets as input, new set as output
• Basic Set Operators
• union, intersection, difference, but no
  complement.
• Selection s
• Projection p
• Cartesian Product X
• Joins (natural,equi-join, theta join, semi-join)
• Renaming r

5
Set Operations

• Binary operations
• Result is table(set) with same attributes
• Watch our for naming of attributes in resulting
  relation.
• Union all tuples in R1 or R2
• Intersection all tuples in R1 and R2
• Difference all tuples in R1 and not in R2
• No complement. Why?
• Bags later.

6
Selection

• Produce a subset of the tuples in a relation
  which satisfy a given condition
• Unary operation returns set with same
  attributes, but selects rows
• Use and, or, not, gt, lt to build condition
• Find all employees with salary more than 40,000
  

7
Find all employees with salary more than 40,000.
8
Projection

• Unary operation, selects columns
• Eliminates duplicate tuples
• Example project social-security number and
  names.

9
(No Transcript)
10
Cartesian Product

• Binary Operation
• Result is tuples combining any element of R1 with
  any element of R2, for R1XR2
• Schema is union of Schema(R1) Schema(R2)

11
(No Transcript)
12
Join (Natural)

• Most important, expensive and exciting.
• Combines two relations, selecting only related
  tuples
• Equivalent to a cross product followed by
  selection
• Resulting schema has all attributes of the two
  relations, but one copy of join condition
  attributes

13
Complex Queries
Product ( name, price, category, maker) Purchase
(buyer, seller, store, product) Company (name,
stock price, country) Person( name, phone number,
city)
Find phone numbers of people who bought gizmos
from Fred. Find telephony products that
somebody bought
14
Exercises
Product ( name, price, category, maker) Purchase
(buyer, seller, store, product) Company (name,
stock price, country) Person( name, phone number,
city) Ex 1 Find people who bought telephony
products. Ex 2 Find names of people who bought
American products Ex 3 Find names of people who
bought American products and did not
buy French products Ex 4 Find names of people
who bought American products and they
live in Seattle. Ex 5 Find people who bought
stuff from Joe or bought products
from a company whose stock prices is more than
50.
15
(No Transcript)
16
Other Joins and Renaming

• Theta join the join involves a predicate
• R S
• Semi-join the attributes of one relation are
  included in the other.
• Renaming

17
Operations on Bags (and why we care)
Basic operations Projection Selection
Union Intersection Set difference
Cartesian product Join (natural join, theta
join)