All Software Engineers are familiar with Relational Databases. Data is stored in tables known as Entities with each table consisting of columns known as attributes or fields. Each table typically contains a Primary Key which cannot be NULL, must composed of a unique minimum set of colums.

Business Rules

• Brief and precise descriptions of policies or procedures within an organization.
• Business rules are used to define Entitis(tables), Attributes(fields/columns), relationships and constraints.
• The main source of business ruls come from managers, policy makers, end users and written documenntation.

Relationship Types

• One-to-One(1:1) - A retail company may require that each of its stores be managed by a single employee. in turn, each store manager who is an employee, manages only a single store. The relationship between EMPLOYEE manages STORE is 1:1.
• One-to-Many(1:M) - 1 painter paints MANY paintigs and 1 painting is painted by 1 painter. The relationship between PAINTER paints PAINTING is 1:M.
• Many-to-Many(M:N) - 1 student can take MANY classes and 1 class can have MANY students. The relationship between STUDENT and CLASS is M:N.

Now, consider that a Room Reservation System for clients would need to be able to allow booking of guests. Information need would consist of the following:

• Guest name
• Guest Contact number
• Guest email
• Room Type for the types of room they would like (King standard, king Suite, Queen Standad, etc)
• Room Number(s) booked
• Check in date
• Check out date
• Default daily cost of room
• Codt that was actually charged for the room
• A unique reservation number
• Guest credit card, expiry date and card type(MC,VISA,etc)
• Total cost for room(s)
• Number of days stayed

Reservation System Business Rules

• Assume more than one guest may use the same phone number or credit card when booking a room.
• Guests cannot have the same email address when booking a room.
• Assume room rates remain the same during a stay period and taxes can be ignored.

1NF

PK - Room#, Reservation Number

Here Room# and Reservation Number must be combined to form a Composite Primary Key and the table is in first Normal form

1NF -

• Each colummn value must be a single value only, i.e Atomic
• Remove repeating groups (No Lists,No Arrays, etc)
• each column must be unique
• No two rows in a table

2NF

PK - Room#

PK - Reservation Number

PK - Room# + ReservationNumber

All partial dependencies are removed as in the Room# Table Room Type and Room Rate can be known once the Room# is known. Name,Phone,email,Check in,Check out,Card# Card Type and Card Expiry can be knowm once the Reservation Number is known. Actual Room Rate remains in the initial table because if the Reservation Number or Room# is known the Actual Room Rate cannot be derrived

2NF -

• Table(s) must meet all requirements of 1NF
• All of its non-key attributes are deoendant on the primary key
• Remove all partial dependencies
• Tables with a single column key are in typically in 2NF

In 2NF form anormarmalies and redundancy usually exist as in this case. A delete anormaly would occur if a guest's credit card information needs to be deleted or updated. Also in 2NF the credit card information is redundant for guests with multiple reserations. For large databases engeneers prefer the 3NF to prevent anormalies and redunancy.

3NF

PK - Card#

PK - email

PK - Room Type

PK - Room#

PK - Reservation Number

PK - Room# + ReservationNumber

All transitive dependencies have been removed and the result is five tables in 3NF. Card# is a primary key for Card Type and card Expiry. Business Rules allow guests to have similar names or phone numbers but not email. The new RoomType table has a 1:M relationship with the Room# table, similarly the email table has a 1:M relationship with the Resevation Number table. The Card# table has a 1:M relationship with the Reservation Number table. The Room# table and the Reservation Number table both have 1:M relationships with the Room# + Reservation Number table

3NF -

• Table(s) must meet all requirements of 2NF
• Remove all transitive dependencies

The Final Normalized Databse

Id fields such as RoomType ID and guestID were also added to the final tables so that primary keys do not contain company data. All anormalies and redundancy from the 2NF form has been removed.

In 2023 no-SQL Databases have become popular for modern web applications like Facebook. Earlier programmers first taught that the most efficient way to query a database was to Noramlize it. Today the excess memeory is used to hold redundant data instead of using relationships so that tables can contain lists instead of foreign keys. This results in wasting memory but faster queries than SQL relational Databases.

Google's noSQL Firebase for example uses documents and collections as its database structure. The Firebase server uses Security Rules to allow reading and writing to the database. appwrite.io and superbase are similar to Firebase.

With the advent of GraphQL over fetching can now be avoided by specifying exactly what fields are needed as GraphQL gives you a single endpoint. The following javascript code how connection to a Firebase database is initialized using an API key provided when creating the Firebase backend.

import firebase from 'firebase';
import app from 'firebase/app';
import auth from 'firebase/auth';
import database from 'firebase/database';

const a1: firebase.app.App = firebase.initializeApp({
  apiKey: 'apiKey',
  databaseURL: 'databaseURL',
  projectId: '42',
})
    

Below is a screenshot of my Firebase Backend for a mobile Application. The data is stored in documents and collections on the Firebase server.

My Firebase Backend

As a Software Engineer and Computer Scientist I think that both relational and noSQL databases both have their uses from large to small applications.