Memo to Team Regarding Entity and Referential Integrity Constraints - Essay Example

Colorado Technical Memo to Team Regarding Entity and Referential Integrity Constraints Phase 2 Task Prof. Russell Mickler Submitted in Partial Fulfillment of the Requirements for IT610Relational Database Management SystemsByMatthew David WarrenStudent ID: 2734799Colorado Springs, ColoradoJune 2008MemoTo: University of Tong’s New Student Class Registration System Project Team MembersFrom: Matthew David WarrenCC: Professor Russell MicklerDate: August 26, 2016RE: Entity and Referential Integrity ConstraintsTo all team members: The purpose of this memo is to emphasize the importance of the entity and referential integrity constraints in relation to the design/development of the University of Tong’s New Student Class Registration System.

In this memo, you will find an overview of the concept of entity and referential integrity along with an explanation of the area of the New Student Class Registration System where these concepts relate. Integrity constraints are applied in the design and development of database systems to maintain the accuracy and integrity of data in the database. Two types of integrity constraints entity integrity and referential integrity are taken into account for the design and development of database system.“An Entity is commonly thought of as a noun – a person, place, or thing.

In a real sense, Entities reflect Tables in the database” (Mickler, 2008). Entity integrity guarantees that there are no duplicate records within the table and that the field that identifies each record within the table is unique and never null. In general, Entity Integrity is the mechanism the system provides to maintain primary keys so that there is a unique identifier for rows in the table (database.dev.co.uk, 2008). It is good practice to have a primary key in every table so that each row can be identified uniquely.

“A primary key is typically only one field and that field is set to a special domain or type” (Mickler, 2008). A primary key, composed of one or more columns, uniquely identifies each row of a table and eliminates the possibility of duplicate rows in a table (CTU Online, 2008). According to Hoffer, Prescott & McFadden (2007), “A foreign key is an attribute in a relation of a database that serves as the primary key of another relation in the same database”. Referential integrity maintains the consistency among the rows of two relations.

The referential integrity rule states that for every foreign key value in a table, there must be a corresponding primary key value in another table in the database (CTU Online, 2008). Problems arise when we fail to relate the tables properly and inadvertently delete data in a particular table which would result in broken links or floating records (Mickler, 2008). For the new Student Class Registration System, we have to make sure that entity and referential integrity constraints are applied. Entity integrity can be achieved by having a primary key not a null field in each table of the database system that uniquely identifies each row.

In the case of our new Student Class Registration System, we can take the id field (for example Student ID in the Student table) as the AutoNumber data type primary key field. This will make sure that there is no need to insert any value for this field and it can be inserted automatically when a record for the student is added it will also ensures that this field is not null. In addition, as a student must have a degree program, therefore, we need to insert a foreign key field for the Student type (Graduate, Undergraduate, Master, or Ph.D.) in the Student table, and that foreign key field must have a primary key field in Student type table.

In conclusion, we need to make sure that there is a not null Primary key field in each table and that there is a primary key filed in the table for foreign key fields in other tables. If you have any questions regarding any of the above information, please feel free to contact me because these constraints are very important for maintaining the integrity of the data in any database management system. I am looking forward to working with all of you to ensure that we apply the concepts of entity and referential integrity during the continued design and development of the University of Tong’s New Student Class Registration System.