العلاقات بين جداول قاعدة البيانات

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)

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

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')

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)