ITSD204 C21-3/C22-3 SYSTEM ANALYSIS METHODS - DATA MODELING

1. ITSD204 C21-3/C22-3 SYSTEM ANALYSIS METHODS - DATA MODELING By Orvilla V. Balangue, MSCS SAS-ICT Faculty

2. Learning Objectives • Differentiate between logical and physical system models. • Recognize and interpret the basic concepts and constructs of a data model. • Read and interpret an entity relationship data model. • Explain when data models are constructed during a project and where the models are stored. • Construct a fully attributed entity relationship diagram (ER-D) and describe all data structures and attributes to the repository. • Normalize a logical data model to remove impurities that can make a database unstable, inflexible, and non-scalable. • Translate business requirements into systems models, clearly represent the system analysis and design by means of basic diagrammatic modelling tools, and design solutions for business requirements.

3. Topic Outline V. SYSTEM ANALYSIS METHODS-DATA MODELING • System Concepts for Data Modeling • Process of Logical Data Modeling • How to Construct Data Models

4. What is a Data Model? • Definition: precise description of the data content in a system • Types of data models: • Conceptual: describes WHAT the system contains • Logical: describes HOW the system will be implemented, regardless of the DBMS • Physical: describes HOW the system will be implemented using a specific DBMS

5. Why do we need to create data models? • To aid in the development of a sound database design that does not allow anomalies or inconsistencies • Goal: to create database tables that do not contain duplicate data values that can become inconsistent

6. Types of Data Models • Entity-Relationship (E-R) Models • Only addresses data and relationships • Classic, simplest • Best for deriving a sound table design • Many extensions/variations exist • Basis for most other modeling approaches • UML (unified modeling language) • Class models • Goes beyond data, also models behaviors

7. The basic steps in data modeling are: 1. Identify the things you are interested in. These are called entities in the logical model and tables in the physical model. 2. Identify relationships between entities. 3. Identify the keys for each entity. 4. Identify the attributes for each entity. • Data models are expressed in an Entity Relationship Diagram (ERD) and a Data Element Dictionary (DED). The ERD is a schematic representation of the database while the DED is a text representation. • The two must be combined to get a clear picture of the data model.

8. Creating an Entity-Relationship Model • Identify entities • Identify entity attributes and primary keys • Specify relationships

9. Data Entities • Entity • A "thing" about which you want to store data in an application • Multiple examples (instances) of the entity must exist • Goal: • Store data about each entity in a separate table • Do not store duplicate data in multiple tables or records • Examples: CUSTOMER, PRODUCT

10. Data Model Naming Conventions • Entity names are short, descriptive, compound word singular nouns • UWEC_STUDENT, CANDY_PRODUCT, • Entity names will ultimately correspond to table names • Why singular? • Makes more sense when you start talking about relationships

11. Data Entity Instances • Entity instance • A specific occurrence (data value) of an entity • An entity must have multiple entity instances or it is not really an entity! • Examples: Davey Jones, Celestial Cashew Crunch

12. ER Model Attributes • Attribute • A characteristic (data field) of an entity that you want to store in the database • Examples: CUST_ID, PROD_DESC • Attribute value • The value of a particular attribute for a particular entity instance • Examples: 42, "Nuts Not Nachos"

13. Data Model Naming Conventions(continued) • Attribute names are descriptive compound words that correspond to the entity name • Attribute names will ultimately correspond to field names • Every attribute name within the database should be unique

14. ER Model Notation • Represent entities as rectangles • List attributes within the rectangle Entity Primary key Attributes

15. Attributes Selection Issues • Primary key • Atomic • Composite • Multi-valued • Derived

16. Primary Key Attributes • Attribute whose value is unique for every entity instance • Every entity MUST have a PK • Designate by: • Placing as first attribute in the entity • Underline • Label using "PK"

17. Selecting Primary Keys • Must be values that are: • Unique for every possible record • Do not change • Best practice: numeric with no blank spaces or formatting characters • Often you need to create a surrogate key • ID value that serves only to identify the object in the database • Exception: objects with "natural" primary keys • SKU • ISBN • VIN

18. Atomic and Composite Attributes • Atomic attribute: represents a single data value • 15, “Daniel", 12/25/2009 • Composite attribute: can be decomposed into atomic attributes • "James B. Brown" • "5580 Pinewood Road, Eau Claire, WI 54701"

19. Composite Attributes • Decompose into atomic components for: • Sorting • Searching • Formatting Student_First_Name Student_MI Student_Last_Name Student Student_ID Student_Name Student_Address Student_DOB Student_Class Student_Address_Line_1 Student_Address_Line_2 Student_City Student_State Student_Country Student_Postal_Code

20. Multi-Valued Attributes • Can have multiple values for the same entity Student Student_ID (PK) Student_First_Name Student_Last_Name Student_Address Student_DOB Student_Class Student_Phone1 Student_Phone2 Employee Employee_ID (PK) Employee_First_Name Employee_Last_Name Employee_Address Employee_DOB Employee_Dependent1 Employee_Dependent2

21. Handling Multi-valued Attributes • If it has a definite maximum number, leave as a repeating attribute • If the upper limit is variable, make a new entity Student Student_ID Student_First_Name Student_Last_Name Student_Address Student_DOB Student_Class Student_Phone1 Student_Phone2 Employee Employee_ID Employee_First_Name Employee_Last_Name Employee_Address Employee_DOB Employee_Dependent1 Employee_Dependent2 Dependent Dependent_ID Dependent_Name has

22. Derived Attributes • Value that can be derived from other attributes • Student_Age = 22 (DOB = 11/20/1986, current date is 11/13/2009) • Order_Total = $500 (Item 1 cost = $200, Item 2 cost = $300)

23. Handling Derived Attributes • Store the underlying data values from which you can derive the attribute value … • Examples: • DOB => Age • CurrentPrice and UnitsSold of an item (for a sales order) • … unless the underlying values can change! • PRODUCT_PRICE, COURSE_CREDITS

24. Creating an Entity-Relationship Model • Identify entities • Identify entity attributes and primary keys • Specify relationships

25. Data Model Relationships • Specify the number of instances of one entity that can be associated with instances of a related entity • Types: • 1:M • 1:1 • M:M • “M” denotes some value greater than 1 whose upper bound is undetermined • This is called relationship cardinality

26. Example:One-to-One (1:1) Relationship • an Employee is related to one Company Car • a Company Car is related to one Employee

27. Example 1:1 Relationship Spouse Spouse_ID Spouse_Name Has Customer Customer_ID Customer_Name Customer_Address

28. ExampleOne-to-Many (1:M) Relationship • Instructor is related to many Courses • Course is related to one Instructor • As a standard convention in ER diagrams, always write the singular form of the entity and let the relationship’s cardinality attach the plural interpretation if appropriate.

29. ExampleOne-to-Many (1:M) Relationship Store Store_ID Store_Name Store_Address Rents Video Video_ID Video_Title Video_Format

30. ExampleMany-to-Many (M:M) Relationship • an Instructor is related to many Courses • a Course is related to many Instructors

31. Example M:M Relationship Video Video_ID Video_Title Rents Customer Customer_ID Customer_Name Customer_Address

32. Example ER Model

33. Sample Problem: • Data model for a simple Order Processing System for Starbucks

34. Simple Order Processing System • We can see customers ordering products (food, drinks and so on). • 1. What are the ‘Things of Interest’? • These things will be called ‘Entities in a Data Model’ and ‘Tables in a database’.

35. Identify relationships between entities. • Identify the keys for each entity, Creating Entities, Primary Keys

36. Foreign Keys, relationships between the entities. • Identify the attributes for each entity.

37. Summary: The Data Modeling Process • Define entities • Define attributes • Define relationships • Identify relationship cardinality (1:1, 1:M, M:M)

38. References: • http://www.databaseanswers.org/downloads/Data_Modeling_by_Example_Vol_1.pdf • Gerhard Beck, http://members.verizon.net/~gtbeck/data_modeling.pdf