Data Modelling

1. Data Modelling Which data to include in the database

2. Aim of Data Modelling • Form a model of the enterprise so that the data are accurate and useful. • Yet another form of abstraction. • First and most important step in database design. • Requires a good understanding of enterprise being modeled. • Different methods of data modeling, but the most widely used is the Entity-Relationship (E-R) model.

3. Entity-Relationship Model • Primarily used to specify an external view of the database. • Recall that the conceptual schema can be created out of the various external views. • Model the world in terms of • entities • relationships between entities • attributes of entities and relationships.

4. Elements in the E-R Model I • Entity: • A distinguishable thing, person or event • Entity set • A set of entities of the same type • Relationship • Representation of the fact that certain entities are related to each other in a specific way. • Relationship set • Set of relationships of a given type.

5. Elements in the E-R Model II • Attributes: • Properties of entities or relationships • Attribute values are drawn from a given domain or value set • Attributes may have a set of values (multi-valued or set-valued attributes). • Key: • An attribute or set of attributes that uniquely identifies each entity in an entity set.

6. E-R Diagrams • E-R models can be visualized in E-R diagrams. • There are different conventions for drawing E-R diagrams. The following is widely used: • Rectangles for entity sets • Ellipses for attributes • Diamonds for relationship sets • Lines to link • Each component is labelled with its corresponding name.

7. Example of an E-R Diagram I • UWI offers many courses. • Each course has a unique course number and a name. • Students can enroll in many courses and a course can be taken by many students. • A student has a name and a unique student number. • Student obtain grades on courses. • A course is taught by a single instructor.

8. Example of an E-R Diagram II c num Course c name Enrol grade Teaches Student Instructor s num i num s_name i name

9. Constraints I • In addition to the entity and relationship sets and their attributes, during data modelling, one also identifies constraints. • Examples: • A course is taught by a single instructor. • No student can enroll for more than 4 courses.

10. Constraints II • Usually, constraints are not captured in the E-R diagram and need to be written down. • Two exceptions: • Mapping cardinalities • Concerns the number of entities involved in a relationship • Existence dependencies • Concerns the fact that sometimes the existence of an entity depends on the existence of another entity.

11. One-to-One Relationships • Each entity in E1 is related to at most one entity in E2 and vice versa. • Example: • A department has only one head and no person can be head of more than one department. Head Of Employee Department

12. One-to-Many Relationships • An entity in E2 is related to at most one entity in E1, but an entity in E1 can be related to many entities in E2. • Example: • A student has one professor as their supervisor but a professor may supervise many students. Super vises Professor Student

13. Many-to-many Relationships • An entity can be related to many entities in the other set, and vice versa. • Example: • A student can be enrolled in many courses, a course can have many students enrolled in it. Enroll Course Student grade

14. Head Of Employee Department Different Conventions • There are different conventions for mapping cardinalities. For example, instead of a 1 on the link, one may use an arrow pointing into the entity.

15. Existence Dependencies • An entity x is existence dependent on an entity y if the existence of x is dependent on the existence of y. • Y is called the dominant entity; x the subordinate entity. • Example: In a personnel database, a dependent (e.g., child) is existence dependent on the employee. If the employee is deleted, so is the dependent.

16. Representation of Subordinate Entities • Subordinate entities are usually represented in a double box Employee Dependent

17. Some Complicationsto the E-R Diagram • An entity set may stand in some relationship to itself. • The links are then labelled with the role that the entity plays. Person Parent Child Child-of

18. Multiple Relationships Between Entity Sets Enroll Student Course Tutor for

19. Ternary Relationships • A customer may has an account at a particular branch. Branch CAB Account Customer

20. Generalizations • Inheritance hierarchies Employee IS A Support Staff Academic Staff

21. Specialization • Sometimes, you want to make a lower level entity from a higher level entity. However, not every high-level entity must also be a lower-level entity. Student IS A Grad Student

22. Aggregation • Sometimes, you want to treat a relationship as an entity in its own right, e.g., to allow modelling of relationships between relationships. • Example: • A customer is assigned a loan manager for every loan that they get. • Note that the relationship from the login is not with the customer alone, or with the loan.

23. Aggregation in an E-R Model borrows Loan Customer Loan-Manager Employee

24. Problems in Data Modelling • Should a real-world concept be modelled as an entity set or a relationship set? • Should a real-world concept be modelled as an attribute or an entity set? • Is the use of a generalization or aggregation appropriate?