All courses

Free preview: first 2 modules. Register to save your progress. Register

Progress 1 / 152 · 1%

Module 1 – What Is a Database

Module 2 – Database Modeling

Module 3 – Normalization

Why Normalization Exists
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
BCNF
When to Break Normalization
Denormalization
Exercise: Bad Model to Good Model

Module 4 – Creating a Database

CREATE DATABASE
CREATE TABLE
Data Types
AUTO_INCREMENT
DEFAULT
NULL
NOT NULL
UNIQUE
CHECK
ENUM
COMMENT
Exercise: Create Full Database

Module 5 – Table Relationships

One to One
One to Many
Many to Many
Foreign Key
ON DELETE CASCADE
ON DELETE RESTRICT
ON DELETE SET NULL
Exercise: Student ↔ Course

Module 6 – DML – Data Manipulation

INSERT
UPDATE
DELETE
TRUNCATE
Exercise: Populate Database

Module 7 – SELECT – Basic Queries

SELECT
WHERE
ORDER BY
LIMIT
DISTINCT
LIKE
IN
BETWEEN
IS NULL
Aliases

Module 8 – SQL Functions

COUNT
SUM
AVG
MAX
MIN
ROUND
CONCAT
UPPER
LOWER
SUBSTRING
LENGTH
DATE Functions
NOW
DATEDIFF

Module 9 – JOIN

INNER JOIN
LEFT JOIN
RIGHT JOIN
CROSS JOIN
SELF JOIN
Exercise: JOIN Practice

Module 10 – Grouping

GROUP BY
HAVING
Aggregate Functions

Module 11 – Subqueries

Subquery
EXISTS
NOT EXISTS
IN (subquery)
ANY
ALL

Module 12 – Advanced SQL

UNION
UNION ALL
CASE
IF()
COALESCE

Module 13 – Views

CREATE VIEW
Updatable View
Views and Security

Module 14 – Stored Procedures

Stored Procedures
Procedure Parameters
IF in Procedures
LOOP
Cursors

Module 15 – Triggers

BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER DELETE
Exercise: Audit Log

Module 16 – Transactions

COMMIT
ROLLBACK
ACID
Isolation Levels

Module 17 – Indexes

Primary Index
Unique Index
Composite Index
Fulltext Index
EXPLAIN
How Indexes Work
Exercise: Index Performance

Module 18 – Optimization

Bad Queries
Good WHERE Clauses
JOIN Optimization
LIMIT and Pagination
Covering Index

Module 19 – Backup

mysqldump
Restore
Import
Export

Module 20 – Security

SQL Injection
Prepared Statements
Privileges
Database Users
Backup Strategy

Module 21 – Serious Database Design

Design: Webshop
Design: Hotel (detailed)
Design: CRM
Design: School System

Module 22 – Real-World Examples

WeldPilot – Welder Management
Apartment Booking
GPS Fleet Tracking
E-Learning Platform
CRM (BrainTech)

Module 23 – Mistakes & Final Project

VARCHAR(255) for Everything
Missing Foreign Keys
Multiple NULL Pitfalls
Poor Table Names
Unnecessary Duplicate Columns
Too Many JOINs
SELECT *
Final Project: Hotel System

Module 2 – Database Modeling Exercise

Exercise: Hotel Database Model

Exercise „Exercise: Hotel Database Model“ on the hotel_db model — apply prior lessons to gosti, sobe, rezervacije, placanja, and zaposleni. After this exercise you should write the SQL reception or finance need daily, without copying solutions from the internet. Use gosti, sobe, rezervacije, placanja, and zaposleni from the hotel_db model. Save each step in a .sql file with comments on what you verified.

Task

Identify entities: gosti, sobe, tip_sobe, rezervacije, placanja, zaposleni, usluge.
Define attributes and column types.
Draw cardinalities and relationships.
Mark PK and FK on the ER diagram.
Ensure no duplicated guest data in rezervacije.
Export diagram and table list.

Sample solution

-- rezervacije: gost_id, soba_id, datum_od, datum_do, status

Goal: Master „Exercise: Hotel Database Model“ through hotel_db until you can write the queries or DDL reception and finance use every morning — then you are ready for exercises, JOIN tasks, and the final hotel project. Results must be reproducible: another student runs your scripts on a fresh hotel_db and gets the same outcome.

Hello!

Contact

Exercise: Hotel Database Model