Chapter 21: System Analysis and Design, and Databases

The systems development life cycle

Information systems are built through a structured process. The systems development life cycle (SDLC) typically has these phases: planning and feasibility (technical, economic, operational, legal, schedule), requirements analysis, design, implementation, testing, deployment, and maintenance. Models include waterfall (sequential), prototyping, spiral, iterative/incremental, and agile methods (Sommerville, 2016). Waterfall suits well-understood requirements; agile suits changing requirements and close user involvement.

Requirements are gathered by interviews, questionnaires, observation, and document review, and written as functional (what the system does) and non-functional (performance, security, usability) requirements.

Modelling tools

Data flow diagrams (DFDs) show processes, data stores, external entities, and data flows. A context diagram (Level 0) shows the whole system as one process; lower levels decompose it.

Flowcharts and pseudocode describe algorithms.

Use case diagrams show actors and their interactions with the system.

Entity–relationship (ER) diagrams model data (see below).

Decision tables and trees capture business rules.

Testing and implementation

Testing includes unit, integration, system, and acceptance testing, as well as black-box (test inputs and outputs) and white-box (test internal paths) approaches. Implementation options for a changeover are direct, parallel, phased, and pilot. Documentation (user and technical) and training support adoption. Maintenance may be corrective, adaptive, perfective, or preventive.

Databases

A database is an organized collection of data; a DBMS (such as MySQL, PostgreSQL, Oracle, or SQL Server) manages it. Compared with file-based storage, databases reduce redundancy, protect integrity, support concurrent access, and enforce security.

The relational model stores data in tables (relations) of rows (tuples) and columns (attributes). Key terms: a primary key uniquely identifies a row; a foreign key refers to the primary key of another table; a candidate key is any minimal unique identifier.

ER modelling. Entities (Student, Course), attributes, and relationships with cardinality (one-to-one, one-to-many, many-to-many). A many-to-many relationship is resolved with a junction table whose key combines the two foreign keys.

Normalization removes redundancy and anomalies (insertion, update, deletion):

1NF: each field holds a single, atomic value; no repeating groups.

2NF: 1NF, and every non-key attribute depends on the whole primary key (no partial dependency).

3NF: 2NF, and no non-key attribute depends on another non-key attribute (no transitive dependency).

Example: a table Orders(OrderID, CustomerID, CustomerName, ItemCode, ItemName, Qty) has CustomerName depending on CustomerID and ItemName depending on ItemCode. Split into Customer(CustomerID, CustomerName), Item(ItemCode, ItemName), and OrderLine(OrderID, ItemCode, Qty) with Orders(OrderID, CustomerID).

SQL (Structured Query Language).

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Marks INT
);

INSERT INTO Student VALUES (1, 'Nimal', 78);

SELECT Name, Marks FROM Student WHERE Marks >= 75 ORDER BY Marks DESC;

UPDATE Student SET Marks = 80 WHERE StudentID = 1;

SELECT c.Name, COUNT(*) AS Orders
FROM Customer c JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.Name
HAVING COUNT(*) > 2;

Aggregate functions are COUNT, SUM, AVG, MIN, MAX. A JOIN combines rows from two tables on a related column. Transactions follow the ACID properties: atomicity, consistency, isolation, and durability. Good practice includes backups, access control, and the use of parameterized queries to prevent SQL injection.

Common mistakes

Putting several values in one field and calling the table normalized.

Forgetting the WHERE clause in UPDATE or DELETE (which changes every row).

Using the wrong key in a join, producing duplicate rows.

Practice questions

Explain the difference between a primary key and a foreign key.

Write an SQL query to find the average marks of students with marks above 50.

Normalize Results(StudentID, StudentName, SubjectCode, SubjectName, Marks) to 3NF.