Re: [sqlite] Sample Employee database ported to SQLite from MySQL
I have modified the database and updated the repository at https://github.com/siara-cc/employee_db. Given below is the revised script. Regards Arun CREATE TABLE employees ( emp_id INTEGER NOT NULL, birth_date DATE NOT NULL, first_name TEXT NOT NULL, last_name TEXT NOT NULL, gender CHAR NOT NULL check(gender="M" or gender="F"), hire_date DATE NOT NULL, PRIMARY KEY (emp_id) ); CREATE TABLE departments ( dept_id INTEGER NOT NULL, dept_name TEXT NOT NULL, PRIMARY KEY (dept_id), CONSTRAINT dept_name_unique UNIQUE (dept_name) ); CREATE TABLE dept_manager ( dept_id INTEGER NOT NULL, emp_id INTEGER NOT NULL, from_dateDATE NOT NULL, to_date DATE NOT NULL, FOREIGN KEY (emp_id) REFERENCES employees (emp_id)ON DELETE CASCADE, FOREIGN KEY (dept_id) REFERENCES departments (dept_id) ON DELETE CASCADE, PRIMARY KEY (emp_id, dept_id) ); CREATE TABLE dept_emp ( emp_id INTEGER NOT NULL, dept_id INTEGER NOT NULL, from_date DATE NOT NULL, to_date DATE NOT NULL, FOREIGN KEY (emp_id) REFERENCES employees (emp_id) ON DELETE CASCADE, FOREIGN KEY (dept_id) REFERENCES departments (dept_id) ON DELETE CASCADE, PRIMARY KEY (emp_id, dept_id) ); CREATE TABLE titles ( emp_id INTEGER NOT NULL, title TEXT NOT NULL, from_date DATE NOT NULL, to_date DATE, FOREIGN KEY (emp_id) REFERENCES employees (emp_id) ON DELETE CASCADE, PRIMARY KEY (emp_id,title, from_date) ); CREATE TABLE salaries ( emp_id INTEGER NOT NULL, salary NUMBER NOT NULL, from_date DATENOT NULL, to_date DATENOT NULL, FOREIGN KEY (emp_id) REFERENCES employees (emp_id) ON DELETE CASCADE, PRIMARY KEY (emp_id, from_date) ); > On Thu, 20 Dec 2018 21:13:47 +0530 Chris Locke > wrote > > > Just because something doesn't have to be calculated, means that it has > > to be stored as text. > > > > Sorry - forgot a 'doesn't'. > > Just because something doesn't have to be calculated, doesn't mean that > it > > has to be stored as text. > > > > On Thu, Dec 20, 2018 at 3:42 PM Chris Locke > > wrote: > > > > > Just because something doesn't have to be calculated, means that it has > to > > > be stored as text. > > > Its usually recommended to set the column affinity to the type of data > > > you're storing. If you're storing a number (and a model number is a > > > numeric number) then it should be stored in a numeric field. If your > model > > > number has punctuation, then yes, a text field is required. > > > Its up to the application (although some would also argue the database) > to > > > validate data input, ie, ensure numeric data was inputted into a > numeric > > > field. > > > > > > On Thu, Dec 20, 2018 at 3:31 PM James K. Lowden > > > > wrote: > > > > > >> On Wed, 19 Dec 2018 10:55:11 + > > >> Chris Locke wrote: > > >> > > >> > 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 > > >> > > >> Lots of numbers are labels that aren't meant to be calculated on. > Item > > >> number, part number, model number, serial number, order number. > > >> Anything that needs to be distinguished and isn't worth naming. > > >> > > >> It's never a good idea to store such numbers as numerical types. > > >> There's always a potential loss of information, be it the leading > zero > > >> or embedded '-' or multiple '.' characters. Unless the "number" is a > > >> quantity, for compuational purposes it's text. > > >> > > >> --jkl > > >> > > >> ___ > > >> 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
Re: [sqlite] Sample Employee database ported to SQLite from MySQL
On Sun 23 Dec 2018 7:15 PM, Larry Brasfield wrote: > Jungle Boogie wrote: > ➢ Anyone else have issues decompressing the file? $ bzip2 -d employees.db.bz2 > bzip2: employees.db.bz2 is not a bzip2 file. bunzip2 employees.db.bz2 > bunzip2: employees.db.bz2 is not a bzip2 file. $ file employees.db.bz2 > employees.db.bz2: Non-ISO extended-ASCII HTML document text $ sha256 > employees.db.bz2 SHA256 (employees.db.bz2) = > 2c24eaa81d65459ec412e1e4e7a0955349f40ccff02abe98b72b0af5e84495f2 > > I browsed to the web page, link to which you quoted. At that page (on > GitHub) I clicked the “Clone or download” button, then clicked the “Download > ZIP” option, whereupon a .zip file could be downloaded. Within that .zip > archive, in a subdirectory, was a file which appeared as follows to the > ‘file’ utility: > > file employees.db > employees.db: SQLite 3.x database I think my problem that I was using the wrong URL. Correct URL to fetch is this: https://github.com/siara-cc/employee_db/raw/master/employees.db.bz2 Thanks Larry and Arun. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sample Employee database ported to SQLite from MySQL
I simply did a bzip2 of the db file, uploaded it using "git add" and "git commit". I just downloaded it now from "View Raw" and the shasum of the downloaded file matches with what I uploaded. shasum -a 256 ~/Downloads/employees.db.bz2 dd0b87d339494d2ee2f21a66a3b253f58c1a5517e7705427382c0ff8c5a6ada1 /Users/arun/Downloads/employees.db.bz2 and bzip2 -t does not report any errors. I guess you probably got the link using "Copy link" and used a wget utility or something. Not sure why it does not work for you (Jungle Boogle). Regards Arun On Mon, 24 Dec 2018 05:45:44 +0530 Larry Brasfield wrote > Jungle Boogie wrote: > ➢ Anyone else have issues decompressing the file? $ bzip2 -d > employees.db.bz2 bzip2: employees.db.bz2 is not a bzip2 file. bunzip2 > employees.db.bz2 bunzip2: employees.db.bz2 is not a bzip2 file. $ file > employees.db.bz2 employees.db.bz2: Non-ISO extended-ASCII HTML document text > $ sha256 employees.db.bz2 SHA256 (employees.db.bz2) = > 2c24eaa81d65459ec412e1e4e7a0955349f40ccff02abe98b72b0af5e84495f2 > > I browsed to the web page, link to which you quoted. At that page (on > GitHub) I clicked the “Clone or download” button, then clicked the “Download > ZIP” option, whereupon a .zip file could be downloaded. Within that .zip > archive, in a subdirectory, was a file which appeared as follows to the > ‘file’ utility: > > file employees.db > employees.db: SQLite 3.x database > > Better yet, sqlite3 v22 thinks it is a valid database. > > I don’t know what you did, but the evidence suggests you simply grabbed > whatever the server dished up under the http(s) protocol for the given link. > ___ > 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
Re: [sqlite] Sample Employee database ported to SQLite from MySQL
Jungle Boogie wrote: ➢ Anyone else have issues decompressing the file? $ bzip2 -d employees.db.bz2 bzip2: employees.db.bz2 is not a bzip2 file. bunzip2 employees.db.bz2 bunzip2: employees.db.bz2 is not a bzip2 file. $ file employees.db.bz2 employees.db.bz2: Non-ISO extended-ASCII HTML document text $ sha256 employees.db.bz2 SHA256 (employees.db.bz2) = 2c24eaa81d65459ec412e1e4e7a0955349f40ccff02abe98b72b0af5e84495f2 I browsed to the web page, link to which you quoted. At that page (on GitHub) I clicked the “Clone or download” button, then clicked the “Download ZIP” option, whereupon a .zip file could be downloaded. Within that .zip archive, in a subdirectory, was a file which appeared as follows to the ‘file’ utility: > file employees.db employees.db: SQLite 3.x database Better yet, sqlite3 v22 thinks it is a valid database. I don’t know what you did, but the evidence suggests you simply grabbed whatever the server dished up under the http(s) protocol for the given link. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sample Employee database ported to SQLite from MySQL
On Wed 19 Dec 2018 2:34 PM, Arun - Siara Logics (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. > Anyone else have issues decompressing the file? $ bzip2 -d employees.db.bz2 bzip2: employees.db.bz2 is not a bzip2 file. bunzip2 employees.db.bz2 bunzip2: employees.db.bz2 is not a bzip2 file. $ file employees.db.bz2 employees.db.bz2: Non-ISO extended-ASCII HTML document text $ sha256 employees.db.bz2 SHA256 (employees.db.bz2) = 2c24eaa81d65459ec412e1e4e7a0955349f40ccff02abe98b72b0af5e84495f2 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sample Employee database ported to SQLite from MySQL
On 21 Dec 2018, at 5:54pm, James K. Lowden wrote: > If the assigning body didn't intend the "number" as a quantity, it's > not. Treating it as such will often come to tears. I've seen this argument phrased as "Are you going to do maths on it ? If not, don't store it as a number.". And yes, there are things called "number" which turn out to have hyphens and letters in. It's a colloquialism. (Apologies to readers who don't have English as their first language.) Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sample Employee database ported to SQLite from MySQL
On Thu, 20 Dec 2018 15:42:27 + Chris Locke wrote: > and a model number is a numeric number My phone's model number is VVX 500. > set the column affinity to the type of data Yes, and not everything that looks like a number is a number. Some things that start out looking like numbers change over time in ways that make them non-numeric. If the assigning body didn't intend the "number" as a quantity, it's not. Treating it as such will often come to tears. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sample Employee database ported to SQLite from MySQL
Right up until procedures change and the identifier has changed from an integer only data type to something that starts containing characters. There's so many different types of UPCs out there, for example, that a company can switch from an integer only type of UPC to something that contains letters. I'd err on the side that if there is even the hint that something in the real world could change to what my schema isn't going to allow for, then, make it as open-ended as possible, even going as far as changing the field to a BLOB and have the softwares future functionality be able to store the exact image that was scanned in. On Thu, Dec 20, 2018 at 10:44 AM Chris Locke wrote: > > Just because something doesn't have to be calculated, means that it has > to be stored as text. > > Sorry - forgot a 'doesn't'. > Just because something doesn't have to be calculated, doesn't mean that it > has to be stored as text. > > On Thu, Dec 20, 2018 at 3:42 PM Chris Locke > wrote: > > > Just because something doesn't have to be calculated, means that it has > to > > be stored as text. > > Its usually recommended to set the column affinity to the type of data > > you're storing. If you're storing a number (and a model number is a > > numeric number) then it should be stored in a numeric field. If your > model > > number has punctuation, then yes, a text field is required. > > Its up to the application (although some would also argue the database) > to > > validate data input, ie, ensure numeric data was inputted into a numeric > > field. > > > > On Thu, Dec 20, 2018 at 3:31 PM James K. Lowden < > jklow...@schemamania.org> > > wrote: > > > >> On Wed, 19 Dec 2018 10:55:11 + > >> Chris Locke wrote: > >> > >> > 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 > >> > >> Lots of numbers are labels that aren't meant to be calculated on. Item > >> number, part number, model number, serial number, order number. > >> Anything that needs to be distinguished and isn't worth naming. > >> > >> It's never a good idea to store such numbers as numerical types. > >> There's always a potential loss of information, be it the leading zero > >> or embedded '-' or multiple '.' characters. Unless the "number" is a > >> quantity, for compuational purposes it's text. > >> > >> --jkl > >> > >> ___ > >> 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
Re: [sqlite] Sample Employee database ported to SQLite from MySQL
Hi Chris, James, Shawn, Thanks for the suggestions - I think they are quite relevant. I will modify and update soon. I kept the page size as 512 without rowid and dropped foreign keys so I could use it to demo SQLite on a low memory SoC ESP8266 (see https://github.com/siara-cc/esp_arduino_sqlite3_lib/). Since this db would be useful for a larger audience, I will keep separate copies. Regards Arun On Thu, 20 Dec 2018 21:13:47 +0530 Chris Locke wrote > > Just because something doesn't have to be calculated, means that it has > to be stored as text. > > Sorry - forgot a 'doesn't'. > Just because something doesn't have to be calculated, doesn't mean that it > has to be stored as text. > > On Thu, Dec 20, 2018 at 3:42 PM Chris Locke > wrote: > > > Just because something doesn't have to be calculated, means that it has to > > be stored as text. > > Its usually recommended to set the column affinity to the type of data > > you're storing. If you're storing a number (and a model number is a > > numeric number) then it should be stored in a numeric field. If your model > > number has punctuation, then yes, a text field is required. > > Its up to the application (although some would also argue the database) to > > validate data input, ie, ensure numeric data was inputted into a numeric > > field. > > > > On Thu, Dec 20, 2018 at 3:31 PM James K. Lowden > > wrote: > > > >> On Wed, 19 Dec 2018 10:55:11 + > >> Chris Locke wrote: > >> > >> > 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 > >> > >> Lots of numbers are labels that aren't meant to be calculated on. Item > >> number, part number, model number, serial number, order number. > >> Anything that needs to be distinguished and isn't worth naming. > >> > >> It's never a good idea to store such numbers as numerical types. > >> There's always a potential loss of information, be it the leading zero > >> or embedded '-' or multiple '.' characters. Unless the "number" is a > >> quantity, for compuational purposes it's text. > >> > >> --jkl > >> > >> ___ > >> 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
Re: [sqlite] Sample Employee database ported to SQLite from MySQL
> Just because something doesn't have to be calculated, means that it has to be stored as text. Sorry - forgot a 'doesn't'. Just because something doesn't have to be calculated, doesn't mean that it has to be stored as text. On Thu, Dec 20, 2018 at 3:42 PM Chris Locke wrote: > Just because something doesn't have to be calculated, means that it has to > be stored as text. > Its usually recommended to set the column affinity to the type of data > you're storing. If you're storing a number (and a model number is a > numeric number) then it should be stored in a numeric field. If your model > number has punctuation, then yes, a text field is required. > Its up to the application (although some would also argue the database) to > validate data input, ie, ensure numeric data was inputted into a numeric > field. > > On Thu, Dec 20, 2018 at 3:31 PM James K. Lowden > wrote: > >> On Wed, 19 Dec 2018 10:55:11 + >> Chris Locke wrote: >> >> > 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 >> >> Lots of numbers are labels that aren't meant to be calculated on. Item >> number, part number, model number, serial number, order number. >> Anything that needs to be distinguished and isn't worth naming. >> >> It's never a good idea to store such numbers as numerical types. >> There's always a potential loss of information, be it the leading zero >> or embedded '-' or multiple '.' characters. Unless the "number" is a >> quantity, for compuational purposes it's text. >> >> --jkl >> >> ___ >> 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
Re: [sqlite] Sample Employee database ported to SQLite from MySQL
Just because something doesn't have to be calculated, means that it has to be stored as text. Its usually recommended to set the column affinity to the type of data you're storing. If you're storing a number (and a model number is a numeric number) then it should be stored in a numeric field. If your model number has punctuation, then yes, a text field is required. Its up to the application (although some would also argue the database) to validate data input, ie, ensure numeric data was inputted into a numeric field. On Thu, Dec 20, 2018 at 3:31 PM James K. Lowden wrote: > On Wed, 19 Dec 2018 10:55:11 + > Chris Locke wrote: > > > 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 > > Lots of numbers are labels that aren't meant to be calculated on. Item > number, part number, model number, serial number, order number. > Anything that needs to be distinguished and isn't worth naming. > > It's never a good idea to store such numbers as numerical types. > There's always a potential loss of information, be it the leading zero > or embedded '-' or multiple '.' characters. Unless the "number" is a > quantity, for compuational purposes it's text. > > --jkl > > ___ > 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
Re: [sqlite] Sample Employee database ported to SQLite from MySQL
On Wed, 19 Dec 2018 10:55:11 + Chris Locke wrote: > 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 Lots of numbers are labels that aren't meant to be calculated on. Item number, part number, model number, serial number, order number. Anything that needs to be distinguished and isn't worth naming. It's never a good idea to store such numbers as numerical types. There's always a potential loss of information, be it the leading zero or embedded '-' or multiple '.' characters. Unless the "number" is a quantity, for compuational purposes it's text. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sample Employee database ported to SQLite from MySQL
> 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 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) 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 DATENOT NULL, > > first_name VARCHAR(14) NOT NULL, > > last_name VARCHAR(16) NOT NULL, > > gender CHAR(1) NOT NULL, > > hire_date DATENOT 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_dateDATENOT NULL, > >to_date DATENOT 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 DATENOT NULL, > > to_date DATENOT NULL, > > PRIMARY KEY (emp_no,dept_no) > > ) without rowid; > > CREATE TABLE titles ( > > emp_no INT NOT NULL, > > title VARCHAR(50) NOT NULL, > > from_date DATENOT 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 DATENOT NULL, > > to_date DATENOT 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) > > > > > > 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 > > > > >
Re: [sqlite] Sample Employee database ported to SQLite from MySQL
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) 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 DATENOT NULL, > first_name VARCHAR(14) NOT NULL, > last_name VARCHAR(16) NOT NULL, > gender CHAR(1) NOT NULL, > hire_date DATENOT 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_dateDATENOT NULL, >to_date DATENOT 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 DATENOT NULL, > to_date DATENOT NULL, > PRIMARY KEY (emp_no,dept_no) > ) without rowid; > CREATE TABLE titles ( > emp_no INT NOT NULL, > title VARCHAR(50) NOT NULL, > from_date DATENOT 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 DATENOT NULL, > to_date DATENOT 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) > > > 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
Re: [sqlite] Sample Employee database ported to SQLite from MySQL
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 DATENOT NULL, first_name VARCHAR(14) NOT NULL, last_name VARCHAR(16) NOT NULL, gender CHAR(1) NOT NULL, hire_date DATENOT 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_dateDATENOT NULL, to_date DATENOT 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 DATENOT NULL, to_date DATENOT NULL, PRIMARY KEY (emp_no,dept_no) ) without rowid; CREATE TABLE titles ( emp_no INT NOT NULL, title VARCHAR(50) NOT NULL, from_date DATENOT 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 DATENOT NULL, to_date DATENOT 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 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) > 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
Re: [sqlite] Sample Employee database ported to SQLite from MySQL
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) 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
