Database Systems Course Review.
Introduction
Aspects of studying (questions to answer):
- Modeling and design of databases.
(How is the information structured?) - Programming: queries and DB operations like update.
(How does one express queries and other operations on the database?) - DBMS implementation.
(How to build a DBMS?)
Relational Database Modeling
The Relational Model of Data
- Data Model = Structure of the data + Operations on data + Constraints
- Schemas
Relation schema = relation name + attribute list
Database = collection of relations
Database schema = set of all relation schemas in the database - Relation instances
is current set of rows for a relation schema - Key of Relations
- Three kinds of table
- Stored relations
real, stored - Views
relations defined by a computation, virtual, not really exists - Temporary tables
constructed by SQL processor, thrown away, not stored
- Stored relations
- Table Declarations
- Most common types
- INT / INTEGER
- REAL / FLOAT
- CHAR(n)
- VARCHAR(n)
- BOOLEAN: true, false, unknown
- DATE
- TIME
- NULL , NOT NULL , DEFAULT value
- PRIMARY KEY , UNIQUE
There can be only one PRIMARY KEY for a relation, but several UNIQUE attributes.
No attribute of a PRIMARY KEY can ever be NULL in any tuple. But attributes declared UNIQUE may have NULL’s and there may be several tuples with NULL.
- Most common types
- Core Relational Algebra:
- Union, intersection, difference
Usual set operations, but both operands must have the same relation schema. - Selection
Picking certain rows - Projection
Picking certain columns - Products and joins
Compositions of relations- Theta Join
- Natural Join
- Renaming
of relations and attributes
- Union, intersection, difference
- Relational Algebra on Bags
A bag (or multiset) is like a set, but an element may appear more than once.- Operations on Bags
- Constraints on Relations
- The ability to restrict the data that may be stored in a database.
- Relational algebra: used as a constraint language abstractly.
