The Zagi Retail Company Database Project is developed to demonstrate the entire lifecycle of database creation and manipulation, including designing a relational model, building and populating the database, and performing various SQL operations.
Project Structure
Relational Model
Conceptual model of the database
Entity-Relationship (ER) diagram and relational schema
Data Dictionary
Detailed descriptions of the database tables and columns
Building the Database
SQL scripts for creating database tables Populating the Database SQL scripts for inserting data into the tables SQL Queries Various SQL queries demonstrating data retrieval and manipulation Queries using WHERE, ORDER BY, JOIN, LEFT OUTER JOIN, HAVING, Set operations, Aggregate functions Database updates using INSERT, UPDATE, and DELETE ER Diagram: Adding New Requirements Updated ER diagram and relational schema to reflect new requirements Scripts for building and populating the updated database Queries based on the newly added information Dimensional Model Dimensional model diagram SQL scripts for building and populating dimensional tables Analytical SQL queries using the dimensional model Database Design The project begins with the creation of a Relational Model:
ER Diagram: Visual representation of the entities and relationships. Relational Schema: Defines the structure of the database. Implementation The implementation includes:
Building the Database: SQL scripts to create the tables as defined in the relational schema. Populating the Database: Scripts to insert initial data into the tables. SQL Queries The project includes a variety of SQL queries demonstrating different operations:
Basic Queries: Using WHERE and ORDER BY clauses. Joins: INNER JOIN, LEFT OUTER JOIN to combine data from multiple tables. Set Operations: UNION, INTERSECT, EXCEPT for combining query results. Aggregate Functions: Functions like COUNT, SUM, AVG to perform calculations on data. Database Updates: INSERT, UPDATE, DELETE operations to modify data. Dimensional Model A Dimensional Model is also developed for analytical purposes:
Dimensional Model Diagram: Represents the structure of fact and dimension tables. Building and Populating Tables: SQL scripts to create and populate the tables. Analytical Queries: Using the dimensional model for business intelligence and data analysis.