6 8306 814 Introduction to Databases 6 830

6. 830/6. 814 Introduction to Databases 6. 830 Lecture 1 Sam Madden madden@csail. mit. edu http: //dsg. csail. mit. edu/6. 830

Administrivia http: //dsg. csail. mit. edu/6. 830 Email: 6. 830 -staff@mit. edu Ask questions on Piazza! Lecturers: Sam Madden (madden@csail. mit. edu) Michael Cafarella (michjc@csail. mit. edu) TAs: Tianyu Li (litianyu@mit. edu) Siva Sudhir (siva@csail. mit. edu) Kapil Vaidya (kapilv@mit. edu) Office hours: TBD

Textbooks • Readings in Database Systems – Available on Books 24 x 7. com – http: //libraries. mit. edu/get/books 24 x 7 • Database Management Systems, 3 rd ed. by Ramakrishnan and Gehrke

Source: mattturck. com

Zoo Data Model Entity Relationship Diagram 1 Animal name 1 age n entity contains relationship 1 Cage n 1 entity feed. Time 1 Time Name keeps 1 species 1 Age bldg n Keeper Species Animals have names, ages, species Keepers have names Cages have cleaning times, buildings Animals are in 1 cage; cages have multiple animals Keepers keep multiple cages, cages kept by multiple keepers 1 1 entity Building name 1 Name

Our Zoo Sam the Salamander Siva the Giraffe Slimy Lanky Sally the Student

Break • Questions – Are there other ways to represent this zoo data than a collection of tables? – What are tradeoffs in different representations?

SQL – Structured Query Language SELECT field 1, …, field. M FROM table 1, … WHERE condition 1, … INSERT INTO table VALUES (field 1, …) UPDATE table SET field 1 = X, … WHERE condition 1, …

Names of Giraffes • Imperative for each row r in animals if r. species = ‘giraffe’ output r. name • Declarative SELECT r. name FROM animals WHERE r. species = ‘giraffe’

Cages in Building 32 • Imperative D E T S E N OPS LO for each row a in animals for each row c in cages if a. cageno = c. no and c. bldg = 32 output a • Declarative SELECT a. name FROM animals AS a, cages AS c WHERE a. cageno = c. no AND c. bldg = 32 J N I O

Average Age of Bears • Declarative SELECT AVG(age) FROM animals WHERE species = ‘bear’

Complex Queries Find pairs of animals of the same species and different genders older than 1 year: SELECT a 1. name, a 2. name FROM animals as a 1, animals as a 2 WHERE a 1. gender = M and a 2. gender = F AND a 1. species = a 2. species “self join” AND a 1. age > 1 and a 2. age > 1 Find cages with salamanders fed later than the average feedtime of any cage: SELECT cages. cageid FROM cages, animals WHERE animals. species = ’salamander' AND animals. cageid = cages. cageid AND cages. feedtime > “nested queries” (SELECT AVG(feedtime) FROM cages )

Complex Queries 2 Find keepers who keep both students and salamanders: SELECT keeper. name FROM keeper, cages as c 1, cages as c 2, keeps as k 1, keeps as k 2, animals as a 1, animals as a 2 WHERE c 1. cageid = k 1. cageid AND keeperid = k 1. keeperid AND c 2. cageid = k 2. cageid AND keeperid = k 2. keeperid AND a 1. species = ’student' AND a 2. species = ’salamander' AND c 1. cageid = a 1. cageid AND c 2. cageid = a 2. cageid a 1 c 1 k 1 species = ‘shrew’ a 2 species = ‘student’ keeperid = k 1. keeperid keeper c 2 keeperid = k 2. keeperid