Title:

Data Modelling and Database Design

Code:DSI
Ac.Year:ukončen 2002/2003
Term:Winter
Study plans:
ProgramBranchYearDuty
EI-BC-3VTB2nd Stage/1st YearCompulsory
EI-MSC-3VTN1stCompulsory
EI-MSC-5VTI2nd Stage/1st YearCompulsory
Language:Czech, English
Public info:http://www.fit.vutbr.cz/study/courses/DSI/public/
Credits:6
Completion:examination (written)
Type of
instruction:
Hour/semLecturesSem. ExercisesLab. exercisesComp. exercisesOther
Hours:3960020
 ExaminationTestsExercisesLaboratoriesOther
Points:50150035
Guarantee:Zendulka Jaroslav, doc. Ing., CSc., DIFS
Lecturer:Zendulka Jaroslav, doc. Ing., CSc., DIFS
Faculty:Faculty of Information Technology BUT
Prerequisites: 
Software Engineering (PPS), DIFS
Follow-ups:
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.
Description:
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. 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:
  1. Fundamental concepts of database systems. Introduction to products used for projectsin a laboratory (Oracle and Centura at present).
  2. Conceptual modelling. Transformation of an ER diagram and a class diagram a relational database schema.
  3. SQL language - data definition, SELECT statement (fundamentals).
  4. SQL language - SELECT statement (extension), other statements for data manipulation. System catalogue.
  5. Introduction to programming in Oracle (Developer/2000 and PL/SQL language) and Centura Team Developer (SQL Windows and SAL language).
  6. SQL language - embedded SQL, cursor, dynamic SQL. Query by example (QBE).
  7. The relational model - data structure, data integrity in the relational model, relational algebra, relational calculus.
  8. Schema normalization - introduction to dependency theory, normal forms.
  9. Data organization at the internal level, indexing, B+ tree, hashing.
  10. Data security and integrity. Using declarative integrity constraints, stored procedures and triggers. Query optimization.
  11. Transaction processing. Failure recovery. Concurrency control - serialization, locking, timestamping, isolation levels of transactions. Transaction processing in SQL.
  12. Client/server and multi-tier architectures. Distributed databases - problems, data fragmentation and replication, distributed transaction processing.
  13. Current trends in database technology - postrelational database systems (mainly object-oriented and object-relational), access to databases from WWW.
Syllabus of computer exercises:
  1. Demonstration of a database application development in database and development environments of Oracle and Centura products.
  2. Creating an Oracle and SQL Base database.
  3. Screen form development in Oracle Developer a SQL Windows.
  4. Screen form development in Oracle Developer a SQL Windows.
  5. Report development in Oracle Developer a SQL Windows
  6. Stored procedures and database triggers in Oracle and SQL Base.
Syllabus - others, projects and individual work of students:
  1. Presentation of a conceptual model (a class diagram and a use case diagram) from Software engineering modified with respect to the extent of realization.
  2. Realization of the database application in one of the two development and databaze environments.
Fundamental literature:
  1. Korth H.F, Silberschatz, A.: Database System Concepts. Third Edition. McGRAW-HILL. 1996, 821 p.
  2. On-line help and documentation of Oracle and Centura products.
Study literature:
  1. Ramakrishnan R.: Database Management Systems. WCB/McGraw-Hill, 1998, 741 p.
  2. 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.