COMP9315-lecture2

https://cgi.cse.unsw.edu.au/~cs9315/22T1/pracs/p01/index.php

COMP9315 24T1 - Prac Exercise 01 (unsw.edu.au)

COMP9315 Introduction (unsw.edu.au)  --这个有129页的PPT，就的19T是100页

COMP9315 Week 01 Thursday Lecture (unsw.edu.au)

Slides - COMP9315 24T1 (unsw.edu.au)

COMP9315 Week 01 Thursday Lecture (unsw.edu.au)

COMP9315 24T1 - COMP9315 24T1 - DBMS Implementation (unsw.edu.au)

GitHub - weil0819/COMP9315: UNSW CSE COMP9315 Database Systems Implementation

WebCMS3 (unsw.edu.au)  --南威的课，还有么有感兴趣的？

---------------------------------------
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)
);

$ psql -l
$ psql 
jas=# create table RR(x integer, y integer);
jas=# \d
jas=# \d rr
---------------------------------------
Data Modification 24/100
Critical function of DBMS: changing data
 insert new tuples into tables
 delete existing tuples from tables
 update values within existing tuples

jas=# insert into values(3,77)
jas=# select * from pairs;
jas=# delete from pairs where x > 3
---------------------------------------
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

 jas=# explain select * from pairs;
 jas=# \d pairs

---------------------------------------

DBMS Architecture
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
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.
---------------------------------------
Database Engine Operations 33/100
DB engine = "relational algebra virtual machine":
  selection (σ) projection (π) join (⋈)
  union (∪) intersection (∩) difference (-)
  sort group aggregate
For each of these operations:
  various data structures and algorithms are available
  DBMSs may provide only one, or may provide a choice
---------------------------------------
Relational Algebra 34/100
Relational algebra (RA) can be viewed as ...
  mathematical system for manipulating relations, or
  data manipulation language (DML) for the relational model
Core relational algebra operations:
  selection: choosing a subset of rows
  projection: choosing a subset of columns
  product, join: combining relations
  union, intersection, difference: combining relations
  rename: change names of relations/attributes
Common extensions include:
  sorting (order by), partition (group by), aggregation
---------------------------------------
... Relational Algebra 35/100
All RA operators return a result of type relation.
For convenience, we can name a result and use it later.
E.g. database R1(x,y), R2(y,z),
  Tmp1(x,y) = Sel[x>5]R1
  Tmp2(y,z) = Sel[z=3]R2
  Tmp3(x,y,z) = Tmp1 Join Tmp2
  Res(x,z) = Proj[x,z] Tmp3
-- which is equivalent to
  Tmp1(x,y,z) = R1 Join R2
  Tmp2(x,y,z) = Sel[x>5 & z=3] Tmp1
  Res(x,z) = Proj[x,z]Tmp2
Each "intermediate result" has a well-defined schema.

select x,z from R1 jion R2 where x>5 and z = 3
Proj[x,z] ( Sell[x>5 & z=3] (R1 Join R2) )
---------------------------------------
Describing Relational Algebra Operations 36/100
We define the semantics of RA operations using
  "conditional set" expressions e.g. { x | condition }
  tuple notations:
    t[ab] (extracts attributes a and b from tuple t)
    (x,y,z) (enumerated tuples; specify attribute values)
  quantifiers, set operations, boolean operators
Notation: r(R) means relation instance r based on schema R
---------------------------------------
Relational Algebra Operations 37/100
Selection
  σC(r) = Sel[C](r) = { t | t ∈ r ∧ C(t) }
  C is a boolean function that tests selection condition
Computational view:
result = {}
for each tuple t in relation r
  if (C(t)) { result = result ∪ {t} }
---------------------------------------