لخّصلي

1.Inventory_Capacity
INT
Dealer's maximum vehicle capacity, vital for efficient inventory management
Location
VARCHAR
Location of the dealer.Location_ID (References Location Dimension Table)

Customer Dimension Table:

Customer_ID
-----
Car Dimension Table:

Car_ID

-----
Location Dimension Table:

Location_ID

-----
Time Dimension Table:


Time_ID
-----
Dealer Dimension Table:

Dealer_ID

-----


2.5 Meta Data

Invoice Fact Table:

Attribute
Data Type
Description
Invoice_ID (PK)
INT
Auto-generated unique identifier for each sales transaction.Introduction & Users' Requirements

In today's rapidly advancing technological and industrial world, the automotive industry is undergoing a major transformation, and vehicle inventory management plays a pivotal role in automotive service providers and dealership operations.B) Potential Benefits of DW Development:

a) Upgraded Stock Administration: The DW will empower dealerships to preserve ideal stock levels, diminish costs, and minimize capacity space necessities.1.1 Organization, its business environment, and problems

This project concerns car dealerships, service centers, and stakeholders in the field of car inventory in the Sultanate of Oman, for example, Saud Bahwan Automotive Company.Invoice (Fact Table): PK Invoice_ID, FK Car_ID, FK Dealer_ID, FK Time_ID, FK Custamer_ID, Unit Price, Tax Amount, Total Amount, Payment Method.Inventory (Fact Table): Inventory_ID PK, Fk Car_ID, Dealer_ID FK, Time_ID FK, Supplier_ID FK, Quantity, Value. A) Problems:

a) Inventory Overstock/Understock: Dealerships often experience inventory overstock and understock, resulting in higher operating costs or lost revenue opportunities. b) Moved forward Decision-Making: Data-driven insights will enable partners to form educated choices, from acquirement and estimating procedures to deals determining and client maintenance.Inventory Fact Table:

Attribute
Data Type
Description
Inventory_ID (PK)
INT
Auto-generated unique identifier for each inventory record.Car Dimension:

Attribute
Data Type
Description
Car_ID (PK)
INT
Auto-generated unique identifier for each car model.Time Dimension:

Attribute
Data Type
Description
Date_ID (PK)
INT
Auto-generated unique identifier for each date.Dealer Dimension:

Attribute
Data Type
Description
Dealer_ID (PK)
INT
Auto-generated unique identifier for each dealer.This project will cover different use cases for data warehouse tools and technologies and factors to consider when choosing a data warehouse solution and using data warehouse models and address the challenges associated with car inventory management. c) Demand Forecasting: Forecasting the demand for automobiles is essential for successful procurement and sales plans, however, it is impeded by the lack of comprehensive historical data. d) Competitive Advantage: By grasping cutting-edge innovation and data-driven bits of knowledge, organizations can pick up a competitive edge in Oman's car showcase.Supplier (Dimension): Supplier_ID PK, Location_ID FK, Supplier Name, Contact Name, Contact Email, Contact Phone.Year
INT
Year the car was built
Color
VARCHAR
Cars Color
Fuel_Type
VARCHAR
Which type of fuel
Vehicle_Type
VARCHAR
Type of vehicle (e.g., Sedan, SUV, Coupe)).Supplier Dimension:
Attribute
Data Type
Description
Supplier_ID (PK)
INT
Unique identifier for each supplier in the dimension.Location_ID (FK)
INT
Identifier linking to the Location Dimension for supplier's location.The automotive industry environment in the Sultanate of Oman is diverse, as the market includes a wide range of car companies and models.With this diversity come many challenges, including inventory management, improving sales operations, and demand forecasting.c) Streamlined Operations: The DW will disentangle information get to and examine, permitting for streamlined operations and way better benefit to clients.Dealer (Dimension): Dealer_ID PK, Dealer Name, Contact Name, Contact Email, Contact Phone, Inventory Capacity, Location.2.4 Constraints

Table
Primary Key (PK):

Foreign Keys (FK):



Inventory Fact Table:



Inventory_ID

?Supplier_ID (References Supplier Dimension Table)



Invoice Fact Table:



Invoice_ID

?Supplier_ID (FK)
INT
Foreign Key linking to the Supplier Dimension.Location Dimension:
Attribute
Data Type
Description
Location_ID (PK)
INT
Unique identifier for each location in the dimension.Customer Dimension:
Attribute
Data Type
Description
Customer_ID (PK)
INT
Unique identifier for each customer in the dimension.Time to Invoice (One-to-Many):
Description: Each time entry can have multiple invoices, but each invoice is associated with only one time.Customer_ID (References Customer Dimension Table)

Supplier Dimension Table:

Supplier_ID

?Car_ID (FK)
INT
Foreign Key linking to the Car Dimension.Dealer_ID (FK)
INT
Foreign Key linking to the Dealer Dimension.Time_ID (FK)
INT
Foreign Key linking to the Time Dimension.Customer_ID (FK)
INT
Foreign Key linking to the Customer Dimension.Car_ID (FK)
INT
Foreign Key linking to the Car Dimension.Time_ID (FK)
INT
Foreign Key linking to the Time Dimension.Dealer_ID (FK)
INT
Foreign Key linking to the Dealer Dimension.Quantity
INT
Represents the quantity of units involved in the inventory transaction (e.g., received or sold).Value
DECIMAL
Represents the total value associated with the inventory transaction.Make
VARCHAR
Identifier linking to the Mke Dimension.Contact_Email
VARCHAR
Email address for communication with the supplier.Many organizations are increasingly turning to data warehousing tools as their primary data technology solution.b) Data Fragmentation: Vehicle data, including model information, service history, and sales history, is spread across multiple systems and databases, making it hard to get a comprehensive picture.1.2 Data warehouse system users:

Database System Users
Usage
Management
- Performance monitoring.Inventory Managers
- Maintain updates on inventory levels.Marketing Teams

- Analyze market trends.Conceptual Dimensional Model

2.1 Dimensional Model Figure


2.2 Entities and their Attributes

?Invoice to Car (Many-to-One):
Description: Each invoice is associated with one car, but a car can be related to multiple invoices.Invoice to Dealer (Many-to-One):
Description: Each invoice is associated with one dealer, but a dealer can be related to
multiple invoices.Invoice to Time (Many-to-One):
Description: Each invoice is associated with a specific time, but multiple invoices can share the same time.Customer to Invoice (One-to-Many):
Description: Each customer can have multiple invoices, but each invoice is associated with only one customer.Dealer to Invoice (One-to-Many):
Description: Each dealer can have multiple invoices, but each invoice is associated with only one dealer.Dealer to Inventory (Many-to-Many):
Description: Each dealer can be associated with multiple inventory records, and each inventory record can be associated with multiple dealers.Car to Invoice (One-to-Many):
Description: Each car can have multiple invoices, but each invoice is associated with only one car.Inventory to Supplier (Many-to-One):
Description: Each inventory record is associated with one supplier, but a supplier can be related to multiple inventory records.Supplier to Inventory (One-to-Many):
Description: Each supplier can have multiple inventory records, but each inventory record is associated with only one supplier.Supplier to Location (Many-to-One):
Description: Each supplier is associated with one location, but a location can be related to multiple suppliers.Location to Supplier (One-to-Many):
Description: Each location can have multiple suppliers, but each supplier is associated with only one location.PaymentMethod
VARCHAR
The payment method used for the transaction.Identifies the dealer associated with the inventory transaction.Model
INT
Identifier linking to the Model Dimension.Contact_Email
VARCHAR
Email address for contacting the dealer.Supplier_Name
VARCHAR
Name or designation of the supplier.Contact_Name
VARCHAR
Contact information for communication with the supplier.Contact_Phone
VARCHAR
Phone number for communication with the supplier.- Upgrade inventory levels.Business Intelligence Teams
- Analyze sales trends.Finance and Accounting
- Financial reporting.Customer (Dimension): PK Customer_ID, Customer Name, Phone, Address, State, Age,
?Time (Dimension): Time_ID PK , Date, Time.Car (Dimension): Car_ID PK, Make, Model, Year, Color, Fuel Type, Vehicle Type.Location (Dimension): PK Location_ID, Country, State, City.Inventory to Car (Many-to-One):
Description: Each inventory record is associated with one car, but a car can be related to multiple inventory records.Inventory to Dealer (Many-to-One):
Description: Each inventory record is associated with one dealer, but a dealer can be related to multiple inventory records.Inventory to Time (Many-to-One):
Description: Each inventory record is associated with a specific time, but multiple inventory records can share the same time.Car_ID (References Car Dimension Table)
?Dealer_ID (References Dealer Dimension Table)
?Time_ID (References Time Dimension Table)
?Car_ID (References Car Dimension Table)
?Dealer_ID (References Dealer Dimension Table)
?Time_ID (References Time Dimension Table)
?UnitPrice
DECIMAL(10, 2)
Price per unit in each transaction.TaxAmount
DECIMAL(12, 2)
Amount of tax associated with the sale.TotalAmount
DECIMAL(12, 2)
Total amount of the transaction, including tax.Identifies the time period of the inventory transaction.Identifies the supplier from whom the inventory was received.Contact_Phone
VARCHAR
Phone number for contacting the dealer.Country
VARCHAR
Indicates the country where the location is situated.State
VARCHAR
Specifies the state or region within the country.City
VARCHAR
Identifies the city or town associated with the location.Customer_Name
VARCHAR
Full name or designation of the customer.- Predicted demand for vehicle components.- Analyze inventory performance and customer behavior.Supplier (Dimension) Relationships:

?Identifies the customer associated with the sale.Identifies the specific car in the inventory.Time
TIME
Represents the time associated with the date.Dealer_Name
VARCHAR
The name of the car dealer.Contact_Name
VARCHAR
Contact person's name for the dealer.Phone
VARCHAR
Contact phone number for communication with the customer.Address
VARCHAR
Physical or mailing address of the customer.State
VARCHAR
State associated with the customer.Age
INT
Represents the age of the customer.- Campaign analysis.Sales Teams
- Manage sales orders.- Check car availability.- Handle customer inquiries.- Budgeting reports.Invoice (Fact Table) Relationships:

?Customer (Dimension) Relationships:

?Time (Dimension) Relationships:

?Dealer (Dimension) Relationships:

?Car (Dimension) Relationships:

?Inventory (Fact Table) Relationships:

?Location (Dimension) Relationships:

?Identifies the specific car involved in the sale.Identifies the dealer responsible for the sale.Identifies the time period of the sale.Date
DATE
Represents the date.- Decision-making.- Monitor part orders.2.??????2.3 Relationships


1.??2.3.4.?5.6.???7.?8.