Welcome back. Today we're going to continue talking about the given introduction to database systems and we're going to talk about the entity relationship model. So, usually when you design a database, you go through different phases. The first phase is that you collect the requirements from the customer. So, the customer is the person that owns a database and wants to design a database for their application. The application can be a bank, can be a flight reservation system, can be a social network, can be something like Amazon online retail store, any of these applications, and the customer is the person that owns the application and wants to design the database. So, the first step is to collect these requirements from the customer to decide how to technically design the database. After that, there is a conceptual database design. The conceptual database design means is that the database designer will sit down, and on paper decide how the data will look like, how the database will look like. Usually, there are so many ways to do that but the most popular way is to use a model called the entity relationship model. Using the entity relationship model, you build something called the entity relationship diagram and this diagram represents the data, the design for the database. The third phase is that after you build the ER diagram, you go ahead and convert this ER diagram into a relational database scheme, which we'll see how it's going to look like later. So, first, let's talk about ER diagrams to understand what it means. So, the ER model, it's called entity relationship and as the name says, it consists of a set of entities and a set of relationships between these entities. Entities represents objects in the real world, again, bank accounts, users, any of these kind of things and their relationship is a relationship between multiple entities. So, let's take an example. So, an example, let's assume we have an employee database and you want to model the employee database. So, you have one entity set called employees, as explained here, and in the entity set consists of a set of entities. These entities, they have attributes and the attributes can be simply the name of the employee, can be the parking lot number for this employee, and can be the SSN. Each entity in the entity set has a key, and the key here means it uniquely identifies the entity. So, in that case, the employee entity has the SSN as the key social security number, it uniquely identifies the employee. There are other attributes in the entity as well and the attributes as we said, name and lot number. Each attribute has a type. So, name for example, is a string and it has a domain of values as well. So, what kind of values that this attribute can take. So, this is simply, and again, this is just one entity but the database that you design in the ER diagram, you can have multiple entities. So, another entity can be the department, for example. So, we've looked at the employees, another entity can be the departments. The departments also can have attributes like the department ID, and department name, and the budget for that department. You can have a relationship between two entities, the department and the employees, and the relationship is basically named works_in, and that means that an employee entity can work in a department. Again, from an application perspective it makes sense. Again, you're building an employees database or a company database and the rule about relationships, again, you represent the relationship using the diamond that way, and entities again it's using a rectangle, and the attributes for either d entities or the relationships are represented using an oval symbol. The works_in here, the works_in relationship means that, again, an employee entity works, an employee can work in a department. It has an attribute and the attribute's called since, and this is basically it says, it's a date. It says that the employee works in this department since whatever, 2014 or something like this. One rule about relationships is that a relationship is uniquely identified by its participant entities. So, which means that the works_in relationship is uniquely identified by the combination of an employee and a department. That's a rule in the ER diagram design. So, when defining a relationship, we've seen that you can define a key for each entity, like here, the department ID for department and SSN for employees. There are also key constraints for relationships. Let's take another relationship called manages. It's also a relationship between the employee and the departments. If there is a relationship between an employee and the department manages, this means that an employee can manage the departments. However, you sometimes want to add some constraints on that relationship, key constraints. The constraints can be like an employee can only manage one department and the department can be managed by one employee, or it depends on the application. Again, the company might allow the employee to manage multiple departments but it will not allow the department to be managed by more than one employee. So, these are constraints that are defined by the application and the database designer needs to take into account. Here to do that for example, you can see that this arrow that's coming from the department to the manages, this arrow means that the department can only be managed by at most one employee. That's what it means. So, this is what we call a one-to-many relationship. So, there are different relationships between, if you have a relationship between two entities, there's a one-to-one relationship. One-to-one relationship means that each of the entities participating in the relationship can participate once in the relationship. A one-to-many relationship means that on one side it has to be one, participation one and the other side can have many entities participating in the relationship. Many-to-many is the typical, is a default which is look at the employee you can have, the employee can manage many departments and the department can be managed by many employees. The one-to-many here is that the employee can manage many departments but the department can be managed by one employee, this is why it's called one-to-many. A one-to-one will happen if the customer that wants the application wants it to be that the employee manages one department and a department is managed by one employee. That's a one-to-one relationship. So, there's also something in the ER diagram called a participation constraint. The participation constraint means that it's represented by a bold line, like this here, and it means that like the department in that case, it must participate at least once in the relationship. So, which means that every department entity in that entity set must appear in an instance of the manages relationship. There is no such a thing as a null participation, it must appear at least once in an instance in the manages relationship. If the line is bold that way, we call this a total participation as opposed to a partial participation. Partial is the default. If you want to make a total participation, the design has to have a thick line coming from the entity of the relationship that way. Then again, that means that this entity must appear in an instance of the manages relationship, it must. So, this is for the manages, what about works_in, for example? Let's look at another example. In the works_in example, you can say that the employee must appear in a works_in relationship because you cannot have an employee that doesn't work in a department. So, this is why this is a bold line here. You can say also, I cannot have a department without having an employee working in it. So, it's not called a department anymore. So, again, this depends on what the user defines for their application, if they want that to be the database design or not. There's also another definition ER diagram called weak entities, and a weak entity is basically a traditional entity. We looked at entities like departments and employees, but you can have other entities that the existence of these entities depend on other entities, stronger entities. If that's the case, we call this a weak entity and more technically speaking, the weak entity cannot be identified alone, it needs another stronger entity to identify it. An example of that is, you have an employee, that's the same entity we looked at before with all the attributes of the employee, and you have dependents of this employee, and they also need the data for these dependents to be stored in the database. However, the dependent in that case is called the weak entity, and the reason behind that is that it relies on the existence of a stronger entity called employee that's the main entity in the database. So, it relies on the existence and it's identified by the employee. So, if I am an employee in that company, I have an entity for me in that company but my dependents, they also have an entity but they're weak entities because the records for my family or my dependents depends on the existence of my records in the database. This is why we call weak entities. Again, there are rules for weak entities. For example, a weak entity must participate in a one-to-many relationship with a stronger entity. We already discussed one-to-many, and we represent this by the arrow in the line from the weak entity to the relationship. Not only this, a weak entity must have total participation in the relationship with the stronger entity. Again, the relationship here between the dependent and an employee is the policy, is for example, medical insurance policy or something like that. That's the relationship. So, rule number one is that it must participate in a one-to-many relationship and rule number two, it must have total participation into identifying relationship set.
