Santa´s Little Helper

Try me

Problem definition

Santa Claus is undergoing a digital transformation process and would like to digitised the letters he receives and store the information in a database. Particularly, he would like to store information about the childs and the list of presents they submit in their letters. For every child, Santa would like to store some basic personal information (first and last name, birth date, address, and gender) for demographic purposes. For every present, Santa would like to store a text description and information about the intended audience of the present provided by the manufacturer (intended gender and age range). For every letter, Santa would like to know the child that submitted the letter, the year, and if the child was nice that year or not. For every item in the present list of a letter, Santa would like to know which is the toy the child wants, and also keep track of which presents are granted. Be a good Santa´s little helper and develop a data model to support this challenging digital transformation project.

Data model

The following data model is a possible solution to the problem presented by Santa Claus:

Basically, in this solution, we have the Child entity representing the children submitting letters, having a unique identifier (childId) which is also the primary key, and the fields requested by Santa, the PresentList entity which represents the present list submitted in a letter (this is equivalent to a letter entity), the present entity, which records the details of the present, and a PresentListItem entity which represents an element in the present list. This is a quite common approach in data modeling to address lists.

There is a 1-N relationship between the entities Child and PresentList, because a child can submit several letters, but there is a unique restriction on the columns childFk - a foreign key to child - and presentYear (the year of submission of the present list), which limits to 1 the keys that a child can effectively submit in a year.

There is a 1-N relationship between PresentList and PresentListItems because a letter can contain 1 or more presents, and a 1-N relationship between Present and PresentListItems because a present can appear in more than one letter (or more than once in a letter).

Try it yourself

The following SQL script creates a database and data model with this solution for you to try:

-- START HERE IF YOU ARE WORKING ON YOUR MYSQL INSTANCE
DROP DATABASE IF EXISTS santas_list;
CREATE DATABASE santas_list;
USE santas_list;

-- START HERE IF YOU ARE WORKING ON SQL_FIDDLE
CREATE TABLE Child (
childId INT PRIMARY KEY,
firstName VARCHAR(40),
lastName VARCHAR(40),
birthDate DATETIME,
address VARCHAR(100),
gender ENUM('male', 'female', 'Other')
);

CREATE TABLE PresentList (
presentListId INT PRIMARY KEY,
childFk INT,
presentYear INT,
isnice BIT(1),
CONSTRAINT present_list_child_fk FOREIGN KEY (childFk) REFERENCES Child(childId),
CONSTRAINT present_child_year_uq UNIQUE (childFk, presentYear)
);

CREATE TABLE Present (
presentId INT PRIMARY KEY,
description VARCHAR(500),
intendedGender ENUM('male', 'female', 'gender-neutral'),
intendedMinRange INT,
intendedMaxRange INT
);

CREATE TABLE PresentListItems(
presentListItemId INT PRIMARY KEY,
presentListFk INT,
presentFk INT,
granted BIT(1),
CONSTRAINT item_present_list_fk FOREIGN KEY (presentListFk) REFERENCES PresentList(presentListId),
CONSTRAINT presentFk FOREIGN KEY (presentFk) REFERENCES Present(presentId)
)

Data Analysis queries

Try to write SELECT queries to extract useful information from the model. You can use the fake data available in the test database santas_little_helper

1. List of the presents requested by a specific child (e.g. with ID 1)

2. Count the number of items in every present list.

3. Provide a summary of the presents granted in 2021

4. Find all the kids who have consistently be nice every year

Data analysis queries solutions

1. List of the presents requested by a specific child (e.g. with ID 1)

The following query shows the first name of every child and the description of every present they have added to a list any year.

SELECT c.firstName, p.description
from present p
inner join presentlistItems pli on (p.presentId = pli.presentFk)
inner join presentlist pl on (pl.presentListId = pli.presentListFk)
inner join child c on (pl.childFk = c.childId)
WHERE c.childId = 1;

2. Count the number of items in every present list.

The results of this query contain the id of the child, the year of the present list and the number of items in every list, using GROUP By and the COUNT aggregated function to count the number of items.

SELECT pl.childFk, pl.presentYear, count(pli.presentListItemId)
FROM presentlistitems pli
inner join presentlist pl on (pli.presentListFk = pl.presentListId)
GROUP BY pl.presentListId;

3. Provide a summary of the presents granted in 2021, including the descriptoin of the present and the number of items granted

SELECT p.description, COUNT(*) AS NumberOfPresentsGranted
FROM Present p
JOIN PresentListItems pli ON p.presentId = pli.presentFk
JOIN PresentList pl ON pli.presentListFk = pl.presentListId
WHERE pl.presentYear = 2021 AND pli.granted = 1
GROUP BY p.description;

4. Find all the kids who have consistently be nice every year

To know which kids are consistently nice every year, we can calculate the MIN of the field isnicein the present lists. If a child is good every year the min will be 1, hence we can use HAVING to select only the results where the min is 1. Note that we use HAVING instead of WHERE because we want to filter values according to the result of the aggregated function MIN

SELECT c.firstName, c.lastName, MIN(pl.isnice) as always_nice
FROM Child c
JOIN PresentList pl ON c.childId = pl.childFk
GROUP BY c.childId
HAVING always_nice = 1