content analysis

profilegenjutsu980
SYAD-Week10DataDesign.pptx
Home>Computer Science homework help>content analysis

Chapter 9 – Data Design

Kent Institute Australia Pty. Ltd.

ABN 49 003 577 302 CRICOS Code: 00161E RTO Code: 90458 TEQSA Provider Number: PRV12051

Version 2 – 18th December 2015

1

Prescribed Text and recommended readings

Prescribed text

Rosenblatt, H. J. (2016), Systems Analysis and Design.11th Edition, Cengage Learning, Boston MA

Prescribed reading

Robertson, S. and Robertson, J. (2013), Mastering the Requirements Process: Getting Requirements Right, 3rd Edition, Addison Wesley, Upper Saddle River, NJ

IIBA (2015), Guide to the Business Analysis Body of Knowledge, BABOK Version 3.0, International Institute of Business Analysis, http://www.iiba.org/BABOKGuide.aspx

2

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

Data Design Concepts

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

5

Mario and Danica - A Data Design Example

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

6

Mario and Danica - A Data Design Example

7

Mario’s Auto Shop

FIGURE 9-2 Mario’s shop uses two separate systems, so certain data must be entered twice. This redundancy is inefficient and can produce data errors.

Danica’s Auto Shop

FIGURE 9-4 Danica’s SHOP OPERATIONS SYSTEM uses a database design, which avoids duplication. The data can be viewed as if it were one large table, regardless of where the data is stored physically.

Data Design Concepts

Is File Processing Still Important?

Used by some companies to handle large volumes of structured data on a regular basis

Cost-effective in certain situations

8

FIGURE 9-4 A credit card company that posts thousands of daily transactions might consider a file processing option.

Data Design Concepts

The Database Environment

Database management system (DBMS): Collection of tools, features, and interfaces that enables users to add, update, manage, access, and analyze data

DBMS advantages

Scalability - System can be expanded, modified, or downsized

Economy of scale

Database design allows better utilization of hardware

9

Figure 9-5 In this example, a sales database can

support four separate business systems.

Data Design Concepts

Enterprise-wide application

A database administrator (DBA) assesses overall requirements and maintains the database

Stronger standards

Standards for data names, formats, and documentation are followed uniformly throughout the organization

Better security

Only legitimate users can access the database

Different users have different levels of access

Data independence

Systems that interact with a DBMS are relatively independent of how physical data is maintained

10

DBMS Components

Interfaces for Users, Database Administrators, and Related Systems

Users

Work with predefined queries and switchboard commands

Use query languages to access stored data

Database administrators

Responsible for DBMS

management and support

Related information systems

DBMS provides support to related information systems

11

FIGURE 9-6 In addition to interfaces or users, database administrators, and related information systems, a DBMS also has a data manipulation language, a schema and subschemas, and a physical data repository.

DBMS Components

Data Manipulation Language (DML)

Controls database operations

Schema

Descriptions of all fields, tables, and relationships

Subschema: Portions of the database 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 at several locations

Uses open database connectivity (ODBC)-compliant software that enables communication among the systems and DBMSs

12

Web-Based Data Design

Connecting to the Web

Databases are created and managed by using languages and commands that have nothing to do with HTML

Objective - To 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

Data Security

Web-based data must be secure, yet easily accessible to authorized users

13

Icon courtesy: https://support.office.com/en-us/article/use-the-azure-diagrams-template-efbb25e7-c80e-42e1-b1ad-7ef630ff01b7

Web-Based Data Design

14

FIGURE 9-8 A Web-based design characteristics include global access, ease of use, multiple platforms, cost effectiveness, security issues, and adaptability issues. In a Web-based design, the Internet serves as the front end, or interface, for the database management system. Access to the database requires only a Web browser and an Internet connection.

Web-Based Data Design

15

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

Data Design Terms

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 (attribute) - Single characteristic or fact about an entity

Common field: Attribute that appears in more than one entity

Tuple (record): Set of related fields that describes one instance, or occurrence, of an entity

16

Icon courtesy: https://stackoverflow.com/questions/39060709/changes-of-product-price-in-database-design

Data Design Terms

Key Fields

Primary key: Field or combination of fields that uniquely and minimally identifies a particular member of an entity

Called a combination key

Candidate key: Any 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

17

Icon courtesy: https://stackoverflow.com/questions/39060709/changes-of-product-price-in-database-design

Data Design Terms

Referential Integrity

Set of rules that avoids data inconsistency and quality problems

18

FIGURE 9-11 Microsoft Access allows a user to specify that referential integrity rules will be enforced in a relational database design.

Entity-Relationship Diagrams

Drawing an ERD

List the entities that were identified during the systems analysis phase

Consider the nature of the relationships that link them

19

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.

Entity-Relationship Diagrams

Types of Relationships

One-to-one relationship: Exists when exactly one of the second entity occurs for each instance of the first entity

Abbreviated 1:1

20

FIGURE 9-13 Examples of one-to-one (1:1) relationships.

Entity-Relationship Diagrams

One-to-many relationship: Exists when 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 instance of the first entity

Abbreviated 1:M

21

FIGURE 9-14 Examples of one-to-many (1:M) relationships.

Entity-Relationship Diagrams

Many-to-many relationship

Exists when one instance of the first entity can relate to many instances of the second entity, and vice versa

Abbreviated M:N

22

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

Entity-Relationship Diagrams

23

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.

Entity-Relationship Diagrams

Cardinality

Describes the numeric relationship between two entities

Shows how instances of one entity relate to instances of another entity

Crow’s foot notation indicates various possibilities using circles, bars, and symbols

24

FIGURE 9-17 Crow’s foot notation is a common method of indicating cardinality. The four examples show how you can use various symbols to describe the relationships between entities.

Entity-Relationship Diagrams

25

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.

Entity-Relationship Diagrams

26

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.

Data Normalization

Normalization: Process of 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

Stages in a normalization process

Un-normalized design

First normal form

Second normal form

Third normal form

27

Data Normalization

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

28

Data Control

A well-designed DBMS must provide built-in control and security features

Forms of data protection

Providing limited access to files and databases

Use of user ID and password, permissions and encryption

Backup copies of databases must be retained for a specified period of time

Recovery procedures can be used to restore the file or database

Maintain audit log files and audit fields

29

Icon courtesy: https://www.plutustree.com/data-management-icon/

Data Control

A well-designed DBMS must provide built-in control and security features

Forms of data protection

Providing limited access to files and databases

Use of user ID and password, permissions and encryption

Backup copies of databases must be retained for a specified period of time

Recovery procedures can be used to restore the file or database

Maintain audit log files and audit fields

30

Icon courtesy: https://www.plutustree.com/data-management-icon/

Chapter Summary

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

In an information system, an entity is a person, place, thing, or event for which data is collected and maintained

31

Icon courtesy: https://www.venturetechsolutions.com/it-infrastructure/backup-strategies/lg-icon-it-infrastructure-db-admin/

Chapter Summary

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

Relationship between two entities is referred to as cardinality

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

32

Icon courtesy: https://www.venturetechsolutions.com/it-infrastructure/backup-strategies/lg-icon-it-infrastructure-db-admin/

Chapter Summary

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 includes:

limiting access to the data

data encryption

backup/recovery procedures

audit-trail files, and

internal audit fields

33

Icon courtesy: https://www.venturetechsolutions.com/it-infrastructure/backup-strategies/lg-icon-it-infrastructure-db-admin/

kent.edu.au Kent Institute Australia Pty. Ltd. ABN 49 003 577 302 ● CRICOS Code: 00161E ● RTO Code: 90458 ● TEQSA Provider Number: PRV12051

34

34