1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103
| CREATE TABLE LOCALISATION
(
LocalisationId INT IDENTITY NOT NULL
, LocalisationNote VARCHAR(64) NOT NULL DEFAULT ''
, CONSTRAINT LOCALISATION_PK PRIMARY KEY (LocalisationId)
);
CREATE TABLE CAMION
(
CamionId INT NOT NULL
, CamionImmat VARCHAR(16) NOT NULL
, CamionDateAchat DATE NOT NULL
, CONSTRAINT CAMION_PK PRIMARY KEY (CamionId)
, CONSTRAINT CAMION_AK UNIQUE (CamionImmat)
, CONSTRAINT CAMION_LOCALISATION_FK FOREIGN KEY (CamionId)
REFERENCES LOCALISATION (LocalisationId)
);
CREATE TABLE LOCAL
(
LocalisationId INT NOT NULL
, LocalCode CHAR(5) NOT NULL
, LocalNom VARCHAR(48) NOT NULL
, CONSTRAINT LOCAL_PK PRIMARY KEY (LocalisationId)
, CONSTRAINT LOCAL_AK UNIQUE (LocalCode)
, CONSTRAINT LOCAL_LOCALISATION_FK FOREIGN KEY (LocalisationId)
REFERENCES LOCALISATION (LocalisationId)
);
CREATE TABLE COMPOSANT_TYPE
(
ComposantTypeId INT IDENTITY NOT NULL
, ComposantTypeLibelle VARCHAR(24) NOT NULL
, ComposantQteMax INT NOT NULL
, CONSTRAINT COMPOSANT_TYPE_PK PRIMARY KEY (ComposantTypeId)
);
CREATE TABLE COMPOSANT
(
ComposantId INT IDENTITY NOT NULL
, ComposantTypeId INT NOT NULL
, ComposantDateAchat DATE NOT NULL
, Fabriquant VARCHAR(48) NOT NULL
, Modele VARCHAR(48) NOT NULL
, CONSTRAINT COMPOSANT_PK PRIMARY KEY (ComposantId)
, CONSTRAINT COMPOSANT_TYPE_FK FOREIGN KEY (ComposantTypeId) REFERENCES COMPOSANT_TYPE (ComposantTypeId)
);
CREATE TABLE MOTEUR
(
ComposantId INT NOT NULL
, MoteurNumeroSerie VARCHAR(25) NOT NULL
, MaxHorsePower INT NOT NULL
, CONSTRAINT MOTEUR_PK PRIMARY KEY (ComposantId)
, CONSTRAINT MOTEUR_AK UNIQUE (MoteurNumeroSerie)
, CONSTRAINT MOTEUR_COMPOSANT_FK FOREIGN KEY (ComposantId) REFERENCES COMPOSANT (ComposantId)
);
CREATE TABLE TRANSMISSION
(
ComposantId INT NOT NULL
, TransmissionNumeroSerie VARCHAR(25) NOT NULL
, TransmissionVitesses INT NOT NULL
, CONSTRAINT TRANSMISSION_PK PRIMARY KEY (ComposantId)
, CONSTRAINT TRANSMISSION_AK UNIQUE (TransmissionNumeroSerie)
, CONSTRAINT TRANSMISSION_COMPOSANT_FK FOREIGN KEY (ComposantId) REFERENCES COMPOSANT (ComposantId)
);
CREATE TABLE DIFFERENTIEL
(
ComposantId INT NOT NULL
, DifferentielNumeroSerie VARCHAR(25) NOT NULL
, DifferentielRatio INT NOT NULL
, CONSTRAINT DIFFERENTIEL_PK PRIMARY KEY (ComposantId)
, CONSTRAINT DIFFERENTIEL_AK UNIQUE (DifferentielNumeroSerie)
, CONSTRAINT DIFFERENTIEL_COMPOSANT_FK FOREIGN KEY (ComposantId) REFERENCES COMPOSANT (ComposantId)
);
CREATE TABLE COMPOSANT_AFFECTATION
(
ComposantId INT NOT NULL
, ComposantAffectationId INT IDENTITY NOT NULL
, ComposantInstallationDate DATE NOT NULL
, ComposantDesInstallationDate DATE NOT NULL DEFAULT '9999-12-31'
, LocalisationId INT NOT NULL
, CONSTRAINT COMPOSANT_AFFECTATION_PK PRIMARY KEY (ComposantId, ComposantAffectationId)
, CONSTRAINT COMPOSANT_AFFECTATION_COMPOSANT_FK FOREIGN KEY (ComposantId)
REFERENCES COMPOSANT (ComposantId)
, CONSTRAINT COMPOSANT_AFFECTATION_MOTEUR_LOCALISATION_FK FOREIGN KEY (LocalisationId)
REFERENCES LOCALISATION (LocalisationId)
, CONSTRAINT COMPOSANT_AFFECTATION_CHECK01 CHECK (ComposantDesInstallationDate > ComposantInstallationDate)
);
CREATE TABLE HUILE_CHANGEMENT
(
ComposantId INT NOT NULL
, HuileDateChangement DATE NOT NULL
, HuileKm INT NOT NULL
, HuileGrade VARCHAR(5) NOT NULL
, CONSTRAINT HUILE_CHANGEMENT_PK PRIMARY KEY (ComposantId, HuileDateChangement)
, CONSTRAINT HUILE_CHANGEMENT_FK FOREIGN KEY (ComposantId)
REFERENCES COMPOSANT (ComposantId)
); |