Comment travailler avec SQL: 2011 demande-temps tableaux d'époque

Prenons un exemple en utilisant des tables SQL de temps application période. Supposons une entreprise veut garder une trace de ce département appartiennent à ses employés, à tout moment au cours de leur période d'emploi. L'entreprise peut le faire en créant des tables de la période d'application à temps pour les employés et les départements:

CREATE TABLE employee_atpt (EmpID ENTIER, EmpStart DATE, EmpEnd DATE, EmpDept VARCHAR (30), le délai d'EmpPeriod (EmpStart, EmpEnd)) -

Le datetime de départ (EmpStart dans l'exemple) est inclus dans la période, mais le datetime fin (EmpEnd dans l'exemple) est pas. Ceci est connu comme la sémantique fermé-ouvert.

INSERT INTO employee_atptVALUES (12345, DATE '2011-01-01', DATE '9999-12-31', 'Ventes') -

Le tableau résultant présente une rangée, comme indiqué dans le tableau 7-1.

La date du 9999-12-31 final indique que le mandat de cet employé de la société n'a pas encore terminé.

Supposons maintenant que le 15 Mars, 2012, 12345 employé est temporairement affecté au département de génie jusqu'au 15 Juillet 2012, de retourner à le département des ventes par la suite. Vous pouvez accomplir cela avec le suivant METTRE À JOUR déclaration:

UPDATE employee_atptFOR PARTIE DE EmpPeriodFROM DATE '2012-03-15'TO DATE' 2012-07-15'SET EmpDept = 'Engineering'WHERE EmpID = 12345-

Après la mise à jour, la table a maintenant trois rangées.

En supposant employé 12345 est toujours employé dans le département des ventes, la table enregistre précisément son ministère de membres du Jour de l'An de 2011 jusqu'à l'heure actuelle.

Suppression de données à partir d'une table de la période d'application en temps peut être un peu plus compliqué que de simplement supprimer des lignes dans un tableau intemporel ordinaire. A titre d'exemple, supposons que l'employé quitte l'entreprise 12345 sur Mars 15 de 2012and est réengagé le 15 Juillet de la même année. Initialement, l'application temps table de période aura une rangée.

UN EFFACER déclaration mettra à jour la table pour montrer la période pendant laquelle 12345 employé a disparu:

SUPPRIMER employee_atptFOR PARTIE DE EmpPeriodFROM DATE '2012-03-15'TO DATE' 2012-07-15'WHERE EmpID = 12345-

Le tableau reflète désormais les périodes de temps au cours de laquelle a été employé 12345 employés par l'entreprise et montre l'écart au cours de laquelle elle n'a pas été employé par la société.

Une application en temps table de période d'employés peut contenir plusieurs lignes pour un seul employé. Le numéro d'identification de l'employé, par lui-même, ne sont pas utilisables en tant que clé primaire de la table. Les données temporelles doivent être ajoutés au mélange.

Comment désigner les clés primaires dans les tables de la période d'application en temps

Afin de garantir qu'il n'y ait pas de dédoublement de lignes, la date de début (EmpStart) et la date de fin (EmpEnd) doit être inclus dans la clé primaire. Cependant, tout en les incluant ne suffit pas. Considérons le cas où l'employé a été transféré à 12345 Ingénierie pour quelques mois, puis est retourné à son ministère d'attache.

Notez que les deux périodes se chevauchent. Il ressemble à un employé 12345 est membre à la fois le département des ventes et du département d'ingénierie de 15 Mars 2012 jusqu'au 15 Juillet, de 2012.

Cela ajoute complication et pourrait conduire à la corruption des données. Application d'une contrainte qui dit qu'un employé peut être un membre d'un seul département à un moment est peut-être ce que la plupart des organisations veulent faire. Vous pouvez ajouter une telle contrainte à une table avec un ALTER TABLE énoncé comme suit:

ALTER TABLE employee_atptADD PRIMARY KEY (EmpID, EmpPeriod sans superposition) -

Il ya une meilleure façon de faire les choses que de créer une première table et en ajoutant sa contrainte de clé primaire tard - à la place, vous pouvez inclure la contrainte de clé primaire dans l'original CREATE déclaration. Il pourrait ressembler à ce qui suit:

CREATE TABLE employee_atptEmpID INTEGER NOT NULL, NULL EmpStart DATE PAS, EmpEnd DATE NOT NULL, EmpDept VARCHAR (30), le délai d'EmpPeriod (EmpStart, EmpEnd) PRIMARY KEY (EmpID, EmpPeriod sans superposition)) -

Comment appliquer référentielles contraintes aux tables de la période d'application en temps

Toute base de données qui est destiné à maintenir plus qu'une simple liste d'éléments nécessitera probablement plusieurs tables. Si une base de données comporte plusieurs tables, les relations entre les tables doivent être définies, et les contraintes d'intégrité référentielle doivent être mis en place.

Vous avez un employé application temps table de période et un département application temps table de période. Il est une relation un-à-plusieurs entre la table de service et la table des employés, parce qu'un ministère peut avoir plusieurs employés, mais chaque employé appartient à un et un seul département.

Cela signifie que vous avez besoin de mettre une clé étrangère dans la table des employés qui fait référence à la clé primaire de la table de service. Avec cela à l'esprit, utiliser un plus complète CREATE déclaration, et de créer une table de département:

CREATE TABLE employee_atpt (EmpID INTEGER NOT NULL, EmpStart DATE NOT NULL, EmpEnd DATE NOT NULL, EmpName VARACHAR (30), EmpDept VARCHAR (30), le délai d'EmpPeriod (EmpStart, EmpEnd) PRIMARY KEY (EmpID, EmpPeriod sans superposition) FOREIGN KEY (EmpDept, PÉRIODE EmpPeriod) Références dept_atpt (DeptID, PÉRIODE DeptPeriod)) - CREATE TABLE dept_atpt (DeptID VARCHAR (30) NOT NULL, directeur VARCHAR (40) NOT NULL, DeptStart DATE NOT NULL, DeptEnd DATE NOT NULL, PÉRIODE POUR DeptTime ( DeptStart, DeptEnd), la clé primaire (DeptID, DeptTime sans superposition)) -

Comment interroger les tables de la période d'application en temps

Maintenant, des informations détaillées peuvent être extraites de la base de données en utilisant SELECT états qui utilisent les données temporelles.

Une chose que vous pouvez faire est d'énumérer toutes les personnes qui sont actuellement employés par l'organisation. Même avant que SQL: 2011, vous pouvez le faire avec une déclaration similaire à la suivante:

* SELECT FROM employee_atptWHERE EmpStart lt; = CURRENT_DATE () ET EmpEnd> CURRENT_DATE () -

Avec la nouvelle PÉRIODE la syntaxe, vous pouvez obtenir le même résultat un peu plus facilement, comme ceci:

* SELECT FROM employee_atptWHERE EmpPERIOD CONTIENT CURRENT_DATE () -

Vous pouvez également récupérer les employés qui ont travaillé pendant une période de temps spécifique, comme ceci:

SELECT * FROM employee_atptWHERE EmpPeriod OVERLAPSPERIOD (DATE ('2012-01-01'), DATE ('2012-09-16')) -