Exemple d'attribut d'agrégation
Schéma relationnel
CTRL+C pour copier, CTRL+V pour coller
1
country(#countrycode:char(2), name:varchar, population:numeric)
2
city(#code:char(3), countrycode=>country, name:varchar, population:numeric):
country(#countrycode:char(2), name:varchar, population:numeric) city(#code:char(3), countrycode=>country, name:varchar, population:numeric):
Schéma de base de données
CTRL+C pour copier, CTRL+V pour coller
1
CREATE TABLE country (
2
countrycode CHAR(2) NOT NULL,
3
name VARCHAR NOT NULL,
4
population NUMERIC(3),
5
PRIMARY KEY (countrycode)
6
);
7
8
CREATE TABLE city (
9
citycode CHAR(3) NOT NULL,
10
countrycode CHAR(2) NOT NULL,
11
name VARCHAR NOT NULL,
12
population NUMERIC(2,1),
13
PRIMARY KEY (citycode),
14
FOREIGN KEY (countrycode) REFERENCES country(countrycode)
15
);
CREATE TABLE country ( countrycode CHAR(2) NOT NULL, name VARCHAR NOT NULL, population NUMERIC(3), PRIMARY KEY (countrycode) ); CREATE TABLE city ( citycode CHAR(3) NOT NULL, countrycode CHAR(2) NOT NULL, name VARCHAR NOT NULL, population NUMERIC(2,1), PRIMARY KEY (citycode), FOREIGN KEY (countrycode) REFERENCES country(countrycode) );
Données
CTRL+C pour copier, CTRL+V pour coller
1
INSERT INTO country VALUES ('ES', 'Spain', 46);
2
INSERT INTO country VALUES ('FR', 'France', 67);
3
INSERT INTO country VALUES ('DE', 'Germany', 82);
4
5
INSERT INTO city VALUES ('BAR', 'ES', 'Barcelona', 1.9);
6
INSERT INTO city VALUES ('MAD', 'ES', 'Madrid', 3.3);
7
INSERT INTO city VALUES ('ZAR', 'ES', 'Zaragoza', 0.7);
8
9
INSERT INTO city VALUES ('PAR', 'FR', 'Paris', 2.2);
10
INSERT INTO city VALUES ('LYO', 'FR', 'Paris', 0.5);
11
INSERT INTO city VALUES ('LLL', 'FR', 'Lille', 0.2);
12
INSERT INTO city VALUES ('AMN', 'FR', 'Amiens', 0.1);
INSERT INTO country VALUES ('ES', 'Spain', 46);
INSERT INTO country VALUES ('FR', 'France', 67);
INSERT INTO country VALUES ('DE', 'Germany', 82);
INSERT INTO city VALUES ('BAR', 'ES', 'Barcelona', 1.9);
INSERT INTO city VALUES ('MAD', 'ES', 'Madrid', 3.3);
INSERT INTO city VALUES ('ZAR', 'ES', 'Zaragoza', 0.7);
INSERT INTO city VALUES ('PAR', 'FR', 'Paris', 2.2);
INSERT INTO city VALUES ('LYO', 'FR', 'Paris', 0.5);
INSERT INTO city VALUES ('LLL', 'FR', 'Lille', 0.2);
INSERT INTO city VALUES ('AMN', 'FR', 'Amiens', 0.1);Sélectionne les countrycode existants dans la table city
Sélectionne les countrycode existants dans la table city avec agrégat
