CS计算机代考程序代写 SQL database >>

>>
Catalogs

• Database Objects
• Catalogs
• Representing Databases
• Representing Tables
COMP9315 21T1 ♢ Catalogs ♢ [0/11]
∧ >>
❖ 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]
<< ∧ >>
❖ 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]
<< ∧ >>
❖ Catalogs (cont)

The catalog is affected by several types of SQL operations:
• create Object as Definition
• drop Object …
• alter Object   Changes
• grant Privilege on Object
where Object is one of table, view, function, trigger, schema, …
E.g. drop table Groups; produces something like

delete from Tables
where schema = ‘public’ and name = ‘groups’;

COMP9315 21T1 ♢ Catalogs ♢ [3/11]
<< ∧ >>
❖ 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]
<< ∧ >>
❖ Catalogs (cont)

You can explore the PostgreSQl catalog via psql commands
• d gives a list of all tables and views
• d Table gives a schema for Table
• df gives a list of user-defined functions
• df+ Function gives details of Function
• ef Function allows you to edit Function
• dv gives a list of user-defined views
• d+ View gives definition of View
You can also explore via SQL on the catalog tables
COMP9315 21T1 ♢ Catalogs ♢ [5/11]
<< ∧ >>
❖ Catalogs (cont)

A PostgreSQL installation (cluster) typically has many DBs
Some catalog information is global, e.g.
• catalog tables defining: 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]
<< ∧ >>
❖ Catalogs (cont)

Side-note:   PostgreSQL tuples contain
• owner-specified attributes (from create table)
• system-defined attributes
• oid
•
• unique identifying number for tuple (optional)
• tableoid
•
• which table this tuple belongs to
• xmin/xmax
•
• which transaction created/deleted tuple (for MVCC)
• 


OIDs are used as primary keys in many of the catalog tables.
COMP9315 21T1 ♢ Catalogs ♢ [7/11]
<< ∧ >>
❖ 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]
<< ∧ >>
❖ 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]
<< ∧ >>
❖ 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 fields from the pg_class table:
• oid, relname, relnamespace, reltype, relowner
• relkind, reltuples, relnatts, relhaspkey, relacl, …
COMP9315 21T1 ♢ Catalogs ♢ [10/11]
<< ∧ ❖ 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] Produced: 15 Feb 2021