MIS 340: Data Modeling 1

1. MIS 340:Data Modeling 1 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? • Represent “reality” of the actual database • Blue print: documentation • Effective Communication Tool • User involvement • Represent abstraction of requirements • Identify the business rules to be stored in the database • Independence from a particular DBMS

4. Conceptual data modeling • The conceptual data modeling revolves around discovering and analyzing organizational and users data requirements (see the supplement). • 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. Entity Instance (con’t)

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

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

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

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

18. Single-valued attribute • can have only a single (atomic) 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. • Part No: CA-08-02-189935 • Location: CA, Factory#:08, shift#: 02, part#: 189935

19. Multi-valued attributes • can have many values. • Examples: • A person may have several college degrees. • A household may have several phones with different numbers • A car color

20. Example - “Movie Database” • Entity: • Movie Star • Attributes: • SS#: “123-45-6789” (single-valued) • Cell Phone: “(661)123-4567, (661)234-5678” (multi-valued) • Name: “Harrison Ford” (composite) • Address: “123 Main Str., LA, CA” (composite) • Birthdate: “1-1-50” (simple) • Age: 50 (derived)

21. How to find entities? • Entity: • A fundamental THING of relevance to the enterprise about which data may be kept: things acted on by business activities • people, places, objects, events…. • Tangible: customer, product • intangible (active/conceptual): equipment breakdown • look for nouns (beginner) BUT a proper noun is not a good candidate….

22. How to find attributes? • Attribute: • property or characteristic of an entity • A descriptor whose values are associated with individual entities of a specific entity type • look for descriptions, characteristics, and properties of entity (beginner)

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