Create a database containing the following tables: * NOTES: Salaried and Current
ID: 3919223 • Letter: C
Question
Create a database containing the following tables:
* NOTES: Salaried and Current columns will hold Y/N data to indicate whether an employee is or is not salaried and is or is not a current employee respectively.
Create the needed tables in the SQL server environment.
Add the primary keys and unique keys as specified.
Add appropriate relationships based on the provided information in the table information and diagram above to establish the foreign keys specified.
Create a database diagram, and generate the data definition language (DDL) for the tables.
Add at least 4 rows of sample data to each table except Employee. The employee table must have at least 8 rows of sample data.
In the Departments table, the Primary Key, DepartmentID values must be between 8000 and 8999
In the Employees table, the Primary Key, EmployeeID values must be between 1000 and 1999.
In the EmployeeAddress table, the Primary Key, AddressID values must be between 2000 and 2999
In the Shift table, the Primary Key, ShiftID values must be 01, 02, 03, 04 as there are 4 shifts.
In the JobCandidate table, the Primary Key, JobCandidateID must be between 5000 and 5999.
All dates should be appropriate based on the table and within the time period of the company’s existence. The company opened on July 14, 2011.
Table: Department Datatype (Length) PK DepartmentID INT Name VARCHAR (50) Location VARCHAR (50)Explanation / Answer
Create the needed tables in the SQL server environment. Add the primary keys and unique keys as specified. Add appropriate relationships based on the provided information in the table information and diagram above to establish the foreign keys specified.
Answer)
create table Department(
DepartmentID INT primary key,
Name VARCHAR(50),
Location VARCHAR (50)
);
create table Employee(
EmployeeID INT primary key,
NationalID INT,
ContactID INT,
LoginID INT,
ManagerID INT FOREIGN KEY REFERENCES Employee(EmployeeID),
ShiftID INT,
Title VARCHAR(3),
BirthDate DATE,
MaritalStatus VARCHAR(20),
Gender VARCHAR(1),
HireDate DATE,
Salaried DECIMAL,
VacationHours DECIMAL,
SickLeaveHours DECIMAL,
Current VARCHAR(1)
);
create table EmployeeAdress(
EmployeeID INT FOREIGN KEY REFERENCES Employee(EmployeeID),
AddressID INT primary key,
Street VARCHAR(150),
City VARCHAR(50),
State VARCHAR(2),
ZIP VARCHAR(5)
);
create table PayHistory(
EmployeeID INT FOREIGN KEY REFERENCES Employee(EmployeeID),
RateChangeDate Date,
Rate DECIMAL,
PayFrequency DECIMAL,
primary key(EmployeeID,RateChangeDate)
);
create table Shift(
ShiftID INT primary key,
Name VARCHAR(30),
StartTime DATE,
EndTime DATE
);
create table EmpDeptHistory(
EmployeeID INT FOREIGN KEY REFERENCES Employee(EmployeeID),
DepartmentID INT FOREIGN KEY REFERENCES Department(DepartmentID),
StartDate DATE,
ShiftID INT FOREIGN KEY REFERENCES Shift(ShiftID),
EndDate DATE
);
create table JobCandidate(
JobCandidateID INT primary key,
EmployeeID INT FOREIGN KEY REFERENCES Employee(EmployeeID),
Resume VARCHAR (500)
);
Create statements according to the requirements of the Questions are as above.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.