Joan a 08:59, 19 des 2014

2014-12-19T08:59:21Z

Pàgina nova

L'esquema de la base de dades en MySQL és:
<pre>
/*
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;
</pre>

'''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
<pre>
SET NAMES 'UTF8'
</pre>
que és un 3 en 1, equival a
<pre>
SET character_set_client = 'UTF8';
SET character_set_results = 'UTF8';
SET character_set_connection = 'UTF8';
</pre>
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:
<pre>
CREATE DATABASE langtrainer DEFAULT CHARACTER SET utf8;
</pre>
(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:
<pre>
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;
</pre>

Langtrainer: Base de dades - Historial de revisió

Joan a 08:59, 19 des 2014