This lecture will discuss the relevance and importance of anomalies. We begin with a review of an Excel spreadsheet about students and advisors. data sheets or spreadsheets often appear in rows and columns. In our example, the columns have specific information about students. The rows, sometimes referred to as a record or topple a specified set of columns. You will notice that our rows are comprised of collected student and advisor information.

There are eight rows of students with one particular student appearing twice Lisa, we will explain this later. Let us now select a student that is one row of information, say student 549 note that they have advisor for the student is sweetening. This particular advisor only appears one time on our spreadsheet. As is the case in academia, students can withdraw from the school or recourse. If this happens with student 549, we would have to remove his or her record from the spreadsheet. In doing so, not only have we lost information, in this case, David, which was our intention, we have also lost all information about sweetening, which was not our intention.

This type of error is referred to as a deletion anomaly. A second undesirable consequence that appears on a spreadsheet is an update anomaly. An example of this would be modifications of the advisor Nikolov in the first record for student 123 if Nick loft changed his office number, this change is only captured in john lumpers record. What about any other student who has nickel off as an advisor? It is easy to spot the other record where nickel off isn't advice in our example. But unfortunately, there are many other students who wouldn't have this advisor.

Are we going to catch all of them and say if we miss a few, the data would become inconsistent, and hence an update anomaly. A third error appears in our spreadsheet, and it's related to Lisa one. At least this case, we inserted a second occur Felisa in a later line since you requested a change to her married name. I realized that the earlier record could have been easily modified to capture the change. But imagine if you have hundreds if not thousands of records, would you have found Lisa's record easily. Also, imagine another scenario where students change their address frequent Or select a different advisor.

The undesirable result is termed an additional anomaly. So how are we to avoid these inconsistencies by separating the data into two different entities, a student and an advisor entity. This is similar to the lesson taught of paragraphs. Remember, a paragraph is a theme a single idea. Likewise, students advisors are two different themes are in database terminology we see entities, so separate them, but connect them. Let me explain.

On our initial spreadsheet, each row connects a student with an advisor. However, in our existing diagram, there is no connection. To do this conceptually, we simply draw a line between the two entities and build it in some rules. The completed diagram is a blueprint of the database this entity relation is universally accepted. Let us highlight the major points. On your screen is a completed diagram displaying how we can connect the two entities conceptually.

First, we have the two entities displayed students and advisors identified as one and two in the diagram, we see that there is a relationship between them. That is the straight line that joins the two entities together. But what are the crow's feet at the end of each line? They are what we call cardinality. Nothing to do with a baseball team or religion, but a database term for indicating the minimum and maximum binary relationship between two entities. There are six possibilities as shown in the screen.

One, many one and only one zero or one, one on many, zero or many notes four and five we just follows. For a student there is either zero or one advisor. For an advisor, there are zero minimum or many maximum students. This blueprint is called an entity relationship diagram. Not to worry if it isn't clear, we will have lots of practice in the next lectures. We have covered a lot in this lecture.

Let's take a quick review. We've discussed the problems with data lists and the inherent anomalies that occur using a relational model how we can eliminate and resolve anomalies and a proposed blueprint on how we can proceed. In the next section, we will go deeper into the relational model. Learn how to transform it conceptual blueprint into a logical relational design and practice the normalization process.