Database-Design-and-Development

Car Dealership Database — Design & Development

A relational database for a car dealership that manages sales, service tickets, customers, and GDPR-aware personal data.
Core deliverables include the ERD, Data Dictionary, Test Plan, and SQL scripts (table creation, data load, stored procedures, MI views, and GDPR delete logic).

---

1) Project Assets

• ERD & Data Dictionary: Conceptual + logical design, table attributes, PK/FK, and relationships.
• Test Plan: FK violation tests and expected vs actual results.
• SQL: Create tables, insert data, stored procedures, MI views, GDPR transaction logic.

2) ERD Overview (High-level)

Main entities & relationships

ERD Cardinalities (Table)

#	Relationship	Cardinality	Read as
1	Customer — Invoice	One-to-Many (1:N)	Customer (1) → Invoices (N)
2	Employee — Invoice	One-to-Many (1:N)	Employee (1) → Invoices (N)
3	Car — Invoice	One-to-Many (1:N)	Car (1) → Invoices (N)
4	Customer — TicketSystem	One-to-Many (1:N)	Customer (1) → Tickets (N)
5	Employee — TicketSystem	One-to-Many (1:N)	Employee (1) → Tickets (N)
6	Car — TicketSystem	One-to-Many (1:N)	Car (1) → Tickets (N)
7	Model — Car	One-to-Many (1:N)	Model (1) → Cars (N)
8	Make — Car	One-to-Many (1:N)	Make (1) → Cars (N)
9	Customer — CustPersonalDetails	One-to-One (1:1)	Customer (1) ↔ CustPersonalDetails (1)
10	TicketSystem — Parts	Many-to-Many (M:N)	Tickets (M) ↔ Parts (N)

---

3) Test Plan:

4) Getting Started with SQL Server

1. Create a SQL Server database (e.g., CarDealership).
2. Run sql/01_create_tables.sql then sql/02_insert_seed_data.sql.
3. (Optional) Run sql/03_stored_procedures.sql and sql/04_views.sql.
4. Validate with sql/05_test_queries.sql and the Test Plan.

---

5) Key SQL — Examples From This Project

a) DDL (Data Definition Language)

Create core tables with PK/FK relationships.

1.

2.

3.

4.

b) DQL (Data Query Language)

Example of insert seed data

c) DML (Data Manipulation Language)

Selects,joins and reporting views

d) TCL (Transaction Control Language)

GDPR-aware delete that keeps business-critical data but removes personal data; uses explicit transaction with TRY…CATCH.

e) DCL (Data Control Language)

example of grant read access on reporting views(SQL code)-

CREATE ROLE reporting_reader;

GRANT SELECT ON OBJECT::dbo.MI_Extract TO reporting_reader;

– EXEC sp_addrolemember ‘reporting_reader’, ‘YourLoginOrUser’;