OCL3 Oracle 10g: SQL

1
OCL3 Oracle 10gSQL PL/SQLSession 3

• Matthew P. Johnson
• CISDD, CUNY
• June, 2005

2
Agenda

• Last time FDs
• This time
• Anomalies
• Normalization
• Then SQL

3
Review examples finding FDs

• Product(name, price, category, color)
• name, category ? price
• category ? color
• Keys are name, category
• Enrollment(student, address, course, room, time)
• student ? address
• room, time ? course
• student, course? room, time
• Keys are in class

4
Next topic Anomalies

• Identify anomalies in existing schema
• How to decompose a relation
• Boyce-Codd Normal Form (BCNF)
• Recovering information from a decomposition
• Third Normal Form

5
Types of anomalies

• Redundancy
• Repeat info unnecessarily in several tuples
• Update anomalies
• Change info in one tuple but not in another
• Deletion anomalies
• Delete some values lose other values too
• Insert anomalies
• Inserting row means having to insert other,
  separate info / null-ing it out

6
Example of anomalies
SSN ? Name, Mailing-address
SSN ? Phone

• Redundancy name, maddress
• Update anomaly Bill moves
• Delete anom. Bill doesnt pay bills, lose phones
  ? lose Bill!
• Insert anom cant insert someone without a
  (non-null) phone
• Underlying cause SSN-phone is many-many
• Effect partial dependency ssn ? name, maddress,
• Whereas key ssn,phone

7
Decomposition by projection

• Soln replace anomalous R with projections of R
  onto two subsets of attributes
• Projection an operation in Relational Algebra
• Corresponds to SELECT command in SQL
• Projecting R onto attributes (A1,,An) means
  removing all other attributes
• Result of projection is another relation
• Yields tuples whose fields are A1,,An
• Resulting duplicates ignored

8
Projection for decomposition
R(A1, ..., An, B1, ..., Bm, C1, ..., Cp)
R1 projection of R on A1, ..., An, B1, ..., Bm
R2 projection of R on A1, ..., An, C1, ..., Cp
A1, ..., An ? B1, ..., Bm ? C1, ..., Cp all
attributes, usually disjoint sets R1 and R2 may
(/not) be reassembled to produce original R
9
Decomposition example
Break the relation into two

• The anomalies are gone
• No more redundant data
• Easy to for Bill to move
• Okay for Bill to lose all phones

10
Thus high-level strategy
Conceptual Model
11
Using FDs to produce good schemas

• Start with set of relations
• Define FDs (and keys) for them based on real
  world
• Transform your relations to normal form
  (normalize them)
• Do this using decomposition
• Intuitively, good design means
• No anomalies
• Can reconstruct all (and only the) original
  information

12
Decomposition terminology

• Projection eliminating certain attributes from
  relation
• Decomposition separating a relation into two by
  projection
• Join (re)assembling two relations
• Whenever a row from R1 and a row from R2 have the
  same value for some atts A, join together to form
  a row of R3
• If exactly the original rows are reproduced by
  joining the relations, then the decomposition was
  lossless
• We join on the attributes R1 and R2 have in
  common (As)
• If it cant, the decomposition was lossy

13
Lossless Decompositions
Lossless Decompositions

• A decomposition is lossless if we can recover
• R(A,B,C)
• R1(B,C) R2(B,A)
• R(A,B,C) should be the same
  as R(A,B,C)

Decompose
Recover
R is in general larger than R. Must ensure R
R
14
Lossless decomposition

• Sometimes the data can be reproduced
• (Word, 100) (Word, WP) ? (Word, 100, WP)
• (Oracle, 1000) (Oracle, DB) ? (Oracle, 1000,
  DB)
• (Access, 100) (Access, DB) ? (Access, 100, DB)

15
Lossy decomposition

• Sometimes its not
• (Word, WP) (100, WP) ? (Word, 100, WP)
• (Oracle, DB) (1000, DB) ? (Oracle, 1000, DB)
• (Oracle, DB) (100, DB) ? (Oracle, 100, DB)
• (Access, DB) (1000, DB) ? (Access, 1000, DB)
• (Access, DB) (100, DB) ? (Access, 100, DB)

Whatswrong?
16
Ensuring lossless decomposition
R(A1, ..., An, B1, ..., Bm, C1, ..., Cp)
R1(A1, ..., An, B1, ..., Bm)
R2(A1, ..., An, C1, ..., Cp)
If A1, ..., An ? B1, ..., Bm or A1, ..., An ?
C1, ..., Cp Then the decomposition is lossless
Note dont need both

• Examples
• name ? price, so first decomposition was lossless
• category ? name and category ? price, and so
  second decomposition was lossy

17
Quick lossless/lossy example

• At a glance can we decompose into R1(Y,X),
  R2(Y,Z)?
• At a glance can we decompose into R1(X,Y),
  R2(X,Z)?

18
Normal Forms

• First Normal Form all attributes are atomic
• As opposed to set-valued
• Assumed all along
• Second Normal Form (2NF)
• Third Normal Form (3NF)
• Boyce Codd Normal Form (BCNF)
• Fourth Normal Form (4NF)
• Fifth Normal Form (5NF)

19
Most important BCNF
A simple condition for removing anomalies from
relations
A relation R is in BCNF if If As ? Bs is a
non-trivial dependency in R , then As is a
superkey for R
I.e. The left side must always contain a
key I.e If a set of attributes determines other
attributes, it must determine all the attributes

• Codd Ted Codd, IBM researcher, inventor of
  relational model, 1970
• Boyce Ray Boyce, IBM researcher, helped develop
  SQL in the 1970s

20
BCNF decomposition algorithm
Repeat choose A1, , Am ? B1, , Bn that
violates the BNCF condition //Heuristic
choose Bs as large as possible split R into
R1(A1, , Am, B1, , Bn) and R2(A1, , Am,
others) continue with both R1 and R2Until
no more violations
21
Boyce-Codd Normal Form

• Name/phone example is not BCNF
• ssn,phone is key
• FD ssn ? name,mailing-address holds
• Violates BCNF ssn is not a superkey
• Its decomposition is BCNF
• Only superkeys ? anything else

22
BCNF motivation

• Two big ideas
• Only a key field can determine other fields
• Key values are unique
• ? no FD-caused redundancy
• Slogan Every FD must contain the key, the whole
  key and nothing but the key.
• More accurate Every FD must contain (on the
  left) a key, a whole key, and maybe other fields.

23
BCNF Decomposition

• Larger example multiple decompositions
• Title, Year, Studio, President, Pres-Address
• FDs
• Title Year ? Studio
• Studio ? President
• President ? Pres-Address
• ? Studio ? President, Pres-Address (why?)
• No many-many this time
• Problem cause transitive FDs
• Title,year ? studio ? president

24
BCNF Decomposition

• Illegal As ? Bs, where As dont include key
• Decompose Studio ? President, Pres-Address
• As studio
• Bs president, pres-address
• Cs title, year
• Result
• Studios(studio, president, pres-address)
• Movies(studio, title, year)
• Is (2) in BCNF? Is in (1) BCNF?
• Key Studio
• FD President ? Pres-Address
• Q Does president ? studio? If so, president is a
  key
• But if not, it violates BCNF

25
BCNF Decomposition

• Studios(studio, president, pres-address)
• Illegal As ? Bs, where As dont include key
• ? Decompose President ? Pres-Address
• As president
• Bs pres-address
• Cs studio
• Studio, President, Pres-Address becomes
• President, Pres-Address
• Studio, President

26
Roadmap

• Want to remove redundancy/anomalies
• Convert to BCNF
• Find FDs closure alg
• Check if each FD A?B is ok
• If A contains a key
• If not, decompose into R1(A,B), R2(A,rest)
• Because A?B, this will be lossless
• Could check by joining R1 and R2
• Would get no rows not in original

27
Decomposition algorithm example

• R(N,O,R,P) F N ? O, O ? R, R ? N
• Key N,P
• Violations of BCNF N ? O, O?R, N ?OR
• which kinds of violations are these?
• Pick N ? OR (on board)
• Can we rejoin? (on board)
• What happens if we pick N ? O instead?
• Can we rejoin? (on board)

28
BCNF summary

• BCNF decomposition is lossless
• Can reproduce original by joining
• Saw last time Every 2-attribute relation is in
  BCNF
• Final set of decomposed relations might be
  different depending on
• Order of bad FDs chosen
• Saw last time But all results will be in BCNF

29
BCNF summary

• BCNF decomp. does not lose data
• Resulting relations can be rejoined to obtain the
  original
• In BCNF, theres no FD-based redundancy
• Values in key field are unique
• Other FDs are from key fields
• ? everything is as compressed as possible

30
BCNF Review

• Q Whats required for BCNF?
• Q Whats the slogan for BCNF?
• Q Who are B C?

31
BCNF Review

• Q How do we fix a non-BCNF relation?
• Q If As?Bs violates BCNF, what do we do?
• Q In this case, could the decomposition be
  lossy?
• Q Under what circumstances could a decomposition
  be lossy?
• Q How do we combine two relations?

32
Design Goals

• Goal for a relational database design is
• No redundancy
• Lossless Join
• Dependency Preservation
• If we cannot achieve this, we accept one of
• dependency loss
• use of more expensive inter-relational methods to
  preserve dependencies
• data redundancy due to use of 3NF
• Interesting SQL does not provide a direct way of
  specifying FDs other than superkeys
• can specify FDs using assertions, but they are
  expensive to test

33
Recap You are here

• First part of course is done conceptual
  foundations
• You now know
• E/R Model
• Relational Model
• Relational Algebra
• You now know how to
• Capture part of world as an E/R model
• Convert E/R models to relational models
• Convert relational models to good (normal) forms
• Next
• Create, update, query tables with R.A/SQL
• Write SQL/DB-connected applications

34
High-level agenda

• Install Oracle
• Start SQL
• Lab on Oracle system info/SQL

35
Next topic SQL

• Standard language for querying and manipulating
  data
• Structured Query Language
• Many standards ANSI SQL, SQL92/SQL2, SQL3/SQL99
• Vendors support various subsets/extensions
• Well do Oracles version
• Basic form (many more bells and whistles in
  addition)

SELECT attributes FROM relations (possibly
multiple, joined) WHERE conditions (selections)
36
Data Types in SQL

• Characters
• CHAR(20) -- fixed length
• VARCHAR(40) -- variable length
• Numbers
• BIGINT, INT, SMALLINT, TINYINT
• REAL, FLOAT -- differ in precision
• MONEY
• Times and dates
• DATE
• DATETIME -- SQL Server

37
Tables
Table name
Attribute names
Product
Tuples or rows
38
Simple SQL Query
Product
SELECT FROM ProductWHERE category'Gadgets'
selection
39
Simple SQL Query
Product
SELECT PName, Price, ManufacturerFROM
ProductWHERE Price gt 100
selection and projection
40
A Notation for SQL Queries
Input Schema
Product(PName, Price, Category, Manfacturer)
SELECT Name, Price, ManufacturerFROM
ProductWHERE Price gt 100
(PName, Price, Manfacturer)
Output Schema
41
SQL

• SQL SELECT
• Sometimes called a projection
• What goes in the WHERE clause
• x y, x lt y, x lt y, etc.
• For number, they have the usual meanings
• For CHAR and VARCHAR lexicographic ordering
• Expected conversion between CHAR and VARCHAR
• For dates and times, what you expect

42
SQL e.g.

• Movies(Title,Year,Length,inColor,Studio,Prdcr)
• Q How long was Star Wars (1977), in SQL?
• Q Which Fox movies are are at least 100 minutes
  long, in SQL?

43
SQL e.g.

• Reps(ssn, name, etc.)
• Clients(ssn, name, rssn)
• Q Who are Georges clients, in SQL?
• Conceptually
• PClients.name(sReps.nameGeorge and
  Reps.ssnrssn(Reps x Clients))

44
The LIKE operator

• s LIKE p pattern matching on strings
• p may contain two special symbols
• _ any single character
• zero or more chars
• Product(Name, Price, Category, Manufacturer)
• Find all products whose name contains gizmo

SELECT FROM ProductsWHERE Name LIKE
'gizmo'
45
The LIKE operator

• Q What it want to search for values containing a
  ?
• PName LIKE '' wont work
• Instead, must use escape chars
• In C/C/J, prepend \
• In SQL, prepend an arbitrary escape char
• PName LIKE 'x' ESCAPE 'x'

46
More on escape chars

• SQL no official default escape char
• In SQLPlus default escape char '\'
• Can set with
• SQLgt set escape x
• Other tools, DBMSs your mileage may vary
• SQL string literals put in ' '
• 'mystring'
• Single-quote literals escaped with single-quotes
• 'George''s string'

47

• Q What about double quotes?
• A Cant be used in place of single quotes
• But can be used when Oracle would otherwise
  misparse your command, e.g.
• Names with spaces
• create table bad table name (a int, b int)
• Reserved words as names
• create table badfieldname(from int, b int)

48

• Q Can an escape char be an escape string?
• A No.

SQLgt select from newtable where a like '\'
escape '\' A B ----------
---------- hi there SQLgt select from
newtable where a like '\' escape '\\' select
from newtable where a like '\' escape '\\'

ERROR at line 1 ORA-01425 escape character
must be character string of length 1
49
More on single-quotes

• Dates with DATE
• DATE '1948-05-14'
• Timestamps with TIMESTAMP
• TIMESTAMP '1948-05-14 120000'

50
Eliminating Duplicates
SELECT category FROM Product
Compare to
SELECT DISTINCT category FROM Product
51
Ordering the Results

• Ordering is ascending, unless you specify the
  DESC keyword per attribute.
• Ordering is ascending, unless you specify the
  DESC keyword per attribute.

SELECT pname, price, manufacturer FROM
Product WHERE category'gizmo' AND price gt
50 ORDER BY price, pname
SELECT pname, price, manufacturer FROM
Product WHERE category'gizmo' AND price gt
50 ORDER BY price DESC, pname ASC
52
Ordering the Results
SELECT Category FROM Product ORDER BY PName
?
53
Joins in SQL

• Connect two or more tables

Product
Company
What is the connection between them?
54
Joins in SQL
Product (pname, price, category,
manufacturer) Company (cname, stockPrice,
country) Find all products under 200
manufactured in Japanreturn their names and
prices.

SELECT PName, PriceFROM Product, CompanyWHERE
ManufacturerCName AND Country'Japan'
AND Price lt 200
55
Joins in SQL
Product
Company
SELECT PName, PriceFROM Product, CompanyWHERE
ManufacturerCName AND Country'Japan'
AND Price lt 200
56
Joins in SQL
Product (pname, price, category,
manufacturer) Company (cname, stockPrice,
country) Find all countries that manufacture
some product in the Gadgets category.
SELECT CountryFROM Product, CompanyWHERE
ManufacturerCName AND Category'Gadgets'
57
Joins in SQL
Product
Company
SELECT CountryFROM Product, CompanyWHERE
ManufacturerCName AND Category'Gadgets'
What is the problem? Whats thesolution?
58
Joins
Product (pname, price, category,
manufacturer) Purchase (buyer, seller, store,
product) Person(name, phone, city) Find names of
Seattleites who bought Gadgets, and the names of
the stores they bought such product from.

SELECT DISTINCT name, storeFROM Person,
Purchase, ProductWHERE persnamebuyer AND
product pname AND city'Seattle' AND
category'Gadgets'
59
Disambiguating Attributes

• Sometimes two relations have the same
  attrPerson(pname, address, worksfor)Company(cna
  me, address)

Whichaddress ?
SELECT DISTINCT pname, addressFROM Person,
CompanyWHERE worksfor cname
SELECT DISTINCT Person.pname, Company.addressFROM
Person, CompanyWHERE Person.worksfor
Company.cname
60
Tuple Variables
Product (pname, price, category,
manufacturer) Purchase (buyer, seller, store,
product) Person(persname, phoneNumber, city)
Find all stores that sold at least one product
that the storeBestBuy also sold
SELECT DISTINCT x.store FROM Purchase AS x,
Purchase AS y WHERE x.product y.product AND
y.store 'BestBuy'
Answer (store)
61
Tuple Variables

• Tuple variables introduced automatically
• Product (name, price, category, manufacturer)
• Becomes
• Doesnt work when Product occurs more than once
• In that case the user needs to define variables
  explicitly

SELECT name FROM Product WHERE price gt 100
SELECT Product.name FROM Product AS
Product WHERE Product.price gt 100
62
Details Disambiguation in SQL

• Every selected field must be unambiguous
• For R(A,B),
• Select A from R, R
• ? Select R1.A from R R1, R R2
• Consider
• Why?
• is shorthand for all fields, each must be
  unambiguous
• ? Select from R R1, R R2

SQLgt Select from R, R Select from R, R
ERROR at line 1 ORA-00918 column ambiguously
defined
63
Details Disambiguation in Oracle SQL

• Can rename fields by
• Select name as n
• Select name n
• But not by
• Select namen
• Can rename relations only by
• from tab t1, tab t2
• Lesson if you get errors, remove all s, ASs

64
SQL Query Semantics

• SELECT a1, a2, , ak
• FROM R1 AS x1, R2 AS x2, , Rn AS xn
• WHERE Conditions
• 1. Nested loops

Answer for x1 in R1 do for x2 in R2
do .. for xn in Rn
do if Conditions
then Answer Answer ?
(a1,,ak) return Answer
65
SQL Query Semantics

• SELECT a1, a2, , ak
• FROM R1 AS x1, R2 AS x2, , Rn AS xn
• WHERE Conditions
• 2. Parallel assignment
• Doesnt impose any order!

Answer for all assignments x1 in R1, , xn
in Rn do if Conditions then Answer
Answer ? (a1,,ak) return Answer
66

• Now lab 3 online