Create and insert an ERD showing the constellation ERD schema below. Please see requirements in the next two sections. Determine four to five (non-date/time) dimension tables a. SCDs need to be considered- make sure to include at least one type 2 and one type 3 Consider the date dimensions of different grains, consider a role-playing or bitemporal dimension, if so, explain why this is the case. b. c. For EACH table answer the following question: What kind of SCD is the dimension table and why did you choose this type of SCD? Determine two to three fact tables a. How are the measures tied to your questions? b. Explain if your fact tables cumulative or snapshot C. Which attributes might be degenerative in your fact tables? d. Which attributes in the OLTP schema will transform to measures? e. What measures can be derived/calculated that should be included? Bike Type PK bike_type_id int NOT NULL bike_type varchar(30) Bike Brand PK bike brand id int NOT NULL bike_brand varchar(30) Part_Type PK part type id int NOT NULL part_type varchar(30) PK bike id int NOT NULL OFK bike_type_id int NOT NULL -OFK bike_brand int NOT NULL Bike tire_size int tire_type varchar(40) bike_color varchar(40) Employee PK employee id int NOT NULL last_name varchar(50) NOT NULL first_name varchar(30) NOT NULL Specialty varchar(50) Role varchar(50) Part PK part id int NOT NULL part_name varchar(50) NOT NULL OFK part_type_id INT part vendor varchar(50) unit_cost numeric Service PK service_ld int NOT NULL FK customer_id int NOT NULL FK bike_id int NOT NULL service_start_Date Time timestamp service_end_Date Time timestamp total_service_price numeric Service_Line PK service line Id int NOT NULL FK service_id int NOT NULL FK service_type_ID int NOT NULL FK employee_id int NOT NULL labor_cost numeric Service Part PK/FK service_line_Id int NOT NULL PK/FK part id int NOT NULL part_count int part_cost_at_service numeric Customer PK customer_id int NOT NULL last_name varchar(50) NOT NULL first_name varchar(30) NOT NULL phone varchar(30) NOT NULL email varchar(50) NOT NULL FK address_id int Address PK address id int NOT NULL AddressLine1 varchar(60) AddressLine2 varchar(60) City varchar(60) State ZipCode varchar(15) Service Type PK service type id int NOT NULL service Type_Name varchar(50) External Weather data is to come via an API such as https://openweathermap.org Here is a sample JSON API response to give you a sense of the data returned: https://openweathermap.org/current#current_JSON wwwm { "coord": { "lon": 10.99, "lat": 44.34 }, "weather": [ { "id": 501, "main": "Rain", "description": "moderate rain", "icon": "10d" } ], "base": "stations", "main": { "temp": 298.48, "feels like": 298.74, "temp min": 297.56, "temp max": 300.05, "pressure": 1015, "humidity": 64, "sea level": 1015, "grnd level": 933 }, "visibility": 10000, "wind": { "speed": 0.62, "deg": 349, "gust": 1.18 }, "rain": { "1h": 3.16 }, "clouds": { "all": 100 }, "dt": 1661870592, "sys": { "type": 2, "id": 2075663, "country": "IT", "sunrise": 1661834187, "sunset": 1661882248 }, "timezone": 7200, "id": 3163858, "name": "Zocca", "cod": 200 } winnaam