1 / 49

DATABASE ANALYSIS

DATABASE ANALYSIS. Chandra S. Amaravadi. IN REQUIREMENTS ANALYSIS. Overview of database analysis (requirements) User views Integrity constraints Steps in database analysis The ER model Guidelines and examples ER Additional semantics. OVERVIEW OF DATABASE ANALYSIS.

callum-rocha
Download Presentation

DATABASE ANALYSIS

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. DATABASE ANALYSIS Chandra S. Amaravadi

  2. IN REQUIREMENTS ANALYSIS.. • Overview of database analysis (requirements) • User views • Integrity constraints • Steps in database analysis • The ER model • Guidelines and examples • ER Additional semantics

  3. OVERVIEW OF DATABASE ANALYSIS

  4. THE DEVELOPMENT CYCLE The database development cycle consists of: • Planning • Analysis (Requirements definition) • Design (Logical & Physical) • Implementation • Maintenance

  5. DATABASE ANALYSIS Database Analysis is the stage of the development cycle that is concerned with identifying and modelling the data requirements of the organization. Primary database analysis takes place during this stage

  6. INFORMATION COLLECTED DURING RD Two types of information are gathered: • Information regarding data structure (from Views) • Information regarding rules/constraints e.g. weight limit

  7. IDENTIFYING DATA STRUCTURE A view is a user’s representation of data (also the way it is presented to them): • Receipts • Forms • Reports and memos • Screen displays • Schedules etc. Information identified: • Eclasses • Attributes • Rules if any

  8. AN EXAMPLE OF A VIEW QUOTATION Quote no: A61-45 Date: June 15,’12 Qty Description Price

  9. IDENTIFYING CONSTRAINTS INTEGRITY CONSTRAINTS Are rules to preserve integrity of the database • Domain • Business constraints • Database constraints • Entity integrity • referential integrity

  10. EXAMPLES OF CONSTRAINTS DOMAIN CONSTRAINTS Constraints on allowable value of an attr. e.g. E_name is alphabetic, E_address is alphanumeric BUSINESS CONSTRAINTS Rules concerning data: customers, purchases etc. e.g. credit limit > $4,000, enrollment limit for course DATABASE CONSTRAINTS: ENTITY & REFERENTIAL INTEGRITY • Primary key; Pkey <> Null • Referential integrity; values of cross-reference keys in different tables must match.

  11. STEPS IN DATABASE ANALYSIS (FYI) The different steps in database analysis are: 1. Define scope (from planning stage) 2. Select methodology (ER, UML) 3. Identify views of data 4. Develop conceptual data model 5. Cross-check with planning 6. Specify constraints

  12. TOP DOWN VS BOTTOM-UP Enterprise Analysis Target Database Cross-check Enterprise Model Data Model* Proposed Database User views The enterprise model is cross-checked with the data model *Note: the terms data model, conceptual data model and ER Model are synonymous

  13. THE ER METHODOLOGY

  14. ER MODEL A method for drawing data models • Introduced in 1976 • Became popular • Used to communicate requirements

  15. BASIC MODELLING CONCEPTS • Entity classes • Attributes • Multi-valued • Derived • Relationships • Degree/cardinality concepts • Class/subclass • Recursive

  16. ENTITY CLASSES Entity Class – Collection of related entities. CUSTOMER CUST# • Common nouns (concepts are fine too!) • Customers, Products, Students etc. • Cannot be attributes or proper nouns • Cannot be database concepts

  17. ATTRIBUTES Attribute – Property of an Entity Class Name Phone ID • Generally on top of eclass • or below eclass or side • Use ovals • Underline pkey CUSTOMER

  18. ATTRIBUTES.. Home Work Phone Name ID CUSTOMER

  19. MULTI-VALUED ATTRIBUTES.. An attribute which can have more than one set of values per entity instance. Name phone ID CUSTOMER

  20. DERIVED ATTRIBUTES An attribute whose value can be derived or computed from other attributes e.g. • total # of students • GPA • average score Do not include derived attributes in your ER model

  21. RELATIONSHIPS Relationships: logical and meaningful connections between two or more entity classes. Name Phone Cust# CUSTOMER Places ORDER • Use diamonds, label • L-R, T-B order cardinality, degree

  22. CARDINALITY CARDINALITY -- Number of entities that participate in a relationship. Can be 1:1, 1:M or M:N Between two entity classes A and B, these are defined as follows: 1:1 -- Each instance of A is associated with one instance of B and vice versa. 1:M -- Each instance of A is associated with many instances of B. Each instance of B is associated with one instance of A. M:N -- Each instance of A is associated with many instances of B and vice versa. Max cardinality rule: In > 2 way relationships, Use max cardinality

  23. DISCUSSION IDENTIFY THE CARDINALITIES OF THE FOLLOWING RELATIONSHIPS • company -- president • instructor -- students • flights -- pilots • city -- convention centers • department -- employees • sports team (“pro”) -- players • company -- city • books – authors • city -- subdivisions

  24. DEGREE OF A RELATIONSHIP The Degree of a relationship: The number of entity classes that participate in the relationship: If it is one à Unary If two à Binary If three à Ternary If four à Quarternary Places CUSTOMER ORDER PRODUCTS

  25. DEGREE.. physician chair of assoc. Max cardinality rule: In > 2 way relationships, Use max cardinality

  26. CLASS/SUBCLASS RELATIONSHIPS.. Class/subclass: Entity classes can be grouped into parent/child or superclass/class or Class/subclass relationships based on common characteristics. Class/ Superclass CUST. Is-a Sub -classes OLD CUST. NEW CUST.

  27. CLASS/SUBCLASS RELATIONSHIPS.. Join dt The attr. of a sub-class are inherited from the parent class, but can have additional attributes CUST. Class/Superclass Is-a Sub -classes OLD CUST. NEW CUST. Member#

  28. ABBREVIATED CLASS/SUBCLASS Class/ Superclass CAR Is-a GM FORD (Exclusive, Non exhaustive)

  29. SUMMARY OF CONCEPTS Entity – Individual example of person, place or thing. Entity Class – Collection of related entities. Attributes – Properties of entity classes about which we would like to collect information. Multi-valued – An attribute with more than one set of values attribute per entity instance. Relationship -- Logical and meaningful connection between two or more entity classes. Class/subclass- Grouping of entities based on common char. Cardinality -- Number of entities participating in a relationship. Degree -- Number of eclasses participating in a relationship. Unary / -- A relationship among entities within an recursive eclass

  30. Entity classes Relationships Attributes Multivalued attributes Class/ subclass 1 : 1 THE ER NOTATION 1 : M M : N

  31. DRAWING THE ER CHART • Identify the eclasses (and attributes) • Identify relationships among eclasses • decide which relationships to show • verbalize and map relationships • Use rectangles for eclasses , diamonds • for the relationships • Depict cardinalities • Draw attributes (underline Pkey) • Handle the special cases

  32. AN EXAMPLE ER CHART Cust# Name CUSTOMER Places ORDERS Are for PRODUCTS

  33. ER CORRECT EXAMPLES

  34. ER INCORRECT EXAMPLES 1403 W Adams House

  35. EXAMPLE PROBLEMS MODEL THE FOLLOWING USING ER • A product can be on one or more orders • A student can have one or more loans at a bank. The bank maintains the following information ss#, name, address, loan id, amt, duration, balance, branch#, branch_mgr. • A judge tries cases in a court. A case can be a civil case or a criminal case. • The DMV issues several types of licenses to drivers: passenger car, chauffeur, motorcycle, farm vehicle. • A basketball team plays games against other teams.

  36. ADDITIONAL COMMENTS • Build model around central e-class • Use only vertical and horizontal lines • Decide whether attributes belong with • eclasses, or relationships • Assume additional attributes if needed • Do not show computed attributes or values • Avoid cycles • Refine, refine...

  37. ER CHECK LIST Once an ER diagram is drawn, go through this check list: • All entity classes depicted? • sub classes shown? • cardinalities shown? • All attributes included/labelled? • multi-valued attr. Shown (if any)? • computed attr. omitted? • pkeys marked? Fkeys omitted? • Relationships correct/labelled? • relationship attributes? recursive? • cycles avoided?

  38. ER ADVANCED SEMANTICS

  39. EXCLUSIVE AND NON-EXCLUSIVE Books Books Is a Is a non- Fiction Poetry Science History Arts Fiction NON-EXCLUSIVE EXCLUSIVE

  40. EXHAUSTIVE AND NON-EXHAUSTIVE Books Books Is a Is a non- Fiction Poetry Literature Fiction NON-EXHAUSTIVE EXHAUSTIVE

  41. ER ADVANCED SEMANTICS.. Books Is a Computers Business NON-EXCLUSIVE AND NON-EXHAUSTIVE

  42. RELATIONSHIP ATTRIBUTES CLIENT Insert sale date? commission? AGENT sells HOUSE

  43. UNARY/RECURSIVE RELATIONSHIPS relationships among entities within an eclass Employee Employee Unary 1:M Unary 1:1 Elder Jacobs Smith White Elder Jacobs Smith White

  44. ER ADVANCED SEMANTICS.. EXCLUSIVE: When an entity of an entity class belongs to one or other of the subtypes given (i.e. the eclass cannot be classified into more than one subtype) NON EXCLUSIVE: When an entity of an entity class belongs to more than one subtype given (i.e. the classification is non-exclusive) EXHAUSTIVE: When all possible subtypes have been enumerated [listed]. (i.e. there are no more subtypes) NON EXHAUSTIVE: When there are some subtypes which have not been enumerated (all subtypes are not listed). Note: A subtype is equivalent to a subclass

  45. ER ADVANCED SEMANTICS Mandatory 1 cardinality Optional 0, 1 cardinality 1,2..M cardinality Optional 0,1..M cardinality Note: You can ignore min/ Max Cardinalities unless you are specifically asked to represent them.

  46. ER ADVANCED SEMANTICS.. Person: Child Proj mgr: Proj (assume every proj has one proj manager and every project manager has at least one proj) Person: Spouse Faculty: Office (assume every faculty has one office and each office is occupied by at least one faculty) Student: Major (assume every student has one major and each major has at least one student)

  47. DISCUSSION • What are the sources of info. for database analysis? • What are the outputs of database analysis? • How can we distinguish between an attr. and an entity class? • When is an ER model complete? • Are foreign keys shown on ER? • What are examples of performance constraints? • What is the difference between domain and business constraints?

  48. DISCUSSION.. • Would the name of the organization be included in • an ER diagram? • Draw ER for: Customer places order with salesperson • Suppose a company has eclasses, R,S & T. If R and S, • T & S and R & T are related, draw an ER diagram. • Suppose you have an organization having entity • classes P, Q and R and S. Assuming P & Q; R & Q; • P&R; R&S are related, draw an ER diagram.

More Related