Computer science 180 database systems
Download
1 / 41

Computer Science 180: Database Systems - PowerPoint PPT Presentation


  • 162 Views
  • Updated On :

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 4:30–5:30pm and by appointment Class web page: http://www.cse.ucsc.edu/classes/cmps180

Related searches for Computer Science 180: Database Systems

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Computer Science 180: Database Systems' - tracey


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Computer science 180 database systems
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 4:30–5: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

Arthur Keller – CS 180


Textbooks
Textbooks

Required:

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

    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.

  • PostgreSQL: Introduction and Concepts, Bruce Momjian, Addison-Wesley, 2001.

    You may also want:

  • Books on Unix, Perl, PHP, and CGI.

Arthur Keller – CS 180


Grading
Grading

  • Assignments: 8 @ 2% each = 16% of grade.

  • Project: 7 @ 5% each = 35% of grade.

  • Midterm: Feb. 14 (TH) in class. 14% of grade.

  • Final: Mar. 16 (Sa) 12–3PM in class. 35% of grade.

Arthur Keller – CS 180


Project
Project

  • You will build an application using a relational database system (PostgreSQL) accessed via the web.

  • The project has 7 parts (due most Thursdays), starting with design and ending up with a complete application.

  • The early programming assignments should be written in C, C++ or Java.

  • Some students found it helpful to switch to PHP or Perl for the final project.

Arthur Keller – CS 180


Warning
Warning

  • This class is a lot of work.

  • But it is worth it.

  • Of all courses you take at UCSC, this may be the one that gets you a job.

Arthur Keller – CS 180


Schedule
Schedule

  • Today: Jan. 3 (TH)

    • Intro, Entity-Relationship Model.

    • Read Chapter 1 and Sections 2.1-2.2.

  • Jan. 8 (T)

    • Weak Entity Sets, Entity-Relationship Design.

    • Read Sections 2.3-2.4.

  • Jan. 10 (TH)

    • Relational Model, Functional Dependencies.

    • Read Sections 3.1-3.5.

  • Jan. 15 (T)

    • Normal Forms, Multivalued Dependencies.

    • Read Sections 3.6-3.7. Assignment 1 due.

Arthur Keller – CS 180


Syllabus
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.

  • Transactions and Security.

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

  • Mediation and warehousing.

Arthur Keller – CS 180


What is a database management system
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.

Arthur Keller – CS 180


Relational model
Relational Model

  • Based on tables, as:

    acct # name balance

    12345 Sally 1000.21

    34567 Sue 285.48

    … … …

  • Today used in most DBMS's.

Arthur Keller – CS 180


The dbms marketplace
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.

Arthur Keller – CS 180


Three aspects to studying dbms s
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 partly in CS277.

Arthur Keller – CS 180


Query languages
Query Languages

Employee

Department

SELECT ManagerFROM Employee, DepartmentWHERE 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>

Name

Dept

Dept

Manager

SQL

Arthur Keller – CS 180


Host languages
Host Languages

C, C++, Fortran, Lisp, COBOL

Application prog.

Host language is completely general (Turing complete)

but gives you no support

Query language—less general "non procedural" and

optimizable

DBMS

Calls to

DB

Local Vars

(Memory)

(Storage)

Arthur Keller – CS 180


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

ALU

ADDER

CPU

A

FA

Adder

ALU

ADDER

Bill of Materials or transitive closure

Arthur Keller – CS 180


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

Arthur Keller – CS 180


LOGIC (Horn Clause) DATA MODEL complex

• 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)

Arthur Keller – CS 180


60’s complex

70's

80's

90’s

now

Hierarchical

Network

Relational

Choice for most new applications

Object Bases

Knowledge Bases

Arthur Keller – CS 180


Entity relationship model
Entity/Relationship Model complex

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.

name

phone

ID

Students

height

Arthur Keller – CS 180


Relationships
Relationships complex

  • Connect two or more entity sets.

  • Represented by diamonds.

Taking

Students

Courses

Arthur Keller – CS 180


Relationship set
Relationship Set complex

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

    … …

Arthur Keller – CS 180


Multiway relationships
Multiway Relationships complex

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).

    Possibly, this E/R diagram is OK:

Taking

Students

Courses

Assisting

TA/Graders

Arthur Keller – CS 180


  • Works in CS180, 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, 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.

Arthur Keller – CS 180


Courses students. Connection student-TA is

Students Courses TAs

Ann CS180 Jan

Sue CS180 Pat

Bob CS180 Jan

… … …

Enrolls

Students

TAs

Arthur Keller – CS 180


Beers bars drinkers example
Beers-Bars-Drinkers Example students. Connection student-TA is

  • Our running example for the course.

name

addr

license

Frequents

Serves

Bars

Likes

Beers

Drinkers

name

manf

name

addr

Arthur Keller – CS 180


Multiplicity of relationships
Multiplicity of Relationships students. Connection student-TA is

Representation of Many-One

  • E/R: arrow pointing to “one.”

    • Rounded arrow = “exactly one.”

Many-many

Many-one

One-one

Arthur Keller – CS 180


Example drinkers have favorite beers
Example: students. Connection student-TA is Drinkers Have Favorite Beers

name

addr

license

Frequents

Serves

Bars

Likes

Beers

Drinkers

Favorite

name

manf

name

addr

Arthur Keller – CS 180


One one relationships
One-One Relationships students. Connection student-TA is

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?

Best-seller

Manfs

Beers

Arthur Keller – CS 180


Attributes on relationships
Attributes on Relationships students. Connection student-TA is

price

  • Shorthand for 3-way relationship:

Sells

Bars

Beers

price

Prices

Sells

Bars

Beers

Arthur Keller – CS 180


  • A true 3-way relationship. students. Connection student-TA is

    • 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.

Arthur Keller – CS 180


Converting multiway to 2 way
Converting Multiway to 2-Way students. Connection student-TA is

  • 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.

BBP

The-Bar

The-Beer

The-Price

Bars

Beers

Price

Arthur Keller – CS 180


Roles
Roles students. Connection student-TA is

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

  • Label edges with roles to distinguish.

Husband Wife

d1d2

d3 d4

… …

Married

husband

wife

Drinkers

Arthur Keller – CS 180


Buddy1 Buddy2 students. Connection student-TA is

d1d2

d1 d3

d2d1

d2 d4

… …

Buddies

  • 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?

1

2

Drinkers

Arthur Keller – CS 180


More design issues
More Design Issues students. Connection student-TA is

1. Subclasses.

2. Keys.

3. Weak entity sets. (Next class.)

Arthur Keller – CS 180


Subclasses
Subclasses students. Connection student-TA is

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.

Arthur Keller – CS 180


E r subclasses
E/R Subclasses students. Connection student-TA is

  • Assume subclasses form a tree (no multiple inheritance).

  • isa triangles indicate the subclass relation.

Beers

name

manf

isa

Ales

color

Arthur Keller – CS 180


Different subclass viewpoints
Different Subclass Viewpoints students. Connection student-TA is

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 propertiesof its superclasses.

Beers

name

manf

isa

Pete’s Ale

Ales

color

Arthur Keller – CS 180


Multiple inheritance
Multiple Inheritance students. Connection student-TA is

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

name

manf

name

manf

Beers

Wines

isa

isa

Grape

Beers

Arthur Keller – CS 180


Problems
Problems students. Connection student-TA is

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.

Arthur Keller – CS 180


Keys students. Connection student-TA is

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.

Arthur Keller – CS 180


Example
Example students. Connection student-TA is

  • Suppose name is key for Beers.

  • Beer name is also key for ales.

    • In general, key at root is key for all.

Beers

name

manf

isa

Ales

color

Arthur Keller – CS 180


Example a multiattribute key
Example: A Multiattribute Key students. Connection student-TA is

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

number

hours

Courses

dept

room

Arthur Keller – CS 180


ad