data

an anonymous user · November 25, 2022
-- create
CREATE TABLE METADATA (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  file_type TEXT NOT NULL,
  file_name TEXT,
  has_child BOOLEAN NOT NULL
);

CREATE TABLE PARENT_CHILD (
  parent_id INTEGER PRIMARY KEY,
  child_id TEXT 
);

CREATE TABLE SOURCE(
  file_id INTEGER PRIMARY KEY,
  file_name TEXT NOT NULL,
  location TEXT NOT NULL
);

CREATE TABLE case_20 (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  case_id TEXT NOT NULL,
  db_year TEXT NOT NULL
);

CREATE TABLE case_21 (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  case_id TEXT NOT NULL,
  db_year TEXT NOT NULL
);

CREATE TABLE coll_20(
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    case_id TEXT NOT NULL,
    collision_date TEXT NOT NULL

);

CREATE TABLE coll_21(
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    case_id TEXT NOT NULL,
    collision_date TEXT NOT NULL

);

CREATE TABLE coll_22(
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    case_id TEXT NOT NULL,
    collision_date TEXT NOT NULL

);

CREATE TABLE covid_20(
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    date TEXT NOT NULL

);

CREATE TABLE covid_21(
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    date TEXT NOT NULL

);

CREATE TABLE covid_22(
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    date TEXT NOT NULL

);

CREATE TABLE weather_19(
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    date TEXT NOT NULL
);

CREATE TABLE weather_20(
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    date TEXT NOT NULL
);

CREATE TABLE weather_21(
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    date TEXT NOT NULL
);

CREATE TABLE weather_22(
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    date TEXT NOT NULL
);

CREATE TABLE at_fault_0(
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    case_id TEXT NOT NULL,
    at_fault TEXT NOT NULL
);

CREATE TABLE at_fault_1(
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    case_id TEXT NOT NULL,
    at_fault TEXT NOT NULL
);

CREATE TABLE victim(
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    case_id TEXT NOT NULL,
    victim_role TEXT NOT NULL,
    victim_sex TEXT NOT NULL,
    victim_age TEXT NOT NULL
    
);


-- insert
INSERT INTO METADATA (file_type, file_name, has_child) VALUES ('DICTECTORY','', TRUE);
INSERT INTO METADATA (file_type, file_name, has_child) VALUES ('DICTECTORY','user', TRUE);
INSERT INTO METADATA (file_type, file_name, has_child) VALUES ('DICTECTORY', 'ellachen', TRUE);
INSERT INTO METADATA (file_type, file_name, has_child) VALUES ('DICTECTORY', 'testdic', False);
INSERT INTO METADATA (file_type, file_name, has_child) VALUES ('DICTECTORY', '7777777', False);

-- /user/ellachen/testdic
-- 0/ 1  / 2      /  3
-- /7777777
-- 0/ 4
INSERT INTO PARENT_CHILD VALUES(0, '1, 4');
INSERT INTO PARENT_CHILD VALUES(1, '2');
INSERT INTO PARENT_CHILD VALUES(2, '3');
INSERT INTO PARENT_CHILD VALUES(3, '');
-- file only
INSERT INTO SOURCE VALUES(1, 'case_id.csv', '');
INSERT INTO SOURCE VALUES(2, 'collision.csv', '');
INSERT INTO SOURCE VALUES(3, 'LA_County_COVID_Cases.csv', '');
INSERT INTO SOURCE VALUES(4, 'LA_Weather.csv', '');
INSERT INTO SOURCE VALUES(5, 'parties.csv', '');
INSERT INTO SOURCE VALUES(5, 'victims.csv', '');
-- fetch 
SELECT * FROM METADATA;
SELECT * FROM PARENT_CHILD;
SELECT * FROM SOURCE;

-- SELECT * FROM METADATA WHERE id=1;

Output
(Run the program to view its output)

Comments

Please sign up or log in to contribute to the discussion.