Systems Analysis Design 5 th Edition Chapter 6

Systems Analysis & Design 5 th Edition Chapter 6 ● Data Design

Phase Description ● Systems Design is the third of five phases in the systems development life cycle (SDLC) ● Now you are ready to begin the physical design of the system that will meet the specifications described in the system requirements document ● Systems design tasks include data design, user interface design, and system architecture 2

Chapter Objectives ● Explain data design concepts and data structures ● Describe file processing systems and various types of files ● Understand database systems and define the components of a database management system (DBMS) ● Describe Web-based data design and characteristics 3

Chapter Objectives ● Explain data design terminology, including entities, fields, common fields, records, files, tables, and key fields ● Describe data relationships, draw an entity-relationship diagram, define cardinality and use cardinality notation ● Explain the concept of normalization ● Understand the steps in database design 4

Chapter Objectives ● Describe hierarchical, network, relational, and object-oriented database models ● Explain data warehousing/data mining ● Differentiate between logical and physical storage and records ● Explain data control measures 5

Introduction ● You will develop a physical plan for data organization, storage, and retrieval ● Begins with a review of data design concepts and terminology, then discusses file-based systems and database systems, including Web-based databases ● Concludes with a discussion of data mining, data warehousing, physical design issues, logical and physical records, data storage formats, and data controls 6

Data Design Concepts ● Before constructing an information system, a systems analyst must understand basic design concepts, including data structures and the characteristics of file-oriented and database systems, including Web-based database design 7

Data Design Concepts ● Data Structures –A file or table contains data about people, places or events that interact with the system –File-oriented system –File processing –Database system 8

Data Design Concepts ● Overview of File Processing –Some companies use file processing to handle large volumes of structured data –Although less common today, file processing can be more efficient and cost less than a DBMS in certain situations 9

Data Design Concepts ● Overview of File Processing – Potential problems • Data redundancy • Data integrity • Rigid data structure – Uses various types of files • • • Master file Table file Transaction file Work file – scratch file Security file History file 10

Data Design Concepts ● Overview of Database Systems – A properly designed database system offers a solution to the problems of file processing – Provides an overall framework that avoids data redundancy and supports a real-time, dynamic environment 11

Data Design Concepts ● Overview of Database Systems 12

Data Design Concepts ● Overview of Database Systems – A database management system (DBMS) is a collection of tools, features, and interfaces that enables users to add, update, manage, access, and analyze the contents of a database – The main advantage of a DBMS is that it offers timely, interactive, and flexible data access 13

Data Design Concepts ● Overview of Database Systems – Advantages • • • Scalability Better support for client/server systems Economy of scale Flexible data sharing Enterprise-wide application – database administrator (DBA) Stronger standards Controlled redundancy Better security Increased programmer productivity Data independence 14

Data Design Concepts ● Database Tradeoffs – Because DBMSs are powerful, they require more expensive hardware, software, and data networks capable of supporting a multiuser environment – More complex than a file processing system – Procedures for security, backup, and recovery are more complicated and critical 15

DBMS Components ● A DBMS provides an interface between a database and users who need to access the data 16

DBMS Components ● Interfaces for Users, Database Administrators, and Related Systems – Users • Query language • Query by example (QBE) • SQL (structured query language) – Database Administrators • A DBA is responsible for DBMS management and support 17

DBMS Components ● Interfaces for Users, Database Administrators, and Related Systems – Related information systems • A DBMS can support several related information systems that provide input to, and require specific data from, the DBMS ● Data Manipulation Language – A data manipulation language (DML) controls database operations, including storing, retrieving, updating, and deleting data 18

DBMS Components ● Schema – The complete definition of a database, including descriptions of all fields, tables, and relationships, is called a schema – You also can define or more subschemas ● Physical Data Repository – The data dictionary is transformed into a physical data repository, which also contains the schema and subschemas – The physical repository might be centralized, or distributed at several locations 19

Web-Based Database Design ● Characteristics of Web-Based Design – In a Web-based design, the Internet serves as the front end, or interface for the database management system. Internet technology provides enormous power and flexibility – Web-based systems are popular because they offer ease of access, cost-effectiveness, and worldwide connectivity 20

Web-Based Database Design ● Internet Terminology – – – Web browser Web page HTML – Hypertext Markup Language Web server Web site Intranet 21

Web-Based Database Design ● Internet Terminology – – – Extranet Protocols Web-centric Clients Servers 22

Web-Based Database Design ● Connecting a Database to the Web – Database must be connected to the Internet or intranet • Middleware • Macromedia’s Cold. Fusion 23

Web-Based Database Design ● Data Security – Web-based data must be totally secure, yet easily accessible to authorized users – To achieve this goal, well-designed systems provide security at three levels: the database itself, the Web server, and the telecommunication links that connect the components of the system 24

Data Design Terminology ● Definitions –Entity –Table or file –Field • Attribute - Common field –Record • Tuple 25

Data Design Terminology ● Key Fields – Primary key • • Combination key Composite key Concatenated key Multi-valued key – Candidate key • Nonkey field – Foreign key – Secondary key 26

Data Design Terminology ● Referential Integrity – Validity checks can help avoid data input errors – In a relational database, referential integrity means that a foreign key value cannot be entered in one table unless it matches an existing primary key in another table 27

Data Relationships ● A relationship is a logical link between entities based on how they interact ● Entity-Relationship Diagrams – – One-to-one relationship (1: 1) One-to-many relationship (1: M) Many-to-many relationship (M: N) Cardinality • Cardinality notation • Crow’s foot notation • Unified Modeling Language (UML) 28

Data Relationships ● Entity-Relationship Diagrams 29

Normalization ● Table design ● Involves four stages: unnormalized design, first normal form, second normal form, and third normal form ● Most business-related databases must be designed in third normal form 30

Normalization ● Standard Notation Format – Designing tables is easier if you use a standard notation format to show a table’s structure, fields, and primary key Example: NAME (FIELD 1, FIELD 2, FIELD 3) 31

Normalization ● Repeating Groups and Unnormalized Design –Repeating group • Often occur in manual documents prepared by users –Unnormalized design 32

Normalization ● First Normal Form –A table is in first normal form (1 NF) if it does not contain a repeating group –To convert, you must expand the table’s primary key to include the primary key of the repeating group 33

Normalization ● Second Normal Form – To understand second normal form (2 NF), you must understand the concept of functional dependence – Field X is functionally dependent on field Y if the value of field X depends on the value of field Y 34

Normalization ● Second Normal Form – A standard process exists for converting a table from 1 NF to 2 NF 1. Create and name a separate table for each field in the existing primary key 2. Create a new table for each possible combination of the original primary key fields 3. Study the three tables and place each field with its appropriate primary key 35

Normalization ● Second Normal Form – Four kinds of problems are found with 1 NF description that do not exist with 2 NF • Consider the work necessary to change a particular product’s description • 1 NF tables can contain inconsistent data • Adding a new product is a problem • Deleting a product is a problem 36

Normalization ● Third Normal Form – 3 NF design avoids redundancy and data integrity problems that still can exist in 2 NF designs –A table design is in third normal form (3 NF) if it is in 2 NF and if no nonkey field is dependent on another nonkey field 37

Normalization ● Third Normal Form – To convert the table to 3 NF, you must remove all fields from the 2 NF table that depend on another nonkey field and place them in a new table that uses the nonkey field as a primary key 38

Normalization ● A Normalization Example – To show the normalization process, consider the familiar situation in Figure 6 -24 which might depict several entities in a school advising system: ADVISOR, COURSE, and STUDENT – The relationships among the three entities are shown in the ERD in Figure 6 -25 39

Steps in Database Design 1. Create the initial ERD 2. Assign all data elements to entities 3. Create 3 NF designs for all tables, taking care to identify all primary, secondary, and foreign keys 4. Verify all data dictionary entries ● After creating your final ERD and normalized table designs, you can transform them into a database 40

Database Models ● Hierarchical and Network Databases – In a hierarchical database, data is organized like a family tree or organization chart, with branches representing parent records and child records – A network database resembles a hierarchical design, but provides somewhat more flexibility 41

Database Models ● Relational Databases – The relational model was introduced during the 1970 s and became popular because it was flexible and powerful – Because all the tables are linked, a user can request data that meets specific conditions – New entities and attributes can be added at any time without restructuring the entire database 42

Database Models ● Object-Oriented Databases –Many systems developers are using object-oriented database (OODB) design as a natural extension of the object-oriented analysis process • Object Data Standard • Object Database Management Group (ODMG) • Each object has a unique object identifier 43

Data Storage ● Data Warehousing – Data warehouse - dimensions – Without a data warehouse it would be difficult for a user to extract data that spans several information systems and time frames – Allows users to retrieve and analyze the data easily 44

Data Storage ● Data Mining – Works best when you have clear, measurable goals • • • Increase average pages viewed per session Increase number of referred customers Reduce clicks to close Increase checkouts per visit Increase average profit per checkout 45

Data Storage ● Logical and Physical Storage – Logical storage • • As seen through a user’s eyes Characters Date element or data item Logical record – Physical storage • Hardware-related • Physical record or block • Blocking factor 46

Data Storage ● Data Storage Formats – – – Binary digits Bit Byte EBCDIC and ASCII Unicode 47

Data Storage ● Data Storage Formats – Binary • • Binary storage format Integer format Long integer format Other binary formats exist for efficient storage of exceedingly long numbers 48

Data Storage ● Selecting a Data Storage Format – In many cases, a user can select a specific data storage format – For example, when using Microsoft Office, you can store documents, spreadsheets, and databases in Unicode-compatible form by using the font called Arial Unicode MS – Selecting the right data storage format depends on the situation 49

Data Storage ● Date Fields – Most date formats now are based on the model established by the International Organization for Standardization (ISO) – Can be sorted easily and used in comparisons – Absolute date – Best method for storing date fields depends on how the specific date will be printed, displayed or used in a calculation 50

Data Control ● File and database control must include all measures necessary to ensure that data storage is correct, complete, and secure ● A well-designed DBMS must provide builtin control and security features, including subschemas, passwords, encryption, audit trail files, and backup and recovery procedures to maintain data 51

Data Control ● ● ● ● User ID Password Backup Recovery procedures Audit log files Audit fields Encryption 52

Chapter Summary ● Files and tables contain data about people, places, things, or events that affect the information system ● DBMS designs are more powerful and flexible than traditional file-oriented systems 53

Chapter Summary ● Data design tasks include creating an initial ERD; assigning data elements to an entity; normalizing all table designs; and completing the data dictionary entries for files, records, and data elements ● The four basic database models are hierarchical, network, relational, and object -oriented 54

Chapter Summary ● Logical storage is information seen through a user’s eyes, regardless of how or where that information actually is organized or stored ● Physical storage is hardware-related and involves reading and writing blocks of binary data to physical media ● File and database control measures include limiting access to the data, data encryption, backup/recovery procedures, audit-trail files, and internal audit fields 55

Systems Analysis & Design 5 th Edition Chapter 6 Complete