The POSTGRES Data Model

1
The POSTGRES Data Model

• Author Lawrence A. Rowe, Michael R. Stonebrake
  (Berkley, 1987)
• Speakers Yu Li, Ying Yang

2
Contents

• Introduction
• Data Model
• Data Types
• User-defined Procedures
• Other data models (if have time)

3
Objectives

• Object Management Storing manipulating
  non-traditional data, Bitmap, Text, Icons,
  Polygons.
• Knowledge Management store manage rules.
• Derive data
• Integrity constraints
• Claim real-world applications need conventional
  AND object AND knowledge management.
• POSTGRES IS DESIGNED TO TACKLE ALL THREE

4
POSTGRES Object-Relational DBMS

• POSTGRES Data Model
• POSTGRES Query Language (POSTQUEL)
• POSTGRES Rules System
• POSTGRES Storage System(not covered)

5
Data Model(OO-Term.)

• A class is a collection of objects or instances
• All instances of a class have the same
  attributes.
• Each attribute has a type
• Each instance has a unique OID(obj. id)
• Classes can inherit
• Supports multiple inheritance
• Conflicts in parental attribute are disallowed.

6
Data Model(cont.)

• Three kinds of classes
• real instances stored directly in database
• derived expressed as a query and are
  materialized as necessary
• version differential relative to a parent.

7
A Base Relation

• create PERSON(
• Namechar25,
• Birthdatedate , Heightint4,
• Weight int4, StreetAddresschar25,
• Citychar25, statechar2)
• key(Name)

8
Derived Relations (Inheritance)

• Create EMPLOYEE(
• Deptchar25,
• Statusint2,
• Mgrchar25,
• JobTitlechar25,
• Salarymoney)
• Inherits (PERSON)

• Create STUDENT(
• Snochar12,
• Statusint2,
• Level char20)
• Inherits (PERSON)
• Create STUDEMP(
• isWorkStudybool)
• Inherits(STUDENT,EMPLOYEE)

9
Relation Hierarchy
PERSON
EMPLOYEE
STUDENT
A relation inherits all attributes from its
parent(s) unless an attribute is overriden in the
definition.
Relations may Inherits from more than one
parents
STUDEMP
10
Versions/Snapshots of Relations

• create version MYEMPLOYEE from PERSON
• Updates to the version do not modify the base
  rel.
• Updates to the base rel. propagated to the
  version unless the value has been modified.
• --- snapshot
• create version YOUREMP from PERSONnow
• Updates to the base rel. not propagated to
  snapshot

11
Merge Command

• A merge command is provided to merge changes made
  to a version back into the base relation.
• merge YOURPEOPLE into PEOPLE
• merge command uses a semi automatic procedure to
  resolve updates to the base relation and the
  version that conflict.

12
Data Types

• Two kinds of types
• 1. Atomic types fundamental types, augmented by
  ADTs
• Structured types arrays of base types,
• procedure type-constructors(variale
  parameterized)

13
Atomic Types

• Predefined int2, int4, float4, bool, date, bool
• All atomic data types are defined as abstract
  data type(ADT). User can add new atomic types
  using ADT definition facility, e.g.
• define type int4 is (IntenalLength4,
• InputProc CharToInt4,
• OutputProcInt4ToChar,
• Default 0)

14
Define Operators

• define operator (int4, int4) returns int4
• is (ProcPlus, Precedence5, Associativityleft
  )

15
More complex operators

• define type box is
• (InternalLength16,
• InputProcCharToBox,
• OutputProc BoxToChar,
• Default)

• define operator AE(box,
• box) returns bool is
• (ProcBoxAR, precedence 3, Associatvity
  left, Sort BoxArea, Hashes, Restrict
  AERSelect, Join AEJSelect, Negator BoxAreaNE)

16
Structured Types

• Array type constructor
• fixed-size array, variable-sized array.
• create PERSON(Namechar25)
• create PERSON(Namechar)
• Procedure type constructors
• variable procedure-type.
• parameterized procedure-type.

17
Procedure types

• Variable
• Allows a different POSTQUEL procedure to be
  stored in each tuple.

• Parameterized
• Stores the same procedure in each tuple but with
  different parameters.

18
Variable Procedure Type

• create DEPARTMENT
• (Name char25,
• Chairchar25,.)
• create STUDENT
• (, Majorspostquel,
• )

• append STUDENT (
• Namesmith,,
• Majors
• retrieve (D.all)
• from D in DEPARTMENT where D.Name Math or
  D.Name CS)

19
Parameterized Procedure Type

• create ENROLL
• (Studentchar25,
• Classchar25)
• retrieve(ClassName E.class) from E in ENROLL
  where E.Student Bill

• define type classes is
• retrieve(ClassName E.class)
• from E in ENROLL
• where E.Student .Name
• end

20
User-Defined Procedures

• It is written in a conventional programming
  language ( C, Pascal).
• Two usages
• - Implement ADT(Abstract data type) operators
• - Move a computation from a front-end
  application process to the back-end DBMS process.

21
Conventional Relation DB Design

• create FORM(FromName, )
• create FIELDS(FormName, FieldName, Origin,
  Height, Width, FieldKind, )
• create SCALARFIELD(FormName, Fieldname, DateType,
  DisplayFormat, )
• create TABLEFIELD(FormName, FieldName,
  NumberOfRows, )
• create TABLECOLUMNS(FormName, FieldName,
  ColumnName, Height, Width, FieldKind, )

22
New Approach

• define type formrep is
• retrieve (rep MakeForm(.FormName))
• end
• addattribute (Formname,
• FormDataStructure formrep)
• to FORM
• retrieve (x FROM.FormDataStructure)
• where FORM.FormName foo

23
A Procedure

• It is defined to the system by specifying the
  following
• - the names and types of the arguments
• - the return type
• - the language it is written in
• - where the source and object code is stored.
• define procedure AgeInYears(data) returns int4
• is (language C, filename AgeInYears)

24
Procedure - cont

• Stored the information in the system catalogs.
• Dynamically load the object code when it is
  called in a query.
• Can take tuple-variable arguments.
• Can be passed tuples in other relations that
  inherit the attributes in the relation declared
  as the argument to the procedure.

25
Procedure Arguments

• Must be passed in a self-describing data
  structure.
• Reasons
• - Procedure can be passed tuples from different
  relations
• - Attributes inherited from other relations may
  be in different positions in the relations
• - The values passed for the same attribute name
  may be different types

26
Self-describing Data Sturcture

• A list of arguments, one per attribute in the
  tuple to be passed
• Ex (AttrName, AttrType, AttrValue)

27
Procedure Inheritance

• define procedure Comp(EMPLOYEE)
• returns int4 is (language C, filename
  Comp1)
• define procedure Comp(STUDENT)
• returns int4 is (language C, filename
  Comp2)
• IPL (inheritance precedence list)

28
Procedure Inheritance - Cont

• Ex
• - create PERSON (Name char25, )
• - create EMPLOYEE (Dept char25, )
• inherits (PERSON)
• - create STUDENT (Sno char12, )
• inherits (PERSON)
• - create STUDEMP (isWorkStudy bool, )
• inherits (STUDENT, EMPLOYEE)
• Its IPL is (STUDEMP, STUDENT, EMPLOYEE, PERSON)

29
Other Models

• Semantic data model
• Functional data model
• Object oriented data model

30
Semantic Functional Data Model

• Not flexible.
• Cannot easily represent data with uncertain
  structure.

31
POSTGRES Model

• Uses procedures to represent shared subobjects.
• The nested-dot notation allows convenient access
  to selected subobjects.
• Support non-first normal form relations with
  procedure-types.

32
Object Oriented Data Model

• Have modeling constructs to deal with uncertain
  structure.
• Sharing of subobjects is represented by storing
  the subobjects as separate records and connecting
  them to a parent object with pointer-chains.
• Performance problem with pointer-chains arises
  when an object is composed of a large number of
  subobjects.

33
POSTGRES Model

• Instead of pointer-chains, it is represented as a
  relation and the system can use all of the query
  processing and storage structure techniques
  available in the system to represent it.