· 7 years ago · Feb 20, 2019, 04:46 PM
1DROP TABLE IF EXISTS `tbl_animal`;
2CREATE TABLE `tbl_animal` (
3 id_animal INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
4 name VARCHAR(25) NOT NULL DEFAULT "no name",
5 specie VARCHAR(10) NOT NULL DEFAULT "Other",
6 sex CHAR(1) NOT NULL DEFAULT "M",
7 size VARCHAR(10) NOT NULL DEFAULT "Mini",
8 edad VARCHAR(10) NOT NULL DEFAULT "Lact",
9 pelo VARCHAR(5 ) NOT NULL DEFAULT "short",
10 color VARCHAR(25) NOT NULL DEFAULT "not defined",
11 ra VARCHAR(25) NOT NULL DEFAULT "not defined",
12 CONSTRAINT `uc_Info_Animal` UNIQUE (`id_animal`)
13) ENGINE=InnoDB DEFAULT CHARSET=utf8;
14
15
16INSERT INTO `tbl_animal` VALUES (1,'no name', 'dog', 'M','Mini','Lact','Long','black','Bobtail');
17INSERT INTO `tbl_animal` VALUES (2,'peluchin', 'cat', 'M','Mini','Lact','Long','white','not defined');
18INSERT INTO `tbl_animal` VALUES (3,'asechin', 'cat', 'M','Mini','Lact','Corto','orange','not defined');
19
20DROP TABLE IF EXISTS `tbl_person`;
21CREATE TABLE `tbl_person` (
22 type_person VARCHAR(50) NOT NULL primary key
23) ENGINE=InnoDB DEFAULT CHARSET=utf8;
24INSERT INTO `tbl_person` (type_person) VALUES ('Worker');
25INSERT INTO `tbl_person` (type_person) VALUES ('Civil');
26
27
28
29DROP TABLE IF EXISTS `tbl_worker`;
30CREATE TABLE `tbl_worker`(
31 id_worker INTEGER NOT NULL PRIMARY KEY,
32 type_person VARCHAR(50) NOT NULL ,
33 name_worker VARCHAR(50) NOT NULL ,
34 address_worker VARCHAR(40) NOT NULL DEFAULT "not defined",
35 delegation VARCHAR(40) NOT NULL DEFAULT "not defined",
36 FOREIGN KEY (type_person) REFERENCES `tbl_person` (type_person),
37 CONSTRAINT `uc_Info_worker` UNIQUE (`id_worker`)
38) ENGINE=InnoDB DEFAULT CHARSET=utf8;
39
40INSERT INTO `tbl_worker` VALUES (1,'Worker','N_CEDENTE1', 'DIR Worker 1', 'DEL');
41INSERT INTO `tbl_worker` VALUES (2,'Worker','N_worker1', 'DIR Worker 2', 'DEL');
42INSERT INTO `tbl_worker` VALUES (3,'Worker','N_worker2', 'address worker','delegation worker');
43
44
45DROP TABLE IF EXISTS `tbl_civil`;
46CREATE TABLE `tbl_civil`(
47 id_civil INTEGER NOT NULL PRIMARY KEY,
48 type_person VARCHAR(50) NOT NULL ,
49 name_civil VARCHAR(50) ,
50 procedence_civil VARCHAR(40) NOT NULL DEFAULT "Socorrism",
51 FOREIGN KEY (type_person) REFERENCES `tbl_person` (type_person),
52 CONSTRAINT `uc_Info_civil` UNIQUE (`id_civil`)
53) ENGINE=InnoDB DEFAULT CHARSET=utf8;
54
55
56INSERT INTO `tbl_civil` VALUES (1,'Civil','N_civil1' , 'Socorrism');
57
58
59CREATE TABLE `tbl_event` (
60 id_event INTEGER NOT NULL,
61 id_animal INTEGER NOT NULL,
62 type_person VARCHAR(50) NOT NULL ,
63 date_reception DATE DEFAULT '2000-01-01 01:01:01',
64 FOREIGN KEY (id_animal) REFERENCES `tbl_animal` (id_animal),
65 FOREIGN KEY (type_person ) REFERENCES `tbl_person` (type_person ),
66 CONSTRAINT `uc_Info_ficha_primer_ingreso` UNIQUE (`id_animal`,`id_event`)
67)ENGINE=InnoDB DEFAULT CHARSET=utf8;
68
69INSERT INTO `tbl_event` VALUES (1,1, 'Worker','2013-01-01 01:01:01' );
70INSERT INTO `tbl_event` VALUES (2,2, 'Civil','2013-01-01 01:01:01' );
71
72SELECT a.*,b.*,z.*
73FROM tbl_event a
74 left JOIN tbl_worker b
75 ON a.type_person = b.type_person
76 left JOIN tbl_animal z
77 ON z.id_animal = a.id_animal ;
78
79SELECT a.*,b.*,z.*
80FROM tbl_event a
81 left JOIN tbl_civil b
82 ON a.type_person = b.type_person
83 left JOIN tbl_animal z
84 ON z.id_animal = a.id_animal ;
85
86CREATE TABLE person (
87 person_id int PRIMARY KEY
88 -- Other fields...
89);
90
91CREATE TABLE civil (
92 civil_id int PRIMARY KEY REFERENCES person (person_id)
93 -- Other fields...
94);
95
96CREATE TABLE worker (
97 worker_id int PRIMARY KEY REFERENCES person (person_id)
98 -- Other fields...
99);
100
101CREATE TABLE event (
102 event_id int PRIMARY KEY,
103 person_id int REFERENCES person (person_id)
104 -- Other fields...
105);
106
107CREATE TABLE person_type (
108 person_type_id int PRIMARY KEY
109 -- data: 1=civil, 2=worker
110 -- Other fields (such as a label)...
111 );
112
113 CREATE TABLE person (
114 person_id int PRIMARY KEY
115 person_type_id int FOREIGN KEY REFERENCES person_type (person_type_id)
116 -- Other fields...
117 );
118
119 CREATE TABLE civil (
120 civil_id int PRIMARY KEY REFERENCES person (person_id)
121 person_type_id int FOREIGN KEY REFERENCES person (person_type_id)
122 -- Other fields...
123 );
124
125 CREATE TABLE worker (
126 worker_id int PRIMARY KEY REFERENCES person (person_id)
127 person_type_id int FOREIGN KEY REFERENCES person (person_type_id)
128 -- Other fields...
129 );
130
131 CREATE TABLE event (
132 event_id int PRIMARY KEY,
133 person_id int REFERENCES person (person_id)
134 -- Type is optional here, but you could enforce event for a particular type
135 person_type_id int FOREIGN KEY REFERENCES person (person_type_id)
136 -- Other fields...
137 );