Como trabalhar com SQL: 2011 aplicativo de tempo Período Tabelas

Considere um exemplo usando tabelas de tempo de período de aplicação SQL. Suponha que uma empresa quer acompanhar o que departamento de seus funcionários pertencem a a qualquer momento durante todo o período de trabalho. O negócio pode fazer isso criando tabelas de período de tempo de aplicação para os funcionários e departamentos:

CRIAR employee_atpt TABLE (EmpID INTEIRO, EmpStart DATA, EmpEnd DATA, EmpDept VARCHAR (30), o prazo de EmpPeriod (EmpStart, EmpEnd)) -

A data e hora de início (EmpStart no exemplo) está incluído no período, mas a data e hora de fim (EmpEnd no exemplo) não é. Isto é conhecido como a semântica fechou-abertas.

INSERT INTO employee_atptVALUES (12345, data '2011-01-01', data '9999-12-31', 'Vendas') -

O quadro resultante tem uma linha, conforme mostrado na Tabela 7-1.

EmpIDEmpStartEmpEndEmpDept
123452011-01-019999-12-31Vendas

A data final de 9999-12-31 indica que a posse deste funcionário com a empresa ainda não acabou.

Agora, suponha que em 15 de março de 2012, empregado 12345 é temporariamente atribuído ao departamento de Engenharia até 15 de Julho de 2012, retornando para o departamento de vendas depois. Você pode fazer isso com o seguinte ATUALIZAR declaração:

ATUALIZAÇÃO employee_atptFOR PARTE DO EmpPeriodFROM DATA '2012-03-15'TO DATA' 2012-07-15'SET EmpDept = 'Engineering'WHERE EmpID = 12345-

Após a atualização, a tabela tem agora três linhas.

EmpIDEmpStartEmpEndEmpDept
123452011-01-012012-03-15Vendas
123452012-03-152012-07-15Engenharia
123452012-07-159999-12-31Vendas

Assumindo empregado 12345 ainda é empregado no departamento de vendas, a tabela registra com precisão a sua filiação departamento de Dia de Ano Novo de 2011, até o presente momento.

Excluindo dados de uma tabela de período de tempo de aplicação pode ser um pouco mais complicado do que simplesmente apagando registros de, uma tabela nontemporal comum. Como exemplo, suponha que o empregado 12345 deixa a empresa em 15 de março de 2012and é recontratado em 15 de julho do mesmo ano. Inicialmente, a tabela período, o tempo de aplicação terá uma linha.

EmpIDEmpStartEmpEndEmpDept
123452011-01-019999-12-31Vendas

UMA EXCLUIR declaração irá atualizar a tabela para mostrar o período durante o qual 12345 empregado tinha ido embora:

APAGAR employee_atptFOR PARTE DO EmpPeriodFROM DATA '2012-03-15'TO DATA' 2012-07-15'WHERE EmpID = 12345-
EmpIDEmpStartEmpEndEmpDept
123452011-01-012012-03-15Vendas
123452012-07-159999-12-31Vendas

A tabela agora reflete os períodos de tempo durante o qual empregado 12345 foi empregado pela empresa e mostra o fosso durante o qual ela não foi empregado pela empresa.

Uma tabela de período de tempo de aplicação de funcionários pode conter várias linhas para um único funcionário. O número de identificação do empregado, por si só, não é utilizada como chave primária da tabela. Os dados temporais devem ser adicionados à mistura.

Como designar chaves primárias em tabelas de período de tempo de aplicação

Para garantir que não haja duplicação de linhas, a data de início (EmpStart) e uma data final (EmpEnd) deve ser incluída na chave primária. No entanto, apenas incluindo deles não é suficiente. Considere o caso em que o trabalhador 12345 foi transferido para Engenharia por alguns meses, e depois voltou para seu departamento de casa.

EmpIDEmpStartEmpEndEmpDept
123452011-01-019999-12-31Vendas
123452012-03-152012-07-15Engenharia

Repare que os dois períodos de tempo sobrepõem. Parece que empregado 12345 é um membro de ambos o departamento de Vendas eo departamento de engenharia de 15 de Março de 2012 até 15 de julho de 2012.

Isso adiciona complicação e pode levar à corrupção de dados. Impor uma restrição que diz que um empregado pode ser um membro de apenas um departamento de cada vez é talvez o que a maioria das organizações iria querer fazer. Você pode adicionar uma tal restrição a uma tabela com um ALTERAR A TABELA declaração como a seguinte:

ALTER TABLE employee_atptADD PRIMARY KEY (EmpID, EmpPeriod SEM sobreposições) -

Há uma maneira melhor de fazer as coisas que a criação de uma tabela de primeiro e adicionando a sua restrição de chave primária depois - em vez disso, você pode incluir a restrição de chave primária no original CRIO declaração. Pode parecer como o seguinte:

CREATE TABLE employee_atptEmpID INTEGER NOT NULL, EmpStart DATE NOT NULL, DATA EmpEnd NOT NULL, EmpDept VARCHAR (30), o prazo de EmpPeriod (EmpStart, EmpEnd) CHAVE PRIMÁRIA (EmpID, EmpPeriod SEM sobreposições)) -

Como aplicar restrições de referência para tabelas de período de tempo de aplicação

Qualquer banco de dados que se destina a manter mais do que uma simples lista de itens provavelmente vai exigir várias tabelas. Se um banco de dados tem várias tabelas, as relações entre as tabelas devem ser definidos, e restrições de integridade referencial deve ser posta em prática.

Você tem uma tabela de período de tempo de aplicação dos funcionários e uma tabela de período de tempo de aplicação departamento. Existe uma relação de um-para-muitos entre a tabela de departamento e a tabela de funcionários, porque um departamento pode ter vários empregados, mas cada funcionário pertence a um e apenas um departamento.

Isso significa que você precisa colocar uma chave estrangeira para a tabela de funcionários que referencia a chave primária da tabela de departamento. Com isto em mente, use uma mais completa CRIO declaração, e criar uma tabela de departamento:

CRIAR employee_atpt TABLE (EmpID INTEGER NOT NULL, EmpStart DATE NOT NULL, EmpEnd DATE NOT NULL, EmpName VARACHAR (30), EmpDept VARCHAR (30), o prazo de EmpPeriod (EmpStart, EmpEnd) CHAVE PRIMÁRIA (EmpID, EmpPeriod SEM sobreposições) FOREIGN KEY (EmpDept, PERÍODO EmpPeriod) Referências dept_atpt (DEPTID, PERÍODO DeptPeriod)) - CRIAR dept_atpt TABLE (DEPTID VARCHAR (30) NOT NULL, Gerente de VARCHAR (40) NOT NULL, DeptStart DATE NOT NULL, DATA DeptEnd NOT NULL, o prazo de DeptTime ( DeptStart, DeptEnd), PRIMARY KEY (DEPTID, DeptTime SEM sobreposições)) -

Como consultar tabelas de período de tempo de aplicação

Agora, a informação detalhada pode ser recuperado a partir da base de dados através da utilização SELECIONAR declarações que fazem uso dos dados temporais.

Uma coisa que você pode querer fazer é listar todas as pessoas que estão atualmente empregados pela organização. Mesmo antes de SQL: 2011, você poderia fazê-lo com uma declaração semelhante ao seguinte:

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

Com o novo PERÍODO sintaxe, você pode obter o mesmo resultado um pouco mais fácil, como este:

* SELECT FROM employee_atptWHERE EmpPERIOD CONTÉM CURRENT_DATE () -

Você também pode recuperar empregados que foram contratados durante um período específico de tempo, assim:

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

Semelhante

menu