1. Lecture 01: Introduction
1.1. Database
1.1.1. DBMS (Database Management System)
1.1.1.1. Microsoft Access
1.1.1.2. SQL Server
1.1.1.3. ORACLE
1.1.2. [Schema / Meta Data]
1.1.2.1. Relation / Database Table
1.1.2.1.1. [Fields / Attributes] Every Fields has: [Data Type / Domain]
1.1.2.1.2. [Rows / Records / Tuples]: Relation consists of tuples
1.1.3. [Data / DB Instance]
2. Lecture 02: Relational Model
2.1. Schema
2.1.1. Specification of how data is to be structured logically
2.1.2. Defined at set-up
2.1.3. Rarely changes
2.2. Instance
2.2.1. Content
2.2.2. Changes rapidly, but always conforms to the schema
2.3. Relational algebra operators
2.3.1. Core set of operators
2.3.1.1. σ Selection
2.3.1.1.1. Filter rows according to some criteria
2.3.1.2. π Projection
2.3.1.2.1. Select columns to output
2.3.1.3. R × S Cross product
2.3.1.3.1. Pairs rows from two tables
2.3.1.4. R ∪ S Union
2.3.1.4.1. R and S must have identical schema
2.3.1.4.2. Contains all rows in R and all rows in S, with duplicates eliminated
2.3.1.5. R − S Difference
2.3.1.5.1. R and S must have identical schema
2.3.1.5.2. Contains all rows in R that are not found in S
2.3.1.6. ρ S ( R ) Renaming
2.3.1.6.1. Rename a table and/or its columns
2.3.2. Derived operators
2.3.2.1. R ⋈ S Join
2.3.2.1.1. Relate rows from two tables according to some criteria
2.3.2.2. R ⋈ S Natural join
2.3.2.2.1. Relate rows from two tables
2.3.2.2.2. Enforce equality on all common attributes
2.3.2.2.3. Eliminate one copy of common attributes
2.3.2.3. R ∩ S Intersection
2.3.2.3.1. R and S must have identical schema
2.3.2.3.2. Contains all rows that are in both R and S
2.3.3. Monotone operators
2.3.3.1. Monotone
2.3.3.1.1. Selection: σ p ( R )
2.3.3.1.2. Projection: π L ( R )
2.3.3.1.3. Cross product: R × S
2.3.3.1.4. Join: R p S
2.3.3.1.5. Natural join: R S
2.3.3.1.6. Union: R ∪ S
2.3.3.1.7. Intersection: R ∩ S
2.3.3.2. Non-monotone
2.3.3.2.1. Difference: R − S
2.4. Lecture Notes
2.4.1. Why is “−” needed for highest GPA?
2.4.2. Why do we need core operator X?
2.4.3. Why is r.a. a good query language?
2.4.3.1. Simple: A small set of core operators who semantics are easy to grasp
2.4.3.2. Declarative: Yes, compared with older languages like CODASYL, But operators look “procedural”
2.4.3.3. Complete:
2.4.4. Relational algebra = “safe” relational calculus
2.4.5. Why not recursion?
2.4.5.1. Relational algebra has no recursion
2.4.5.2. Optimization becomes undecidable
2.4.5.3. You can always implement it at the application level
2.4.5.4. Recursion is added to SQL nevertheless
2.5. Relational algebra
3. Lecture 4: Relational Database Design Part 2
3.1. Design principles
3.1.1. KISS: Keep It Simple, Stupid
3.1.2. Avoid redundancy
3.1.2.1. Redundancy wastes space
3.1.2.2. complicates updates
3.1.2.3. inconsistency
3.1.3. common sense
3.2. double diamonds
3.3. Examples
3.3.1. LocalTrain ExpressTrain Stops
4. Lecture 03: Relational Database Design
4.1. Keys
4.1.1. K is a key for a relation
4.1.2. K is a “tuple identifier”
4.1.3. Usage of keys
4.1.3.1. More constraints on data, fewer mistakes
4.1.3.2. Look up a row by its key value
4.1.3.3. Pointers
4.1.3.3.1. Example: Enroll (SID, CID) SID is a key of Student • CID is a key of Course • An Enroll tuple “links” a Student tuple with a Course tuple
4.1.4. key of an entity set is represented by underlining all attributes in the key
4.2. Database design
4.2.1. Understand the real-world domain
4.2.2. Using a database design model
4.2.2.1. Entity/Relationship (E/R) model
4.2.2.1.1. very popular
4.2.2.1.2. “watered-down” object-oriented design model
4.2.2.1.3. represent designs
4.2.2.1.4. Primarily a design model not implemented by any major DBMS
4.2.2.2. Object Definition Language (ODL)
4.2.3. Translate specification to the data model of DBMS
4.2.3.1. Relational
4.2.3.2. XML
4.2.3.3. object-oriented
4.2.4. Create DBMS schema
4.2.5. E/R basics
4.2.5.1. Entity
4.2.5.1.1. Represented as a rectangle
4.2.5.1.2. Entity: a “thing,” like a record or an object
4.2.5.1.3. Entity set: a collection of things of the same type
4.2.5.2. Relation
4.2.5.2.1. Represented as a diamond
4.2.5.2.2. Relationship: an association among entities
4.2.5.2.3. each relationship is uniquely identified by the entities it connects
4.2.5.2.4. Multiplicity of relationships
4.2.5.2.5. Roles in relationships
4.2.5.2.6. Weak entity sets
4.2.5.2.7. ISA relationships
4.2.5.3. Attributes
4.2.5.3.1. Represented as ovals
4.2.5.3.2. Attributes: properties of entities or relationships
5. Lecture 5: Enhanced ER (EER) model
5.1. Advantages
5.1.1. design more accurate database schemas
5.1.2. data properties and constraints more precisely
5.1.3. complex requirements
5.2. Additional Modeling concepts
5.2.1. Subclasses
5.2.2. Superclasses
5.2.3. Inheritance
5.2.3.1. Subclass entity inherits all attributes and relationships of superclass
5.2.4. Specialization
5.2.4.1. Specialization: top-down refinement into (super)classes and subclasses
5.2.4.2. Generalization groups entity types; bottom up synthesis
5.2.4.3. Subclasses inherit the attributes and relationships of their superclasses
5.2.5. generalization
5.2.6. Category or union type
5.2.7. Attribute
5.2.8. relationship inheritance
5.2.9. Constraints
5.2.9.1. Disjointness
5.2.9.1.1. Disjoint (d): The subclasses must have disjoint sets of entities
5.2.9.1.2. Overlap (o): The subclasses may have overlapping sets of entities
5.2.9.2. Completeness
5.2.9.2.1. Partial: An entity may not belong to any of the subclasses (single-line)
5.2.9.2.2. Total: Every entity in the superclass must be a member of some subclass (double-edge)
5.2.9.3. Membership
5.2.9.3.1. Predicate defined subclasses
5.2.9.3.2. Attribute defined subclasses
5.2.9.3.3. User defined subclasses