Data Modelling and Database Design

Ac.Year:ukončen 2004/2005
EI-BC-3VTB2nd Stage/1st YearCompulsory
EI-MSC-5VTI2nd Stage/1st YearCompulsory
Public info:http://www.fit.vutbr.cz/study/courses/DSI/public/
Completion:accreditation+exam (written)
Type of
Hour/semLecturesSem. ExercisesLab. exercisesComp. exercisesOther
Guarantee:Zendulka Jaroslav, doc. Ing., CSc., DIFS
Lecturer:Zendulka Jaroslav, doc. Ing., CSc., DIFS
Instructor:Bartík Vladimír, Ing., Ph.D., DIFS
Heckel Martin, Ing., DIFS
Ráb Jaroslav, Ing., DIFS
Faculty:Faculty of Information Technology BUT
Department:Department of Computer Systems FIT BUT
Software Engineering (PPS), DIFS
Information Systems Design (INS), DIFS
Learning objectives:
  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.
  Fundamentals of database systems (DBS). Relational database design from a conceptual model. SQL language. Fundamentals of the relational model. Normalization-based design of a relational database. Organization of data at an internal level. Data security and integrity. Introduction to query optimization. Transaction processing - database recovery, concurrency control. Introduction to DBS architectures: client/server, multi-tier architectures, distributed DBS. Introduction to postrelational DBS. Access to databases from WWW. Project - development of a database application in modern development and database environment.
Learning outcomes and competences:
  Students are able to develop conceptual models of an application domain for database applications and have knowledge of relational database management system fundamentals.
Syllabus of lectures:
  • Fundamental concepts of database systems. Introduction to products used for projectsin a laboratory (Oracle and Centura at present).
  • Conceptual modelling. Transformation of an ER diagram and a class diagram a relational database schema.
  • SQL language, data definition. SELECT statement (fundamentals).
  • SQL language: SELECT statement (extension), other statements for data manipulation. System catalogue.
  • SQL language: embedded SQL, cursor, dynamic SQL. Query by example (QBE).
  • The relational model: data structure, data integrity in the relational model, relational algebra, relational calculus.
  • Schema normalization: introduction to dependency theory, normal forms.
  • Data organization at the internal level, indexing, B+ tree, hashing.
  • Data security and integrity. Using declarative integrity constraints, stored procedures and triggers. Query optimization.
  • Transaction processing. Failure recovery.
  • Concurrency control - serialization, locking, timestamping, isolation levels of transactions. Transaction processing in SQL.
  • Client/server and multi-tier architectures. Introduction to distributed database systems: problems, data fragmentation and replication, distributed transaction processing.
  • Current trends in database technology: postrelational database systems (mainly object-oriented and object-relational), access to databases from WWW.
Syllabus of computer exercises:
  • Demonstration of a database application development in database and development environments of Oracle and Gupta products.
  • Creating an Oracle and SQL Base database.
  • Screen form development in Oracle Developer a SQL Windows.
  • Screen form development in Oracle Developer a SQL Windows.
  • Report development in Oracle Developer a SQL Windows
  • Stored procedures and database triggers in Oracle and SQL Base.
Syllabus - others, projects and individual work of students:
  • Presentation of a conceptual model (a class diagram and a use case diagram) from Software engineering modified with respect to the extent of realization.
  • Realization of the database application in one of the two development and databaze environments.
Fundamental literature:
  • Silberschatz, A., Korth ,H.F, Sudarshan, S.:Database System Concepts. Fourth Edition. McGRAW-HILL. 2001, 1064 p.
  • On-line help and documentation of Oracle and Gupta products.
Study literature:
  • Ramakrishnan, R.: Database Management Systems. WCB/McGraw-Hill, 1998, 741 p.
  • Date, C.J.: An Introduction to Database Systems. Sixth edition. Addison-Wesley, 1995, 839 p.
Controlled instruction:
  Mid-term exam passing, realization and presentation/defence of projects in due dates.
Progress assessment:
  Written mid-term exam, presentation of the first project and defence of the second one in due dates.
Exam prerequisites:
  Duty credit consists of mid-term exam passing, presentation of the first project and defence of the second one in due dates, and of obtaining at least 25 points for activities during semester (a mid-term exam, a project).