BESC104E/204E - Essentials of Information Technology

Module 4: Software Engineering

Topics
✓ The Software Engineering Discipline
✓ The Software Life Cycle
✓ Software Engineering
✓ Methodologies
✓ Modularity
✓ Tools of the Trade

Software Engineering

Software engineering is the branch of computer science that seeks principles to guide the
development of large, complex software systems. The subject is named such because software
development is an engineering process.

The goal of software engineering researchers is to find principles that lead to efficient, reliable
software products. The problems encountered in large systems are more than just scaled-up
versions of small programming problems, as they involve:

• The effort of more than one person over an extended period.

• Changing requirements of the proposed system.
• Changes in personnel assigned to the project.
• The discipline, therefore, includes topics related to business management, such as
personnel and project management.

4.1 The Software Engineering Discipline

The field faces unique challenges that distinguish it from other, older engineering disciplines:

• Lack of Prefabricated Components: Unlike traditional engineering (e.g., using "off-

the-shelf" engines in car design), software has historically lagged in using generic,
reusable components, often requiring complex systems to be built from scratch.
• Lack of Quantitative Metrics: There is a lack of quantitative techniques (metrics) for
measuring software properties like complexity or quality. For example, the metric
mean time between failures is not as applicable because software does not wear out.

Advancements in the Discipline:

• Practitioners work toward developing techniques for immediate application, while

theoreticians search for underlying principles and theories.
• Computer-Aided Software Engineering (CASE): The application of computer
technology to the software development process.

1
BESC104E/204E - Essentials of Information Technology

o CASE Tools are computerized systems that streamline development, including

project planning, project management, documentation, prototyping, simulation,
and programming systems.
o Integrated Development Environments (IDEs) combine tools for developing
software (editors, compilers, debugging tools, simulators, etc.) into a single,
integrated package.

4.2 The Software Life Cycle

The software life cycle is the most fundamental concept in software engineering. It is a cycle
of being developed, used, and maintained, which continues for the rest of the software's life.
Figure 7.1- represents the fact that once software is developed, it enters a cycle of being used
and maintained—a cycle that continues for the rest of the software’s life Such a pattern is
common for many manufactured products as well.

Figure 7.1 The software life cycle: Illustrates the continuous cycle of software after its creation, consisting of
three primary phases: Development, Use, and Maintenance.

• Maintenance Phase: This phase is typically a process of correcting or updating the

software, not just physical repair like in other products.
• Software enters maintenance because: errors are discovered, the software's
application requirements change, or previous modifications induced problems
elsewhere.
• The process requires a person (often not the original author) to study the program and
documentation until it is understood. Due to this difficulty, it is often in this phase that
the software is discarded in favor of a new system.
• Research in software engineering focuses on the Development stage, as effort here can
greatly simplify later modifications in the Maintenance stage.

The Traditional Development Phase

The major steps in the traditional software development life cycle are requirements analysis,
design, implementation, and testing.

2
BESC104E/204E - Essentials of Information Technology

Figure 7.2 The traditional development phase of the software life cycle: Shows the sequential flow of the
four major steps in the traditional, or waterfall, development process: Requirements Analysis -> Design ->
Implementation ->Testing.

Development Phases

Step1/Phase1-Requirements Analysis: The goal is to specify what services the proposed

system will provide, any conditions (time constraints, security), and how the outside world will
interact with it.

o It involves input from stakeholders (future users, legal/financial interests).

o The results are recorded in a document called a software requirements
specification (SRS), which serves as a written agreement to guide development.
o Poor communication and changing requirements are cited as major causes of
cost overruns and late delivery.

Step2/Phase2-Design: Involves creating a plan for the construction of the proposed system; it
is about developing a solution (how to do it).

o The goal is to establish the internal structure of the software system.

o The result is a detailed description of the system's structure, which can then be
converted into programs.

Step3/Phase3-Implementation: The actual writing of programs, creation of data files, and

development of databases.

o A software analyst (or system analyst) is involved with the entire process,
especially analysis and design, while a programmer is primarily involved with
implementation (writing programs to implement the design).

Step4/Phase4-Testing: Traditionally focused on debugging and confirming compatibility with

the SRS.

o Today, testing is part of the larger objective of quality assurance, which

permeates the entire life cycle.
o It is recognized that the result of each intermediate step (or artifact) in the
entire development process should be "tested" for accuracy.

3
BESC104E/204E - Essentials of Information Technology

4.3 Software Engineering Methodologies

The field of software engineering methodologies has evolved significantly since its early
days, moving from rigid, sequential processes to more flexible, adaptive approaches.

Sequential vs. Adaptive Models

Sequential Models Adaptive Models

Linear and step-by-step Flexible and iterative
Waterfall, V-Model Agile, Spiral, Incremental
Each phase is completed before the Phases overlap and evolve based on
next begins feedback
Rigid; difficult to accommodate Highly flexible; changes are easily managed
changes
Minimal after initial requirements Continuous involvement throughout the
phase project
Risks identified late, during testing or Risks identified and mitigated early through
deployment iterations
Collected at the end of the process Continuous feedback after each iteration
Product delivered only at the end Working software delivered in short cycles
Projects with well-defined, stable Projects with changing or unclear
requirements requirements
Heavy documentation required Light documentation; focus on working
product

Methodologies/Models:
1. Waterfall – Sequential and rigid.
2. Incremental – Build in parts.
3. Iterative – Refine through versions.
4. Prototyping – Evolutionary and Throwaway.
5. Agile & Open-Source Development

1. Waterfall Model

A linear, step-by-step approach where each phase must be completed before the next begins.
Key Features:
• Sequential flow: Requirements → Design → Implementation → Testing →
Deployment → Maintenance.
• No overlapping or returning to previous phases.
• Suitable when requirements are clearly defined and stable.
Advantages:
• Simple to understand and manage.
• Clear documentation and milestones.
Disadvantages:
• Inflexible to change.
• Errors found late in the process are expensive to fix.

4
BESC104E/204E - Essentials of Information Technology

2. Incremental Models

The system is developed and delivered in small, functional parts (increments). Each
increment adds more features until the full system is complete.
Key Features:
• Each increment passes through design, coding, and testing.
• Early versions deliver basic functionality, with improvements added later.
• Useful when the requirements are partially known.
Advantages:
• Early delivery of working software.
• Easier to manage risk and changes.
Disadvantages:
• Requires good planning for integration of increments.
• Architecture must support incremental development.

3. Incremental Model

The software is developed through repeated cycles (iterations), with each version being
improved based on feedback and testing.
Key Features:
• Focuses on refinement — each iteration improves the previous one.
• Requirements and solutions evolve with feedback.
• Encourages continuous improvement.
Advantages:
• Early detection of issues.
• Adaptable to changing requirements.
Disadvantages:
• Requires continuous customer involvement.
• Can be difficult to manage progress tracking.

4. Prototyping

A prototype (sample or mock-up) of the system is built to understand requirements better.

There are two types:
• Evolutionary Prototyping: The prototype is gradually refined into the final product.
• Throwaway Prototyping: The prototype is discarded after requirements are
understood; the final system is built separately.
Key Features:
• Focus on user interaction and feedback.
• Helps clarify unclear or evolving requirements.
Advantages:
• Reduces risk of misunderstanding user needs.
• Provides a visual model early in development.
Disadvantages:
• Can lead to scope creep if not managed.
• May cause confusion if the prototype is mistaken for the final product.

5
BESC104E/204E - Essentials of Information Technology

5. Agile and Open-Source Developments

Agile methods are iterative, flexible, and customer-focused approaches to software

development. They emphasize small, incremental releases, continuous feedback, and
collaboration between the development team and customers.
Key Features:
• Iterative Development: Work is divided into short cycles called iterations or sprints.
• Customer Collaboration: Continuous feedback from users or clients.
• Adaptability: Easily accommodates changes in requirements.
• Working Software: Delivered frequently in usable increments.
• Teamwork: Encourages self-organizing and cross-functional teams.
Popular Agile Frameworks:
• Scrum – Uses sprints, daily stand-up meetings, and defined roles (Scrum Master,
Product Owner).
• Kanban – Visual board to track workflow and limit ongoing tasks.
• Extreme Programming (XP) – Focus on coding practices, testing, and customer
feedback.
Advantages:
• Quick and frequent delivery of working software.
• Highly flexible to changes.
• Increases customer satisfaction and communication.
Disadvantages:
• Requires experienced, collaborative teams.
• Difficult to estimate cost and time precisely.
• Documentation may be minimal.

Open-Source Development

This is a less used, incarnation of incremental and iterative ideas. An initial version is posted
online (e.g., the Linux operating system), and other users can download, modify, and enhance
the source code. These changes are reported back to the original author and incorporated into
an extended version, leading to rapid, community-driven evolution.

4.4 Modularity

6
BESC104E/204E - Essentials of Information Technology

Modularity is the division of software into manageable units, or modules, each of which deals
with only a part of the software’s overall responsibility.

Modular Implementation
• Imperative Paradigm: Modules appear as functions. Design focuses on representing
the overall task as individual, manageable processes. Refer: Figure 7.3 – Which
Represents the modular structure in the imperative paradigm. Functions are
rectangles, and arrows indicate function dependencies (function calls/control coupling).
It shows ControlGame calling Serve, Return, ComputePath, and UpdateScore.

• Object-Oriented Paradigm: Modules appear as objects. Design focuses on

identifying the objects in the system and understanding their interactions. Refer:
Figure 7.4 – Which Represents the modular structure in the object-oriented paradigm.
It shows the Class PlayerClass and its internal parts (Attributes: skill, endurance and
Methods: serve, returnVolley), and its Objects (instances) PlayerA and PlayerB. and Figure
7.5 – Which Depicts a sequence of communication events between objects (PlayerA,
PlayerB, Judge, Score). Events are chronological as one moves down the diagram, showing
method calls like PlayerA calling evaluateServe in Judge.

7
BESC104E/204E - Essentials of Information Technology

Coupling
• Definition: The linkage between modules (intermodule coupling).
• Goal: To minimize coupling to maximize independence among modules. This ensures
changes in one module are less likely to affect others.
• Control Coupling: A form of coupling that occurs when one module passes control of
execution to another, as in a function call.

Cohesion
• Definition: The internal binding, or the degree of relatedness of a module’s internal
parts (intramodule cohesion).
• Goal: To maximize cohesion. High cohesion ensures that if a change is needed, all the
necessary modifications are likely contained within that single module.
• Logical Cohesion: A weak form where internal elements perform activities that are
logically similar (e.g., all functions within a communication module). Entire objects in
OOP are typically only logically cohesive.
• Functional Cohesion: A stronger form where all parts of the module are focused on
the performance of a single activity. Software designers strive to make individual
methods within an object functionally cohesive. Refer: Figure 7.7 – Which Shows a
single Object containing multiple methods (Perform action A, B, C). The object as a whole
is described as only logically cohesive, but each individual method within it is to be
functionally cohesive.

8
BESC104E/204E - Essentials of Information Technology

Information Hiding
• Definition: The restriction of information (including data, data structures, and
internal composition) to a specific portion of a software system.
• Goal: To prevent modules from having unnecessary dependencies on or effects on
other modules, protecting against corrupted data or future malfunctions if internal
structure is altered.
• It is the concept of treating a module as a "black box" (abstraction). It is achieved by
design goals (maximizing cohesion, minimizing coupling) and implementation goals
(using local variables, encapsulation).

Components
• Definition: A reusable unit of software; an object is a special case of a component.
Most are a collection of one or more objects that function as a self-contained unit.
• Component Architecture (Component-Based Software Engineering): A field where a
component assembler constructs systems from prefabricated components (often
displayed as icons) by connecting and minimally customizing them.

4.5 Tools of the Trade

Dataflow Diagrams: Represent information about how data moves through a system. Arrows
represent data paths, ovals represent points of data manipulation (processing), and rectangles
represent data sources and stores. They are useful in the analysis stage for improving
communication between clients and software engineers. Figure 7.8 - displays an elementary
dataflow diagram representing a hospital’s patient billing system.

Data Dictionary: A central repository of information about the data items used throughout a
software system. Includes the item's identifier, valid entries (e.g., data type, range of values),
storage location (file/database), and references (which modules use it). To establish uniformity
and reveal redundancies or contradictions in terminology across the system.

Unified Modeling Language - UML

A modern collection of tools developed with the object-oriented paradigm in mind.

✓ Use Case Diagram

✓ Class Diagram
✓ Generalization (Inheritance)
✓ Sequence Diagrams (Interaction Diagrams)
✓ CRC (Class-Responsibility-Collaboration) Cards

• Use Case Diagram: Depicts the proposed system from the user's point of view.

9
BESC104E/204E - Essentials of Information Technology

o Notation: The system is a large rectangle. Interactions (use cases) are ovals.
Users (actors) are stick figures.
o Figure 7.9 - indicates that the proposed Hospital Records System will be used
by both Physicians and Nurses to Retrieve Medical Records.

• Class Diagram: A notational system for representing the structure of classes and the
relationships (associations) between them.
o Notation: Classes are rectangles, and associations are lines. Arrowheads can
be used to indicate the reading direction of a label (e.g., cares for).
o Multiplicity: Used to indicate how many instances of one class may be
associated with another, recorded at the ends of association lines (e.g., 1, 0..1, or
* for an arbitrary nonnegative number).
o Figure 7.10 - shows how the relationships among these classes could be
represented in a UML class diagram. Classes are represented by rectangles and
associations are represented by lines. Association lines may or may not be
labeled. If they are labeled, a bold arrowhead can be used to indicate the
direction in which the label should be read.

The associations (relationships) between classes, often shown on a Class Diagram (Figure
7.10), occur in three basic forms:

• One-to-One: Each instance of one class is associated with only one instance of the
other, and vice versa (e.g., a patient and an occupied private room).
• One-to-Many: One instance of a class can be associated with many instances of the
second, but each instance of the second is associated with only one of the first (e.g., one
primary physician to many patients).
• Many-to-Many: Instances of both classes can be associated with many instances of
the other (e.g., patients and consulting physicians).

10
BESC104E/204E - Essentials of Information Technology

Figure 7.11 - Summarizes the basic forms of association multiplicities in class diagrams: one-
to-one, one-to-many, and many-to-many relationships.

Generalization (Inheritance): Generalization is a concept in object-oriented design where

one class represents a more specific version of another (e.g., a child/subclass is a generalization
of a parent/superclass). It is typically implemented using inheritance.

• UML Notation: A generalization is represented by an arrow with a hollow

arrowhead pointing from the specific class (subclass) to the general class (superclass).
• Internal Class Structure: Class rectangles contain the name, attributes, and
methods in the format introduced in Figure 7.4.
• Inheritance vs. Coupling: While inheritance is the natural implementation, it
introduces a strong degree of coupling between classes. Changes in the parent class
are automatically reflected in all children, which can lead to unforeseen consequences
during maintenance.
• Principle: Inheritance should be restricted to cases where the generalization is
immutable (unchangeable), not used merely for programming convenience.

Figure 4.12 - A generalization relationship: Depicts the generalization among the classes
MedicalRecord, SurgicalRecord, and OfficeVisitRecord. MedicalRecord is the general class, and the
specific classes (SurgicalRecord and OfficeVisitRecord) inherit its features while adding specialized
attributes/methods (e.g., SurgicalRecord adds surgical procedure).

11
BESC104E/204E - Essentials of Information Technology

Sequence Diagrams (Interaction Diagrams): Sequence Diagrams are a type of Interaction

Diagram in UML that represents the dynamic features of a program by depicting the time-
ordered sequence of communication between individuals (objects or components) during the
performance of a task.

• Lifelines: Individuals are represented by a rectangle with a dashed line extending

downward, known as a life line.
• Communication:
o Request: Labeled arrows connect life lines chronologically from top to bottom,
indicating an action being requested.
o Return: An unlabeled arrow pointing back to the original life line indicates the
completion of a task and return of control.
• Sequence Diagram Syntax (Figure 7.13):
o The entire diagram is enclosed in a frame.
o The top-left contains sd (sequence diagram) followed by an identifier (e.g., the
method initiating the sequence).
o Objects are of a certain type (e.g., PlayerClass), with one designated self to initiate
the sequence.
• Interaction Fragments: Inner rectangles used to capture variations in a single diagram:
o loop: Represents repetition (a while structure).
o alt: Represents alternatives (an if-else structure).

Figure 7.13 - A formal sequence diagram: Depicts a general volley in the tennis game design,
showing the communication between player objects and the Judge object. It demonstrates the
formal syntax, including the frame, lifelines, and interaction fragments (loop and alt) to
represent complex, indefinite processes

CRC (Class-Responsibility-Collaboration) Cards: A CRC Card is a simple index card on

which the description of an object is written.

The methodology involves software designers producing a card for each proposed object and
then using the cards to simulate the system's execution—a process known as a structured
walkthrough. This simulation is crucial for validating object-oriented designs and
identifying flaws before implementation.

12
BESC104E/204E - Essentials of Information Technology

Design Patterns

A design pattern is a predeveloped, high-quality model for solving a recurring problem in

software design.

• Goal: To find high-quality solutions that provide flexibility later in the software life
cycle by adhering to good design principles (minimizing coupling and maximizing
cohesion).
• Examples:
o Adapter Pattern: A standard approach for "wrapping" a prefabricated
module inside another module to translate an incompatible interface, allowing
the original module to be used.
o Decorator Pattern: A standardized way to design systems that perform
different combinations of the same activities without creating overly complex
software.
• Impact: Design patterns have been incorporated into modern software development
toolkits (like Java and .NET frameworks) as templates, providing ready-made, high-
quality solutions to common design problems.
• Origin: The concept was inspired by the work of Christopher Alexander in traditional
architecture.

13
BESC104E/204E - Essentials of Information Technology

Module4 : Database Systems

Topics
✓ Database Fundamentals
✓ The Relational Model.

4.1 Database Fundamentals

A database is a system that converts a large collection of data into an abstract tool, allowing
users to search for and extract pertinent information in a convenient manner. It is a collection
of data that is multidimensional, in that internal links between entries make the information
accessible from a variety of perspectives, unlike a traditional flat file system. Database systems
emerged as a means of integrating information stored and maintained by an organization.

1. Significance of Database Systems

• Integration and Efficiency: Historically, applications used separate files (e.g., payroll
file, inventory file), which led to significant data duplication and separate but related
items being stored apart. Database systems resolve this by integrating information into
a single pool.
• Versatile Use: The same integrated data can be used for multiple applications
simultaneously, such as generating restocking orders, creating reports on market trends,
directing advertisements, and generating sales force bonus checks.
• Strategic Management Tool: Database technology, often combined with data mining
techniques, allows management to extract valuable, pertinent information from
enormous amounts of data for decision-making.
• Web Foundation: Databases are the underlying technology for many popular World
Wide Web sites (like Google, eBay, and Amazon), where the server interrogates a
database to organize results into a Web page for the client. In some cases, the database
becomes the company's core product.

14
BESC104E/204E - Essentials of Information Technology

2. The Role of Schemas and Subschemas

• Access Control: The ability to control access to information is vital to prevent sensitive
data from being seen by unauthorized personnel (e.g., a website customer vs. an
employee in the financial department).
• Schema (Entire Database): A description of the entire database structure used by
the database software to maintain the database. It details all data items and their logical
linkages (e.g., student record linked to faculty adviser record).
• Subschema (User View): A description of only that portion of the database pertinent
to a particular user's needs. For instance, a registrar's subschema might exclude a faculty
member's employment history, while the payroll department's subschema would
include it but exclude student-adviser linkages.

3. Database Management Systems (DBMS) and Data Independence

• Conceptual Layers: Database applications have two major software layers: the
application software (handles communication with the user) and the DBMS (performs
the actual database manipulation, searches, and alterations).
• Benefits of Separation:
o Abstraction: The DBMS acts as an abstract tool, isolating the application
software from the complexities of actual data storage (e.g., whether the database
is centralized or a distributed database across a network).
o Access Enforcement: By dictating that the DBMS performs all access, the
DBMS can enforce the restrictions imposed by the various subschemas.
o Data Independence: This is the ability to change the organization of the
database itself (like adding a new field to an employee record) without requiring
changes to the application software of users whose subschemas remain
unaltered.

4. Database Models

A database model is a conceptual framework that defines how data is logically structured,
stored, and accessed within a database system. It uses abstraction to hide the complexities of
physical data storage, presenting users with an organized and meaningful view of information.
The relational model represents data in tables with rows and columns, while the object-
oriented model stores data as objects that combine both data and behavior. The DBMS
provides routines that translate user commands into actions on the actual stored data, allowing
applications written in general-purpose programming languages to easily interact with the
database. Continuous advancements in database models aim to simplify complex data
representation, make querying more intuitive, and improve the overall efficiency of data
management systems.

15
BESC104E/204E - Essentials of Information Technology

4.2 The Relational Model

The relational database model represents data in the form of rectangular tables known as
relations, similar to how data appears in spreadsheet programs. Each table stores information
about a specific entity, such as employees in a company.

• A single row in a relation, representing a record or entity (e.g., information about a

particular employee) this is Tuple
• A column in a relation, describing a characteristic of the entity represented by the tuple
(e.g., name, address, SSN) this is Attribute

Figure 9.3 is Structure of a Relation

Issues and Resolution in Relational Design

• Problematic Design (Combining Concepts): Combining too many distinct concepts

(e.g., employee details, job details, and assignment details) into a single large relation
leads to problems.
• Redundancy (Inefficiency): Information is repeated across multiple tuples; for
example, if an employee holds several jobs, their personal information (name, address)
is repeated in every corresponding tuple. Figure 9.4 ia A relation containing
redundancy.

16
BESC104E/204E - Essentials of Information Technology

• Deletion Anomaly (Loss of Data): Deleting a tuple (e.g., an employee leaving the
company) can cause the unintentional loss of information about a related concept (e.g.,
details about a job if that employee was the only holder of it).
• Solution via Decomposition: The solution is to separate the concepts into different
relations (e.g., EMPLOYEE, JOB, and ASSIGNMENT).
o Information can still be implicitly obtained by combining data from different
relations (e.g., finding an employee's department by linking ASSIGNMENT to JOB
via Job Id).
• Lossless Decomposition: A successful decomposition is a lossless decomposition,
meaning no information is lost by dividing the relation, and the original information
can be fully recovered by combining the new relations.

Relational Operations

The DBMS provides routines to perform these three core operations, which form the basis for
data manipulation in the relational model:

• SELECT (Tuples/Rows): Extracts a new relation consisting of the tuples (rows) from
the parent relation that possess certain specified characteristics.
o Syntax: NEW ← SELECT from EMPLOYEE where EmplId = '34Y70'.
• PROJECT (Attributes/Columns): Extracts a new relation containing only the
specified attributes (columns) from an existing relation. Duplicate rows are typically
removed.
o Syntax: MAIL ← PROJECT Name, Address from EMPLOYEE.
• JOIN (Combining Relations): Combines two different relations into a single new
relation. Tuples from the original relations are concatenated to form the new tuples, but
only if they satisfy a specified condition (e.g., equality between designated attributes).
o Syntax: NEW1 ← JOIN ASSIGNMENT and JOB where [Link] =
[Link].

These challenges occur because the relation mixes multiple concepts—employee details, job
information, and job assignments—into one table.
To resolve these issues, the database should be redesigned into separate relations: an
EMPLOYEE relation for personal details, a JOB relation for job descriptions, and an
ASSIGNMENT relation to link employees to their jobs. This approach eliminates redundancy,
prevents data loss, and improves database efficiency and integrity. This is shown in Figure 9.5

17
BESC104E/204E - Essentials of Information Technology

Figure 9.6: Finding the Departments in Which Employee 23Y34 Has Worked. This diagram
illustrates the retrieval process when data is decentralized (as in Figure 9.5), effectively
demonstrating the conceptual use of the JOIN operation.

The user must:

1. Search the ASSIGNMENT relation for Empl Id 23Y34 to find their Job Ids (S25X
and S26Z).
2. Search the JOB relation using those Job Ids.
3. The result is the corresponding departments: Personnel and Accounting.

18
BESC104E/204E - Essentials of Information Technology

Dividing data into multiple tables in a database is not always simple and may sometimes cause
loss of information. For example, if employee details like ID, job title, and department are
split into separate tables, it can become unclear which department an employee belongs to.
When the division keeps all information intact, it is called lossless decomposition; when some
information is lost, it is lossy decomposition. Hence, database designers must be careful to
divide tables properly to avoid data loss and ensure the design remains efficient and accurate.

Figure 9.7: A Relation and a Proposed Decomposition

Relational operations are used to extract and manipulate data stored in tables (relations) in a
database. These operations help retrieve specific information based on certain conditions. For
example, to find details of a particular employee, we select the tuple (row) with the matching
employee ID from the EMPLOYEE table. Similarly, to list all job titles in a specific
department, we select tuples from the JOB table where the department matches the given name.
The result of such a selection is a new relation that contains only the tuples satisfying the given
condition.

This process is represented as:

NEW ← SELECT from EMPLOYEE where EmplId = '34Y70'

The operation helps in filtering data, making it easier to view and analyze only the required
information.

The SELECT and PROJECT operations are two fundamental relational operations used to
retrieve specific data from a database.

SELECT Operation: Used to extract specific rows (tuples) from a table based on a given
condition.

• Example: NEW ← SELECT from EMPLOYEE where EmplId = '34Y70' (Figure 9.8).
• It filters data and creates a new relation with only the matching records.

19
BESC104E/204E - Essentials of Information Technology

PROJECT Operation: Used to extract specific columns (attributes) from a table.

• Example: NEW2 ← PROJECT JobTitle from NEW1 or MAIL ← PROJECT Name,

Address from EMPLOYEE
• It helps display only the required fields from the relation.

20
BESC104E/204E - Essentials of Information Technology

In a JOIN operation, a new relation is created by combining (concatenating) tuples from two
existing relations based on a specified condition. The tuples that are joined depend on whether
certain attributes in both relations have the same value.

For example, in the statement C ← JOIN A and B where A.W = B.X,

a tuple from relation A is combined with a tuple from relation B only when the value of attribute
W in A matches the value of attribute X in B. Hence, the tuple (r, 2) from A joins with (2, m,
q) from B because the values of W and X are equal. However, (r, 2) from A and (5, g, p) from
B are not joined since their attribute values do not match.

SQL (Structured Query Language)

Definition: SQL stands for Structured Query Language and is used to manage and manipulate
data in relational databases.

It allows users to interact with a DBMS (Database Management System) without worrying
about the details of data storage.

SQL is used to retrieve, insert, update, and delete data from databases.

Although SQL statements look imperative, they are actually declarative—users describe what
data they want, not how to get it.

SQL is standardized by ANSI and widely supported by systems like MySQL, Oracle, and
PostgreSQL.

Components:

• SELECT clause – specifies the columns (attributes) to display (similar to PROJECT).

• FROM clause – identifies the tables (relations) involved.
• WHERE clause – defines conditions to filter rows (similar to SELECT operation).

21
BESC104E/204E - Essentials of Information Technology

For example, consider a query that retrieves all employee IDs and their corresponding
departments. Instead of performing three separate relational operations (JOIN, SELECT, and
PROJECT), this can be written in SQL as:

SELECT EmplId, Dept

FROM Assignment, Job
WHERE Assignment. JobId = [Link]
AND Assignment. TermDate = '*';

In SQL, every query typically includes three main clauses:

1. SELECT – Specifies which columns or attributes to display (similar to PROJECT).

2. FROM – Specifies the tables or relations from which data is retrieved.
3. WHERE – Defines the condition that must be met to include rows (similar to SELECT
operation in relational algebra).

Examples:

SQL Query Example1

SELECT Name, Address

FROM Employee;

→ Displays all employee names and addresses. (Equivalent to a PROJECT operation.)

SQL Query Example2

SELECT EmplId, Name, Address, SSNum

FROM Employee
WHERE Name = 'Cheryl H. Clark';

→ Displays all details of the employee named Cheryl H. Clark. (Equivalent to a

SELECT operation.)

SQL Query Example3

UPDATE Employee
SET Address = 'New Address'
WHERE Name = 'Joe E. Baker';

→ Updates the address of the employee Joe E. Baker in the Employee table.

22