CS计算机代考程序代写 database SQL 2021/4/28 Catalogs

2021/4/28 Catalogs
Catalogs
Database Objects Catalogs
Representing Databases Representing Tables
>>
COMP9315 21T1 ♢ Catalogs ♢ [0/11]
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/catalogs/slides.html
1/13

2021/4/28 Catalogs
∧ >>
❖ Database Objects
RDBMSs manage different kinds of objects
databases, schemas, tablespaces relations/tables, attributes, tuples/records constraints, assertions
views, stored procedures, triggers, rules
Many objects have names (and, in PostgreSQL, some have OIDs).
How are the different types of objects represented?
How do we go from a name (or OID) to bytes stored on disk?
COMP9315 21T1 ♢ Catalogs ♢ [1/11]
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/catalogs/slides.html
2/13

2021/4/28 Catalogs
<< ∧ >>
❖ Catalogs
Consider what information the RDBMS needs
about relations:
name, owner, primary key of each relation
name, data type, constraints for each attribute
authorisation for operations on each relation
Similarly for other DBMS objects (e.g. views, functions, triggers, …)
This information is stored in the system catalog tables
Standard for catalogs in SQL:2003:
INFORMATION_SCHEMA
COMP9315 21T1 ♢ Catalogs ♢ [2/11]
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/catalogs/slides.html
3/13

2021/4/28 Catalogs
<< ∧ >>
❖ Catalogs (cont)
The catalog is affected by several types of SQL
operations:
create Object as Denition drop Object … alterObject Changes grant Privilege on Object
where Object is one of table, view, function, trigger, schema, …
E.g.drop table Groups;producessomething like
delete from Tables
where schema = ‘public’ and name = ‘groups’;
COMP9315 21T1 ♢ Catalogs ♢ [3/11]
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/catalogs/slides.html
4/13

2021/4/28 Catalogs
<< ∧ >>
❖ Catalogs (cont)
In PostgreSQL, the system catalog is available
to users via:
special commands in the psql shell (e.g. \d)
SQL standard information_schema e.g.select * from
information_schema.tables;
The low-level representation is available to sysadmins via:
a global schema called pg_catalog
a set of tables/views in that schema (e.g.
pg_tables) COMP9315 21T1 ♢ Catalogs ♢ [4/11]
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/catalogs/slides.html
5/13

2021/4/28 Catalogs
<< ∧ >>
❖ Catalogs (cont)
You can explore the PostgreSQl catalog via
psql commands
\d gives a list of all tables and views
\d TablegivesaschemaforTable
\df gives a list of user-dened functions \df+ FunctiongivesdetailsofFunction \ef FunctionallowsyoutoeditFunction \dv gives a list of user-dened views
\d+ ViewgivesdenitionofView
You can also explore via SQL on the catalog tables
COMP9315 21T1 ♢ Catalogs ♢ [5/11]
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/catalogs/slides.html
6/13

2021/4/28 Catalogs
<< ∧ >>
❖ Catalogs (cont)
A PostgreSQL installation (cluster) typically
has many DBs
Some catalog information is global, e.g.
catalog tables dening: databases, users, …
one copy of each such table for the whole PostgreSQL installation
shared by all databases in the cluster (in PGDATA/pg_global)
Other catalog information is local to each database, e.g
schemas, tables, attributes, functions, types, …
separate copy of each “local” table in each database
a copy of many “global” tables is made on database creation
COMP9315 21T1 ♢ Catalogs ♢ [6/11]
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/catalogs/slides.html
7/13

2021/4/28 Catalogs
<< ∧ >>
❖ Catalogs (cont)
Side-note: PostgreSQLtuplescontain
owner-specied attributes (from create table)
system-dened attributes
oid
tableoid xmin/xmax
unique identifying number for tuple (optional)
which table this tuple belongs to
which transaction created/deleted tuple (for MVCC)
OIDs are used as primary keys in many of the catalog tables.
COMP9315 21T1 ♢ Catalogs ♢ [7/11]
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/catalogs/slides.html
8/13

2021/4/28 Catalogs
<< ∧ >> ❖ Representing Databases
Above the level of individual DB schemata, we have:
databases … represented by pg_database schemas … represented by pg_namespace
table spaces … represented by pg_tablespace
These tables are global to each PostgreSQL cluster.
Keys are names (strings) and must be unique within cluster.
COMP9315 21T1 ♢ Catalogs ♢ [8/11]
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/catalogs/slides.html
9/13

2021/4/28 Catalogs
<< ∧ >> ❖ Representing Databases (cont)
pg_database contains information about databases:
oid, datname, datdba, datacl[], encoding, …
pg_namespace contains information about schemata:
oid, nspname, nspowner, nspacl[]
pg_tablespace contains information about tablespaces:
oid, spcname, spcowner, spcacl[]
PostgreSQL represents access via array of access items:
Role=Privileges/Grantor
where Privileges is a string enumerating privileges, e.g.
jas=arwdRxt/jas,fred=r/jas,joe=rwad/jas
COMP9315 21T1 ♢ Catalogs ♢ [9/11]
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/catalogs/slides.html
10/13

2021/4/28 Catalogs
<< ∧ >>
❖ Representing Tables
Representing one table needs tuples in several
catalog tables.
Due to O-O heritage, base table for tables is
called pg_class.
The pg_class table also handles other “table-
like” objects:
views … represents attributes/domains of view
composite (tuple) types … from CREATE TYPE AS
sequences, indexes (top-level defn), other “special” objects
All tuples in pg_class have an OID, used as primary key.
Some elds from the pg_class table:
oid, relname, relnamespace, reltype,
relowner
relkind, reltuples, relnatts, relhaspkey, relacl, …
COMP9315 21T1 ♢ Catalogs ♢ [10/11]
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/catalogs/slides.html
11/13

2021/4/28 Catalogs
<< ∧ ❖ Representing Tables (cont) Details of catalog tables representing database tables pg_class holds core information about tables relname, relnamespace, reltype, relowner, ... relkind, relnatts, relhaspkey, relacl[], ... pg_attribute contains information about attributes attrelid, attname, atttypid, attnum, ... pg_type contains information about types typname, typnamespace, typowner, typlen, ... typtype, typrelid, typinput, typoutput, ... COMP9315 21T1 ♢ Catalogs ♢ [11/11] cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/catalogs/slides.html 12/13 2021/4/28 Catalogs Produced: 15 Feb 2021 cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/catalogs/slides.html 13/13