Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

SQL Language, Relational Model Example - Lecture Slides | 198 336, Study notes of Computer Science

Material Type: Notes; Class: 198 - PRIN INFO & DATA MGT; Subject: COMPUTER SCIENCE; University: Rutgers University; Term: Unknown 1989;

Typology: Study notes

Pre 2010

Uploaded on 09/17/2009

koofers-user-d86-1
koofers-user-d86-1 🇺🇸

10 documents

1 / 21

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
1
Principles of Information and
Database Management
198:336
Week 5 – Feb 21
Matthew Stone
Today
Joins and other advanced features of SQL
Recap
Loose ends
Joins
Optimization and relational algebra
Summary statistics in SQL
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff
pf12
pf13
pf14
pf15

Partial preview of the text

Download SQL Language, Relational Model Example - Lecture Slides | 198 336 and more Study notes Computer Science in PDF only on Docsity!

Principles of Information and

Database Management

Week 5 – Feb 21

Matthew Stone

Today

Joins and other advanced features of SQL

  • Recap
  • Loose ends
  • Joins
  • Optimization and relational algebra
  • Summary statistics in SQL

Recap

Relational model

sid name login age gpa 53666 Jones jones@cs 18 3. 53688 Smith smith@eecs 18 3. 53650 Smith smith@math 19 3.

In the relational model, IM stores

set of relations or tables

TELL INFORMATION MANAGER ASK

DEFINE

Relational model example

Tell statement

insert into books values ('0-672-31697-8', 'Michael Morgan', 'Java 2 for Professional Developers', 34.99);

Relational model example

Tell statement

  • Adds a row to the specified table in the information manager to include the specified entity or relationship.

Relational model example

Query example

SELECT author,title FROM books WHERE price > 30;

Relational model example

This returns a new table

author title Michael Morgan

Java 2 for Professional Developers

... …

Loose ends

Null values and primary keys

create table nullness ( id integer primary key, stuff text );

insert into nullness values (null, "you lose");

Loose ends

Updating an existing row

update table

set column=expression

where restriction

Update

changes columns in existing table rows

  • Set clause indicates which columns to modify and the values they should be given.
  • Where clause specifies which rows should be updated.

Example

Book gets “new edition”

Current row of books:

  • isbn='0-672-31697-8',
  • author='Michael Morgan',
  • title='Java 2 for Professional Developers',
  • price=34.

Example

Book goes out of print

delete from books where isbn='0-672-31697-8‘

Loose ends

Adding or deleting columns: alter table

Add a column, give all rows null value:

alter table books

add column publisher char(40)

Loose ends

Adding or deleting columns: alter table

Get rid of a column:

alter table books

drop column publisher

Loose ends

Adding or deleting columns: alter table

Lots of other ways to use this command.

Demo break

Any questions?

Joins - Motivation

How do you combine information from

multiple tables?

Example, from book domain:

who ordered what titles?

Recap

Not useful:

select C.name, O.isbn from customers C, order_items O

  • performs cross product on tables
  • no connections between rows

Recap

Need to establish relationships

select C.name, I.isbn from customers C, orders O, order_items I where C.customerid = O.customerid and O.ordernumber = I.orderid

Selection

Extract rows from a relation

extract all the rows from relation R that

satisfy condition

 condition ( R )

Example

Get the rows from S where rating > 8

Corresponds to

select * from S where rating > 8

 rating  8 ( S )

Projection

Extract columns from a relation

make a smaller table from R with just the

specified columns

 columns ( R )

Example

Extract sailor names and ratings

corresponds to

select sname, rating from S

 sname rating , ( S )

Equijoins

Join conditions contain only equalities

Duplicated fields are dropped

Natural join: special case

  • all fields in common are equated

R Ä R. i  S i. S

Equivalences

Cascading of selections

Commutativity of selections

 (^) c  d ( S )  (^) c ( (^) d ( S ))

 (^) c ( (^) d ( S ))  (^) d ( (^) c ( S ))

Equivalences

Cascading projections

 (^) c ( (^) d ( S ))  c ( S )

Equivalences

Commutativity of joins

Associativity of joins

R  S  S  R

R  ( S  T )  ( R  S ) T