Databases I I-NI7O>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).
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.
Term 2021/2022-Z:
None |
Term 2022/2023-Z:
None |
Term 2023/2024-Z:
None |
Term 2024/2025-Z:
None |
Course coordinators
<b>Final assessment</b>
Term 2022/2023-Z: (in Polish) student uzyskuje ocenę końcową, jeżeli uzyskał zaliczenia z: wykładu i laboratorium. Ocena końcowa jest ustalana na podstawie średniej arytmetycznej z ocen OW i OL.
| Term 2023/2024-Z: (in Polish) student uzyskuje ocenę końcową, jeżeli uzyskał zaliczenia z wykładu i laboratorium; ocena końcowa jest ustalana na podstawie średniej arytmetycznej z ocen OW i OL.
| Term 2024/2025-Z: (in Polish) student uzyskuje ocenę końcową, jeżeli uzyskał zaliczenia z wykładu i laboratorium; ocena końcowa jest ustalana na podstawie średniej arytmetycznej z ocen OW i OL.
|
<b>Prerequisites</b>
Term 2022/2023-Z: (in Polish) student osiągnął efekty uczenia się w zakresie modułów: Programowanie I (IZI.20 – sem. I), Programowanie II (IZI.21 – sem. II), Podstawy interakcji człowiek-komputer (IZI.24 – sem. I) oraz Algorytmy i struktury danych (IZI.25 – sem. II). Wymagań dodatkowych brak.
| Term 2023/2024-Z: (in Polish) student osiągnął efekty uczenia się w zakresie modułów: Programowanie I (IZI.20 – sem. I), Programowanie II (IZI.21 – sem. II), Podstawy interakcji człowiek-komputer (IZI.24 – sem. I) oraz Algorytmy i struktury danych (IZI.25 – sem. II); wymagań dodatkowych brak.
| Term 2024/2025-Z: (in Polish) student osiągnął efekty uczenia się w zakresie modułów: Programowanie I (IZI.20 – sem. I), Programowanie II (IZI.21 – sem. II), Podstawy interakcji człowiek-komputer (IZI.24 – sem. I) oraz Algorytmy i struktury danych (IZI.25 – sem. II); wymagań dodatkowych brak.
|
<b>Basic literature</b>
- Dewson R.: SQL Server. Wstęp dla programistów, Helion, wyd. IV, Gliwice 2016
- Ben-Gan I.: Podstawy języka T-SQL. Microsoft SQL Server 2016 I Azure SQL Database, APN Promise, Warszawa 2016
<b>Supplementary literature</b>
- Garcia-Molina H., Ullman J.D., Widom J.: Systemy baz danych. Pełny wykład, WNT, Warszawa 2006
- Campbell L., Majors C.: Inżynieria niezawodnych baz danych. Projektowanie systemów odpornych na błędy, Helion, Gliwice 2018
<b>Other information</b>
Learning outcomes
Knowledge
Related directional learning outcomes:
IF1A_W04
Verification metods:
Knowledge
Related directional learning outcomes:
IF1A_W09
Verification metods:
Skills
Related directional learning outcomes:
IF1A_U03
Verification metods:
Skills
Related directional learning outcomes:
IF1A_U24, IF1A_U25
Verification metods:
Skills
Related directional learning outcomes:
IF1A_U04
Verification metods:
Social competence
Related directional learning outcomes:
IF1A_K07
Verification metods:
Assessment criteria
1. Students obtain a credit for a lecture on the basis of a positive assessment of a written test conducted at the last lecture in a semester - the grade for the OW lecture is determined on the basis of the assessment of the test. In the case of classes conducted remotely, the test will be conducted online on the platform elearning.ubb.edu.pl.
2. Students obtain a credit for the laboratory on the basis of positive grades from two written tests in a semester (in the case of classes conducted remotely - in the form of tasks sent back to the platform elearning.ubb.edu.pl). - the grade for the OL laboratory is determined by the arithmetic mean of the test grades.
3. Students may obtain resit credit (lecture, laboratory) in the resit session or at a later date with the approval of the dean.
4. Students obtain the final grade OK if he/she has obtained credits in the lecture and laboratory. The final OK grade is determined by the arithmetic mean of the OW and OL grades.
Bibliography
A. List of primary literature:
1. Ben-Gan I.: Podstawy języka T-SQL. Microsoft SQL Server 2022 I Azure SQL Database, APN Promise, Warszawa 2023
2. Elmsari R., Navathe S.B.: Wprowadzenie do systemów baz danych, wyd. VII, Helion, Gliwice 2019
B. List of supplementary literature:
1. Ben-Gan I.: Funkcje okna w języku T-SQL dla SQL Server 2019, APN Promise, Warszawa 2019
2. Dewson R.: SQL Server. Wstęp dla programistów, Helion, wyd. IV, Gliwice 2016
3. Garcia-Molina H., Ullman J.D., Widom J.: Systemy baz danych. Pełny wykład, WNT, Warszawa 2006
4. Pelikant A.: MS SQL Server. Zaawansowane metody programowania, wyd. II, Helion, Warszawa 2022