Explore

Database modeling

Course Material

⁠

Database-design-and-normalization.pdf

2.5 MB

⁠

🟢 Level 1

Design a database for a country that sells pears and apples to other countries.

Pears and apples have the same price for all countries.

Each country orders several times a year.

🟠 Level 2 Hotel Chain Database Modeling

Context

You are a No Code Automation Specialist hired by a hotel chain to design a centralized database system. The chain operates multiple hotel properties across different cities and wants to consolidate information currently scattered across emails, spreadsheets, and various tools.

Business Requirements

The hotel chain needs to:

Manage multiple hotel properties (each with a name, location, and city)

Track available spaces/rooms in each hotel (ballrooms, conference rooms, meeting rooms with capacity, amenities like WiFi, catering facilities, AV equipment)

Record client information (company name, contact person, email, phone, address, booking history)

Track event bookings (date, time, duration, which space is booked, which client booked it, number of attendees, special requests)

Manage services that can be added to events (catering, audiovisual equipment, parking, decoration, security – each with a base price)

Monitor space availability (which spaces are free on which dates/times)

Your Task

Design a normalized database schema that solves these problems. Include:

Identify the entities (main objects) your database needs

Define attributes for each entity (what information do you need to store?)

Identify relationships between entities (one-to-many, many-to-many, etc.)

Draw an Entity-Relationship Diagram (ERD) showing your schema using Whimsical or any other drawing tool

Explain why your design allows the chain to answer questions like:

Which client booked the most events in the last 6 months?

What services were included in event X?

Is space Y available on date Z?

What is the total revenue from client A?

Implement your newly designed database in Airtable.

Deliverables

A clear Entity-Relationship Diagram (hand-drawn, Whimsical, or any tool.

A link to your Airtable base

A publicly accessible Google Doc containing :

A short explanation (2-3 sentences per entity) describing the purpose of each table and its key relationships.

Your answers to question 5.

⁠

Want to print your doc?
This is not the way.

Try clicking the ··· in the right corner or using a keyboard shortcut (

CtrlP

) instead.