Werabe University

Institute of Technology
Department of Information Systems

Fundamentals of System Analysis & Design

Chapter Four: System Design

1
• Information system design involves tasks focusing on the
specification of detailed computer based solution: also called
physical design to differentiate from data modeling, which
is basically a logical design
• System analysis emphasized the business-problem while the
system design focuses on the technical or implementation
concerns of the new system
• System design transform results of analysis to certain design
model accordingly:
Input to
DFD Architectural Design

Input to
E-R/data model Database Design
System
Requirement Input to
Specification (SRS) Interface Design (input/output)

2
 Cont’d...
Deliverables or outputs of system design:
 Architectural design
 Database design and
 Interface design
• Architectural Design: representation of the architecture of a software system
oThe common tool used here is system structure chart (SSC) which is
hierarchical diagram that shows how an information system’s organized
oIt is basically transformation of DFDs into a more procedural way
• Database Design: transformation of E-R diagrams and data requirement from
interface of reports and the like into data i.e. structure the data in stable
structure, called normalized table/relation
 Having minimal redundancy
 Not likely to change over time
oThe data objects and relationships defined in E-R diagram and detailed data
content depicted in the data dictionary provide the basis for the database design
activity.
• Interface Design: refers to the concept of designing how the software
communicates within itself (with systems that interoperate with it) and with
humans who use it. An interface implies flow of information and a specific type of
behavior.
3
 Cont’d…
• Logical and Physical Design: the design phase can be broadly classified in to logical
and physical design
• Logical Design: a stage where the “look and feel” of all system inputs, outputs and
interfaces and dialogues are defined
 Broadly speaking, it specifies means to capture inputs, give output and the interaction
between users and the system and how data should be organized and stored
 Specifically:
 Designing forms and reports (the inputs and outputs)
 Deigning interfaces and dialogues : the interaction
 Designing logical database (structure of the database) this is the conceptual data
modeling
Note: the above design list need not necessarily be sequentially performed.
• Physical Design (Architectural Design): designing the system and program structure:
• That means translating (transforming) the process and logic in DFD (that was
depicted in the logic modeling) into a form that can directly be used by programmers
• Important relationship between physical design and earlier stages (phases)
 Every function / process in DFDs must be transformed into program and program
module
 Program will accept or produce data through the forms and reports

4
 Cont’d…
Database Design: database and file design occurs in two
steps
1.Developing logical database model: describe organization
used by DBMS (Database Management System)- like Ms
Access, Oracle or SQL server, the most common is a
relational database model. Logical database design is
driver not only from E-R models, but also from form and
report layouts of the system.
2.Physical database design activities include
transformation of the logical database model into a
format for the selected DBMs and specify computer
programmers, database administrators, network
managers, and others to how to implement the new
information system.
5
 Logical Database Design
• As mentioned above, database design is transforming an E-R
model and their specifications design into a normalized
relation
• A relation is a named, two _ two _ dimensional tables of data,
having set of named columns and an arbitrary number of
unmade rows
• Each column in a relation corresponds to an attribute of that
relation and each row of a relation corresponds to a record
that contains data values for an entity
• Well structured relation corresponds to records that contain a
minimum amount of redundancy and allows users to insert,
modify and delete the rows without errors or inconsistencies
• It is the process of converting complex data in to simple, stable
and structures using a process called normalization
6
 Cont’d…
• Normalization: is a process of organizing the data in
database to avoid data redundancy, insertion anomaly,
update anomaly & deletion anomaly.
• Normalization: performed based on well accepted
principles and rules, there are three steps of
normalization
Un normalized  Normalized Relations  Second Normal  Third Normal Form
relations (1st Normal Form) Form relations (2 NF)
nd
relations (3rd NF)

• The result of normalizations is that every no primary

key attribute depends upon the whole primary key and
nothing but the primary
• Normalization is based on the analysis of functional
dependence 7
 Cont’d…
Functional dependence and primary keys
• Functional dependency is particular
relationship between two attributes, in a
given relation; attribute of B is functionally
dependent on attribute A if, for every valid
value of A, that value of A uniquely
determine the value of B.
• Example you can express the structure of
relation, by the following shorthand
EMPLOTEE (Emp_ID, Name, Dept, Salary)
8
 Cont’d…
• EMPLOYEE1 (with sample data)
Emp_ID Name Dept Salary

100 Margaret Simpson Marketing 42,000

140 Allen Beeton Accounting 39,000

110 Chris Lucero Inform. Systems 41,500

190 Lorenzo Davis Finance 38,000

• The functional dependence of B on A is

represented by an arrow as: AB (e.g.
Emp_IDName:- for a given Emp_ID value,
there can be only one Name values associated
with it)
9
 Cont’d…
• Consider the following relation (EMPLOYEE2) that contains data about employees and
the courses they have completed.

EMPLOYEE2

Emp_ID Name Dept Salary Course Date_Completed

100 Margaret Simpson Marketing 42,000 SPSS 6/19/2002
100 Margaret Simpson Marketing 42,000 Surveys 10/7/2002
110 Chris Lucero Inf. Systems 41,500 SPSS 1/22/2002
110 Chris Lucero Finance 41,500 C++ 4/22/2002
190 Lorenzo Davis Finance 38,000 Investment 5/7/2002
150 Susan M. Marketing 38,500 SPSS 6/19/2002
150 Susan M. Marketing 38,500 TQM 8/12/2002

• Notice: the redundancy in EMPLOYEE2, where Emp_ID, Name, Dept, and Salary
values for employees 100,110 and 150 appear in we must record this fact in two
rows. 
• The problem with this relation is that it contains data about two entities: EMPLOYEE
and COURSE

10
 Cont’d…
• In normalization, we divide EMPLOYEE2 into relations, EMPLOYEE1 (above)
and the following EMP COURSE relation having a primary key combining
Emp_ID and course.
• EMPCOURSE
Emp_ID Course Date_Completed
100 SPSS 6/19/2002
100 Surveys 10/7/2002
110 SPSS 1/22/2002
110 C++ 4/22/2002
190 Investment 5/7/2002
150 SPSS 6/19/2002
150 TQM 8/12/2002

• An attribute may be functionally dependent on two (or more) attributes,

rather than on a single attribute.
• Example: Emp_ID, courseDate_Completed. In this case, Date_Completed
cannot be determined by either Emp_ID or course alone, because the
Date_Completed is characteristic of an employee taking a course.
 
11
 Cont’d…
To get 1st normal form:
 Remove all repeating groups
 Identify primary keys
i.e. breaking or dividing the relation into two or more (like the case from EMPLOYEE2 to
EMPLOYEE1 and EMPCOURSE) 
Second Normal Form (2NF)
 A relation is 2NF if every no primary key attribute is functionally dependent on the
whole primary key, i.e. not on part, but on fullest of the primary key
 Remove partial dependencies to get 2NF relation
A 2NF is satisfied if any one of the following conditions apply
1. The primary key consists of only one attribute
2. No non primary key attribute exist in the relation
3. Every non primary key attribute is functionally dependent on the full set of primary
key attributes 
 Third Normal Form (3NF)
• A relation is in 3NF if it is in 2NF and there are no functional dependencies between
two (or more) non primary key attributes (a functional dependency between non
primary key attributes is also called a transitive dependency)
• Consider the following TREATMENT Relation
12
 
 Cont’d…
• Treatment

Patient_ID Patient_Name Physician Ward

1570 Anderson Hicks ICU
1575 Simith Faulb IPD
1460 Arnold Hicks ICU
1589 John Mark Medical
1560 Mike Faulb DPD

• Treatment is its 2NF because it has only one primary key attribute
• The following relational dependencies exist in the above relation
1. Patient_ID, Patient_Name, Physician, Ward (Patient_ID is the primary key)
2. Physician  ward (each physician is assigned to a unique ward)  transitive dependency
• A relation like the above create data maintenance problems and need to be changed in 3NF
• To change TREATEMENT relation in into 3NF, decompose it into two relations based on Patient_ID
and Physician as:
TREATMENT (Patient_ID, Patient_Name, Physician)
PHYSICIAN (Physcian, Ward)
• Physician is a foreign key in the treatment relation
• A foreign key is an attribute that appears as a non primary key attribute in one relation and as a
primary key attribute (or part of a primary key) in other relation.
• Sometimes, you may not need to go through all the steps of normalization, a relation may already be
in the 3NF at the 1st step.
13
 Cont’d…
Transforming E-R Diagrams into Relations
• Future information need of the system (the relations in the database) is derived
both from system input and output interfaces and the E-R diagrams developed
during conceptual data modeling.
• Transforming an E-R diagram into normalized relations and then merging all the
relations into one final, consolidated set of relations can be accomplished in four
steps.
1. Represent entities. Each entity type in the E-R diagram becomes a relation. The
identifier of the entity type becomes the primary key of the relation, and other
attributes of the entity type become non primary key attributes of the relation.
2. Represent relationships. Each relationship in an E-R diagram must be
represented in the relational database design; this may be by using a foreign key or
by creating a separate relation to represent a relationship.
3. Normalize the relations. Remove unnecessary redundancy from relations created
in steps 1 and 2 using normalization.
4. Merge the relations; all the relations created so far are gathered from sources like
E-R diagrams and user view, etc. So, there may be redundant relations (two or more
relations about the same entity type) that must be merged and renormalized to
remove the redundancy.
•  
14
 Cont’d…
Representing Relationships of E-R Diagrams
• This procedure depends on both the degree of the relationship (unary, binary,
ternary) and the cardinalities of the relationship.
• Binary 1:N (one-to-many) and 1:1 Relationships: add the primary key
attribute (or attributes) of the entity on the side of the relationship as a foreign
key in the relation that is on many side of the relationship.
• Binary and Higher-Degree M:N Relationships: if there is a binary many-to-
many (M:N) relationship (or associative entity) exists between two entity types A
and B we create a separate relation C. The primary key of C is a composite key
consisting of the primary key for each of the two entities in the relationship.
• Unary Relationship (Recursive relationship): this is a relationship between
the instances of a single entity type.
• For unary 1:N relationship (such as EMPLOYEE), entity is modeled as a
relation and has the identifier as primary key, then a foreign key is added to
the relation that references the primary key values.
• A recursive foreign key is a foreign key in a relation that for the above example:
EMPLOYEE (Emp_ID, Name, Birth_Date, Manager_ID)
• Manager_ID is a recursive foreign key that takes its value from the same set of
employee identification numbers as Emp_ID. 15
 Cont’d…
• E.g Emp_ID Name Birthdate

One-to-many relationships
EMPLOYEE

Manager

• For Unary M:N (many-to- many) relationship: we model the entity type as
one relation. Then we create a separate relation to represent the M:N
relationship. The primary key of this new relation is a composite key that
consists of two attributes (which need not have the same name) that both
take their values from the same primary key. Any attribute associated with
the relationship is included as a non key attribute in this new relation.
• Example: assume the following unary M:N relationship in the STUDENT
quantity type; A student may give a tutorial (tutor) for students.
• We can express the result for this E-R diagram as follows SUTORIAL
(Student_ID, Tutorial_ID, Subject) 16
 Physical Database Design
• The tasks we saw so far are activities of logical
database design
• Physical database design involves the following
activities
 Designing fields of tables: choosing data types,
set calculated (or computed or derived) fields,
setting default values, range controls, etc
 Designing physical tables in the database
• Activities here take into account the technology
required in processing, accessing etc.

17
 Interface Design
A good human computer interface provide a unifying structure for finding, viewing
and invoking the different components of a system
Interface design is basically
User focused activity
Employs (user) prototyping methodology
Interface may be designed to allow
Command based interactions
Menu based interaction
Object-based interaction: icon, symbols
Natural language interaction
• These are the means or methods by which users interact with the system. Points
to be considered in designing interfaces;
Flexibility: users should be able to move freely between fields
Consistency
Reduce data entry errors by structuring the data entry. Like by providing
default values, making clear the type of data of units required for entry.
Providing feedback, like status information to tell the user what is going on,
error and warning messages.
Designing help: help and context sensitive
Use simple grammatically correct sentences (forms) and be consistent with user
terminology. 18
 Cont’d…
Form and Report
• System inputs and outputs- forms and reports- were identified during requirement
structuring. The kinds of forms and reports the system will have were established
during the analysis phase.
• There are some guidelines you have to follow when designing forms and reports.
But first define what a form is and what a report is.
• A form is a business document containing some predefined data and often
includes some areas where additional data are to be filled in. Most forms have a
stylized format and are usually not in a simple rows and columns format. Usually
a form is based on one table of a database and provides some mechanism of
interaction with the user.
• On the other hand, a report is a business document containing only predefined
data; it is a passive document used solely for reading or viewing. Examples of
reports are invoices, weekly sales summaries by region and salesperson, and a pie
chart of population by age categories. Usually a report contains data from
multiple tables in a database.

19
 Cont’d…
Form and Report Design Deliverables
• The forms and reports design process has got three final deliverables, these are
1. Narrative overview
2. Sample design
3. Testing and usability assessment
1. Narrative overview
• The first section of a design specification for forms and reports contains a general
overview of the characteristics of the target users, tasks, system, and environmental
factors in which the form or report will be used. The purpose of this section is to
explain to those who will actually develop the final form, why this form exists, and
how it will be used so that they can make the appropriate implementation decisions.
2. Sample Design
• The sample design of a form can be hand-drawn as is customary these days using
CASE tools or standard form and report development tools used such as Oracle form
developer or Visual Basic.
• In fact, using actual development allows the design to be more thoroughly tested and
assessed. This means a prototyping approach is used for the design of forms and
reports.
• Following is an example of data input screen (a form) designed by Microsoft Visual
Basic.NET 20
Cont’d…

21
 Cont’d…
3. Assessing usability
• The objective for designing forms, reports and all human computer interactions is
usability.
• Usability typically refers to the following three characteristics:
1. Speed: can a task be completed efficiently (in a short time) using the form or report?
2. Accuracy: does the output provide what you expect?
3. Satisfaction: do users like using output? 
General guidelines for Formatting Forms and Reports
• Use clear and specific titles that describe the content and use of the form or report
• Current data which identifies when the form or report was generated should be included
• A valid data which identifies on what date (or time) the data in the form or report were
accurate should be included
• The form or report should display only the needed information
• Adequate spacing and margins should be used
• All data and entry fields should be clearly labeled
• The form or the report should shown where the user currently is in navigating the form
or report and how to move forward and backward
• The user should be notified when the last page of a multiple –paged sequence is reached
 
22
23