SYSTEMS ANALYSIS DESIGN PHASE 3 SYSTEMS DESIGN Data

SYSTEMS ANALYSIS & DESIGN PHASE 3 SYSTEMS DESIGN Data Design

Chapter 8 Data Design SYSTEMS ANALYSIS & DESIGN 4 E PHASE 3 2

Objectives PHASE 3 3 à Explain data design concepts and data structures à Describe file processing systems and various types of files à Describe database systems, their characteristics, and advantages à Define the components of a database management system (DBMS) SYSTEMS ANALYSIS & DESIGN 4 E

Objectives PHASE 3 4 à Explain the concepts of data warehousing and data mining à Understand data design terminology, including entities, fields, common fields, records, files, tables, and key fields à Explain data relationships and draw an entityrelationship diagram SYSTEMS ANALYSIS & DESIGN 4 E

Objectives PHASE 3 5 à Define cardinality, cardinality notation, and crow’s foot notation à Explain normalization, including examples of first, second, and third normal form à Describe hierarchical, network, relational, and object-oriented database models SYSTEMS ANALYSIS & DESIGN 4 E

Objectives PHASE 3 6 à Differentiate between logical and physical records, and discuss data storage formats and date fields à Explain data control measures SYSTEMS ANALYSIS & DESIGN 4 E

Introduction PHASE 3 7 à Main focus is on data design and management issues necessary to construct the physical model à Develop a physical plan for data organization, storage, and retrieval à Review data design concepts and terminology à Discuss relationships among data objects à Draw entity-relationship diagrams à Use normalization concepts to build an effective database design à Physical design issues à Logical and physical records à Data storage formats and controls SYSTEMS ANALYSIS & DESIGN 4 E

Data Design Concepts PHASE 3 8 à Items to understand before constructing an information system à How data is structured à Characteristics of file-oriented and database systems à Data warehousing à Data mining à Components of a database management system SYSTEMS ANALYSIS & DESIGN 4 E

PHASE 3 9 Data Design Concepts à Data structures à A file contains data about people, places, things, or events that interact with the information system à A file-oriented system processes files using file processing SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -1

Data Design Concepts PHASE 3 10 à Data structures à A database consists of linked data files, also called tables, that form an overall data structure à A database management system (DBMS) is a collection of tools, features, and interfaces that enable users to add, update, manage, access, and analyze data in a database SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -2

Data Design Concepts PHASE 3 11 à Overview of file processing à File processing can be more efficient and cost less than a DBMS in certain situations à Potential problems in a file-processing environment à Data redundancy à Data integrity à Rigid data structure SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -3

Data Design Concepts à Overview of file processing à Types of files used in a file-oriented information system à Master files à Table files à Transaction files à Work files à Security files à History files SYSTEMS ANALYSIS & DESIGN 4 E PHASE 3 12

Data Design Concepts PHASE 3 13 à Overview of file processing à Master files à Stores relatively permanent data about an entity à Might include data for customers, sales representatives, students, employees, or patients SYSTEMS ANALYSIS & DESIGN 4 E

Data Design Concepts PHASE 3 14 à Overview of file processing à Table files à Contains reference data used by the information system à Relatively permanent à Not updated by the information system SYSTEMS ANALYSIS & DESIGN 4 E

Data Design Concepts PHASE 3 15 à Overview of file processing à Transaction files à Stores records that contain day-to-day business and operational data à An input file that updates a master file à Temporary files SYSTEMS ANALYSIS & DESIGN 4 E

Data Design Concepts PHASE 3 16 à Overview of file processing à Work files à Temporary file created by an information system for a single task à Also called scratch files à Can contain copies of master file records that are needed temporarily SYSTEMS ANALYSIS & DESIGN 4 E

Data Design Concepts PHASE 3 17 à Overview of file processing à Security files à Created and saved for backup and recovery purposes à New security files must be created regularly to replace outdated files SYSTEMS ANALYSIS & DESIGN 4 E

Data Design Concepts PHASE 3 18 à Overview of file processing à History files à A file copy created and saved for historical or archiving purposes à New history files do not replace old files SYSTEMS ANALYSIS & DESIGN 4 E

Data Design Concepts PHASE 3 19 à Overview of database system à A database provides an overall framework that avoids data redundancy and supports a realtime, dynamic environment à Several systems can be built around a single database SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -4

Data Design Concepts à Overview of database system à DBMS advantages à à à à à Scalability Better support for client/server systems Economy of scale Sharing of data Balancing conflicting requirements Enforcement of standards Controlled redundancy Security Increased programmer productivity Data independence SYSTEMS ANALYSIS & DESIGN 4 E PHASE 3 20

PHASE 3 21 Data Design Concepts à DBMS Components à Interfaces for users àUsers work with predefined queries and switchboard commands äQuery language allows users to specify a task without specifying how the task will be accomplished à Database administrators and related systems Click to see Figure 8 -5 SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -6

Data Design Concepts PHASE 3 22 à DBMS Components à Data manipulation language (DML) à Controls database operations à Schema à The complete definition of a database à A subschema defines only those portions of the database that a particular system or user is allowed access à Physical data repository à Contains the transformed data dictionary, schema, and subschemas SYSTEMS ANALYSIS & DESIGN 4 E

Data Design Concepts PHASE 3 23 à Data warehousing à A data warehouse is an integrated collection of data that can support management analysis and decision making à Stores transaction data in a format that allows users to access, combine, and analyze data. à Users can specify dimensions SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -7

Data Design Concepts PHASE 3 24 à Data mining software looks for meaningful patterns and relationships among data à E-commerce uses data mining as a tool to analyze Web visitor behavior and traffic trends SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -8

Data Design Terminology à Data design includes à Entities à Fields à Records à Files and tables SYSTEMS ANALYSIS & DESIGN 4 E PHASE 3 25

Data Design Terminology PHASE 3 26 à Definitions à Entity: a person, place, thing, or event for which data is collected and maintained à Field (attribute): a single characteristic or fact about an entity à Record: a collection of fields that describes one instance of an entity à File and table: a set of records that contains data about a specific entity SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -9

Data Design Terminology PHASE 3 27 à Key fields à Used to organize, access, and maintain data structures à Four types of keys à Primary keys à Candidate keys à Foreign keys à Secondary keys SYSTEMS ANALYSIS & DESIGN 4 E

Data Design Terminology PHASE 3 28 à Key fields à Primary keys à A field or combination of fields that uniquely and minimally identifies each member of an entity à A primary key composed of more than one field is called a multivalued key SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -10

Data Design Terminology PHASE 3 29 à Key fields à Candidate keys à Any field that could serve as primary key à Any field that is not a primary key or candidate key is called a nonkey field SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -10

Data Design Terminology PHASE 3 30 à Key fields à Foreign keys à A field in one file that matches a primary key value in another file à Example: the advisor number is a foreign key in the STUDENT file that matches a primary key value in the ADVISOR file à A foreign key need not be unique à A combination of two or more foreign keys can form a unique primary key value SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -10

Data Design Terminology PHASE 3 31 à Key fields à Secondary keys à A field or combination of fields that can be used to access or retrieve records à Secondary keys do not need to be unique SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -10

Data Design Terminology PHASE 3 32 à Referential integrity à A set of rules that avoids data inconsistency and quality problems à Referential integrity ensures that a foreign key value cannot be entered unless it matches a primary key value in another file Click to see Figure 8 -10 SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -11

Data Relationships à A relationship is a logical link between entities based on how they interact à Entity-relationship diagrams (ERDs) à An ERD is a graphical model that shows relationships among system entities SYSTEMS ANALYSIS & DESIGN 4 E PHASE 3 33

PHASE 3 34 Data Relationships à Entity-relationship diagrams (ERDs) à Each entity is a rectangle, labeled with a noun à Each relationship is a diamond, labeled with a verb à Types of relationships à One-to-one (1: 1) à One-to-many (1: M) à Many-to-many (M: N) à A complete ERD shows all system relationships SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -12

Data Relationships PHASE 3 35 à One-to-one (1: 1) relationship à Exists when exactly one of the second entity occurs for each instance of the first entity à Examples à One office manager heads one office à One vehicle ID number is assigned to one vehicle à One driver drives one delivery truck à One faculty member is chairperson of one department SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -13

PHASE 3 36 Data Relationships à One-to-many (1: M) relationship à Exists when one occurrence of the first entity can be related to many occurrences of the second entity, but each occurrence of the second entity can be associated with only one occurrence of the first entity à Examples à One individual owns many automobiles à One customer places many orders à One department employs many employees à One faculty advisor advises many students SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -14

PHASE 3 37 Data Relationships à Many-to-many (M: N) relationship à Exists when one instance of the first entity can be related to many instances of the second entity, and one instance of the second entity can be related to many instances of the first à Examples à A student enrolls in one or more classes, and each class has one or more students registered à A passenger buys tickets for one or more flights, and each flight has one or more passengers à An order lists one or more products, and each product is listed on one or more orders SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -15

PHASE 3 38 Data Relationships à A complete ERD shows all system relationships à Examples à A sales rep serves one or more customers, but each customer has only one sales rep à A customer places one or more orders, but each order has only one customer à An order lists one or more products, and each product can be listed in one or more orders à A warehouse stores one or more products, and each product can be stored in one or more warehouses SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -16

Data Relationships PHASE 3 39 à Cardinality à Describes how instances of one entity relate to another à Mandatory vs. optional relationships à Crow’s foot notation is one method of showing cardinality SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -17

Data Relationships PHASE 3 40 à Cardinality à Describes how instances of one entity relate to another à Mandatory vs. optional relationships à Crow’s foot notation is one method of showing cardinality à Most CASE products support the drawing of ERDs SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -18

Data Relationships PHASE 3 41 à Creating an ERD 1. Identify the entities 2. Determine all significant events or activities for two or more entities 3. Analyze the nature of the interaction 4. Draw the ERD SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -19

Normalization PHASE 3 42 à A process by which identifies and corrects inherent problems and complexities in record designs à A record design specifies the fields of the primary key for all records in a particular file or table à Three stages of normalization à First normal form à Second normal form à Third normal form SYSTEMS ANALYSIS & DESIGN 4 E

Normalization PHASE 3 43 à Record design à Use a standard method of showing the record structure, fields, and primary keys SYSTEMS ANALYSIS & DESIGN 4 E

Normalization PHASE 3 44 à First normal form (1 NF) à Unnormalized records contain a repeating group à A repeating group refers to a single record that has multiple values in a particular field à Example: multiple product numbers in a single order record à A 1 NF record cannot have a repeating group SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -20

Normalization PHASE 3 45 à First normal form (1 NF) à To convert an unnormalized record to 1 NF, the repeating group must be removed à Expand the primary key to include the primary key of the repeating group à The new primary key is a combination of the original primary key and the key of the repeating group à Instead of a single record with a repeating group, the result is many records, one for each instance of the repeating group SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -21

Normalization PHASE 3 46 à Second normal form (2 NF) à To be in second normal form, a record must be in 1 NF, and all nonkey fields must be functionally dependent on the entire primary key - not just part of it à Functional dependency means that a value in one field determines a value in another field à If the primary key is a single field, then any record in 1 NF is automatically in 2 NF à In 2 NF, all nonkey fields are functionally dependent on the entire primary key SYSTEMS ANALYSIS & DESIGN 4 E

Normalization PHASE 3 47 à Second normal form (2 NF) à To convert a 1 NF record to 2 NF à Create a new record design for each field (or combination of fields) in the primary key à Place remaining fields with the appropriate record à The result will be several records, each with a primary key field (or combination of fields) that determines the values of the other fields in that record SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -22

Normalization PHASE 3 48 à Third normal form (3 NF) à To be in 3 NF, a record must be in 2 NF and no nonkey field is functionally dependent on another nonkey field à In 3 NF, all nonkey fields are functionally dependent on the primary key, the entire key, and nothing but the key SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -23

Normalization PHASE 3 49 à Third normal form (3 NF) à To convert a 2 NF record to 3 NF à Remove all nonkey fields that depend on another nonkey field and place them in a new record that has the determining field as a primary key SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -24

Normalization PHASE 3 50 à A normalization example à Identify the entities à ADVISOR à STUDENT à COURSE à Identify the relationships à One advisor advises many students (1: M) à Students take one or more courses, and courses have one or more students (M: N) Click to see Figure 8 -25 SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -26

Normalization PHASE 3 51 à A normalization example à Identify the entities à ADVISOR à STUDENT à COURSE à Identify the relationships à One advisor advises many students (1: M) à Students take one or more courses, and courses have one or more students (M: N) à Document the unnormalized record à Note the repeating group of courses SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -27

Normalization PHASE 3 52 à A normalization example à Convert the unnormalized record to 1 NF à Remove the repeating group à Create a primary key composed of the original primary key (student number) and the primary key of the repeating group (course number) à The result is one record for each instance of the combination primary key SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -28

Normalization PHASE 3 53 à A normalization example à Convert the 1 NF record to 2 NF à Create a separate record design for each field and combination of fields in the primary key à Place functionally dependent fields with an appropriate primary key à The result is three records instead of one, each with a unique primary key à Now all nonkey fields are dependent on the entire primary key, not just a portion of it SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -29

Normalization PHASE 3 54 à A normalization example à Convert the 2 NF record to 3 NF à The STUDENT record contains a nonkey field (advisor name) that is dependent on another nonkey field (advisor number) à Create a new record with advisor number as the primary key à Remove the dependent nonkey field (advisor name) and include it in the new record SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -30

Normalization PHASE 3 55 à A normalization example à Convert the 2 NF record to 3 NF à The STUDENT record contains a nonkey field (advisor name) that is dependent on another nonkey field (advisor number) à Create a new record with advisor number as the primary key à Remove the dependent nonkey field (advisor name) and include it in the new record à Now all nonkey fields are dependent on the entire primary key, and nothing but the key SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -31

Steps in Database Design PHASE 3 56 à Four steps in database design 1. Create the initial ERD 2. Assign all data elements to entities 3. Create 3 NF designs for all records, taking care to identify all primary, secondary, and foreign keys 4. Verify all data dictionary entries Click to see Figure 8 -32 Click to see Figure 8 -33 Click to see Figure 8 -34 SYSTEMS ANALYSIS & DESIGN 4 E

Database Models PHASE 3 57 à Hierarchical and network databases à Hierarchical database à Data is organized like a family tree or organization chart à The parent record can have multiple child records – child records can only have one parent à Pointers link each parent record with each child record à Complex and difficult to maintain SYSTEMS ANALYSIS & DESIGN 4 E

Database Models PHASE 3 58 à Hierarchical and network databases à Network databases à More flexible than a hierarchical database à Child record can have relationships with more than one parent à Complex with many of the same disadvantages as hierarchical models SYSTEMS ANALYSIS & DESIGN 4 E

Database Models PHASE 3 59 à Relational databases à Data is organized into two-dimensional tables, or relations à Each row is a tuple (record) and each column is an attribute (field) à A common field appears in more than one table and is used to establish relationships à Because of its power and flexibility, the relational database model is the predominant design approach Click to see Figure 8 -35 SYSTEMS ANALYSIS & DESIGN 4 E

Database Models PHASE 3 60 à Object-oriented databases à Used as a natural extension of the objectoriented analysis process à Object Definition Language (ODL) is a design standard set forth by the Object Database Management Group (ODMG) à Each object has a unique object identifier à The identifier allows the object to interact with other objects and form relationships Click to see Figure 8 -36 SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -37

Data Storage PHASE 3 61 à Logical and physical records à Bit à Byte (character) à Field (data element, or data item) à Logical record à Contains field values that describe a single person, place, thing, or event à Physical record (block) à Smallest unit of data that can be accessed à Contains one or more logical records, based on a blocking factor SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -38

Data Storage PHASE 3 62 à Data storage formats à EBCDIC à ASCII à Unicode à Binary SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -39

Data Storage PHASE 3 63 à Date fields à International Organization for Standardization (ISO) requires a format of four digits for the year, two for the month, and two for the day (YYYYMMDD) à Julian date is a five-digit number in which the first two digits represent the year and the last three digits represent the day of the year à Absolute date is the total number of days from some specific base date. à Julian dates and absolute dates are easier to use for calculations SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -40

PHASE 3 64 Data Control à Control must include measures to ensure that data is correct, complete, and secure à User IDs and Passwords à Encryption à Backup and recovery procedures à Audit log files à Audit fields SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -41

SOFTWEAR, LIMITED PHASE 3 65 à Michael Jeremy has asked the IT department to develop an overall strategy for information management as part of SWL’s strategic plan à Work continues on the ESIP system, which will serve as a prototype for future SWL systems development à Tom Adams and Becky Evans will work on developing an ERD and normalized record designs for the ESIP system SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -42

SOFTWEAR, LIMITED PHASE 3 66 à ERD and normalization tasks à A CASE tool was used to draw the ERD à The ERD showed the entities and the nature of the relationships among them à The record designs progressed from 1 NF to 2 NF to 3 NF Click to see Figure 8 -43 SYSTEMS ANALYSIS & DESIGN 4 E Click to see Figure 8 -44

PHASE 3 67 End Chapter 8 SYSTEMS ANALYSIS & DESIGN 4 E