IM_Week3.pdf

Objectives: ❑ Explain the Entity Relationship Model ❑ Learn how relationships between entities are defined and refined ❑ Learn how to interpret the modeling symbols ❑ create an ERD ENTITY RELATIONSHIP MODEL Definition * Uses What is an Entity-Relationship Model? It is based on a...

Objectives: ❑ Explain the Entity Relationship Model ❑ Learn how relationships between entities are defined and refined ❑ Learn how to interpret the modeling symbols ❑ create an ERD ENTITY RELATIONSHIP MODEL Definition * Uses What is an Entity-Relationship Model? It is based on a perception of real world, which consists of a collection of basic objects called entities and relationships among these objects. It was developed to facilitate database design by allowing the specification of an enterprise scheme, which represents the overall logical structure of the database.  Uses of Entity Relationship Diagrams O Database design: ER diagrams are used to model and design relational databases, in terms of logic and business rules (in a logical data model) and in terms of the specific technology to be implemented (in a physical data model.) O Database troubleshooting: ER diagrams are used to analyze existing databases to find and resolve problems in logic or deployment.  Uses of Entity Relationship Diagrams O Business information systems: The diagrams are used to design or analyze relational databases used in business processes. O Database troubleshooting: ER diagrams are used to analyze existing databases to find and resolve problems in logic or deployment. O Business process re-engineering (BPR): ER diagrams help in analyzing databases used in business process re-engineering and in modeling a new database setup.  Uses of Entity Relationship Diagrams Education: Databases are today’s method of storing relational information for educational purposes and later retrieval, so ER Diagrams can be valuable in planning those data structures. Research: Since so much research focuses on structured data, ER diagrams can play a key role in setting up useful databases to analyze the data. The Components and Features of an ER Diagram.  ENTITIES Definition * Entity Sets * Entity Type vs. Entity Instance * Guidelines on naming entities  Entity Sets Entity - “thing” or object. - a data that pertains to, or describes some component of the enterprise - is an individual object (a person, place or thing), concept, or event that exists and is distinguishable from other objects. - rectangle (symbol’s used) Example : Person – EMPLOYEE, STUDENT PATIENT Place – STATE, REGION, COUNTRY Object - MACHINE, BUILDING AUTOMOBILE Event – SALE, REGISTRATION, RENEWAL Concept – ACCOUNT,COURSE, WORK CENTER Kinds of Entity ✓Entity Categories Employee ✓Entity Type ✓Entity Instance ✓Entity Keys Student  Entity Categories 1. Strong Entity Type - one that exists independently from other entity types. Always have a unique characteristic (identifier) – an attribute or combination of attributes that uniquely distinguish each occurrence of that identity Example: STUDENT, EMPLOYEE, AUTOMOBILE, COURSE Diagrammatic Representation of a Strong Entity Type Employee Student  Kinds of Entity Types 2. Weak Entity Type - existence depends on some other entity type. It has no meaning in the ER diagram without the entity on which it depends (such as DEPENDENT) The entity type on which the weak entity type depends is called the Identifying owner (or owner for short). Identifying relationship is the relationship between a weak entity type and its owner (such as ‘Has’). Diagrammatic Representation of a Weak Entity Type Children Organization membership  Weak relationship EMPLOYEE HAS Dependents Example of a weak entity:  ENTITIES O Entity Type Entity Instance - is a collection of – is a single entities that share occurrence of common properties an entity type. or characteristics  ENTITIES O Example O Example Entity type: Employee Entity Instance: Attributes: Instance of Employee: Emp_No 2008-0843 Name Alex Gonzaga Address Calamba, Laguna Year Hired 2012 Birthdate August 08, 1995  Entity keys O Refers to an attribute that uniquely defines an entity in an entity set. Entity keys can be super, candidate or primary. 1. Super key 2. Candidate key 3. Primary key 4. Foreign key  Entity keys 1. Super key: A set of attributes (one or more) that together define an entity in an entity set.  Entity keys 2. Candidate key: A minimal super key, meaning it has the least possible number of attributes to still be a super key. An entity set may have more than one candidate key. Example:  Entity keys 3. Primary key: A candidate key chosen by the database designer to uniquely identify the entity set.  Entity keys 4. Foreign key: Identifies the relationship between entities.  ATTRIBUTES Definition * Simple and Composite * Stored and Derived * Single and Multivalued * Primary Key Attribute Example  Attribute Domain An attribute domain is a set of values that may be assigned to a single-valued attribute. For example: – the attribute age can have a set of values from 19 to 65 – The attribute name can have a set of values that combine the letters of the alphabet – The attribute sex can have the value of either male or female  Attribute Types 1. simple attribute 2. composite attribute 3. single-valued attribute 4. multiple-valued attribute 5. derived attribute Name  E-R Notations for Attributes Name simple attribute Tel. Nos. multi-valued attribute Total Students derived attribute ID No. primary key  Attribute Types A simple attribute is composed of a single component with an independent existence. It cannot be further subdivided. For example: – salary – Student ID  Attribute Types A composite attribute is composed of multiple components, each with an independent existence. For example: – date : yyyy, mm, dd – Full name Last name First name Middle initial  Attribute Types A single-valued attribute holds a single value for a single entity. For example: the student entity has as single value for id number. – Student no : 9900001 – SSS no: ASB-6935-1234  Attribute Types A multiple-valued attribute holds multiple values for a particular entity. It may have a set of numbers with upper and lower limits. For example: telephone numbers – 534-1111 (home phone) – 984-4567 (office phone)  Attribute Types A derived attribute represents a value that is derivable from the value of a related attribute or a set of attributes, not necessarily in the same entity. For example: – age can be derived from DOB (date of birth) – no. of students can be derived from entity type student – rental deposit can be derived by multiplying the weekly rental by 4.  Key Primary key - a candidate key that has been selected as an identifier for an entity type. - unique number to identify the entity given  Example attribute Sex Name primary key Address Discount % Cust No entity type CUSTOMER RELATIONSHIP Definition * Cardinal Relationship  Relationship ❑ Relationships are associations between entities. ❑ Typically, a relationship is indicated by a verb connecting two or more entities. ❑ Employees are assigned to projects Employees Are Projects assigned  Relationship Type A relationship type is a set of meaningful associations among two or more entity types. For example: is_enrolled_in is a relationship between entities student and courses teaches is a relationship between entities faculty and course  Relationship Type A strong relationship (type) is represented in an E-R diagram as: is_enrolled_in  Relationship Type A weak relationship (type) is represented in an E-R diagram as: returns Relationship are classified as Cardinality O Defines the numerical attributes of the relationship between two entities or entity sets. ❑One-to-one (1:1) ❑One-to-many (1:N) or Many-to-one (N:1) ❑Many-to-many (M:N) Relationship Examples  Crow’s Foot Notation O Known as IE notation is that it uses graphical symbols to indicate the ‘many’ side of the relationship. O Entity: O Represented by a rectangle, with its name on the top. The name is singular (entity) rather than plural (entities). 52  Basic Cardinality Type O 1-to-1 relationship O 1-to-M relationship O M-to-N relationship Example: Cardinality Type Basic Cardinality Type Example of ER Diagram  Examples 1. Construct an E-R diagram for a university registrar’s office. The office maintains data about each class, including the instructor, the enrollment, and the time and place of class meetings. For each student-class pair, a grade is recorded. 2. Construct an E-R diagram for a car insurance company with a set of customers, each of whom owns a number of cars. Each car has a number of recorded accidents associated with it. Q&A Assignment: 1. Convert the examples given into table with cardinality 2. Give one example of ER Diagram that shows customer order a product. 3. Upload that in a doc format.