1 / 62

Systems Analysis & Design

CSUN Information Systems. Systems Analysis & Design. http://www.csun.edu/~dn58412/IS431/IS431_SP14.htm. Data Modeling. IS 431: Lecture 5. Data Modeling. Elements of Entity Relationship Diagram (ERD) Relational Data Model. Data Modeling.

oshin
Download Presentation

Systems Analysis & Design

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CSUN Information Systems Systems Analysis & Design http://www.csun.edu/~dn58412/IS431/IS431_SP14.htm Data Modeling IS 431: Lecture 5

  2. Data Modeling • Elements of Entity Relationship Diagram (ERD) • Relational Data Model IS 431 : Lecture 5

  3. Data Modeling Data Modeling (database modeling, information modeling) is a technique for organizing and documenting a system’s data in a model. Entity Relationship Diagram (ERD) depicts data in terms of the entities and relationships described by the data. IS 431 : Lecture 5

  4. From Data Model to DB Implementation • ERD: a conceptual model of data entities (things of interest) , their attributes (characteristics of interest), and their relationships (with other things) in an information system (technical independent).(Analysis) • Relational Data Model: a blueprint for implementation of a conceptual data model (ERD) in relational database environment (software independent)(Design) • MS Access Relationship Window: a graph showing how a data model is implemented with Microsoft Access (a specific DBMS software)(Implementation) IS 431 : Lecture 5

  5. Entity-Relationship Diagrams • Database = data + relationship • ERD is used to model data and their relationship • ERD is a graphical representation of a conceptual data model • ERD is resource independent: it does not commit to any particular database environment. IS 431 : Lecture 5

  6. Entities • Entityis a group of attributes corresponding to the same conceptual object about which we need to capture and store data • objects, persons, places, events concepts whose existence is not dependent on many other entities • Entity is a set of occurrences (instances) of the object that it represents • Entity must have a unique name (a singular noun), unique identifier, and at least one attribute (the identifier itself) IS 431 : Lecture 5

  7. Entities: Examples • Persons: agency, contractor, customer, department, division, employee, instructor, student, supplier. • Places: sales region, building, room, branch office, campus. • Objects: book, machine, part, product, raw material, software license, software package, tool, vehicle model, vehicle. • Events: application, award, cancellation, class, flight, invoice, order, registration, renewal, requisition, reservation, sale, trip. • Concepts: account, block of time, bond, course, fund, qualification, stock. IS 431 : Lecture 5

  8. Entity Instance: Example Entity instance – a single occurrence of an entity. Attributes Entity Instances IS 431 : Lecture 5

  9. Entities: Attributes An attribute is a descriptive property or characteristic of interest of an entity. Also called element, property, and field. The data type for an attribute defines what type of data can be stored in that attribute. The domain of an attribute defines what values an attribute can legitimately take on. The default value for an attribute is the value that will be recorded if not specified by the user. IS 431 : Lecture 5

  10. Data Type IS 431 : Lecture 5

  11. Data Domains IS 431 : Lecture 5

  12. Entities: Identification • A key is an attribute, or a group of attributes, that assumes a unique value for each entity instance. • A group of attributes that uniquely identifies an instance of an entity is called a composite(or concatenated) key. • A candidate key is a “candidate to become the primary key” of instances of an entity. (StudentID, SSN, DriverLicenseNo) • A primary key(identifier) is that candidate key that will most commonly be used to uniquely identify a single entity instance. (StudentID) • Any candidate key that is not selected to become the primary key is called an alternate key. (SSN, DriverLicenseNo) • A secondary key is an attribute whose values divide all entity instances into useful subgroups/sub-criteria. (Major, Gender, etc) IS 431 : Lecture 5

  13. Entities ... ENTITY NAME - entity id - attribute 1 - attribute 2 - ………….. - attribute n CUSTOMER - Customer_ID - Cust_Name - Cust_Address - Cust_Phone IS 431 : Lecture 5

  14. Relationships • ARelationshipdocuments an association between one, two, or more entities • It must have a name (and may carry data) • Degree of Relationship (number of entities) • Cardinalities of Relationship (number of instances/members) IS 431 : Lecture 5

  15. Relationships: Degree • Degree of Relationship defines how many entities are involved in a relationship: • Recursive (Unary) • Binary • Ternary • …. IS 431 : Lecture 5

  16. Relationships: Cardinalities • Cardinalitiesdocument how many occurrences/members of one entity can relate to a single occurrence/member of another entity in a relationship. • Max / Min number of occurrences • Reflect business policies or general business practices (e.g., how many classes a student can take, how many students a class can hold). (15, 35) (1, 5) Student Take Class IS 431 : Lecture 5

  17. Cardinalities ... One-to-One (1:1) Relationship 1 1 Sales Pay Cash Collections Ex: Cash Sales One-to-Many (1:M) Relationship 1 M Sales Pay Cash Collections Ex: Installment Payments IS 431 : Lecture 5

  18. Cardinalities ... Many-to-One (M:1) Relationship M 1 Sales Pay Cash Collections Ex: Pay many credit purchases in full Many-to-Many (M:N) Relationship M N Sales Pay Cash Collections Ex:Pay credit purchases with partial payments over some months IS 431 : Lecture 5

  19. Cardinalities ... • Maximum / Minimum Cardinality (0,E) (1,S) Employee Possess Skill An employee must have minimum one skill A particular skill may not be possessed by any employee (1,E) (0,P) Employee Belong Project A project must have minimum one employee A particular employee may not belong to any project IS 431 : Lecture 5

  20. Relationships ... • Recursive relationships involve only one entity (occurrences in the same entity) N N ENTITY - entityID - attribute 1 - attribute 2 TOWN -TownID -Town_Name Relationship Name Attrib 1, Attrib 2 TRAVEL Distance M M IS 431 : Lecture 5

  21. Relationships ... • Binary relationship ENTITY 1 -entity1_ID -attribute 11 -attribute 12 ENTITY 2 -entity2_ID -attribute 21 -attribute 22 Relationship Name Attrib 1, Attrib 2 M N EMPLOYEE - Emp_ID - Emp_Name - Emp_Title PROJECT - Project_ID - Proj_Name - Proj_Due Manage Date M N IS 431 : Lecture 5

  22. Relationships ... • Ternary relationship EMPLOYEE - EmpID - Emp_Name - Emp_Title PROJECT - ProjectID - Proj_Name - Proj_Due Assign Date N M P TASK - TaskID - TaskName IS 431 : Lecture 5

  23. Logical Data Modeling Stages • Context Data model • To establish project scope • Key-base data model • Eliminate nonspecific relationships • Add associative entities • Include primary and alternate keys • Precise cardinalities • Fully attributed data model • All remaining attributes • Subsetting criteria • Normalized data model IS 431 : Lecture 5

  24. What is a Good Data Model? • A good data model is simple. • Data attributes that describe any given entity should describe only that entity. • Each attribute of an entity instance can have only one value. • A good data model is essentially non redundant. • Each data attribute, other than foreign keys, describes at most one entity. • Look for the same attribute recorded more than once under different names. • A good data model should be flexible and adaptable to future needs. IS 431 : Lecture 5

  25. Building ERD • Identify entities of interest (Use REAL framework – Resources/Event/Agents/Locations – more in Lecture 5B) – (Top Down) • Identify all the attributes with sufficient details (context specific) • Assign attributes to entities – (Bottom Up) • Identify degrees of relationships between entities (context specific) • Complete the relationships with cardinalities (context specific) • Build the model IS 431 : Lecture 5

  26. Rules in ERD Building • Each entity must have a name • Each entity must have an identifier • An occurrence itselfcannotbe an entity (a constant or a table with one unique record?) • Each relationship must have a name (may or may not carry data) • Reasonable cardinalities (context specific) IS 431 : Lecture 5

  27. Alternative ERD Notations Attribute 1 Attribute 2 Attribute 1 Attribute 2 relates to Entity 2 Attribute 3 Entity 1 is related to Attribute 3 Attribute 4 Attribute 4 Attribute 5 IS 431 : Lecture 5

  28. Alternative ERD Notations 1 (0, 1) Entity Entity Entity 0 (1, 1) 1 Entity Entity Entity 1 (0 , *) M Entity Entity Entity 0 (1 , *) M Entity Entity Entity 1 IS 431 : Lecture 5

  29. Associative Entity • An associative entity is an entity that inherits its primary key from more than one other entity (called parents). • Each part of that composite (concatenated) key points to one and only one instance of each of the connecting entities. • Represent an M:N relationship carrying data EMPLOYEE - Emp_ID - Emp_Name - Emp_Title PROJECT - Project_ID - Proj_Name - Proj_Due ASSIGN Date M N 1 1 IS 431 : Lecture 5

  30. M:N Relationship vs. Associative Entity EMPLOYEE - Emp_ID - Emp_Name - Emp_Title PROJECT - Project_ID - Proj_Name - Proj_Due ASSIGN Date M N EMPLOYEE - Emp_ID - Emp_Name - Emp_Title PROJECT - Project_ID - Proj_Name - Proj_Due ASSIGNMENT - Emp_ID - Proj_ID - Date 1 M N 1 IS 431 : Lecture 5

  31. Foreign Keys in Relational Database • A foreign key (FK) in Entity E1(CustID in ORDER) is a primary key of another Entity E2(CustID in CUSTOMER), which is used to identify (link) a 1:M relationship between E1 and E2 (CUSTOMER and ORDER). • Foreign key is made on the many side (CUSTOMER has many ORDERS, therefore ORDER carries CustID as FK to show which Customer places that Order) IS 431 : Lecture 5

  32. Foreign Key CUSTOMER ORDER 1:M Relationship CUSTOMER CustomerID ORDER OrderID CustomerID Primary Key Foreign Key IS 431 : Lecture 5

  33. Foreign Keys in Relational Database. . . • In M:N relationship, the associative/junction table with a composite key will be used to capture the relationship. • ORDER involved many PRODUCTS, PRODUCT involved in many ORDERS. Composite key ProductID-OrderID for LINE ITEM to indicate which product involves in which sales • Each part of the composite key serves like a foreign key. • Sometimes, a “surrogate” key (RecordNo) is used as primary key to simplify the identification of record. IS 431 : Lecture 5

  34. Composite Key ORDER PRODUCT M:N Relationship ORDER OrderID PRODUCT ProductID Primary Key LINE_ITEM RecordNo OrderID ProductID Composite Key JUNCTION TABLE IS 431 : Lecture 5

  35. ERD Case Studies • ERD 1: Group Consultants Database • ERD 2: Video Rental Database • ERD 3: Health Club Database • ERD 4: Music Store Database to be discussed in class IS 431 : Lecture 5

  36. Convert ERD to Relational Data Model • Legend • E1, E2, E3 , … represent entities • a1, a2, a3, …, b1, b2, b3, … represent entity attributes • R represents a relationship • r represents one or many attributes that can be carried by a relationship IS 431 : Lecture 5

  37. Convert ERD to Relational Data Model … • Pattern #1 E1 - a1 - a2 E1( a1, a2 ) TASK - TaskID - TaskName TASK( TaskID , TaskName ) IS 431 : Lecture 5

  38. Convert ERD to Relational Data Model … • Pattern #2 1 E1 - a1 - a2 E1( a1, a2 ) R( a1, a1’, r) R r 1 1 INDIVIDUAL - SSN - Name INDIVIDUAL ( SSN , Name) MARRY ( SSN , SSN’, Date) MARRY Date 1 Husband Wife IS 431 : Lecture 5

  39. Convert ERD to Relational Data Model … • Pattern #3 1 E1 - a1 - a2 E1( a1, a2 ) R( a1’, a1 ) R M 1 INDIVIDUAL - SSN - Name INDIVIDUAL ( SSN , Name) LEAD ( SSN’ , SSN ) LEAD M Member Leader IS 431 : Lecture 5

  40. Convert ERD to Relational Data Model … • Pattern #4 M E1 - a1 - a2 E1( a1, a2 ) R( a1, a1’, r) R r N M TOWN - TownID - Name TOWN ( TownID , Name) TOUR ( TownID, TownID’ , Date ) Tour Date N IS 431 : Lecture 5

  41. Convert ERD to Relational Data Model … • Pattern #5 E1( a1, a2 ) E2( b1, b2 , b3 , a1) E1 - a1 - a2 E2 - b1 - b2 - b3 R 1 1 OR E1( a1, a2 , b1 ) E2( b1, b2 , b3 ) PROJECT - ProID - ProName - ProDue EMPLOYEE - EmpID - EmpName 1 1 MANAGE EMPLOYEE(EmpID, EmpName) PROJECT(ProID, ProName, ProDue, EmpID) OR EMPLOYEE(EmpID, EmpName, ProID) PROJECT(ProID, ProName, ProDue ) IS 431 : Lecture 5

  42. Convert ERD to Relational Data Model … • Pattern #6 E1 - a1 - a2 E2 - b1 - b2 - b3 E1( a1, a2 ) E2( b1, b2 , b3 , a1) R M 1 CUSTOMER - CustID - CustName PURCHASE - PurchID - Item - Quantity 1 M INVOLVE CUSTOMER (CustID, CustName) PURCHASE(PurchID, Item, Quantity, CustID) IS 431 : Lecture 5

  43. Convert ERD to Relational Data Model … • Pattern #7 E1 - a1 - a2 E2 - b1 - b2 - b3 E1( a1, a2 ) E2( b1, b2 , b3) R( a1, b1, r) R N M r CUSTOMER - CustID - CustName SALESREP - RepID - RepName M N SALE Date CUSTOMER (CustID, CustName) SALESREP (RepID, RepName) SALE(CustID, RepID, Date) IS 431 : Lecture 5

  44. Convert ERD to Relational Data Model … • Pattern #8 E1 - a1 - a2 E2 - b1 - b2 - b3 R N 1 r P E3 - c1 - c2 - c3 E1( a1, a2 ) E2( b1, b2 , b3) E3( c1, c2 , c3 ) R( b1, c1 , a1, r) IS 431 : Lecture 5

  45. Convert ERD to Relational Data Model … • Pattern #8 (example) EXECUTIVE - ExecID - ExecName DEPT - DeptID - DeptName RESPONSIBLE N 1 AppointDate P PROJECT - ProjectID EXECUTIVE( ExecID, ExecName) DEPT( DeptID, DeptName) PROJECT (ProjectID) RESPONSIBLE (DeptID, ProjectID, ExecID, AppointDate ) IS 431 : Lecture 5

  46. Convert ERD to Relational Data Model … • Pattern #9 E1 - a1 - a2 E2 - b1 - b2 - b3 R N M r P E3 - c1 - c2 - c3 E1( a1, a2 ) E2( b1, b2 , b3) E3( c1, c2 , c3 ) R(a1, b1, c1 , r) IS 431 : Lecture 5

  47. Convert ERD to Relational Data Model … • Pattern #9 (example) EMPLOYEE - EmpID - EmpName TASK - TaskID ASSIGN N M Date P PROJECT - ProjectID EMPLOYEE( EmpID, EmpName ) TASK( TaskID) PROJECT( ProjectID) ASSIGN(EmpID, TaskID, ProjectID, Date) IS 431 : Lecture 5

  48. Generalization • Generalization is a technique wherein the attributes that are common to several types of an entity are grouped into their own entity, called a supertype. • An entity supertype is an entity whose instances store attributes that are common to one or more entity subtypes. • An entity subtype is an entity whose instances inherit some common attributes from an entity supertype and then add other attributes that are unique to an instance of the subtype. IS 431 : Lecture 5

  49. Type & Subtypes Employee Name DOB SSN DateHire Executive Foreperson Worker Rank Wage Rate Position Location DateAppoint Salary Factory Station DateAppointWage Rate IS 431 : Lecture 5

  50. Generalization Hierarchy IS 431 : Lecture 5

More Related