Databases, Networks and Security: free practice, theory and problems

1. Relational databases and SQL

What is it about?

Almost every app stores its data in a database: users, orders, measurements. A relational database stores data in tables, and you fetch exactly what you need with the query language SQL.

Concepts and formulas

• A table has columns (fields) and rows (records).
• Primary key: a column that identifies each row uniquely, for example student_id.
• Foreign key: a column that points to the primary key of another table. This is how tables are linked.
• The basic form of SQL:

SELECT columns FROM table WHERE condition ORDER BY column;

• Aggregate functions: COUNT(*), SUM(x), AVG(x), MIN(x), MAX(x). With GROUP BY they are computed per group.
• JOIN combines rows from two tables where the keys match:

SELECT s.name, e.course FROM Student s JOIN Exam e ON e.student_id = s.id;

• Normalization: each fact is stored in one place. That prevents conflicting copies when something changes.

How to solve the problems

1. Read FROM first (which table), then WHERE (which rows), then SELECT (what is shown).
2. Go through the rows one by one and check the condition.
3. Apply the aggregate function to the remaining rows.

Example

The table Employee has the salaries 520,000, 610,000, 480,000 and 700,000. What does SELECT COUNT(*) FROM Employee WHERE salary > 500000; give?

1. The rows with salary above 500,000: 520,000, 610,000 and 700,000.
2. COUNT(*) counts them: the result is 3.

Common mistakes

• Forgetting WHERE in an UPDATE or DELETE. Then all rows are changed or deleted.
• Mixing up WHERE (filters rows) and HAVING (filters groups after GROUP BY).
• Storing the same fact in several tables, so they end up disagreeing.

Concepts in this part

Practise relational databases and SQL in the app →

2. IP networks and subnetting

What is it about?

Everything connected to the internet has an IP address. To split a network into smaller parts, for example one for the office and one for production, you use subnetting. You also need to calculate how long it takes to send data.

Concepts and formulas

• An IPv4 address is 32 bits, written as four numbers 0–255: 192.168.1.10.
• Prefix /p: the first $p$ bits are the network part, the rest are for machines (hosts).
• The number of addresses in a subnet is $2^{32-p}$. The number of usable hosts is

because the network address (first) and the broadcast address (last) cannot be used by machines.

• /24 corresponds to the netmask 255.255.255.0 (256 addresses, 254 hosts).
• The network address is found by rounding the last part down to a whole number of blocks: the block size is $2^{32-p}$.
• Transfer time: $t = \dfrac{\text{data in bits}}{\text{bit rate}}$. Remember that 1 byte = 8 bits.
• Layers: IP is the network layer, TCP and UDP are the transport layer. TCP guarantees delivery in the right order. DNS translates names to IP addresses.

How to solve the problems

1. Hosts: compute $2^{32-p} - 2$.
2. Network address: block size $2^{32-p}$, divide the last octet by it and round down.
3. Time: convert MB to bits ($\cdot 8$) before dividing by Mbit/s.

Example

How long does it take to download 100 MB at 50 Mbit/s?

1. $100\ \text{MB} = 800$ Mbit.
2. $t = 800/50 = 16$ s.

Common mistakes

• Forgetting the two reserved addresses when counting hosts.
• Mixing MB (megabytes) and Mb (megabits), a factor of 8.
• Thinking a larger prefix gives more addresses. It is the opposite: /30 is much smaller than /24.

Concepts in this part

Practise iP networks and subnetting in the app →

3. Cyber security

What is it about?

Every engineer needs basic cyber security: data must be kept secret, not changed behind your back, and be available when needed. Many attacks start with a weak password or a tricked user.

Concepts and formulas

• CIA: confidentiality (only the right people can read), integrity (data is not changed without authorization), availability (the system works when needed).
• Password strength (entropy) in bits, when each character is chosen at random from $N$ characters and the password has $L$ characters:

• The number of possible passwords is $N^L$. Time to try them all: $N^L$ divided by the number of guesses per second.
• Hash function: turns data into a fingerprint that cannot be reversed. Passwords are stored as hashes (with salt), never in plain text.
• Symmetric encryption: the same key locks and unlocks. Asymmetric: the public key encrypts, only the private key decrypts.
• Two-factor authentication: something you know (password) plus something you have (phone or token).
• Phishing: fake emails or sites that trick the user into giving away passwords.

How to solve the problems

1. Entropy: multiply the length by $\log_2$ of the number of possible characters.
2. Cracking time: compute $N^L$ and divide by guesses per second, then convert to a suitable unit.

Example

A password of 8 lowercase letters (26 possible characters):

1. $H = 8\cdot\log_2 26 \approx 8\cdot 4.70 = 37.6$ bits.
2. $26^8 \approx 2.1\cdot 10^{11}$ possibilities. At $10^{10}$ guesses per second, all are tried in under half a minute.

Common mistakes

• Thinking complicated short passwords beat long ones. Length gives the most strength.
• Storing passwords in plain text or with a fast hash without salt.
• Reusing the same password in several places.

Concepts in this part

Practise cyber security in the app →