Transcript slides1 - Santa Clara University

COEN 178
Database Systems
•
•
•
•
JoAnne Holliday
Email: [email protected] (best way to reach me)
Office: Engineering 247, (408) 551-1941
Office Hours: Mon,Tues 3:30-4:45 and by
appointment
• Class web page: http://www.cse.scu.edu/~jholliday/
• Two midterms, a project and a final exam
• T.A. – James Dong
Santa Clara University
COEN 178 Holliday
1–1
Textbooks and Meetings
Required:
• A First Course in Database Systems, by Ullman
and Widom (second edition), Prentice Hall, 2002.
Recommended:
• Oracle 8 Programming, A Primer, (fourth
edition), by R. Sunderraman, Addison-Wesley
Longman, 2000.
Class Meetings:
• Lecture MWF 1:00-2:05pm, Engr 602
• Lab: W or F 2:15-5:00 608B
Santa Clara University
COEN 178 Holliday
1–2
Grading
• Lab work and instructor discretion: 10% of
grade.
• Project: 15% of grade.
• 2 Midterms for a total of 40% of grade.
• Last midterm: 35% of grade.
Santa Clara University
COEN 178 Holliday
1–3
Project
• You will build an application using a relational
database system (Oracle8i).
• The project has 2 parts, starting with design
and ending up with a complete application.
• The project should be written in Java and use
the JDBC or written in Perl.
• Java is the preferred language.
Santa Clara University
COEN 178 Holliday
1–4
Why take this class?
• This class may seem easy, but there is a
theoretical part which many students find
difficult – don’t be caught sleeping!!
• The benefits of accepting data
constraints.
• Of all courses you take at SCU, this may
be the one that gets you a job.
Santa Clara University
COEN 178 Holliday
1–5
Schedule
• Today: Sept. 19 (M)
Intro,
Entity-Relationship Model.
Read Chapter 1 and Sections 2.1-2.2.
• Sept 21 (W)
Weak
Entity Sets, Entity-Relationship Design.
Read Sections 2.3-2.4.
• Sept 23 (F)
Relational
Model, Functional Dependencies.
Read Sections 3.1-3.5.
Santa Clara University
COEN 178 Holliday
1–6
Syllabus
• Chapter 1. Overview of database management
systems. (1)
• Chapter 2. ERD’s, principles of modeling data and
constraints. (2)
• Chapter 3. The relational data model, functional
dependencies and normal forms. (4)
• Chapter 5 (5.1-5.2 only). Theoretical background
of the relational model. (1)
• Chapter 6. Queries and Updates. (3)
• Chapter 7. Constraints and Triggers (2)
• Chapter 8 (sec 6,7) Transactions and Security. (2)
• Indexing and the DBA (2)
Santa Clara University
COEN 178 Holliday
1–7
Definitions
• A database is a collection of (related) data.
• Database Management System (DBMS) is
the hardware/software package that
facilitates the creation and maintenance of a
computerized database.
• In a relational database, the tables are
relations, the rows are tuples and there are
mathematically well defined operations on
relations (relational algebra).
Santa Clara University
COEN 178 Holliday
1–8
Benefits of a DBMS
1. Manages very large amounts of data.
2. Supports efficient access to data through
indexing and a rich query language.
3. Supports concurrent access to data.
 Example:
bank and its ATM machines.
4. Supports secure, atomic access to very large
amounts of data.
 Contrast
two people editing the same UNIX file – last to write
“wins” – with the problem if two people deduct money from
the same account via ATM machines at the same time – new
balance is wrong whichever writes last.
Santa Clara University
COEN 178 Holliday
1–9
More Benefits of a DMBS
•
•
•
•
•
•
Controls redundancy in data storage
Protects against unauthorized access of data
Enforces integrity constraints
Provides backup and recovery services
Enforces user-defined standards
Provides data abstraction through schemas.
Santa Clara University
COEN 178 Holliday
1–10
Relational Model
• Based on tables, like the Account table:
acct #
12345
34567
…
name
Sally
Sue
…
balance
1000.21
285.48
…
• Today used in most DBMS's.
• DB schema Account=(acct#,name,balance)
and DB instance
Santa Clara University
COEN 178 Holliday
1–11
The DBMS Marketplace
• Relational DBMS companies – Oracle, Sybase – are among the
largest software companies in the world.
• IBM offers its relational DB2 system. With IMS, a nonrelational
system, IBM is by some accounts the largest DBMS vendor in the
world.
• Microsoft offers SQL-Server, plus Microsoft Access for the cheap
DBMS on the desktop, answered by “lite” systems from other
competitors.
• Relational companies also challenged by “object-oriented DB”
companies.
• But countered with “object-relational” systems, which retain the
relational core while allowing type extension as in OO systems.
Santa Clara University
COEN 178 Holliday
1–12
Three Aspects to Studying DBMS's
1. Modeling and design of databases.

Allows exploration of issues before committing to an
implementation.
2. Programming: queries and DB operations like
update.

SQL = “intergalactic dataspeak.”
3. DBMS implementation. (concurrency control2PL, backup and recovery- logging, query
optimization)
 COEN178 = (1) + (2)
Santa Clara University
COEN 178 Holliday
1–13
Forming Queries
Department
Schema is
Department = (Dept,Manager)
Dept
Manager
Data definition language (DDL) creates the schema and metadata
CREATE TABLE Account (…..);
Data Manipulation Language (DML)
Query (SELECT), UPDATE, DELETE
In the SQL query language:
SELECT Manager
FROM Department
WHERE Department.Dept = ‘accounting’
Santa Clara University
COEN 178 Holliday
1–14
Host Languages
C, C++, Java, Lisp
Application prog.
Calls to
DB
DBMS
Local Vars
(Memory)
(Storage)
Host language is completely general (Turing complete)
but gives you no support
Query language—less general "non procedural" and
optimizable
Santa Clara University
COEN 178 Holliday
1–15
Relational model is good for:
Large amounts of data —> simple operations
Navigate among small number of relations
Difficult Applications for relational model (complex objects and
relationships:
• VLSI Design (CAD in general)
• CASE
• Graphical Data
ALU
ADDER
A
FA
CPU
Adder
ALU
ADDER
Bill of Materials or
transitive closure
Santa Clara University
COEN 178 Holliday
1–16
Other Models
Where number of "relations" is large, relationships are complex
• Object Data Model
• Logic Data Model
OBJECT DATA MODEL
1.
2.
3.
4.
Complex Objects – Nested Structure (pointers or
references)
Encapsulation, set of Methods/Access functions
Object Identity
Inheritance – Defining new classes like old classes
Object model: usually find objects via explicit navigation
Also query language in some systems
Santa Clara University
COEN 178 Holliday
1–17
LOGIC (Horn Clause) DATA MODEL
• Prolog, Datalog
if A1 and A2 then B
prolog B:- A1 and A2
Functions s(5) = 6 (successor)
Predicates with Arguments sum(X,Y,Z)
X+Y=Z
sum(X,0,X) means X + 0 = X (always true for all X)
sum(X,s(Y),s(Z)):-sum(X,Y,Z)
means X+(Y+1) = (Z+1) if X + Y = Z
More power than relational model
Can Compute Transitive Closure
edge(X,Y)
path(X,Y) :- edge(X,Y)
path(X,Z) :- path(X,Y) & edge(Y,Z)
Santa Clara University
COEN 178 Holliday
1–18
Entity/Relationship Diagrams
A model where diagrams represent designs.
• Entity like object, = “thing.”
• Entity set like class = set of “similar”
entities/objects.
• Attribute = property of entities in an entity set,
similar to fields of a struct.
• In diagrams, entity set  rectangle;
attribute  oval.
ID
name
Students
Santa Clara University
COEN 178 Holliday
phone
major
1–19
Relationships
• Connect two or more entity sets.
• Represented by diamonds.
Students
Santa Clara University
Taking
COEN 178 Holliday
Courses
1–20
Relationship Set
Think of the “value” of a relationship set as a table.
• One column for each of the connected entity sets.
• One row for each of the entities, one from each
set, that are connected by the relationship.
Students
Sally
Sally
Joe
…
Santa Clara University
Courses
COEN178
COEN111
COEN178
…
COEN 178 Holliday
1–21
Multiway Relationships
Usually binary relationships (connecting two E.S.) suffice.
• However, there are some cases where three or more E.S.
must be connected by one relationship.
• Example: relationship among students, courses, TA's
(and graders).
Students
Possibly, this E/R diagram is OK:
Taking
Courses
Assisting
TA/Graders
Santa Clara University
COEN 178 Holliday
1–22
Multiway Relationships
• Works in COEN178, because each TA (or
grader) is a TA of all students. Connection
student-TA is only via the course.
• But what if students were divided into
sections, with different TAs?
Then,
a student in COEN178 would be related
to only one of the TA's. Which one?
• Need a 3-way relationship to tell.
Santa Clara University
COEN 178 Holliday
1–23
Courses
Students
Enrolls
TAs
Students
Ann
Sue
Bob
…
Santa Clara University
Courses
CS180
CS180
CS180
…
COEN 178 Holliday
TAs
Jan
Pat
Jan
…
1–24
Beers-Bars-Drinkers Example
• Example of complex relationships.
name
addr
license
Serves
Bars
Frequents
Beers
Likes
Drinkers
name
Santa Clara University
manf
name
COEN 178 Holliday
addr
1–25
Multiplicity of Relationships
Many-many
Many-one
One-one
Representation of Many-One
• Arrow pointing to “at most one.”
• Rounded arrow = “exactly one.”
Santa Clara University
COEN 178 Holliday
1–26
Example:
Drinkers Have Favorite Beers
name
Serves
addr
license
Bars
Frequents
Likes
Beers
name
Santa Clara University
manf
Drinkers
Favorite
COEN 178 Holliday
name
addr
1–27
Many-One Relationships
A bar has one best seller – many bars may
have the same one.
Bars
Bestseller
Beers
Is the rounded arrow justified?
Alternate notation: total participation
Bars
Santa Clara University
Bestseller
COEN 178 Holliday
Beers
1–28
Attributes on Relationships
price
Bars
Sells
Beers
• Shorthand for 3-way relationship:
price
Prices
Bars
Santa Clara University
Sells
COEN 178 Holliday
Beers
1–29
• A true 3-way relationship.
Price
depends jointly on beer and bar.
• Notice arrow convention for multiway
relationships: “all other E.S. determine one
of these.”
Not
sufficiently general to express any
possibility.
However, if price, say, depended only on the
beer, then we could use two 2-way
relationships: price-beer and beer-bar.
Or better: just make price an attribute of beer.
Santa Clara University
COEN 178 Holliday
1–30
Converting Multiway to 2-Way
• Necessary in certain “object-oriented” models.
• Create a new connecting E.S. to represent rows of a relationship
set.

E.g., (Joe's Bar, Bud, $2.50) for the Sells relationship.
• Many-one relationships from the connecting E.S. to the others.
BBP
TheBar
TheBeer
ThePrice
Bars
Beers
Price
Santa Clara University
COEN 178 Holliday
1–31
Roles
Sometimes an E.S. participates more than
once in a relationship.
• Label edges with roles to distinguish.
Married
husband
wife
Husband
d1
d3
…
Wife
d2
d4
…
Drinkers
Santa Clara University
COEN 178 Holliday
1–32
Buddies
1
2
Drinkers
Buddy1
d1
d1
d2
d2
…
Buddy2
d2
d3
d1
d4
…
• Notice Buddies is symmetric, Married not.
No
way to say “symmetric” in E/R.
Design Question
Should we replace husband and wife by one
relationship spouse to make it symmetric?
Santa Clara University
COEN 178 Holliday
1–33
More Design Issues
1. Subclasses.
2. Keys.
3. Weak entity sets.
Santa Clara University
COEN 178 Holliday
1–34
Subclasses
Subclass = special case = fewer entities =
more properties.
• Example: A bank stores records of bank
accounts. Savings accounts are a kind of
bank account. In addition to the properties
(= attributes and relationships) of bank
accounts, there is a “interest rate” attribute
for savings accounts.
Santa Clara University
COEN 178 Holliday
1–35
E/R Subclasses
• Assume subclasses form a tree (no multiple
inheritance).
• isa triangles indicate the subclass relation.
name
Accounts
Acc#
isa
rate
Savings a/c
Santa Clara University
Checking a/c
COEN 178 Holliday
1–36
Multiple Inheritance
Theoretically, an E.S. could be a subclass of
several other entity sets.
name
owner
Acc#
Bank account
Interest instrument
isa
isa
Savings
A/C
Santa Clara University
COEN 178 Holliday
Certificate
of deposit
1–37
Problems
How to show?
• A customer can have several loans (or no
loans) but each loan is associated with only
one (exactly one) customer?
• A customer can have at most one loan and a
loan may be ‘owned’ by several customers?
Customer
Santa Clara University
Borrows
COEN 178 Holliday
Loan
1–38
Keys
A key is a set of attributes whose values can
belong to at most one entity.
• In E/R model, every E.S. must have a key.
It
could have more than one key, but one set of
attributes is the “designated” key.
• In E/R diagrams, you should underline all
attributes of the designated key.
Santa Clara University
COEN 178 Holliday
1–39
Example
• Suppose acc# is key for Account.
acc#
Account
name
isa
rate
S/A
• acc# is also key for S/A.
 In
general, key at root is key for all.
Santa Clara University
COEN 178 Holliday
1–40
Example: A Multiattribute Key
number
dept
hours
Courses
room
• Possibly, the combination of hours + room
also forms a key, but we have not
designated it as such.
Santa Clara University
COEN 178 Holliday
1–41