Sample Employee database ported to SQLite from MySQL

classic Classic list List threaded Threaded
18 messages Options
Reply | Threaded
Open this post in threaded view
|

Sample Employee database ported to SQLite from MySQL

Arun - Siara Logics (cc)
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sample Employee database ported to SQLite from MySQL

Chris Locke-3
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) <[hidden email]>
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sample Employee database ported to SQLite from MySQL

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  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 <[hidden email]> 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) <[hidden email]>
 > 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
 > > [hidden email]
 > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 > >
 > _______________________________________________
 > sqlite-users mailing list
 > [hidden email]
 > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 >


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sample Employee database ported to SQLite from MySQL

Shawn Wagner
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) <[hidden email] 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 <
> [hidden email]> 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) <[hidden email]
> >
>  > 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
>  > > [hidden email]
>  > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>  > >
>  > _______________________________________________
>  > sqlite-users mailing list
>  > [hidden email]
>  > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>  >
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Sample Employee database ported to SQLite from MySQL

Hick Gunter
In reply to this post by Chris Locke-3
Perhaps it is designed to be "somewhat nonstandard" in order to enable edge cases in testing.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Chris Locke
Gesendet: Mittwoch, 19. Dezember 2018 11:55
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] 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) <[hidden email]>
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.p
> ng?raw=true
>
> Regards
> Arun - Siara Logics (cc)
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sample Employee database ported to SQLite from MySQL

Chris Locke-3
In reply to this post by Shawn Wagner
> 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 <[hidden email]>
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) <[hidden email]
> 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 <
> > [hidden email]> 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)
> <[hidden email]
> > >
> >  > 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
> >  > > [hidden email]
> >  > >
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >  > >
> >  > _______________________________________________
> >  > sqlite-users mailing list
> >  > [hidden email]
> >  > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >  >
> >
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sample Employee database ported to SQLite from MySQL

James K. Lowden
In reply to this post by Chris Locke-3
On Wed, 19 Dec 2018 10:55:11 +0000
Chris Locke <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sample Employee database ported to SQLite from MySQL

Chris Locke-3
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 <[hidden email]>
wrote:

> On Wed, 19 Dec 2018 10:55:11 +0000
> Chris Locke <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sample Employee database ported to SQLite from MySQL

Chris Locke-3
> 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 <[hidden email]>
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 <[hidden email]>
> wrote:
>
>> On Wed, 19 Dec 2018 10:55:11 +0000
>> Chris Locke <[hidden email]> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sample Employee database ported to SQLite from MySQL

Arun - Siara Logics (cc)
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 <[hidden email]> 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 <[hidden email]>
 > 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 <[hidden email]>
 > > wrote:
 > >
 > >> On Wed, 19 Dec 2018 10:55:11 +0000
 > >> Chris Locke <[hidden email]> 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
 > >> [hidden email]
 > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 > >>
 > >
 > _______________________________________________
 > sqlite-users mailing list
 > [hidden email]
 > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 >


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sample Employee database ported to SQLite from MySQL

Stephen Chrzanowski
In reply to this post by Chris Locke-3
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 <[hidden email]>
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 <[hidden email]>
> 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 <
> [hidden email]>
> > wrote:
> >
> >> On Wed, 19 Dec 2018 10:55:11 +0000
> >> Chris Locke <[hidden email]> 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
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sample Employee database ported to SQLite from MySQL

James K. Lowden
In reply to this post by Chris Locke-3
On Thu, 20 Dec 2018 15:42:27 +0000
Chris Locke <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sample Employee database ported to SQLite from MySQL

Simon Slavin-3
On 21 Dec 2018, at 5:54pm, James K. Lowden <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sample Employee database ported to SQLite from MySQL

Jungle Boogie
In reply to this post by Arun - Siara Logics (cc)
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sample Employee database ported to SQLite from MySQL

Larry Brasfield
In reply to this post by Arun - Siara Logics (cc)
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sample Employee database ported to SQLite from MySQL

Arun - Siara Logics (cc)
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 <[hidden email]> 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
 > [hidden email]
 > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 >


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sample Employee database ported to SQLite from MySQL

Jungle Boogie
In reply to this post by Larry Brasfield
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sample Employee database ported to SQLite from MySQL

Arun - Siara Logics (cc)
In reply to this post by Chris Locke-3
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_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 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   DATE            NOT NULL,
    to_date     DATE            NOT 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 <[hidden email]> 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 <[hidden email]>
 >  > 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 <[hidden email]>
 >  > > wrote:
 >  > >
 >  > >> On Wed, 19 Dec 2018 10:55:11 +0000
 >  > >> Chris Locke <[hidden email]> 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
 >  > >> [hidden email]
 >  > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 
 >  > >>
 >  > >
 >  > _______________________________________________
 >  > sqlite-users mailing list
 >  > [hidden email]
 >  > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 
 >  >  
 >


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users