create DATABASE [HumanResources] ON (NAME = N'HumanResources_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\HumanResources_data.mdf' , SIZE = 3, FILEGROWTH = 10%) LOG ON (NAME = N'HumanResources_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\HumanResources_log.ldf' , SIZE = 1, FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS GOuse [HumanResources] Go--first we create our departments table used for storing --a list of departments within our database create table dbo.Departments( DepartmentId int not null Identity(1,1) primary key, [Description] varchar(256) not null ) --Next we create our Employees table. This is the core table of --our database and will contain a list of the organizatin's employeesCreate table dbo.Employees( EmployeeId int not null Identity(1,1) primary key, FirstName varchar(30) not null, SurName varchar(30) not null,
DateOfBirth smalldatetime not null--The check constraint ensures that the employee is --over 15 years old but younger than 100 years oldcheck(DateOfBirth >DateAdd(yy,-100,getDate()) and DateofBirth<Dateadd(yy,-15,getdate())),SecurityPhoto image null,DateOfHire smalldatetime not null --The check constraint ensures that employee --was hired in the last 100 years or is intended ot -- be hired in the next five yearscheck(dateofHire>dateAdd(yy,-100,getdate())and dateOfHire< DateAdd(yy,5,GetDate())),Department int references Departments(DepartmentId) not null) go--Next we crete the Salaries table.This is used to store --The salary records,current and historical,for the employeesCreate Table dbo.Salaries( EmployeeID int not null REFERENCES Employees(employeeID),
SalaryAppliedFrom smalldatetime not null, --The check aonstraint ensures that the salary was applied --within the last 100 years,but is not scheduled to be --applied more than 1 year into the future SalaryLevel money not null,--The Check constraint ensures that the Salary is more than 0 --but less than 10000 primary key (EmployeeID,SalaryAppliedFrom), constraint salary_ck Check(SalaryAppliedFrom>DateAdd(yy,-100,getdate()) and SalaryAppliedFrom <=Dateadd(yy,1,getDate()))) --Now we create the Courses tables.This contains training coures --available for our employees.Create table dbo.Courses( CourseID int not null identity(1,1) primary key,CourseDesc varchar(50) not null, CourseDate smallDateTime not null, --The Check constraint ensures the Course is scheduled for --sometime in the next five years Location varchar(100) not null,MaxNoAttendees int not null --The Check constraint ensures that the maxinum number of --aatendees on the coures is at lease1.check(MaxNoAttendees>0), constraint coursedate_ck Check(CourseDate<=dateadd(yy,5,getdate())) ) --Next we create our CourseAttendess table .This associates an --Employee with a specific course that they are attending.create table dbo.courseAttendees( EmployeeID int not null references employees(employeeID),CourseID int not null references Courses(CourseID), primary key(employeeID,CourseID) )--Our PaymentTypes table contains a list of diffent types of --payments that can be made to employeesCreate table dbo.PaymentTypes( PaymentTypeID tinyint not null IDENTITY(1,1) primary key,PaymentDesc varchar(30) not null ) go--Our final table created is Payments.This table will --contain a list of payments that have been made to employeescreate Table dbo.Payments ( PaymentID int not null, EmployeeID int Not null references employees(EmployeeID), Amount money not null check(amount>0 and amount<=100000),PaymentDate smalldatetime not null --The check constraint ensures that the payment was made --at some stage during the last 100 years or is scheduled 1 --year into the futurecheck(paymentDate>DateAdd(yy,-100,getdate()) and PaymentDate<=DateAdd(yy,1,getDate())),PaymentTypeID tinyint not nullReferences PaymentTypes(PaymentTypeID), primary key(EmployeeID,PaymentID) ) Go--Fianlly we add a foreign key to tour Departments table --that references an employee row.alter table dbo.Departments add DepartmentHeadID int null references Employees(EmployeeID) GO
COLLATE SQL_Latin1_General_CP1_CI_AS
GOuse [HumanResources]
Go--first we create our departments table used for storing
--a list of departments within our database
create table dbo.Departments(
DepartmentId int not null Identity(1,1) primary key,
[Description] varchar(256) not null
)
--Next we create our Employees table. This is the core table of
--our database and will contain a list of the organizatin's employeesCreate table dbo.Employees(
EmployeeId int not null Identity(1,1) primary key,
FirstName varchar(30) not null,
SurName varchar(30) not null,
DateOfBirth smalldatetime not null--The check constraint ensures that the employee is
--over 15 years old but younger than 100 years oldcheck(DateOfBirth >DateAdd(yy,-100,getDate()) and
DateofBirth<Dateadd(yy,-15,getdate())),SecurityPhoto image null,DateOfHire smalldatetime not null
--The check constraint ensures that employee
--was hired in the last 100 years or is intended ot
-- be hired in the next five yearscheck(dateofHire>dateAdd(yy,-100,getdate())and dateOfHire< DateAdd(yy,5,GetDate())),Department int references Departments(DepartmentId) not null)
go--Next we crete the Salaries table.This is used to store
--The salary records,current and historical,for the employeesCreate Table dbo.Salaries(
EmployeeID int not null REFERENCES Employees(employeeID),
SalaryAppliedFrom smalldatetime not null,
--The check aonstraint ensures that the salary was applied
--within the last 100 years,but is not scheduled to be
--applied more than 1 year into the future
SalaryLevel money not null,--The Check constraint ensures that the Salary is more than 0
--but less than 10000
primary key (EmployeeID,SalaryAppliedFrom),
constraint salary_ck Check(SalaryAppliedFrom>DateAdd(yy,-100,getdate()) and SalaryAppliedFrom <=Dateadd(yy,1,getDate())))
--Now we create the Courses tables.This contains training coures
--available for our employees.Create table dbo.Courses(
CourseID int not null
identity(1,1) primary key,CourseDesc varchar(50) not null,
CourseDate smallDateTime not null,
--The Check constraint ensures the Course is scheduled for
--sometime in the next five years
Location varchar(100) not null,MaxNoAttendees int not null
--The Check constraint ensures that the maxinum number of
--aatendees on the coures is at lease1.check(MaxNoAttendees>0),
constraint coursedate_ck Check(CourseDate<=dateadd(yy,5,getdate()))
)
--Next we create our CourseAttendess table .This associates an
--Employee with a specific course that they are attending.create table dbo.courseAttendees(
EmployeeID int not null
references employees(employeeID),CourseID int not null
references Courses(CourseID),
primary key(employeeID,CourseID)
)--Our PaymentTypes table contains a list of diffent types of
--payments that can be made to employeesCreate table dbo.PaymentTypes(
PaymentTypeID tinyint not null
IDENTITY(1,1) primary key,PaymentDesc varchar(30) not null
)
go--Our final table created is Payments.This table will
--contain a list of payments that have been made to employeescreate Table dbo.Payments
(
PaymentID int not null,
EmployeeID int Not null
references employees(EmployeeID),
Amount money not null
check(amount>0 and amount<=100000),PaymentDate smalldatetime not null
--The check constraint ensures that the payment was made
--at some stage during the last 100 years or is scheduled 1
--year into the futurecheck(paymentDate>DateAdd(yy,-100,getdate()) and PaymentDate<=DateAdd(yy,1,getDate())),PaymentTypeID tinyint not nullReferences PaymentTypes(PaymentTypeID),
primary key(EmployeeID,PaymentID)
)
Go--Fianlly we add a foreign key to tour Departments table
--that references an employee row.alter table dbo.Departments
add DepartmentHeadID int null
references Employees(EmployeeID)
GO