List of tables mapped from the ERD

Customers: stores the personal information of each customer, including their National ID, name, phone number, age, gender, address, nationality, and date of birth.

Reservations: stores the reservations made by customers, including the customer's National ID, the movie id, and the reservation date.

Movies: stores the details of each movie, including its movie id, title, duration, revenue, year, and production company id.

Genres: stores the details of each genre, including its genre id and name.

Movie Genres: stores the relationship between movies and genres, including the movie id and genre id.

Production Companies: stores the details of each production company, including its production company id, name, address, and phone number.

Database schema which includes the fields, datatypes and constraints (data validation), identifiers (primary keys and foreign keys).

Database schema:

Customers:

National ID (primary key, varchar(20))

First name (varchar(50))

Mid name (varchar(50))

Last name (varchar(50))

Phone number (varchar(20))

Age (int)

Gender (char(1))

Address (varchar(100))

Nationality (varchar(50))

Date of birth (date)

Reservations:

Reservation id (primary key, int)

Customer id (foreign key to Customers table, varchar(20))

Movie id (foreign key to Movies table, int)

Reservation date (date)

Movies:

Movie id (primary key, int)

Movie title (varchar(100))

Duration (int)

Revenue (int)

Year (int)

Production company id (foreign key to Production Companies table, int)

Genres:

Genre id (primary key, int)

Genre name (varchar(50))

Movie Genres:

Movie id (foreign key to Movies table, int)

Genre id (foreign key to Genres table, int)

Production Companies:

Production company id (primary key, int)

Production company name (varchar(100))

Address

Q: Check whether the provided design is normalized. If not, normalize the database by removing the anomalies.