Answered You can hire a professional tutor to get the answer.
Assignment DetailsReview 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/conn
Assignment Details
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, FK1EmployeeIDINTPKRateChangeDateDateRateDECIMALPayFrequencyDECIMALTable:EmpDeptHistoryDatatype (Length)PK, FK2EmployeeIDINTPK, FK1DepartmentIDINTPKStartDateDATEPK, FK3ShiftIDINTEndDateDATETable:ShiftDatatype (Length)PKShiftIDINTNameVARCHAR (30)StartTimeDATEEndTimeDATETable: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.
