CHAPTER 9 DESIGNING THE DATA

1. CHAPTER 9DESIGNING THE DATA

2. Chapter Objectives • Explain file-oriented systems and how they differ from database management systems • 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

3. Chapter Objectives • Explain the concept of normalization • Explain the importance of codes and describe various coding schemes • Explain data warehousing and data mining • Differentiate between logical and physical storage and records • Explain data control measures

4. 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 storage and access, including strategic tools such as data warehousing and data mining, physical design issues, logical and physical records, data storage formats, and data controls

5. Data Design Concepts • Before constructing an information system, a system analyst must understand basic data design concepts, including data structures and the characteristics of file-oriented and database management systems, including Web-based database design. • Data Structures • Is a framework for organizing, storing, and managing data • Consists of files or tables • Each file or table contains data about people, places, things or events that interact with the information system • Information system is called: • File-oriented system • Database mangement system

6. Data Design Concepts • File oriented system • Sometimes called file processing system • Stores data in one or more separate files • The same data is stored in more than one location • Redundancy problem, thus reduces efficiency and data quality.

7. File oriented system

8. Data Design Concepts • Database Management System • All table are connected by common fields • A common field that connects two tables is said to link, join or relate the tables • Also called relational database/relational model Refer pg 393

9. Data Design Concepts • Overview of File Processing • File processing can be efficient and cost-effective in certain situations • Potential problems • Data redundancy • Data integrity • Rigid data structure

10. A credit card company might use a file processing system to post daily sales transaction from a TRANSACTION file to the CUSTOMER file.

11. Data Design Concepts • File oriented information system can contain Various types of files • Master file-stores relatively permanent data about an entity • Table file-contains reference data used by the information system • Transaction file-stores record that contain day-to-day business and operational data • Work file-temporary file created by an information system for single task • Security file-created and saved for backup and recovery purposes • History file-file created for archiving purposes

12. Data Design Concepts • The Evolution from File Systems to 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 • Some Advantages • Scalability-a system can be expanded, modified or downsized easily to meet the rapidly changing needs of a business enterprise • Better support for client/server systems • Economy of scale-efficiency of high volume processing on larger computers • Flexible data sharing • Enterprise-wide application – database administrator (DBA)

13. Controlled redundancy • Stronger standards • Data independence • Better security • Increased programmer productivity In this example, a sales database can support four separate business systems. Inventory System SALES DATABASE Order System Accounting System Production System

14. DBMS Components • DBMS provides an interfaces for Users, Database Administrators, and Related Systems • When users, database administrator and related information systems request data and services, he DBMS processes the request, manipulates he data and provides a response

15. Users • To access stored data • Query language • Query by example (QBE) • SQL (structured query language) • Database Administrators • A DBA is responsible for DBMS management and support -Related information systems • A DBMS can support several related information systems that provide input to, and require specific data from, the DBMS • No human intervention is required for two-way communication

16. Using QBE

17. DBMS Components • Data Manipulation Language • A data manipulation language (DML) controls database operations, including storing, retrieving, updating, and deleting data • 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 • ODBC – open database connectivity • JDBC – Java database connectivity

18. Web-Based Database Design • Characteristics of Web-Based Design

19. Web-Based Database Design • Internet Terminology • Web browser-application that enables the user to navigate or display the web pages • Web page-text document written in HTML • HTML (Hypertext Markup Language) • Tags-specify how the text and visual elements will be displayed in Web browser • Web server-computer that receives request and makes Web pages available to users • Web site-

20. Web-Based Database Design • Internet Terminology • Intranet-private, company-owned network that provide access to internal users • Extranet-extension of a company intranet that allows access by external users • Protocols-data transmission standards • Web-centric • Clients-workstation that users interact with • Servers-computers that supply data, processing and services to the client workstations

21. Web-Based Database Design • Connecting a Database to the Web • Database must be connected to the Internet or intranet • Use Middleware • Software that integrates different applications and allows them to exchange data • Adobe ColdFusion • Data Security • 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

22. Data Design Terminology • Definitions • Entity • Table or file • Field • Record or tuple • Key field

23. Data Design Terminology • Key Fields • Primary key • Candidate key • Foreign key • Secondary key

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

25. Entity-Relationship Diagrams • Drawing an ERD • The first step is to list the entities that you identified during the fact-finding process and to consider the nature of the relationships that link them • A popular method is to represent entities as rectangles and relationships as diamond shapes

26. Entity-Relationship Diagrams • Types of Relationships • Three types of relationships can exist between entities • One-to-one relationship (1:1) • One-to-many relationship (1:M) • Many-to-many relationship (M:N)

27. Entity-Relationship Diagrams • Cardinality • Cardinality describes the numeric relationship between two entities and shows how instances of one entity relate to instances of another entity • Cardinality notation-special symbol to represent the relationship • Crow’s foot notation • Unified Modeling Language (UML) • Now that you understand database elements and their relationships, you can start designing tables

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

29. Normalization • Repeating Groups and Unnormalized Design • Repeating groups • A set of one or more fields that a occur any number of times in a single record, with each occurrence having different values • Often occur in manual documents prepared by users • Unnormalized-table design that contains a repeating group • Enclose the repeating group of fields within a second set of parentheses

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

31. Normalization • Second Normal Form • A table design is in second normal form (2NF) if it is in 1NF and if all fields that are not part of the primary key are functionally dependent on the entire primary key • A standard process exists for converting a table from 1NF to 2NF • The objective is to break the original table into two or more new tables and reassign the fields so that each nonkey field will depend on the entire primary key in its table

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

33. Normalization • A Normalization Example

34. Using Codes During Data Design • Codes is a set f letters or numbers that represents a data item • Codes can be used to simplify output, input and data formats. • Overview of Codes • Because codes often are used to represent data, you encounter them constantly in your everyday life • They save storage space and costs, reduce data transmission time, and decrease data entry time • Can reduce data input errors

35. Using Codes During Data Design • Types of Codes • Sequence codes • Block sequence codes • Alphabetic codes • Significant digit codes • Derivation codes • Cipher codes • Action codes

36. Using Codes During Data Design • Developing a Code • Keep codes concise • Allow for expansion • Keep codes stable • Make codes unique • Use sortable codes • Avoid confusing codes • Make codes meaningful • Use a code for a single purpose • Keep codes consistent

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

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

39. Data Storage and Access • Data storage and access involve strategic business tools • Strategic tools for data storage and access • Data warehouse • Data Mining

40. Data Storage and Access • Logical and Physical Storage • Logical storage • Data that a user can view, understand and access regardless of how or where that information actually is organized or stored • Consist of alphabetic and numeric Characters • Field also called Data element or data item • Logical record is a set of field values that describes a single person, place, thing or event.

41. Data Storage and Access • Physical storage • The process of reading and writing binary data to physical media (h/d,CD) • Involves a Physical record or block • Load data from storage into Buffer • Physical record can contain more than one logical record, depending on Blocking factor • Data Coding and Storage • Computer represent data as Binary digits (Bit,Byte) • EBCDIC, ASCII, and Binary • Unicode

42. Data Storage and Access • Storing dates • Y2K Issue • Most date formats now are based on the model established by the International Organization for Standardization (ISO) • Absolute date

43. Data Control • User ID • Password • Permissions • Encryption • Backup • Recovery procedures • Audit log files • Audit fields

44. 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 • An entity-relationship diagram (ERD) is a graphic representation of all system entities and the relationships among them

45. Chapter Summary • A code is a set of letters or numbers used to represent data in a system • The most common database models are relational and object-oriented • Logical storage is information seen through a user’s eyes, regardless of how or where that information actually is organized or stored

46. Chapter Summary • 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

47. Review Questions • Explain the main differences between a file processing system and database system. • What is DBMS? Briefly describe the components of a DBMS. • What is ERD? • What are data warehouse and data mining? • Explain the difference between a logical record and physical record. • State seven types of codes.