• Lecturer
• Useful Contacts
• Course Goals
• Pre-requisites
• Exercise: SQL (revision)
• Exercise: Unix File I/O (revision)
• Learning/Teaching
• Rough Schedule
• Textbooks
• Prac Work
• Assignments
• Quizzes
• Exam
• Assessment Summary
• Relational Database Revision
• Relational DBMS Functionality
• Data Definition
• Data Modification
• Query Evaluator
• DBMS Architecture
• Installing/Using PostgreSQL
• Installing PostgreSQL
• Before Installing ...
• Installing/Using PostgreSQL
• Exercise: Install PostgreSQL
• Using PostgreSQL for Assignments

 

❖ Lecturer

Name:		John Shepherd
Admin:		Deputy Head of School (education)
Office:		K17-410   (turn right from lift)
Email:		cs9315@cse.unsw.edu.au
Consults:		still working out the details
Research:		e-Learning Technologies (e.g. Webcms) Information Extraction/Integration Information Retrieval/Web Search Multimedia Databases Query Processing

❖ Useful Contacts

Email:		cs9315@cse.unsw.edu.au
The Nucleus		Enrolment problems
Special Consideration		Illness, Misadventure Assignment extension, Supp Exam Must be documented
Forum		Assignment Problems
Email/Help Sessions		Detailed assignment Problems

❖ Course Goals

Introduce you to:

• architecture of relational DBMSs  (e.g. PostgreSQL)
• algorithms/data-structures for data-intensive computing
• representation of relational database objects
• representation of relational operators (sel,proj,join)
• techniques for processing SQL queries
• techniques for managing concurrent transactions
• ?concepts in distributed and non-relational databases?

Develop skills in:

• analysing the performance of data-intensive algorithms
• the use of C to implement data-intensive algorithms

❖ Pre-requisites

We assume that you are already familiar with

• the C language and programming in C (or C++)
    (e.g. completed ≥ 1 programming course in C)
• developing applications on RDBMSs
    (SQL, [relational algebra]   e.g. an intro DB course)
• basic ideas about file organisation and file manipulation
    (Unix   open, close, lseek, read, write, flock)
• sorting algorithms, data structures for searching
    (sorting, trees, hashing   e.g. a data structures course)

If you don't know this material very well, don't take this course.

❖ Exercise: SQL (revision)

Given the following schema:

Students(sid, name, degree, ...)
e.g. Students(3322111, 'John Smith', 'MEngSc', ...)
Courses(cid, code, term, title, ...)
e.g. Courses(1732, 'COMP9311', '12s1', 'Databases', ...)
Enrolments(sid, cid, mark, grade)
e.g. Enrolments(3322111, 1732, 50, 'PS')

Write an SQL query to solve the problem

• find all students who passed COMP9315 in 18s2
• for each student, give (student ID, name, mark)

❖ Exercise: Unix File I/O (revision)

Write a C program that reads a file, block-by-block.

Command-line parameters:

• block size in bytes
• name of input file

Use low-level C operations: open, read.

Count and display how many blocks/bytes read.

❖ Learning/Teaching

What's available for you:

• Textbooks: describe some syllabus topics in detail
• Notes: describe syllabus topics in some detail (from 2019)
• Topic Videos: describe syllabus topics in less detail (from 2021)
• Lecture slides: summarise Topic Videos and contain exercises (old+new)
• Lecture videos: for review or if you miss a lecture, or are in WEB stream
• Readings: research papers on selected topics

The onus is on you to use this material.

Note: Exercises and videos will be available only after the lecture.

❖ Learning/Teaching (cont)

Things that you need to do:

• Exercises: tutorial-like questions
• Prac work: lab-class-like exercises
• Assignments: large/important practical exercises
• On-line quizzes: for self-assessment

Dependencies:

• Exercises → Exam (theory part)
• Prac work → Assignments → Exam (prac part)

There are no tute/lab classes; use Forum, Email, Help Sessions

• debugging is best done in person  (can see full context)
• at the very least, send error messages   (not screenshots)

❖ Rough Schedule

Week 01		intro, dbms review, RA, catalogs
Week 02		storage: disks, buffers, pages, tuples
Week 03		RA ops: scan, sort, projection
Week 04		selection: heaps, hashing, indexes
Week 05		selection: N-d matching, similarity
Week 06		no lectures
Week 07		joins: naive, sort-merge, hash join
Week 08		query processing, optimisation
Week 09		transactions: concurrency, recovery
Week 10		distributed and non-SQL databases

❖ Textbooks

No official text book; several are suitable ...

• Garcia-Molina, Ullman, Widom
  "Database Systems: The Complete Book"
• Ramakrishnan, Gehrke
  "Database Systems Management"
• Silberschatz, Korth, Sudarshan
  "Database System Concepts"
• Kifer, Bernstein, Lewis
  "Database Systems: An algorithmic-oriented approach"
• Elmasri, Navathe
  "Database Systems: Models, languages, design ..."

but not all cover all topics in detail

❖ Prac Work

In this course, we use PostgreSQL v15.6   (compulsory)

Prac Work requires you to compile PostgreSQL from source code

• instructions explain how to do this on Linux at CSE
• also works easily on Linux and Mac OSX at home
• PostgreSQL docs describe how to compile for Windows

Make sure you do the first Prac Exercise when it becomes available.

Sort out any problems ASAP (preferably at a help session).

❖ Prac Work (cont)

PostgreSQL is a large software system:

• > 2400 source code files in the core engine/clients
• > 1,400,000 lines of C code in the core

You won't be required to understand all of it :-)

You will need to learn to navigate this code effectively.

Will discuss relevant parts in lectures to help with this.

PostgreSQL books?

• tend to add little to the manual, and cost a lot

❖ Assignments

Schedule of assignment work:

Ass		Description		Due		Marks
1		New Data Type		Week 5		15%
2		Query Processing		Week 10		20%

Assignments will be carried out individually

Ultimately, submission is via CSE's give system.

Will spend some time in lectures reviewing assignments.

Assignments will require up-front code-reading (see Pracs).

❖ Assignments (cont)

Don't leave assignments to the last minute

• they require significant code reading
• as well as code writing and testing
• and, you can submit early.

Standard UNSW late penalty applies to assignments

• 5% off total mark for each day late
• after 5 days, mark is zero

Valid special consideration can give extension of X days

• late penalty starts accruing after X days

❖ Assignments (cont)

Cheating will be penalised with mark of zero for that assignment

• submitting work copied from another student
• submitting work copied from an online code repo
• submitting work derived from generative AI
• submitting work written by someone else for $$$

You are only cheating yourself ... losing an opportunity to learn

❖ Quizzes

Over the course of the semester ...

• five online quizzes; released Monday, due Friday
• taken in your own time (but there are deadlines)
• each quiz is worth a small number of marks

Quizzes are primarily a review tool to check progress.

But they contribute 15% of your overall mark for the course.

No extensions on quizzes, since solutions released on following Monday

For valid special consideration on quiz

• cancel late penalty if quiz submitted before solutions released
• remove this quiz from calculation of overall quiz mark if not submitted

❖ Exam

Three-hour exam in the May exam period.

Exam is held in CSE Labs (learn the environment, VLab)

PostgreSQL and C documentation (only) will be available in the exam.

Things that we can't reasonably test in the exam:

• writing large programs, running major experiments

Everything else is potentially examinable.

Contains: descriptive questions, analysis, small programming exercises.

Exam contributes 50% of the overall mark for this course.

❖ Exam (cont)

If you cannot attend the final exam ...

• because of documented illness/misadventure

then you will be offered a Supplementary Exam (in O-week of Term 2)

You get one chance at passing the exam

• unsw's fit-to-sit rule applies

Exam hurdle 20/50 (which is 40%)

You must attend the exam in-person

• no online exams are available ... be in Sydney ... be at UNSW

❖ Assessment Summary

Your final mark/grade is computed according to the following:

quiz   = mark for on-line quizzes   (out of 15)
ass1   = mark for assignment 1      (out of 15)
ass2   = mark for assignment 2      (out of 20)
exam   = mark for final exam        (out of 50)
okExam = exam > 20/50           (after scaling)

mark   = ass1 + ass2 + quiz + exam
grade  = HD|DN|CR|PS,  if mark ≥ 50 && okExam
       = FL,           if mark < 50 && okExam
       = UF,           if !okExam

❖ Relational Database Revision

❖ Relational DBMS Functionality

Relational DBMSs provide a variety of functionalities:

• storing/modifying data and meta-data  (data defintions)
• constraint definition/storage/maintenance/checking
• declarative manipulation of data (via SQL)
• extensibility via views, triggers, stored procedures
• query re-writing (rules), optimisation (indexes)
• transaction processing, concurrency/recovery
• etc. etc. etc.

Common feature of all relational DBMSs: relational model, SQL.

❖ Data Definition

Relational data: relations/tables, tuples, values, types, e.g.

create domain WAMvalue float
   check (value between 0.0 and 100.0);

create table Students (
   id          integer,  -- e.g. 3123456
   familyName  text,     -- e.g. 'Smith'
   givenName   text,     -- e.g. 'John'
   birthDate   date,     -- e.g. '1-Mar-1984'
   wam         WAMvalue, -- e.g. 85.4
   primary key (id)
);

The above adds meta-data to the database.

DBMSs typically store meta-data as special tables (catalog).

❖ Data Definition (cont)

Input: DDL statement   (e.g. create table)

Result: meta-data in catalog is modified

❖ Data Modification

Critical function of DBMS: changing data

• insert new tuples into tables
• delete existing tuples from tables
• update values within existing tuples

E.g.

insert into Enrolments(student,course,mark)
values (3312345, 5542, 75);

update Enrolments set mark = 77
where  student = 3354321 and course = 5542;

delete Enrolments where student = 3112233;

❖ Data Modification (cont)

Input: DML statements

Result: tuples are added, removed or modified

❖ Query Evaluator

Most common function of relational DBMSs

• read an SQL query
• return a table giving result of query

E.g.

select s.id, c.code, e.mark
from   Students s
       join Enrolments e on s.id = e.student
       join Courses c on e.course = c.id;

❖ Query Evaluator (cont)

Input: SQL query

Output: table (displayed as text)

❖ DBMS Architecture

The aim of this course is to

• look inside the DBMS box
• discover the various mechanisms it uses
• understand and analyse their performance

Why should we care?   (apart from passing the exam)

Practical reason:

• if we understand how query processor works,
  we can (maybe) do a better job of writing efficient queries

Educational reason:

• DBMSs contain interesting data structures + algorithms
  which may be useful outside the (relational) DBMS context

❖ DBMS Architecture (cont)

Path of a query through a typical DBMS:

❖ DBMS Architecture (cont)

❖ DBMS Architecture (cont)

Important factors related to DBMS architecture

• data is stored permanently on large slow devices**
• data is processed in small fast memory

Implications:

• data structures should minimise storage utilisation
• algorithms should minimise memory/disk data transfers

Modern DBMSs interact with storage via the O/S file-system.

** SSDs change things a little, but most high volume bulk storage still on disks

❖ DBMS Architecture (cont)

Implementation of DBMS operations is complicated by

• potentially multiple concurrent accesses to data structures
  (not just data tables, but indexes, buffers, catalogues, ...)
• transactional requirements (atomicity, rollback, ...)
• requirement for high reliability of raw data (recovery)

Require "concurrency-tolerant" data structures.

Transactions/reliability require some form of logging.

❖ Installing/Using PostgreSQL

❖ Installing PostgreSQL

PostgreSQL is available via the COMP9315 web site.

Provided as tar-file in ~cs9315/web/24T1/postgresql/

File: postgresql-15.6.tar.bz2 is ~23MB **

Unpacked, source code + binaries is ~210MB **

If using on CSE, do not put it under your home directory

Place it under /localstorage/YOU/ which has 600MB quota

❖ Before Installing ...

If you have databases from previous DB courses

• the databases will no longer work under v15.6
• to preserve them, use dump/restore

E.g.

... login to vxdb
... run your old server for the last time ...
$ pg_dump -O -x myFavDB > /localstorage/YOU/myFavDB.dump
... stop your old server for the last time ...
... remove data from your old server ...
$ rm -fr /localstorage/YOU/pgsql
... install and run your new PostgreSQL 15.6 server ...
$ createdb myFavDB
$ psql myFavDB -f /srvr/YOU/myFavDB.dump
... your old database is restored under 15.6 ...

# Must be "source"d from sh, bash, ksh, ...

export PGDATA=/localstorage/$USER/pgsql/data
export PGHOST=$PGDATA
export LD_LIBRARY_PATH=/localstorage/$USER/pgsql/lib

export PATH=/localstorage/$USER/pgsql/bin:$PATH

alias p0="pg_ctl stop"
alias p1="pg_ctl -l $PGDATA/log start"

$ tar xfj ..../postgresql/src.tar.bz2
  # create a directory postgresql-11.3
  # set up environment variables
$ configure --prefix=$PGHOME
$ make
$ make install
$ source your/environment/file
$ initdb
  # set up postgresql configuration ... done once?
$ edit postgresql.conf
$ pg_ctl start -l $PGDATA/log
  # do some work with PostgreSQL databases
$ pg_ctl stop

$ edit source code
$ pg_ctl stop
$ make
$ make install
$ pg_ctl start -l $PGDATA/log
  # run tests, analyse results, ...
$ pg_ctl stop

$ edit source code
$ save a copy of postgresql.conf
$ pg_dump testdb > testdb.dump
$ pg_ctl stop
$ make
$ make install
$ rm -fr $PGDATA
$ initdb
$ restore postgresql.conf
$ pg_ctl start -l $PGDATA/log
$ createdb testdb
$ psql testdb -f testdb.dump
  # run tests and analyse results