Data Design Chapter 9 PDF

Chapter 9 Data Design Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-...

Chapter 9 Data Design Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Learning Objectives (1 of 2) After this chapter, you will be able to: Explain basic data design concepts, including data structures, DBMSs, and the evolution of the relational database model Explain the main components of a DBMS Define the major characteristics of web-based design Define data design terminology Draw entity-relationship diagrams Apply data normalization Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Learning Objectives (2 of 2) Utilize codes to simplify output, input, and data formats Explain data storage tools and techniques, including logical versus physical storage Explain data coding Explain data control measures Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Data Design Concepts (1 of 5) Data structures Framework for organizing, storing, and managing data Comprises of files or tables that interact in various ways Each file or table contains data about people, places, things, or events Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Data Design Concepts (2 of 5) Mario and Danica: A Data Design Example Mario’s auto shop uses file-oriented systems MECHANIC SYSTEM uses the MECHANIC file to store data about shop employees JOB SYSTEM uses the JOB file to store data about work performed at the shop Danica’s auto shop uses a relational model SHOP OPERATIONS SYSTEM: tables are linked by a common field named Mechanic No field Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.  Data Design Concepts (3 of 5) FIGURE 9-4 Danica’s SHOP OPERATIONS SYSTEM uses a database design, which FIGURE 9-3 Mario’s shop uses two separate avoids duplication. The data can be viewed systems, so certain data must be entered twice. as if it were one large table, regardless of This redundancy is inefficient and can produce where the data is stored physically. data errors. Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Data Design Concepts (4 of 5) Database management system (DBMS) Collection of tools, features, and interfaces that enables users to add, update, manage, access, and analyze data Advantages Scalability Economy of scale Enterprise-wide application Stronger standards and better security Data independence Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Data Design Concepts (5 of 5) Figure 9-5 In this example, a sales database can support four separate business systems. Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. DBMS Components (1 of 2) Interfaces for users, database administrators, and related systems Users Work with predefined queries and switchboard commands Database administrators Responsible for DBMS management and support Related information systems DBMS provides support to related information systems Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. DBMS Components (2 of 2) Schema Descriptions of all fields, tables, and relationships Subschema: database portions that a particular system or user needs or is allowed to access Physical data repository Contains the schema and subschemas Can be centralized or distributed Uses open database connectivity (ODBC) compliant software Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Web-Based Design (1 of 2) Databases are created and managed by using languages and commands that have nothing to do with HTML Objective: connect the database to the Web and enable data to be viewed and updated Middleware is used integrate different applications and allow them to exchange data Web-based data must be secure, yet easily accessible to authorized users Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.  Web-Based Design (2 of 2) FIGURE 9-9 When a client workstation requests a Web page (1), the Web server uses middleware to generate a data query to the database server (2). The database server responds (3), and middleware translates the retrieved data into an HTML page that can be sent by the Web server and displayed by the user’s browser (4). Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Data Design Terms (1 of 3) Definitions Entity: person, place, thing, or event for which data is collected and maintained Table or file: contains a set of related records that store data about a specific entity Field (i.e., attribute): single characteristic or fact about an entity Common field: attribute in more than one entity Record (i.e., tuple): set of related fields that describes one instance of an entity Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Data Design Terms (2 of 3) Key fields Primary key: field or fields that uniquely and minimally identifies a member of an entity Candidate key: field that could serve as a primary key Foreign key: field in one table that must match a primary key value in another table for a relationship between the two tables to exist Secondary key: field or combination of fields that can be used to access or retrieve records Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Data Design Terms (3 of 3) Referential integrity Set of rules that avoids data inconsistency and quality problems FIGURE 9-11 Microsoft Access allows a user to specify that referential integrity rules will be enforced in a relational database design. Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Entity-Relationship Diagrams (1 of 8) Drawing an ERD List the entities that were identified during the systems analysis phase Consider the nature of the relationships that link them FIGURE 9-12 In an entity-relationship diagram, entities are labeled with singular nouns and relationships are labeled with verbs. The relationship is interpreted as a simple English sentence. Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Entity-Relationship Diagrams (2 of 8) Types of relationships One-to-one (1:1) relationship: exactly one of the second entity occurs for each instance of the first entity FIGURE 9-13 Examples of one-to-one (1:1) relationships. Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Entity-Relationship Diagrams (3 of 8) One-to-many relationship (1:M): one occurrence of the first entity can relate to many instances of the second entity Each instance of the second entity can associate with only one FIGURE 9-14 Examples of one- instance of the first entity to-many (1:M) relationships. Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Entity-Relationship Diagrams (4 of 8) Many-to-many relationship (M:N): one instance of the first entity can relate to many instances of the second entity, and vice versa FIGURE 9-15 Examples of many-to-many (M:N) relationships. Notice that the event or transaction that links the two entities is an associative entity with its own set of attributes and characteristics. Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Entity-Relationship Diagrams (5 of 8) FIGURE 9-16 An entity-relationship diagram for SALES REP, CUSTOMER, ORDER, PRODUCT, and WAREHOUSE. Notice that the ORDER and PRODUCT entities are joined by an associative entity named ORDER LINE. Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Entity-Relationship Diagrams (6 of 8) Cardinality Describes numeric relationship between two entities Shows how instances of one entity relate to instances of another entity Crow’s foot notation indicates various possibilities FIGURE 9-17 Crow’s foot notation is a common method of indicating cardinality. using circles, bars, and The four examples show how you can use symbols various symbols to describe the relationships between entities. Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Entity-Relationship Diagrams (7 of 8) FIGURE 9-18 In the first example of cardinality notation, one and only one CUSTOMER can place anywhere from zero to many of the ORDER entity. In the second example, one and only one ORDER can include one ITEM ORDERED or many. In the third example, one and only one EMPLOYEE can have one SPOUSE or none. In the fourth example, one EMPLOYEE, or many employees, or none, can be assigned to one PROJECT, or many projects, or none. Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Entity-Relationship Diagrams (8 of 8) FIGURE 9-19 An ERD for a library system drawn with Visible Analyst. Notice that crow’s foot notation has been used and relationships are described in both directions. Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Data Normalization (1 of 9) Normalization: creating table designs by assigning specific fields or attributes to each table in the database Table design: specifies fields Identifies the primary key in a particular table or file Normalization stages Unnormalized design First normal form Second normal form Third normal form Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Data Normalization (2 of 9) Standard notation format Used to show a table’s structure, fields, and primary key The primary key field(s) is underlined NAME (FIELD 1, FIELD 2, FIELD 3) Recognition of repeating group fields is important Repeating group: set of one or more fields that can occur any number of times in a single record Each occurrence would possess different values Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.  Data Normalization (3 of 9) FIGURE 9-20 In the ORDER table design, two orders have repeating groups that contain several products. ORDER is the primary key for the ORDER table, and PRODUCT NUMBER serves as a primary key for the repeating group. Because it contains repeating groups, the ORDER table is unnormalized. Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Data Normalization (4 of 9) First Normal Form (1NF) Does not contain a repeating group Converting an unnormalized design to 1NF requires expansion of the table’s primary key to include the primary key of the repeating group Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Data Normalization (5 of 9) FIGURE 9-21 The ORDER table as it appears in 1NF. The repeating groups have been eliminated. Notice that the repeating group for order 86223 has become three separate records, and the repeating group for order 86390 has become two separate records. The 1NF primary key is a combination of ORDER and PRODUCT NUMBER, which uniquely identifies each record. Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Data Normalization (6 of 9) Second Normal Form (2NF) Examples of functional dependence Field A is functionally dependent on Field B if the value of Field A depends on Field B A table design is in 2NF if it is in 1NF and all fields not part of the primary key are functionally dependent on the entire primary key Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Data Normalization (7 of 9) FIGURE 9-22 ORDER, PRODUCT, and ORDER LINE tables in 2NF. All fields are functionally dependent on the primary key. Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Data Normalization (8 of 9) Third Normal Form (3NF) Design is in 3NF if every nonkey field depends on the key, the whole key, and nothing but the key Avoids redundancy and data integrity problems that still can exist in 2NF designs Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Data Normalization (9 of 9) FIGURE 9-23 When the PRODUCT table is transformed from 2NF to 3F, the result is two separate tables: PRODUCT and SUPPLIER. Note that in 3NF, all fields depend on the key, the whole key, and nothing but the key! Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Two Real-World Examples A good way to learn about normalization is to apply the rules to a representative situation Refer to the text for two scenarios School Technical service company Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Codes (1 of 4) Overview of codes Shorter than the data they represent Save storage space and costs Decrease data entry time and transmission time Reveal or conceal information Reduce data input errors Easier to remember Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Codes (2 of 4) Types of codes Sequence codes: numbers or letters assigned in a specific order Block sequence codes: use blocks of numbers for different classifications Alphabetic codes: use letters to distinguish one item from another Category codes: identify a group of related items Abbreviation codes: alphabetic abbreviations Mnemonic codes: specific combination of letters that are easy to remember Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Codes (3 of 4) Significant digit codes: distinguish items by using a series of subgroups of digits Derivation codes: combine data from different item attributes, or characteristics Cipher codes: use a keyword to encode a number Action codes: indicate what action is to be taken with an associated item Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Codes (4 of 4) Designing codes Keep codes concise and consistent Allow for expansion Keep codes stable and make them unique Use sortable codes and a simple structure Avoid confusion and make codes meaningful Use a code for a single purpose Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Data Storage and Access (1 of 7) Tools and techniques Data warehouse An integrated collection of data that can include seemingly unrelated information, no matter where it is stored in the company Data mart Designed to serve needs of a specific department Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Data Storage and Access (2 of 7) FIGURE 9-37 A data warehouse stores data from several systems. By selecting data dimensions, a user can retrieve specific information without having to know how or where the data is stored. Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Data Storage and Access (3 of 7) Data mining Looks for meaningful data patterns and relationships Suggested goals for data mining Increase the number of pages viewed per session and referred customers Reduce clicks to close Increase checkouts per visit and average profit per checkout Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Data Storage and Access (4 of 7) Logical versus 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 Physical storage: strictly hardware related Involves the process of reading and writing binary data to physical media Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Data Storage and Access (5 of 7) Data coding EBCDIC (Extended Binary Coded Decimal Interchange Code) Mainframe computers and high-capacity servers ASCII (American Standard Code for Information Interchange) Used on most personal computers Binary storage format Represents numbers as actual binary values Unicode: uses two bytes per character Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Data Storage and Access (6 of 7) FIGURE 9-38 Unicode is an international coding format that represents characters as integers, using 16 bits per character. The Unicode Consortium maintains standards and support for Unicode. Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Data Storage and Access (7 of 7) Storing dates International Organization for Standardization (ISO) requires a format of four digits for the year, two for the month, and two for the day (YYYYMMDD) Absolute date: total number of days from some specific base date Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Data Control Well-designed DBMS Must provide built-in control and security Forms of data protection Limited access to files and databases User ID and passwords Permissions and encryption Backup copies of databases must be retained for a specified period of time Recovery procedures can be used to restore Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Summary (1 of 3) A database consists of linked tables that form an overall data structure DBMS enable users to add, update, manage, access, and analyze data in a database DBMS designs are more powerful and flexible than traditional file-oriented systems Components include interfaces for users, database administrators, and related systems Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Summary (2 of 3) Key fields include primary keys, candidate keys, foreign keys, and secondary keys An entity-relationship diagram (ERD) is a graphic representation of all system entities and the relationships among them Normalization is a process for avoiding problems in data design A code is a set of letters or numbers used to represent data in a system Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. Summary (3 of 3) Logical storage is information seen through a user’s eyes, regardless of how or where that information actually is organized or stored File and database control measures include limiting access to the data, data encryption, backup/recovery procedures, audit-trail files, and internal audit fields Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.