• SQL server

• SQl manegement studio 

• Luid Chart

La fuente de datos iba a ser cualquier publicación de Facebook que reportara, pero existen 2 tipos de publicaciones (quizás 3 o más): una que reporta las noticias y otra que es más parecida a un informe policial, que incluye más detalles que se pueden utilizar para la base de datos.

-- Use the CrashesDB database

USE CrashesDB;

-- Table: Country

CREATE TABLE Country (

    country_code CHAR(3) PRIMARY KEY,

    country_name VARCHAR(50) NOT NULL

);

-- Table: State

CREATE TABLE State (

    id INT PRIMARY KEY IDENTITY(1,1),

    state_name VARCHAR(50),

    country_code CHAR(3) NOT NULL,

    CONSTRAINT FK_State_Country FOREIGN KEY (country_code) REFERENCES Country(country_code)

);

-- Table: Incident

CREATE TABLE Incident (

    id INT PRIMARY KEY IDENTITY(1,1),

    date DATE NOT NULL,

    location GEOGRAPHY NOT NULL

);

-- Table: Persons_Involved

CREATE TABLE Persons_Involved (

    id INT PRIMARY KEY IDENTITY(1,1),

    first_name VARCHAR(50) NOT NULL,

    last_name VARCHAR(50),

    year_of_birth INT,

    country_code CHAR(3),

    sex CHAR(1),

    CONSTRAINT FK_Persons_Country FOREIGN KEY (country_code) REFERENCES Country(country_code)

);

-- Table: People_Injured

CREATE TABLE People_Injured (

    id INT PRIMARY KEY IDENTITY(1,1),

    person_id INT NOT NULL,

    incident_id INT NOT NULL,

    injury_severity VARCHAR(50),

    injury_description TEXT,

    CONSTRAINT FK_PeopleInjured_Person FOREIGN KEY (person_id) REFERENCES Persons_Involved(id),

    CONSTRAINT FK_PeopleInjured_Incident FOREIGN KEY (incident_id) REFERENCES Incident(id)

);

-- Table: Fatalities

CREATE TABLE Fatalities (

    id INT PRIMARY KEY IDENTITY(1,1),

    incident_id INT NOT NULL,

    fatalities INT,

    injuries INT,

    CONSTRAINT FK_Fatalities_Incident FOREIGN KEY (incident_id) REFERENCES Incident(id)

);

-- Table: Make

CREATE TABLE Make (

    id INT PRIMARY KEY IDENTITY(1,1),

    make_name VARCHAR(50) NOT NULL

);

-- Table: Model

CREATE TABLE Model (

    id INT PRIMARY KEY IDENTITY(1,1),

    model_name VARCHAR(50) NOT NULL,

    make_id INT NOT NULL,

    CONSTRAINT FK_Model_Make FOREIGN KEY (make_id) REFERENCES Make(id)

);

-- Table: Vehicle_Types

CREATE TABLE Vehicle_Types (

    id INT PRIMARY KEY IDENTITY(1,1),

    type_name VARCHAR(50) NOT NULL

);

-- Table: Vehicles

CREATE TABLE Vehicles (

    id INT PRIMARY KEY IDENTITY(1,1),

    car_color VARCHAR(35) NOT NULL,

    vin VARCHAR(20),

    has_plates BIT NOT NULL,

    car_year INT NULL,

    plate_num VARCHAR(10) NULL,

    plate_country CHAR(3),

    plate_state INT,

    make_id INT NOT NULL,

    model_id INT NOT NULL,

    vehicle_type_id INT NOT NULL,

    CONSTRAINT FK_Vehicles_Country FOREIGN KEY (plate_country) REFERENCES Country(country_code),

    CONSTRAINT FK_Vehicles_State FOREIGN KEY (plate_state) REFERENCES State(id),

    CONSTRAINT FK_Vehicles_Make FOREIGN KEY (make_id) REFERENCES Make(id),

    CONSTRAINT FK_Vehicles_Model FOREIGN KEY (model_id) REFERENCES Model(id),

    CONSTRAINT FK_Vehicles_Type FOREIGN KEY (vehicle_type_id) REFERENCES Vehicle_Types(id)

);

-- Table: Car_Damages

CREATE TABLE Car_Damages (

    id INT PRIMARY KEY IDENTITY(1,1),

    incident_id INT NOT NULL,

    vehicle_id INT NOT NULL,

    driver_id INT NOT NULL,

    is_responsible BIT,

    damage_level VARCHAR(50),

    CONSTRAINT FK_CarDamages_Incident FOREIGN KEY (incident_id) REFERENCES Incident(id),

    CONSTRAINT FK_CarDamages_Vehicle FOREIGN KEY (vehicle_id) REFERENCES Vehicles(id),

    CONSTRAINT FK_CarDamages_Driver FOREIGN KEY (driver_id) REFERENCES Persons_Involved(id)

);

-- Table: Infrastructure_Damages

CREATE TABLE Infrastructure_Damages (

    id INT PRIMARY KEY IDENTITY(1,1),

    infrastructure VARCHAR(100) NOT NULL,

    damage_level VARCHAR(50),

    damage_description TEXT,

    incident_id INT NOT NULL,

    owner_id INT,

    CONSTRAINT FK_InfrastructureDamages_Incident FOREIGN KEY (incident_id) REFERENCES Incident(id),

    CONSTRAINT FK_InfrastructureDamages_Owner FOREIGN KEY (owner_id) REFERENCES Persons_Involved(id)

);

Usar la (GUI) en SQL Server Management Studio para agregar o editar datos ofrece una buena experiencia de usuario, pero para crear tablas a través de la GUI puede llegar a ser fastidioso. Es más eficiente crear tablas utilizando scripts.