IT SQL work week 3

profileencdannekga

 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, 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
Answer(1)

Purchase the answer to view it

blurred-text
NOT RATED
  • attachment
    DatabaseandTablesScript.sql
  • attachment
    DataInsertintoTablesScript.sql
  • attachment
    DDL.docx