Week 3

Homework 1: sql1.text, due Friday, Feb 9

Read the notes Introduction to Relational Databases

• Chapter 1/1, Databases and Database Users (skim)
  
• Chapter 2/2, Database System Concepts an Architecture, Section 1 on Data Models, Schemas and Instances
  
• Chapter 5/3, The Relational Data Model and SQL Sections 1 & 2
  

1. Relational Math

2. Atomic transactions, in "A few more basic concepts"

3. Where did Mr Borg go in last Friday's join giving employees and their supervisors:

select e.ssn, e.super_ssn from employee e;

select e.lname, super.lname from employee e join employee super on e.super_ssn = super.ssn;

Now put left in front of join in the second one. Why does it change?

4. Start on SQL Part 1

Wednesday

Foreign keys

A Look At Constraints

SQL examples

Friday

A strange join

create table nums (num integer primary key);

insert into nums select generate_series as num from generate_series(1,100);

select count(*) from nums n1 join nums n2 on random() < 0.5;

What is this join?

Compare with "select e.lname, d.dname from employee e join department d on e.dno = d.dnumber;"

What is "select * from nums n1 join nums n2 on random() < 0.1;"

Query Example 2