Relational databases are derived from relational/logical model. The relational/logical model is based on first order predicate logic and set theory and has no graphical representation unlike EER. The relational model is represented in sets of tuples, or set of ordered list of attribute values for an entity. Relation is set of tuples and each tuple is a representation of entity like a student or a professor.
When you compare relational model to EER model, relation would be entity type equivalent while tuple is an entity equivalent. Column name would be attribute type equivalent and cell would be attribute equivalent. Example of relation in a relational model would be as follows: Student (studentid, name, phone, address). The relation states the name and all the attribute types or so called column name in relational database. Example of a tuple would be: Student (111, James Jackson, 919-904-1212, 19th street).
Domain is what specifies the range of values for the column name or attribute type. For example, you can set the domain of male or female for gender column name/attribute type and it can only be one of the two and nothing else. You can even set an integer values from 1-10 to ensure no other domains can be submitted unless it’s 1-10.
Tuples are identified uniquely using keys in a relational model. There are multiple keys that one should be aware of when it comes to relational modeling. First, we have the superkey, which ensures that there are no tuples in any relation state with the same combination of values for the attribute types. It basically says says that every relation must have 1 key that makes individual tuples unique. For example, for student relation, we have Student (SSN, name, email, address). The superkey here would be SSN, because when you pull numerous tuples like Student (111-22-1111, bob, bob@gmail.com, bob street), that superkey will make that tuple be unique from other tuples. If you have multiple superkeys, one can be considered primary and other alternative. Primary keys are underlined and it will be used to uniquely identify tuples in the relation and also be used to establish connection to other relations. When the primary keys are used in other relations, it’s called a foreign key and they are always in italics. Foreign keys have two conditions, which are 1. attribute types must have the same domains in the relation and 2. The value of foreign key in a tuple occurs as value of primary key in the current state or is NULL. Ensure there isn’t a multi valued attribute type and if it’s a N:M relationship, new relation should be created.
So the four relational constraints are domain, key, entity integrity, and referential integrity constraints. Relational database management system will ensure it takes care of these constraints to ensure accuracy and consistency. Normalization has to be done to get rid of bad data. Normalization is a process of ensuring that there are no redundancies during inserting, deleting, and updating processes. There are three types of anomalies that needs to be normalized, an insertion anomaly, a deletion anomaly, and an update anomaly.
HansITAcademy 