8/12/2019 Note Ais 630 Chp 11

1/6

Chapter 11

System Design Database, Network, Program and Control

DATABASE DESIGNDatabase mgt is important as org have to manage vast

volumes of data that they need to process

Poorly managed data can cause problem for org in their

attempt to respond to changes and to implement business

strategies.

Part of managing data is how the organization build their

database ( ie how org define their data and type data they

collect)

Database is a central source of data that is meant to be shared

by many users for variety of application

Eg : Integrated system :information are only entered once and

can be used in other department

Non-Integrated system : information have to be keyed in

several separate systems that would result in higher cost

and error rate.

A carefully designed database makes it easy for user to query

the database, modify data and create report

Database Types

1.Hierarchical

2.Network

3.Relational ie Microsoft ACCESS

4.Object-oriented

5.Object relational

Hierarchi+Network

Old technologies as it offer limited data access and not very

flexible

Object oriented+Object relational > newer techno

Relational Database (Exhibit 11.1 pg 158)

It is a database that allows tables to be related to each other so

that changes in one table are reflected in other tables

automatically.

Table A collection of related information. It contains records

that have the same field types.

Database Design Guidelines1. Determine purpose of database

-Carry out analysis of all data needed for processing

and filling. Analysis need to be accurate so tables

are complete and workable

2. Identify entities

-Entity any class of objects of which data n info are

collected. Can be person, place, thing(product)

,event(sale)

-Entities in ERD use rectangular symbols and labeled

with singular nouns

3. Determine R/ship among the entities

-R/ships used to describe how 2 entities are

associated

-R/ship in ERD use diamond symbol n labeled with

active verbs

Types of r/ship

One

-to

-one (1:1)

Eg CFO report to CFO

One-to-many (1:M)

Eg supervisor have many employees under him

Many-to-many (M:M)

Eg Construction workers

*Another eg pg 161

4. Draw the ERD

ERD graphically depicts the contents of a database. It

shows various entities and the important

relationships among the entities

Act as communion tools btw user and database designer

DFD allow users to take a logical look at the way which

data flows through the orgs system. But it doesn't

show the relationship between various entities that

exist in the sys. (*Comparison btw DFD and ERD)

Symbols used are : entities rectangles, r/shiplines

and diamonds

Another way that is suitable for ad hoc data request is by

creating a view which is an invisible table that does not exist in

physical terms

Database Software (DS)

Users create a computerized software by using database

software.

You may add, change and delete data in database

You may sort and retrieve data from database

Create form and report from data in database

DS contains Data Definition Language (DDL) to create and

populate database and describe the logical views of users.

It is used together with data dictionary.

Data Dictionary

It keep track of every data field in the database, how they are

formatted to into different types of records and how the record

types are integrated

Keeps track of where data field are used, who has access to

them etc

DS includes programs like :

DML (Data Manipulation Language)

Insert, delete and update database

DQL (Data Query Language)

Perform retrieving, sorting, ordering and presenting database in

response to users request

DQL Generally all users have access

DML and DDL Should be restricted to employees with

programming and admin responsibilities. Help limit people who

have capability to make changes to database

DS have programs called application generator make it easy for

user to retrieve info, to produce reports, and even build

transaction processing application w/out need of technical

specialist

DS also contain data mgt function such as backup

8/12/2019 Note Ais 630 Chp 11

2/6

How to draw ERD

1.Identify the entities

2.Add diamonds to represent important r/ships between the

entities

3.Specify the associations of these r/ship

How to read ERD (*read pg 162 to u/stand example)

ERDs and DFDs

DFD is used to model system flows while ERD is used to model

system data

Both system used to describe same system, must be

consistent in their use of system names

One model can be used to help develop the other model and

check whether the other model is complete

Most methodologies start with DFD and then use the data

stores and data flows to indicate major system entities for

ERD

Converting ERD into Relatinal Database

1.Create tables for each entity (table will become database file)

2.Identify attributes for each table (to create attri means to

create a field. U need to assign primary key to identify each

table. Thus, u need to specify data types n field)

Identify keys :

Primary keyspurpose is to uniquely identify a

record. Every record in file/table shud have it used

for accessing the record. Unique means it wud b

impossible to access any other record when u use

the key

Secondary keysA field used to identify a record but

it is not unique. Its selection will affect database

processing efficiency and information retrieval

Concatenated keysits a primary key in a table

that is made up of 2 primary keys that comes

from other table in the database.

*refer Exhibit 11.4 pg 165

Specify data types and field sizes

Types of info in a field is determined by its data type.

Numeric fields contain only numbers that can b

used to calculate totals and other mathematical

formulas. Date field only contain date values

NETWORK DESIGN

Distributed Information System

Each user department must be tied together w/in org

framework by communication tech in order to enable fast

and effective decision making

Ii is highly modular, employs independent but related

system arranged as a network. Dispersed over 2 or >

geographical location. Made possible by the Internet.

Client-Server Architecture

Advanced development in multi performing operating

system that is capable in working different type of

hardware made it possible fully connect/integrate

computer n other hardware

In a client server system, individual users use client-server

workstation normally PCs. Loxal/departmental server is

shared by few users with same computing needs. All users

share a central/corporate server

Intranet

Many org apply Internet tech into their own internal

networks. Intranet is an internal network that uses

internet tech making the orgs info accessible to

employees and facilitate working in groups.

It is essentially a small version of Internet exist w/in an

org.Users update info on intranet by creting and posting a

Web page that is similar to method used in internet.

Extranet

Org uses externet to allow customer/suppliers to access

part of it intranet system. Similar to intranet except it isdesigned to facilitate communication between 2 or > buss

partners

Eg. Shipping co allow customer to access their intranet to

print schedule pickups

Network cost

1.Network cost are very high

2.You need to perform cost-benefit analysis on network

infrastructure of an org.

3.As a general rule, cost of data communication increase

as distance rate increase and as max rate/capacity of

link increase. Designer have no control over distance

involved. Distance affect speed in terms of

productivity.

3. Normalize table to convert ERD into relational database

Normalizationa technique that organizes the database into

one of several normal forms to remove ambiguous r/ships btw

data n minimize data redundancy.

It allows complex r/ship btw entities to be simplified so that all

data for an entity can b hold in 2dimensional tables. Data MUST

be normalized before being stored in relational database.

Normalization process:

1.First Normal form (1NF)

2.Second Normal form (2NF)

3.Third Normal Form (3 NF) and so on

Database designer must ensure that tables are at its highest

normal form.

The forms constitute progression meaning table in 1NF better

than unnormalized table, while table in 2NF better than 1NF n

so forth.

Poorly designed database can cause operational problems :

1.Restricting/not allowing users to access the info needed.

2.Prevent insertion of records

3.Require users to do excessive updates

4.Unintentional deletion of data from table

Can result in unrecorded transactions and incomplete audit

trails.

Data Dictionary

You document structure of database in data dictionary. It is

usually maintained automatically by the database software.

It is a repository in database that contains data about data

(metadata). It contains database component parts and detailed

description such as field size, data type and uthorized users and

their access privileges.

8/12/2019 Note Ais 630 Chp 11

3/6

Evaluating Network (Questions)

Functional capabilities and limitations

1.How fast can network respond to instruction n

supply requested

2.How long will it likely continue operating w/out

errors or unplanned outages

3.How much does it cost per volume traffic or per time

period

Ease of use

1.

How easy it is to learn the instruction for using thenetwork n connecting the devices to it

2.How much effort required to become proficient in

using the network

Compatibility

1.To what extent does network conform to accepted

industry standards

2.To what extent does the network use the same

internal coding/external interface as other

network must with/substitute for

Maintainability

1.Is it possible to significantly increase/decrease

capacity w/out major disruptions

2.Is it possible to change important aspects of network

operation w/out major disruptions

Basic Connection Topology

Topology

Configuration of a network/ How computer is physically linked

to another computer. 3 types : star, ring, bus.

In determining which topology is to be used, you will need to

consider :

i. distance between computer

Ii. Frequency and volume of data transmission

Iii. Processing capability of each computer

Iv. Performance of the topo such as delay, speed reliability and

network capability to continue in case of sys failure.

Star Topology

Interconnect many nodes through central computer system ie a

server. When sending msg to another nodes, must be sent to

server first . Server will receive and retransmit the msg to

intended node

Communication Channels

1.Wired

Use physicals wires or cables to transmit the data or

information from one place to another. .

Transmission capacity depends on the distance and on

whether the medium is point-to-point or multipoint.

There are three types of wired channels:

Twisted Pair

Made of pairs of solid or stranded copper twisted alongand the most popular medium and easy to install. It is

also inexpensive and light weight.

It also support many different types of network,

supports the speed of 100mps and used most in LANs.

It is used for voice and text information transmission.

Disadvantages of twisted pair Relatively low bandwidth

and relatively slow in transmit the data or information

and subject to electromagnetic interference.

Coaxial

It is also made from copper but high transmission speed

than twisted pairs. It is used for voice, text

information and other type of information such as

image and video, so it is more expensive than twisted

pair. It contains two conductors that are parallel to

each other. The center conductors in the cable usually

copper.

Advantages : inexpensive, easy to install, easy to expand

and moderate level of EMI immunity, higher

bandwidth , much less susceptible to interference

than twisted pair.

Disadvantages : single cable failure can take down an

entire network and less susceptible to

electromagnetic interference , expensive over longdistance and bulky

Fiber Optic

Made up of thousands of very thin filaments of

fiberglass and use light as a digital information carrier.

It uses electrical signals to transmit data. Require a

light source with injection laser diode (ILD) or light-

emitting diodes (LED).

Advantages : faster in transmitting information, low

attenuation, no EMI interference. Greater bandwidth,

smaller size and lighter weight, highly secure difficult

to tap.

Disadvantages of fiber optic: Hard to install, expensive

over short distance, requires highly skilled installers,

and adding additional nodes is difficult.

Ring Topology

Each node is connected to an adjacent node in a circular fashion.

No central node. Msg are sent through the network.

Each node will examine the identification code msg and accepts

msg if it has the code. If it doesn't, then the process will

continue until msg reach the intended node.

Bus Topology

All nodes are attached to the bus. Data transmission from one

node is sent to every node in the network. Each node will

examine the identification code. It will accept msg containing its

code and ignore other msgs.

Hybrid Topology

Most networks are hybrid, , meaning they are combinations of

the three topologies.

Types of Network

1. LAN

2. WAN

LANA network that interconnects computers and communication

devices w/in an office/series of offices, distance between few

hundred meters to one km. Part of networks are linked by wired

or wireless.

It can use any/combination of topology mentioned earlier. Client

server application runs on LAN.

WAN

It is a network that interconnects sites located across states,

countries or continents. Info travel over great distances in WAN.

Large WANs are possible due to Internet, telephone lines,microwave relay towers and satellite. Internet is a WAN

WAN and LAN key differences :

1.Geographical area covered by WAN is larger, not limited to

one area.building

2.WAN send data over telecommunication data. LAN uses direct

cables

3.WAN use larger computer as file server

4.WAN is often larger than LANs, more terminal/ compute

linked to network

WAN usually use star topology in order to tightly control the

network

8/12/2019 Note Ais 630 Chp 11

4/6

8/12/2019 Note Ais 630 Chp 11

5/6

CONTROL DESIGN

Purpose of system control :

1.to make sure data is input,processed and output correctly

2.Prevent fraud and tampering off the computer system

Fraud

Intentional deception, manipulation of financial data or

misappropriation of org asset. By individuals for personal

benefits

Tampering

Entering false, fabricated or fraudulent data into computer

system or changing or deleting existing data.

As accountant, must ensure that planned controls are adequate

and auditable. As auditor, youll determine adequacy of internal

control

Design features allows several checks to be performed :

1. Programmed edit check

-editing automatically performed on entry of data into

computer. Can highlight actual and potential input errors and

allows them to be corrected quickly and efficiently

2. Mathematical accuracy od performed on this data where it

will compare calculation performed manually and done by

compare to determined if documents have been entered

correctly.

IS must have a security features to ensure safety and

confidentiality of info. System admin is responsible to format

authority level that the users could assess into the system

which will allow authorized person to enter into system.

Encompass data security, control and contingency planning.

Data Security

Controls to safeguard hardware and software. Unauthorized

access may result to loss such as fund embezzlement or

disclosure of sensitive data

Password

Security feature such as password can help in preventing

security breaches. Different password are required by different

users and possibly different operations even for same user.

It restrict access to only who know the password

First w/through checks for ommissions and inaccuracies. It

should detect flaws, weaknesses, errors and ommisions in

proposed design.

There may be more than 1 w/through in sys project. It is

crucial for effectiveness of w/through to be established as

a quality assurance tool.

Encryption

Used to encode so that copies of data cannot be made, taken to

another computer and accessed. To make unauthorized data

unreadable and interpretable

Prevent person who doesn't have encryption key from decoding

and understanding data.

Logging

Log any system users and what info, files and equipment they

use. Detection method of security system. Analysis of amount of

computer time used by a person shud also be considered

Data Control

It is a quality measure to ensure the accuracy of data entered

into the system. Data have to be authorized, recorded,

processed accurately in accordance to mgt policies to be

acceptable

It should be error free and reliable.

Processing controls ensure data are complete, valid and

accurate when being processed and those programs have been

properly executed. Processed in timely manner. Timeliness

means data are to be processed efficiently.

Captured data containing errors must be able to be detected

asap and be fully corrected

Data accepted/recorded by IS may be destroyed, misdirected,

corrupted or lost intentionally or unintentionally

Contingency Planning

It lays down procedure to be followed before, during and after

an emergency. It ensure continuity of operations and availability

of critical computing resources in the event of emergency

We cant rely on single large contingency plan. We must develop

several of them that address specific contingencies. It shud be

written down so it will be effective in case of emergency, kept insafe place, copies given to key managers and plant to be audited

periodically.

PERFORM A SYSTEM DESIGN WALKTHROUGH

When system design is complete, u need to do a walkthrough.

Purpose is to ensure that design is free from errors that cud be

programmed later on. It helps reduce cost of reprogramming

later.

Should be scheduled frequently during sys development so that

a manageable piece of work can be thoroughly reviewed in 1/2

hours. Begins when logicals model is completed.

8/12/2019 Note Ais 630 Chp 11

6/6

MICROSOFT ACCESS

ACCESS

A database software that organizes a collection of related info

used for specific purpose

Relational Database

One in which info is divided into separate stacks of logical info,

each of which is stored in a separate table in the file

You need :

TABLES

1.Enter all data in fields and group relevant data into records

2.R/ship among tables are key to success

3.Create table, Add records to table, Specifying r/ship based on

ERD, Print tables

FORMS

Purpose:

1.Provide a dialog box for access to predesign queries and

other forms of report.

2.Provide a dialog box to capture info from a user and use it to

carry out an action

3.Create form, Print form

Subforms

Simple form within forms. To exist, the info from one form must

have 1 : M r/ship with info in other form

QUERIES

1.Questions of the info provided in the database. Used to ask

questions of the data and retrieve a subset of info

available. Involve listing specific data from various tables2.Create Query, Print Query, Create Sum/Count/Cost Query

Querying selected records

To specify what records we are looking for can be done using

Character Data and Wildcards

Character based queries work well when u have a specific name

or set of characters

Queries using Wildcards allow that the user to find such things

as field value records or filenames. The asterisk (*) which

matches any number characters can be used as the first or last

character in character string

Create Validation Rules

Improves data integrity and validity. Checks info entered into a

database. and test the entry to see if it meets certain criteria.

If info enterd pass the test, entry is allowed. If not, a warning is

provided and entry is rejected.

Also provide specific feedback to user as to why the validation

test failed.

Performing Calculation

Can calculate sum of average of values in one field, multiply

values in to fields or calculate the date three months from

current date

REPORTS

It is a way for user to get info out of database in format that

helps the analytical process

Report need to be properly formatted/sorted/grouped

It may need chart, logo, subtotals/grand totals, date, special

title, headers or footers

It organize ur info according to specification that you set

5 steps to create a report

1.Identify which field u want in your report and in which tables

these fields are present

2.Specify group levels if any

3.Specify sort order if necessary

4.Specify report style