Review the following tutorials:
Tutorial: Connect to and query a SQL Server instance by using SQL Server Management Studio – https://docs.microsoft.com/en-us/sql/ssms/tutorials/connect-query-sql-server
SQL Tutorial – use links on left of the screen to navigate the SQL topics – https://www.w3schools.com/sql/default.asp
You will now create a database for the following seven tables. You will build upon this database in the upcoming units of the course.
Create a database containing the following tables:
Table:DepartmentDatatype (Length)PKDepartmentIDINTNameVARCHAR (50)LocationVARCHAR (50)
TableEmployeeDatatype (Length)PKEmployeeIDINTNationalIDINTFK2ContactIDINTLoginIDINTFK1ManagerIDINTShiftIDINTTitleVARCHAR (3)BirthDateDATEMaritalStatusVARCHAR (20)GenderVARCHAR (1)HireDateDATESalariedDECIMALVacationHoursDECIMALSickLeaveHoursDECIMALCurrentVARCHAR (1)
* 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.
Table:EmployeeAddressDatatype (Length)FK1EmployeeIDINTPKAddressIDINTStreetVARCHAR (150)CityVARCHAR (50)StateVARCHAR (2)ZIPVARCHAR (5)
Table:PayHistoryDatatype (Length)PK, FK1EmployeeIDINTPKRateChangeDateDateRateDECIMALPayFrequencyDECIMAL
Table:EmpDeptHistoryDatatype (Length)PK, FK2EmployeeIDINTPK, FK1DepartmentIDINTPKStartDateDATEPK, FK3ShiftIDINTEndDateDATE
Table:ShiftDatatype (Length)PKShiftIDINTNameVARCHAR (30)StartTimeDATEEndTimeDATE
Table:JobCandidateDatatype (Length)PKJobCandidateIDINTFKEmployeeIDINTResumeVARCHAR (500)
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. Submit a zip file containing your database and log files containing your database diagram, tables, and sample data.