1 / 28

CSE 1012 Basic Computer Applications Entity-relationship diagram

CSE 1012 Basic Computer Applications Entity-relationship diagram. Appavoo Paramasiven. 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.

Download Presentation

CSE 1012 Basic Computer Applications Entity-relationship diagram

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related