SP2024 CMPSC 431W: Database Management Systems

Project – Stage 2

Database Normalization and Implementation

Objective

The objective of Stage 2 is to delve deeper into database management using PostgreSQL and Command

Line Interface (CLI) based functionalities. In this stage, you will focus on table design, normalization,

schema definition, and implementation of various data manipulation operations.

As part of the project, you will need to implement a full-fledged system with user-faced interfaces

through a CLI (command-line interface). The functionalities mentioned below should have a CLI based

interface in the system to perform the necessary action. You will need to ensure that the database

system meets specified requirements and functionalities to support efficient data management.

Stage 2 Tasks:

1. Table Design and Implementation:

• Conversion of ERD into Tables using DDL Commands:

• Convert the Entity-Relationship Diagram (ERD) into tables using Data Definition

Language (DDL) commands in PostgreSQL.

• Provide the schema and the DDL commands used to create the tables.

• Normalization: Applied normalization techniques to ensure tables are in Boyce-Codd Normal

Form (BCNF), minimizing redundancy and ensuring data integrity.

• Key Mapping: Appropriately mapped keys to establish relationships between tables, ensuring

referential integrity.

• Schema Definition: Defined the schema for the database, specifying table structures, attributes,

and relationships.

2. CLI Based User Interface:

• Build a CLI interface to include options for numbers 1 through 10 and beyond. These options will

be used to specify which functionality of the SQL query to perform.

• Document each query and the functionality associated with each number option.

• When a user selects an option, execute the corresponding SQL query or functionality.

• After executing each query or functionality, capture screenshots of the query execution result.

Ensure that these screenshots are clear and properly labeled.

SP2024 CMPSC 431W: Database Management Systems

3. Make sure to have the following functionalities are implemented: The database system you will be

building must include the following in the queryi