spelling-appDB.sql

drop table if exists
accounts,
roles,
account_roles,
sessions,
spelling_table
cascade;

CREATE TABLE accounts (
	user_id serial PRIMARY KEY,
	firstName VARCHAR ( 100 ) NOT NULL,
	lastName VARCHAR ( 100 )  NOT NULL,
	userName VARCHAR ( 100 ) UNIQUE NOT NULL,
	password VARCHAR ( 100 ) NOT NULL,
	email VARCHAR ( 255 )  NOT NULL,
	created_on TIMESTAMP,
    last_login TIMESTAMP 
);

CREATE TABLE roles(
   role_id serial PRIMARY KEY,
   role_name VARCHAR (255) UNIQUE NOT NULL
);

CREATE TABLE account_roles (
  user_id INT NOT NULL,
  role_id INT NOT NULL,
  grant_date TIMESTAMP,
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (role_id) REFERENCES roles (role_id),
  FOREIGN KEY (user_id) REFERENCES accounts (user_id)
);


CREATE TABLE sessions (
session_id serial PRIMARY KEY,
user_id INT REFERENCES accounts (user_id),
correctWordsList TEXT,
wrongWordsList TEXT,
number_of_correct_words INT,
number_of_incorrect_words INT,
session_accuracy_percentage NUMERIC(5,2),
percentage_sign VARCHAR(1) DEFAULT '%'
);

CREATE TABLE spelling_table(
word_id serial PRIMARY KEY,
year3And4Words varchar(50),
year5And6Words varchar(50)
);


INSERT INTO spelling_table(year3And4Words, year5And6Words)
VALUES
  ('accident', 'accompany'),
  ('accidentally', 'according'),
  ('actual', 'achieve'),
  ('actually', 'aggressive'),
  ('address', 'amateur'),
  ('answer', 'ancient'),
  ('appear', 'apparent'),
  ('arrive', 'appreciate'),
  ('believe', 'attached'),
  ('bicycle', 'available'),
  ('breath', 'average'),
  ('breathe', 'awkward'),
  ('build', 'bargain'),
  ('busy', 'bruise'),
  ('business', 'category'),
  ('calendar', 'cemetery'),
  ('caught', 'committee'),
  ('centre', 'communicate'),
  ('century', 'community'),
  ('certain', 'competition'),
  ('circle', 'conscience'),
  ('complete', 'conscious'),
  ('consider', 'controversy'),
  ('continue', 'convenience'),
  ('decide', 'correspond'),
  ('describe', 'criticise'),
  ('different', 'curiosity'),
  ('difficult', 'definite'),
  ('disappear', 'desperate'),
  ('early', 'determined'),
  ('earth', 'develop'),
  ('eight', 'dictionary'),
  ('eighth', 'disastrous'),
  ('enough', 'embarrass'),
  ('exercise', 'environment'),
  ('experience', 'equip'),
  ('experiment', 'especially'),
  ('extreme', 'exaggerate'),
  ('famous', 'excellent'),
  ('favourite', 'existence'),
  ('February', 'explanation'),
  ('forward', 'familiar'),
  ('forwards', 'foreign'),
  ('fruit', 'forty'),
  ('grammar', 'frequently'),
  ('group', 'government'),
  ('guard', 'guarantee'),
  ('guide', 'harass'),
  ('heard', 'hindrance'),
  ('heart', 'identity'),
  ('height', 'immediate'),
  ('history', 'individual'),
  ('imagine', 'interfere'),
  ('increase', 'interrupt'),
  ('important', 'language'),
  ('interest', 'leisure'),
  ('island', 'lightning'),
  ('knowledge', 'marvellous'),
  ('learn', 'mischievous'),
  ('length', 'muscle'),
  ('library', 'necessary'),
  ('material', 'neighbour'),
  ('medicine', 'nuisance'),
  ('mention', 'occupy'),
  ('minute', 'occur'),
  ('natural', 'opportunity'),
  ('naughty', 'parliament'),
  ('notice', 'persuade'),
  ('occasion', 'physical'),
  ('occasionally', 'prejudice'),
  ('often', 'privilege'),
  ('opposite', 'profession'),
  ('ordinary', 'programme'),
  ('particular', 'pronunciation'),
  ('peculiar', 'queue'),
  ('perhaps', 'recognise'),
  ('popular', 'recommend'),
  ('position', 'relevant'),
  ('possess', 'restaurant'),
  ('possession', 'rhyme'),
  ('possible', 'rhythm'),
  ('potatoes', 'sacrifice'),
  ('pressure', 'secretary'),
  ('probably', 'shoulder'),
  ('promise', 'signature'),
  ('purpose', 'sincere'),
  ('quarter', 'soldier'),
  ('question', 'stomach'),
  ('recent', 'sufficient'),
  ('regular', 'suggest'),
  ('reign', 'symbol'),
  ('remember', 'system'),
  ('sentence', 'temperature'),
  ('separate', 'thorough'),
  ('special', 'twelfth'),
  ('straight', 'variety'),
  ('strange', 'vegetable'),
  ('strength', 'vehicle'),
  ('suppose', 'yacht'),
  ('surprise', ''),
  ('therefore', ''),
  ('though', ''),
  ('although', ''),
  ('thought', ''),
  ('through', ''),
  ('various', ''),
  ('weight', ''),
  ('woman', ''),
  ('women', '');