Assignment 1: Data Extraction & Integration

In this assignment, you need to design a database for one of the following events:

FIFA world cup 2026
Grand Slam & ATP 1000 tennis tournaments

Your database should be as complete/realistic as possible. It should contain relations that represent entities and relations that represent relationships.

⚠Generative AI tools

This assignment assesses your capabilities in designing a database, extracting the required information, and understanding how to apply what you learned in the lectures on real world problems.

You may use AI tools such as ChatGPT only for generating a set of records to be stored in the database to test your queries or drawing the schema. However, you should describe the different components of the figure in your own words.

Task 1: Database Design

Draw the schema chart for the database or the ER-diagram.
Identify the cardinalities of the relationships between the entities and explain how do you represent the relationships in your design.
List all the primary-key-foreign-key and referential integrity constraints in the database. What policies do you use to define the referential integrity?
Write the SQL code that creates the whole database in SQLite and save the file as .sql that can be run directly from the sqlite3 bash using the command .read my_file.sql
List all the relations (tables) that represent entities and state whether they are in the BCNF or not. Justify your answer.

Task 2: Querying the database

Using the database from Task 1, write queries in natural language, relational algebra and SQL that cannot be answered unless you:

join two or more tables.
use aggregate function.
use nested query.
use self join.
use set operation.

NOTE: write a different query for every condition. By different, you can use different tables or different attributes.

To make sure that your queries can be run on the database, you need to include, in your .sql file, a set of queries to enter a few records in the tables that are involved.

Task 3: Data extraction in Python

Using the database from Task 1, write Python code that performs the following tasks:

creates a connection to the database file that was created in Task 1, sends SQL queries to the database and extracts the results.
reads one of the relations that contains an attribute with strings (e.g. names) and store it in a Pandas DataFrame (df_sim).
Define a Hashing Function that hashes the strings into indexes between 0 and m-1, where m is the table cardinality. Report the collision rate of the function on the available data.

To make sure that your queries and the code can be run and tested, you need to include queries that enters a set of records in the table that you will read in this task. Make sure to include strings that have values with high similarity.

Submission

Submit a zip file that contains:

A pdf file that reports and explains how you answered the questions.
An SQL file (with extension .sql) that contains all the code for creating the database, adding records in the selected relations and querying the database.
A Python notebook that includes the code for solving Task 3 with clear comments on the code. The explanation of the logic behind the code should be included in the report (the pdf file).

Submission should be done on BrightSpace and only one submission per group is needed. You can submit as many versions as you like but only the last submission before the deadline will be graded.

Deadline

as specified on the BrightSpace, the deadline is (02-10-2026 11:59PM).