SQL

Semestr: Summer

Range: 2+2c

Completion:

Credits: 4

Programme type: Undefined

Study form: Fulltime

Course language: Czech

Time table at FEE

Summary:

Successful graduation of the course requires knowledge and skills, that students have acquired in course Database systems and Database systems II (relational model, object-relational model, database optimiser). Since these courses cover the SQL language in a short overview and haphazardly, a thorough treatment of most features of the SQL language is given here, particularly according to the standards SQL:1999 and SQL:2003. As a background of these standards, the object-relational data model is discussed in detail. An attention is devoted to recursion in SQL as well as to selected parts of the SQL/MM standard. The last part o lectures is devoted to temporal extensions of SQL, to possibilities of SQL for OLAP, and to partial integration of SQL and the XML language.

Keywords:

database, SQL, Query language, data manipulation language, transactions, access control, database modul language, cursor, exception control, stored subroutines, stored packages, triggers, bussiness logic, integrity constraint

Course syllabus:

1.Introduction, relational database model, basic constructs of SQL, basics of DDL language: data types, integrity constraints, CREATE TABLE statements,
2. indexing in SQL, statement SELECT, simple queries, arithmetic, aggregation functions
3. multi-level aggregation, value expressions, predicates in SQL, quantification in SQL, set operations,
4. null value, joins, DML language (statements INSERT, DELETE, UPDATE), integrity constraints, referential integrity. Views. Statements for granting and revoking privileges for user access to databases. SQL standardization.
5. Static and dynamic SQL, cursors, exception states and their processing, JDBC.
6. Stored procedures, triggers.
7. Object-relational (OR) model and SQL: the notion of a universal server, ad hoc extensibility, type, user defined types, row types constructors, references, and collections.
8. OR model and SQL: typing system in SQL:1999 and SQL:2003, subtypes, subtables, reference and dereference. Implementation of OR model in commercial DBMSs.
9. Another features of SQL:1999 and SQL:2003. Recursion in SQL.
10. SQL/MM: Full-Text.
11. SQL/MM Spatial.
12. Temporal extensions of SQL.
13. OLAP in relational databases, CUBE operator.
14. SQL and XML.

Seminar syllabus:

1. Organization rules, objectives, the class room environment
2. Getting started with thin and fat SQL clients, SQL queries
3. The first milestone of the semestral project
4. An anonymous program in the language of modules
5. Use of cursors, records and collections
6. The written test, in the second part the discussion about the test
7. The second milestone of the semestral project
8. Exceptions handling, stored procedures, the implementation of the semestral project
9. Stored functions, the implementation of the semestral project
10. Database triggers, the implementation of the semestral project
11. Stored packages, the implementation of the semestral project
12. Written test, in the second part the discussion about the test
13. The implementation of the semestral project
14. The evaluation of the semestral project

Literature:

1. Pokorný J.: Dotazovací jazyky. Science, Veletiny, 1994
2. Hoffman, J.: Introduction to Structured Query Language
http://www.highcroft.com/highcroft/sql_intro.pdf
3. Abbey M., Corey M., Abramson I.: Základy práce s databází Oracle 9i.
SoftPress 2002
4. ISO, Information Technology - Database Language SQL2. ISO/IEC 9075:1992
5. Oracle10g SQL Reference.
http://oraserv.felk.cvut.cz:7777/10gdoc/server.102/b14200/toc.htm
6. Oracle10g PL/SQL Reference.
http://oraserv.felk.cvut.cz:7777/10gdoc/appdev.102/b14261/toc.htm

Examiners:

Lecturers:

Instructors: