Database Systems I

Actual info

• Lecture number four (19.10.) will be replaced by an uploaded video.
• The repair test from SELECT queries will be 18.11.2022 at 14:15 in the classroom EB213.
• The second repair test from SELECT queries will be 2.12.2022 at 9:00 in the classroom EB207.
• The repair test from DML/DDL language will be 2.12.2022 at 10:45 in the classrom EB213.

Summary of main documents

• Project instructions (last update: 13.10.2022)
• Sample project (last update: 15.11.2021)
• DDL script for a test (NHL) - it works just on a dbsys.cs.vsb.cz\SQLDB (last update 10.10.2022)
• Description of the test database (NHL) (last update 10.10.2022)
• Example questions for the final test (last update 30.11.2022)

Tasks from the tests

• Tasks tuesday 12:30

Online Tests

You have to pass two tests which will take place directly on a computer during an exercise. The first test will be focused on SELECT queries. The second test will be focused on a database conceptual design and its creation. It is possible to use any materials during the tests (any communication with classmates will be prohibited). The maximum number of points for the SELECT test is 40 and the minimum 20. The maximum number of points for the conceptual modeling test is 20 and a minimum 10. At most two attempts of every test will be possible.  Students are allowed to use any static materials available on the web. They are not allowed to access shared content (email, social networks, forums, online documents, and so on).

Home Project

A home project is focused on data analysis of an information system. The maximum number of points for the home project is 30 and the minimum is 15. One part of the project is focused on the SQL scripts. Before you will upload your SQL scripts into the system, you may test them using the desktop application. We will use this software for the evaluation as well. The link also contains a template of the SQL scripts with the three files. 

Week	Lecture	Exercise
1.	Lecture slides: Introduction to database systems	Tasks: SQL Basics                           Solutions: SQL Basics  Script: Sakila DB                           Description: Connection to a Sql Server
2.	Lecture slides: Relational algebra + SQL I                             Script: Student study subject	Tasks: Join            Solutions: Join
3.	Lecture slides: Aggregations + Task specification	Tasks: Aggregate Functions and Group By          Solutions: Aggregate Functions and Group By
4.	Lecture slides: Subqueries	Tasks: Set Operations and Quantifiers            Solutions: Set Operations and Quantifiers
5.	Lecture slides: Complex SQL Select Queries	Tasks: Subqueries      Solutions: Subqueries
6.	Lecture slides: Conceptual modeling	Sample first test     Sample first test - solution
7.	Lecture slides: DDL & DML SQL	First test: Test 10:45 , Test 14:15    Solutions: Test 10:45 , Test 14:15
8.	Lecture slides: Indexing	Tasks: DML&DDL
9.	Lecture slides: Functional dependencies	DML/DDL Test
10.	Lecture slides: Normal Forms	Tasks: Functional Dependencies
11.		Tasks: Normal Forms