Software Shinobi
89c04a328f
All checks were successful
learn org at code.softwareshinobi.com/databases.softwareshinobi.com/pipeline/head This commit looks good
8.0 KiB
8.0 KiB
Monaco Motors
Schema Overview
The Monaco Motors database centralizes all critical business data, enabling efficient management of sales, customer information, vehicle inventory, and dealership operations.
Table Summary
| Table Name | Primary Responsibility |
|---|---|
Customer |
Stores essential customer contact and identification details. |
Deal |
Records each vehicle sales transaction, linking customers, agents, vehicles, and insurance. |
Dealership |
Defines the various dealerships, associating them with distributors and geographic regions. |
Distributor |
Catalogs the vehicle distributors, providing their unique identifiers and names. |
Insurance |
Contains details about insurance policies associated with vehicle deals. |
Manager |
Manages information for dealership managers, including their assigned dealership and distributor. |
Region |
Maps geographic regions to their respective ZIP codes and names. |
Sales_Agent |
Houses details for sales agents, including their manager and assigned dealership. |
Vehicle |
Maintains the inventory of vehicles, capturing their specifications, dealership, and pricing. |
Customer Table
This table stores comprehensive customer information, critical for sales tracking, communication, and relationship management.
| Column Name | Data Type | Description |
|---|---|---|
CustomerID |
CHAR(8) | Unique identifier for this entity. Used for all lookups and relationships. |
AgentID |
CHAR(8) | Establishes a relationship with Sales_Agent.AgentID. Essential for data integrity and joins. |
CustFirstName |
VARCHAR(15) | The customer's first name. |
CustLastName |
VARCHAR(15) | The customer's last name. |
PhoneNumber |
VARCHAR(12) | The customer's primary phone number. |
Email |
VARCHAR(40) | The customer's email address. |
Deal Table
This table records every completed vehicle transaction, linking all involved entities and providing a historical record of sales.
| Column Name | Data Type | Description |
|---|---|---|
DealID |
CHAR(5) | Unique identifier for this entity. Used for all lookups and relationships. |
VehicleID |
CHAR(3) | Establishes a relationship with Vehicle.VehicleID. Essential for data integrity and joins. |
AgentID |
CHAR(8) | Establishes a relationship with Sales_Agent.AgentID. Essential for data integrity and joins. |
CustomerID |
CHAR(8) | Establishes a relationship with Customer.CustomerID. Essential for data integrity and joins. |
InsuranceID |
CHAR(5) (Optional) | Establishes a relationship with Insurance.InsuranceID. Essential for data integrity and joins. Mandates explicit null handling in Java code. |
DealDate |
DATE | The date the deal was closed. Map to java.time.LocalDate for modern date/time handling. |
Dealership Table
This table defines each dealership within the Monaco Motors ecosystem, including its geographic and distribution affiliations.
| Column Name | Data Type | Description |
|---|---|---|
DealershipID |
CHAR(5) | Unique identifier for this entity. Used for all lookups and relationships. |
DistributorID |
CHAR(8) | Establishes a relationship with Distributor.DistributorID. Essential for data integrity and joins. |
RegionID |
CHAR(3) | Establishes a relationship with Region.RegionID. Essential for data integrity and joins. Part of a composite foreign key. |
RegionZIP |
CHAR(5) | Establishes a relationship with Region.RegionZIP. Essential for data integrity and joins. Part of a composite foreign key. |
DealershipName |
VARCHAR(40) | The official name of the dealership. |
Distributor Table
This table lists all vehicle distributors that supply to Monaco Motors dealerships.
| Column Name | Data Type | Description |
|---|---|---|
DistributorID |
CHAR(8) | Unique identifier for this entity. Used for all lookups and relationships. |
DistributorName |
VARCHAR(40) | The name of the vehicle distributor. |
Insurance Table
This table stores details about the insurance policies associated with vehicle deals, including policy types and renewal dates.
| Column Name | Data Type | Description |
|---|---|---|
InsuranceID |
CHAR(5) | Unique identifier for this entity. Used for all lookups and relationships. |
PolicyType |
VARCHAR(15) | Describes the type of insurance policy (e.g., "Full Coverage", "Liability"). |
RenewalDate |
DATE | The date the insurance policy is due for renewal. Map to java.time.LocalDate for modern date/time handling. |
Manager Table
This table contains information about the managers overseeing dealerships, including their salary and bonus details.
| Column Name | Data Type | Description |
|---|---|---|
ManagerID |
CHAR(8) | Unique identifier for this entity. Used for all lookups and relationships. |
DealershipID |
CHAR(5) | Establishes a relationship with Dealership.DealershipID. Essential for data integrity and joins. |
DistributorID |
CHAR(8) | Establishes a relationship with Distributor.DistributorID. Essential for data integrity and joins. |
MngrFirstName |
VARCHAR(15) | The manager's first name. |
MngrLastName |
VARCHAR(15) | The manager's last name. |
MngrSalary |
DECIMAL(8,2) | The manager's annual salary. Map to java.math.BigDecimal for financial values to ensure precision. |
MngrBonus |
DECIMAL(8,2) (Optional) | The manager's annual bonus. Map to java.math.BigDecimal for financial values to ensure precision. Mandates explicit null handling in Java code. |
Region Table
This table defines geographic regions by their ID, ZIP code, and descriptive name.
| Column Name | Data Type | Description |
|---|---|---|
RegionID |
CHAR(3) | Unique identifier for this entity, part of a composite primary key. Used for all lookups and relationships. |
RegionZIP |
CHAR(5) | The ZIP code associated with the region, part of a composite primary key. Used for all lookups and relationships. |
RegionName |
VARCHAR(15) | The descriptive name of the region (e.g., "EMEA", "APJ", "AMS"). |
Sales_Agent Table
This table stores details for each sales agent, including their reporting manager and assigned dealership.
| Column Name | Data Type | Description |
|---|---|---|
AgentID |
CHAR(8) | Unique identifier for this entity. Used for all lookups and relationships. |
ManagerID |
CHAR(8) | Establishes a relationship with Manager.ManagerID. Essential for data integrity and joins. |
DealershipID |
CHAR(5) | Establishes a relationship with Dealership.DealershipID. Essential for data integrity and joins. |
AgentFirstName |
VARCHAR(15) | The sales agent's first name. |
AgentLastName |
VARCHAR(15) | The sales agent's last name. |
AgentSalary |
DECIMAL(9,2) | The sales agent's annual salary. Map to java.math.BigDecimal for financial values to ensure precision. |
Vehicle Table
This table details the vehicle inventory, including specifications, pricing, and associated dealerships and distributors.
| Column Name | Data Type | Description |
|---|---|---|
VehicleID |
CHAR(3) | Unique identifier for this entity. Used for all lookups and relationships. |
DealershipID |
CHAR(5) | Establishes a relationship with Dealership.DealershipID. Essential for data integrity and joins. |
DistributorID |
CHAR(8) | Establishes a relationship with Distributor.DistributorID. Essential for data integrity and joins. |
Make |
VARCHAR(40) | The manufacturer of the vehicle. |
Model |
VARCHAR(40) | The specific model of the vehicle. |
BodyType |
VARCHAR(40) | The body style of the vehicle (e.g., "Coupe", "SUV", "Spyder"). |
ModelYear |
INT | The manufacturing year of the vehicle. |
Price |
DECIMAL(9,2) | The selling price of the vehicle. Map to java.math.BigDecimal for financial values to ensure precision. |