1 / 24

FIS 431/631 Financial Information Systems: Analysis and Design ERD & Normalization Joe Callaghan

FIS 431/631 Financial Information Systems: Analysis and Design ERD & Normalization Joe Callaghan. Oakland University Department of Accounting & Finance. Data Modeling. What information is required by the business Logical view vs. physical view Carried out in parallel with activity analysis

callie
Download Presentation

FIS 431/631 Financial Information Systems: Analysis and Design ERD & Normalization Joe Callaghan

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. FIS 431/631Financial Information Systems: Analysis and DesignERD & NormalizationJoe Callaghan Oakland University Department of Accounting & Finance

  2. Data Modeling • What information is required by the business • Logical view vs. physical view • Carried out in parallel with activity analysis • We already know much because of REA

  3. Analysis Steps • Activity Models • FHD • ADD • REA • Events, RALs • Transform Events to E-Ts necessary to capture event information, i.e. verb to noun • Data Model: ERD • Entity Types, Attributes and Relationships

  4. Process and Events Sell goods Receive cash Business Process: Simple MSC

  5. Salesperson Department Sell Goods Customer Inventory Receive Cash Store Cashier Cash A REA Interaction Model for MSC Function

  6. Sell Goods Transformation • To SALE • Header and Detail for multi-product firm • To Sale Header • To Sale Detail • Now, rearrange RAL relationships • Look for special relationships (i.e. business rules)

  7. Data model: ERD with Normalization

  8. Entity Types • A collection of business objects • An Entity is a uniquely identifiable object of the collection, a manifestation of the entity type, downstream is a tuple (or row) in a database table • Uniquely Identifiable mechanism is the Identifier (downstream it is the Primary Key (PK)), which enforces Entity Integrity • Have attributes

  9. Attributes • Information that describes the entity type • Has a data type • Is mandatory or optional • Has a Domain • Downstream are columns in Relational tables • Foreign keys are not attributes!

  10. Relationships • Business rule aspects: • Cardinality: pairing • Optionality: requiring (type of referential integrity inferred) • REA Types: • From “Event” to RAL • From “Event” to “Event” • Among the RAL

  11. Different Notations to Represent Relationship Cardinalities (1,1) (1,*) (0,1) (0,*)

  12. Entity Subtypes • An entity subtype is a subdivision of the entity type • Each subtype must have one or more special attribute of its own and/or participate in one or more special relationship of its own • The special attributes and relationships are not common to all entities of the entity type

  13. Partitioning • A classifying attribute is the basis for partitioning, e.g. EMPLOYEE - Name, Number, Type where Type can be Full-Time or Part-Time • Each subtype must be associated with a permitted value of the classifying attribute • Each subtype inherits properties ( attributes, identifiers, relationships) of its parent entity type

  14. Partitioning can be • Fully enumerated • Not fully enumerated • There can be multiple partitionings Manager Employee Full Time NAME ADDRESS NUMBER TYPE JOB STATUS Part Time Staff

  15. Rules for Partitioning and Subtypes • Each subtype belongs to exactly one partitioning • Each partitioning must be associated with a classifying attribute • The subtype to which a given entity occurrence belongs must be identifiable by a classifying attribute value • A fully enumerated partitioning must divide the entity type into two or more subtypes

  16. Refining the Data Model • Developing a data model is iterative, aiming at an accurate portrayal of the business • A set of techniques can be applied to identify and reduce likely errors • Each component of the data model is subject to refinement

  17. Identifiers (to be Primary Keys (PKs)) • Each identifier of an entity type consists of one or more attributes and/or relationships that uniquely identify an entity • A single attribute may be an identifier, e.g. STUDENT - Name, Address, Number

  18. Composite Identifier Using an Attribute and Relationship Attributes NUMBER PRICE QUANTITY is included on Order Item I Order includes Identifier is a combination of: NUMBER and the fact that Each ORDER ITEM is included on one ORDER

  19. Composite Identifier Using Relationships Product Warehouse holds stores is stored in Stock Item I o o I is held for Identifier is a combination of: Each STOCK ITEM is stored in one WAREHOUSE and each STOCK ITEM holds one PRODUCT

  20. Identifiers - Summary • Each value of an identifier uniquely identifies one entity of a given type • Identifiers may be • Simple (one attribute) • Composite (two or more attributes and/or relationships

  21. Identifiers - Summary (continued) • Identifiers may consist of an entity type’s own: • Attributes • Relationship memberships • Mixture of both • An entity type must have at least one identifier • An entity type may have more than one identifier

  22. Relationship Membership Creation and Deletion Rules Customer delete - if relationship within circle is mandatory, when customer is deleted, whatever customer order is related is also deleted create - if the relationship outside the circle is mandatory, a create of customer creates the other entity Order

  23. Refining 1:1 Relationships • Merge entity types where both relationships are mandatory • Leave alone if both relationships are optional

  24. Refining M:N Relationships • An M:N relationship may contain hidden data Product Order o o specified by specifies Product Order Purchasing Agent approved by specified by approves specifies

More Related