IS511 Database

Get Started. It's Free
or sign up with your email address
Rocket clouds
IS511 Database by Mind Map: IS511 Database

1. Lecture 01: Introduction

1.1. Database

1.1.1. DBMS (Database Management System) Microsoft Access SQL Server ORACLE

1.1.2. [Schema / Meta Data] Relation / Database Table [Fields / Attributes] Every Fields has: [Data Type / Domain] [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 σ Selection Filter rows according to some criteria π Projection Select columns to output R × S Cross product Pairs rows from two tables R ∪ S Union R and S must have identical schema Contains all rows in R and all rows in S, with duplicates eliminated R − S Difference R and S must have identical schema Contains all rows in R that are not found in S ρ S ( R ) Renaming Rename a table and/or its columns

2.3.2. Derived operators R ⋈ S Join Relate rows from two tables according to some criteria R ⋈ S Natural join Relate rows from two tables Enforce equality on all common attributes Eliminate one copy of common attributes R ∩ S Intersection R and S must have identical schema Contains all rows that are in both R and S

2.3.3. Monotone operators Monotone Selection: σ p ( R ) Projection: π L ( R ) Cross product: R × S Join: R  p S Natural join: R  S Union: R ∪ S Intersection: R ∩ S Non-monotone 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? Simple: A small set of core operators who semantics are easy to grasp Declarative: Yes, compared with older languages like CODASYL, But operators look “procedural” Complete:

2.4.4. Relational algebra = “safe” relational calculus

2.4.5. Why not recursion? Relational algebra has no recursion Optimization becomes undecidable You can always implement it at the application level Recursion is added to SQL nevertheless

2.5. Relational algebra

3. Lecture 03: Relational Database Design

3.1. Keys

3.1.1. K is a key for a relation

3.1.2. K is a “tuple identifier”

3.1.3. Usage of keys More constraints on data, fewer mistakes Look up a row by its key value Pointers 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

3.1.4. key of an entity set is represented by underlining all attributes in the key

3.2. Database design

3.2.1. Understand the real-world domain

3.2.2. Using a database design model Entity/Relationship (E/R) model very popular “watered-down” object-oriented design model represent designs Primarily a design model not implemented by any major DBMS Object Definition Language (ODL)

3.2.3. Translate specification to the data model of DBMS Relational XML object-oriented

3.2.4. Create DBMS schema

3.2.5. E/R basics Entity Represented as a rectangle Entity: a “thing,” like a record or an object Entity set: a collection of things of the same type Relation Represented as a diamond Relationship: an association among entities each relationship is uniquely identified by the entities it connects Multiplicity of relationships Roles in relationships Weak entity sets ISA relationships Attributes Represented as ovals Attributes: properties of entities or relationships

4. Lecture 4: Relational Database Design Part 2

4.1. Design principles

4.1.1. KISS: Keep It Simple, Stupid

4.1.2. Avoid redundancy Redundancy wastes space complicates updates inconsistency

4.1.3. common sense

4.2. double diamonds

4.3. Examples

4.3.1. LocalTrain ExpressTrain Stops

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 Subclass entity inherits all attributes and relationships of superclass

5.2.4. Specialization Specialization: top-down refinement into (super)classes and subclasses Generalization groups entity types; bottom up synthesis 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 Disjointness Disjoint (d): The subclasses must have disjoint sets of entities Overlap (o): The subclasses may have overlapping sets of entities Completeness Partial: An entity may not belong to any of the subclasses (single-line) Total: Every entity in the superclass must be a member of some subclass (double-edge) Membership Predicate defined subclasses Attribute defined subclasses User defined subclasses