You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
99 lines
4.3 KiB
99 lines
4.3 KiB
SET @OLD_UNIQUE_CHECKS = @@UNIQUE_CHECKS, UNIQUE_CHECKS = 0;
|
|
SET @OLD_FOREIGN_KEY_CHECKS = @@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0;
|
|
SET @OLD_SQL_MODE = @@SQL_MODE, SQL_MODE =
|
|
'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
|
|
|
|
DROP DATABASE IF EXISTS collaborateur_epa;
|
|
CREATE DATABASE IF NOT EXISTS collaborateur_epa /*!40100 DEFAULT CHARACTER SET latin1 */;
|
|
USE collaborateur_epa;
|
|
|
|
DROP TABLE IF EXISTS businessunit;
|
|
CREATE TABLE IF NOT EXISTS businessunit
|
|
(
|
|
Id int NOT NULL AUTO_INCREMENT,
|
|
Name varchar(100) NOT NULL UNIQUE,
|
|
PRIMARY KEY (Id)
|
|
);
|
|
|
|
DROP TABLE IF EXISTS agence;
|
|
CREATE TABLE IF NOT EXISTS agence
|
|
(
|
|
Id int NOT NULL AUTO_INCREMENT,
|
|
Name varchar(100) NOT NULL UNIQUE,
|
|
BusinessUnitId int NOT NULL,
|
|
CONSTRAINT FK_BUSINESS_UNIT_AGENCE FOREIGN KEY (BusinessUnitId) references businessunit (Id),
|
|
PRIMARY KEY (Id)
|
|
);
|
|
|
|
DROP TABLE IF EXISTS collaborateur;
|
|
CREATE TABLE IF NOT EXISTS collaborateur
|
|
(
|
|
Id int NOT NULL AUTO_INCREMENT,
|
|
Name varchar(100) NOT NULL,
|
|
FirstName varchar(100) NOT NULL,
|
|
BirthDate date NOT NULL,
|
|
Gender ENUM ('masculin','feminin','autre') NOT NULL DEFAULT 'masculin',
|
|
Status ENUM ('cadre','noncadre','alternant','stagiaire') NOT NULL DEFAULT 'noncadre',
|
|
ChildrenNumber smallint NOT NULL,
|
|
Address varchar(200) NOT NULL,
|
|
Telephone varchar(15) NOT NULL,
|
|
PersonalMail varchar(100) NOT NULL,
|
|
ApsideMail varchar(100) NOT NULL UNIQUE,
|
|
ResignationDate date,
|
|
businessunitId int NOT NUll,
|
|
CONSTRAINT FK_COLLABORATEUR_BUSINESSUNIT FOREIGN KEY (businessunitId) REFERENCES businessunit (Id),
|
|
PRIMARY KEY (Id)
|
|
);
|
|
|
|
DROP TABLE IF EXISTS referencement;
|
|
CREATE TABLE IF NOT EXISTS referencement
|
|
(
|
|
Id int NOT NULL AUTO_INCREMENT,
|
|
StartingDate date NOT NULL,
|
|
EndingDate date,
|
|
ReferredId int NOT NULL,
|
|
ReferrerId int NOT NULL,
|
|
CONSTRAINT FK_REFERENCEMENT_REFERRED FOREIGN KEY (ReferredId) REFERENCES collaborateur (Id),
|
|
CONSTRAINT FK_REFERENCEMENT_REFERRER FOREIGN KEY (ReferrerId) REFERENCES collaborateur (Id),
|
|
PRIMARY KEY (Id)
|
|
);
|
|
|
|
DROP TABLE IF EXISTS periodeessai;
|
|
CREATE TABLE IF NOT EXISTS `periodeessai`
|
|
(
|
|
`Id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`StartingDate` date NOT NULL,
|
|
`PlannedEndingDate` date NOT NULL,
|
|
`RealEndingDate` date,
|
|
`Comment` varchar(100),
|
|
`Issue` enum ('VALIDEE','PROLONGEE_COLLAB','PROLONGEE_APSIDE','ARRETEE_COLLAB','ARRETEE_APSIDE','INDETERMINEE'),
|
|
`CollaborateurId` int(11) NOT NULL,
|
|
PRIMARY KEY (`Id`),
|
|
KEY `FK_COLLABORATEUR_PERIODEESSAI` (`CollaborateurId`),
|
|
CONSTRAINT `FK_COLLABORATEUR_PERIODEESSAI` FOREIGN KEY (`CollaborateurId`) REFERENCES `collaborateur` (`Id`)
|
|
);
|
|
|
|
|
|
INSERT INTO businessunit(Name)
|
|
VALUES ('Business Unit Clermont-Ferrand');
|
|
INSERT INTO agence(Name, BusinessUnitId)
|
|
VALUES ('Cournon', 1);
|
|
|
|
INSERT INTO businessunit(Name)
|
|
VALUES ('Business Unit TOP');
|
|
INSERT INTO agence(Name, BusinessUnitId)
|
|
VALUES ('Tours', 2);
|
|
|
|
|
|
INSERT INTO collaborateur(Name, FirstName, BirthDate, Gender, Status, ChildrenNumber, Address, Telephone, PersonalMail,
|
|
ApsideMail, ResignationDate, businessunitId)
|
|
VALUES ('RUIZ', 'Alexandre', '1990-07-07', 'masculin', 'cadre', 0, 'adresse', 'tel', 'alexandre.ruiz@gmail.com', 'alexandre.ruiz@apside-groupe.com', null, 1),
|
|
('FERRERE', 'Clément', '2000-08-30', 'masculin', 'noncadre', 0, 'adresse', 'tel', 'clement.ferrere@gmail.com',
|
|
'clement.ferrere@apside-groupe.com', null, 1);
|
|
|
|
INSERT INTO referencement(StartingDate, EndingDate, ReferredId, ReferrerId)
|
|
VALUES ('2021-08-09', null, 1, 2);
|
|
|
|
SET SQL_MODE = @OLD_SQL_MODE;
|
|
SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS;
|
|
SET UNIQUE_CHECKS = @OLD_UNIQUE_CHECKS; |