Transcript Slides in Powerpoint.

Computer Science 180:
Database Systems
•
•
•
•
•
•
•
•
Arthur M. Keller
Email: [email protected] (best way to reach me)
Office: Baskin Engineering 153a, (831) 459-1485
Office Hours: Tuesdays 2:30–3:30pm and by
appointment
Class web page:
http://www.cse.ucsc.edu/classes/cmps180
My web page: http://www.cse.ucsc.edu/~ark
Assignments due most Tuesdays; Project Parts due most
Thursdays
T.A. – T.J. Steed
Fall 2001
Arthur Keller – CS 180
1–1
Textbooks
Required:
• Database Systems: The Complete Book, by Garcia-Molina,
Ullman, and Widom (first edition), Prentice Hall, 2002.

This book will be available in the bookstore in early October. First
chapters handed out today.
Recommended: (one of these)
• A Guide to the SQL Standard: A User's Guide to the
Standard Database Language SQL, (fourth edition), by C.J.
Date and Hugh Darwen, Addison-Wesley, 2000.
• SQL: 1999 - Understanding Relational Language
Components, (first edition), by Melton and Simon, Morgan
Kaufmann, 2002.
Available in Library:
• Books in PostgreSQL
Fall 2001
Arthur Keller – CS 180
1–2
Grading
Introduction Letter and Personal Resume 2%
Assignments
16%
Project
32%
Midterm Exam
15%
Final Exam
35%
• Midterm: Nov. 1 (TH) in class
• Final: Dec. 3 (M) 8–11AM in class
• Introduction Letter and Personal Resume

Fall 2001
This assignment includes your name and contact information, your
background, relevant courses, and relevant experience. Please tell why
you are taking this course and what you expect to get out of it. Tell what
makes you unique and interesting. Also include a statement
acknowledging that you have read and understand the policies regarding
academic dishonesty for this class. This assignment is due Oct. 2 (T).
Arthur Keller – CS 180
1–3
Schedule
• Today Sep 20 (TH)
Intro,
Entity-Relationship Model.
Read Chapter 1 and Sections 2.1-2.2.
• Next Class: Sep. 25 (T)
More
Entity-Relationship Model.
 Read Sections 2.3-2.4.
• Note: Sep. 27 (TH) Class cancelled.
Fall 2001
Arthur Keller – CS 180
1–4
Syllabus
• The background and history of database
management systems.
• The fundamentals of using a database
management systems.
• Industry standards used for database
management systems.
• Theoretical background of the relational
model.
• Queries and Updates.
• Transactions and Security.
• Object-oriented, object-relational, semistructured and XML database systems.
Fall 2001
Arthur Keller – CS 180
1–5
What is a Database Management System?
1. Manages very large amounts of data.
2. Supports efficient access to very large amounts
of data.
3. Supports concurrent access to v.l.a.d.
 Example:
bank and its ATM machines.
4. Supports secure, atomic access to v.l.a.d.
 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.
Fall 2001
Arthur Keller – CS 180
1–6
Relational Model
• Based on tables, as:
acct#
na me
ba lance
12 345
34 567
…
Sal ly
Sue
…
10 00.2 1
28 5.48
…
• Today used in most DBMS's.
Fall 2001
Arthur Keller – CS 180
1–7
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.
Fall 2001
Arthur Keller – CS 180
1–8
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.
CS180 = (1) + (2), while (3) is covered in CS277.
Fall 2001
Arthur Keller – CS 180
1–9
Entity/Relationship Model
Diagrams to 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
phone
Students
Fall 2001
Arthur Keller – CS 180
height
1–10
Relationships
• Connect two or more entity sets.
• Represented by diamonds.
Students
Fall 2001
Taking
Arthur Keller – CS 180
Courses
1–11
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 list of entities, one from each
set, that are connected by the relationship.
Fall 2001
Stu dents
Cours es
Sal ly
Sal ly
Joe
…
CS180
CS111
CS180
…
Arthur Keller – CS 180
1–12
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.
Possibly, this E/R
Students
Taking
Course s
diagram is OK:
Assisting
TAs
Fall 2001
Arthur Keller – CS 180
1–13
• Works in CS180, because each TA is a TA
of all students. Connection student-TA is
only via the course.
• But what if students were divided into
sections, each headed by a TA?
Then,
a student in CS180 would be related to
only one of the TA's for CS180. Which one?
• Need a 3-way relationship to tell.
Fall 2001
Arthur Keller – CS 180
1–14
Students
Courses
Enrolls
TAs
Fall 2001
Stu dents
Cours es
TAs
An n
Sue
Bob
…
CS180
CS180
CS180
…
Jan
Pat
Jan
…
Arthur Keller – CS 180
1–15
Beers-Bars-Drinkers Example
• Our running example for the course.
na me
addr
license
Bars
Serve s
Beers
na me
Fall 2001
ma nf
Frequents
Like s
Drinkers
na me
Arthur Keller – CS 180
addr
1–16
Multiplicity of Relationships
Many-many
Many-one
One-one
Representation of Many-One
• E/R: arrow pointing to “one.”
Rounded
Fall 2001
arrow = “exactly one.”
Arthur Keller – CS 180
1–17
Example:
Drinkers Have Favorite Beers
name
addr
lic ense
Ba rs
Serves
Be ers
name
Fall 2001
manf
Freque nts
Likes
Favorite
Arthur Keller – CS 180
Drinkers
name
addr
1–18
One-One Relationships
Put arrows in both directions.
Manfs
Bestseller
Beers
Design Issue:
Is the rounded arrow justified?
Design Issue:
Here, manufacturer is an E.S.; in earlier
diagrams it is an attribute. Which is right?
Fall 2001
Arthur Keller – CS 180
1–19
Attributes on Relationships
price
Bars
Sells
Beers
• Shorthand for 3-way relationship:
price
Prices
Bars
Fall 2001
Sells
Arthur Keller – CS 180
Beers
1–20
• 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.
Fall 2001
Arthur Keller – CS 180
1–21
Converting Multiway to 2-Way
• Baroque in E/R, but necessary in certain “objectoriented” 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
Bars
Fall 2001
TheBeer
Beers
Arthur Keller – CS 180
ThePrice
Prices
1–22
Roles
Sometimes an E.S. participates more than
once in a relationship.
• Label edges with roles to distinguish.
Fall 2001
Husb and
Wi fe
d1
d3
d2
d4
…
…
Arthur Keller – CS 180
1–23
Buddies
1
2
Drinkers
Bud dy1
Bud dy2
d1
d1
d2
d3
d2
d3
d1
d1
…
…
• 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?
Fall 2001
Arthur Keller – CS 180
1–24