Implementación de la BD

Código en MySQL para la base de datos del historial clinico

 
mysql> CREATE DATABASE hist;
Query OK, 1 row affected (0.00 sec)
 
mysql> USE hist;
Database changed

mysql> CREATE TABLE paciente(id_pte INT NOT NULL PRIMARY KEY AUTO_INCREMENT, id_caresp INT NOT NULL, id_prop INT NOT NULL, nompte VARCHAR(20), esppte VARCHAR(20), rzpte VARCHAR(20), sxpte VARCHAR(5), pspte DECIMAL, alzpte DECIMAL, fnpte DATE);
Query OK, 0 rows affected (0.09 sec)
 
mysql> CREATE TABLE Caracteristicas_especiales(id_caresp INT NOT NULL PRIMARY KEY AUTO_INCREMENT, clpte VARCHAR(10), plpte VARCHAR(10), ccpte VARCHAR(10), cepte VARCHAR(20), tjpte VARCHAR(10), cppte INT, fzpte VARCHAR(20));
Query OK, 0 rows affected (0.05 sec)
 
mysql> CREATE TABLE propietario(id_prop INT NOT NULL PRIMARY KEY AUTO_INCREMENT, id_pte INT NOT NULL, id_est INT NOT NULL, id_cd INT NOT NULL, id_col INT NOT NULL, id_tel INT NOT NULL, nomprop VARCHAR(20), ap1_prop VARCHAR(20), ap2_prop VARCHAR(20), dirprop VARCHAR(30), poprop VARCHAR(20));
Query OK, 0 rows affected (0.08 sec)
 
mysql> ALTER TABLE paciente ADD FOREIGN KEY(id_caresp) REFERENCES Caracteristicas_especiales(id_caresp) ON  DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> ALTER TABLE paciente ADD FOREIGN KEY(id_prop) REFERENCES propietario(id_prop) ON  DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> ALTER TABLE propietario ADD FOREIGN KEY(id_pte) REFERENCES paciente(id_pte) ON  DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> CREATE TABLE estado(id_est INT NOT NULL PRIMARY KEY AUTO_INCREMENT, est VARCHAR(20));
Query OK, 0 rows affected (0.13 sec)
 
mysql> CREATE TABLE ciudad(id_cd INT NOT NULL PRIMARY KEY AUTO_INCREMENT, cd VARCHAR(20));
Query OK, 0 rows affected (0.10 sec)
 
mysql> CREATE TABLE colonia(id_col INT NOT NULL PRIMARY KEY AUTO_INCREMENT, col VARCHAR(20), cp INT);
Query OK, 0 rows affected (0.08 sec)
 
mysql> CREATE TABLE telefono(id_tel INT NOT NULL PRIMARY KEY AUTO_INCREMENT, id_prop INT NOT NULL, telprop VARCHAR(10));
Query OK, 0 rows affected (0.10 sec)
 
mysql> ALTER TABLE telefono ADD FOREIGN KEY(id_prop) REFERENCES propietario(id_prop) ON  DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> ALTER TABLE propietario ADD FOREIGN KEY(id_est) REFERENCES estado(id_est) ON  DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> ALTER TABLE propietario ADD FOREIGN KEY(id_cd) REFERENCES ciudad(id_cd) ON  DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> ALTER TABLE propietario ADD FOREIGN KEY(id_tel) REFERENCES telefono(id_tel) ON  DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> CREATE TABLE ambiente(id_amb INT NOT NULL PRIMARY KEY AUTO_INCREMENT, ent VARCHAR(30), nut VARCHAR(30), esvid VARCHAR(30));
Query OK, 0 rows affected (0.08 sec)
 
mysql> CREATE TABLE constantes_fisicas(id_constfis INT NOT NULL PRIMARY KEY AUTO_INCREMENT, frecar VARCHAR(30), freres VARCHAR(30), pul VARCHAR(30), temp DECIMAL);
Query OK, 0 rows affected (0.06 sec)
 
mysql> CREATE TABLE consulta(folio INT NOT NULL PRIMARY KEY AUTO_INCREMENT, id_amb INT NOT NULL, id_constfis INT NOT NULL, id_lespri INT NOT NULL, id_lessec INT NOT NULL, id_linf INT NOT NULL, id_sistesq INT NOT NULL, id_sistner INT NOT NULL, ant VARCHAR(30), FOREIGN KEY(id_amb) REFERENCES ambiente(id_amb) ON DELETE RESTRICT ON UPDATE CASCADE, FOREIGN KEY(id_constfis) REFERENCES constantes_fisicas(id_constfis) ON DELETE RESTRICT ON UPDATE CASCADE);
Query OK, 0 rows affected (0.08 sec)
 
mysql> CREATE TABLE lesiones_primarias(id_lespri INT NOT NULL PRIMARY KEY AUTO_INCREMENT, maclespri VARCHAR(10), paplespri VARCHAR(10), puslespri VARCHAR(10), hablespri VARCHAR(10), veslespri VARCHAR(10), plalespri VARCHAR(10), nodlespri VARCHAR(10), tumlespri VARCHAR(10), quilespri VARCHAR(10));
Query OK, 0 rows affected (0.11 sec)
 
mysql> CREATE TABLE lesiones_secundarias(id_lessec INT NOT NULL PRIMARY KEY AUTO_INCREMENT, comlessec VARCHAR(10), coeplessec VARCHAR(10), esclessec VARCHAR(10), coslessec VARCHAR(10), exclessec VARCHAR(10), erlessec VARCHAR(10), liqlessec VARCHAR(10), ullessec VARCHAR(10), hipelessec VARCHAR(10), hipolessec VARCHAR(10), ciclessec VARCHAR(10));
Query OK, 0 rows affected (0.06 sec)
 
mysql> ALTER TABLE consulta ADD FOREIGN KEY(id_lespri) REFERENCES lesiones_primarias(id_lespri) ON  DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> ALTER TABLE consulta ADD FOREIGN KEY(id_lessec) REFERENCES lesiones_secundarias(id_lessec) ON  DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> CREATE TABLE linfodos(id_linf INT NOT NULL PRIMARY KEY AUTO_INCREMENT, manlin VARCHAR(10), esclin VARCHAR(10), inglin VARCHAR(10), poplin VARCHAR(10));
Query OK, 0 rows affected (0.05 sec)
 
mysql> CREATE TABLE patron_de_distribucion(id_patdis INT NOT NULL PRIMARY KEY AUTO_INCREMENT, vislatizq VARCHAR(10), vislatder VARCHAR(10), visdor VARCHAR(10), visvent VARCHAR(10));
Query OK, 0 rows affected (0.07 sec)
 
mysql> CREATE TABLE sistema_esqueletico(id_sistesq INT NOT NULL PRIMARY KEY AUTO_INCREMENT, id_insp INT NOT NULL, id_palp INT NOT NULL, id_pruesp INT NOT NULL);
Query OK, 0 rows affected (0.06 sec)
 
mysql> CREATE TABLE inspeccion(id_insp INT NOT NULL PRIMARY KEY AUTO_INCREMENT, postpte VARCHAR(10), valmar VARCHAR(10), valtro VARCHAR(10));
Query OK, 0 rows affected (0.07 sec)
 
mysql> CREATE TABLE palpacion(id_palp INT NOT NULL PRIMARY KEY AUTO_INCREMENT, id_exttor INT NOT NULL, id_extpel INT NOT NULL);
Query OK, 0 rows affected (0.06 sec)
 
mysql> CREATE TABLE extremidad_toraxica(id_exttor INT NOT NULL PRIMARY KEY AUTO_INCREMENT, reesc VARCHAR(10), coan VARCHAR(10), cade VARCHAR(10));
Query OK, 0 rows affected (0.11 sec)
 
mysql> CREATE TABLE extremidad_pelviana(id_extpel INT NOT NULL PRIMARY KEY AUTO_INCREMENT, pel VARCHAR(10), rod VARCHAR(10), corv VARCHAR(10));
Query OK, 0 rows affected (0.12 sec)
 
mysql> ALTER TABLE palpacion ADD FOREIGN KEY(id_exttor) REFERENCES extremidad_toraxica(id_exttor) ON  DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> ALTER TABLE palpacion ADD FOREIGN KEY(id_extpel) REFERENCES extremidad_pelviana(id_extpel) ON  DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> ALTER TABLE sistema_esqueletico ADD FOREIGN KEY(id_insp) REFERENCES inspeccion(id_insp) ON  DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> ALTER TABLE sistema_esqueletico ADD FOREIGN KEY(id_palp) REFERENCES palpacion(id_palp) ON  DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> CREATE TABLE pruebas_especificas(id_pruesp INT NOT NULL PRIMARY KEY AUTO_INCREMENT, pruca VARCHAR(10), pruor VARCHAR(10), otras VARCHAR(10));
Query OK, 0 rows affected (0.07 sec)
 
mysql> ALTER TABLE sistema_esqueletico ADD FOREIGN KEY(id_pruesp) REFERENCES pruebas_especificas(id_pruesp) ON  DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> CREATE TABLE sistema_nervioso(id_sistner INT NOT NULL PRIMARY KEY AUTO_INCREMENT, id_parcra INT NOT NULL, id_refpos INT NOT NULL, id_estmen INT NOT NULL, id_refesp INT NOT NULL, id_reapos INT NOT NULL, id_otros INT NOT NULL);
Query OK, 0 rows affected (0.13 sec)

mysql> CREATE TABLE pares_craneales(id_parcra INT NOT NULL PRIMARY KEY AUTO_INCREMENT, olf VARCHAR(10), opt VARCHAR(10), ocu VARCHAR(10), troc VARCHAR(10), trig VARCHAR(10), abd VARCHAR(10), fac VARCHAR(10), vesloc VARCHAR(10), glofar VARCHAR(10), vago VARCHAR(10), espac VARCHAR(10), hipgl VARCHAR(10));
Query OK, 0 rows affected (0.07 sec)

mysql> ALTER TABLE sistema_nervioso ADD FOREIGN KEY(id_parcra) REFERENCES pares_craneales(id_parcra) ON  DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE reflejos_postulares(id_refpos INT NOT NULL PRIMARY KEY AUTO_INCREMENT, carr VARCHAR(10), hemmar VARCHAR(10), hemmie VARCHAR(10), salto VARCHAR(10), procon VARCHAR(10), proinc VARCHAR(10));
Query OK, 0 rows affected (0.13 sec)

mysql> CREATE TABLE estado_mental(id_estmen INT NOT NULL PRIMARY KEY AUTO_INCREMENT, dep VARCHAR(10), estu VARCHAR(10), coma VARCHAR(10));
Query OK, 0 rows affected (0.05 sec)

mysql> ALTER TABLE sistema_nervioso ADD FOREIGN KEY(id_refpos) REFERENCES reflejos_postulares(id_refpos) ON  DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sistema_nervioso ADD FOREIGN KEY(id_estmen) REFERENCES estado_mental(id_estmen) ON  DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE reacciones_postulares(id_reapos INT NOT NULL PRIMARY KEY AUTO_INCREMENT, postv VARCHAR(10), reatc VARCHAR(10));
Query OK, 0 rows affected (0.12 sec)

mysql> ALTER TABLE sistema_nervioso ADD FOREIGN KEY(id_reapos) REFERENCES reacciones_postulares(id_reapos) ON  DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE reflejos_espinales(id_refesp INT NOT NULL PRIMARY KEY AUTO_INCREMENT, id_miant INT NOT NULL, id_mipost INT NOT NULL);
Query OK, 0 rows affected (0.10 sec)

mysql> CREATE TABLE miembro_anterior(id_miant INT NOT NULL PRIMARY KEY AUTO_INCREMENT, bic VARCHAR(10), tric VARCHAR(10), flexa VARCHAR(10));
Query OK, 0 rows affected (0.17 sec)

mysql> CREATE TABLE miembro_posterior(id_mipost INT NOT NULL PRIMARY KEY AUTO_INCREMENT, flexp VARCHAR(10), pat VARCHAR(10), tibcra VARCHAR(10), gastr VARCHAR(10));
Query OK, 0 rows affected (0.07 sec)

mysql> ALTER TABLE reflejos_espinales ADD FOREIGN KEY(id_idmiant) REFERENCES miembro_anterior(id_miant) ON  DELETE RESTRICT ON UPDATE CASCADE;
ERROR 1072 (42000): Key column 'id_idmiant' doesn't exist in table
mysql> ALTER TABLE reflejos_espinales ADD FOREIGN KEY(id_miant) REFERENCES miembro_anterior(id_miant) ON  DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE reflejos_espinales ADD FOREIGN KEY(id_mipost) REFERENCES miembro_posterior(id_mipost) ON  DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sistema_nervioso ADD FOREIGN KEY(id_refesp) REFERENCES reflejos_espinales(id_refesp) ON  DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE otros(id_otros INT NOT NULL PRIMARY KEY AUTO_INCREMENT, reexcr VARCHAR(10), sensu VARCHAR(10), senpro VARCHAR(10), refpa VARCHAR(10), refsac VARCHAR(10), cocc VARCHAR(10), siba VARCHAR(10), refmic VARCHAR(10));
Query OK, 0 rows affected (0.07 sec)

mysql> ALTER TABLE sistema_nervioso ADD FOREIGN KEY(id_otros) REFERENCES otros(id_otros) ON  DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0
 

 

 

 

 

mysql> INSERT INTO paciente VALUES(NULL, 1, 1, "Firulais", "Canina", "Dalmata", "Macho", 40.5, 1.5, '2012-05-10');
Query OK, 1 row affected, 2 warnings (0.00 sec)

 

mysql> INSERT INTO paciente VALUES(NULL, 1, 1, "Bruno", "Canina", "Pitbull", "Macho", 60.2, 1.1, '2011-08-20');

Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> INSERT INTO paciente VALUES(NULL, 3, 2, "Pelusa", "Felina", "Bengali", "Hembra", 22.8, 0.7, '2011-11-03');

Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> INSERT INTO propietario VALUES(NULL, 1, 1, 1, 2, 1, "Jose", "Linares", "Bueno", "Pinotepa, #2422", "Ing. Industrial");
Query OK, 1 row affected (0.00 sec)

 

mysql> INSERT INTO propietario VALUES(NULL, 2, 3, 4, 1, 3, "Ernesto", "Torres", "Ramirez", "Allende, #1548", "Lic. Administracion");
Query OK, 1 row affected (0.00 sec)

 

mysql> INSERT INTO propietario VALUES(NULL, 3, 1, 3, 5, 7, "Diana", "Flores", "Campa", "Omitlan, #2674", "Contador publico");
Query OK, 1 row affected (0.00 sec)

 

mysql> SELECT * FROM paciente;
+--------+-----------+---------+----------+--------+---------+-------+-------+--------+------------+
| id_pte|id_caresp|id_prop|nompte|esppte|rzpte  | sxpte | pspte | alzpte | fnpte      |
+--------+-----------+---------+----------+--------+---------+-------+-------+--------+------------+
|      1 |         1 |       1 | Firulais | Canina | Dalmata | Macho |    41 |      2 | 2012-05-10 |
|      2 |         1 |       1 | Bruno    | Canina | Pitbull | Macho |    60 |      1 | 2011-08-20 |
|      3 |         3 |       2 | Pelusa   | Felina | Bengali | Hembr |    23 |      1 | 2011-11-03 |
+--------+-----------+---------+----------+--------+---------+-------+-------+--------+------------+
3 rows in set (0.02 sec)

 

mysql> SELECT * FROM propietario;
+---------+--------+--------+-------+--------+--------+---------+----------+----------+-----------------+---------------------+
|id_prop|id_pte|id_est|id_cd|id_col|id_tel|nomprop|ap1_prop|ap2_prop|dirprop |poprop              |
+---------+--------+--------+-------+--------+--------+---------+----------+----------+-----------------+---------------------+
| 1 |  1 |  1 |  1 |  2 | 1 |Jose  | Linares  | Bueno    | Pinotepa, #2422 | Ing. Industrial     |
|  2 | 2 | 3 | 4 | 1 | 3 | Ernesto | Torres  |Ramirez | Allende, #1548 | Lic. Administracion |
| 3 | 3 | 1 |  3 | 5 | 7 | Diana | Flores | Campa  | Omitlan, #2674  | Contador publico    |
+---------+--------+--------+-------+--------+--------+---------+----------+----------+-----------------+---------------------+
3 rows in set (0.00 sec)