DBST 651 UMGC Relational Database Model Discussion

Please read and comment/contribute to peers’ discussions. 

Peer 1: What is your understanding of Entity Integrity Vs Referential Integrity. Illustrated with simple examples for ease of understanding of the concepts.

An “entity integrity” is any person, place, or thing that is recorded in a database, with each table representing one specific entity, and each row within the table representing an instance of the entity (IBM, 2021). One example of this would be the “entity integrity” being labeled “student”, with each row within that entity being a specific name (e.g., Daniella, Carli, Chad, Michael, and Ashley). In comparison, a “referential integrity” refers to the relationship between different tables within a database (IBM, 2021). Continuing with the example, the student name “Chad” can then be carried into different tables within the database (e.g., class roster, dormitory list, etc.) making that primary key repeated. 

2. From your reading of the text, describe various types of keys (Primary, Foreign, Super Key etc.) with simple examples.

Within database systems management, keys are used as an attribute or set of attributes that helps the user: identify a row within a table, find the relationship between two tables. as well as finding a unique record or row (Peterson, 2022). The following are the three main types of keys as well as a brief description of each: 1) “Primary key” is a column or group of columns in a table that uniquely identify every row in that table; 2) “Foreign key” is a column that creates a relationship between two tables. The purpose of Foreign keys is to maintain data integrity and allow navigation between two different instances of an entity; and 3) “Super key” is a group of single of multiple keys which identifies rows in a table (Peterson, 2022). 

3. Think about what you plan to do for your lab project. Based upon your lab project describe 3 entities and illustrate different types of joins using these 3 entities. If you have not yet decided about your lab project then assume you have three entities student, course & enrollment and these tables are related.

For this week’s lab activity I decided to illustrate the aspects of a person’s health and the potential impacting factors. Three of the five entities I used were “person” (e.g., age, weight, family history, smoking, and height), “positive intake” (e.g., proteins, fats, carbohydrates, supplements, and fruits and vegetables, and “negative intake” (e.g., soda, drugs, white grains, sugar, and alcohol). The joins I used varied from “mandatory” (e.g., person has to be included in every relationship”, as well as “optional” (e.g., the person does not have to take part in consuming “negative intake”. In addition, while there can only be “one” “person”, there can be “many” “positive intakes”.  

References:
IBM. (2021). Referential integrity. IBM. https://www.ibm.com/docs/en/informix-servers/14.10?topic=integrity-referential

Peterson, R. (2022, August 25). DBMS Keys:
Candidate, Super, Primary, Foreign Key Types with Example. Guru99. https://www.guru99.com/dbms-keys.html#3

Peer 2: 

1. My understanding of Entity Integrity so far, is that it ensures the tuples in a table all are uniquely identified and there are no duplicates (IBM, 2021). This is normally accomplished with a primary key that serves to prevent nulls from being included. Also, entity integrity allows rows in a table to be distinguished from each other. An example of this using the student ID example would be the prevention of two different students having the same ID number within the same table. Referential Integrity is more so in relation to the synchronization of relationships between tables using foreign keys, the primary goal being to prevent the creation of orphan records where a record in a child table cannot be linked to a parent table record (Database Applications, 2017). An example of this would be where two tables exist, one related to physicians and the other patients, where both tables contain a Physician ID tuple. In order to maintain referential integrity, the physician ID section in the patient table can only accept values that also exist in the physician table, and physician ID’s in the physician table cannot be changed if doing so would mean a record in the patient table no longer relate to a valid physician ID value.

2.There are several different types of keys: Super, candidate, primary, surrogate, secondary, and foreign (Peterson, 2019). Peterson writes that a super key can be a single attribute or multiple attributes that help to uniquely identify tuples, such as a student number. A candidate key, a subset of a super key, is any set of attributes that helps to identify tuples within a table with no repeated attributes. A candidate key example can be a student ID or student email. Every table needs at least one candidate key.  A primary key is a single or multiple columns that uniquely identify every tuple in the table without duplicates, however there can only be one primary key in a table, and it is selected from the available candidate keys. Again, student ID can serve as a primary key. A surrogate key is able to uniquely identify each tuple without lending any meaning to the data within the table, typically in the form of integers like a shift start or end time in an employee database. Surrogate keys are also artificial, meaning they are generated by the system. Secondary keys are used for quicker retrieval of data and can don’t have to be entirely unique like a first name and last name key within the same table. Lastly, a foreign key, which creates referential integrity, creates a relationship between two different tables due to the sharing of a primary key. An example here could be two tables, one detailing department codes and names in a school, the other detailing different teacher’s names and their ID’s, where department code can be inserted into the second table to help establish which teacher works in what department, thus establishing a relationship between the two tables (Peterson, 2019).

3. 3 Entities I plan to use in my lab this week are Patient (patient ID, age, height, weight, race, blood pressure), Physician (Physician ID, Physician name, physician phone number, department, office number) and Hospital (Hospital ID, hospital name, Hospital address, number of beds, hospital service). It is difficult to illustrate some joins without the use of foreign keys, so for this purpose lets assume the patient table also has the attribute Physician ID. Now these two entities can be joined naturally using the common attribute, leaving any unmatched pairs out. Another way to join these would be using a left outer join, where we can imagine the hospital attribute also has a department key. In this join, physician and hospital entities can be joined with rows matching based off of department, with any hospital row not having a related physician in that department will be left with a null value.

References:

Database Applications. (2017). New Page 1. Www.pkirs.utep.edu. http://www.pkirs.utep.edu/cis4365/Tutorials/RDBMS/tut200050b/tut2000050b.htm

IBM. (2021). Entity integrity, referential integrity, and referential constraints. Www.ibm.com. https://www.ibm.com/docs/en/zos-basic-skills?topic=rules-entity-integrity-referential-integrity-referential-constraints

Peterson, R. (2019, September 21). DBMS Keys: Primary, Candidate, Super, Alternate and Foreign (Example). Guru99.com. https://www.guru99.com/dbms-keys.html