d - Academic Program Pages at Evergreen

obtainablerabbiΔιαχείριση Δεδομένων

31 Ιαν 2013 (πριν από 4 χρόνια και 4 μήνες)

125 εμφανίσεις

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

1

Database Systems


Lecture/Workshop:
Mon 1
-
3 LIB 1316; Wed 11


12, LH2



Lab: Thu 10
-
12, ACC, additional help: Mon or Tue?


Judy Cushing
-

judyc@evergreen.edu


Office Hours: Thursdays 1
-
2pm, Lab I, 1003, 867
-
6652


Class web page: off the SOS web page, or
academic.evergreen.edu/r/ricdan16/SOSdatabase/databaseHome.html


Assignments due most Wednesdays


Project Parts due Mondays or Thursdays


Lab Aids: Dan Rice, Jeff Jones

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

2

Textbooks

Required:


Database Systems: The Complete Book
, by Garcia
-
Molina, Ullman, and
Widom (first edition), Prentice Hall, 2002.

Recommended:

(one of these)


SQL in a Nutshell :
A Desktop Quick Reference, Kevin E. Kline, Daniel
Kline, O’Reilly
.


A Guide to the SQL Standard.
C. J. Date and H. Darwen, Addison
-
Wesley, 1999. It is more succinct but perhaps a more useful summary,
than the Melton
-
Simon book.


SQL3 Complete,
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.

If you are using PostgreSQL:
Books on Unix, Perl, PHP, and CGI,
PostgreSQL: Introduction and Concepts
, Bruce Momjian, Addison
-
Wesley, 2001. This might also be on the web.

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

3

Evaluations

I will evaluate you on your ability to design and
implement a database application, and on your
understanding of database concepts. I will
consider:



Assignments and in class participation: about 30%.



Project: about 35%.



Exams: about 35%.

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

4

Project


Build an application using a relational database
system (SQL Server or PostgreSQL) accessed via
Java and the web.


The project has 8 parts (due Mondays or Thursdays),
starting with design and ending with a complete
application.


The early programming assignments should be
written in SQL, Java.


Some students found it helpful to switch to
JavaScript, HTML (PHP or Perl if Postgres) for the
web
-
accessible part.

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

5

Project
-

tentative schedule

1.
Project Part 1 due Jan. 14 (Mon) Choose a project and
design an E/R diagram.

2.
Project Part 2 due Jan. 24 (TH) Relational design.

3.
Project Part 3 due Jan. 31 (TH) Create database in
SQLServer.

4.
Project Part 4 due Feb. 7 (TH) Queries, updates, and
indexes.

5.
Project Part 5 due Feb. 21 (TH) Embedded SQL.

6.
Project Part 6 due Feb. 28 (TH) Views, constraints, and
triggers.

7.
Project Part 7 due Mar. 6 (Wed) Entire database
application, in Java.

8.
Project Part 8 due Mar. 11 (Mon) Database application,
accessed via the web.

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

6

Typical Warning

(for those taking this class elsewhere)



The database class is a lot of work.


But it is worth it.


Of all courses you take, it may be the
one that gets you a job….


We know that SOS gets people jobs, but
database expertise does help…. Data
is key to many applications….

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

7

Schedule


Today: Jan. 7


Intro, Entity
-
Relationship Model.


Read Chapter 1 and Sections 2.1, 2.3.


Jan. 9 (W)


Weak Entity Sets, Entity
-
Relationship Design.


Read Sections 2.2, 2.4.


Jan. 14 (M)


Relational Model, Functional Dependencies.


Read Sections 3.1
-
3.5. Project 1 due.


Jan. 16 (W)


Normal Forms, Multivalued Dependencies.


Read Sections 3.6
-
3.7. Assignment 1 due.

Date

Topic

Reading

Week 1

Intro, E/R model

Weak enitity sets, E/R design

1, 2.1, 2.3

2.2, 2.4

Week 2

Relational model, Functional Dependencies

Normal forms, Multivalued dependencies

3.1
-
3.5

3.6
-
3.7


Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

8

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.


Logic databases? Constraints and Triggers.


Transactions and Security.


Object
-
oriented, object
-
relational, semi
-
structured
and XML database systems.


Mediation and warehousing.

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

9

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 very large
amounts of 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.

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

10

Relational Model


Based on tables, as:




acct #


name


balance




12345


Sally


1000.21




34567


Sue



285.48














Today used in
most

DBMS's.

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

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, and XML data stores.


But countered with “object
-
relational” systems, which retain the
relational core while allowing type extension as in OO systems.

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

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.


Consider DBMS system components, p. 11.

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

13

Query Languages

Employee

Name

Dept

Department

Dept

Manager

SQL


SELECT Manager

FROM Employee, Department

WHERE Employee.name = "Clark Kent”


AND Employee.Dept = Department.Dept


Query Language


Data definition language (DDL) ~ like type defs in C or Pascal



Data Manipulation Language (DML)




Query (SELECT)



UPDATE < relation name >



SET <attribute> = < new
-
value>



WHERE <condition>

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

14


Host Languages

C, C++, Fortran, Lisp, COBOL

Application prog.

Local Vars

DBMS

Calls to

DB

Host language is completely general (Turing complete)


but gives you no support

Query language

less general "non procedural" and


optimizable

(Memory)

(Storage)

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

15

Relational model is good for:

Large amounts of data

> simple operations

Navigate among small number of relations


Difficult Applications for relational model:


VLSI Design (CAD in general)


CASE



Graphical Data

CPU

ALU

ADDER

Adder

A

FA

ALU

ADDER

Bill of Materials or
transitive closure

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

16

Where number of "relations" is large, relationships are complex


Object Data Model


Logic Data Model


OBJECT DATA MODEL


1.

Complex Objects


Nested Structure (pointers or

references)

2.

Encapsulation, set of Methods/Access functions

3.

Object Identity

4.

Inheritance


Defining new classes like old classes

Object model: usually find objects via explicit navigation

Also query language in some systems

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

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

Can Compute Transitive Closure

edge(X,Y)

path(X,Y) :
-

edge(X,Y)

path(X,Z) :
-

path(X,Y) & edge(Y,Z)


Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

18

Hierarchical

60’s


70's


80's


90’s




now

Relational

Choice for most new
applications

Object Bases

Knowledge Bases

Network

Multi
-
tiered (web) Architectures

XML Data Transfers and Transformations

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

19

Students

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

height

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

20

Relationships


Connect two or more entity sets.


Represented by diamonds.

Students

Courses

Taking

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

21

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.




Students

Courses




Sally


CS180





Sally


CS111





Joe


CS180










Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

22

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, Lab
Aids.



Possibly, this E/R diagram is OK:

Student

Course

Taking

Assisting

Lab Aid

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

23


Works in CS180, because each Lab Aid
works for all students. Connection student
-
LabAid is
only

via the course.


But what if students were divided into
sections, each headed by a LabAid?


Then, a student in CS180 would be related to
only one of the LabAids for CS180. Which
one?


Need a 3
-
way relationship to tell.

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

24

Student

Course

LabAid

Ann


CS180

Jan

Sue


CS180

Pat

Bob


CS180

Jan









Student

Course

LabAid

Enrolls

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

25

Beers
-
Bars
-
Drinkers Example


Our running example for the course.

name

addr

license

name

manf

name

addr

Beer

Drinker

Bar

Serves

Frequents

Likes

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

26

Multiplicity of Relationships

Representation of Many
-
One


E/R: arrow pointing to “one.”


Rounded arrow = “exactly one.”

Many
-
many

Many
-
one

One
-
one

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

27

Example:

Drinkers Have Favorite Beers

name

addr

license

name

manf

name

addr

Beer

Drinker

Bar

Serves

Frequents

Likes

Favorite

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

28

One
-
One Relationships

Put arrows in both directions.



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?

Manufacturer

Beer

Best
-

seller

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

29

Attributes on Relationships


Shorthand for 3
-
way relationship:

Bar

Beer

Sells

price

Bar

Beer

Sells

price

Price

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

30


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.

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

31

Converting Multiway to 2
-
Way


Baroque in E/R, but 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.

Bar

Beer

The
-

Bar

Price

The
-

Beer

The
-

Price

BBP

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

32

Roles

Sometimes an E.S. participates more than
once in a relationship.


Label edges with
roles

to distinguish.

Husband

Wife

d
1


d
2

d
3


d
4






Drinker

Married

husband

wife

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

33




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
?

Buddy1

Buddy2

d
1


d
2

d
1


d
3

d
2


d
1

d
2


d
4






Drinker

Buddies

1

2

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

34

More Design Issues

1. Subclasses.

2. Keys.

3. Weak entity sets. (Next class.)

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

35

Subclasses

Subclass = special case = fewer entities =
more properties.


Example: Ales are a kind of beer. In
addition to the
properties

(= attributes and
relationships) of beers, there is a “color”
attribute for ales.

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

36

E/R Subclasses


Assume subclasses form a tree (no multiple
inheritance).


isa

triangles indicate the subclass relation.

name

manf

Beer

Ale

color

isa

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

37

Different Subclass Viewpoints

1.
E/R viewpoint
: An entity has a
component

in each entity set
to which it logically belongs.


Its properties are the union of the properties of these E.S.

2. Contrasts with
object
-
oriented viewpoint
: An object (entity)
belongs to exactly one class.


It
inherits

properties

of its superclasses.

name

manf

Beer

Ale

color

isa

Pete’s Ale

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

38

Multiple Inheritance

Theoretically, an E.S. could be a subclass of
several other entity sets.

name

manf

Beers

name

manf

Wines

Grape

Beers

isa

isa

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

39

Problems

How should conflicts be resolved?


Example:
manf

means vintner for wines,
bottler for beers. What does
manf

mean for
“grape beers”?


Need ad
-
hoc notation to resolve meanings.


In practice, we shall assume a tree of entity
sets connected by
isa
, with all “isas”
pointing from child to parent.

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

40

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.

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

41

Example


Suppose
name

is key for
Beers
.










Beer name is also key for ales.


In general, key at root is key for all.

name

manf

Beers

Ales

color

isa

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

42

Example: A Multiattribute Key


Possibly, the combination of hours + room
also forms a key, but we have not
designated it as such.

dept

room

Courses

number

hours

Winter 2002

Judy Cushing, Art Keller, Jeff Ullman

1

43

In Class exercise (if time)

or

Assignment for Wednesday

(for those who did SOS this Fall)

Convert the UML for EU
-
Bid and EU
-
Lease
to an ER diagram.

Merge them into a design for one database.