Content:
Data Models: the Physical and the Logical RDBMS Models, Entities and Relationships, the ER, EER, and other Data Models
Data Normalization: Normalization by Decomposition, Functional Dependencies, First (1NF), Second (2NF), and Third (3NF) Normal Forms. ER Model to RDBMS Schema, and RDBMS Schema to ER Model Mapping. Primary and Foreign Keys. Referential Data Integrity.
Relational Algebra: Data Organization and Processing in 2D Relations/Tables. Basic and Composite Relational Algebra Operators. User Queries and Relational Expressions.
Structured Query Language (SQL): The ANSI/ISO SQL Standard. Relational Database Schema Creation/Implemenation. Data Integrity Constraints. The CONSTRAINT clause. User Queries expressed as SQL command syntax. Inner and Outer Join operations. Division in SQL. Updatable and Not Updatable SQL Views. Recursive SQL syntax.
Stored Routines: Functions, Procedures, Triggers, and Assertions.
Description:
An introduction to the basics of data modeling and database schema design. Software used: IBM DB2 Express-C RDBMS, PostgreSQL, MySQL. Also: the DBTechNet (http://www.dbtechnet.org) DebianDB virtual machine with pre-installed all of the aforementioned software, plus the IBM Data Studio, Oracle Express, MySQL workbench, etc. The virtual machine is made available to the students for free downloading. In the Hands-on-Laboratory (HoL) part of the course, the students make extensive use of the tutorials and exercises of the SQLZoo environment (www.sqlzoo.net).
Structure:
The course consists of two components/course modules:
(a) Theory, and
(b) Laboratory hands-on practise
Evaluation:
(a) Theory course module: final exam (contributes 60% towards the overall course grade)
(b) Laboratory course module midterm exam (contribute 20% towards the overall course grade), plus it is taken to comprise an up to 20% bonus contribution to the final exam grade of the theory module
(c) Laboratory course module final exam (contributes 20% towards the overall course grade
Possible course enrollment for Erasmus students: project work with presentation and/or oral examination (in English)
By enrolling in this course, the student:
. Learns to apply the ER modeling approach
. Applies the normalization by decomposition methodology in the design of a data model
. Appreciates the use and the importance of data modeling
. Understands and reports on the use of data modeling software (e.g. the IBM Data Studio, DB Designer, etc.)
. Appreciates the importance of the ISO/ANSI SQL standard
. Learns to construct the SQL syntax to evaluate user queries
. Differentiates between the procedural and the non-procedural data processing
. Designs, models, and implements the relational database schems subject to given constraints and specifications
. Appreciates the use of and compiles/constructs SQL stored routines
. Appreciates the use of and compiles/constructs SQL views
. Explains the role and the importance of SQL indexes
. Appreciates the importance of SQL index maintenance procedures during the life cycle of the database application
Bibliography:
T. Connolly, C. Begg, Database Systems: A Practical Approach to design, Implementation, and Management, 5th Edition, Addison Wesley, 2010
R. Elmasri και S.B. Navathe, Fundamentals of Database Systems, 5th Edition, Addison-Wesley 2006
R. Ramakrishnan και J. Gehrke, Database Management Systems, 3rd Edition, Mc Graw-Hill, 2002
J.D. Ullman, J. Widom, A First Course in Database Systems, Prentice-Hall, 2007
