CSE 1012 Basic Computer Applications Entity-relationship diagram

1. CSE 1012Basic Computer ApplicationsEntity-relationship diagram Appavoo Paramasiven

2. Lesson Objectives • Learn about the components that make up an Entity Relationship Diagram (ERD) using the Chen’s notation • Learn how to draw an ERD • Learn how to transform an ER Model into a Relational Model

3. Content • ER Model • Entities • Relationships • Attributes • Classes of Attributes • Cardinality Mapping • From entities to relations

4. Introduction • What is a model? Representation of sth • Entity Relationship Model • Representation of relationships between data • Developed by Peter Chen (1976)

5. ER Diagramming - Modelling a DB • Data view of a system • Provides a high level, conceptual view of the DB structure • What data should be stored?? • What relationship exists between data items?? DB Student marks Student details File Link

6. The ER Model • Used for: • Conceptual database design • Made up: • Entities • Attributes • Relationships • Benefits: • Pictorial representation (non-tech people) • Easily map to relations

7. ER Model - Entity • Entity • Group of ‘objects’ • In real ‘world’ • With same properties Each rectangle contains the name of the entity it represents • An entity can be uniquely identified • Examples: Car, University, Department, Person EntityName Car

8. ER Model - Relationships Relationship Name • Relationships • Associations between entities • Corresponds to primary key-foreign key equivalencies in related tables Each diamond contains the name of the relationship it represents • Examples Own, register, employ Own

9. ER Model - Attributes • Attributes • “ describe the entity’s characteristics” • Attributes are represented by an Ellipse/Ovalconnected to the Entity with a line Each oval contains the name of the attribute it represents Entity: Person Attributes: NID, Name, Address, LicenseNumber, height AttributeName NID

10. ER Model EntitiesAttributesRelationships NID Name Address Age License No Owns Plate No Make Model Colour

11. ER Diagram Make Address License No Plate No Name Colour NID Model Person Own Car Since

12. Classes of Attributes • Simple Attribute • Composite Attribute • Derived Attributes • Single-valued • Multi-valued Attribute

13. Classes of Attributes • Simple Attribute • cannot be subdivided • Composite Attribute • can be further subdivided to yield additional attributes.

14. Classes of Attributes • Derived Attribute • is not physically stored within the database • is derived by using an algorithm

15. Classes of Attributes • Single-valued • can have only a single value. • Examples: • A person can have only one social security number. • A manufactured part can have only one serial number. • A single-valued attribute is not necessarily a simple attribute.

16. Classes of Attributes • Multivalued attributes • Can have many values • Examples: • A person may have several college degrees • A household may have several phones with different numbers

17. Cardinality Mapping • It is the specification of the number of occurrences of one object that can be related to the number of occurrence of another object. • One to one (1:1) • One to many (1:N) • Many to many (M:N)

18. Examples N Give birth 1 Mother Child N employ 1 School Teacher N guide M Lecturer Student Students can register for one course at a time. N register 1 Course Student

19. ACTIVITY – Identify the entities and their relationships for the following: • A department hires many employee. A employee is employed by one department. • A manager manages one department. A department is managed by one manager. • A person must have one and only one DNA pattern and that pattern must apply to one and only one person. • Each student takes many classes, and each class is taken by many students. • A team consists of many players. A player plays for only one team.

20. Steps for creating an ERD • Identify the entities • Identify the attributes • Identify the relationships • Identify cardinalities

21. ACTIVITY Lecturers can lecture many courses and give lecture to many students. Each student can register for one course and at the same time and in each course there will be many students. Draw the ERD of the above. You are free to decide the attributes of the entities.

22. Age name DOB Address Phone # Entities Lecturer Relationship Attributes lecture register StudentID Student Course name Course # DOB Title Age Address

23. Table Design (1) Make Address License No Plate No Name Colour NID Model Person Owns Car Since

24. Table Design (1) Person( NID, Name, Address, LicenseNo ) Car( PlateNo, Colour, Make, Model ) Owns( NID, PlateNo ) From Entities to relation!!! , DatePurchased )

25. Table Design and Normalisation • Each entity in the Logical Design model, along with the associated attributes, corresponds to one or more tables in the system. • A table is the grouping of attributes to identify a physical entity. • The table name should ‘describe’ the entity name. • The unique attributes may become the unique key. • Base your table design on a normalized logical data model.

26. Table Design and Normalisation • Normalized data model avoids redundancies and inconsistencies in your data. • Tables in a database should be at least in the 3rd Normal Form.

27. Draw the ERD of the above using the CHEN’s model. • The Ministry of Fisheries wants to keep records of the existing fishing companies. Given the following: A fisherman can work for one fishing company at a time and each fishing company can have many fishermen. Training is provided to at most five trainee fishermen in each company who are guided by existing fishermen of that company. Fishing companies also own fishing boats. The Ministry wants to store the identity number, social security number, name, surname, address, date of birth and number of years of experience for each fisherman. The same information will be kept for the trainees. As for that of the fishing company, its name and address, the contact person and the telephone number will be stored (there can be more than one telephone number). Every boat has a name, a manufacturer, a capacity, and a date of purchase and is assigned to a particular fisherman.

28. Recap • ER Model • Entities • Relationships • Attributes • Classes of Attributes • Cardinality Mapping • From entities to relations