Chapter 5: System Design

Information system design involves tasks focusing on the specification of a 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 system design focuses on the technical or
implementation concerns of the new system.

System design transform results of analysis to a certain design model, accordingly:

Deliverables: architectural design, database design and interface designs.

Architectural design: representation of the architecture of a software system.

 The common tool used here is system structure hart (SSC) which is a hierarchical
diagram that shows how an information system is organized (Read more about SSC)
 It is basically transformation of DFDs into a more procedural way.
Database Design: transformation of E-R diagrams and data requirement from interfaces of reports
and the like into data i.e. structure the data in stable structure, called normalized tables/ relation
Having minimal redundancy
Not likely to change over time.

• The data objects and relationships defined in the 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.

Logical and physical Design: The design phase can be broadly classified into logical and physical

designs.

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 & outputs)
 Designing 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.
Programs will accept or produce data through the forms and reports.

Database Design: Database and file design occurs in two steps:

i. Developing logical database model: describe organization used by a DBMs (database

management System) – like Ms Access, Oracle, or SQL server, the most common is a relational
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.
ii. Physical database design activities include transformation of the logical database model into a
format suitable for the selected DBMs and specify computer programmers, database
administrators, network managers, and others on how to implement the new information
system.

Logical database design

As mentioned above, database design is transforming an E-R model and their data 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 unnamed rows, Each column in a relation corresponds to an
attribute of that relation and each row of a relation corresponds to a records 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 structures into simple, stable data structures using a process called normalization.

Normalization: performed based on well-accepted principles and rules,

There are three steps of normalization.

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.

Functional dependence and primary keys

Functional dependency is a particular relationship between two attributes, in a given relation,

attribute B is functionally dependent on attribute A if, for every valid value of A, that value of A

uniquely determines the value of B.

Example: you can express the structure of a relation by the ff shorthand: EMPLOYEE1 (Emp-1p,

Name, Dept, Salary)

EMPLOYEE 1 (with sample data)

The functional dependence of B on A is a represented by an arrow as: A Æ B (eg. Emp-ID Æ

Name: - for a given Emp-ID value, there can be only one Name value associated with it).

Consider the following relation (EMPLOYEE2) that contains data about employees and the courses

they have completed.

EMPLOYEE 2

Notice the redundancy in EMPLOYEE 2, where Emp-IP, 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 if contains data bout two entities: EMPLOYEE and course In
normalization, we divide EMPLOYE 2 into two relations, EMPLOYEE 1 (above) and the

following EMP course relation having a primary key combining Emp-ID and course.

EMP COURSE
An attribute may be functionally dependent on two (or more) attributes, rather than on a single attribute,

Examle, Emp-ID, course  Date_completed. In this case, Date_completed can not be determined

by either Emp-ID or course alone, because the Date_completed is characteristic of an employee

taking a course.

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

Second Normal Form (2NF)

A relation is in 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 3 NF 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.

Treatment

Treatment relation is its 2NF because it has only one primary key attribute.
The following functional dependencies exist in the above relation
 1. patient_ID patient_Name, physician, ward (patient_Id is the primary key)

2. Physical 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 into 3NF.
To change TREATMENT relation it into 3NF, decompose it into two relations, based on
patient_ID and physician as:

TREATMENT1 (patient_ID patient_Name, physician)

PHYSICIAN (Physician, 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 (o r part of a primary key) in another relation.

Sometimes, you may not need to go through all the steps f normalization, i.e. a relation may

already be in the 3NF at the 1st step.

Transforming E-R Diagrams into Relations

Future information need of the system (the relations in the database) are derived both from system input
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 becomes primary key of the relation, and other attributes of the entity 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 foreign keys 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 views, etc. so, there may be redundant relations (two or more relations about the same
entity type) that must be merged and renormalize

Representing relationships of E-R diagrams

This procedure depends both on 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 (many-to-many) Relationships: if there is a binary many-tomany
relationship (or an associative entity) between entities types A and B, we create a
separate relation C. the primary key of C is a composite key consisting f the primary key for
each of the two entities in the relationship.
 Unary Relationships (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.

Eg

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 nonkey 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 (stude_ID, Tutorial_ID, Subject)

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 the data types, set calculated (or computed or derived)
fields, setting default values, range controls, etc.
 esigning physical tables in the database.
Activities here take into account the technology required in processing, accessing etc.

Interface Design
 A good human computer interface provides a unifying structure for finding, viewing and invoking
the different components of a system.
Interface design is basically
o User focused activity
o Employs (user) prototyping methodology
Interface may be designed to allow:
o Command based interactions
o Menu based interaction
o Object_based interaction: icons, symbols
o Natural language interaction

These are the means or methods by which users interact with the system. Points to be considered in

designing interfaces;

o Flexibility: users should be able to move freely between fields

o Consistency
o 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.
o Providing feedback, like status information to tell the user what is going on, error and
warning messages.
o Designing help: general and context sensitive help
o Use simple grammatically correct sentences (forms) and be consistent with user
terminology.

Form and Report Design

System inputs and outputs- forms and reports- were identified during requirements structuring. The kinds
of forms and reports the system will have were established during the analysis phase.

However, the precise appearance of forms and reports may not have been specified at the analysis
phase.Forms and reports are integrally related to various diagrams developed during requirements
structuring. For example, every input form will be associated with a data flow entering a process on a DFD
and every output form or report will be a data flow produced by a process on a DFD. This means that the
contents of a form or report correspond to the data elements contained in the associated data flow.

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 stylized format and are usually not in a simple row and
column format.

Following is an example of a form from SMUC’s Student Record Management information system.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 from various information systems include invoices,
weekly sales summaries and a pie chart of population by age categories.
Usually a report contains data from multiple tables in a database.

Form and Report Design Deliverables

The forms and reports design process has got three final deliverables, these are:

1. Narrative overview

2. Sample design; and

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 or report 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 or as is customary these days using CASE tools or standard
form and report development tools such as Oracle form developer or Visual Basic. In fact, using actual
development tools 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 a data input screen (a form) designed using Microsoft Visual Basic.NET
3. Assessing Usability

The objective for designing forms, reports and all human-computer interactions is usability.

Usability typically refers to the following three characteristics:

i. Speed- can a task be completed efficiently (in a short time) using the form or report?

ii. Accuracy- does the output provide what you expect?

iii. Satisfaction- do users like using the output?

In general the design of forms and reports should be in such a way that it assists user performance than
hindering it.

General Guidelines for Formatting Forms and Reports

Following are some guidelines that help in making a form or report “user friendly”:

o Use clear and specific titles that describe the content and use of the form or report
o Current data which identifies when the form or report was generated should be included
o A valid data which identifies on what date (or time) the data in the form or report were accurate
should be included
o The form or report should display only the needed information
o Adequate spacing and margins should be used
o All data and entry fields should be clearly labeled
o The form or report should show where the user currently is in navigating the form or report
and how to move forward and backward
o The user should be notified when the last page of a multi-paged sequence is reached