• Week 07
• Famous DB Nerds #6
• Week 05
• Famous DB Nerds #5
• Week 04
• Famous DB Nerds #4
• Week 03
• Assignment 1
• Famous DB Nerds #3
• Week 02
• Famous DB Nerds #2
• Week 01
• Text References
• Prac Work
• Famous DB Nerds #1

❖ Week 07

Things to Note ...

• Assignment 2 now available ... due Monday Week 10
• Quiz 3 out Sunday ... due Friday 9pm

This Week ...

• Signature-based Indexing (E17.4, GUW14.4-6, SKS12.5, K?)
• Implementing Join (EN15.3, GUW15, SKS13.5, K10.5)

Coming Up ...

• Query execution and optimisation (EN18, GUW16, SKS13, K11)

❖ Famous DB Nerds #6

❖ Famous DB Nerds #6 (cont)

Paul Larson Witold Litwin

Together, invented linear hashing Litwin: University Paris 9 Dauphine Awarded ACM Fellowship 2001 Query processing, distributed databases Recently: secure data in cloud databases Larson: U Waterloo, Microsoft Research Awarded ACM Fellowship 2004 Query processing/optimization Recently: SQL server, main-memory databases

❖ Week 05

Things to Note ...

• Assignment 1 due before 9pm THIS Friday (19 March)

This Week ...

• 1d Selection (indexes, B-trees) (E16.8,17, G14, S12, K9.4-6)
• Nd Selection: indexes, hashing (E18.2, G14.4-6, S12.5, K10.4)

Coming Up ...

• Implementing Join (EN15.3, GUW15, RG14.4, SKS13.5)
• Query execution and optimisation (EN18, GUW16, RG12, KSS13)

❖ Famous DB Nerds #5

❖ Famous DB Nerds #5 (cont)

Rudolf Bayer Ed McCreight

Together, they invented the B-tree (1972) Bayer Extensive database research career access methods (B-tree, UB-tree, ...) deductive and XML databases query processing in digital libraries Now, Emeritus Prof, Technical U Berlin McCreight Unis: Washington, Stanford, TUM, ETH Worked for Boeing, Xerox PARC, Adobe Now, retired to Switzerland

❖ Week 04

Things to Note ...

• Assignment 1 due before Friday 19 March, 9pm
• Quiz 2 due before Friday 12 March, 9pm

This Week ...

• RelOps: Projection (E18.2, G15.2, R14.3, S13.6, K10.3)
• RelOps: Selection
• Selection (heaps, sorted files) (E16.6,7, G14, S12.1, K9.2,3)
• 1d Selection (hashing) (E16.8,17, G14, S12, K9.4-6)

Coming Up ...

• RelOps: Nd Selection (E18.2, G14.4-6, R?, S12.5, K10.4)

❖ Famous DB Nerds #4

❖ Famous DB Nerds #4 (cont)

Bruce Lindsay

PhD UC Berkeley 1976 Part of System R Team at IBM buffer pool manager, ... Since System R: distributed databases (R*) transactions/concurrency control query optimisation composite objects in RDBMS database replication IBM Research Fellow (Almaden Lab) Chief Data Scientist, Paradata

❖ Week 03

Things to Note ...

• Assignment 1 due before Friday 19 March, 11:59pm
• New Pracs and Theory Exercises available
• Quiz 2 coming next week ...

This Week ...

• Storage: representing tuples (E?, G12, R9.6-7, S11.7)
• Relops: scan, sort, project (E5.7-9, G15, R13,14.3, S13.4,6)
• RelOps: Scan, Sort (E18.2, G15.1-2, R13, S13.4, K10.2)

Coming Up ...

• Relops: Projection (E18.2, G15.2, R14.3, S13.6, K10.3)
• RelOps: 1d Selection (E18.2, G14, R10,11, S12, K10.4)
• RelOps: Nd Selection (E18.2, G14.4-6, R?, S12.5, K10.4)

❖ Assignment 1

Aim: add a new base data type to PostgreSQL

• intSet type, with literals like '{1,2,3,4}'
• parse literals to canonical internal form, display them
• stored tuples may not be fixed-size
• implement operations like membership, subset

Hint: User-defined Types, Section 37.13 in PostgreSQL Docs

Testing data and testing framework available soon

Note: complex.* only gives you a rough sketch (e.g. fixed-size)

❖ Famous DB Nerds #3

❖ Famous DB Nerds #3 (cont)

IBM San Jose Research Centre Members of System R Team System R (1975) first prototype RDBMS Pioneered architecture of modern RDBMSs Chamberlin: Design of SQL (SEQUEL) Lorie: Buffer manager, optimizer, SQL Mehl: First SQL parser to see a modern SQL parser: postgresql-12.5/src/backend/parser

❖ Week 02

Things to Note ...

• Quiz 1 ... out Monday 9am ... due Friday 23:59
• Assignment 1 released soon
• Course Notes ... detailed slides ... coming soon

This Week's Topics ...

• Storage: disks and files (E16.2, G15.7, K9.1, S11.5)
• Storage: buffer pool/cache (E16.3, G15.7, K9.1, S11.5)
• Storage: page internals (E?, G8.3, S11.8)
• Storage: representing tuples (E16.4, G12, S11.7)

❖ Famous DB Nerds #2

❖ Famous DB Nerds #2 (cont)

Michael Stonebraker

Professor, UC Berkeley (1971-2000) Developed/commercialised several DBMSs: Ingres: very early relational DBMS Distributed Ingres: very early dDBMS Postgres: object-relational DBMS (Illustra) XPRS: Parallel Postgres Mariposa: federated data system (Cohera) Aurora: stream processing (StreamBase) C-store: column-oriented DBMS (Vertica) Morpheus: data integration system (Goby) H-store: distributed XP system (VoltDB) SciDB: DBMS for scientific apps (Paradigm4) ACM Turing Award 2014 IEEE John von Neumann Medal 2005 Currently Adjunct Professor at MIT

❖ Week 01

Things to Note ...

• Videos + Slides available Sun/Mon each week
• Prac P01 now available ... install PostgreSQL
• Quiz 1 ... out next Monday ... due next Friday
• Assignment 1 released soon ... add a new base type
• Course Admin: Dylan Brotherston

This Week's Topics ...

• DBMS Revision (Architecture, SQL, RelAlg, Catalogs)
• PostgreSQL introduction and installation
• Storage: devices/files (E16, G11, K9.1, S11)

Next Week's Topics ...

• buffer pool, page internals, tuple representation

❖ Text References

References to material in texts use format:

• BookChapter.Section....   (e.g. E2.1.1)

Books are denoted by:

• G = Garcia-Molina/Ullman/Widom
• S = Silberschatz/Korth/Sudarshan
• E = Elmasri/Navathe
• K = Kifer/Bernstein/Lewis

❖ Prac Work

Some hints/tips:

• Read instructions ... read log/error messages ... debug
• Give complete context when asking for help (OS, settings, etc)
• Do all PostgreSQL work on Grieg   (or on your laptop)
  • ssh -Y zID@grieg.cse.unsw.edu.au
  • binaries compiled on Grieg only run on Grieg
  • other incompatible versions of PostgreSQL exist
    try which psql ... if not the version under /srvr/zID ...
• priv srvr creates /srvr/zID/ directory
• Start all Grieg sessions with source /srvr/zID/env
• Stop the server after playing with it

❖ Famous DB Nerds #1

❖ Famous DB Nerds #1 (cont)

Charles Bachman

First to develop notion of "database" Turing Award 1973, for this achievement 1950 Michigan State College, Mech Eng 1950-1960 Dow Chemical, process control 1957 ... first business EDP department 1964 ... MIACS (manufacturing control system) Integrated Data Store (IDS) Store, Retrieve, Modify, Delete Transaction-oriented processing Bachman diagrams (pre-cursor to ER) Locking, deadlock-detection/recovery 1971-1980 Honeywell, IDS development 1980-1996 Bachman Info Sys, CASE tools