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 > <sql...@chrisjlocke.co.uk> 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 <sql...@chrisjlocke.co.uk> > > 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 +0000 > > >> Chris Locke <sql...@chrisjlocke.co.uk> 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
