Question

Consider Ace Rent-A-Car, a nationwide automobile rental company. For each car that the company owns, Ace records its unique vehicle identification number (VIN), its “make” (manufacturer), model, year of manufacture, and the manufacturer’s factory in which it was made. Each factory is identified by the combination of its company name (i.e. manufacturer name) and the city in which it is located. We also know its size and the year it was built. Each manufacturer is identified by its unique name, plus its headquarters city and the name of its president. Customers are identified by a unique customer number, plus Ace wants to store each customer’s name, address, and telephone number. Each Ace rental location has a unique rental location number, address, and telephone number. Each Ace rental location is assigned to an Ace region, which has a unique region name, a manager, and the location of the main regional office.

Ace wants to develop a data warehouse to store its historical rental data. For each rental, Ace wants to record which customer rented which car from which rental location and when the rental began and when it ended. Ace also wants to record the mileage on the car when the rental began, the mileage when it was returned, whether or not the customer bought the insurance that Ace offered, and the total cost of the rental.

Given this scenario, develop a star schema, which may be a snowflake schema, for Ace’s data warehouse.

Answer 1

Above diagram in the image represents a snow flake schema for ace data warehouse details

Snow flake schema is logical arrangement of tables in a multidimensional database such that the ER diagram resemble a snow flake.

Snow flake schema is an extension of star schema and it adds additional dimensions. The dimensional table are normalised which splits data into multiple tables.

This snow flake schema consists the fact table as ace rent a car table contains the main information such as Vehicle Identification Number, customer number, rental location and number.

The fact table ace rent a car connects to dimension tables like, customer, vehicle, rental location, and data warehouse.

The dimension table customer contains it's attributes like customer number, name address, telephone number.

Dimension Table rental location has attributes location number, address and telephone number. And also another table connected to it as sub dimension table aceregion which has attributes region name manager and location.

The vehicle table contains VIN, make, model, year, factory as it's attributes. And sub dimension ta ke as factory and manufacturer for vehicle table. Factory table contains name, city, size, year as attributes. Manufacturer table have name, city, name of President as it's attributes.

Data warehouse dimension table contains it's attribute as car and customer, start rental, end rental, mileage when started, mileage when end, insurance, total cost as it's attribute.

If it was a star shema then it wouldn't contain the dimension table for vehicle as factory and manufacturer. All the attribute will come under table vehicle itself

Attributes of ace region come under the table rental location

Star schema will have the fact table ace rent a car in centre and dimension tables as rental location, customer, vehicle and data Warehouse as dimension tables.