1 / 76

Yunsheng Liu

Ⅶ. Database Design. Yunsheng Liu. College of Software, HUST 2008.11. 7.1 Introduction. 1. Generic Objective Data characteristics, application properties and DBMS features are integrated into a DBS by effectively using alternative DB

ariane
Download Presentation

Yunsheng Liu

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 Design Yunsheng Liu College of Software, HUST 2008.11

  2. 7.1 Introduction 1. Generic Objective Data characteristics, application properties and DBMS features are integrated into a DBS by effectively using alternative DB design approaches, techniques and tools 7.1.1 Problems in DB Design Yunsheng Liu

  3. 2 7.1.1 Problems in DB Design 2. The Main Tasks • Determination and representation of requirements • Translation of the requirements into an effective DB structure • Implementation of the DB structure on a particular computer system • Consideration on the implementation in evolution Yunsheng Liu

  4. Information changes • Process changes • Performance changes • System SW/HW changes • Central control of data • Sharing data • Data availability • Data non-redundancy 7.1.1 Problems in DB Design 3. Design principles 1). Matching the application properties and data characteristics with the DBMS features 2). Cost/performance tradeoff 3). Data integration and structure flexibility Yunsheng Liu

  5. 7.1.2 Database Design Methodologies 1. Concept A methodology is a combination of an organizational framework/serial steps for successive DB structure development projects and a set of techniques and tools used in the framework /steps sequentially. Yunsheng Liu

  6. 7.1.2 Database Design Methodologies 2. Classification • Function-based Methodology • Data-based Methodology • Object-Oriented Methodology 3. Criteria • availability, • generality and flexibility, • reproducibility Yunsheng Liu

  7. System Environment properties Methodologies: Tech. Toots. Constraints DBMS OS Application Environment Infor. Requi. DB struc. DBS Database Design Proc. Requi. Appl. guide Sys. spec. Op Requi. Control procedure Sys. impl. 7.1.3 Generic DB Design Process 1. Overview of DB Design Yunsheng Liu

  8. Data Relation Theory Data Modeling Tech. DBMS Features Data Organizing Access Analysis Modeling Design Implem. User’s Requirements Organization’s Info. Stru. DB Data Model DB Structure Physical DB 2. Technology Flow 7.1.3 Generic DB Design Process Yunsheng Liu

  9. 1). Requirements formulation: • Information • Function/Process • Operation 2).Constraints in aspects: • Performance, • System environment, • Techniques availability • Organization (politic, bureaucratic etc) 7.1.3 Generic DB Design Process 3. Considerations 3). Tech., tools and methods, and evolution Yunsheng Liu

  10. Appli. Envi Properties Appl. Analy& Requi. Determi. Conceptual Modeling Techniques Requi. Spec. Conceptual Design Conceptual (E-R) Model Design Tools, Technologies DBMS Features Implem. Design Logical DB Stru., Appl. Spec. Physical Design OS, HW Support Running DBS  Stored DB stru. Access Methods. Implementation 7.1.4 The DB Design Steps Yunsheng Liu

  11. 7.2 Requirements Analysis & Formulation • Data requirements • Data in ISP —Information Structure Perspective, - describing all the natural and conceptual data and the relationships of them in the DB, not just bounded to any processes or applications - providing flexibility and adaptability • Data in UP —Usage Perspective - describing all the data and the relationships used in applications, current and estimated known future applications - providing efficiency 7.2.1 Types of Requirements Yunsheng Liu

  12. 7.2.1 Types of Requirements • Functional/Processing requirements • Operational requirements - representing goal related to the running environment and performance • Consistency and security constraints • Response time constraints • Recovery time constraints • Number of applications supported in simultaneity Yunsheng Liu

  13. Appl. Enviro. Analysis Metadata Collection Appl. Funct. Analysis Enterprise Modeling Requirements Specification 7.2.2 Tasks and Goals of Requirements Analysis 1. The Main Tasks Yunsheng Liu

  14. 7.2.2 Tasks and Goals of Requirements Analysis 2. The Overall Goals - Analyzing info. structures and decision process to understand: (1). The organization’s mechanisms (2). The scope of the system to be designed (3). User views of data (business dept.) and data elements in the views (4). Relationships among the elements (5). Characteristics and the primary keys of the data Yunsheng Liu

  15. 7.2.2 Tasks and Goals of Requirements Analysis (6). Data processing functions and properties (business activities or applications) (7). Relationships between the functions and the data (8). Operational requirements: 2. The Overall Goals • Integrity, Consistency and Security constraints • Query modes • Response time requests • … Yunsheng Liu

  16. 7.2.3 Application Environment Analysis • Review the previous developments • Analyze existing reports, files, documents, display, etc. • Questionnaire • Interview with all kinds of key users • Look into work fields The Approaches Yunsheng Liu

  17. 7.2.4 Metadata Collection • Metadata concept • Static metadata - The information of data structure, i.e. definitions and descriptions of data • Naming • Constructions • Formats • Types and lengths • Dynamic metadata Yunsheng Liu

  18. Behavior • Usage modes: interactive or preprogramming • Operation types and frequencies • Interactions among data • Message switches etc. • Operational requirements • Integrity, Consistency and Security Constraints • Query modes • Response time constraints • DB changes in volume and structure 7.2.4 Metadata Collection Two aspects of dynamic metadata Yunsheng Liu

  19. 7.2.5 Application Analysis 1.Objectives to determine a variety of applications with their data, and data usages in the applications 2.TasksAnalysis Hierarchically to decompose the applications into function modules . • Be performed within one area of business • Be authorized as a whole at the same level • Be performed as a whole, and no different parts of a task can be performed by different callers or triggering conditions, or within different time slice • Utilize the same set of data uniformly Yunsheng Liu

  20. Data Analysis —Hierarchically to decompose the data • according to the applications decompositions • - Data elements definitions: names, types, lengths, • pictures etc. DD • - Documenting data structures and formats • - Relationships between data E-R-A model • Identifying —the relationships between the processes • and data objects • - Operation types, frequencies and data volumes 7.2.5 Application Analysis Yunsheng Liu

  21. 7.2.5 Application Analysis • Integrating —respectively, the decomposed data and functions in the different parts are integrated into a completed database structure and an application systematic module structure ● Removing the inconsistencies, such as the same name with different meanings and the same meaning with different names ● Removing the redundancies Yunsheng Liu

  22. 7.2.6 Enterprise Modeling • Describing the organizational model of the enterprise—Indicating the system scope • Representing a Syst./info. Flows Chart (SFC) • Developing a logical model of the system • Physical procedures  logical processes • Human being actions  abstract processes • Particular implementations of an algorithm  the description/representation of the algorithm • Operational objectives  computations for achieving the objectives Yunsheng Liu

  23. Hierarchy 7.2.7 System/Requirements Specification 1. Specifying the logical model of the system with: • DFD • DD • Task IPO chart • Data usage matrix 2. DFD Data Flow Diagram Yunsheng Liu

  24. e D2 P2 b printer h f source p1 a P3 Level 1 c d g D1 h P3.2 f D2 x Level 2 z printer P3.1 P3.3 d g D1 y A Hierarchical DFD Sample Yunsheng Liu

  25. 3. DDData Dictionary A collection of the information on definitions, structures and usages of data elements in an DB The contents: typically 1). General: name, aliases, or synonyms, description 2). Format: type, length, domain, format or picture 3). Structure: parent/subsidiary, location (file, record..) 4). Usage: range of values, frequency of uses, kind ( I/O, global, local) 5). Control: origin, users, authorizations (C, Q, D, I…) conditions of use( key, Consistency constraints…), Yunsheng Liu

  26. An Example - for defining or finding a data item, you may need to use more than one entry in the DD. BATCH_STATUS_FILE = {BATCH_JOB_STATUS} BATCH_JOB_STATUS = JOB_ID_NUMBER + JOB_STATUS JOB_ID_NUMBER= JOB_ID + JOB_NUMBER JOB_ID = Char(2), JOB_NUMBER = Number(4) : SALARY = Real(5.2) MEETING_TIME= YY:MM:DD + HH:MN:SS Yunsheng Liu

  27. IPO CHART-iD SYSTEM SUBSYSTEM MODUL DESIGNER AUTHORIZER DATE Called by: Caller: Input: Output: Process: Local data: Notes: 4. Application spec.Input-Process-Output Chart Yunsheng Liu

  28. D1D2 DjDn T1I5, U, Q12, Not  Q3, U2 T2C, TiAll C, I2, D, U6, Q15 Tm   5. Task-Data Usage Matrix • C, I, D, U, Q — Create, Insert, Delete, Update • Q12 —12 times performing Q per time unit Yunsheng Liu

  29. Create Query Insert Update Deiete D1V1c  N1cV1i  N1ii D2 DiVic  Nic Viu  Niu Dm   6. Data-Operation Relationship Matrix Yunsheng Liu

  30. 7.3 Conceptual Design Overview 7.3.1 Introduction 1. Concept - a process to develop a conceptual DB structure, which is independent of system specifics, with modeling user views and then integrating them Yunsheng Liu

  31. 7.3.1 Introduction 2. The Main Tasks — analyzing and modeling data based on the data requirements  conceptual data modeling 3.The Objective — an abstract data representation that is comprehensible to both users and designers  E-R Yunsheng Liu

  32. 7.3.1 Introduction 4. Design Considerations • Management perspective —management view of data • Operation and transaction perspective —processing view of data • Structure perspective —intra- and inter- structures of data • Event perspective —requirements on time and scheduling of applications Yunsheng Liu

  33. 7.3.2 Approaches and Techniques • Modeling management user views • Modeling operation/transaction user views • Modeling hierarchical structures and relationships among data elements • Modeling application events—“Wh”：when, what( tran./op.), which( data), 1. Entity Analysis—top-down design 1).Modeling the user’s viewsof data as follows: Yunsheng Liu

  34. Requirements Entities formulation Classifying items Composing entities Entity-Attr. analysis Relationship creation Formulating relationships Attributes Synthesis Entity Modeling Graphical representation 7.3.2 Approaches and Techniques 2). Modeling entities - Formulating E/R/A 3). Consolidating the user’s views into an integrated conceptual view 2. Attributes Synthesis —bottom-up - Classifying items: Identity (E, R)  Description - FD sets Yunsheng Liu

  35. 1. Steps Data Requi. Spec. Conceptual Model Development Conceptual Design Review Modeling User Views Integrating The Views 2. User views modeling - Based on individual user perspectives: • Identify data elements • Identify data groups • Form relationships among the data elements, intra-groups and inter-groups 7.3.3 Conceptual Design Steps Yunsheng Liu

  36. 3. Views integration - Synthesizing the views into global single structure • Remove redundancies • Coordinate inconsistencies • Conceptual model components • Logical data constructs—intra-structures of entities • Logical data relationships—inter-structures of entities • Logical access map—logical access patches of appli. 5. Conceptual review —for correctness, consistency, completeness, un-redundancy 7.3.3 Conceptual Design Steps Yunsheng Liu

  37. Real world Semantic Data Models DB Data Models Computer world 7.4 Semantic Data Analysis and Modeling 7.4.1 Introduction 1. What 2. The design process: • Semantic modeling  • Rrelational analysis  • Logical conversion (to target DM) Yunsheng Liu

  38. Products - The relationship: Is-Part-of P-name P-factory P-type F-name F-address F-phone -The relationship: Is-Instance-of P10001 P21888 TV T-Shirt LG XYZ BT-49’ Playboy Products 7.4.2 Data Abstractions 1. Aggregation 2. Classification Yunsheng Liu

  39. 3. Generalization Products - The relationship: Is-a  Machine Electric Fabric  Computer TV Refrigerator 4. Association Products - The relationship: Is-Member-of  Substandard products Qualified products Waste products Excellent products Standard products 7.4.2 Data Abstractions Yunsheng Liu

  40. Suppliers Supply Items Suppliers supply supplier item item supplier Items 7.4.3 E-R Modeling 1. Representations of objects in a real world  Sometimes, an object in real-world may be represented an entity, a relationship, or even an attributes, depending on designer’s views. Example: Yunsheng Liu

  41. 7.4.3 E-R Modeling 2. E/R/A selection • “noun, verb and adjective” principle • Practical rules: • Identifier  entity type • Property if “A is a property of B”, B is an entity type and A is an attribute of B • Event/action  the subjects and objects in an event or action corresponds to entity types, and its behavior relates to a relationship type Yunsheng Liu

  42. employ award Employee Employee Enterprise Honor appoint Employee 7.4.3 E-R Modeling 3. Relationships Analysis 1). Memberships • Optional - General • Dependent - Weak entity • Conditional - Normalization! manager 2). Identifying —both of the two E’s keys Yunsheng Liu

  43. Supplier lecture Teacher Teacher Course Course lecture supply Part Supplier Project Supply Part Project 7.4.3 E-R Modeling 5. N-ary relationships: replaced with a “relationship entity” 4. Multi-value relationships: - How to identify?  Create a dependent entity type or give a special Id Yunsheng Liu

  44. 7.4.4 Conversion from E-RM to RM 1. General rule: E relation, R relation, A attribute, Id key 2. Conversions of nondeterministic entity types - An entity type with an attribute of repeating group - The repeating group attribute must be converted into a dependent entity for normalization 3. Minimizing the number of relations Yunsheng Liu

  45. 7.4.4 Conversion from E-RM to RM • Relations which have a common key should be merged into a single relation • A relation corresponding to an 1:1 relationship type should be merged into one of the two entity relations • A relation corresponding to an 1:M relationship type should be merged into the relation corresponding to the entity at M side of the relationship - Because there exist superfluous attributes in the key, and after removing the superfluous attributes from the key, the relationship relation has the same key as one of the entity at M side of the relationship Yunsheng Liu

  46. 7.5 Relational Analysis 7.5.1 Introduction 1. What constitute a bad DB design? • Redundancies • Null values • Operation anomalies 2. How to avoid a bad DB design? Normalization How to normalize? Decompositions 3. Normal Forms NF2  1NF  2NF 3NF  BCNF Yunsheng Liu

  47. 7.5.2 Functional Dependencies 1. FD Concepts Def.: Let R(A) be a relation and X, YA be two attribute sets of R(A). If for any given X-value in R(A), there exists only one corresponding Y-value in R(A), then we say that Xfunctionally determines Y in R(A), notated by XY. And XY is called a functional dependence (FD) of R(A). ●X: determinator of the FD ● Y: dependent of the FD, respectively. Yunsheng Liu

  48. P-NAME P-BUDGET 7.5.2 Functional Dependencies • Example EMPLOYEE# WORK-TIME PROJECT# Yunsheng Liu

  49. 7.5.2 Functional Dependencies 2. An alternative definition of a key of a relation Def.: Let R(A) be a relation and XA be an attribute set of R(A). X is referred as a key of R(A) if the following two predicates are held: ●Ai∈A ( X Ai ) ●∄X’ X ( Ai∈A(X’Ai）) Yunsheng Liu

  50. EMPLOYEE# PROJECT# 7.5.2 Functional Dependencies 3. Full FD For R(A), if XY and ∀X’⊂X ( X’↛Y ), then XY is called a full FD. • Example: P-NAME WORK-TIME P-BUDGET Yunsheng Liu

More Related