Chapter 6

1. Data Design Chapter 6

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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

11. 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

12. Data Design Concepts • Overview of Database Systems

13. 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

14. 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

15. 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

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

17. 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

18. 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

19. 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 one 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

20. 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

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

22. Web-Based Database Design • Internet Terminology • Extranet • Protocols • Web-centric • Clients • Servers

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

24. 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

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

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

27. 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

28. 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)

29. Data Relationships • Entity-Relationship Diagrams

30. Normalization • 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

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

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

33. Normalization • First Normal Form • A table is in first normal form (1NF) 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

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

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

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

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

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

39. 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

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

41. 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 databaseresembles a hierarchical design, but provides somewhat more flexibility

42. Database Models • Relational Databases • The relational model was introduced during the 1970s 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

43. 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

44. 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

45. 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

46. 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

47. Data Storage • Data Storage Formats • Binary digits • Bit • Byte • EBCDIC and ASCII • Unicode

48. 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

49. 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

50. 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