| Week | Content | |
|---|---|---|
| W01 | Introduction I: Syllabus & Introduction to Databases » Readings: Chapter 1.1-1.3, Elmasri-Navathe 7ED Content: Course Objectives and Syllabus, Database Types and Applications, Basic Definitions, Basic Operations in a Databasem, A Database Example, Basic Advantages of using a Databases (as opposed to Files) |
01 syllabus |
| Introduction II: Database Users and Database System Concepts and Architecture » Readings: Chapter 1.4-1.9, 2.1, Elmasri-Navathe 7ED Content: Database Users and Responsibilities, More Advantages of Using the DBMS Approach, A Brief History of Database Applications, When Not to Use a DBMS, Data Models, Schemas, and Instances |
02 | |
| W02 | Introduction III: Database System Concepts and Architecture » Readings: Chapter 2.2-2.7, Elmasri-Navathe 7ED Content: Three-Schema Architecture and Data Independence, Database Languages and Interfaces, The Database System Environment, Centralized and Client/Server Architectures for DBMSs, Classification of Database Management Systems |
03 |
| Entity-Relationship Model I: Data Modeling Using the Entity-Relationship (ER) Model » Readings: Chapter 3-3.3, Elmasri-Navathe 7ED Content: Using High-Level Conceptual Data Models for Database Design, An Example Database Application, Entity Types, Entity Sets, Attributes, and Keys |
04 | |
| W03 | Entity-Relationship Model II: Data Modeling Using the Entity-Relationship (ER) Model » Readings: Chapter 3.4-3.7, Elmasri-Navathe 7ED Content: Relationship Types, Relationship Sets, Roles, and Structural Constraints, Weak Entity Types, Refining the ER Design for the COMPANY Database, ER Diagrams, Naming Conventions, and Design Issues |
05 |
| Entity-Relationship Model III: ER + The Enhanced Entity-Relationship (EER) Model » Assignment 1 (Entity-Relationship Model) Announced Readings: Chapter 3.8, Appendix A and 4.1-4.4, Elmasri-Navathe 7ED Content: Relationship Types of Degree Higher Than Two, Designing Tools, Enhanced Entity-Relationship (Subclasses, Superclasses, and Inheritance, Specialization and Generalization, Constraints and Characteristics of Specialization and Generalization Hierarchies, Modeling of UNION Types Using Categories, An Example UNIVERSITY EER Schema) |
06 | |
| W04 | Relational Model I » Readings: Chapter 5.1-5.2, Elmasri-Navathe 7ED Content: Introduction to the Relational Model, Definitions (Relation, Key, Relational Schema, Domain, Tuple, Attribute, Relation State, Cardinality) and Examples, Characteristics of the Relational Model (Ordering of Tuples, Ordering of Attributes, Values and NULL Values), Introduction to Relational Constraints (Key, Entity, Referential Integrity, Domain) |
07 |
| Relational Model II + Relational Algebra I » Assignment 1 (Entity-Relationship Model) Due Project Phase 1 (Entity-Relationship & Relational Model) Announcement (in Laboratory) Readings: Chapter 5.2-5.3 and 6.1, Elmasri-Navathe 7ED Content: Relational Constraints (Key, Entity, Referential Integrity, Domain), Update Operations, Transactions, and Dealing with Constraint Violations. Unary Relational Operations: SELECT (σ) and PROJECT (π) |
08 | |
| W05 | Relational Algebra II » Readings: Chapter 8.2-8.3, Elmasri-Navathe 7ED Content: Unary Relational Operations: RENAME (ρ), Relational Algebra Operations from Set Theory (UNION, INTERSECTION, MINUS, SYMMETRIC DIFFERENCE), Binary Relational Operations: JOIN |
09 |
| W06 | Relational Algebra III » Assignment 2 (Relational Model & Relational Algebra) Announced Readings: Chapter 8.4-8.5, Elmasri-Navathe 7ED Content: Binary Relational Operations (DIVISION), Additional Relational Operations (Aggregate Functions, Generalized Projection, Grouping, Recursive Closure, Outer Join), Examples of Queries in Relational Algebra with University and Sailors-Reserve-Boats Example |
10 |
| ER-to-Relational Mapping » Readings: Chapter 4, Elmasri-Navathe 7ED Content: ER-to-Relational Mapping Algorithm (Mapping of: Regular Entity Types, Weak Entity Types, Binary 1:1, 1:N and M:N Relation Types, Multivalued attributes, N-ary Relationship Types, ER-to-Relational Mapping Algorithm (Options for Mapping Specialization or Generalization, Mapping of Union Types (Categories)). |
11 | |
| W07 | SQL-DDL I: Introduction to SQL-DDL Data Types » Project Phase 1 (Entity-Relationship & Relational Model) Due (in Laboratory) Project Phase 2 (Implementation) Begins (in Laboratory) Readings: Chapter 6.1, Elmasri-Navathe 7ED + "Transact-SQL Reference" Content: Introduction to SQL-DDL, SQL 1999 Data types (Numeric, Char, BLOB, CLOB, Bit, Boolean, Date, Time, Timestamp, etc) |
12 |
| SQL-DDL II » Readings: Chapter 6.2-6.3, Elmasri-Navathe 7ED + "Transact-SQL Reference" Content: SQL Server's TSQL: Date Functions (DATEDIFF, DATEADD, DATEPART, ISDATE), IDENTITY data type, Computed attribute, CREATE TYPE, CREATE/DROP/TRUNCATE/DELETE/ALTER, Database Schema, Constraints (PRIMARY KEY, UNIQUE, FOREIGN KEY, NOT NULL, CHECK), Integrity Constraints Actions: ON DELETE | ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }, Temporarily Disabling Constraints: WITH CHECK / WITH NO CHECK |
13 | |
| W08 | SQL-DML I » Assignment 2 (Relational Model & Relational Algebra) Due Readings: Chapter 6.4, Elmasri-Navathe 7ED + "Transact-SQL Reference" Content: Introduction to SQL-DML, SQL vs. Relational Algebra, Simple SELECT-FROM-WHERE Queries, JOIN and Cartesian Product, DISTINCT, Set Operations (UNION [ALL], INTERSECT, EXCEPT) |
14 |
| MIDTERM Exam: Check Lecture 1 for exact date. This is a closed book exam: no books, notebooks, notes, etc. |
- | |
| W09 | SQL-DML II » Readings: Chapter 6.4-6.5.4, Elmasri-Navathe 7ED + "Transact-SQL Reference" Content: Ordering of Results (ORDER-BY), Ranking Results: TOP K (TSQL) / LIMIT K, Pattern Matching with LIKE and CONTAINS (TSQL), COLLATE (sort and compare strings according regional conventions), Comparisons Involving NULLS (IS NULL, ISNULL/TSQL and COALESCE/SQL), Nested Queries (Correlated and Uncorrelated) and Set/Multiset Comparisons (EXIST, IN, op-ALL, op-ANY), DIVISION (using NOT EXISTS ... EXCEPT) |
15 |
| SQL-DML III » Readings: Chapter 6.5.6-6.5.7, Elmasri-Navathe 7ED + "Transact-SQL Reference" Content: Renaming with AS, Advanced Joins (NATURAL, INNER JOIN, OUTER JOIN (LEFT, RIGHT, FULL), CROSS JOIN), Aggregate Functions in SQL (COUNT, MAX, MIN, AVG, SUM), Characteristics of COUNT in TSQL (DISTINCT, ALL, *). |
16 | |
| W10 | SQL-DML IV » Assignment 3 (Structured Query Language (SQL)) Announced Readings: Chapter 6.5.8-6.5.9, Elmasri-Navathe 7ED + "Transact-SQL Reference" Content: Conceptual Query Execution Plan Explanation, GROUP-BY, HAVING in SQL, Insert/Delete/Update Operations in SQL, BULK INSERT in TSQL, SQL Data Control Language (SQL-DCL) |
17 |
| Advanced SQL: Internal Database Programming » Readings: Chapter 6.7-6.8 and 7.1-7.4, Elmasri-Navathe 7ED + "Transact-SQL Reference" Content: Views (CREATE/DROP/ALTER VIEW), Assertions and Triggers (CREATE ASSERTION, CREATE TRIGGER), BEFORE|INSTEAD OF Triggers, Examples with input validation, logging table state / versioning (INSERTED, DELETED tables), |
18 | |
| W11 | Advanced SQL: Internal Database Programming » Assignment 3 (Structured Query Language (SQL)) Due Readings: Chapter 10, Elmasri-Navathe 7ED + "Transact-SQL Reference" Content: Programming inside the Database: Introductory (SET, DECLARE, USE, SELECT, GO,IF..ELSE, WHILE, CASE), Scripts and Batches, Dynamic SQL and EXEC. |
19 |
| Advanced SQL: External Database Programming » Readings: Chapter 10, Elmasri-Navathe 7ED + "Transact-SQL Reference" Content: Stored Procedures (SPROCs) and SET NOCOUNT, Advantages and Disadvantages of Stored Procedures, SQL Injection Attacks (Definitions, Types, Examples, Handling in TSQL, Relevant Functions QUOTENAME, REPLACE), SPROCs in JAVA (CallableStatement), Transactions, SQL Transaction Control Language (SQL-TCL), Indexes (Basic, Clustered), User Defined Functions (UDFs), Temporary Tables with: #local, ##global, view, tables, Temporary Result Sets: Common Table Expressions (CTE), Αναδρομικά CTE, Caching CTEs, Storing Passwords in Databases (TSQL HASHBYTES), System Catalogs TSQL (sys.foreign_keys, sys.tables) and ANSI (INFORMATION_SCHEMA.TABLES), Unit Testing SQL (Generate Random Data), External DB Programming: Bridges (ODBC, JDBC, SqlSrv), Architecture (HTML, HTTP, Web Browser) |
20 | |
| W12 | Functional Dependencies and Normalization I » Readings: Chapter 14.1, Elmasri-Navathe 7ED Content: Introduction to Normalization, Informal Design Guidelines for Relational Schemas: i) Semantics of Relation and Attribute Names, ii) Redundant Information in Tuples and Update Anomalies (Insert, Update, Delete), iii) NULLs in Tuples and iv) Spurious Tuples. |
21 |
| Functional Dependencies and Normalization II » Readings: Chapter 14.2, Elmasri-Navathe 7ED Content: Introduction to Functional Dependencies (FD): Motivation and Definitions, Inference Rules for FD, Armstrong Axioms and FD Proofs, Additional Definitions for FDs: FD Closure F+, Attribute Closure X+, Equivalence of two sets of FDs (F+=G+), Cover of an FD set, Minimal Cover for an FD Set. |
22 | |
| W13 | Functional Dependencies and Normalization III » Assignment 4 (Functional Dependencies and Normal Forms) Announced Readings: Chapter 14.3-14.4, Elmasri-Navathe 7ED Content: Overview of Normalization and Normal Forms, Definitions: Prime and Non-Prime Attribute, Partial and Full Functional Dependencies, Normal Forms (1NF, 2NF and 3NF): Motivation, Examples, Decomposition. |
23 |
| Functional Dependencies and Normalization IV » Project Phase 2 (Implementation) Due (in Laboratory) Readings: Chapter 14.5, Elmasri-Navathe 7ED Content: Normal Forms (BCNF): Motivation, Examples, Decomposition, Introduction to EPL446 (Advanced Database Systems): Syllabus and Other Details |
24 | |
| Exams | FINAL Exam This is a closed book exam: no books, notebooks, notes, etc. allowed |
- |