Database Systems

Database, DBMS and database system, problems with plain file storage (Integrity, Management, Durability), the 4 characteristics and 5 advantages of DB systems, the 3 levels of data models (Conceptual, Representational, Physical), the three-schema architecture and mapping, logical and physical data independence, the DB design process, advantages of the relational model, numbers to memorize

The COMPANY ER diagram, relational schema and tables compared as three layers, ER notation, kinds of entities and attributes, relationship type, degree, cardinality, participation, (min, max), recursive relationships and role names, weak entities, owners, identifying relationships and partial keys, relation schema, state, tuple, domain and NULL, superkey, key, primary key and foreign key, key constraint, entity integrity and referential integrity, violation checks

CREATE TABLE, types, constraints, ON DELETE/ON UPDATE, INSERT, DELETE and UPDATE, SELECT, aliases, DISTINCT, ORDER BY and set operations, nested queries with IN, ALL, ANY and EXISTS, JOIN and OUTER JOIN, aggregate functions, GROUP BY and HAVING, ASSERTION and TRIGGER, VIEW and view updates, a table mapping question wording to syntax

Query modification, view materialization, incremental update, immediate, lazy and periodic view update timing

σ SELECT, π PROJECT, ρ RENAME, set operations ∪, ∩ and −, CARTESIAN PRODUCT, THETA, EQUI and NATURAL JOIN, OUTER JOIN, aggregation ℱ, DIVISION ÷, the complete set, query trees, SQL compared with relational algebra, a table mapping question wording to symbols