Databases весна 2025

Материал из Public ATP Wiki
Перейти к: навигация, поиск

Общий сведения

  • Semester: 4 (2nd year), 2 (1st year), 6 (3rd year)
  • Form of control: Differentiated grade
  • 15 weeks, Semester from 01.02 to 16.05
  • [Register]

Common rules

Submission of any control task after the deadline is not provided. Submission of any control task has a strict deadline. Deadlines can be extended only for a valid reason, +- three days after the date

Exceptions are absences due to illness with a medical certificate and competitions in the university team, absences must be notified in advance.

Deadlines can be extended for valid reasons in an individual manner (To do this, you need to write a letter to the professor's email within the specified timeframes, specify the course leader in a copy. In the body of the letter, you need to write the event for which you want to extend the deadline and indicate the reason + attach a supporting document). The deadline is postponed for a week from the date of the event.


All deadlines: specified day 23-59, unless otherwise noted.

How to prepare for the course and set up the environment and software

See the instructions here раздел Установка ПО

How to register

This is mandatory for all the students

1. Fill in the registration form

2. Attend to the course chat and news channel

3. Fill in this form if you have any problems

Course content

  • Lectures and seminars schedule can be viewed on gitlab
  • Big tests (practical assignments + a bit of theory)
  • Homework (practical assignments)
  • (Blocking) 1 report - project
  • Diff. exam at the end of the semester


Course plan

1. Lesson No. 1

  • Rel. algebra
  • DBMS architecture (schema, table, etc.)
  • General structure of SELECT query and Query execution order
  • Unioun and Union all
  • Processing NULL, NaN and greatest, least functionality
  • Working with different data types (time and date, text formats, logical data)
  • Useful SQL functions (for example, read csv and examples)

2. Lesson #2

  • Joins
  • Keys
  • Useful functions, including If-ELSE, CASE branching
  • Queries with conditions and groupings

3. Lesson #3

  • Design
  • SCD versioning
  • Normal forms

4. Lesson #4

  • Complex queries (WITH, integrity, useful functions)
  • Subqueries (related, unrelated, scalar)

5. Lesson #5

  • Window functions

6. Lesson #6

  • CTE

7. Lesson #7

  • Recursion
  • Views

8. Lesson #8

  • Stored functions
  • Procedures
  • Triggers

9. Lesson #9

  • Indexes
  • Integration with other programming languages

10. Lesson #10

  • Clickhouse
  • SQL dialects
  • Query optimization

Grade formula

The maximum grade for the course can be more than 10 - the final grade is 10

Maximum amount of points for control tasks:

  • Big tests - 2
  • Homeworks - 1
  • Project - 5
  • Exam - 2
  • Bonus - 1


Big tests

The format of the tests is Google/Yandex or a similar form, which contain a test part and a part with a detailed answer.

There are 3 tests planned for the semester.

Students will be informed about the test at least 2 days in advance in the channel or chat of the telegram, and it can also be announced in the group classes or information can be transmitted through the group leaders.

Grades for the tests are given until the next seminar (approximately 1 week). Absence for an unjustified reason = 0 points. Any technical delays on the student's side, which can lead to "sending" the work after the deadline or "dropping" the answers = 0 points and do not give the right to re-write the CR.

Dates of tests

КР № Date
1 March 24 - 28
2 April 21 - 25
3 May 12 - 16

Students do tests at seminars, links to the CR are given out in class before the CR begins. Works are written according to options. Collective work on the test may lead to zeroing out of points.


Homework

You can complete homework from the moment it is published

How to submit an assignment correctly

Use the gitlab repositories provided to you to submit tasks.

1. The essence of homework is to write an SQL script based on the task.

2. Save the scripts in the root of the repository.

3. The name of the file with the solution is formed from the branch name: <BRANCH NAME>.sql (create it yourself).

4. Each branch is a separate solution. Branch name: hw<HOMEWORK NUMBER>task<TASK NUMBER>. A separate repository will be created for each homework.

5. When defending your homework, you will be asked to demonstrate how all the scripts work and solve task 1-2 or similar tasks.


Homeworks

HW 1 - https://drive.google.com/drive/folders/1OYg71tXxKQ0hYbIITY0f1Cj5aZtVqEbR?usp=sharing

HW 2 - https://drive.google.com/drive/folders/1n9hQF-rMfr6QD4imS7I_X-G-X8lSgnJd?usp=sharing

HW 3 - https://drive.google.com/drive/folders/1riyye6rRBH_t7uVjnhdgzgOAh7MK-UTA?usp=sharing

HW 4 - https://drive.google.com/drive/folders/1eGn7Yotnfz256uv9nTON0GMdDKyOzoGW?usp=sharing

HW 5 - https://drive.google.com/drive/folders/1Kk7mfKskQQHYiH2PXZHR9N9eDVdZTI8u?usp=sharing

Raw data (sql) - https://drive.google.com/drive/folders/1M5XvYuNUAhll_nPitrVatJKhPIFlzxx5?usp=drive_link

Raw ddata - https://drive.google.com/drive/folders/1bdwD7k7U_hk__pcahcYnHE0N9b8QKe6v?usp=sharing

Homework deadlines:

Homework 1 - 01.03.2025

Homework 2 - 14.03.2025

Homework 3 - 14.04.2025

Homework 4 - 30.04.2025

Homework 5 - 10.05.2025

Other organization information

Homework № Points Themes
1 0.2 Simple queries
2 0.2 Aggregations, groupings, joins, subqueries
3 0.2 Window functions
4 0.2 CTE, Views
5 0.2 Functions, Stored Procedures, Triggers


Course project

Важно! Всю информацию, итерации сдачи/защиты курсового проекта можно найти в разделе Проект. Обратите внимание на примеры оформления.

Примеры хорошо оформленных интеграций по проекту


Обязательно:

После получения предварительной оценки за итерацию (и после защиты), необходимо загрузить материалы данной итерации на гитлаб. В комментариях надо указать :

1. Группу,

2. Итерацию,

3. Поставленную оценку,

4. Комментарии семинариста/ассистента (если есть) и кто это принял.

ДЕДЛАЙНЫ

Все пункты должны быть сданы и утверждены до дедлайна. При сдаче в последние дни перед дедлайном или в день дедлайна, если что-то требует доработки, то этот пункт не засчитывается

Рекомендация (не обязательно, но логично): выполнять и сдавать пункты №1 и №2 вместе, №3 и №4 вместе, №5 и №6 вместе

Обязательная часть (итерации 1-7) - 30.03

Дополнительная часть - 15.05

Course team

Course director: Julia A. Smolkina

E-mail: smolkina.iua@phystech.edu

Telegram: @malayataglit

Lecturer: Sophia F. Iakusheva

E-mail: yakusheva.sf@phystech.edu

Telegram: @stager108

Полезные ссылки

Телеграм чат

Источник — «http://wiki.atp-fivt.org/index.php?title=Databases_весна_2025&oldid=3755»