CSE 544 Principles of Database Management Systems

[Pages:67]CSE 544 Principles of Database Management Systems

Magdalena Balazinska Winter 2015

Lecture 2 ? SQL and Schema Normalization

Magda Balazinska - CSE 544, Winter 2015

1

Announcements

? Paper review

? First paper review is due before lecture on Wednesday

? Find partners (0 or more) for the project

? Project groups due on Wednesday (email) ? You don't need to choose a project yet; more suggestions will

continue to be posted

? Start working on Homework 1 now!

? Due in two weeks

Magda Balazinska - CSE 544, Winter 2015

2

Outline

Two topics today ? Brief overview of SQL ? Brief overview of database design

Magda Balazinska - CSE 544, Winter 2015

3

References

? R&G Book. Chapter 4 "Relational Algebra and Calculus" and chapter 5 "SQL DML".

? R&G Book. Chapter 19: "Schema refinement and normal forms"

? Also relevant to this lecture. Chapter 2: "Introduction to database design" and Chapter 3.5: "Logical database design: ER to relational"

Magda Balazinska - CSE 544, Winter 2015

4

Relational Model

Database is a collection of relations ? Relation schema

Supplier(sno: integer, sname: string, scity: string, sstate: string)

? Relation instance

sno

sname scity

sstate

1

s1

city 1

WA

2

s2

city 1

WA

3

s3

city 2

MA

4

s4

city 2

MA

Magda Balazinska - CSE 544, Winter 2015

5

Relational Algebra Queries

An RA expression represented as a tree

sname

pno=pno

Relations are sets of tuples Each operator takes relations as input and outputs a relation Can easily compose operators into expressions also called plans

sno=sno

Supplier Supply

psize > 10

Part

Magda Balazinska - CSE 544, Winter 2015

6

Extended Operators of Relational Algebra

? Duplicate elimination ()

? Since commercial DBMSs operate on multisets/bags not sets

? Aggregate operators ()

? Useful in practice and requires bag semantics ? Min, max, sum, average, count

? Grouping operators ()

? Partitions tuples of a relation into "groups" ? Aggregates can then be applied to groups

? Sort operator ()

Magda Balazinska - CSE 544, Winter 2015

7

Relational Calculus

? Alternative to relational algebra

? Declarative query language ? Describe what we want NOT how to get it

? Tuple relational calculus query

? { T | p(T) } ? Where T is a tuple variable ? p(T) denotes a formula that describes T ? Result: set of all tuples for which p(T) is true ? Language for p(T) is subset of first-order logic

Q1: Names of patients who have heart disease

{ T | P AnonPatient V Voter

(P.zip = V.zip P.age = V.age P.disease = `heart' T.name = V.name ) }

Magda Balazinska - CSE 544, Winter 2015

8

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download