CSCI 331 Database Systems

Final Project

Objective: To convert QueensClassScheduleCurrentSemester Data into an Entity-Relationship Diagram (ERD) design and load the data into a new database.

Team Members:

• Aleksandra Georgievska
• Nicholas Kong
• Aryeh Richman
• Edwin Wray
• Sigalita Yakubova
• Ahnaf Ahmed

Language: T-SQL (Transact-SQL)

Tools:

• SSMS
• Azure Data Studio
• Docker
• Jupyter Notebook
• Redgate
• Google Cloud Suite
• Discord
• DaVinci Resolve
• ChatGPT

Implementation:

• Highlighted data anomalies and designed an ERD to divide tables into multiple tiers.
• Created necessary 'Schemas' to organize tables based on their category.
• Created necessary 'UDTs' to provide various attributes more functionality based on the context.
• Added primary and foreign key constraints to implement and maintain the 3rd Normal Form (3NF) in the database.
• Added default and other necessary constraints to keep the data consistent in the database.
• Created stored procedures for various tasks such as loading, deleting, adding and dropping foreign keys, truncating tables, etc.
• Migrated data from the source file into the new database using the stored procedures.

Outcome:

A T-SQL script that can migrate data from a flat file and transform it into a fully functioning 3NF database.