The data
https://www.kaggle.com/nhtsa/2015-traffic-fatalities?select=nmprior.csv
factor
Accident
Person
Vehicle
Distract
Cevent
Note : only answer the blue Question
The color blue = Individual
The color black = working with a group
1 Group Deliverables
Describe your strategy and allocation of the work among the team members.
[Your Answer]
1.1 Project Descriiption
Provide a brief descriiption of the project based on the selected dataset.
[Your Answer]
1.2 Business Rules
Provide a list of business rules derived from the dataset.
[Your Answer]
1.3 Conceptual Model
Provide a brief descriiption and the ERD (Lucidchart or any CASE tool of choice) of the Conceptual Model.
[Your Answer]
1.4 Conceptual Model
Accident Main table to record accidents
Vehicle Vehicles involved in accidents
Person Persons involved in accidents
Event Events before, during and after accident, describes the accident involving the vehicles and the people
Damage Describes the damage happened to each vehicle.
1.5 Logical Model
1.6 Logical Model
Provide a Logical Model based on the Relational model (use Lucidchart or any CASE tool of choice) and a brief descriiption of the model.
[Your Answer]
1.6.1 Normalized Model
Provided an optimized model for the 3rd Normal Form unless necessary for which a full justification is required.
[Your Answer]
1.7 Physical Model: PostgreSQL
Provide a brief descriiption of the physical model/internal schema using SQL as DDL supported by PostgreSQL database.
[Your Answer]
1.8 Data Population: PostgreSQL
Provide scriipts for populating the tables using sample data to PostgreSQL database.
[Your Answer]
1.9 Views
Provide a View as external schema with a structure which includes attributes form at least 3 entities and a brief descriiption of it.
[Your Answer]
1.10 Data Manipulation
1.10.1 Data Manipulation: Internal Schema
Provide a brief descriiption and the purpose of samples of manipulating (using SQL as supported by PostgreSQL database) data with the list of the results applying insertion, deletion and update operations applied against the internal schema.
[Your Answer]
1.10.2 Data Manipulation: External Schema (View)
Provide a brief descriiption and the purpose of samples of manipulating (using SQL as supported by PostgreSQL database) data with the list of the results applying insertion, deletion and update operations applied against the external schema (view developed above).
[Your Answer]
2 Individual Deliverables
I propose renaming vehicle to vehicular-involvement and adding a vehicle table with VIN as PK.
2.1 Rationale
In the logical model, vehicles are child entities of the accident. This will require to have multiple rows for a vehicle if involved in multiple accidents.
2.2 Comparison
2.2.1 Against the Original Model
Justify how your proposed extension is different from the proposed data model by your team.
My suggestion is towards normalization of the database.
2.2.2 Against Other Team Members Individual Extension
Justify how your proposed extension is different from the proposed extensions by the other team members.
[Your Answer]
2.3 Business Rules
1. Vehicle is involved in an accident: search for vehicle VIN before adding a row for vehicle, if not found add a new row, otherwise use the existing row.
2.4 Conceptual Model
No change in conceptual model as shown above.
2.5 Logical Model
Provide a brief descriiption of the extended Logical model based on the Relational model (use Lucidchart or any CASE tool of choice) optimized for the 3rd Normal Form unless necessary for which a full justification is required.
[Your Answer]
2.5.1 Normalized Model
Provided an optimized model for the 3rd Normal Form unless necessary for which a full justification is required.
[Your Answer]
2.6 Physical Model: PostgreSQL
Provide a brief descriiption of the extended physical model/internal schema using SQL as DDL supported by PostgreSQL database.
[Your Answer]
2.7 Data Population: PostgreSQL
Provide Script to populate the extended tables using sample data to PostgreSQL database.
[Your Answer]
2.8 Views
Provide a View as external schema with a structure which includes attributes form at least 3 entities and the extended one. Describe it.
[Your Answer]
2.9 Data Manipulation
2.9.1 Data Manipulation: Internal Schema
Provide a brief descriiption and the purpose of samples of manipulating (using SQL as supported by PostgreSQL database) data with the list of the results applying insertion, deletion and update operations applied against the internal schema.
[Your Answer]
2.9.2 Data Manipulation: External Schema (View)
Provide a brief descriiption and the purpose of samples of manipulating (using SQL as supported by PostgreSQL database) data with the list of the results applying insertion, deletion and update operations applied against the external schema (view developed above).
[Your Answer]
