Entity Relationship Diagrams
Understand entities, attributes, relationships, and database design stages. Learn about conceptual and logical designs, ER diagrams, and conversion to relational schemas. Explore ER models, schema, and basic concepts for effective database design.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
Presentation Transcript
Entity Relationship Diagrams Taslima Ferdaus Shuva Sr. Lecturer, CSE
Entity An entity is a business object that represents a group, or category of data. Do we know a similar concept? 1) Stephens, R.K. and Plew. R.R., 2001. Database Design. SAMS, Indianapolis , IN.
Attribute An attribute is a sub-group of information within an entity. Do we know a similar concept? 1) Stephens, R.K. and Plew. R.R., 2001. Database Design. SAMS, Indianapolis , IN.
Entity Relationship Models Ratio from entity to entity: Cardinality Many-to-Many Relationships |m..m/n n/*..*/<-| .|-> One-to-Many Relationships |1..m/1 n/1..*/|..|-> One-to-One Relationships |1..1
Overview of Database Design Requirements Analysis: Understand what data will be stored in the database, and the operations it will be subject to. Conceptual Design: (ER Model is used at this stage.) What are the entities and relationships in the enterprise? What information about these entities and relationships should we store in the database? What are the integrity constraints or business rules that hold? A database `schema in the ER Model can be represented pictorially (ER diagrams). Can map an ER diagram into a relational schema. Logical Design: Convert the conceptual database design into the data model underlying the DBMS chosen for the application.
ERD Schema Database!!!!!
name ER Model Basics ssn DoB Employees Entity: Real-world object distinguishable from other objects. An entity is described using a set of attributes. Entity Set: A collection of entities of the same kind. E.g., all employees. All entities in an entity set have the same set of attributes. Each entity set has a key(a set of attributes uniquely identifying an entity). Each attribute has a domain.
Entity An entity can be a person, place, event, or object that is relevant to a given system. For example, a school system may include students, teachers, major courses, subjects, fees, and other items. Entities are represented in ER diagrams by a rectangle and named using singular nouns. Weak Entity A weak entity is an entity that depends on the existence of another entity. In more technical terms it can defined as an entity that cannot be identified by its own attributes
Child De pe nd en cy Employee
Attribute An attribute is a property, trait, or characteristic of an entity, relationship, or another attribute. For example, the attribute Inventory Item Name is an attribute of the entity Inventory Item. An entity can have as many attributes as necessary. Meanwhile, attributes can also have their own specific attributes. For example, the attribute customer address can have the attributes number, street, city, and state. These are called composite attributes. Note that some top level ER diagrams do not show attributes for the sake of simplicity. In those that do, however, attributes are represented by oval shapes.
Classification Multivalued Attribute If an attribute can have more than one value it is called an multivalued attribute. It is important to note that this is different to an attribute having its own attributes. For example a teacher entity can have multiple subject values. E.g. : Skill Example of a multivalued attribute Derived Attribute An attribute based on another attribute. This is found rarely in ER diagrams. For example for a circle the area can be derived from the radius. E.g: Age Derived Attribute in ER diagrams
DoB ID Age Student Address Skill Street House Zip
Classification Simple Attribute: Combined of only one attribute. E.g. ID Complex Attribute: Combined of more than one attribute Example: Address
WEAK Entity & WEAK Relationship For converting ER to Relational Schema, Weak entity will accept the primary key of strong entity. We need not to convert the weak relationship into table.
name (Contd.) ssn lot Employees since name dname super- visor ssn budget DoB did subor- dinate Reports_To Works_In Employees Departments Relationship: Association among two or more entities. E.g., Peter works in Pharmacy department. Schema Employee(ssn, name, DoB) Department(did, dname, budget) Works_in(since, ssn, did )
Key Constraints (Cardinality) since name dname . ssn lot did budget Employees Manages Departments 1-to-1 1-to Many Many-to-1 Many-to-Many Constraints are IMPORTANT because they must be ENFORCED when IMPLEMENTING the database
Consider Works_In (in previous slide): An employee can work in many departments; a dept can have many employees. In contrast, each dept has at most one manager, according to the key constraint on Manages
Specialization & Generalization Generalization As mentioned above, the process of generalizing entities, where the generalized entities contain the properties of all the generalized entities, is called generalization. In generalization, a number of entities are brought together into one generalized entity based on their similar characteristics. For example, pigeon, house sparrow, crow and dove can all be generalized as Birds.
Specialization Specialization is the opposite of generalization. In specialization, a group of entities is divided into sub-groups based on their characteristics. Take a group Person for example. A person has name, date of birth, gender, etc. These properties are common in all persons, human beings. But in a company, persons can be identified as employee, employer, customer, or vendor, based on what role they play in the company.
