Operating Systems and Databases

This repository contains database schemas and backups used for university classes. It includes several sample databases and utility scripts for managing them within a SQL Server environment.

Repository Structure

• backups/: Compressed SQL Server database backup files (.bak.gz).
• console-queries/: SQL scripts and query consoles used during classes.
• schema/: SQL scripts for creating the database schemas (T-SQL).
• scripts/: Utility scripts for database management, including restore helpers.

Available Databases

The following databases are included in this repository:

• Lab5: Database used for Laboratory Work #5.
• Northwind: The standard Northwind sample database, often used for learning SQL and database design.
• TT319-1: A project-specific database for the TT319-1 course.
• Test: A general-purpose test database for various exercises and experiments.

How to Restore a Database

A helper script is provided to restore the backups into a Microsoft SQL Server Docker container.

Prerequisites

• Docker installed and running.
• A running SQL Server container named mssql.
• gunzip utility (available on most Unix-like systems).

Restore Procedure

To restore one of the databases, use the restore_one.sh script located in the scripts/ directory.

1. Make the script executable (if not already):

   chmod +x scripts/restore_one.sh

2. Run the script with the name of the database you want to restore:

   ./scripts/restore_one.sh <DB_NAME>

   Replace <DB_NAME> with one of: BooksDB, Lab5, Northwind, TT319-1, or Test.

3. Enter the SA password when prompted.

The script will handle decompression, copy the backup to the container, and execute the restoration command.

Manual Restore (Schema Only)

If you only need the schema without the data from the backups, you can run the SQL scripts found in the schema/ directory against your SQL Server instance using your preferred SQL client (e.g., Azure Data Studio, SQL Server Management Studio, or sqlcmd).

Query Consoles

The console-queries/ directory contains various SQL scripts that were used during the laboratory works and exercises. These include:

• main_console.sql: A collection of queries and table definitions used for multiple exercises.
• console_1.sql through console_10.sql: Sequential exercise scripts covering different topics such as table creation, data manipulation, and complex joins.

These scripts can be opened in any SQL client to review the work done or to re-run the queries against the restored databases.

Database Diagrams

Lab5

Northwind

TT319-1

Test