After you accomplish all the above tasks, save the database. Upload it to Canvas by 11:59 pm, Monday (Nov. 12^th).

Download WPC.accdb from Blackboard. Open the database and examine relationships between the four tables first.

DEPARTMENT (DepartmentName, BudgetCode, OfficeNumber, Phone)

EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Phone, Email)

PROJECT (ProjectID, Name, Department, MaxHours, StartDate, EndDate)

ASSIGNMENT (ProjectID, EmployeeNumber, HoursWorked)

Recently WPC has decided to keep track of computers used by the employees.In order to do this, two new tables will be added to the database.The schema for these tables as related to the existing EMPLOYEE table is:

EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Phone, Email)

COMPUTER (SerialNumber, Make, Model, ProcessorType, ProcessorSpeed, MainMemory, DiskSize)

COMPUTER_ASSIGNMENT (SerialNumber, EmployeeNumber, DateAssigned, DateReassigned)

The referential integrity constraints are:

SerialNumber in COMPUTER_ASSIGNMENT must exist in SerialNumber in COMPUTER

EmployeeNumber in COMPUTER_ASSIGNMENT must exist in EmployeeNumber in EMPLOYEE

EmployeeNumber is a surrogate key and never changes.Employee records are never deleted from the database.SerialNumber is not a surrogate key since it is not generated by the database.However, a computer’s SerialNumber never changes, and, therefore, there is no need to cascade updates.When a computer is at its end of life, the record in COMPUTER for that computer and all associated records in COMPUTER_ASSIGNMENT are deleted from the database.

Run SQL statements in Access to obtain your results for the following questions.For each SQL statement you write, save it as XXXQueryname (You need to name each query as appropriate.) in WPC.accdb. If necessary, you need to manually modify the database to add data requirements and constraints not supported by Access SQL.

• Figure 1 shows the column characteristics for the WPC COMPUTER table.Using the column characteristics, use Access SQL to create the COMPUTER table and its associated constraints in the WPC database.Are there any table characteristics that cannot be created in SQL?Use the Access GUI to finish setting table characteristics if necessary.
• The data for the COMPUTER table is in Figure 2.Use Access SQL to enter this data into your COMPUTER table. Save each query.
• Figure 3 shows the column characteristics for the WPC COMPUTER_ASSIGNMENT table.Using the column characteristics, use Access SQL to create the COMPUTER table and its associated constraints in the WPC database.Are there any table characteristics or relationship settings or characteristics that cannot be created in SQL?Use the Access GUI to finish setting table characteristics and relationship settings if necessary.
• The data for the COMPUTER_ASSIGNMENT table is in Figure 4.Use Access SQL to enter this data into your COMPUTER_ASSIGNMENT table.

****SEE ATTACHMENT FOR FULL INSTRUCTIONS*****