INFO 340 - April 5, 2004 - Lecture 3
Notes By: Fortier, Prins

Database Management & Information Retrieval


Assignment 1 due April 12 before class in an email to the TA with a link to your page.
Project, Deliverable 1 due April 19
Notes on A1:
See website for comment on duplicates


Read Chapters 5 & 6 as well as Appendix C


Recap From Last Time:
Key Idea: Data independence
ANSI-SPARC three-level architecture
Two Kinds of Data Independence:
    Logical: If you add a field in the DB and it would not break an external view for that database.
    Physical: Having your data on different hard drives or different physical systems. Internal schema is what defines where the data is stored on the physical drive.

It is easier to add a table to a DB then it is to remove from a DB. Systems should be in place to check that if you remove a table a warning would be displayed telling the user of problems if the table is removed

(Some Key) Functions of DBMS (Database Management Systems)
Wont talk about in class but have a basic understanding
    1) Data storage, retrieval, and update
    2) A user-accessible catalog
        - It is represented as a database. All of the data that is needed by the relational database is stored in another relational database. It is recursive.
    3) Transaction support
    4) Concurrency control
    5) Recovery Services
    6) Authorization services
    7) Support for data communication
    8) Integrity services

SQL (Structured Query Language)

SQL: Key Aims
A language to...
    - Specify the structure of a database
        > It lets you establish the type of data that you wish to store (chars, ints, etc...)
    - Perform data management (INSERT, DELETE, & UPDATE)
    - Specify queries
        >  Query By Example: Use a GUI to create queries like Microsoft Access
Other goals:
    - Easy to learn syntax
    - Standard language
    
Two Types of SQL Commands
Data Definition Commands 
    - Create database structure
        > CREATE TABLE
        > ALTER TABLE
        > DROP TABLE

Data Manipulation
    - Populate and query tables
        > SELECT
        > INSERT
        > UPDATE
        > DELETE

Database Design: Example
Database: BoatClub
Three Tables:
    Sailor (sid: integer; sname: string; rating: integer; age: real)
    Boat (bid: integer, bname: string; color: string)
    Reserve(sid:integer; bid:integer; day:date)

Basic Form of SQL Query
SELECT [DISTINCT] select-list (attributes)
FROM from-list (tables)
WHERE qualification (comparisons, criteria)

Basic SQL Stuff:
* = Get all
DISTINCT = Don't get duplicates
AS = Make an alias (FROM coolpeople AS cp)
ORDER BY = Orders the results (ASC by default) [ASC, DESC]


Qualifiers:
Boolean combinations (AND, OR, NOT)
Comparison operators (<, <=, <>, =, >=, >)

SELECT [DISTINCT] select-list <---- Projection
FROM from-list <---- Cross product
WHERE qualification <---- Selection

Introduction to Joins
Cartesian Product (cross product) shows all possibility

SELECT s.name
FROM Sailor s, Reserve r
WHERE s.id = r.id AND r.bib = 103

Find the names of sailors who have reserved a red boat
SELECT s.name
FROM Sailor s, Reserve r, Boat b
WHERE (s.sid = r.sid) AND (r.bid = b.bid) AND (b.color = 'Red');

Aggregate Operators
    > Count ([DISTINCT] A)
    > SUM ([DISTINCT] A)
    > AVG ([DISTINCT] A)
    > MAX(A)
    > MIN(A)


SELECT AVG(s.age)
FROM Sailor s
WHERE ....

How many sailors are there?
SELECT COUNT(*)
FROM Sailor;