CZ2007 Database

CZ2007 Database

1. SQL

   • CONSTRAINTS
     • Key
       • Determine all attibutes of relation in the schema
       • Primary Key
         • single attribute
           • PRIMARY KEY (after declaration of attribute)
         • multiple attribute
           • declared separately
           • PRIMARY KEY (key1, key2);
       • Unique
         • single attribute
         • UNIQUE (after declaration of attribute)
       • determine all attributes of the schema
     • Foreign Keys / Referential Integrity
       • FOREIGN KEY primary_key REFERENCES <RELATION>(attribute)
       • Referenced attributes must be declared as PRIMARY KEY
       • Policy
         • set NULL
         • CASCADE
         • ON [UPDATE, DELETE] [SET NULL, CASCADE]
     • Value based constraints
       • CHECK (<conditions>) (after declaration of attribute)
     • Tuple-based constraints
     • ASSERTIONS
     • TRIGGER
       • ECA
         • Event
           • A type of database modification
           • INSERT
             • new tuple or set of tuples
           • UPDATE
             • old tuple
             • new tuple
           • DELETE
             • old tuple or old table
         • Condition: specified in WHEN clause
         • Action
           • Database modification
           • DELETE
           • UPDATE
           • INSERT
           • ROLLBACK
       • Execution
         • BEFORE
         • AFTER
         • INSTEAD OF
           • for views
       • Granularity
         • FOR EACH ROW
         • FOR EACH STATEMENT
       • REFERENCING
         • OLD ROW AS
         • NEW ROW AS
         • OLD TABLE AS
         • NEW TABLE AS
   • SUBQUERIES
     • scalar
       • single value
     • row
       • single row
     • table
       • multiple rows
       • operators
         • IN
           • is a member?
         • ALL
           • check every tuple
         • ANY/SOME
           • at least one
     • uncorrelated
     • correlated
       • EXISTS
       • UNIQUE
     • clause
       • FROM
       • WHERE
     • combining queries
       • UNION
       • INTERSECT
       • EXCEPT
   • SYNTAX + OPERATIONS
     • Complex Condition
       • OR
       • AND
       • EXCEPT
       • IN
     • Basic Structure
       • SELECT
         • INTO
         • mathematical expressions
         • *
       • FROM
       • WHERE
     • NULL VALUES
       • IS NULL
       • IS NOT NULL
       • missing or inapplicable
       • Three value logic
     • Ordering
       • ORDER BY
         • ASC
         • DESC
     • Multi-queries
       • <relation>.<attribute>
       • tuple variables: AS
   • Data Manipulation Language
     • perform queries
     • perform updates
   • Data Definition Language
     • create tables, database, indices
     • create views
     • specify authorizations
     • specify integrity constraints

2. E/R

   • Entity Sets
     • Collection of entities
     • Weak Entity Set
     • must be nonkey attribute
     • the many in relationship
   • Attributes
   • Relationships
     • Types
       • Many-one
       • One-one
         • ARROW for one
       • Many-many
     • Multiway
     • Relationship Attribute
     • Roles
     • LINE
   • Subclass
     • has more properties
     • fewer entities
     • isa triangles point to superclass
     • one-one, no need for arrow
   • CONSTRAINTS
     • Keys
       • no two entities agree on all attributes of key
       • UNDERLINE
     • Single-value
       • Many to One
     • Referential Integrity
       • ROUNDED ARROW
     • Domain constraints
     • General constraints

3. NORMALISATION

   • based on analysis of FDs
   • Functional Dependencies
     • is an assertion
     • Armstrong's Axiom
       • Transitivity
       • Reflexivity
       • Augmentation
     • Properties
       • Key
       • Trivial
       • Splitting
       • Combining

4. XML

   • DTD
   • QUERIES
   • SYNTAX
   • XPATH

5. RELATIONAL SCHEMA

   • Entity Set -> relation
   • Relationships -> relations
   • combining relations
   • subclass hierarchy
   • Weak Entity Set -> Relation
   • PITFALLS
     • Storage redudancy
     • Update anomaly
     • Insertion anomaly
     • Deletion anomaly

6. RELATIONAL ALGEBRA

   • Operation on Bags
   • Extended Relational Algebra
     • Duplicate elimination
     • Sorting
     • Extended Projection
       • arithmetic operation
     • Aggregation
       • NOT operators
       • apply entire columns of tables and produce SINGLE result
       • SUM
       • AVG
       • COUNT
       • MIN/MAX
       • GROUPING
     • outer join