Crear una base de datos en SQL Server
software
Luid Chart
Trabajo Previo:
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.
Reporte tipo Noticia
Reporte tipo policíaco
Después de revisar el tipo de información que puedo obtener de las publicaciones de Facebook, planeé las estructuras de datos en Lucidchart. (necesito un softwate alternativo gratuito)
Lucid chart
Codigo
-- 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)
);
Notas
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.