Add referential integrity between:

a. Department table and Employee table, add foreign key constraint in the Employee table.

b. Employee table and Dependent table, add foreign key constraint in the Dependent table

c. Department table and Project table, add foreign key constraint in the Project table .

d. Employee table and EmployeeProject table, add foreign key constraint in the EmployeeProject table.

e. Project table and EmployeeProject table, add foreign key constraint in the EmployeeProject table.

2. Add a domain constraint to the Salary field of the Employee table: Salary is between 500000 and 10000.

3. Add a domain constraint to the Sex field of the Dependent table: valid sex code; F, M (you may add other valid sex codes)

4. Test the constraints by trying to add a record that violates the constraints:

a. Add a new employee record with a duplicated employeeSSN.

b. Add a new employee record where the deptNumber does not exist in the Department table.

c. Add a new dependent record where the employeeSSN does not exist in the Employee table.

d. Add a new employee record with $5,000 salary.

e. Add a new dependent record with an invalid sex code.

f. Change one department’s number, deptNumber, in the department table, and see the changes of deptNumber in the employee table.

5. Submit the followings:

a. For each of the constraint violation in step 4, copy the error message and paste to the Word document.

b. Show the employee records with updated deptNumber.

c. Submit the Word document by email attachment.

this assignement must be done using the database software mySQL.

Your mydb schema of assignment 3 should be similar to this design, if not, you should make changes:

Department table: deptNumber, deptName

Employee table: employeeSSN, empName, address, birthday, salary, deptNumber

Dependent table:dependentSSN, name, sex, birthday, employeeSSN

Project table: projectID, projName, deptNumber employee

Project table: employeeSSN, projectID

Answer rating: 100% (QA)

This seems to be a very comprehensive assignment related to MySQL involving table creation with referential integrity domain constraints and constrain... View the full answer