> Then add foreign key constraints so the relations between the tables > are explicit...
On the GitHub page for the database, it states that, "RowIds, Foreign keys, secondary keys, defaults and cascade have not been ported." Most of the tools to create a 'proper' database... But otherwise, an interesting concept. "The data was generated, and as such there are inconsistencies and subtle problems. Rather than removing them, we decided to leave the contents untouched, and use these issues as data cleaning exercises." Chris On Wed, Dec 19, 2018 at 12:16 PM Shawn Wagner <shawnw.mob...@gmail.com> wrote: > I'd start by making the employees table a normal rowid one with an INTEGER > PRIMARY KEY (*Not* INT) column, and change all those VARCHAR, CHAR and DATE > column types to TEXT (or NUMERIC for the dates depending on the values they > hold). > > Then add foreign key constraints so the relations between the tables are > explicit... > > On Wed, Dec 19, 2018, 4:06 AM Arun - Siara Logics (cc) <a...@siara.cc > wrote: > > > Hi Chris, > > > > I don't own the MySQL side of the db, but its easy for me to change > > anything on Sqlite side. To me the data looks decent for testing and > > creating applications for demo or learning. > > > > I am giving below the script and I will incorporate any other suggestions > > you may come up with: > > > > CREATE TABLE employees ( > > emp_no INT NOT NULL, > > birth_date DATE NOT NULL, > > first_name VARCHAR(14) NOT NULL, > > last_name VARCHAR(16) NOT NULL, > > gender CHAR(1) NOT NULL, > > hire_date DATE NOT NULL, > > PRIMARY KEY (emp_no) > > ) without rowid; > > CREATE TABLE departments ( > > dept_no CHAR(4) NOT NULL, > > dept_name VARCHAR(40) NOT NULL, > > PRIMARY KEY (dept_no) > > ) without rowid; > > CREATE TABLE dept_manager ( > > dept_no CHAR(4) NOT NULL, > > emp_no INT NOT NULL, > > from_date DATE NOT NULL, > > to_date DATE NOT NULL, > > PRIMARY KEY (emp_no, dept_no) > > ) without rowid; > > CREATE TABLE dept_emp ( > > emp_no INT NOT NULL, > > dept_no CHAR(4) NOT NULL, > > from_date DATE NOT NULL, > > to_date DATE NOT NULL, > > PRIMARY KEY (emp_no,dept_no) > > ) without rowid; > > CREATE TABLE titles ( > > emp_no INT NOT NULL, > > title VARCHAR(50) NOT NULL, > > from_date DATE NOT NULL, > > to_date DATE, > > PRIMARY KEY (emp_no,title, from_date) > > ) without rowid; > > CREATE TABLE salaries ( > > emp_no INT NOT NULL, > > salary INT NOT NULL, > > from_date DATE NOT NULL, > > to_date DATE NOT NULL, > > PRIMARY KEY (emp_no, from_date) > > ) without rowid; > > CREATE INDEX emp_first_name on employees (first_name); > > CREATE INDEX emp_last_name on employees (last_name); > > > > Regards > > Arun > > > > ---- On Wed, 19 Dec 2018 16:25:11 +0530 Chris Locke < > > sql...@chrisjlocke.co.uk> wrote ---- > > > The scheme (for me) is like nails on a chalkboard. 'dept_no' but > > defined > > > as a 'CHAR', then 'emp_no' as an INT. > > > Fields with '_no' are read as 'number' and so should be a number. OK, > > that > > > doesn't always work for 'telephone_no' (they usually start with a 0 > ... > > > well, they do in the UK where I am...) > > > But I digress...... > > > > > > > > > Chris > > > > > > > > > On Wed, Dec 19, 2018 at 9:05 AM Arun - Siara Logics (cc) > <a...@siara.cc > > > > > > wrote: > > > > > > > This project (https://github.com/siara-cc/employee_db) hosts the > > Sqlite3 > > > > db file ported from mysql test_db found at > > > > https://github.com/datacharmer/test_db. It can be used to test your > > > > applications and database servers. To use this project, download > > > > employees.db.bz2, unzip and open using sqlite3 command line tool. > > > > > > > > The original data was created by Fusheng Wang and Carlo Zaniolo at > > Siemens > > > > Corporate Research. The data is in XML format. > > > > http://timecenter.cs.aau.dk/software.htm > > > > > > > > Giuseppe Maxia made the relational schema and Patrick Crews exported > > the > > > > data in relational format. > > > > > > > > The database contains about 300,000 employee records with 2.8 > million > > > > salary entries. The export data is 167 MB, which is not huge, but > > heavy > > > > enough to be non-trivial for testing. > > > > > > > > A picture of the schema can be found at: > > > > > > > https://github.com/siara-cc/employee_db/blob/master/employees-schema.png?raw=true > > > > > > > > Regards > > > > Arun - Siara Logics (cc) > > > > > > > > > > > > _______________________________________________ > > > > sqlite-users mailing list > > > > sqlite-users@mailinglists.sqlite.org > > > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > _______________________________________________ > > > sqlite-users mailing list > > > sqlite-users@mailinglists.sqlite.org > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
