Database Design Discusion Posts

Part 1 Initial Post

Referential Integrity and Intersection Table

Hello Class! Please respond to BOTH of the following questions:

Question A

Referential integrity "rules" trigger some cleanup of your data when insertion, update, or deletion events would typically cause data anomalies. But the word "trigger" has its own place in database design and implementation. Research the concept of a "trigger" and compare and contrast "trigger" events with referential integrity rules.

Question B

Discuss the concept of the intersection table. Why is it needed in many to many relationships? Why is intersection data needed?

PART 2 Peer Responces

Respond to both peers discusion posts

Peer #1 Bill

Triggers are somewhat like a procedure is in computer languages such as a Java or JavaScript. Triggers are able to perform a few different functions whenever the operation is called; Insert, Update, Delete, Create, Alter, or perform events such as logon and off or startup and shutdown. Trigger events assist tables with referential integrity by maintaining their orphaned records. That is, inserting a child record when no parent record exists; deleting a parent record, and leave the child record alone; and changing a parent's record's pk value so that the fk no longer point to a valid parent record.

An intersection table provides an intersection between two components and is a table that defines many-to-many relationships. The intersection table is also created as another table to eliminate many-to-many relationships of two tables. The table contains two foreign keys that defines the many-to-many relationship between two separate tables.

Peer #2 John

Referential integrity rules are used when you want to update, insert, or delete information in certain tables. For instance, Figure 5.13 Saleperson and Office 1:1 relation table, were one office is occupied with one and only one Saleperson. If Saleperson 361 that occupies office 1227 is relocated to another building, then once the Saleperson is deleted from office 1227 in the office table it will be updated in the Saleperson table for Saleperson 361. Thus office 361 is vacant and can be use.

Triggers are used normally executed when an event associated with a specific table occurs in conjunction with an insert, delete, or update. You can also have the trigger execute before or after an update, deletion, or insert request for a table. For instance if we use Figure 5.14 of the General Hardware Company relational database. If a customer want to purchase a specific product form a company I would want to check to see if that company had that item in stock first. I would place a trigger on products table. The trigger would check the current stock of the item before the item is placed in the customer's checkout cart.

An intersection table is needed between a many to many relationship because it brings together information from two tables and gives it substance. You need an intersection table in order to account for the many to many relations of the entities or values that you want to represent in the table. For instance, you can have a Student table and a Class table. Since many students can be enrolled in multiple classes and Classes can have multiple Students you need to be able to represent and account for those values. If you put the PK of Students in the Class table you could not properly represent all of the students that are in a particular class because the cell can only hold one value and the same thing holds for the PK for the Classes in the Students table. So the best option you have is to put the PK for both Classes and Students in an intersection table in order to account for the multiple students and classes that with multiple values.