Transcript Essential Databases

Essential Querying
The simple truth (again)
Recap
• databases contain
– (large) volumes of data
– stored according to a rigid model
• with facilities for
– capturing
– querying
– maintaining consistency
The relational model
• (currently) most popular paradigm
– Oracle, DB2 etc
• other paradigms have largely similar
properties
– but differ in algebraic detail
The relational query model
• everything is a relation
– entities and relationships
• query model is based on three operations:
– select, project, join
– (this is a gross simplification for real databases)
• functional (or applicative) paradigm:
– each operation creates a new relation as its
result
select
• selects the tuples within a single relation to
those that match the predicate
• e.g. find all people whose name is “James”
select people where name = “James”
project
• projects the attributes of a single relation to
those stated
• e.g. give just the name and age of all people
project people onto name, age
join
• joins relations to create a new one, based on
an attribute-based predicate
• e.g. join people and addresses, where the
postcode is the same
join people to addresses on post_code
a query
• a query is a statement, written in a query
algebra, of some combination of these
operator
project(
select( join items to rooms on room_ID )
where category = “Electronic” and
room_loc = “upstairs”
onto description, cost
static knowledge
• each relation has a type
– based on attribute names and types
• the type of each operation’s results can be
determined statically
– depending on its parameter types
• thus a whole query can be checked for
soundness
– particularly important where nil results can
occur
views
• views are simply query statements that are
dynamically expressed
• can give different users different views of
the data
– e.g. suppress sensitive fields from some
contexts
efficiency
• composition is general, but looks inefficient
– typically joins occur in deep levels of nesting
• their mathematical properties mean that
they can be highly optimised
– wrt the database schema
– users need not worry about efficiency
– another use of static knowledge
real query algebras can also…
• perform more generalised evaluation
– e.g. arithmetic etc.
• perform updates to the database
• (relational) can’t perform generalised
computation
– no recursion