Tax Database Project

Table of Contents

• Overview
• Features
• Project Structure
• Database Entities
  • Relationships & Cardinalities
• Key Constraints (Highlights)
• Sample Queries
• How to Run
• Project Objective
• Documentation
• Contributing
• License

Overview

This project designs and implements a relational database for managing tax-related information, covering taxpayers, their businesses, tax files, declarations, assessments, inspectors, and tax department offices. It includes conceptual design (ER modeling, keys, cardinalities, integrity constraints), physical implementation (table creation), sample data (initial inserts), and a set of analytical queries.

Features

• Relational database design
• Entity Relationship (ER) modeling
• Primary and Foreign Keys
• Integrity Constraints
• Sample dataset
• SQL queries for data retrieval
• Database normalization concepts

Project Structure

Project
│
├── Code
│   ├── CreateTable
│   ├── InsertTable
│   └── Queries
│
├── Project Instruction
│
├── Report
│
└── README.md

Database Entities

Entity	Description
Taxpayer	Individuals or entities registered as taxpayers
Business	Businesses owned by taxpayers
TaxFile	Tax file opened per business
TaxDeclaration	Tax declarations submitted for a tax file
TaxInspector	Inspectors who review declarations and issue assessments
TaxAssessment	Tax assessment issued for a declaration
TaxDepartment	Tax office/department that inspectors report to

Relationships & Cardinalities

• Taxpayer — Business: N:M → resolved via an associative table (Business_Tax)
• Business — TaxFile: 1:N
• TaxFile — TaxDeclaration: 1:1
• TaxInspector — TaxDeclaration: N:1 (an inspector reviews many declarations)
• TaxInspector — TaxAssessment: 1:N (an inspector issues many assessments)
• TaxDepartment — TaxInspector: 1:N (a department has many inspectors)

Key Constraints (Highlights)

• Taxpayer.NationalCode: NOT NULL, UNIQUE, length = 10
• Business.EconomicCode: PRIMARY KEY, NOT NULL, UNIQUE
• TaxFile.EconomicCode: FOREIGN KEY → Business
• TaxDeclaration.TaxFileID, InspectorID: FOREIGN KEYs → TaxFile, TaxInspector
• TaxAssessment.TaxAmount: must be > 0
• TaxDeclaration.TotalIncome, TotalExpense: must be >= 0

Sample Queries (in Code/Queries)

1. List taxpayers of type 1
2. List businesses ordered by registration date
3. Show business owners (join Business + Taxpayer)
4. Average inspector salary
5. Count of inspectors per tax office
6. Highest tax amount on record
7. Businesses with tax amount greater than 150,000
8. Taxpayers whose last name starts with "A"
9. Add an Email column to the Taxpayer table
10. Businesses located in Tehran

How to Run

The files in Code/ are plain .sql scripts, not standalone programs — they need to be executed against a running database instance through a SQL client (e.g., MySQL Workbench, SQL Server Management Studio, DBeaver, psql, or the mysql CLI).

1. Open your preferred SQL database system.
2. Execute all scripts inside Code/CreateTable.
3. Execute all scripts inside Code/InsertTable.
4. Execute scripts inside Code/Queries to test the database.

Example using the MySQL CLI:

mysql -u <username> -p <database_name> < Code/CreateTable/create_tables.sql
mysql -u <username> -p <database_name> < Code/InsertTable/insert_data.sql
mysql -u <username> -p <database_name> < Code/Queries/queries.sql

Adjust file names/paths to match what's actually inside each folder, and swap the mysql command for the equivalent tool if you're using a different DBMS (e.g., psql -U <user> -d <database> -f <file>.sql for PostgreSQL).

Run the scripts in the order above — tables reference each other via foreign keys (e.g., Business depends on Taxpayer, TaxFile depends on Business), so creating/populating them out of order will cause constraint errors.

Project Objective

The objective of this project is to design and implement a normalized relational database for a tax management system while applying database design principles, SQL programming, and query development.

Documentation

See the Report folder for the complete write-up, including the ER diagram description, normalization/key analysis, integrity constraints, full DDL/DML scripts, and query explanations.

Contributing

Contributions, corrections, and suggestions are welcome. If you'd like to contribute:

1. Fork the repository.
2. Create a new branch for your change (git checkout -b feature/your-feature).
3. Commit your changes with a clear message.
4. Open a pull request describing what you changed and why.

Please keep SQL formatting consistent with the existing scripts and update the report/README if you change the schema. you can also reach out via email at rseyednozadi@gmail.com.

License

This project is licensed under the MIT License. See the LICENSE file for details.