Data Modeling

1. Data Modeling Yong Choi School of Business CSUB

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

3. Why Data Modeling?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

4. 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.

5. 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.

6. 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)

7. ERD using Chen’ Notation (first - original)

8. Chen’s Notation • Entities • rectangle containing the entity’s name. • Attributes • oval containing the attribute’s name. • Relationships • diamond containing the relationship’s name.

9. Steps for creating an ERD • Identify entities • Identify attributes • Identify relationships

10. 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)

11. ERD using IE Notation (most popular)

12. Entity Instance Entity instance: a single occurrence of an entity. • 6 instances Entity: student instance

13. Attributes “describe property or characteristic of an entity” • Entity: Employee • Attributes: • Employee-Name • Address (composite) • Phone Extension • Date-Of-Hire • Job-Skill-Code • Salary

14. Classes of attributes • Simple attribute • Composite attribute • Derived attributes • Single-valued attribute • Multi-valued attribute

15. 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

16. 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)

17. (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)

18. Type of Relationships • One – to – One (1:1) • Each instance in the relationship will have exactly one related member on the other side • One – to – Many (1:M) • 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

19. 1:1 relationship in Set notation

20. 1:M relationship in Set notation

21. M:N relationship in Set notation

22. M:N relationship Each student takes many classes, and a class must be taken by many students. STUDENT CLASS IS_TAKEN_BY 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) **

23. 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. 

24. Example M:N Relationship 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

25. Converting M:N Relationship to Two 1:M Relationships Bridge Entity

26. Bridge Entity • MUST have a composite (unique) identifier • STU_NUM (from STUDENTentity) and CLASS_CODE (from CLASSentity)