Introduction

This project requires the identification of entities, their relationships, cardinalities, and attributes from the provided text. Solutions proposed must include assumptions made, and an E-R diagram of the relationships and their flows for each pair must be created. The SQL DDL will be used to set up the database's data types and key constraints, and a set of scenarios must be provided where SQL queries satisfy the business goals.

Overview

In creating the logical design for the Hotel chain problem, we started by creating an entity for the hotels. We focused on making sure the database complied with the normalization forms, especially the first one, by creating a granular database with atomic values. We also respected the second normal form by creating entities with only one primary key and avoiding partial dependencies. Test data was added to each table using insert statements, and NULL and NOT NULL constraints were specified to avoid insert anomalies. An E-R diagram of the database is also provided.

The physical database was created in R Studio using the package RSQLite. The full project containing all of our reporting can be seen on my GitHub.

Project GitHub