Course detail

Database Systems

FIT-IDSAcad. year: 2019/2020

Fundamentals of database systems (DBS). Conceptual Modelling. The relational model. Relational database design from a conceptual model. Normalization-based design of a relational database. SQL language. Transaction processing. DBS architectures: client/server, multi-tier architectures, distributed DBS. Introduction to database administration: data security and integrity, introduction to physical database design, performance optimization, database recovery, concurrency control. Trends in database technology. The project consists in designing the structure of a relational database and programming SQL scripts to create the database, querying data in it, database triggers, and stored procedures.

Language of instruction

Czech, English

Number of ECTS credits

Mode of study

Not applicable.

Guarantor

doc. Ing. Jaroslav Zendulka, CSc.

Department

Department of Information Systems (UIFS)

Learning outcomes of the course unit

  • He/she knows fundamentals of the relational model of data and the theory of relational database design.
  • Student is able to develop conceptual models of an application domain for database applications and to transform it to relational database schema.
  • He/she knows the standard database language for relational databases SQL.
  • He/she receives basic competencies for database administrator's work like user account creation, access rights assignment and performance tuning.
  • Student acquaints with fundamentals of some important functions of advanced database system like transactional processing, concurrency and recovery.
  • Student acquaints with basic English terminology in the subject.

The student will learn how to analyze a given problem in a small team and he/she will learn to design and implement a solution of the problem individually. He/she learns to present and defend  both partial and final results of the project.

Prerequisites

The sets, relations and mappings. The elementary notions of the graph theory. Basics of hashing and tree-based search. Basic steps of software development. Rudiments of programming and data modelling.

Co-requisites

Not applicable.

Planned learning activities and teaching methods

Not applicable.

Assesment methods and criteria linked to learning outcomes

  • Data and use case models - 5 points
  • Building of the database in SQL - 5 points 
  • Querying in SQL - 5 points
  • Advanced database objects and documentation - 19 points
  • Midterm written exam - 15 points
  • Final written examination - 51 points
  • To be allowed to sit for written examination student is  to present and defend project oucomes in due dates, and to earn at least 24 points during the semester.
  • The minimum number of points for the final examination is 20.

Exam prerequisites:
To be allowed to sit for written examination student is present and defend project outcomes, and to earn at least 24 points during the semester.

Course curriculum

Not applicable.

Work placements

Not applicable.

Aims

Mastering fundamentals of relational database theory and skill in using database technology at a level required for database design, development of database applications and database administration.

Specification of controlled education, way of implementation and compensation for absences

Mid-term exam passing, realization and presentation/defence of projects in due dates. In the case of a reported barrier of the mid-term exam and to present/defend a project, a student will be offered a substitute date of the exam or an oral exam, and she/he will be allowed to present/defend the project in a substitute date, respectively.

Recommended optional programme components

Not applicable.

Prerequisites and corequisites

Basic literature

Silberschatz, A., Korth H.F, Sudarshan, S.: Database System Concepts. Sixth Edition. McGraw-Hill. 2010, 1320 p.

Recommended reading

Lemahieu, W., Broucke, S., Baesens, B.: Principles of Database Management. Cambridge University Press. 2018, 780 p.

Classification of course in study plans

  • Programme BIT Bachelor's 2 year of study, summer semester, compulsory

  • Programme IT-BC-3 Bachelor's

    branch BIT , 2 year of study, summer semester, compulsory

Type of course unit

 

Lecture

39 hod., optionally

Teacher / Lecturer

doc. Ing. Jaroslav Zendulka, CSc.
Ing. Vladimír Bartík, Ph.D.
RNDr. Marek Rychlý, Ph.D.

Syllabus

  1. Fundamental concepts of database systems.
  2. Conceptual modelling.
  3. Fundamentals of the relational model. Transformation of a conceptual model to a relational database schema.
    Introduction to Oracle database, tools for database management. 
  4. Introduction to normal forms, the use of normalization in database design.
  5. The SQL - data definition.
  6. The SQL - SELECT statement (fundamentals).
  7. The SQL - SELECT statement (extension).
  8. The SQL - other statements for data manipulation. System catalogue.
    Introduction to PL/SQL.
  9. The SQL - views, missing information, embedded SQL, cursor, dynamic SQL. Query by example (QBE). 
  10. The client/server architecture. Database triggers and stored procedures. Data integrity, data security.
  11. Data organization at the internal level - indexing and hashing. Query processing and optimization.
  12. Transaction processing - properties and states of  a database transaction. Introduction to failure recovery and concurrency control. Query optimization, indexes and database transactions in the Oracle database. 
  13. Current trends in database technology.

Project

13 hod., compulsory

Teacher / Lecturer

RNDr. Marek Rychlý, Ph.D.
Ing. Vladimír Bartík, Ph.D.

Syllabus

  1. Presentation of a conceptual model (ERD or a class diagram) and a use case model for a given problem (continuation of the project started in the subject Introduction to software engineering).
  2. An SQL script that creates and populates database tables.
  3. An SQL script with queries over the database tables.
  4. An SQL script with statements for advanced database objects and project documentation.