EPL342: Databases
by Demetris Zeinalipour
Department of Computer Science
University of Cyprus

Quick Links

WebMail
CS Courses

EPL342: Schedule

 

Week

Description PDF
1 Introduction I: Syllabus & Introduction to Databases (Chapter 1.1-1.3, Elmasri-Navathe 5ED)

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)
Syllabus

Lecture 1

  Introduction II: Database Users and Database System Concepts and Architecture (Chapter 1.4-1.9, 2.1, Elmasri-Navathe 5ED)

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

Lecture 2

2 Introduction III: Database System Concepts and Architecture (Chapter 2.2-2.7, Elmasri-Navathe 5ED)

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


Lecture 3


Entity-Relationship Model I: Data Modeling Using the Entity-Relationship (ER) Model (Chapter 3-3.3, Elmasri-Navathe 5ED)

Using High-Level Conceptual Data Models for Database Design,  An Example Database Application,  Entity Types, Entity Sets, Attributes, and Keys


Lecture 4

 3 Entity-Relationship Model II: Data Modeling Using the Entity-Relationship (ER) Model (Chapter 3.4-3.7, Elmasri-Navathe 5ED)

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, 

Lecture 5


Entity-Relationship Model III: ER + The Enhanced Entity-Relationship (EER) Model (Chapter 3.8, Appendix A and 4.1-4.4, Elmasri-Navathe 5ED)

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)

Exercise 1 (ER) Announcement (Wednesday in Recitation)


Lecture 6


Ex1

Relational Model I: (Chapter 5.1-5.2, Elmasri-Navathe 5ED)

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)

Exercise 1 (ER) Due Date (Wednesday in Recitation)
Project Phase 1 (ER) Announcement (Thursday in Laboratory)

Lecture 7

Pro1


 October 1st - Cyprus Independence Day---
5
Relational Model II + Relational Algebra I (Chapter 5.2-5.3 and 6.1, Elmasri-Navathe 5ED)

Relational Constraints (Key, Entity, Referential Integrity, Domain), Update Operations, Transactions, and Dealing with Constraint Violations
Unary Relational Operations: SELECT (ó) and PROJECT (ð)

Lecture 8

  Relational Algebra II (Chapter 6.2-6.3, Elmasri-Navathe 5ED)

Unary Relational Operations: RENAME (ñ), Relational Algebra Operations from Set Theory (UNION, INTERSECTION, MINUS, SYMMETRIC DIFFERENCE), Binary Relational Operations: JOIN

Lecture 9
6 Relational Algebra III (Chapter 6.4-6.5, Elmasri-Navathe 5ED)

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

Exercise 2 (RM+RA) Announcement (Wednesday after Recitation)


Lecture 10

Ex2

  ER-to-Relational Mapping (Chapter 7, Elmasri-Navathe 5ED)

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)).

Lecture 11

7
SQL-DDL I: Introduction to SQL-DDL Data Types (Chapter 8.1, Elmasri-Navathe 5ED + ÔransactSQL Reference Guide)
* ÔransactSQL Reference Guide:  http://msdn.microsoft.com/en-us/library/bb510741.aspx

Finish Lecture 11 (EER-to-Relational)

Introduction to SQL-DDL, SQL 1999 Data types (Numeric, Char, BLOB, CLOB, Bit, Boolean, Date, Time, Timestamp, etc),

Exercise 2 (RM+RA) Due Date (Wednesday in Recitation)

Lecture 12


SQL-DDL II (Chapter 8.2-8.3, Elmasri-Navathe 5ED + ÔransactSQL Reference Guide)
* ÔransactSQL Reference Guide:  http://msdn.microsoft.com/en-us/library/bb510741.aspx

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


Lecture 13

8 SQL-DML I (Chapter 8.4, Elmasri-Navathe 5ED)

Introduction to SQL-DML, SQL vs. Relational Algebra, Simple SELECT-FROM-WHERE Queries, JOIN and Cartesian Product, DISTINCT, Set Operations (UNION [ALL], INTERSECT, EXCEPT)

Lecture 14


SQL-DML II (Chapter 8.4-8.5.4, Elmasri-Navathe 5ED)

Ordering of Results (ORDER-BY), Pattern Matching with LIKE and CONTAINS (TSQL), Comparisons Involving NULLS (IS NULL), Nested Queries (Correlated and Uncorrelated) and Set/Multiset Comparisons (EXIST, IN, op-ALL, op-ANY), DIVISION (using NOT EXISTS ... EXCEPT)

Project Phase 1 (ER) Due Date (Wednesday in Recitation)
Project Phase 2 (ER2RM) Begins (Wednesday in Recitation)
Lecture 15
9
 MIDTERM - Tuesday 02/11/10
Room #109: A-M (57 persons)
Room #101: N-X (38 persons)
This is a closed book exam: no books, notebooks, notes, etc. allowed

  SQL-DML III (Chapter 8.5.6-8.5.7, Elmasri-Navathe 5ED + ÔransactSQL Reference Guide)
* ÔransactSQL Reference Guide:  http://msdn.microsoft.com/en-us/library/bb510741.aspx

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, *)

Lecture 16

10 SQL-DML IV (Chapter 8.5.8-8.5.9, Elmasri-Navathe 5ED + ÔransactSQL Reference Guide)
* ÔransactSQL Reference Guide:  http://msdn.microsoft.com/en-us/library/bb510741.aspx

Conceptual Query Execution Plan Explanation, GROUP-BY, HAVING in SQL, Insert/Delete/Update Operations in SQL, BULK INSERT in TSQL.

Exercise 3 (SQL) Announcement (Wednesday in Recitation)

Lecture 17

Ex3


Advanced SQL: Internal Database Programming (Chapter 8.7-8.8, Elmasri-Navathe 5ED + ÔransactSQL Reference Guide)
* ÔransactSQL Reference Guide:  http://msdn.microsoft.com/en-us/library/bb510741.aspx

Views (CREATE/DROP/ALTER VIEW),  Assertions and Triggers (CREATE ASSERTION, CREATE TRIGGER),

Lecture 18

11 Advanced SQL: Internal Database Programming (Chapter 9.1, Elmasri-Navathe 5ED + ÔransactSQL Reference Guide)
* ÔransactSQL Reference Guide:  http://msdn.microsoft.com/en-us/library/bb510741.aspx

Programming inside the Database: Introductory (SET, DECLARE, USE, SELECT, GO
,IF..ELSE, WHILE, CASE), Scripts and Batches, Dynamic SQL and EXEC.

Exercise 3 (SQL) Due Date (Wednesday in Recitation)

Lecture 19

  Advanced SQL: External Database Programming (Chapter 9.2-9.4, Elmasri-Navathe 5ED + ÔransactSQL Reference Guide)
* ÔransactSQL Reference Guide:  http://msdn.microsoft.com/en-us/library/bb510741.aspx

Stored Procedures (SPROCs), Advantages and Disadvantages of Stored Procedures, User Defined Functions (UDFs), Programming Database Objects with High Level Languages (SQLCLR), Embedded SQL, DB Programming with Function Calls: SQL/CLI or JDBC

Please also check the material of Recitation 11: Connecting to SQLServer From Java with JDBC


Lecture 20

12 Functional Dependencies and Normalization I (Chapter 10.1, Elmasri-Navathe 5ED)

Finish Lecture 20

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.


KEDIMA Evaluation Questionnaires

Lecture 21

  Functional Dependencies and Normalization II (Chapter 10.2, Elmasri-Navathe 5ED)

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.


Lecture 22

13 Functional Dependencies and Normalization III (Chapter 10.3-10.4, Elmasri-Navathe 5ED)

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.

Exercise 4 (FD+NF) Announcement (Monday Online)


Lecture 23

Ex4

 

Functional Dependencies and Normalization IV (Chapter 10.5, Elmasri-Navathe 5ED)

Normal Forms (BCNF): Motivation, Examples, Decomposition, 
Introduction to EPL446 (Advanced Database Systems): Syllabus and Other Details

Project Phase 3 (Implementation) Due Date (Thursday in Laboratory) - Best Project Competition to be held with prizes from Microsoft!

Lecture 24

               
                Exercise 4 (FD+NF) Due Date (Wednesday, 6/12/2010)

FINAL EXAM

Day: Friday, 10 / 12 / 2010
Time: 8:30-11:30
Place1: ×ÙÄ01-108 (Á-Ë), New University Campus
Place2: ×ÙÄ01-107 (Ì-×), New University Campus

This is a closed book exam: no books, notebooks, notes, etc. allowed

 
 

 

Copyright © Department of Computer Science, University of Cyprus