Entity-Relationship Model

1. Entity-Relationship Model ER-Diagrams Weak entity sets

2. Outline: Conceptual modeling using ER-model • Data modeling • Entity-relationship model • - Entity types • - strong entities (regular) • - weak entities • - Relationships among entities • - Attributes - attribute classification • - Constraints • - degree • - cardinality constraints • - participation constraints • - …

3. Database design • Database design is an important and challenging task. • Good design requires a thorough understanding the data associated with an enterprise and how they are used, as well as a solid understanding of the functionalities of your DBMS (Oracle in our case)

4. Is relational model suitable for database designers? • While simple data models consisting of few tables can be created "manually“, large applications need a more systematic approach. • While the data model of the DBMS hides many details, it is nonetheless closer to how the DBMS stores data than to how a user thinks about the underlying enterprise.

5. Conceptual Semantic data models are needed to bridge the gap and assist the database design process. • Being syntactically correct does not mean being semantically correct.

6. Conceptual modeling • A high-level conceptual model provides concepts for describing the database structure that are close to the way users perceive data. • A conceptual schema desribes the structure of the database by hiding the details of physical storage structures. • Conceptual modeling is the process of creating a conceptual schema using a high-level conceptual model.

7. The main phases of database application Design Mini World Functional Analysis Requirements Collection and Analysis Functional Analysis Database Requirements High level transaction specification Conceptual Design Conceptual Schema (high level) DBMS-independent Logical Design (Data Model Mapping) DBMS-specific Logical Schema (in the data model of a sepcific DBMS) APPLICATION PROGRAM DESIGN Physical Design TRANSACTION IMPLEMENTATION Internal Schema Application programs

8. Example The company database keeps track of a company’s employees, departments, and projects: Requirements: concerning the department: 1. The company is organized into departments 2. Each department has a unique name, a unique number, and a specific employee is its’ manager 3. we track the start date for the manager function 4. a department may be in several locations 5. a department controls a number of projects concerning the project: 6. a project has a unique name, a unique number, and is in a single location

9. example continued • concerning the employee: • 7. each employee has a name, social insurance number, address, salary, sex, and birth date • 8. an employee is assigned to one department but may work on several projects which are not necessarily controlled by the same department • 9. we track the number of hours per week that an employee works on each project • 10. we keep track of the direct supervisor of each employee • 11. we track the dependents of each employee (for insurance purposes) • concerning the dependent: • 12. we record each dependent’s first name, sex, birth date, and relationship to the employee

10. ER model concepts • The Entity-Relationship (ER) model is a popular high-level conceptual model which helps describe a real application scenario in a pictorial format. • It employees the notions of entities, attributes and relationships, as well as constraints. • This model and its variations are frequently used for the conceptual design of database applications, and many database design tools employ its concepts. • It was introduced by P. Chen (bit.csc.lsu.edu/~chen) in "The Entity-Relationship model - Toward a Unified Views of Data", TODS 1:1, March 1976.

11. Purpose of E/R Model • The E/R model allows us to sketch the design of a database informally. • Designs are pictures called entity-relationship diagrams. • Fairly mechanical ways to convert E/R diagrams to real implementations like relational databases exist.

12. Entity (entities) • Entity = “thing” or object. • An entity is a specific object or thing in the mini-world (for instance, a company) with an independent existence. • Example: the EMPLOYEE entity David, the research DEPARTMENT, the product X PROJECT

13. Entity Set & Entity type • Entity type defines the data type of a collection of entities having the same attributes (that are of the same type). • Similar to a class in object-oriented languages. • Entity set = collection of similar entities of a particular entity type in the database at any point in time. • The same name is used for both the entity type and its entity set. They are interchangeably used when the context is clear.

14. Attributes • Attribute = property of an entity set. • Generally, all entities in a set have the same properties. • Attributes are simple values, e.g. integers or character strings. • Attributes are properties used to describe an entity. Example: an EMPLOYEE entity may have a Name, SSN, Address, Sex, BirthDate. • A specific entity has a value for each of its attributes. • Example: a specific EMPLOYEE entity may have • name='Kelly David', • SSN='12321312', • Address='733 Spruce str. Oneonta, NY', • Sex='M', • Birthdate='08-Jan-1980'.

15. Types of attributes • Simple (Atomic) Vs. Composite (compound) • Single-valued vs. Multi-valued • Stored vs. Derived Attributes • Nesting composite and Multi-valued attributes.

16. Atomic vs. Composite • Atomic attri: Each entity has a single atomic value for the attribute. These attributes are not divisible. • Example: SSN or Sex • Composite attribute: The attribute may be composed of several components. • Example: • Address (Apt#, House#, Street, City, State, ZipCode, Country) • Name (FirstName, MiddleName, LastName) • The value of a composite attributes is the concatenation of the values of its component simple attributes.

17. Composition may form a hierarchy where some components are themselves composite.

18. Single-valued vs. Multivalued • Single-valued attri: It has a single value for a particular entity. • Example: Age. • Mutli-valued: An entity may have multiple values for that attribute. • Example: Color of a Car or PreviousDegrees of a STUDENT. • Set Notation: {color} or {previous degrees}

19. Stored vs. Derived Attributes • Derived attribute: An attribute whose value can be derived from the value of another attribute (the latter is called stored attribute). • Example: Age(Derived) and BirthDate(Stored) attributes. • Remark: The values of some attributes can be derived from related entities. • Example: The attribute NumberofEmployees of a DEPARTMENT entity can be derived by counting the EMPLOYEE entities of this department.

20. Nesting composite and Multi-valued attributes • Depending on how you model attributes, won’t be discussed here.

21. Null values • In some cases a particular entity may not have an applicable value for an attribute. In such situations a special value NULL is used. • Example, for the ApartmentNumber attribute of an address of a student

22. When NULL values will be used. • There are different meanings and reasons for null values. • NULL Not applicable unknown missing Not known

23. E/R Diagrams • In an entity-relationship diagram, each entity set is represented by a rectangle. • Each attribute of an entity set is represented by an oval, with a line to the rectangle representing its entity set.

24. name salary Employee Example • Entity set (Entity type) employees has two attributes, name and salary (manufacturer). • Each specific employee entity has values for these two attributes (name an example).

25. The entities: employee dependent department project

26. The entities: fname lname minit dependent salary address name sex relationship ssn name sex birthdate employee bdate name location number location project department name number location

27. Relationship types • A relationship type among n entity types E1,…,En defines a set of associations ( a relationship set) among entities from these types. • Example: For example, the WORKS_ON relationship type in which EMPLOYEEs and PROJECTs participate, or the MANAGES relationship type in which EMPLOYEEs and DEPARTMENTs participate.

28. Relationship Types • A relationship set is a set of relationship instances where each relationship instance associates entities, one from each participating entity type.

29. Relationship Set • The current “value” of an entity set is the set of entities that belong to it. • Example: the set of all employees in our database. • The “value” of a relationship is a set of lists of currently related entities, one from each of the related entity sets.

30. Example • For the relationship works_on, we might have a relationship set like: Employee Project project1 112322

31. Relationships • A relationship connects two or more entity sets. • It is represented by a diamond, with lines to each of the entity sets involved.

32. With relationships: 1 works for N department 1 1 controls employee manages 1 N supervisee supervisor N N M supervision 1 works on 1 project dependents of N partial constraint dependent total constraint

33. location location With attributes, etc: name number 1 fname lname works for minit N department salary address name sex 1 number of employees startdate 1 ssn controls manages employee 1 bdate N supervisor hours N degree supervisee M supervision works on 1 project dependents of name number location N dependent relationship name sex birthdate

34. ERD symbols Entity Weak entity Relationship Identifying relationship name Attribute Key attribute name

35. ERD symbols Composite attribute name Derived attribute name Mutlti-valued attribute name Partial participation Total participation Cardinality 1 : N

36. There are several ways of classifying relationships, according to the degree, cardinality, participation constraint (mandatory or optional), whether recursion is involved, and whether or not relationship is identifying weak entities.

37. Relationship types • The degree of a relationship type is the number of participating entity types. • When we speak of a student enrolling in a course, we are discussing a relationship, enroll in, where two entity sets (STUDENT and COURSE) are involved; the relationship is of degree 2 because each instance of enroll in will always involve one student entity and one course entity. • Both MANAGES and WORKS_ON are binary relationship types.

38. Multiway Relationships • Sometimes, we need a relationship that connects more than two entity sets. • Suppose that suppliers will provide parts for projects. • binary relationships do not allow us to make this distinction. • But a 3-way (ternary) relationship would.

39. Example name addr name id supplier parts license supply projects name addr

40. Cardinality ratio • The cardinality ratio for a binary relationship specifies the number of relationship instances an entity can participate in. • Some times cardinality ratio is also called “Multiplicity” or “cardinality” • Possible cardinality ratios for binary relationships are 1:1, 1:N, M:N.

41. One-One Relationships • In a one-one relationship, each entity of either entity set is related to at most one entity of the other set. • Example: Relationship couple

42. Many-One Relationships • Some binary relationships are many -one from one entity set to another. • Each entity of the first set is connected to at most one entity of the second set. • But an entity of the second set can be connected to zero, one, or many entities of the first set.

43. Example • Let us assume manages, from department to employee is many-one. • A department has at most one employee as its head. • But an employee can head any number of departments, including zero.

44. Many-Many Relationships • Think of a relationship between two entity sets, such as works_on between employees and projects. • In a many-many relationship, an entity of either set can be connected to many entities of the other set. • E.g., an employee works on many projects; a project needs many employees.

45. In Pictures many-many many-one one-one M:N N:1 1:1

46. m Enroll-in n Students Course An example of an Entity Relationship Model: Entities are indicated using rectangular shapes, and relationships between entities are shown with a diamond shape on a line connecting the entities. Entity sets are shown with a rectangular shape. Relationship sets are shown with a diamond shape. The "m" indicates that one course may have many students enrolled in it. The "n" indicates that one student may have enrolled in many courses.

47. Suppose we know of four student entities and two courses entities. For example, four students named John, Amelia, Lee, and April, and two courses “Intro to Art” and “Intro to History”. Information about these is listed below.

48. Students Name Id# Phone John 184 283-4984 Amelia 337 838-3737 Lee 876 933-2211 April 901 644-3838 Course Name Course# Dept Intro to Art 661 Art Intro to History 765 History

49. John • Amelia • Lee • April We can represent these two sets of entities using set diagrams. Intro to Art Intro to History set of students set of courses Relationships describe how entities relate to one another

50. Suppose we have the two courses and four students listed previously. Suppose also that - John and Amelia are enrolled in Intro to Art - John and Lee are enrolled in Intro to History - April is not enrolled in any course. Below, we depict a relationship set and show the four instances of an enroll in relationship.