Unit 2.2 ER Model.pptx.pdf

The Relational Database Entity Relational Modeling (ERM) CSC105 Modern Database Design Phase of Database Design Requirements Miniworld Collection and Analysis...

The Relational Database Entity Relational Modeling (ERM) CSC105 Modern Database Design Phase of Database Design Requirements Miniworld Collection and Analysis Conceptual Relational Design DBMS Relational Database Schema Phase of Database Design Requirements Miniworld Collection and Analysis Conceptual Relational Design DBMS Relational Database Schema We are here ER Data Model ❖ The entity-relationship (ER) data model has existed for over 35 years. It is well suited to data modelling for use with databases because it is fairly abstract and easy to discuss and explain. ❖ ER models are readily translated to relations. ❖ ER models, also called ER schema, are represented by ER diagrams. ❖ ER modelling is based on two concepts: ❖ Entities are defined as tables that hold specific information (data) ❖ Relationships, defined as the associations or interactions between entities ER Diagram Notation COMPANY Database ❖ We will use the COMPANY database to illustrate the concepts of the ER model. This database contains information about employees, departments and projects. Important points to note include: ❖ There are several departments in the company. Each department has a unique identification, a name, location of the office and a particular employee who manages the department. ❖ A department controls a number of projects, each of which has a unique name, a unique number and a budget. ❖ Each employee has a name, identification number, address, salary and birthdate. An employee is assigned to one department but can join in several projects. We need to record the start date of the employee in each project. We also need to know the direct supervisor of each employee. ❖ We want to keep track of the dependents for each employee. Each dependent has a name, birthdate and relationship with the employee. Company Database Entity, Entity Set and Entity Type ❖ An entity is an object in the real world with an independent existence that can be differentiated from other objects. An entity might be ❖ An object with physical existence (e.g., a department, an employee) ❖ An object with conceptual existence (e.g. a Project) Entity, Entity Set and Entity Type ❖ Entities can be classified based on their strength. An entity is considered weak if its tables are existence dependent. ❖ That is, it cannot exist without a relationship with another entity ❖ Its primary key is derived from the primary key of the parent entity ❖ The Spouse table, in the COMPANY database, is a weak entity because its primary key is dependent on the Employee table. Without a corresponding employee record, the spouse record would not exist. ❖ An entity is considered strong if it can exist apart from all of its related entities. ❖ Kernels are strong entities. ❖ A table without a foreign key or a table that contains a foreign key that can contain nulls is a strong entity Kinds of Entities Independent entities ❖ Independent entities, also referred to as kernels, are the backbone of the database. They are what other tables are based on. Kernels have the following characteristics: ❖ They are the building blocks of a database. ❖ The primary key is not a foreign key. ❖ They do not depend on another entity for their existence. ❖ If we refer back to our COMPANY database, examples of an independent entity include the Employee or Department. Dependent entities ❖ Dependent entities, also referred to as derived entities, depend on other tables for their meaning. These entities have the following characteristics: ❖ Dependent entities are used to connect two kernels together. ❖ They are said to be existence dependent on two or more tables. ❖ Many to many relationships become associative tables with at least two foreign keys. ❖ They may contain other attributes. ❖ The foreign key identifies each associated table. ❖ There are three options for the primary key: ❖ Use a composite of foreign keys of associated tables if unique ❖ Use a composite of foreign keys and a qualifying column ❖ Create a new simple primary key Characteristic entities ❖ Characteristic entities provide more information about another table. These entities have the following characteristics: ❖ They represent multivalued attributes. ❖ They describe other entities. ❖ They typically have a one to many relationship. ❖ The foreign key is used to further identify the characterized table. ❖ Options for primary key are as follows: ❖ Use a composite of foreign key plus a qualifying column ❖ Create a new simple primary key. In the COMPANY database, these might include: ❖ * Employee (EID, Name, Address, Age, Salary) – EID is the simple primary key. ❖ * EmployeePhone (EID, Phone) – EID is part of a composite primary key. Here, EID is also a foreign key. Attributes ❖ Each entity is described by a set of attributes (e.g., Employee = (Name, Address, Birthdate (Age), Salary). ❖ Each attribute has a name and is associated with an entity and a domain of legal values. However, the information about attribute domain is not presented on the ERD. Types of Attributes Simple attributes ❖ Simple attributes are those drawn from the atomic value domains; they are also called single-valued attributes. In the COMPANY database, an example of this would be: Name = {John} ; Age = {23} Composite attributes ❖ Composite attributes are those that consist of a hierarchy of attributes. Using our database example, and shown in Figure below, Name may be spilt into First_name, Middle_name and Last_name. Multivalued attributes ❖ Multivalued attributes are attributes that have a set of values for each entity. An example of a multivalued attribute from the COMPANY database, as seen in Figure below, the department has location as a multi-valued attribute. Derived attributes ❖ Derived attributes are attributes that contain values calculated from other attributes. An example of this can be seen in Figure below Age can be derived from the attribute Birthdate. In this situation, Birthdate is called a stored attribute, which is physically saved to the database. Key Attributes ❖ Entity types usually have one or more attributes whose values are distinct for each individual entity in the entity set ❖ Such an attribute is called a key attribute, and its values can be used to identify each entity uniquely Candidate key ❖ A candidate key is a simple or composite key that is unique and minimal. It is unique because no two rows in a table may have the same value at any time. It is minimal because every column is necessary in order to attain uniqueness. ❖ From our COMPANY database example, if the entity is Employee(EID, First Name, Last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID), possible candidate keys are: ❖ EID, SIN ❖ First Name and Last Name – assuming there is no one else in the company with the same name ❖ Last Name and DepartmentID – assuming two people with the same last name don’t work in the samedepartment Composite key ❖ A composite key is composed of two or more attributes, but it must be minimal. ❖ Using the example from the candidate key section, possible composite keys are: ❖ First Name and Last Name – assuming there is no one else in the company with the same name ❖ Last Name and Department ID – assuming two people with the same last name don’t work in the same department Primary key ❖ The primary key is a candidate key that is selected by the database designer to be used as an identifying mechanism for the whole entity set. It must uniquely identify tuples in a table and not be null. The primary key is indicated in the ER model by underlining the attribute. ❖ A candidate key is selected by the designer to uniquely identify tuples in a table. It must not be null. ❖ A key is chosen by the database designer to be used as an identifying mechanism for the whole entity set. ❖ This is referred to as the primary key. This key is indicated by underlining the attribute in the ER model. Primary key In the following example, EID is the primary key: ❖ Employee(EID, First Name, Last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID) Secondary key ❖ A secondary key is an attribute used strictly for retrieval purposes (can be composite), for example: Phone and Last Name. Alternate key ❖ Alternate keys are all candidate keys not chosen as the primary key Foreign key ❖ A foreign key (FK) is an attribute in a table that references the primary key in another table OR it can be null. Both foreign and primary keys must be of the same data type. ❖ In the COMPANY database example below, DepartmentID is the foreign key: ❖ Employee(EID, First Name, Last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID) Nulls A null is a special symbol, independent of data type, which means either unknown or inapplicable. It does not mean zero or blank. Features of null include: ○ No data entry ○ Not permitted in the primary key ○ Should be avoided in other attributes ○ Can represent An unknown attribute value A known, but missing, attribute value A “not applicable” condition ○ Can create problems when functions such as COUNT, AVERAGE and SUM are used ○ Can create logical problems when relational tables are linked Nulls Use the Salary table (Salary_tbl) in Figure below to follow an example of how null can be used. To begin, find all employees (emp#) in Sales (under the jobName column) whose salary plus commission are greater than 30,000. SELECT emp# FROM Salary_tbl WHERE jobName = Sales AND (commission + salary) > 30,000 –> E10 and E12 This result does not include E13 because of the null value in the commission column. Nulls ❖ To ensure that the row with the null value is included, we need to look at the individual fields. By adding commission and salary for employee E13, the result will be a null value. The solution is shown below. ❖ SELECT emp# FROM Salary_tbl ❖ WHERE jobName = Sales AND ❖ (commission > 30000 OR ❖ salary > 30000 OR ❖ (commission + salary) > 30,000 –>E10 and E12 and E13 Relationships ❖ Relationships are the glue that holds the tables together. They are used to connect related information between tables. ❖ Relationship strength is based on how the primary key of a related entity is defined. A weak, or non-identifying, relationship exists if the primary key of the related entity does not contain a primary key component of the parent entity. ❖ Company database examples include: ❖ Customer(CustID, CustName) ❖ Order(OrderID, CustID, Date) ❖ A strong, or identifying, relationship exists when the primary key of the related entity contains the primary key component of the parent entity. Examples include ❖ Course(CrsCode, DeptCode, Description) ❖ Class(CrsCode, Section, ClassTime…) Types of Relationships: Cardinality Ratio 1. One to many (1:M) relationship ❖ A one to many (1:M) relationship should be the norm in any relational database design and is found in all relational database environments. For example, one department has many employees. Figure below shows the relationship of one of these employees to the department. Relationship – One to Many (1:M or 1..*) ❖ An entity in A is associated with any number (zero or more) of entities in B. An entity in B, however, can be associated with at most one entity in A. 2. One to one (1:1) relationship A one to one (1:1) relationship is the relationship of one entity to only one other entity, and vice versa. It should be rare in any relational database design. In fact, it could indicate that two entities actually belong in the same table. An example from the COMPANY database is one employee is associated with one spouse, and one spouse is associated with one employee. Relationship – One to One (1:1) ❖ An entity in A is associated with at most one entity in B, and an entity in B is associated with at most one entity in A. 3. Many to many (M:N) relationships For a many to many relationship, consider the following points: ❖ It cannot be implemented as such in the relational model. ❖ It can be changed into two 1:M relationships. ❖ It can be implemented by breaking up to produce a set of 1:M relationships. ❖ It involves the implementation of a composite entity. ❖ Creates two or more 1:M relationships. ❖ The composite entity table must contain at least the primary keys of the original tables. ❖ The linking table contains multiple occurrences of the foreign key values. ❖ Additional attributes may be assigned as needed. ❖ It can avoid problems inherent in an M:N relationship by creating a composite entity or bridge entity. For example, an employee can work on many projects OR a project can have many employees working on it, depending on the business Relationship–Many to Many (N:M or *..*)-Example ❖ An entity in A is associated with any number (zero or more) of entities in B, and an entity in B is associated with any number (zero or more) of entities in A. ❖ shows another aspect of the M:N relationship where an employee has different start dates for different projects. Therefore, we need a JOIN table that contains the EID, Code and StartDate. Unary relationship ❖ A unary relationship, also called recursive, is one in which a relationship exists between occurrences of the same entity set. In this relationship, the primary and foreign keys are the same, but they represent two entities with different roles. See Figure below for an example. ❖ For some entities in a unary relationship, a separate column can be created that refers to the primary key of the same entity set. Ternary Relationships ❖ A ternary relationship is a relationship type that involves many to many relationships between three tables. Refer to Figure below for an example of mapping a ternary relationship type. Note n-ary means multiple tables in a relationship. (Remember, N = many.) ❖ For each n-ary (> 2) relationship, create a new relation to represent the relationship. ❖ The primary key of the new relation is a combination of the primary keys of the participating entities that hold the N (many) side. ❖ In most cases of an n-ary relationship, all the participating entities hold a many side. Participation Constraints : Total Participation ❖ Total Participation, also known as Mandatory Participation, indicates that every instance of one entity must be related to at least one instance of another entity in a relationship. ❖ It enforces a strong relationship between the entities, ensuring that no instance is left unassociated. ❖ Total Participation is often depicted by a double line connecting the relationship to the entity. Participation Constraints : Partial Participation ❖ Partial Participation, also known as Optional Participation, allows some instances of an entity not to participate in a relationship. ❖ This means that certain instances of an entity can be unrelated to any instances of another entity in the relationship. ❖ Partial Participation is depicted by a single line connecting the relationship to the entity. ERD on Bhutan Premiere League ❖ The BPL has many teams, ❖ Each team has a name, a city, a coach, a captain, and a set of players, ❖ Each player belongs to only one team, ❖ Each player has a name, a position (such as left-wing or goalie), a skill level, and a set of injury records, ❖ A team captain is also a player, ❖ A game is played between two teams (referred to as host_team and guest_team) and has a date (such as May 11th, 2017) and a score (such as 4 to 2) ❖ Construct a clean and concise ER diagram for the BPL database using the Chen notation as in draw.io. List your assumptions and clearly indicate the cardinality mappings as well as any role indicators in your ER diagram. THANK YOU