| 1 |
49820
|
michele.ar
|
CREATE TABLE persons (
|
| 2 |
|
|
id varchar(32) PRIMARY KEY,
|
| 3 |
60657
|
michele.ar
|
name varchar(256) NOT NULL,
|
| 4 |
|
|
surname varchar(256) NOT NULL,
|
| 5 |
|
|
suffix varchar(256)
|
| 6 |
49820
|
michele.ar
|
);
|
| 7 |
|
|
|
| 8 |
|
|
CREATE TABLE groups (
|
| 9 |
|
|
id varchar(16) PRIMARY KEY,
|
| 10 |
|
|
name varchar(256) NOT NULL,
|
| 11 |
50621
|
michele.ar
|
type varchar(32)
|
| 12 |
49820
|
michele.ar
|
);
|
| 13 |
|
|
|
| 14 |
|
|
CREATE TABLE affiliations (
|
| 15 |
|
|
pid varchar(32) REFERENCES persons(id),
|
| 16 |
|
|
gid varchar(16) REFERENCES groups(id),
|
| 17 |
|
|
year int,
|
| 18 |
61899
|
michele.ar
|
PRIMARY KEY (pid, gid, year),
|
| 19 |
|
|
FOREIGN KEY (pid) REFERENCES persons(id),
|
| 20 |
|
|
FOREIGN KEY (gid) REFERENCES groups(id)
|
| 21 |
49820
|
michele.ar
|
);
|
| 22 |
50023
|
michele.ar
|
|
| 23 |
50634
|
michele.ar
|
INSERT INTO groups(id, name, type) VALUES ('UNKNOWN', 'UNKNOWN', 'UNKNOWN');
|
| 24 |
|
|
|
| 25 |
60657
|
michele.ar
|
CREATE VIEW affiliations_view AS SELECT
|
| 26 |
|
|
p.id as pid,
|
| 27 |
|
|
TRIM(CONCAT(p.surname, ", ", p.name, " ", COALESCE(p.suffix, ''))) as pfullname,
|
| 28 |
|
|
g.id as gid,
|
| 29 |
|
|
g.name as gname,
|
| 30 |
|
|
g.type as gtype,
|
| 31 |
|
|
a.year as year
|
| 32 |
50023
|
michele.ar
|
FROM
|
| 33 |
|
|
persons p
|
| 34 |
61881
|
michele.ar
|
JOIN affiliations a ON (p.id = a.pid)
|
| 35 |
|
|
JOIN groups g ON (a.gid = g.id);
|