OCL3 Oracle 10g: SQL

1
OCL3 Oracle 10gSQL PL/SQLSession 1

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

2
Personnel

• Instructor Matthew P. Johnson
• mpjohnson-at-gmail.com
• TA Ratna Priya Moganti
• rmoganti7-at-yahoo.com
• Admin Dawn Kleinberger
• dkleinberger-at-gc.cuny.edu

3
Communications

• Web page
• http//pages.stern.nyu.edu/mjohnson/oracle/
• syllabus
• course policies
• reading assignments
• etc.

4
Acknowledgements

• Thanks to Ramesh at NYU, Ullman, et al., Raghu
  and Johannes, Dan Suciu, Arthur Keller, David
  Kuijt for course materials
• See classpage for other related, antecedent DBMS
  courses

5
What is a Database?

• A very large, integrated collection of data.
• Models real-world enterprise.
• Entities
• students, courses, instructors, TAs
• Relationships
• George is currently taking OCL
• Dick is currently teaching OCL
• Condi is currently TA-ing OCL but took it last
  semester
• Database Management System (DBMS) large software
  package designed to store and manage databases

6
Databases are everywhere ordering a pizza

• Databases involved?
• Pizza Huts DB
• stores previous orders by customer
• stores previous credit cards used
• Credit card records
• huge databases of (attempted) purchases
• location, date, amount, parties
• Got approved by credit-report companies
• phone companys records
• Local Usage Details (Pull his LUDs, Lenny.)
• Caller ID
• ensures reported address matches destination

7
Your wallet is full of DB records

• Drivers license
• Credit cards
• Medical insurance card
• Social security card
• Gym membership
• Individual checks
• Dollar bills (w/serial numbers)
• Maybe even photos (ids on back)

8
Databases are everywhere

• Q Websites backed by DBMSs?
• retail Amazon, etc.
• data-mining Page You Made
• search engines Google, etc.
• directories Internic, etc.
• searchable DBs IMDB, tvguide.com, etc.
• Q Non-web examples of DBMSs?
• airline bookings
• criminal/terrorist TIA
• NYPDs CompStat
• all serious crime stats by precinct
• Retailers Wal-Mart, etc.
• when to re-order, purchase patterns, data-mining
• Genomics!

9
Example of a Traditional DB App

• Suppose we are building a system to store the
• information about
• checking accounts
• savings accounts
• account holders
• state of each of each persons accounts

10
Can we do it without a DBMS?

• Sure we can! Start by storing the data in files
• checking.txt savings.txt
  customers.txt
• Now write C or Java programs to implement
  specific tasks

11
Doing it without a DBMS...

• Transfer 100 from Georges savings to checking

Write a C program to do the following
Read savings.txt Findupdate the record
George balance - 100 Write savings.txt Read
checking.txt Findupdate the record
George balance 100 Write checking.txt
12
Problems without an DBMS...

• 1. System crashes
• Q What is the problem ?
• A George lost his 100
• Same problem even if reordered
• 2. Simultaneous access by many users
• George and Dick visit ATMs at same
• Lock checking.txt before each usewhat is the
  problem?

Read savings.txt Findupdate the rec
George. Write savings.txt Read
checking.txt Findupdate the rec George Write
checking.txt
CRASH !
13
Problems without an DBMS...

• 3. Large data sets (say 100s of GB or TBs)
• Why is this a problem?
• No indices
• Finding George in huge flatfile is expensive
• Modifications intractable without better data
  structures
• George ? Georgie is very expensive
• Deletions are very expensive

14
Problems without a DBMS...

• 5. Security?
• File system may be insecure
• File system security may be coarse
• 6. Application programming interface (API)?
• suppose need other apps to access DB
• 7. How to interact with other DBMSs?

15
General problems to solve

• In building our own system, many Qs arise
• how do we store the data? (file organization,
  etc.)
• how do we query the data? (write programs)
• make sure that updates dont mess things up?
• leave the DB consistent
• provide different views on the data?
• e.g., ATM users view v. bank tellers view
• how do we deal with crashes?
• Too hard! Go buy Oracle!
• Q How does a DBMS solve these problems?
• A Long story see other courses/books

16
Big issue Transaction processing

• Grouping of several queries (or other database
  actions) into one transaction
• ACID properties
• Atomicity
• all or nothing
• Consistency
• constraints on relationships
• Isolation
• concurrency control
• Simulated solipsism
• Durability
• Crash recovery

17
Atomicity Durability

• Saw how George lost 100 with makeshift software
• A DBMS prevents this outcome
• xacts are all or nothing
• One idea Keep a log (history) of all actions in
  set of xacts
• Durability Use log to redo or undo certain ops
  in crash recovery
• Atomicity dont really commit changes until end
• Then, all at once

18
Isolation

• Concurrent execution is essential for
  performance.
• Frequent, slow disk accesses
• ? dont waste CPU keep running
• Interleaving actions of different user programs
• ?can lead to inconsistency
• e.g., two programs simultaneously withdraw from
  the same account
• DBMS ensures such problems dont arise
• users can pretend they are using a single-user
  system

19
Isolation

• Contrast with a file in two Notepads
• Strategy ignore multiple users
• whichever saves last wins
• first save is overwritten
• Contrast with a file in two Words
• Strategy blunt isolation
• One can edit
• To the other its read-only

20
Consistency

• Each xant (on a consistent DB) must leave it in a
  consistent state
• can define integrity constraints
• checks the defined claims about the data remain
  true

21
Data Models

• Every DBMS uses some data model collection of
  concepts for describing data
• Schema description of partic set of data, using
  some data model
• Relational data model most widely used (by far)
  data model
• Oracle, DB2, SQLServer, other SQL DBMSs
• main concept relation table of rows columns
• a rels schema defines its fields

22
Example university database

• Conceptual schema
• Students(ssn string, name string, login
  string, age int, gpa real)
• Courses(cid string, cname string, credits int)
• Enrolled(sidstring, cidstring, grade string)
• Physical schema
• Relations stored as unordered text files.
• Indices on first column of each rel
• External Schema (View)
• Course_info(ssn string, name string)
• My_courses(cname string, grade string)

23
How the programmer sees the DBMS

• Start with DDL to create tables
• Continue with DML to populate tables

CREATE TABLE Students ( Name CHAR(30), SSN
CHAR(9) PRIMARY KEY NOT NULL, Category
CHAR(20) )
INSERT INTO Students VALUES('Howard',
'123456789', 'undergraduate')
24
How the programmer sees the DBMS

• Tables
• Still implemented as files, but behind the scenes
  can be quite complex

Takes
Students
Courses
data independence separate logical view from
physical implementation
25
Querying Structured Query Language

• Find all the students who have taken OCL2
• Find all the students who OCL2 last fall
• Find the students names
• Query processor does this efficiently

SELECT SSN FROM Takes WHERE CID'OCL2'
SELECT SSN FROM Takes WHERE CID'OCL2' AND
Semester'Fall, 2003'
SELECT Name FROM Students, Takes WHERE
Students.SSNTakes.SSN AND CID'OCL2' AND
Semester'Fall, 2003'
26
Database Industry

• Relational databases are a great success of
  theoretical ideas
• based on most theoretical type of math there
  is set theory
• DBMS companies are among the largest software
  companies in the world
• Oracle, IBM (with DB2), Microsoft (SQL Server,
  Microsoft Access), Sybase
• Also opensource MySQL, PostgreSQL, etc.
• 20B industry
• XML (semi-structured data) also important
• New lingua franca for exchanging data

27
Databases are used by

• DB app programmers
• desktop app programmers
• web developers
• Database administrators (DBAs)
• design schemas
• security/authorization
• crash recovery
• tuning
• better paid than programmers!
• Everyone else (perhaps indirectly)
• You may not be interested in databases, but
  databases are interested in you. - Trotsky

28
The Study of DBMS

• Several aspects
• Modeling and design of databases
• DBMS programming querying and update
• DBMS implementation
• This course covers the first two
• Also will look at some more advanced areas
• XML, data-warehousing, regexps

29
Course outline

• Two biggest topics
• SQL
• PL/SQL
• But also
• Database design
• Entity/Relationship models
• Modeling constraints
• The relational model
• Relational algebra
• Transforming E/R models to relational schemas

30
Outline (Continued)

• SQL views and triggers
• Connecting to a Oracle from programming languages
• Web apps
• Data warehousing
• XML
• May change as course progresses
• partly in response to audience

31
Textbook

• Oracle Database 10g PL/SQL 101
• by Christopher Allen
• Hardcover 416 pages
• Publisher McGraw-Hill/
• Osborne Media
• ISBN 0072255404
• 1st edition (August 10, 2004)
• Distributed in class

32
SQL Readings

• Optional reference Oracle PL/SQL Programming
• Online (free) SQL tutorials include
• A Gentle Introduction to SQL (http//sqlzoo.net/)
• SQL for Web Nerds (http//philip.greenspun.com/sql
  /)

33
On-going Feedback

• Dont wait until the class is over to give
  feedback on improving it
• too late for you then!
• Send mail if you have questions or concerns
• Were in touch, so you be in touch.

34
So what is this course about, really?

• A bit of everything!
• Languages SQL, XPath, XQuery
• Data modeling
• Some theory!
• Functional dependencies, normal forms
• e.g., how to find most efficient schema for data
• Writing lots of SQL queries
• Lots of coding in PL/SQL
• Business DBMS examples/cases
• Most importantly how to meet real-world needs

35
For right now additional written survey

• Email to mpjohnson-at-gmail.com
• name
• email
• previous cs/is/math/logic courses/background
• previous programming experience
• Perl?
• PHP?
• HTML?
• Job programmer, DBA, etc.
• why taking class

36
Agenda

• Last time intro, RDBMS, ACID test
• This time E/R model
• Identify entity sets, relations and attributes
• One-one, one-many, many-many relations
• Simple ER diagrams to model a situation
• 3-way relationships Converting to binary
• Entities with multiple roles
• Subclasses
• Design issues
• Principles of faithfulness simplicity in ER
  diagrams
• Redundancy
• Whether an element should be an attribute or
  entity set
• Replacing a relationships with entity sets

37
DB development path

• the
• World

38
Entity/Relationship (E/R) Model

• A popular data model useful to database
  designers
• Graphical representation of miniworld
• Helps design the database, not implement it
• E/R design is translated to a relational design
• relational design then implemented in an RDBMS
• Elements of model
• Entities
• Entity Sets
• Attributes
• Relationships (! relations!)

39
Elements of E/R Model Entity Sets

• Entity like an object
• e.g. President Bush
• Particular instance of a concept
• Entity set set of one sort of entities or a
  concept
• e.g. World leaders
• Generally, same set of attributes
• Represented by a rectangle
• A good entity set you decide
• Common properties
• Correspond to class of phys. or bus. objects
• (People, products, accounts, grades, etc.)

World Leader
40
Elements of E/R Model Attributes

• Properties of entities in entity set
• Like fields in a struct
• Like columns in a table/spreadsheet
• Like data members in an object
• Values in some domain (e.g., ints, strings)
• Represented by ovals
• Assumed atomic
• But could have limited structure
• Ints, strings, etc.

ID
Name
Student
41
Elements of E/R Model Relationships

• Connect two or more entity sets
• e.g. students enroll in courses
• Binary relationships connect two entity sets
  most common
• Multiway relationships connect several entity
  sets
• Represented by diamonds

Students
Enroll
Courses
42
Elms of E/R Model Relships (contd)

• Students Enroll in courses
• Courses are Held in rooms
• The E/R data model

Students
Enroll
Courses
ID
Name
Held
Rooms
43
A little set theory

• A mathematical set a collection of members
• A set is defined by its members
• Are you in or are you out?
• No other structure, no order, no duplicates
  allowed
• Sets can be specified by listing
• 1, 2, 3, N
• 1, 2, George Bush (few applications, but valid)
• Or by set-builder notation
• x in N 2 divides x ?
• x in Presidents reelected(x) ?
• 2x x in N ?

44
A little set theory

• One set can be a subset of another (which is then
  a superset of it)
• ReelectedPresidents is a subset of Presidents
• Also, RP is a proper subset of Pres some lost
  reelection
• Given two sets X and Y, the cross product or
  Cartesian product is
• X x Y (x,y) x in X, y in Y
• the set of all ordered pairs in which the
  first comes from X and the second comes from Y
• Important (x,y) ! x,y
• In an order pair or tuple
• Order matters
• Duplicates are allowed

45
A little set theory

• Mathematically, a relation(ship) between X and Y
  is just a subset of X x Y
• all those pairs (x,y) s.t. x is related to y
• Example owner-of O on People, Cats
• O(MPJ, Gödel) holds
• The equals relation E on N, N
• E(3,3) holds because 3 3
• E(3,4) does not hold
• E is still a set E (1,1), (2,2), (3,3),
• Father of relation F on People, People
• F(GHWB, GWB) holds
• F(GWB, GHWB) does not hold
• ? Relations arent necessarily symmetric

46
Multiplicity of Relation(ship)s

• Representation of relationships
• No arrow many-to-many
• Sharp arrow many-to-one
• Rounded arrow exactly one
• key constraint
• One-one

47
Multiplicity of Relation(ship)s
Many-to-many

• Many to one a student lives in lt 1 residence
  hall

Many to exactly one a student must live in a
residence hall
48
Multiplicity, set-theoretically

• Assume no vars below are equal
• Many-one means
• if (x1,y1) in R then (x1,y2) cannot be in R
• One-many means
• if (x1, y1) in R then (x2,y1) cannot be in R
• One-one means
• if (x1,y1) in R, then neither (x2,y1) nor (x1,y2)
  can be in R
• Notice one-one is stronger than many-one
• One-one implies both many-one and one-many

49
E/R Diagram e.g.
Name
Name
Enrolls
Students
Courses
ID
ID
Assisting
TA
Name
ID
50
E/R Diagrams

• Works if each TA is a TA of all students
• Student and TA connected only through Course
• But what if students were divided among multiple
  TAs?
• Then a student in OCL3 would be related to only
  one of the TA's for OCL3which one?
• Schema doesnt store enough info
• 3-way relationship is helpful here

51
Multiway Relationships
NB Enrolls determines TA (student, course) ? at
most one TA
52
Converting multiway relships to binary

• Some models limit relationships to binary
• Multiway relationship equivalent collection of
  binary, many to one relationships
• Replace relationship with connecting entity set

NB Enrolls has no attributes!
53
Second multiway e.g. renting movies

• Scenario a Customer Rents a Movie from a
  VideoStore on a certain date
• Q Which entity does date belong to?
• A To the fact of the renting
• Relationships can have attributes
• always (implicitly) many-one

54
Second multiway e.g. renting movies

• But they dont have to
• Relationship attributes can be replaced with
  (trivial) new entities

date
Date
VideoStore
Rental
Movie
Customer
55
Second multiway e.g. renting movies

• Where can we draw arrows?
• (store, video, customer) ? date ?
• (store, video, date) ? customer ?
• (store, date, customer) ? video ?
• (video, date, customer) ? store ?

56
Q (Why) does it matter?

• Round arrow benefit
• Obvious One item takes less space than many
• Less obvious easier to access one item x than
  set of one item x
• In programming an int v. a linked list with just
  one int
• Regular arrow benefit
• Mapping to a set of either one elm or none seems
  bad
• But not implemented this way
• Always one element, but that value may be NULL
• Lesson it pays to identify your relships
  multiplicity

57
Second multiway e.g. renting movies

• Convert to binary?

58
Roles in relationships

• Entity set appears more than once in a relship
• Generally distinct entities
• Each appearance is in a different role
• Edges labeled by roles

Successor
Pre-req
Course
Prereq
59
Subclasses in the E/R model

• Some entities are special cases of others
• Conversely some are generalizations
• Humans are specialized mammals
• Grad students are specialized students
• And, in turn, specialized mammals
• Subclass A isa B
• Represented by a triangle
• Always one-to-one, though arrows omitted
• Root is more general
• Multiple inheritance is allowed!
• A single entity may consist of all components
  (sets of fields) in arbitrary ESs and their
  ancestors

60
Subclasses

• Lion King atts of Movies relship Voices

title
length
year
Movies
stars
isa
isa
Weapon
Voices
Murder-Mysteries
Cartoons

• Roger Rabbit atts of Movies relship Voices
  att weapon

Component
61
E/R inheritance v. OO inheritance

• In a OOP class hierarchy, children also inherit
  attributes from parents
• But an object is an instance of one class
• In E/R, an entity may be composed of components
  from multiple, not-directly-related ESs
• Roger Rabbit is composed of components from
  Cartoons, Murder Mysteries, and Movies
• We could create a Cartoon Murder Mysteries ES if
  there were any atts specific to them
• So the real difference In E/R, can have implicit
  multiple inheritance between any set of
  IS-A-connected nodes (sharing a root)

62
Next

• Lab 1 online