Transcript Entity
Data Modeling Yong Choi School of Business CSUB Part # 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships between entities are defined and refined, and how such relationships are incorporated into the database design process Learn how ERD components affect database design and implementation Learn how to interpret the modeling symbols 2 Why Data Modeling? Part # 2 Data Model by CASE tool = Actual Database Represent “reality” of the actual database Blue print: documentation Effective Communication Tool User involvement Identify the business rules to be stored in the database Independence from a particular DBMS Example of data model by CASE tool on the website 3 Part # 2 Conceptual data modeling The conceptual data modeling revolves around discovering and analyzing organizational and users data requirements. What data is important What data should be maintained The major activity of this phase is identifying entities, attributes, and their relationships to construct model using the Entity Relationship Diagram methodology. 4 Part # 2 Entity Relationship diagram (ERD) Data modeling methodology Developed by Peter Chen (1976). See his original ERD article on the class website ERD is commonly used to: Translate different views of data among managers, users, and programmers to fit into a common framework. Define data processing and constraint requirements to help us meet the different views. Help implement the database. 5 Part # 2 Basic ERD Elements Entity : a collection of people, places, objects, events, concepts of interest (a table) Entity instance – a member of the Entity : a person, a place, an object … (a row in a table) Attribute - property or characteristic of interest of an entity (a field in a table) Relationship – association between entities (corresponds to primary key-foreign key equivalencies in related tables) 6 ERD using Chen’ Notation (first - original) Part # 2 Part # 2 Chen’s Notation Entities Attributes rectangle containing the entity’s name. oval containing the attribute’s name. Relationships diamond containing the relationship’s name. 8 Part # 2 Steps for creating an ERD 1. 2. 3. Identify entities Identify attributes Identify relationships 9 Part # 2 Entity “A fundamental THING of relevance to the enterprise about which data may be kept” What should be an Entity: both tangible & intangible An object that will have many instances in the database An object that will be composed of multiple attributes An object that we are trying to model What should NOT be an Entity: A user of the database system An output of the database system (e.g. a report) 10 Part # 2 ERD using IE Notation (most popular) 11 Part # 2 Entity Instance Entity instance: a single occurrence of an entity. 6 instances Entity: student instance Student ID Last Name First Name 2144 Arnold Betty 3122 Taylor John 3843 Simmons Lisa 9844 Macy Bill 2837 Leath Heather 2293 Wrench Tim 12 Part # 2 Attributes “describe property or characteristic of an entity ” Entity: Employee Attributes: Employee-Name Address (composite) Phone Extension Date-Of-Hire Job-Skill-Code Salary 13 Part # 2 Classes of attributes Simple attribute Composite attribute Derived attributes Single-valued attribute Multi-valued attribute 14 Part # 2 Simple/Composite attribute A simple attribute cannot be subdivided. Examples: Age, Gender, and Marital status A composite attribute can be further subdivided to yield additional attributes. Examples: ADDRESS -- Street, City, State, Zip PHONE NUMBER -- Area code, Exchange number 15 Part # 2 Derived attribute is not physically stored within the database instead, it is derived by using an algorithm. Example: AGE can be derived from the date of birth and the current date. MS Access: int(Date() – Emp_Dob)/365) 16 Part # 2 (unique) Identifier “attributes that uniquely identify entity instances” Uniquely identify every instance of the entity One or more of the entity’s attributes Composite identifiers are identifiers that consist of two or more attributes Identifiers are represented by underlying the name of the attribute(s) Employee (employee_ID), student (student_ID) 17 Part # 2 Type of Relationships One – to – One (1:1) One – to – Many (1:M) Each instance in the relationship will have exactly one related member on the other side A instance on one side of the relationship can have many related members on the other side, but a member on the other side will have a maximum of one related instance Many – to – Many (M:N) Instances on both sides of the relationship can have many related instances on the other side Part # 2 1:1 relationship in Set notation DEPARTMT EMPLOYEE Part # 2 1:M relationship in Set notation DEPARTMT EMPLOYEE Part # 2 M:N relationship in Set notation WAREHOUSE PRODUCT Part # 2 M:N relationship Each student takes many classes, and a class must be taken by many students. IS_TAKEN_BY STUDENT CLASS TAKE ** Many-to-many relationships cannot be used in the data model because they cannot be represented by the relational model (see the next slide for the reason) ** Part # 2 Example of M:N Many-to-many relationships is a second sign of complex data. When x relates to many y's and y relates to many x's, it is a many-to-many relationship. In our example schema, a color swatch can relate to many types of sweaters and a type of sweater can have many color swatches. Example M:N Relationship Part # 2 Table to represent Entity 3 to 3 30 to 30 300 to 300 3000 to 3000 30,000 to 30,000 300, 000 to 300, 000 Converting M:N Relationship to Two 1:M Relationships Part # 2 Bridge Entity Part # 2 Bridge Entity MUST have a composite (unique) identifier STU_NUM (from STUDENT entity) and CLASS_CODE (from CLASS entity)