COMP9315-lecture1

COMP9315-lecture1
https://github.com/weil0819/COMP9315
1------------------------
comp9315 19T2
DBMS Implementation(data structures and algorithms inside relational DBMSs)
Lecturer: john Shepherd
Web Site:
http://www.cse.unsw.edu.au/~cs9315
23-------------------
Lecturer jiangshi 
4----------------------------
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 i?concepts in
  distributed and non-relational databases?
  
  Develop skill in:
  - analysing the performance of data-intensive algorithms
  - the use of C to implment data-instensive algorithms
5----------------
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 applicaiton 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.
6----------------------------
Exercise 1: 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 ann SQL query to solve the problem
- find all students who passed COMP9315 in 18s2
- for each student, give(student ID, name,mark)

demo
cd cs9315
/users/jas/cs9315
$source srvr/env9
-bash: srvr/env9: No such file
$ cd lectures
/users/jas/srvr/apps/cs9315/19T2/lectures
$ cd exercises
/users/jas/srvr/apps/cs9315/19T2/lectures/week01/exercises
$ ls
pg-data pizza.dump uni.sql pg-src query unixio
$vi query
---------------query begin
--Schema:
--Students(sid, name, degree, ...)
--Courses(cid, code, term, title, ...)
--Enrolments(sid, cid, mark, grade)

-- all stduents who passed COMP9315 in 16s1 (sid, name, mark)

select s.sid, s.name, e.mark
from Students s join Enrolments e join Courses c
where c.code = 9315 and c.term = 1821 and mark >= 50

select s.sid, s.name, e.mark
from Students s join Enrolments e join Courses c
where c.code = 'COMP9315' and c.term = '18s2' and mark >= 50

Tmp1 = sel[code=9315 and term=18s2]Courses
Tmp2 = join[cid](Tmp1,Enrolments)
Tmp3 = sel[mark >= 50]Tmp2
Tmp4 = join[sid](Tmp2,Students)
Res = proj[sid,name,mark]Tmp4
---------------query end

7--------------------------------------------------------
Exercise 2: 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.

$ cd unixio
/users/jas/srvr/apps/cs9315/19T2/lectures/week01/exercises/unixio
$ ls
Makefile blocks small.txt big.txt blocks.c
$ ./blocks 1024 big.txt
$ vi blocks.c
#include <stdlib.h>
#include <stdio.h>
#include <unistd.h>
#include <sys/types.h>
#include <sys/stat.h>
#inlcude <fcntl.h>

void giveUp(char *);

int main(int argc, char **argv)
{
  char *buf;
  int inf; // file descriptor
  int bufsize;
  ssize_t nread;
  int nblocks = 0, nbytes = 0;
  
  // process command line args
  if (args<3) giveUp("Insufficient args");
  bufszie = atoi(argv[1]);
  if(bufsize <100) giveUp("Invalid buffer size");
  buf = malloc(bufsize * sizeof(char));
  if(buf==null) giveUp("can't create buffer");
  
  // open file
  if((inf = open(argv[2], O_RDONLY))<0)
    giveUp("can't read file");
  
  // read file and count blocks/bytes
  whiel((nread = read(inf, buf,bufsize)) != 0) {
    nblocks++;
    nbytes += nread;
    printf("%ld bytes read in current block\n", nread);
  }
  
  // display results
  printf("Read %d blocks and %d bytes\n", nblocks, nbytes);
  
  exit(EXIT_SUCCESS);
}
8--------------------------------------------------------
Learning/Teaching
- Textbooks: descirbe some syllabus（kechengbiao）topics in detail
- Notes: descirbe syllabus topcis in some detail
- Lecture slides: summarise Note and contain exercises
- Lecture videos: for review or if you miss a lecture, or are in WEB stream
- Readings: research papers on selected topics

The onus(zeren) is on you to use this materail

Note: Exercises and vidoes will be availabel only after the lecture.
9--------------------------------------------------------
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(pingjia)

Dependencies:
- Exercises -> Exam(theory part)
- Prac work -> Assignments -> Exam(prac part)
There are no tute/lab classes; use Forum, Email, Consults
- Debugging is best done in person(can see ful context)

Prac Exercises
P01 installing your PostgreSQL server
cd /srvr/YOU
tar xfj /web/cs9315/postsql/postgresql-10.
... creaes and populates a directory called postgresql-11.3
cd postgresql-11.3
./configure --prefix=/srvr/YOU/pgsql
...produces lots of output...
edit src/backend/storage/ipc/latch.c
... and fix an annoying Grieg glitch ...
... search for "epoll_create1" ...
... on the line above "#if define(WAIT_USE_EPOLL" ...
... add #undef EPOLL_CLOEXEC" ...
$ make
... produces lots of output; take approx 3-5 minutes...
$ make install
... produces lots of output ...
$ cp /web/cs9315/19T2/postgresql/env /srvr/YOU/env
$ source /srvr/YOU/env
$ which inidb
/srvr/YOU/pgsql/bin/initdb
$ inidb
... produces some output, takes approx 1 minute...
$ ls $PGDATA
... gives a listing of newly-created PostgreSQL data directory
 
10--------------------------------------------------------
Rough Schedule
week01 intro, dbms review, RA, catalogs
week02 storage: disks,buffers, pages, tuples
week03 RA ops:scan, sort, projection
week04 selection:heaps, hashing, indexes
week05 no lecutes
week06 selection:N-d matching, similarity
week07 joins:naive, sort-merge,hash join
week08 query processing, optimisation
week09 transactions: concurrency, recovery
week10 distributed and non-SQL databases
11--------------------------------------------------------
Textbooks
No official text book; serveral are suitable...
- Garcia-Molina,Ullman,Widom
  "Database Systems: The Complete Book"
- Remakrishnan, Gehrke
  "Database Systems management"
-Silberschatz, Korth, Sudarshan
  "Database System Concepts"
- Kifer, Bernstern, Lewis
  "Database Systems: An algorithmic-oriented approach"
- Elmasri, Navathe
  "Database Systems: Models, languages, design..."
  
  but not all cover all topics in detail        
12--------------------------------------------------------
Prac Work
In this course, we use PostgreSQL V11.3(compulsory) qiangzhi
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 do the first Prac Exercise when it becomes availabel
Sort out any problems ASAP (preferably at a consultation)
13--------------------------------------------------------
Prac Work (cont)
PostgreSQL is a large software system:
- >1700 source code files in the core engine/clients
- > 1,000,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 whith this.
PostgreSQK books?
- tend to add little to the manual, and cost a lot

demo
cd systems
/users/jas/systems
cd postgresql-11.3
/users/jas/systems/postgresql-11.3
cd src
$ vi -t PostgresMain
: ta exec_query

14--------------------------------------------------------
Assignments(zuoye)
Schedule of assignment work:
1 storage management week5 13%
2 query processing   week10 17%
Assignments will be carried out in groups of size 1-3
choose own online tools to share code(e.g. git, dropbox).
ultimately, submission is via CSE's give system
Wil spend some time in lectures reviewing assignments.
Assignments will require up-front code-reading (see pracs).
15--------------------------------------------------------
Assignments(cont)
Don't leave assignment to the last minute
- they require significant cod reading
- as well as code writing and testing
- and, you cna submit early
carrot: bounus marks are available for early submissions.
stick: marks deducted (from max) for late submissions.
16--------------------------------------------------------
Quizzes xiaoceyan
Over the course of the semester.. xueqi
- six online quizzes
- 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 10% of your overall mark for the course.
17--------------------------------------------------------
Exam
Three-hour exam in the August exam period
Exam is held in CSE labs(learn the environment,VLab)
The course notes(only) will be available in the exam
Thins 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 contribute 60% of the overall mark for this course.

18--------------------------------------------------------
Exam cont
If you cannot attend the final exam...
- because of documented illness/misadventure
then you will be offered a Supplementary Exam.
You get one chance at passing the exam
-unsw's new fit-to-sit rule applies
-exam hurdle 24/60 which is only 40%

21--------------------------------------------------------
Relational DBMS Functinality
Relational DBMSs provide a variety of functionalities:
- storing/modifying data and meta-data (data definitions)
- constraint definition/storage/maintenance/checking
-declarative manipulation of data(via SQL)
-extensibility via veiws,triggers,stored procedures
-querey re-writing(rules), optimisation(index)
-transaction processing, concurrency/recovery
-etc,etc,etc.
Common feature of all relational DBMSs: relational model, SQL.