• Week 01
• Text References
• Activities
• Famous DB Nerds #1
• Week 02
• Debugging
• Famous DB Nerds #2
• Week 03
• Famous DB Nerds #3
• Week 04
• Famous DB Nerds #4
• Week 05
• Famous DB Nerds #5
• Week 07
• Famous DB Nerds #6
• Week 08
• Famous DB Nerds #7
• Week 09
• Assignment 2
• Famous DB Nerds #8
• Week 10
• Famous DB Nerds #9
• Not-so-famous Database Nerds #1
• Week 11

❖ Week 01

Things to Note ...

• Important notices appear here ...
  • e.g. Assignment 3 Spec released tomorrow
  • e.g. Quiz 5 out now ... due Friday
• Not officially enrolled? Sort it out Real Soon
• Dropping the course? Send me email to let me know
• ELS students ... send me your advice ASAP

And watch the Welcome and Overview videos  (under Slides and Videos)

❖ Week 01 (cont)

Sources of content ...

• videos and slides ... follow course syllabus exactly
• course notes ... more details, plus non-examinable stuff
• textbooks ... full details on most course topics
• documentation ... PostgreSQL, SQLite, Psycopg2

Activities ...

• lectures ... reinforce topic videos by solving problems
• tutorials ... work theory/prac exercises
• quizzes ... review topics from previous week(s) (on Webcms3)
• prac exercises ... build skills for assignments (on d2.cse or own laptop)
• assignments ... solve non-trivial DB problems (on d2.cse or own laptop)

❖ Week 01 (cont)

This Week's Topics

• Data Modelling
• ER Data Model   (EN3,4, GUW2, KSS2, KBL4.1-4)
• Relational Data Model   (EN7, GUW3, KSS3, KBL3.2)
• ER-to-relational Mapping   (EN9.1, GUW3.2, KSS2.9, KBL4.5)

Upcoming Topics ...

• SQL Data Definition Language

❖ Text References

References to material in texts uses format:

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

Books are denoted by:

• EN = Elmasir/Navathe (4th ed)
• GUW = Garcia-Molina/Ullman/Widom (1st ed)
• KSS = Silberschatz/Korth/Sudarshan (4th ed)
• KBL = Kifer/Bernstein/Lewis (2nd ed)

We dropped the Ramakrishnan book ...

❖ Activities

Tutorials ...

• No tutes this week

Prac Work ...

• Play with SQLite ... be writing SQL in 5 mins
• Set up PostgreSQL server on  d2.cse.unsw.edu.au

Prac Exercises coming soon to the website.

❖ Famous DB Nerds #1

❖ Famous DB Nerds #1 (cont)

Larry Ellison

CEO of Oracle Corporation Founded in 1977 First product 1979 Now, world's #1 DB supplier (also acquired: Java, Peoplesoft, MySQL) Currently worth ~$30 billion (was $50B) Eccentric, opinionated, flamboyant

Old Silicon Valley Joke:

Q: What's the difference between God and Larry Ellison?

A: God doesn't think he's Larry Ellison.

❖ Week 02

Things to Note ...

• Quiz 1 ... open Monday 9am ... closes Friday midnight
• Prac Exercise 02 ... sort out a PostgreSQL server
• Prac Exercise 03 ... SQL Schemas and Constraints

This Week's Topics ...

• Data definition in SQL (EN8.1-2, GUW6.6, RG3.1-3, KSS4.11, KBL3.3)
• ER-to-SQL Mapping (EN9.1, GUW3.2, RG3.5, KSS2.9, KBL4.5)
• Managing data in SQL (EN8.6,9, GUW6.5, RG3.1, KSS3.10, KBL5.3)

❖ Week 02 (cont)

Tutorials ...

• meet your tutor and fellow tutees
• ER Model, Relational Model, SQL DDL (intro)

Prac Work ...

• Install a PostgreSQL server ASAP
• Prac 02 explains how to do it on vxdb2
• For home installation, Google "install postgresql"
• or look at Kyu-Sang's web page on this
  https://gist.github.com/hexDoor/14ab7fbb8b6301a150a332174e861b26

❖ Week 02 (cont)

Quizzes ...

• available on Monday at 9am
• close at midnight on following Friday
• can make multiple submissions
• late penalty if submitted after midnight Friday
• solutions released 9am on the following Monday
• no submissions permitted after solutions released
• if have special consideration, discount this quiz
• overall quizzes mark determined by # submitted quizzes

❖ Debugging

We cannot help you unless you give us sufficient information

The following is not sufficient:

$ p1
waiting for serv to start... stopped waiting
pg_ctl: could not start server
Examine the log output.

This tells us nothing except "My server didn't start"

At the very least, look at the log to see if you can work it out.

If you can't, paste the last few lines of the log file into your query.

Debugging via email/forum is difficult; arrange a Teams call for help.

❖ Famous DB Nerds #2

❖ Famous DB Nerds #2 (cont)

Peter Chen

Invented ER Data Model Original paper in 1976 One of the most-cited papers in CS ER provided foundation for: design methodologies (e.g. UML) CASE tools (e.g. Oracle Designer/2000) Fellow of ACM and IEEE Professor at Louisiana SU

❖ Week 03

Things to Note ...

• Quiz 2 ... due by Friday 3 March 11:59pm
• Assignment 1 ... due by Friday 17 March 11:59pm
• Quiz and Tute solutions for previous week out each Monday

Things to Do ...

• make sure you have a working PostgreSQL server
• look at the ER→SQL mapping in tutes
• Look at the Slides and Videos for Week 03
• load the Assignment 1 database and explore

❖ Week 03 (cont)

In Lectures this week ...

• Assignment 1 ... database
• Managing data in SQL (EN8.6,9, GUW6.5, RG3.1, KSS3.10, KBL5.3)
• Queries in SQL (EN7.2-5, GUW6.1-5, RG5.1-6, KSS4.1-10, KBL5.2)

Coming Up ...

• Stored Procedures (EN10.5, GUW8.2, RG6.5, KSS9.6)
• PLpgSQL (PostgreSQL Manual, Chapter 43)

❖ Famous DB Nerds #3

❖ Famous DB Nerds #3 (cont)

Don Chamberlin

Jim Mehl

Ray Lorie

IBM San Jose Research Centre Part of System R Team Chamberlin: Design of SQL Lorie: Design of SQL Mehl: First SQL Parser

❖ Week 04

Things to Note ...

• Quiz 3 ... due before Friday 10 March 11.59pm
• Assignment 1 ... due by Friday 17 March 11:59pm
• Quiz and Tute solutions for previous week out each Monday

Things to Do ...

• Make sure you have a working PostgreSQL server
• Look at the Slides and Videos for Week 04
• Load the Assignment 1 database and start writing SQL
• Discuss SQL queries in tutes, and maybe Assignment 1

❖ Week 04 (cont)

In Lectures this Week ...

• More Queries in SQL (EN7.2-5, GUW6.1-5, RG5.1-6, KSS4.1-10, KBL5.2)
• PLpgSQL (PostgreSQL Manual, Chapter 42)
• Aggregates (PostgreSQL Manual, CREATE AGGREGATE)

Coming Up ...

• Assertions and Triggers (EN8.6, GUW7.4, RG5.7-8, KSS6.3-4)

❖ Famous DB Nerds #4

The next in a series on famous database people ...

❖ Famous DB Nerds #4 (cont)

Bruce Lindsay

PhD. UC Berkeley, 1976 IBM Almaden Research Centre Part of System R Team buffer pool manager, ... Since System R: distributed databases & replication transactions/concurrency control query execution performance IBM Research Fellow now, Chief Data Scientist, Paradata

❖ Week 05

Things to Note ...

• Quiz 3 now due Wednesday 15 March 11:59pm
• Assignment 1 ... due this Friday 17 March 11:59pm
• No classes or Quiz next week (Flex week)

In Lectures This Week ...

• More PLpgSQL (PostgreSQL Manual, Chapter 42)
• Aggregates (PostgreSQL Manual, CREATE AGGREGATE)
• Constraints and Triggers (EN8.6, GUW7.4, RG5.7-8, KSS6.3-4)
• Interface between DBMSs and Programming Languages

❖ Famous DB Nerds #5

❖ Famous DB Nerds #5 (cont)

Patricia Selinger

PhD Harvard, Applied Maths, 1975 1976 IBM San Jose Research Centre Part of System R Team Cost-based query optimisation Awards: IBM Research Fellowship ACM Innovations Award Director of DB Technology Institute at IBM Almaden Research Centre until 2014 2014-2018 worked in industry (Paradata) now retired

❖ Week 07

Things to Note ...

• Quiz 4 this week .. due this Friday at 11:59pm
• Assignment 2 ... due Friday 14 April 11:59pm

This Week's Topics ...

• Python and psycopg2 online documentation e.g. docs.python.org
• Relational Design Theory   (EN10, GUW3.4-6, RG19, KSS3)

Coming Up ...

• Relational Algebra   (EN7.4, GUW5, RG4.2, KSS3.2, KBL5.1)

❖ Week 07 (cont)

Things you can grab now ...

• "Unseen" database for Assignment 1
• ~cs3311/web/23T1/assignments/ass1/ass1a.dump

Things we're still working on ...

• Assignment 1 marking ... ETA this week
• Assignment 2 Spec ... ETA Wednesday
• New Prac Exercise on Psycopg2 ... ETA Friday

❖ Famous DB Nerds #6

❖ Famous DB Nerds #6 (cont)

Jeff Ullman

DB Professor at Stanford U (1980...) Very well-known database researcher Published over 200 articles + 10 textbooks (DB, theory, data-structs, compilers, ...) Research areas include: deductive databases (NAIL) information itegration (TSIMMIS) information extraction (MIDAS) PhD supervisor of Sergey Brin, Larry Page

❖ Week 08

Things to Note ...

• Quiz 5 .. due Friday April 7 at 11:59pm
• Assignment 2 ... due Friday April 14 at 11:59pm
  • spec to be finalised Real Soon Now

This Week's Topics ...

• Relational Design Theory (normalization)   (EN10, GUW3.4-6, RG19, KSS3)

Coming Up ...

• Relational Algebra   (EN7.4, GUW5, RG4.2, KSS3.2, KBL5.1)

❖ Week 08 (cont)

Python3 / Psycopg2

• see example scripts from Week 5/7 lectures
• see prac exercise: Prac 08
• see tute exercises: Week 8

Webcms3 Forum usage

• Forums page has a decent search function ... use it
• forums are context-based ... send message from relevant page

❖ Famous DB Nerds #7

❖ Famous DB Nerds #7 (cont)

Ted Codd

Researcher at IBM San Jose Lab Invented relational model (RM) Original paper in 1970 Proposed Alpha query language Very influential on DB research/practice ACM Turing Award 1981 Died April 2003

Turing Award Citation:
For his fundamental and continuing contributions to the theory and practice of database management systems. He originated the relational approach to database management in a series of research papers published commencing in 1970. His paper "A Relational Model of Data for Large Shared Data Banks" was a seminal paper, in a continuing and carefully developed series of papers. Dr. Codd built upon this space and in doing so has provided the impetus for widespread research into numerous related areas, including database languages, query subsystems, database semantics, locking and recovery ...

❖ Week 09

Things to Note ...

• Assignment 2 ... due this Friday 23:59
• Help Sessions ... Tu 5-7, We 5-7, Th 4-6, Fr 4-6

This Week's Topics ...

• Query Evaluation   (EN13, GUW11, RG8, KSS11, KBL9)
• Transactions, Concurrency   (EN19, GUW8.6,18.1-3, RG16, KSS15, KBL18,20)

Coming Up ...

• Concurrency Control   (EN19, GUW8.6,18.1-3, RG16, KSS15, KBL18,20)
• Databases: past, present, future
• Course Review, Exam Preview

❖ Assignment 2

Things to note:

• 60 submissions so far (30 fully correct) ... 480 to go
• last minute work ⇒ vxdb2 overloaded + dylan overloaded
• need to send code? send as attachment, not as a screenshot
• make sure helpers.* load without error into new ass2 db
• make sure all scripts are executable (chmod 750)
• don't  create views within Python code
• use typecasts to fix "anonymous composite type" errors
• write to stderr via   print("...",file=sys.stderr)

❖ Famous DB Nerds #8

❖ Famous DB Nerds #8 (cont)

Michael Stonebraker

Professor, UC Berkeley (1971-2000) Adjunct Professor at MIT (2000-now) Developed/commercialised several DBMSs: Ingres: very early relational DBMS (mid-70's) Distributed Ingres: very early dDBMS Postgres: object-relational DBMS (Illustra) (late 80's) 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 applications IEEE John von Neumann Medal 2005 ACM Turing Award 2014

❖ Week 10

Things to Note ...

• Quiz 6 ... due Friday at 11:59pm
• All course marks sorted out by early Week 11
• Exam on Monday 1 May (details in Tuesday lecture)
• MyExperience course/teacher evaluation open now

This Week's Topics ...

• Course Review, Exam Preview

❖ Famous DB Nerds #9

❖ Famous DB Nerds #9 (cont)

Jim Gray

Pioneered transaction processing 1969 PhD from UC Berkeley 1970-1995 IBM, Tandem, DEC 1995- Microsoft Research Lab 1999 Turing Award   (for tp research) Most recent work: scientific databases sensor networks + databases Disappeared at sea, 27 Jan 2007

❖ Not-so-famous Database Nerds #1

The next in a series on (not so) famous database people ...

Ok ... so it's obvious who it is ... but where am I? (Webcms3 Poll)

❖ Week 11

Things to Note ...

• Exam preview: Week 10 Tuesday Lecture
• Course marks sorted out by late Week 11
• Pre-exam Consults: Mon 1-2.30, Wed 11.30-1, Fri 11-1
  • see Notices for link to Zoom session
• Exam on Monday 1 May in CSE Labs
  • Exam database available on Thursday
• MyExperience evaluation closes on Thursday
  • only 19% response rate so far (100/540)