Get best of the week

Relacionamentos entre tabelas de banco de dados

1. Introdução


A comunicação é um tópico bastante importante que deve ser entendido ao projetar bancos de dados. Por experiência própria, direi que, tendo realizado as conexões, fiquei muito mais fácil entender a normalização do banco de dados.

1.1 Para quem é este artigo?


Este artigo será útil para quem deseja entender os relacionamentos entre as tabelas do banco de dados. Nele, tentei dizer em linguagem simples o que é. Para uma melhor compreensão do tópico, alterno o material teórico com exemplos práticos apresentados na forma de um diagrama e uma consulta que cria as tabelas necessárias. Eu uso o DBMS do Microsoft SQL Server e escrevo consultas no T-SQL. O código que escrevi deve funcionar em outros DBMSs, pois as consultas são universais e não usam construções T-SQL específicas.

1.2 Como você pode aplicar esse conhecimento?


  1. O processo de criação de bancos de dados se tornará mais fácil e compreensível para você.
  2. Compreender os relacionamentos entre tabelas ajudará você a entender melhor a normalização, o que é muito importante ao projetar um banco de dados.
  3. Lidar com um banco de dados estrangeiro será muito mais fácil.
  4. Na entrevista, isso será uma vantagem muito boa.

2. Agradecimentos


Os conselhos e críticas dos autores foram levados em consideração. jobgemws, não preenchido, firnind, Hamaruba.
Obrigado!

3.1 Como as comunicações são organizadas?


Os links são criados usando chaves estrangeiras.
Uma chave estrangeira é um atributo ou conjunto de atributos que fazem referência à chave primária ou exclusiva de outra tabela. Em outras palavras, é algo como um ponteiro para uma linha em outra tabela.

3.2 Tipos de relações


Os links são divididos em:

  1. Muitos para muitos.
  2. Um para muitos.
    • com conexão obrigatória;
    • com comunicação opcional;
  3. Um a um.
    • com conexão obrigatória;
    • com comunicação opcional;

Vamos considerar em detalhes cada um deles.

4. Muitos para muitos


Imagine que precisamos escrever um banco de dados que será armazenado por um funcionário da empresa de TI. Ao mesmo tempo, há um certo conjunto padrão de postagens. Em que:

  • Um funcionário pode ter uma ou mais postagens. Por exemplo, um determinado funcionário pode ser um administrador e um programador.
  • Uma posição pode "possuir" um ou mais funcionários. Por exemplo, os administradores são um conjunto específico de trabalhadores. Em outras palavras, alguns trabalhadores pertencem a administradores.

Os funcionários são representados pela tabela Funcionário (id, nome, idade), os cargos são representados pela tabela Posição (id e nome do cargo). Como você pode ver, ambas as tabelas estão interconectadas pela regra de muitos para muitos: cada funcionário tem uma ou mais posições (muitas posições), cada posição corresponde a um ou mais funcionários (muitos funcionários).

4.1 Como construir essas tabelas?


Já temos duas tabelas que descrevem o funcionário e a profissão. Agora precisamos estabelecer muitos e muitos relacionamentos entre eles. Para implementar esse relacionamento, precisamos de algum tipo de intermediário entre as tabelas "Funcionário" e "Posição". No nosso caso, será uma determinada tabela "EmployeesPositions" (trabalhadores e posições). Esta tabela de mediação vincula o funcionário e a posição da seguinte maneira:
ID do EmpregadoPositionId
1 11 1
1 12
23
33
À esquerda, os funcionários (seu ID), à direita, as posições (seu ID). Os funcionários e os cargos nesta tabela são indicados usando o id'shniki.

Esta tabela pode ser vista de dois lados:

  1. Assim, dizemos que o funcionário com o ID 1 está na posição com o ID 1. Ao mesmo tempo, preste atenção ao fato de que nesta tabela o funcionário com o ID 1 tem duas posições: 1 e 2. Ou seja, cada funcionário à esquerda corresponde a uma certa posição à direita.
  2. Também podemos dizer que as postagens com o código 3 pertencem a usuários com os códigos 2 e 3. Ou seja, cada funcionário à direita tem um funcionário à esquerda.

4.2 Implementação


Diagrama


Código T-SQL
create table dbo.Employee
(
	EmployeeId int primary key,
	EmployeeName nvarchar(128) not null,
	EmployeeAge int not null
)

--   Employee .
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (1, N'John Smith', 22)
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (2, N'Hilary White', 22)
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (3, N'Emily Brown', 22)

create table dbo.Position
(
	PositionId int primary key,
	PositionName nvarchar(64) not null
)

--   Position .
insert into dbo.Position(PositionId, PositionName) values(1, N'IT-director')
insert into dbo.Position(PositionId, PositionName) values(2, N'Programmer')
insert into dbo.Position(PositionId, PositionName) values(3, N'Engineer')

--   EmployeesPositions .
create table dbo.EmployeesPositions
(
	PositionId int foreign key references dbo.Position(PositionId),
	EmployeeId int foreign key references dbo.Employee(EmployeeId),
	primary key(PositionId, EmployeeId)
)

insert into dbo.EmployeesPositions(EmployeeId, PositionId) values (1, 1)
insert into dbo.EmployeesPositions(EmployeeId, PositionId) values (1, 2)
insert into dbo.EmployeesPositions(EmployeeId, PositionId) values (2, 3)
insert into dbo.EmployeesPositions(EmployeeId, PositionId) values (3, 3)


Explicação
foreign key primary key unique .

  • PositionId EmployeesPositions PositionId Position;
  • EmployeeId EmployeesPositions — EmployeeId Employee;


4.3 Conclusão


Para implementar relacionamentos muitos para muitos, precisamos de um mediador entre as duas tabelas em consideração. Ele deve armazenar duas chaves estrangeiras, a primeira delas referente à primeira tabela e a segunda à segunda.

5. Um para muitos


Essa é a conexão mais comum entre bancos de dados. Consideramos isso depois de vincular muitos a muitos para comparação.

Suponha que precisamos implementar um determinado banco de dados que registre os dados do usuário. O usuário possui: nome, sobrenome, idade, números de telefone. Além disso, cada usuário pode ter um ou mais números de telefone (muitos números de telefone).

Nesse caso, observamos o seguinte: um usuário pode ter muitos números de telefone, mas não se pode dizer que um usuário específico pertence ao número de telefone.

Em outras palavras, o telefone pertence a apenas um usuário. Um usuário pode possuir 1 ou mais telefones (muitos).

Como podemos ver, esse relacionamento é um para muitos.

5.1 Como construir essas tabelas?


Os usuários serão representados por uma determinada tabela "Pessoa" (ID, nome, sobrenome, idade), os números de telefone serão representados pela tabela "Telefone". Isso parecerá assim:
PhoneIdPersonidNúmero de telefone
1 1511 091-10
2519 124-66
31721 972-02
Esta tabela representa três números de telefone. Nesse caso, os números de telefone com identificação 1 e 2 pertencem ao usuário com identificação 5. Mas o número com identificação 3 pertence ao usuário com identificação 17.
Nota . Se a tabela "Telefones" tivesse mais atributos, nós os adicionaríamos com segurança a esta tabela.

5.2 Por que não estamos fazendo uma tabela intermediária aqui?


Uma tabela intermediária só é necessária se tivermos um relacionamento muitos-para-muitos. Pela simples razão de que podemos considerá-lo de dois lados. Como a tabela EmployeesPositions anteriormente:

  1. Cada funcionário tem vários cargos (muitos).
  2. Cada posição pertence a vários funcionários (muitos).

Mas, no nosso caso, não podemos dizer que cada telefone tenha vários usuários - apenas um usuário pode pertencer a um número de telefone.
Agora leia novamente a nota no final do parágrafo 5.1. - ficará mais compreensível para você.

5.3 Implementação


Diagrama


Código T-SQL
create table dbo.Person
(
	PersonId int primary key,
	FirstName nvarchar(64) not null,
	LastName nvarchar(64) not null,
	PersonAge int not null
)

insert into dbo.Person(PersonId, FirstName, LastName, PersonAge) values (5, N'John', N'Doe', 25)
insert into dbo.Person(PersonId, FirstName, LastName, PersonAge) values (17, N'Izabella', N'MacMillan', 19)

create table dbo.Phone
(
	PhoneId int primary key,
	PersonId int foreign key references dbo.Person(PersonId),
	PhoneNumber varchar(64) not null
)

insert into dbo.Phone(PhoneId, PersonId, PhoneNumber) values (1, 5, '11 091-10')
insert into dbo.Phone(PhoneId, PersonId, PhoneNumber) values (2, 5, '19 124-66')
insert into dbo.Phone(PhoneId, PersonId, PhoneNumber) values (3, 17, '21 972-02')


Explicação
Phone . ( Person). , : « ». , id .

6. Um para um


Imagine que, no trabalho, você teve a tarefa de escrever um banco de dados para registrar todos os funcionários do RH. O chefe garantiu que a empresa precisava saber apenas o nome, idade e número de telefone do funcionário. Você desenvolveu esse banco de dados e colocou nele todos os 1000 funcionários da empresa. E então o chefe diz que, por algum motivo, eles precisam saber se o funcionário está desativado ou não. A coisa mais simples que vem à mente é adicionar uma nova coluna bool à sua tabela. Mas é muito longo para inserir 1000 valores e true você inserirá com muito menos frequência do que false (2% será verdadeiro, por exemplo).

Uma solução mais fácil seria criar uma nova tabela, vamos chamá-la de "DisabledEmployee". Isso parecerá assim:
DisabledPersonIdID do Empregado
1 1159
2722
3937
Mas este não é um relacionamento individual. O fato é que nessa tabela um funcionário pode ser inserido mais de uma vez, respectivamente, temos um relacionamento um para muitos: um funcionário pode ser desativado várias vezes. É necessário garantir que o empregado possa ser inserido na tabela apenas uma vez, respectivamente, e que possa ser desativado apenas uma vez. Para fazer isso, precisamos indicar que a coluna EmployeeId pode armazenar apenas valores exclusivos. Nós apenas precisamos impor uma restrição única na coluna EmloyeeId. Essa restrição relata que um atributo pode receber apenas valores exclusivos.

Ao fazer isso, conseguimos um relacionamento individual.

A anotação. Observe que também podemos impor uma restrição de chave primária no atributo EmloyeeId. Ele difere da restrição exclusiva apenas na medida em que não pode ser nulo.

6.1 Conclusão


Podemos dizer que um relacionamento individual é uma divisão da mesma tabela em duas.

6.2 Implementação


Diagrama


Código T-SQL
create table dbo.Employee
(
	EmployeeId int primary key,
	EmployeeName nvarchar(128) not null,
	EmployeeAge int not null
)

insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (159, N'John Smith', 22)
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (722, N'Hilary White', 29)
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (937, N'Emily Brown', 19)
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (100, N'Frederic Miller', 16)
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (99, N'Henry Lorens', 20)
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (189, N'Bob Red', 25)

create table dbo.DisabledEmployee
(
	DisabledPersonId int primary key,
	EmployeeId int unique foreign key references dbo.Employee(EmployeeId)
)

insert into dbo.DisabledEmployee(DisabledPersonId, EmployeeId) values (1, 159)
insert into dbo.DisabledEmployee(DisabledPersonId, EmployeeId) values (2, 722)
insert into dbo.DisabledEmployee(DisabledPersonId, EmployeeId) values (3, 937)


Explicação
DisabledEmployee EmployeeId, . EmployeeId Employee. , unique, , . , .

7. Comunicações obrigatórias e opcionais


As relações podem ser divididas em obrigatórias e opcionais.

7.1 Um para muitos


  1. Um para muitos com uma conexão obrigatória:
    muitos soldados pertencem a um regimento. Um lutador pertence a apenas um regimento. Observe que qualquer soldado necessariamente pertence a um regimento, e um regimento não pode existir sem soldados.
  2. Um para muitos com conexão opcional:
    todas as pessoas vivem no planeta Terra. Todo mundo vive apenas na Terra. Além disso, um planeta pode existir sem a humanidade. Assim, encontrar-nos na Terra é opcional

O mesmo relacionamento pode ser considerado obrigatório e opcional. Considere este exemplo:
Uma mãe biológica pode ter muitos filhos. Uma criança tem apenas uma mãe biológica.
A) Uma mulher não necessariamente tem seus próprios filhos. Por conseguinte, a comunicação é opcional.
B) A criança necessariamente tem apenas uma mãe biológica - nesse caso, a comunicação é necessária.

7.2 Um a um


  1. Um para um com uma conexão obrigatória:
    um cidadão de um determinado país deve ter apenas um passaporte desse país. Um passaporte tem apenas um titular.
  2. Um para um com link opcional:
    um país pode ter apenas uma constituição. Uma constituição pertence a apenas um país. Mas a constituição não é vinculativa. Um país pode ou não tê-lo, como, por exemplo, Israel e Grã-Bretanha.

A mesma conexão pode ser considerada obrigatória e opcional:
Uma pessoa pode ter apenas um passaporte. Um passaporte tem apenas um proprietário.
A) A presença de um passaporte é opcional - um cidadão pode não o ter. Este é um link opcional.
B) Um passaporte deve ter apenas um proprietário. Nesse caso, isso já é uma conexão obrigatória.

7.3 Muitos para muitos


Qualquer relacionamento muitos-para-muitos é opcional. Por exemplo:
Uma pessoa pode investir em ações de diferentes empresas (muitas). Os investidores de algumas empresas são certas pessoas (muitas).
A) Uma pessoa não pode investir seu dinheiro em ações.
B) Ninguém poderia comprar ações da empresa.

8. Como ler gráficos?


Acima, dei diagramas das tabelas que criamos. Mas, para entendê-los, você precisa saber como "lê-los". Vamos entender isso usando o exemplo do diagrama do parágrafo 5.3.



Vemos um relacionamento um para muitos. Uma pessoa possui muitos telefones.

  1. Perto da mesa, Person é uma chave de ouro. Denota a palavra "um".
  2. Perto da mesa Telefone, há um sinal de infinito. Denota a palavra "muitos".

9. Resumo


  1. As conexões são:
    • Muitos para muitos.
    • Um para muitos.
      1) com comunicação obrigatória;
      2) com um link opcional.
    • Um a um.
      1) com comunicação obrigatória;
      2) com um link opcional.
  2. As conexões são organizadas usando chaves estrangeiras.
  3. Uma chave estrangeira é um atributo ou conjunto de atributos que fazem referência à chave primária ou exclusiva de outra tabela. Em outras palavras, é algo como um ponteiro para uma linha em outra tabela.

10. Objetivos


Para uma melhor assimilação do material, sugiro que você resolva os seguintes problemas:

  1. : id, , , , . , , , .
  2. : id, , , . , .
  3. : , , ,
    • : id, , .
    • : id, .

    , — . , .
  4. 6.2. - , DisabledEmployee.

More articles:


All Articles