Database Instance: PROJECT Code ASSIGNMENT Title EmployeelD ProjectCode Hours 10

Question

Database Instance: PROJECT Code ASSIGNMENT Title EmployeelD ProjectCode Hours 10 Highway Construction 102 Bridge Testing 103 Traffic Measurement 104 Disaster Recovery 105 Flood Control 102 102 101 104 105 75 20 25 100 20 41485 29030 41485 EMPLOYEE ID DEPARTMENT Code Name DeptCode Name Management Research 41485 Maria Colorado 29030 Robert Carolina 86262 Nathan Arizona 17112 Suzanne Nevada3 3 Engineering a. List the attribute(s) that make up the primary key (if one exists) in PROJECT b. List the attribute(s) that make up the primary key (if one exists) in ASSIGNMENT c. List the attribute(s) that make up the primary key (if one exists) in EMPLOYEE. d. List the attribute(s) that make up the foreign key(s) (if any exist) in PROJECT e. List the attribute(s) that make up the foreign key(s) (if any exist) in ASSIGNMENT f. List the attribute(s) that make up the foreign key(s) (if any exist) in EMPLOYEE g. Construct a new tuple that can be inserted into ASSIGNMENT without violating any constraints. h. Construct a new tuple that cannot be inserted into ASSIGNMENT because doing so would violate referential integrity (but would not violate any other constraints) i. Construct a new tuple that can be inserted into EMPLOYEE without violating any constraints. j. Construct a new tuple that cannot be inserted into EMPLOYEE because doing so would violate a key constraint (but would not violate any other constraints). k. Which tuples in PROJECT could be removed without violating referential integrity, and which tuples in PROJECT would cause referential integrity to be violated if they were removed?

Explanation / Answer

1) In Project Table, Code is the primary key because it is unique.
2) In Assignment table, no primary key can be made.
3) In Employee Table, ID column is the primary key
4) In Project Table, There is no foreign key.
5) In assignment table, EmployeeId and ProjectCode are the Foreign Keys.
6) In Employee table, DeptCode is the Foreign Key.
7) INSERT INTO ASSIGNMENT(EmployeeID, ProjectCode, Hours)
VALUES (41485,102,60);
8)INSERT INTO ASSIGNMENT(EmployeeID, ProjectCode, Hours)
VALUES (41486,108,60);
It is violating referential Integrity since 41486 is not valid employee Id and
108 is not valid project code.(These values are not present in respective tables).
9)INSERT INTO EMPLOYEE(ID, Name, DeptCode)
VALUES (41487,'Lara craft',2);
10) INSERT INTO EMPLOYEE(ID, Name, DeptCode)
VALUES (41488,'Lara craft',4);
It is violating referential Integrity since deptCode is not valid since it is not present in
DEPARTMENT table.

11) You can remove the TITLE without violating referential Integrity.
If you remove Project CODE which being used as a foreign key in ASSIGNMENT
the table will create a referential Integrity Violation.

Thanks

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.