Databases AR-SI7O>BD

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).
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 MS SQL Server.
3. basics of query development and programming in T-SQL. Collective operators. Joins (JOINs): cross, inner, multiple, outer.
4. Designing table expressions: derived tables, CTE, views, built-in functions returning tables.
5. Advanced query design issues: window functions, data representation, inverse data representation, grouping sets.
6. Modifying data: insert, update, delete, merge. Elementary DDL commands - creating and modifying tables.
7. Creating temporal tables. Data and index structures in MS SQL Server.
8. Programmable objects: variables, batches, execution flow control elements, cursors, temporary tables, dynamic SQL, error handling.
9. Introduction to the creation of user-defined functions, stored procedures and triggers.
10. Credit of the lecture (written test).

B. Laboratory exercises:
1. Introductory class - health and safety training in the computer lab, explanation of the rules in the computer lab. Introduction to the MS SQL Server relational database management environment. Overview of the sample databases used in the following laboratory activities Databases I. Creation of a new database, followed by the creation and modification of tables according to a given schema. Analysis of the operation of CREATE DATABASE, CREATE TABLE, ALTER TABLE, DROP TABLE commands - creating scripts using the graphical user interface (Miscrosoft SQL Server Managenet Studio)). Overview of relational database schema constraints - a brief recapitulation of the theoretical material presented in the lecture on key constraints (PRIMARY KEY, FOREIGN KEY and UNIQUE constraints).
2. 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.
3. 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.
4. 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.
5. 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), cross joins (CROSS JOIN), the concept of union (UNION).
6. Written test 1. Extended queries, creating nested and correlated subqueries, derived tables, EXISTS operator, mixing data types (CAST and CONVERT functions). Simple and compound views, creating, editing and deleting views.
7. Stored procedures - a brief reminder of the material presented in the lecture, creating (CREATE PROC), changing (ALTER PROC) and deleting (DROP PROC) stored procedures, defining the parameters of procedures.
8. 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.
9. 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.
10. Written test 2.