IT SQL work week 3
encdannekgaAssignment 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, 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.
- 6 years ago
- 20
Purchase the answer to view it
- DatabaseandTablesScript.sql
- DataInsertintoTablesScript.sql
- DDL.docx
- Academic Language function
- Hi Tom I have a new assignment Advance Marketing Unit II Scholarly Activity Marketing Plan: Company Overview and Market Research ·Overview of Company ·Market Research Strategies · Analyzing Macro-environment-PEST Analysis In this section of
- APA apprenticeship proposal
- $12 fixed price 3pages due in 12 hours Research Table
- State and Local Gov: 6 page Policy Related Research Paper
- IT Management Capstone
- I need only expert please no delay
- Introduction to Transportation, Logistics, and Supply Chain Management
- NUR-502 Week 4 Collaborative Learning Community, Grand Nursing Theorist Assignment, Theorist Identification and Rationale
- Power of Observation