CS W4111.001 Introduction to Databases Fall 2020
Computer Science Department Columbia University
1
Object-Relational Databases, or ORDBMSs
2
Inheritance
• To reuse and refine type definitions
• To create hierarchies of collections with similar but not identical objects
SQL standard doesn’t support multiple inheritance, so each subtype has only one immediate supertype
32
Type Inheritance Example
33
Type Inheritance Example
CREATE TYPE Person_Type AS ( name VARCHAR(20), address VARCHAR(20) INSTANTIABLE NOT FINAL);
// INSTANTIABLE: can have tables of this schema; NOT FINAL: subtypes possible We can use inheritance to define two subtypes:
CREATE TYPE Student_Type UNDER Person_Type AS (
degree VARCHAR(20),
department VARCHAR(20)
INSTANTIABLE); // all attributes of Person_Type plus 2 new ones
CREATE TYPE Teacher_Type UNDER Person_Type AS ( salary INTEGER,
department VARCHAR(20)
INSTANTIABLE); // all attributes of Person_Type plus 2 new ones
Subtypes can add new attributes, add new methods, change implementation of inherited methods (but signature of methods cannot change); subtypes cannot change inherited attributes, though
34
Table Inheritance Example
“Supertable”/“subtable” relationships specified also with UNDER:
35
propagated
Table Inheritance Example
“Supertable”/“subtable” relationships specified also with UNDER:
CREATE TABLE Person OF Person_Type REF IS person_id SYSTEM GENERATED;
CREATE TABLE Student OF Student_Type UNDER Person; CREATE TABLE Teacher OF Teacher_Type UNDER Person;
• When a tuple is inserted into a subtable (e.g., Student), it is also automatically inserted into its supertable(s) (e.g., Person)
• INSERT, DELETE, and UPDATE are appropriately
36
Inheritance Requirements
All tuples “corresponding to each other” (i.e., tuples with the same values for inherited attributes) must be derived from the insertion of just one tuple in one table
• In our example, we cannot then have a Person tuple corresponding to both a Student tuple and a Teacher tuple
• Then, Student and Teacher cannot overlap (unless Person is not INSTANTIABLE)
• Do Student and Teacher cover Person (i.e., does every Person tuple have to be either a Student or a Teacher)? Should be specified: Yes if Person_Type is declared as NON INSTANTIABLE
37
Substitution Principle
• Given a supertype A and a subtype B, it is always possible to substitute an object of type B into a legal expression written for objects of type A
• Then dynamic binding of methods is needed at query time (recall that subtype definitions can change implementation of inherited methods)
39
PostgreSQL 12 Support of OR Features
• Typeconstructors:
• ROW:notreallysupported,butcanuseCREATETYPEmechanism
• ARRAY:somewhatsupported,witharraysizelimitsandnumberof dimensions ignored (allowed for documentation only)
CREATE TABLE sal_emp (
name TEXT,
pay_by_quarter INTEGER [ ], schedule TEXT [ ] [ ]);
• MULTISET:notsupported
• User-definedtypes:supported(composite,enumerated, range, “base” types—similar to ADTs but only for superusers)
CREATE TYPE … AS …
• Objectreferences:notreallysupported(primitiveobjectids present, but not in full OR sense)
40
PostgreSQL 12 Support of OR Features
Inheritance: limited support, just to specify a list of tables from which a new table automatically inherits all columns
CREATE TABLE Cities ( name TEXT,
population FLOAT); CREATE TABLE Capitals (
state CHAR(2))
INHERITS (Cities); // if more than 1 table listed, attributes are unioned/merged, and error is returned if problem with incompatible types of attributes named the same
SELECT C.name, C.population FROM Cities C WHERE C.population>1000000 // executed over both Cities and Capitals (i.e., over Cities and all its descendants) … FROM ONLY Cities …
// executed over Cities that are not Capitals
• Problem: PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints not inherited
• Problem: tuples are inserted exactly in table mentioned, not “propagated” to supertables
41
Serious Limitations of PostgreSQL Inheritance
• PRIMARYKEYandUNIQUEconstraintsapplytoonlyone table, not to their inherited children
• SoifPRIMARYKEY(name)isspecifiedinCitiestable,nothing prevents a “duplicate” city –with conflicting values for other inherited attributes— from appearing in subtable Capitals
Different from behavior in SQL standard, where tuples inserted in Capitals get automatically propagated to Cities And duplicate tuples would appear by default on queries over Cities that do not say FROM ONLY Cities!
• FOREIGNKEYconstraintsalsoapplytoonlyonetable,not to their inherited children
Foreign keys from other tables onto Cities (and Capitals) are then a problem: FOREIGN KEY … REFERENCES Cities(name) would not capture or include Capitals
42
Project 2
You will expand your project by adding three out of: • TEXT
• ARRAY
• Composite TYPE
• TRIGGER
43
Overview of Storage and Indexing
2
Data on External Storage
• Disks: Unit for reading and writing data is a “page” or “block”
Reading several physically consecutive pages/blocks is much cheaper than reading them in random order
3
Data on External Storage
• Disks: Unit for reading and writing data is a “page” or “block”
Reading several physically consecutive pages/blocks is much cheaper than reading them in random order
• File organization: Method for arranging a file of records (such as tuples) on external storage
• Can think of each relation as stored in a dedicated file
• A “record id”—or “rid”—is sufficient to physically locate a record on storage
• An “index” allows us to identify efficiently which records have a given value of the “search key” for the index
4