Databases I I-SI7O>BD-I

A. Lectures - detailed learning content:
1. Introduction, types and classification of databases, characteristics of different types of databases, applications of databases, features, functions and requirements for databases, overview of modern RDBMS (Relational Database Management Systems).
2. Relational databases: introduction and description of basic concepts (relation, table, record, tuple, attribute, domain, primary key, foreign key, uniqueness, integrity constraints, query, subquery, view, join, relation algebra operations, etc.), data types in SQL Server 2016.
3. Basics of query development and programming in T-SQL.
4 Collective operators. Joins (JOINs): cross, inner, multiple, outer.
5. Table expression design - part I: derived tables, CTE.
6. Table expression design - part II: views, built-in functions returning tables.
7. Advanced query design issues: window functions, data representation, inverse data representation, grouping sets.
8. Modifying data: insert, update, delete, merge.
9. Elementary DDL commands - creating and modifying tables.
10 Creating temporal tables.
11 Data structures and indexes in SQL Server.
12 Programmable objects - part I: variables, batches, execution flow control elements.
13. Introduction to creating user-defined functions, stored procedures and triggers.
14. Programmable objects - part II: cursors, temporary tables, dynamic SQL, error handling.
15 Written test.

B. Laboratory exercises:
1. Introductory class - health and safety training in the computer lab, explanation of the rules of the computer lab. Introduction to relational database management environment MS SQL Server. Getting acquainted with sample databases used in the further part of the classes. Executing the simplest queries to single tables of a selected exemplary database using the SELECT command.
2. Creating and modifying tables, the CREATE DATABASE and CREATE TABLE commands and their options, the ALTER command (ALTER DATABASE and ALTER TABLE), the DROP command (DROP DATABASE and DROP TABLE), creating scripts using the above-mentioned commands and parallel execution of the above tasks using the graphical user interface of the MS SQL Server system.
3. Constraints, a brief recap of the theoretical material presented in the lecture, key constraints (PRIMARY KEY, FOREIGN KEY and UNIQUE constraints), CHECK and DEFAULT constraints, disabling constraints, temporarily disabling an existing constraint, rules and default values, introduction to the concept of triggers.
4. Creating and modifying SQL queries for individual tables. Syntax of the SELECT statement, use of the WHERE clause (introduction of the concept of relational, arithmetic, theorem, logical operators), Use of the ORDER BY clause to display query results in the desired order.
5. Explanation of the concept of data aggregation (use of GROUP BY and HAVING clause), presentation of available predefined aggregation functions (MIN, MAX, COUNT, AVG, SUM, STDEV, VAR), explanation of the concept of ‘alias’, introduction of DISTINCT and ALL predicates, extending the possibilities of selection queries.
6. Entering data into tables using the INSERT command, use of the INTO and VALUES keyword, use of the INSERT INTO ... SELECT command. Making substitutions using the UPDATE command. Deleting data from a table using the DELETE command.
7. Joins, a brief reminder of lecture material on the JOIN clause, inner join (INNER JOIN), outer join (OUTER JOIN), left and right outer joins, full joins (FULL JOIN).
8. Written test 1. Cross joins (CROSS JOIN), the concept of union (UNION).
9. Extended queries, creating nested and correlated subqueries, derived tables, EXISTS operator, mixing data types (CAST and CONVERT functions).
10. Data structures and indexes in MS SQL Server, brief reminder of the material presented in the lecture, creating, modifying and deleting indexes (CREATE INDEX command), advantages and disadvantages of using indexes.
11. Simple and compound views, creating, editing and deleting views. Use of the MS SQL Server graphical user interface to perform the above activities. Exemplary exercises under the supervision of the instructor.
12. Stored procedures - a brief review of the material presented in the lecture, creating (CREATE PROC), changing (ALTER PROC) and deleting (DROP PROC) stored procedures, defining the parameters of the procedures.
13. Instructions (commands) that control program flow (IF..ELSE, CASE, WHILE, WAITFOR, TRY/CATCH), other commands, error handling instructions, the issue of recursion when creating stored procedures.
14. User-defined functions, creating, changing and deleting functions. Triggers - examples of different types of triggers, usage, advantages and disadvantages, good practice in creating and using triggers.
15. Written test 2.