Database systems (E-DBS) - Sylabus

Jaroslava Mikulecká 

Last updated: 06/11/2006

Goal:

After passing this course, students should understand the theoretical fundamentals of relational database systems and should  be able to design and implement simple database applicatios using ACCESS and MS SQL server technology.

Textbook:

T.Connolly, C.Begg:  Database Systems. A practical Approach to Design, Implementation, and Management, Addison Wesley,2005, ISBN 0321210255

Topics:

1. Introduction to Databases and Database Environment

Examples of common uses of database systems, the typical functions of a DBMS, the history of the development of DBMSs and the advantages and disadvantages of DBMSs. The three-level ANSI-SPARC architecture for a database system,  introduction to  different data models and the importance of conceptual modeling.

1. Entity-Relationship Modeling

Introduction to the Entity–Relationship (ER) model, a high-level conceptual data model developed by Chen (1976), basic concepts associated with the ER model.  Identification of potential problems associated with the development of an ER model.

2. Enhanced Entity-Relationship Modeling and UML  Class diagram

Demonstration of the  inherent problems associated with representing complex applications using the concepts of the ER model. Additional concepts that have been added to the original ER model resulting in development of the Enhanced Entity–Relationship (EER) model, namely specialization/generalization, aggregation, and composition. Introduction to  a diagrammatic technique for displaying specialization/generalization, aggregation, and composition in an EER diagram using the Unified Modeling Language (UML).

3. Normalization

The purpose of normalization, the concept of functional dependency, identification of functional dependencies, the definition of 1NF, 2NF and 3NF, the steps leading to the 3.NF.

4. The Relational Model  

The terminology and concepts of the relational model, which is now the dominant system for business applications. Tables as representatives of data, the connection between mathematical relations and relations in relational model. Candidate, primary and foreign key, entity integrity, referential integrity. Views.

5. Relational Algebra

Introduction to  the theoretical languages of the relational model, relational completeness, Relational operators: selection, projection, union, cartesian product, theta join, natural join, outer join. Relational algebra as query language.

6. Query-By-Example

An overview of the major features of the query-by-example (QBE) facility of the Microsoft Office Access database management system (DBMS).

7. SQL: Data Manipulation

Introduction the data manipulation facilities in the SQL standard:.SELECT, INSERT, UPDATE, and DELETE.

8. SQL: Data Definition

The main data definition facilities in the SQL standard: the SQL data types, the SQL Integrity Enhancement Feature (IEF), the main data definition statements,  views and the access control statements GRANT and REVOKE.

Practicals:

Application development in MS ACCESS:

a. Available wizzards

b. Creating of tables

c. Creating and using of forms and  reports

d. Creating of user interface

SQL queries in MS SQL server.