Course detail
Database Systems
FSI-RDBAcad. year: 2010/2011
The course provides an introduction to DBS. It deals with the following topics:
Data processing, redundancy, inconsistency, integrity, security, data sharing.
Index-sequential organisation. Relationship integrity constraints.
DBS architecture, data models. Entity-relationship model, relational algebra.
Theoretical fundamentals of information system design. Functional dependencies, normal forms, decomposition theorem.
Structured Query Language (SQL).
Microsoft Access 2003. Visual Basic for Applications (VBA).
Web databases. PHP and MySQL.
Language of instruction
Czech
Number of ECTS credits
5
Mode of study
Not applicable.
Guarantor
Learning outcomes of the course unit
The course familiarises students with theoretical aspects of database application design and its implementation in MS Access including programming in SQL and Visual Basic. They will be able to create an application for an efficient management of large scale data structures.
Prerequisites
Successful completion of the course "Database Systems" is conditional on basic knowledge of algoritmization and Pascal (or Delphi) language.
Co-requisites
Not applicable.
Planned learning activities and teaching methods
Teaching methods depend on the type of course unit as specified in the article 7 of BUT Rules for Studies and Examinations.
Assesment methods and criteria linked to learning outcomes
The course-unit credit is awarded on condition of having implemented a nontrivial database application in MS Access using Visual Basic and SQL. The exam has a written form and tests students’ knowledge of database design, relational algebra, SQL and programming in Visual Basic.
Course curriculum
Not applicable.
Work placements
Not applicable.
Aims
The course aims to acquaint the students with basic database technologies for an efficient design and management of applications with large data structures in Microsoft Access 2003 environment using SQL (Structured Query Language) and Visual Basic.
Specification of controlled education, way of implementation and compensation for absences
Since the attendance at seminars is required, it will be checked systematically by the teacher supervising the seminar. If a student misses a seminar, an excused absence can be compensated for via make-up topics of exercises.
Recommended optional programme components
Not applicable.
Prerequisites and corequisites
Not applicable.
Basic literature
Connoly, T.M. and Begg, C.E.: Database Systems : A Practical Approach to Design, Implementation and Management. Pearson Education, 2004 (4th edition).
Date, C.J.: An Introduction to Database Systems. Addison Wesley, New York, 2003 (8th edition).
Fortier, P.J.: Database Systems Handbook. McGraw-Hill, 1997.
Lacko, L.: SQL. Hotová řešení pro SQL Server, Oracle a MySQL. Computer Press, Brno, 2003.
Pokorný, J.: Konstrukce databázových systémů. Skriptum ČVUT FEL, Praha, 1999.
Pokorný, J.: Visual Basic pro aplikace Accessu 2000. Kopp, České Budějovice, 2000.
Pratt, J.P.: A Guide to SQL. Course Technology, 2003 (6th edition).
Ullman, L.: PHP and MySQL for Dynamic Web Sites. Pearson Education, 2003 (český překlad: PHP a MySQL: Názorný průvodce tvorbou dynamických www stránek. Computer Press, Brno, 2004).
Viescas, J.: Mistrovství v Microsoft Access 2000. Computer Press, Praha, 2000.
Williams, H.E. and Lane, D.: Web Database Applications with PHP and MySQL. O’Reilly, 2002 (český překlad: Programujeme webové aplikace pomocí PHP a MySQL. Computer Press, Praha, 2002).
Date, C.J.: An Introduction to Database Systems. Addison Wesley, New York, 2003 (8th edition).
Fortier, P.J.: Database Systems Handbook. McGraw-Hill, 1997.
Lacko, L.: SQL. Hotová řešení pro SQL Server, Oracle a MySQL. Computer Press, Brno, 2003.
Pokorný, J.: Konstrukce databázových systémů. Skriptum ČVUT FEL, Praha, 1999.
Pokorný, J.: Visual Basic pro aplikace Accessu 2000. Kopp, České Budějovice, 2000.
Pratt, J.P.: A Guide to SQL. Course Technology, 2003 (6th edition).
Ullman, L.: PHP and MySQL for Dynamic Web Sites. Pearson Education, 2003 (český překlad: PHP a MySQL: Názorný průvodce tvorbou dynamických www stránek. Computer Press, Brno, 2004).
Viescas, J.: Mistrovství v Microsoft Access 2000. Computer Press, Praha, 2000.
Williams, H.E. and Lane, D.: Web Database Applications with PHP and MySQL. O’Reilly, 2002 (český překlad: Programujeme webové aplikace pomocí PHP a MySQL. Computer Press, Praha, 2002).
Recommended reading
Pokorný, J. - Halaška, I. : Databázové systémy, , 0
Viescas, J.L. : Mistrovství v Microsoft Access 2000, , 0
Viescas, J.L. : Mistrovství v Microsoft Access 2000, , 0
Classification of course in study plans
Type of course unit
Lecture
26 hod., optionally
Teacher / Lecturer
Syllabus
1. Introduction to DBS. Data sorting. Drawbacks of the classical file processing.
2. Database systems, data definition language (DDL) and data manipulation language (DML). Database system architecture. Physical and logical independence. Data models. Integrity constraints for relationships.
3. Relational algebra, basic operations. Relational algebra as a query language.
4. Design of data structures. Normal forms of relations.
5. Structured Query Language (SQL). Introduction to SQL SELECT.
6. SQL SELECT, examples.
Aggregate functions. Multisegment aggregate key.
7. SQL. Subqueries. Multiple opening a table, relationship 1 to N within one table. Nested aggregations, query implementation using a sequence of queries.
8. SQL. Computing with NULL value. Existential and universal qualifier in SQL. Crosstab query. Update (action) queries. Definition queries.
9. Visual Basic for Applications. Definition of constants, declaration of variables,
data types. Simple control structures.
10. Visual Basic for Applications. Loop statements, jump statements,
With statement. Subroutines.
Database object, .OpenRecordset method. Recordset object and its basic methods and properties
(move, add, update, delete, find).
11. Visual Basic for Applications. Recordset object, search method, indexing, filtering, sorting, synchronizing.
DoCmd and Form objects.
12. Visual Basic for Applications. Calling SQL from Visual Basic. Transactions.
13. Web databases. PHP and MySQL.
2. Database systems, data definition language (DDL) and data manipulation language (DML). Database system architecture. Physical and logical independence. Data models. Integrity constraints for relationships.
3. Relational algebra, basic operations. Relational algebra as a query language.
4. Design of data structures. Normal forms of relations.
5. Structured Query Language (SQL). Introduction to SQL SELECT.
6. SQL SELECT, examples.
Aggregate functions. Multisegment aggregate key.
7. SQL. Subqueries. Multiple opening a table, relationship 1 to N within one table. Nested aggregations, query implementation using a sequence of queries.
8. SQL. Computing with NULL value. Existential and universal qualifier in SQL. Crosstab query. Update (action) queries. Definition queries.
9. Visual Basic for Applications. Definition of constants, declaration of variables,
data types. Simple control structures.
10. Visual Basic for Applications. Loop statements, jump statements,
With statement. Subroutines.
Database object, .OpenRecordset method. Recordset object and its basic methods and properties
(move, add, update, delete, find).
11. Visual Basic for Applications. Recordset object, search method, indexing, filtering, sorting, synchronizing.
DoCmd and Form objects.
12. Visual Basic for Applications. Calling SQL from Visual Basic. Transactions.
13. Web databases. PHP and MySQL.
Computer-assisted exercise
13 hod., compulsory
Teacher / Lecturer
Syllabus
Seminars in computer labs.
1. Table creation and defintion of item properties (type, masque, validation rule, …), definition of keys (primary key, nonprimary keys, multisegment keys).
2. Sorting by more columns. Simple query, ordering of results; AND, OR operations in criteria.
3. Form, controls Combo Box/List Box - SQL/Table, Value List, Field List. OLE object, images (seříznutí, rozprostření, zoom).
4. Relationship, referential integrity, join type (INNER JOIN, LEFT JOIN, RIGHT JOIN). Form with subform(s).
5. Form, event procedure Click() of command buttons with searching.
6. Report, sorting and grouping, aggregate functions, page numbering.
7. Criteria form without joined table containing the following controls: combo box (SELECT DISTINCT …), edit field, command button.
8. Form, evaluation in edit fields, AfterUpdate() event procedure. Form, dynamic update of listboxs, .Requery method.
9. Form, twolevel selection. Option Group and List Box controls; funkce Iif(). Form_Current()event procedure.
10. Form, twolevel selection. Check Box and Toggle Button controls; visible and hidden controls. Tab control. Calling update queries from VBA (DoCmd.RunSQL).
11. Design of more complex application. Recordset object, SQL SELECT and update queries in VBA subroutines.
12. Review and practice - SQL: subqueries, realization of 'at least 1', 'none' and 'all' expressions.
13. Course-unit credit awarding.
1. Table creation and defintion of item properties (type, masque, validation rule, …), definition of keys (primary key, nonprimary keys, multisegment keys).
2. Sorting by more columns. Simple query, ordering of results; AND, OR operations in criteria.
3. Form, controls Combo Box/List Box - SQL/Table, Value List, Field List. OLE object, images (seříznutí, rozprostření, zoom).
4. Relationship, referential integrity, join type (INNER JOIN, LEFT JOIN, RIGHT JOIN). Form with subform(s).
5. Form, event procedure Click() of command buttons with searching.
6. Report, sorting and grouping, aggregate functions, page numbering.
7. Criteria form without joined table containing the following controls: combo box (SELECT DISTINCT …), edit field, command button.
8. Form, evaluation in edit fields, AfterUpdate() event procedure. Form, dynamic update of listboxs, .Requery method.
9. Form, twolevel selection. Option Group and List Box controls; funkce Iif(). Form_Current()event procedure.
10. Form, twolevel selection. Check Box and Toggle Button controls; visible and hidden controls. Tab control. Calling update queries from VBA (DoCmd.RunSQL).
11. Design of more complex application. Recordset object, SQL SELECT and update queries in VBA subroutines.
12. Review and practice - SQL: subqueries, realization of 'at least 1', 'none' and 'all' expressions.
13. Course-unit credit awarding.