Langtrainer: Base de dades

De Wikijoan
Dreceres ràpides: navegació, cerca

L'esquema de la base de dades en MySQL és:

/*
LOGIN(id_login (PK), login, pwd, name, mail, default_lang_w, default_lang_t)
LANGUAGE(id_language (PK), language, abr)
WORD(id_word (PK), id_language(FK), id_login(FK), word, probability,day,day_learnt)
TRANSLATION(id_translation (PK), id_language (FK), id_word (FK), translation, type, sample)
on tipus és : verb, adjective, phraseal verb
QUIZ(id_quiz,id_login,date_time,number_quest,result)
QUIZ_DETAIL(id_quiz,id_quest,id_word,result)
STATS(id_login, date_time)
*/

DROP TABLE QUIZ_DETAIL;
DROP TABLE QUIZ;
DROP TABLE TRANSLATION;
DROP TABLE WORD;
DROP TABLE LOGIN;
DROP TABLE LANGUAGE;

CREATE TABLE LANGUAGE(
id_language smallint primary key,
language varchar(20) not null,
abr varchar(3) not null
);

CREATE TABLE LOGIN(
id_login smallint primary key,
login varchar(10) not null,
pwd varchar(50) not null,
name varchar(30) not null,
mail varchar(30),
default_lang_w smallint not null,
default_lang_t smallint not null
);

ALTER TABLE LOGIN ADD FOREIGN KEY(default_lang_w) REFERENCES LANGUAGE(id_language) ON DELETE CASCADE;
ALTER TABLE LOGIN ADD FOREIGN KEY(default_lang_t) REFERENCES LANGUAGE(id_language) ON DELETE CASCADE;

CREATE TABLE WORD(
id_word smallint primary key,
id_language smallint not null,
id_login smallint not null,
word varchar(25) not null,  
probability tinyint not null default 100,
day date not null,
day_learnt date
);

ALTER TABLE WORD ADD FOREIGN KEY(id_language) REFERENCES LANGUAGE(id_language) ON DELETE CASCADE;
ALTER TABLE WORD ADD FOREIGN KEY(id_login) REFERENCES LOGIN(id_login) ON DELETE CASCADE;

CREATE TABLE TRANSLATION(
id_translation smallint primary key, 
id_language smallint not null,
id_word smallint not null, 
translation varchar(255) not null,
type varchar(15),
sample tinytext
);
# noun, adjective, verb, adverb, preposition, phrasal verb, acronym

ALTER TABLE TRANSLATION ADD FOREIGN KEY(id_language) REFERENCES LANGUAGE(id_language) ON DELETE CASCADE;
ALTER TABLE TRANSLATION ADD FOREIGN KEY(id_word) REFERENCES WORD(id_word) ON DELETE CASCADE;

CREATE TABLE QUIZ(
id_quiz smallint primary key,
id_login smallint not null,
day datetime not null,
number_quest smallint,
result decimal(4,2)
);

ALTER TABLE QUIZ ADD FOREIGN KEY(id_login) REFERENCES LOGIN(id_login) ON DELETE CASCADE;

CREATE TABLE QUIZ_DETAIL(
id_quiz smallint,
id_quest smallint not null,
id_word smallint not null,
result char(1),
PRIMARY KEY (id_quiz, id_quest)
);

ALTER TABLE QUIZ_DETAIL ADD FOREIGN KEY(id_quiz) REFERENCES QUIZ(id_quiz) ON DELETE CASCADE;
ALTER TABLE QUIZ_DETAIL ADD FOREIGN KEY(id_word) REFERENCES WORD(id_word) ON DELETE CASCADE;

INSERT INTO LANGUAGE(id_language,language, abr) VALUES(1,'English','ENG');
INSERT INTO LANGUAGE(id_language,language, abr) VALUES(2,'Català','CAT');

INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(1,'admin','a5302fbf1b07ed51d09716ba5907a179','Admin',1,2);

INSERT INTO WORD(id_word, id_language, id_login, word, probability, day) VALUES(1,1,1,'deserve (to)',100,'2009/09/01');
INSERT INTO WORD(id_word, id_language, id_login, word, probability, day) VALUES(2,1,1,'weird',100,'2009/09/01');
INSERT INTO WORD(id_word, id_language, id_login, word, probability, day) VALUES(3,1,1,'gills',100,'2009/09/01');
INSERT INTO WORD(id_word, id_language, id_login, word, probability, day) VALUES(4,1,1,'draw breath (to)',100,'2009/09/01');
INSERT INTO WORD(id_word, id_language, id_login, word, probability, day) VALUES(5,1,1,'draw (to)',100,'2009/09/01');
INSERT INTO WORD(id_word, id_language, id_login, word, probability, day) VALUES(6,1,1,'snout',100,'2009/09/01');
INSERT INTO WORD(id_word, id_language, id_login, word, probability, day) VALUES(7,1,1,'wound',100,'2009/09/01');
INSERT INTO WORD(id_word, id_language, id_login, word, probability, day) VALUES(8,1,1,'dismiss (to)',100,'2009/09/01');
INSERT INTO WORD(id_word, id_language, id_login, word, probability, day) VALUES(9,1,1,'shame',100,'2009/09/01');
INSERT INTO WORD(id_word, id_language, id_login, word, probability, day) VALUES(10,1,1,'hump',100,'2009/09/01');
INSERT INTO WORD(id_word, id_language, id_login, word, probability, day) VALUES(11,1,1,'lump',100,'2009/09/01');
INSERT INTO WORD(id_word, id_language, id_login, word, probability, day) VALUES(12,1,1,'spit',100,'2009/09/01');

INSERT INTO TRANSLATION(id_translation, id_language, id_word, translation,type,sample) VALUES(1,2,1,'merèixer, ser digne de','v',NULL);
INSERT INTO TRANSLATION(id_translation, id_language, id_word, translation,type,sample) VALUES(2,2,2,'rar, extrany','a',NULL);
INSERT INTO TRANSLATION(id_translation, id_language, id_word, translation,type,sample) VALUES(3,2,3,'branquies, agalles','n','who has either grown gills from spending 45 years underwater or learned to draw breath only once every few weeks: que, després de 45 anys sota l''aigua ha desenvolupat brànquies, o bé ha après a agafar aire només una vegada cada poques setmanes.');
INSERT INTO TRANSLATION(id_translation, id_language, id_word, translation,type,sample) VALUES(4,2,4,'agafar aire','v','who has either grown gills from spending 45 years underwater or learned to draw breath only once every few weeks: que, després de 45 anys sota l''aigua ha desenvolupat brànquies, o bé ha après a agafar aire només una vegada cada poques setmanes.');
INSERT INTO TRANSLATION(id_translation, id_language, id_word, translation,type,sample) VALUES(5,2,5,'dibuixar','v',NULL);
INSERT INTO TRANSLATION(id_translation, id_language, id_word, translation,type,sample) VALUES(6,2,5,'Estirar (pull), treure, atraure','v',NULL);
INSERT INTO TRANSLATION(id_translation, id_language, id_word, translation,type,sample) VALUES(7,2,6,'morro, hocico','n',NULL);
INSERT INTO TRANSLATION(id_translation, id_language, id_word, translation,type,sample) VALUES(8,2,7,'ferida','n',NULL);
INSERT INTO TRANSLATION(id_translation, id_language, id_word, translation,type,sample) VALUES(9,2,7,'ferir (pp, ferit)','v',NULL);
INSERT INTO TRANSLATION(id_translation, id_language, id_word, translation,type,sample) VALUES(10,2,8,'acomiadar, destituir. Refusar','v','the shark dismissed me as worthy of neither fearing nor eating: el tauró em va refusar de considerar-me ni per fer-li por ni per menjar-me');
INSERT INTO TRANSLATION(id_translation, id_language, id_word, translation,type,sample) VALUES(11,2,9,'vergonya, llàstima','n',NULL);
INSERT INTO TRANSLATION(id_translation, id_language, id_word, translation,type,sample) VALUES(12,2,10,'pujol, puig','n','4000 islands, islets, cays, humps, lumps, and spits');
INSERT INTO TRANSLATION(id_translation, id_language, id_word, translation,type,sample) VALUES(13,2,11,'terròs, tros','n','4000 islands, islets, cays, humps, lumps, and spits');
INSERT INTO TRANSLATION(id_translation, id_language, id_word, translation,type,sample) VALUES(14,2,12,'escupinada, saliva','n','4000 islands, islets, cays, humps, lumps, and spits');

COMMIT;

Nota sobre la creació de les taules: per tal que funcioni el ON DELETE CASCADE (busco la màxima integritat de les dades i que no hi hagi incongruències), és necessari crear les taules com a TYPE=InnoDB, i fer les FOREIGN KEY-REFERENCES amb un ALTER TABLE.

Nota sobre la codificació dels caràcters. Per tal de què les paraules surtin amb els accents correctes, s'ha de posar

SET NAMES 'UTF8'

que és un 3 en 1, equival a

SET character_set_client = 'UTF8';
SET character_set_results = 'UTF8';
SET character_set_connection = 'UTF8';

Això ho hauria de ficar a dalt de tot, després del CREATE DATABASE. Clar que el millor seria fer el CREATE DATABASE amb l'opció WITH ENCODING, i així potser ja no caldria... S'ha de documetnar bé tot això.

Nota oct 2011: tot això que explico en principi ja no cal, perquè la manera de crear la bd és:

CREATE DATABASE langtrainer DEFAULT CHARACTER SET utf8;

(abans no ficava DEFAULT CHARACTER SET utf8;). Ara bé, si faig inserts manualment a la bd, i hi ha accents i caràcters estranys, continua sent necessari ficar SET NAMES 'UTF8'

Per fer la pràctica d'anglès empresarial a l'assignatura de IAIG he de crear un login per a cada alumne:

INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(5,'asi2a01','718069449f1acb05475f1dadf50b79ad','asi2a01',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(6,'asi2a02','718069449f1acb05475f1dadf50b79ad','asi2a02',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(7,'asi2a03','718069449f1acb05475f1dadf50b79ad','asi2a03',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(8,'asi2a04','718069449f1acb05475f1dadf50b79ad','asi2a04',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(9,'asi2a05','718069449f1acb05475f1dadf50b79ad','asi2a05',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(10,'asi2a06','718069449f1acb05475f1dadf50b79ad','asi2a06',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(11,'asi2a07','718069449f1acb05475f1dadf50b79ad','asi2a07',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(12,'asi2a08','718069449f1acb05475f1dadf50b79ad','asi2a08',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(13,'asi2a09','718069449f1acb05475f1dadf50b79ad','asi2a09',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(14,'asi2a10','718069449f1acb05475f1dadf50b79ad','asi2a10',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(15,'asi2a11','718069449f1acb05475f1dadf50b79ad','asi2a11',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(16,'asi2a12','718069449f1acb05475f1dadf50b79ad','asi2a12',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(17,'asi2a13','718069449f1acb05475f1dadf50b79ad','asi2a13',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(18,'asi2a14','718069449f1acb05475f1dadf50b79ad','asi2a14',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(19,'asi2a15','718069449f1acb05475f1dadf50b79ad','asi2a15',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(20,'asi2a16','718069449f1acb05475f1dadf50b79ad','asi2a16',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(21,'asi2a17','718069449f1acb05475f1dadf50b79ad','asi2a17',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(22,'asi2a18','718069449f1acb05475f1dadf50b79ad','asi2a18',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(23,'asi2a19','718069449f1acb05475f1dadf50b79ad','asi2a19',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(24,'asi2a20','718069449f1acb05475f1dadf50b79ad','asi2a20',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(25,'asi2a21','718069449f1acb05475f1dadf50b79ad','asi2a21',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(26,'asi2a22','718069449f1acb05475f1dadf50b79ad','asi2a22',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(27,'asi2a23','718069449f1acb05475f1dadf50b79ad','asi2a23',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(28,'asi2a24','718069449f1acb05475f1dadf50b79ad','asi2a24',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(29,'asi2a25','718069449f1acb05475f1dadf50b79ad','asi2a25',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(30,'asi2a26','718069449f1acb05475f1dadf50b79ad','asi2a26',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(31,'asi2a27','718069449f1acb05475f1dadf50b79ad','asi2a27',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(32,'asi2a28','718069449f1acb05475f1dadf50b79ad','asi2a28',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(33,'asi2a29','718069449f1acb05475f1dadf50b79ad','asi2a29',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(34,'asi2a30','718069449f1acb05475f1dadf50b79ad','asi2a30',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(35,'asi2a31','718069449f1acb05475f1dadf50b79ad','asi2a31',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(36,'asi2a32','718069449f1acb05475f1dadf50b79ad','asi2a32',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(37,'asi2a33','718069449f1acb05475f1dadf50b79ad','asi2a33',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(38,'asi2a34','718069449f1acb05475f1dadf50b79ad','asi2a34',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(39,'asi2a35','718069449f1acb05475f1dadf50b79ad','asi2a35',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(40,'asi2a36','718069449f1acb05475f1dadf50b79ad','asi2a36',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(41,'asi2a37','718069449f1acb05475f1dadf50b79ad','asi2a37',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(42,'asi2a38','718069449f1acb05475f1dadf50b79ad','asi2a38',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(43,'asi2a39','718069449f1acb05475f1dadf50b79ad','asi2a39',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(44,'asi2a40','718069449f1acb05475f1dadf50b79ad','asi2a40',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(45,'asi2a41','718069449f1acb05475f1dadf50b79ad','asi2a41',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(46,'asi2a42','718069449f1acb05475f1dadf50b79ad','asi2a42',1,2);
INSERT INTO LOGIN(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(47,'asi2a43','718069449f1acb05475f1dadf50b79ad','asi2a43',1,2);

COMMIT;
Eines de l'usuari
Espais de noms
Variants
Accions
Navegació
IES Jaume Balmes
Màquines recreatives
CNC
Informàtica musical
joanillo.org Planet
Eines