Objectives

1. Structuring System Requirements & System DesignSystem Design & Data and File DesignPresented by : Harminder Kaur Bhatia

2. Objectives • Data Analysis using • Data Modeling • Relational Modeling • Design Principles • Design Strategies • Design Components • Physical Data Design • Input Design • Output Design • Finalizing Design Specifications

3. Data Analysis • Objective • To gain a sound understanding of the data in use in the area of study and to provide a basis for the design of physical data structure which are: • Flexible • Easily maintainable • Unambiguous • Able to provide for differing access requirements • Subject to a minimum of data redundancy • Methods of Data Analysis • Entity Analysis (Conceptual Data Modeling) • Relational Analysis & Modeling

4. Conceptual Data Modeling • Representation of organizational data • Purpose is to show rules about the meaning and interrelationships among data • Entity-Relationship (E-R) diagrams are commonly used to show how data are organized • Main goal of conceptual data modeling is to create accurate E-R diagrams • Methods such as interviewing, questionnaires and JAD are used to collect information • Consistency must be maintained between process flow, decision logic and data modeling descriptions

5. Process of Conceptual Data Modeling • First step is to develop a data model for the system being replaced • Next, a new conceptual data model is built that includes all the requirements of the new system • In the design stage, the conceptual data model is translated into a physical design • Project repository links all design and data modeling steps performed during SDLC

6. Deliverables and Outcome • Primary deliverable is the entity-relationship diagram • There may be as many as 4 E-R diagrams produced and analyzed during conceptual data modeling • Covers just data needed in the project’s application • E-R diagram for system being replaced • An E-R diagram for the whole database from which the new application’s data are extracted • An E-R diagram for the whole database from which data for the application system being replaced is drawn

7. Sample conceptual data model diagram

8. Deliverables and Outcome • Second deliverable is a set of entries about data objects to be stored in repository or project dictionary • Repository links data, process and logic models of an information system • Data elements that are included in the DFD must appear in the data model and visa versa • Each data store in a process model must relate to business objects represented in the data model

9. Introduction to Entity-Relationship (E-R) Modeling • Notation uses three main constructs • Data entities • Relationships • Attributes • Entity-Relationship (E-R) Diagram • A detailed, logical representation of the entities, associations and data elements for an organization or business

10. Entity-Relationship (E-R) Modeling • Entity • A person, place, object, event or concept in the user environment about which the organization wishes to maintain data • Represented by a rectangle in E-R diagrams • Entity Type • A collection of entities that share common properties or characteristics • Attribute • A named property or characteristic of an entity that is of interest to an organization • Candidate keys and identifiers • Each entity type must have an attribute or set of attributes that distinguishes one instance from other instances of the same type • Candidate key • Attribute (or combination of attributes) that uniquely identifies each instance of an entity type

11. Entity-Relationship (E-R) Modeling • Relationship • An association between the instances of one or more entity types that is of interest to the organization • Association indicates that an event has occurred or that there is a natural link between entity types • Relationships are always labeled with verb phrases

12. Example relationships of different degrees

13. Cardinality • The number of instances of entity B that can be associated with each instance of entity A • Minimum Cardinality • The minimum number of instances of entity B that may be associated with each instance of entity A • Maximum Cardinality • The maximum number of instances of entity B that may be associated with each instance of entity A

14. The Role of CASE in Conceptual Data • CASE tools provide two important functions: • Maintain E-R diagrams as a visual depiction of structured data requirements • Link objects on E-R diagrams to corresponding descriptions in a repository

15. Database design - objectives • Discuss the role of designing databases in the analysis and design of an information system • Learn how to transform an Entity-Relationship (ER) Diagram into an equivalent set of well-structured relations • Learn how to merge normalized relations from separate user views into a consolidated set of well-structured relations • Explain choices of storage formats for database fields • Learn how to transform well-structured relations into efficient database tables • Discuss use of different types of file organizations to store database files • Discuss indexes and their purpose

16. Purpose of Database Design • Structure the data in stable structures, called normalized tables • Not likely to change over time • Minimal redundancy • Develop a logical database design that reflects actual data requirements • Develop a logical database design from which a physical database design can be developed • Translate a relational database model into a technical file and database design that balances several performance factors • Choose data storage technologies that will efficiently, accurately and securely process database activities

17. Process of Database Design • Logical Design • Based upon the conceptual data model • Four key steps • Develop a logical data model for each known user interface for the application using normalization principles • Combine normalized data requirements from all user interfaces into one consolidated logical database model • Translate the conceptual E-R data model for the application into normalized data requirements • Compare the consolidated logical database design with the translated E-R model and produce one final logical database model for the application

18. Process of Database Design • Physical Design • Based upon results of logical database design • Key decisions • Choosing storage format for each attribute from the logical database model • Grouping attributes from the logical database model into physical records • Arranging related records in secondary memory (hard disks and magnetic tapes) so that records can be stored, retrieved and updated rapidly • Selecting media and structures for storing data to make access more efficient

19. Deliverables and Outcomes • Logical database design must account for every data element on a system input or output • Normalized relations are the primary deliverable • Physical database design results in converting relations into files

20. Relational Database Model • Data represented as a set of related tables or relations • Relation • A named, two-dimensional table of data. Each relation consists of a set of named columns and an arbitrary number of unnamed rows • Relation – File or table • Tuple - Record or row of the table • Domain - data field or column of the table • Properties • Entries in cells are simple • Entries in columns are from the same set of values • Each row is unique • The sequence of columns can be interchanged without changing the meaning or use of the relation • The rows may be interchanged or stored in any sequence • Well-Structured Relation • A relation that contains a minimum amount of redundancy and allows users to insert, modify and delete the rows without errors or inconsistencies

21. Normalization • The process of converting complex data structures into simple, stable data structures • First Normal Form (1NF) • Each row is uniquely identified with a key • Second Normal Form (2NF) • Each nonprimary key attribute is identified by the whole key (called full functional dependency) • Third Normal Form (3NF) • Non-primary key attributes do not depend on each other (called transitive dependencies) • The result of normalization is that every non-primary key attribute depends upon the whole primary key

22. Normalization steps • Take data items from each source in un-normalized form • Convert to First Normal Form (FNF) • Covert to Second Normal Form (SNF) • Covert to Third Normal Form (TNF) • Test and Verify

23. Identify unique key Remove repeating groups Example : Un-normalized data Order-no. Order Date Supplier Code Supplier Name Item Code Item Name Qty Ordered Unit Price Order Amount FNF data Orders Order No. Order Date Supplier code Supplier Name Order Amount Order Details Order No. Item code Item name Qty Ordered Unit Price First Normal Form (FNF)

24. Second Normal form (2NF) • Second Normal Form (2NF) • A relation is in second normal form (2NF) if any of the following conditions apply: • The primary key consists of only one attribute • No nonprimary key attributes exist in the relation • Every nonprimary key attribute is functionally dependent on the full set of primary key attributes • In 2NF, we remove part-key dependencies • 2NF data • Orders • Order No. • Order Date • Supplier code • Supplier Name • Order Amount • Order Details • Order No. • Item code • Qty Ordered • Unit Price • Items Master • Item code • Item Name

25. Third Normal Form (3NF) • Third Normal Form (3NF) • A relation is in third normal form (3NF) if it is in second normal form (2NF) and there are no functional (transitive) dependencies between two (or more) nonprimary key attributes • Check dependencies between the fields within each relation • Remove calculated field • Create separate table and retain the key as foreign key • 3NF data • Orders • Order No. • Order Date • Supplier code • Suppliers • Supplier code • Supplier Name • Order Details • Order No. • Item code • Qty Ordered • Unit Price • Items Master • Item code • Item Name

26. Third Normal Form Test • Given the value for the key(s) is there only one possible value for each of the associated data items? • IS each data item directly dependent on the whole key? • Optimization • Merge relations which have the same keys • Look for synonyms e.g.. Clients, customers being different names for the same relation

27. Transforming E-R Diagrams into Relations • It is useful to transform the conceptual data model into a set of normalized relations • Steps • Identify entities from ER Model and create a relation for each entity • Include only the atomic components of the composite attributes • E.g. Name may comprise of First name, Middle name and Last name. In the tables include the elements First name, Middle name and Last name instead of the composite attribute Name • From among the candidate keys choose a primary key that uniquely identifies the entity type

28. Transforming E-R Diagrams into Relations • Represent Entities • Each regular entity is transformed into a relation • The identifier of the entity type becomes the primary key of the corresponding relation • The primary key must satisfy the following two conditions • The value of the key must uniquely identify every row in the relation • The key should be non-redundant

29. Physical File and Database Design • The following information is required • Normalized relations, including volume estimates • Definitions of each attribute • Descriptions of where and when data are used, entered, retrieved, deleted and updated (including frequencies) • Expectations or requirements for response time and data integrity • Descriptions of the technologies used for implementing the files and database

30. Designing Fields • Field • The smallest unit of named application data recognized by system software • Each attribute from each relation will be represented as one or more fields • Choosing data types • Data Type • A coding scheme recognized by system software for representing organizational data • Four objectives • Minimize storage space • Represent all possible values of the field • Improve data integrity of the field • Support all data manipulations desired on the field • Calculated fields • A field that can be derived from other database fields

31. Methods of Controlling Data Integrity • Default Value • A value a field will assume unless an explicit value is entered for that field • Range Control • Limits range of values which can be entered into field • Referential Integrity • An integrity constraint specifying that the value (or existence) of an attribute in one relation depends on the value (or existence) of the same attribute in another relation • Null Value • A special field value, distinct from 0, blank, or any other value, that indicates that the value for the field is missing or otherwise unknown

32. Designing Physical Tables • Relational database is a set of related tables • Physical Table • A named set of rows and columns that specifies the fields in each row of the table • Design Goals • Efficient use of secondary storage (disk space) • Disks are divided into units that can be read in one machine operation • Space is used most efficiently when the physical length of a table row divides close to evenly with storage unit • Efficient data processing • Data are most efficiently processed when stored next to each other in secondary memory • De-normalization • The process of splitting or combining normalized relations into physical tables based on affinity of use of rows and fields

33. Designing Physical Tables • Arranging Table Rows • Physical File • A named set of table rows stored in a contiguous section of secondary memory • Each table may be a physical file or whole database may be one file, depending on database management software utilized • File Organization • A technique for physically arranging the records of a file • Objectives for choosing file organization • Fast data retrieval • High throughput for processing transactions • Efficient use of storage space • Protection from failures or data loss • Minimizing need for reorganization • Accommodating growth • Security from unauthorized use

34. Designing Physical Tables • Types of File Organization • Sequential • The rows in the file are stored in sequence according to a primary key value • Updating and adding records may require rewriting the file • Deleting records results in wasted space • Indexed • The rows are stored either sequentially or non-sequentially and an index is created that allows software to locate individual rows • Index • A table used to determine the location of rows in a file that satisfy some condition • Secondary Index • Index based upon a combination of fields for which more than one row may have same combination of values

35. System Design - Objectives • System Design Definition • Aims of system Design • Categories of Design Methodologies • Features of a good design • Design Constraints • Activities in the System Design

36. System Design - Definition • The iterative process of taking a logical model of a system together with a strongly stated set of objectives for that system and producing specification of a physical system that will meet those objectives. • The requirements analysis and specifications describes ‘what’ the system is required to do and the Design describe ‘how’ it will do that.

37. Aims of System Design • Design input and output • Design user-computer interface • Design data structures • Design processes • Make decisions about hardware and software

38. Categories of Design Methodology • Top down functional design • Designed from functional view-point • Define high level view and refine it iteratively • Object oriented design • View system as a collection of objects with messages passed between them • Each object has a set of associated operations • Data Driven design • Design is derived from an analysis of the data flows and data entities

39. Desirable features of ‘good’ quality design • Functional • Satisfies user requirements • Efficient • Efficient performance in terms of response time and throughput • Flexible • Able to accommodate growth, diversity and change in organization • Portable • Hardware and software independent as far as possible • Usable • Easy to learn and operate

40. Design constraints • Budget • Time (Delivery Schedule) • Integration with other systems • Skills • Standards

41. Activities in System Design • Input-Output Design • Methods of data capture and reporting • Screens and reports similar to the existing documents / forms / reports • Specify format and layout • Dialog design • User Interface design • Consider user interaction, possible errors, help, prompts, screen content and layout • Data Design • Design of files and databases • Access requirements, file organization • Process Design • Structure chart • Program flowchart / pseudocodes • System Controls • Data integrity, recovery, data backup etc.

42. Activities in System Design • Technical considerations • Hardware and software to be used • Design documentation • Design Verification and validation • Review • Technical • Economic • Operational feasibility

43. Input-output Design Dialog Design Data Design Process Design Input Document and Report Layouts Menu Trees, Screen layouts File/ database table structures Structure charts and action diagrams, flowcharts Deliverables

44. Selecting the Best Alternative Design Strategy • Two basic steps • Generate a comprehensive set of alternative design strategies • Select the one design strategy that is most likely to result in the desired information system • Process • Divide requirements into different sets of capabilities • Enumerate different potential implementation environments that could be used to deliver the different sets of capabilities • Propose different ways to source or acquire the various sets of capabilities for the different implementation environments

45. Selecting the Best Alternative Design Strategy • Deliverables • At least three substantially different system design strategies for building the replacement information system • A design strategy judged most likely to lead to the most desirable information system • A Baseline Project Plan (BPP) for turning the most likely design strategy into a working information system

46. Issues to Consider in Generating Alternatives • Sources of Software • Hardware manufacturers • Packaged software producers • Custom software producers • Enterprise solution software • Application Service Providers • In-house development

47. Existing Platform Lower costs Information system staff is familiar with operation and maintenance Increased odds of successfully integrating system with existing applications No added costs of converting old systems to new platform or transferring data New Hardware and System Software Some software components will only run on new platform Developing system for new platform gives organization opportunity to upgrade technology holdings New requirements may allow organization to radically change its computing operations Hardware and Software Issues

48. Designing Forms & reports (I/O Design) • Explain the process of designing forms and reports and the deliverables for their creation • Discuss general guidelines for formatting forms and reports • Use color and know when color improves the usability of information • Learn how to effectively format text, tables and lists • Explain how to assess usability

49. Designing Forms and Reports • System inputs and outputs are produced at the end of the analysis phase • Precise appearance was not defined during this phase • Forms and reports are integrally related to DFD and E-R diagrams • Definitions • Form • A business document that contains some predefined data and may include some areas where additional data are to be filled in • An instance of a form is typically based on one database record • Report • A business document that contains only predefined data • A passive document for reading or viewing data • Typically contains data from many database records or transactions

50. The Process of Designing Forms and Reports • User-focused activity • Follows a prototyping approach • Requirements determination • Who will use the form or report? • What is the purpose of the form or report? • When is the report needed or used? • Where does the form or report need to be delivered and used? • How many people need to use or view the form or report?