Define basic algorithms

Define basic algorithms

Department of Information Technology Page 1 of 3
ICON College of Technology and Management
Pearson BTEC HND in Computing (RQF)
Unit 1: Programming (L4)
Schedule of Work
This unit is aimed at achieving the following learning outcomes:
LO1. Define basic algorithms to carry out an operation and outline the process of programming an
application.
LO2. Explain the characteristics of procedural, object-orientated and event-driven programming,
conduct an analysis of a suitable Integrated Development Environment (IDE).
LO3. Implement basic algorithms in code using an IDE.
LO4. Determine the debugging process and explain the importance of a coding standard.

Session Indicative Contents Activity
Week 1
LO1
introduction: Unit learning outcome , Syllabus, and Assignment
briefing Reading, Academic Skill, citation and referencing , group
discussion
Introduction to Programming
Lecture
Academic writing
Week 2
LO1
An introduction to algorithms; what is an algorithm? Defining an
algorithm; purpose and structure and the outline of a program.
• Present a series of problems and the steps to solve them via
brute force and then with an algorithm, such as searching
through records.
• An overview – programming is:
o the analysis of the scenario/problem
o defining a specification
o identifying input, process and output
testing/debugging.
A look at the most common algorithms and their application.
• Examine a range of algorithms for various purposes including:
o sorting algorithms
o encryption/decryption.
• Analysing efficiency: their performance against brute force.
• Cost of running time, acknowledging hardware performance as
a factor.
Lecture
Academic writing
Week 3
LO1 – LO2
A look at the code implementation of common algorithms.
• Examination of the structure of a program: input, process and
output.
• Identifying methods, variables, constants, scope, relating to an
algorithm implementation.
Analysing the characteristics of procedural programming.
• Identify what procedural programming is.
• Overview of the process of procedural development.
• Recognise the characteristics.
An examination of a program implementation.
Lecture
Seminar
Academic writing
Formative feedback
Week 4
LO2
Identification of the characteristics of object-orientated
programming (OOP).
• Identify what OOP is.
• Building on the procedural programming paradigm.
• Definition of an object.
Analysing the characteristics of object-orientated programming.
• Class definition and its make up.
Lecture
Group discussion

AssignmentTutorOnline

Department of Information Technology Page 2 of 3

• Class and object association.
Week 5
LO2
Analysing practical implementation of object-orientated
programming.
• An examination of an implementation of a program.
• Identifying object class relationship.
• Overview of the process of OOP development.
Identification of the characteristics of event driven programming.
• Identify what event driven programming is.
• Building on the previous programming paradigms.
Lecture
Group discussion
Week 6
LO2
Analysing the characteristics of event driven programming.
• Examination of what makes an event driven program.
• Typical events encountered; timers, input etc.
• Event listeners, triggers
A look at the relationships between the programming paradigms.
• Discuss the relationship between the programming paradigms,
how they complement each other.
• Review a range of applications that would be best suited for
developing in each/or multiple programming paradigms.
Lecture
Group discussion
Formative feedback and
Academic writing
Week 7
LO2 – LO3
A look at the various components of an IDE, the editor, file
manager and compiler.
• Setup and installation of an IDE.
• User configuration of an IDE (fonts, shortcuts, etc.).
Lecture
Group discussion
Week 8
LO2 – LO3
A look at the debugger, performance analyser, version control.
• Examine a typical IDE project structure; source code and
binaries, file structure layout.
• Build a test application (Hello World!) to utilise the components
of an IDE.
Lecture
Group discussion
Week 9
LO3
An overview of the implementation process.
• Setup of a project with version control.
• Use the IDE to develop an application.
• Use of best practice.
LAB
Configuration overview
Week 10
LO3
Implement an application using algorithms for a specified
purpose, assignment workshop.
• Continue development of application.
• Use of best practice.
Lecture
Group discussion
Formative feedback
Week 11
LO4
Internal testing and debugging an application using the IDE.
• Use the features in the IDE to test and debug.
• Identify features of the IDE that help with documentation and
maintain a coding standard.
Group discussion
Week 12
LO4
Working to the specification.
• Adapting development of application according testing
process.
Formative feedback
Week 13 Presentation on coursework if it is necessary
Students support
Week 14 Study week and preparations for assignment submission and Exams
Students support
Week 15 Students support

Department of Information Technology Page 3 of 3
Recommended reading
Books
Jason Cannon (2014) Python Programming for Beginners: An Introduction to the Python Computer Language and
Computer Programming
John P. Newton (2017) Python Programming: An Easy and Comprehensive Guide to Learn Python Programming
Language
Jason R. Briggs (2012) Python for Kids: A Playful Introduction to Programming 22 Dec
Journals
The Institute of Engineering and Technology (IET)
British Computer Society (BCS)
Websites
HN Global | BTEC Higher Nationals at https://www.highernationals.com/hn-global
Academic Writing Books
Bailey, S. (2011), Academic Writing – A Handbook for International Students, London, Routledge
Burns, T. and Sinfield, S (2016), Essential Study Skills: The Complete Guide to Success at University, Los
Angeles, SAGE,
Swales, J. M and Feak, C, B. (2012), Academic Writing for Graduate Students, Ann Abor, The University of
Michigan

Data Modelling with Entity-Relationship

Data Modelling with Entity-Relationship

13/10/2017
1

Lecture 3
Data Modelling with Entity-Relationship Model

AssignmentTutorOnline

13/10/2017
2

Part 4 – Objectives
 How to use Entity–Relationship (ER) modeling
in database design.
 Basic concepts associated with ER model.
 Diagrammatic technique for displaying ER
model using Unified Modeling Language (UML).
 How to build an ER model from a requirements
specification.
4
A data model is a plan, or blueprint, for a
database design.
A data model is more generalized and
abstract than a database design.
It is easier to change a data model than it
is to change a database design, so it is the
appropriate place to work through
conceptual database problems.
The Data Model

13/10/2017
3

Concepts of the ER Model
 Entity types
 Relationship types
 Attributes
 Entity-Relationship model is a set of concepts and graphical
symbols that can be used to create conceptual schemas.
 Versions
– Original E-R model — Peter Chen (1976).
– Extended E-R model — Extensions to the Chen model.
– Information Engineering (IE) — James Martin (1990); it uses “crow’s
foot” notation, is easier to understand and we will use it.
– Unified Modeling Language (UML) — The Object Management Group; it
supports object-oriented methodology
E-R Model

13/10/2017
4

 Something that can be identified and the users want to
track. It can be an object, a concept, or person that
users are interested in recording information about.
 Examples:-Person, Account, Product, etc.
– Entity type/class — a collection of entities of a given type or
Group of objects with same properties, identified by
enterprise as having an independent existence.
– Entity instance — the occurrence of a particular
entity/Uniquely identifiable object of an entity type.
 There are usually many instances of an entity in an
entity class.
Entities
Examples of Entity Types

13/10/2017
5

CUSTOMER- The Entity Class and Two Entity
Instances
10
 Attributes describe an entity’s characteristics.
 Examples of attributes on Customer entity class are :-
Cust_no, cust_name, Cust_Dob, etc.
 All entity instances of a given entity class have the
same attributes, but vary in the values of those
attributes.
 Originally shown in data models as ellipses.
 Data modeling products today commonly show
attributes in rectangular form.
Attributes

13/10/2017
6

11
EMPLOYEE: Attributes in Ellipses
12
EMPLOYEE: Attributes in Entity Rectangle

13/10/2017
7

13
 Identifiers are attributes that name, or identify, entity instances.
 The identifier of an entity instance consists of one or more of the
entity’s attributes.
 Composite identifiers: Identifiers that consist of two or more
attributes
 Identifiers in data models become keys in database designs:
– Entities have identifiers.
– Tables (or relations) have keys.
Identifiers
14
Entity Attribute Display in Data Models

13/10/2017
8

15
 Entities can be associated with one another in relationships:
– Relationship classes: associations among entity classes
– Relationship instances: associations among entity instances
 In the original E-R model, relationships could have attributes but
today this is no longer done.
 A relationship class can involve two or more entity classes.
Relationships
Relationship Types
 Relationship type
– Set of meaningful associations among entity
types.
 Relationship occurrence
– Uniquely identifiable association, which
includes one occurrence from each
participating entity type.

13/10/2017
9

17
 Entity: is modeled as a
square
 Relationship: is modeled
as a link between the two
entities.
 Attribute: is modeled as a
oval.
Entity Relationship Notation
18
ID Name
Address
Entity Relationship: Example

Student Course
Course
13/10/2017
10

19
•Define the relationship:
•has to be a verb
•Recommended to be one word
•has to be clear
This reads like this: A student enrolls in a course. A course
has students.
Entity Relationship:

Student Course
Enrolls
has

20
 The degree of a relationship is the number of entity classes
participating in the relationship:
– One entity class has a unary or recursive relationship of degree
one.
– Two entity classes have a binary relationship of degree two.
– Three entities have a ternary relationship of degree three.
Degree of a Relationship

13/10/2017
11

 A recursive relationship occurs when an entity has a
relationship to itself
 Relationship type where same entity type
participates more than once in different roles
Marries
Manages
Recursive Relationships

Person
Employee

22
Binary Relationship

13/10/2017
12

23
Ternary Relationship
Quaternary relationship called Arranges

13/10/2017
13

The principle difference between an entity and
a table (relation in a relational database) is that
you can express a relationship between entities
without using foreign keys.
This makes it easier to work with entities in the
early design process where the very existence
of entities and the relationships between them is
uncertain.
Entities and Tables
Attributes
 Attribute
– Property of an entity or a relationship type.
 Attribute Domain
– Set of allowable values for one or more attributes.
 Simple Attribute
– Attribute composed of a single component with an
independent existence.
 Composite Attribute
– Attribute composed of multiple components, each
with an independent existence.

13/10/2017
14

Attributes
 Single-valued Attribute
– Attribute that holds a single value for each occurrence
of an entity type.
 Multi-valued Attribute
– Attribute that holds multiple values for each occurrence
of an entity type.
 Derived Attribute
– Attribute that represents a value that is derivable from
value of a related attribute, or set of attributes, not
necessarily in the same entity type.
Keys
 Candidate Key
– Minimal set of attributes that uniquely
identifies each occurrence of an entity type.
 Primary Key
– Candidate key selected to uniquely identify
each occurrence of an entity type.
 Composite Key
– A candidate key that consists of two or more
attributes.

13/10/2017
15

Structural Constraints
Multiplicity is made up of two types of restrictions
on relationships: cardinality and participation.
Cardinality
– Describes maximum number of possible
relationship occurrences for an entity
participating in a given relationship type.
Participation
– Determines whether all or only some entity
occurrences participate in a relationship.
Multiplicity as cardinality and participation
constraints

13/10/2017
16

31
Cardinality means “count,” and is expressed as
a number.
Maximum cardinality (Cardinality) is the
highest number of entity instances that can
participate in a relationship.
Minimum cardinality (Optionality) is the
smallest number of entity instances that must
participate in a relationship.
Cardinality & Optionality
32
 Maximum cardinality is the highest number of entity instances that
can participate in a relationship.
 There are three types of maximum cardinality:
– One-to-One [1:1]
– One-to-Many [1:N]
– Many-to-Many [N:M]
Maximum Cardinality

13/10/2017
17

33
The Three Types of
Maximum Cardinality
34
 In a one-to-many relationship:
– The entity on the one side of the relationship is called the parent entity
or just the parent.
– The entity on the many side of the relationship is called the child entity
or just the child.
 In the figure below, EMPLOYEE is the parent and COMPUTER is the
child:
Parent and Child Entities

13/10/2017
18

35
The relationships we have been discussing are
known as HAS-A relationships:
– Each entity instance has a relationship with
another entity instance:
»An EMPLOYEE has one or more
COMPUTERs.
»A COMPUTER has an assigned
EMPLOYEE.
HAS-A Relationships
36
 Minimum cardinality is the minimum number of
entity instances that must participate in a
relationship.
 Minimums are generally stated as either zero or one:
– IF zero [0] THEN participation in the relationship by the
entity is optional, and no entity instance must participate in
the relationship.
– IF one [1] THEN participation in the relationship by the
entity is mandatory, and at least one entity instance must
participate in the relationship.
Minimum Cardinality(Optionality)

13/10/2017
19

37
 As shown in the examples in a following slide:
– Minimum cardinality of zero [0] indicating optional
participation is indicated by placing an oval next to the optional
entity.
– Minimum cardinality of one [1] indicating mandatory
(required) participation is indicated by placing a vertical hash
mark next to the required entity.
Indicating Minimum Cardinality
38
 Look toward the entity in question:
– IF you see an oval THEN that entity is optional (minimum
cardinality of zero [0]).
– IF you see a vertical hash mark THEN that entity is mandatory
(required) (minimum cardinality of one [ 1]).
Reading Minimum Cardinality

13/10/2017
20

39
The Three Types of
Minimum Cardinality
40
Data Modeling Notation

13/10/2017
21

41
Data Modeling Notation:
ERwin
42
 A Student can enroll in one Course at a time.
 A Course can have one to many Students at a
time.
 A Lecturer can teach in zero to many Courses at
a time.
 A Course would have one or more Lectures
teaching in that course at anytime.
Lets look at some examples:

Entity
Constraint
13/10/2017
22

43
 one
 one to many
 zero to many
(Min One, Max One)
(Min One, Max Many)
(Min Zero, Max Many)
Constraints:

A
A
A

44
Optionality specifies the minimum number of
instances of the related entity.
Cardinality specifies the maximum number of
instances of the related entity.
E-R: Optionality and Cardinality

A B
Cardinatlity
Optionality
13/10/2017
23

Lets see how it is implemented:
 A Student can enroll in one Course at a time only.
 A Course can have one to many Students at a time.
 A Lecturer can teach in zero to many Courses at a time.
 A Course would have one or more Lectures teaching at it at
anytime.

Student Course
Lecturer

46
 Note that:
 (1) ERwin cannot indicate true
minimum cardinalities on N:M
relationships
 (2) Visio introduces the
intersection table instead of
using a true N:M model
Data Modeling Notation:
N:M and O-M

13/10/2017
24

47
 An ID-dependent entity is an entity (child) whose identifier
includes the identifier of another entity (parent).
 The ID-dependent entity is a logical extension or sub-unit of the
parent:
– BUILDING : APARTMENT
– PAINTING : PRINT
 The minimum cardinality from the ID-dependent entity to the parent
is always one.
ID-Dependent Entities
A solid line
indicates an
identifying
relationship
ID-Dependent Entities

13/10/2017
25

Entity Type
 Strong Entity Type
– Entity type that is not existence-dependent
on some other entity type.
 Weak Entity Type
– Entity type that is existence-dependent on
some other entity type.
Strong entity type called Client and weak entity type
called Preference

13/10/2017
26

51
 A weak entity is an entity whose exisitence depends upon another
entity.
 All ID-Dependent entities are considered weak.
 But there are also non-ID-dependent weak entities.
– The identifier of the parent does not appear in the identifier of the
weak child entity.
Weak Entities
Weak entities
must be
indicated by an
accompanying
text box in
Erwin – There is
no specific
notation for a
nonidentifying
but weak entity
relationship
A dashed line
indicates a
nonidentifying
relationship
Weak Entities (Continued)

13/10/2017
27

53
ID-Dependent and Weak Entities
A subtype entity is a special case of a
supertype entity:
– STUDENT :
UNDERGRADUATE or
GRADUATE
The supertype contains all common attributes,
while the subtypes contain specific attributes.
The supertype may have a discriminator
attribute that indicates the subtype.
Subtype Entities

13/10/2017
28

Subtypes with a Discriminator
56
 If subtypes are exclusive, one supertype relates to at most one
subtype.
 If subtypes are inclusive, one supertype can relate to one or more
subtypes.
 Other database texts refer to exclusive subtypes as being disjoint.
 Inclusive subtypes are also known as overlaps.
Subtypes: Exclusive (Disjoint)
or Inclusive (Overlap)

13/10/2017
29

57
Subtypes: Exclusive or
Inclusive (Continued)
 Relationships connecting supertypes and subtypes are called IS-A
relationships, because a subtype IS A supertype.
 The identifer of the supertype and all of its subtypes must be
identical, i.e., the identifier of the supertype becomes the identifier
of the related subtype(s).
 Subtypes are used to avoid value-inappropriate nulls.
Subtypes: IS-A relationships

Purpose and importance of SQL

Purpose and importance of SQL
Lecture 8

Be able to use manipulation and querying tools
 Data manipulation: query languages; visual tools;
typical tasks eg for database maintenance, inserts,
updates and amendments
 Queries and reporting: query languages and query by
example (QBE); formatting; functions/formulae; report
writing tools
3
Part 1 – Objectives
 Purpose and importance of SQL.
 How to retrieve data from database using
SELECT and:
– Use compound WHERE conditions.
– Sort query results using ORDER BY.
– Use aggregate functions.
– Group data using GROUP BY and HAVING.
– Use subqueries.
Part 1 – Objectives
– Join tables together.
– Perform set operations (UNION, INTERSECT,
EXCEPT).
 How to update database using INSERT,
UPDATE, and DELETE.
5
Objectives of SQL
 Ideally, database language should allow user to:
– create the database and relation structures;
– perform insertion, modification, deletion of
data from relations;
– perform simple and complex queries.
 Must perform these tasks with minimal user
effort and command structure/syntax must be
easy to learn.
 It must be portable.
6
Objectives of SQL
 SQL is a transform-oriented language with 2
major components:
– A DDL for defining database structure.
– A DML for retrieving and updating data.
 Until SQL:1999, SQL did not contain flow of
control commands. These had to be implemented
using a programming or job-control language, or
interactively by the decisions of user.
7
Objectives of SQL
 SQL is relatively easy to learn:
– it is non-procedural – you specify what
information you require, rather than how to get
it;
– it is essentially free-format.
Objectives of SQL
 Consists of standard English words:
1) CREATE TABLE Staff(staffNo VARCHAR(5),
lName VARCHAR(15),
salary DECIMAL(7,2));
2) INSERT INTO Staff VALUES (‘SG16’, ‘Brown’,
8300);
3) SELECT staffNo, lName, salary
FROM Staff
WHERE salary > 10000;
9
Objectives of SQL
 Can be used by range of users including DBAs,
management, application developers, and other
types of end users.
 An ISO standard now exists for SQL, making it
both the formal and de facto standard language
for relational databases.
Pearson Education © 2009
10
History of SQL
 In 1974, D. Chamberlin (IBM San Jose
Laboratory) defined language called ‘Structured
English Query Language’ (SEQUEL).
 A revised version, SEQUEL/2, was defined in
1976 but name was subsequently changed to SQL
for legal reasons.
Pearson Education © 2009
11
History of SQL
 Still pronounced ‘see-quel’, though official
pronunciation is ‘S-Q-L’.
 IBM subsequently produced a prototype DBMS
called System R, based on SEQUEL/2.
 Roots of SQL, however, are in SQUARE
(Specifying Queries as Relational Expressions),
which predates System R project.
Pearson Education © 2009
12
History of SQL
 In late 70s, ORACLE appeared and was probably first
commercial RDBMS based on SQL.
 In 1987, ANSI and ISO published an initial standard for
SQL.
 In 1989, ISO published an addendum that defined an
‘Integrity Enhancement Feature’.
 In 1992, first major revision to ISO standard occurred,
referred to as SQL2 or SQL/92.
 In 1999, SQL:1999 was released with support for objectoriented data management.
 In late 2003, SQL:2003 was released.
Pearson Education © 2009
13
Importance of SQL
 SQL has become part of application architectures
such as IBM’s Systems Application Architecture.
 It is strategic choice of many large and influential
organizations (e.g. X/OPEN).
 SQL is Federal Information Processing Standard
(FIPS) to which conformance is required for all
sales of databases to American Government.
Pearson Education © 2009
14
Importance of SQL
 SQL is used in other standards and even
influences development of other standards as a
definitional tool. Examples include:
– ISO’s Information Resource Directory System
(IRDS) Standard
– Remote Data Access (RDA) Standard.
Pearson Education © 2009
15
Writing SQL Commands
 SQL statement consists of reserved words and userdefined words.
– Reserved words are a fixed part of SQL and must
be spelt exactly as required and cannot be split
across lines.
– User-defined words are made up by user and
represent names of various database objects such
as relations, columns, views.
Pearson Education © 2009
16
Writing SQL Commands
 Most components of an SQL statement are case
insensitive, except for literal character data.
 More readable with indentation and lineation:
– Each clause should begin on a new line.
– Start of a clause should line up with start of
other clauses.
– If clause has several parts, should each appear
on a separate line and be indented under start
of clause.
Pearson Education © 2009
17
Writing SQL Commands
 Use extended form of BNF notation:
– Upper-case letters represent reserved words.
– Lower-case letters represent user-defined words.
– | indicates a choice among alternatives.
– Curly braces indicate a required element.
– Square brackets indicate an optional element.
– … indicates optional repetition (0 or more).
Pearson Education © 2009
18
Literals
 Literals are constants used in SQL statements.
 All non-numeric literals must be enclosed in
single quotes (e.g. ‘London’).
 All numeric literals must not be enclosed in
quotes (e.g. 650.00).
Pearson Education © 2009
19
SELECT Statement
SELECT [DISTINCT | ALL]
[columnExpression [AS newName]] [,…]

FROM
[WHERE
[GROUP BY
TableName [alias] [, …]
condition]
columnList] [HAVING condition]

AssignmentTutorOnline

[ORDER BY columnList]
Pearson Education © 2009
20
SELECT Statement
FROM Specifies table(s) to be used.
WHERE Filters rows.
GROUP BY Forms groups of rows with same
column value.
HAVING Filters groups subject to some
condition.
SELECT Specifies which columns are to
appear in output.
ORDER BY Specifies the order of the output.
Pearson Education © 2009
21
SELECT Statement
 Order of the clauses cannot be changed.
 Only SELECT and FROM are mandatory.
Pearson Education © 2009
22
Example 6.1 All Columns, All Rows
List full details of all staff.
SELECT staffNo, fName, lName, address,
position, sex, DOB, salary, branchNo
FROM Staff;
 Can use * as an abbreviation for ‘all columns’:
SELECT *
FROM Staff;
Pearson Education © 2009
23
Example 6.1 All Columns, All Rows
Pearson Education © 2009
24
Example 6.2 Specific Columns, All Rows
Produce a list of salaries for all staff, showing only
staff number, first and last names, and salary.
SELECT staffNo, fName, lName, salary
FROM Staff;
Pearson Education © 2009
25
Example 6.2 Specific Columns, All Rows
Pearson Education © 2009
26
Example 6.3 Use of DISTINCT
List the property numbers of all properties that
have been viewed.
SELECT propertyNo
FROM Viewing;
Pearson Education © 2009
27
Example 6.3 Use of DISTINCT
 Use DISTINCT to eliminate duplicates:
SELECT DISTINCT propertyNo
FROM Viewing;
Pearson Education © 2009
28
Example 6.4 Calculated Fields
Produce list of monthly salaries for all staff,
showing staff number, first/last name, and salary.
SELECT staffNo, fName, lName, salary/12
FROM Staff;
Pearson Education © 2009
29
Example 6.4 Calculated Fields
 To name column, use AS clause:
SELECT staffNo, fName, lName, salary/12
AS monthlySalary
FROM Staff;
Pearson Education © 2009
30
Example 6.5 Comparison Search Condition
List all staff with a salary greater than 10,000.
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary > 10000;
Pearson Education © 2009
31
Example 6.6 Compound Comparison Search Condition
List addresses of all branch offices in London or
Glasgow.
SELECT *
FROM Branch
WHERE city = ‘London’ OR city = ‘Glasgow’;
Pearson Education © 2009
32
Example 6.7 Range Search Condition
List all staff with a salary between 20,000 and
30,000.
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary BETWEEN 20000 AND 30000;
 BETWEEN test includes the endpoints of range.
Pearson Education © 2009
33
Example 6.7 Range Search Condition
Pearson Education © 2009
34
Example 6.7 Range Search Condition
 Also a negated version NOT BETWEEN.
 BETWEEN does not add much to SQL’s
expressive power. Could also write:
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary>=20000 AND salary <= 30000;
 Useful, though, for a range of values.
Pearson Education © 2009
35
Example 6.8 Set Membership
List all managers and supervisors.
SELECT staffNo, fName, lName, position
FROM Staff
WHERE position IN (‘Manager’, ‘Supervisor’);
Pearson Education © 2009
36
Example 6.8 Set Membership
 There is a negated version (NOT IN).
 IN does not add much to SQL’s expressive power.
Could have expressed this as:
SELECT staffNo, fName, lName, position
FROM Staff
WHERE position=‘Manager’ OR
position=‘Supervisor’;
 IN is more efficient when set contains many values.
Pearson Education © 2009
37
Example 6.9 Pattern Matching
Find all owners with the string ‘Glasgow’ in their
address.
SELECT ownerNo, fName, lName, address, telNo
FROM PrivateOwner
WHERE address LIKE ‘%Glasgow%’;
Pearson Education © 2009
38
Example 6.9 Pattern Matching
 SQL has two special pattern matching symbols:
– %: sequence of zero or more characters;
– _ (underscore): any single character.
 LIKE ‘%Glasgow%’ means a sequence of
characters of any length containing ‘Glasgow’.
Pearson Education © 2009
39
Example 6.10 NULL Search Condition
List details of all viewings on property PG4
where a comment has not been supplied.
 There are 2 viewings for property PG4, one with
and one without a comment.
 Have to test for null explicitly using special
keyword IS NULL:
SELECT clientNo, viewDate
FROM Viewing
WHERE propertyNo = ‘PG4’AND
comment IS NULL;
Pearson Education © 2009
40
Example 6.10 NULL Search Condition
 Negated version (IS NOT NULL) can test for
non-null values.
Pearson Education © 2009
41
Example 6.11 Single Column Ordering
List salaries for all staff, arranged in descending
order of salary.
SELECT staffNo, fName, lName, salary
FROM Staff
ORDER BY salary DESC;
Pearson Education © 2009
42
Example 6.11 Single Column Ordering
Pearson Education © 2009
43
Example 6.12 Multiple Column Ordering
Produce abbreviated list of properties in order of
property type.
SELECT propertyNo, type, rooms, rent
FROM PropertyForRent
ORDER BY type;
Pearson Education © 2009
44
Example 6.12 Multiple Column Ordering
Pearson Education © 2009
45
Example 6.12 Multiple Column Ordering
 Four flats in this list – as no minor sort key
specified, system arranges these rows in any order
it chooses.
 To arrange in order of rent, specify minor order:
SELECT propertyNo, type, rooms, rent
FROM PropertyForRent
ORDER BY type, rent DESC;
Pearson Education © 2009
46
Example 6.12 Multiple Column Ordering
Pearson Education © 2009
47
SELECT Statement – Aggregates
 ISO standard defines five aggregate functions:
COUNT returns number of values in specified
column.
SUM returns sum of values in specified column.
AVG returns average of values in specified column.
MIN returns smallest value in specified column.
MAX returns largest value in specified column.
Pearson Education © 2009
48
SELECT Statement – Aggregates
 Each operates on a single column of a table and
returns a single value.
 COUNT, MIN, and MAX apply to numeric and
non-numeric fields, but SUM and AVG may be
used on numeric fields only.
 Apart from COUNT(*), each function eliminates
nulls first and operates only on remaining nonnull values.
Pearson Education © 2009
49
SELECT Statement – Aggregates
 COUNT(*) counts all rows of a table, regardless
of whether nulls or duplicate values occur.
 Can use DISTINCT before column name to
eliminate duplicates.
 DISTINCT has no effect with MIN/MAX, but
may have with SUM/AVG.
Pearson Education © 2009
50
SELECT Statement – Aggregates
 Aggregate functions can be used only in
SELECT list and in HAVING clause.
 If SELECT list includes an aggregate function
and there is no GROUP BY clause, SELECT list
cannot reference a column out with an aggregate
function. For example, the following is illegal:
SELECT staffNo, COUNT(salary)
FROM Staff;
Pearson Education © 2009
51
Example 6.13 Use of COUNT(*)
How many properties cost more than £350 per
month to rent?
SELECT COUNT(*) AS myCount
FROM PropertyForRent
WHERE rent > 350;
Pearson Education © 2009
52
Example 6.14 Use of COUNT(DISTINCT)
How many different properties viewed in May ‘04?
SELECT COUNT(DISTINCT propertyNo) AS myCount
FROM Viewing
WHERE viewDate BETWEEN ‘1-May-04’
AND ‘31-May-04’;
Pearson Education © 2009
53
Example 6.15 Use of COUNT and SUM
Find number of Managers and sum of their
salaries.
SELECT COUNT(staffNo) AS myCount,
SUM(salary) AS mySum
FROM Staff
WHERE position = ‘Manager’;
Pearson Education © 2009
54
Example 6.16 Use of MIN, MAX, AVG
Find minimum, maximum, and average staff
salary.
SELECT MIN(salary) AS myMin,
MAX(salary) AS myMax,
AVG(salary) AS myAvg
FROM Staff;
Pearson Education © 2009
55
SELECT Statement – Grouping
 Use GROUP BY clause to get sub-totals.
 SELECT and GROUP BY closely integrated:
each item in SELECT list must be single-valued
per group, and SELECT clause may only contain:
– column names
– aggregate functions
– constants
– expression involving combinations of the above.
Pearson Education © 2009
56
SELECT Statement – Grouping
 All column names in SELECT list must appear in
GROUP BY clause unless name is used only in an
aggregate function.
 If WHERE is used with GROUP BY, WHERE is
applied first, then groups are formed from
remaining rows satisfying predicate.
 ISO considers two nulls to be equal for purposes
of GROUP BY.
Pearson Education © 2009
57
Example 6.17 Use of GROUP BY
Find number of staff in each branch and their
total salaries.

SELECT branchNo,
COUNT(staffNo) AS myCount,

SUM(salary) AS mySum
FROM Staff
GROUP BY branchNo
ORDER BY branchNo;
Pearson Education © 2009
58
Example 6.17 Use of GROUP BY
Pearson Education © 2009
59
Restricted Groupings – HAVING clause
 HAVING clause is designed for use with GROUP
BY to restrict groups that appear in final result
table.
 Similar to WHERE, but WHERE filters
individual rows whereas HAVING filters groups.
 Column names in HAVING clause must also
appear in the GROUP BY list or be contained
within an aggregate function.
Pearson Education © 2009
60
Example 6.18 Use of HAVING
For each branch with more than 1 member of
staff, find number of staff in each branch and
sum of their salaries.
SELECT branchNo,
COUNT(staffNo) AS myCount,
SUM(salary) AS mySum
FROM Staff
GROUP BY branchNo
HAVING COUNT(staffNo) > 1
ORDER BY branchNo;
Pearson Education © 2009
61
Example 6.18 Use of HAVING
Pearson Education © 2009
62
Subqueries
 Some SQL statements can have a SELECT
embedded within them.
 A subselect can be used in WHERE and
HAVING clauses of an outer SELECT, where it
is called a subquery or nested query.
 Subselects may also appear in INSERT,
UPDATE, and DELETE statements.
Pearson Education © 2009
63
Example 6.19 Subquery with Equality
List staff who work in branch at ‘163 Main St’.
SELECT staffNo, fName, lName, position
FROM Staff
WHERE branchNo =
(SELECT branchNo
FROM Branch
WHERE street = ‘163 Main St’);
Pearson Education © 2009
64
Example 6.19 Subquery with Equality
 Inner SELECT finds branch number for branch
at ‘163 Main St’ (‘B003’).
 Outer SELECT then retrieves details of all staff
who work at this branch.
 Outer SELECT then becomes:
SELECT staffNo, fName, lName, position
FROM Staff
WHERE branchNo = ‘B003’;
Pearson Education © 2009
65
Example 6.19 Subquery with Equality
Pearson Education © 2009
66
Example 6.20 Subquery with Aggregate
List all staff whose salary is greater than the average
salary, and show by how much.
SELECT staffNo, fName, lName, position,
salary – (SELECT AVG(salary) FROM Staff) As SalDiff
FROM Staff
WHERE salary >
(SELECT AVG(salary)
FROM Staff);
Pearson Education © 2009
67
Example 6.20 Subquery with Aggregate
 Cannot write ‘WHERE salary > AVG(salary)’
 Instead, use subquery to find average salary
(17000), and then use outer SELECT to find those
staff with salary greater than this:
SELECT staffNo, fName, lName, position,
salary – 17000 As salDiff
FROM Staff
WHERE salary > 17000;
Pearson Education © 2009
68
Example 6.20 Subquery with Aggregate
Pearson Education © 2009
69
Subquery Rules
 ORDER BY clause may not be used in a
subquery (although it may be used in outermost
SELECT).
 Subquery SELECT list must consist of a single
column name or expression, except for
subqueries that use EXISTS.
 By default, column names refer to table name in
FROM clause of subquery. Can refer to a table
in FROM using an alias.
Pearson Education © 2009
Lab
 SQL Server

Overview of Programming Paradigms

Overview of Programming Paradigms
1
Overview of
Programming
Paradigms
Week 6
2
Overview of Programming Paradigms
 Lecture Objectives:
 Be able to explain the differences between programming languages and programming paradigms.
 Be able to differentiate between low-level and high-level programming languages and their
associated advantages and disadvantages
 Be able to list four programming paradigms and describe their strengths and weaknesses.
 Introduction to Computer Programming
 Programming Languages
 Programming Paradigms
 Exercises
3
Programming Languages
 A computer program is a clear, step-by-step, finite set of instructions. A
computer program must be clear so that only one meaning can be derived
from it and is written in a computer language called a programming
language.
 There are three categories of programming languages:
 1. Machine languages. (low-level languages)
 2. Assembly languages.
 3. High-level languages.
4
Programming Languages (cont’d)
 A Machine language program consists of a sequence of zeros and ones.
 Each kind of CPU has its own machine language.
 Advantages
 Fast and efficient
 Machine oriented
 No translation required
 Disadvantages
 Not portable
 Not programmer friendly
5
Assembly Language
 Assembly language programs use mnemonics to represent machine instructions
 Each statement in assembly language corresponds to one statement in machine language.
 Assembly language programs have the same advantages and disadvantages as machine
language programs.
 Compare the following machine language and assembly language programs:

8086 Machine language program for
var1 = var1 + var2 ;
8086 Assembly program for
var1 = var1 + var2 ;
1010 0001 0000 0000 0000 0000
0000 0011 0000 0110 0000 0000 0000 0010
1010 0011 0000 0000 0000 0000
MOV AX , var1
ADD AX , var2
MOV var1 , AX

6
High-Level Programming Languages
 A high-level language (HLL) has two primary components
 (1) a set of built-in language primitives and grammatical rules
 (2) a translator
 A HLL language program consists of English-like statements that are
governed by a strict syntax.
 Advantages
 Portable or machine independent
 Programmer-friendly
 Disadvantages
 Not as efficient as low-level languages
 Need to be translated
 Examples : C, C++, Java, FORTRAN, Visual Basic, and Delphi.
7
Programming Paradigms
 Why are there hundreds of programming languages in use today?
 Some programming languages are specifically designed for use in certain applications.
 Different programming languages follow different approaches to solving programming problems
 A programming paradigm is an approach to solving programming problems.
 A programming paradigm may consist of many programming languages.
 Common programming paradigms:
 Imperative or Procedural Programming
 Object-Oriented Programming
 Event driven programming
8
8
Procedural
programming paradigm
9
Procedural programming
 Often thought as a synonym for imperative programming.
 Specifying the steps the program must take to reach the desired state.
 Based upon the concept of the procedure call.
 Procedures, also known as routines, subroutines, methods, or functions
that contain a series of computational steps to be carried out.
 Any given procedure might be called at any point during a program’s
execution, including by other procedures or itself.
 A procedural programming language provides a programmer a means
to define precisely each step in the performance of a task. The
programmer knows what is to be accomplished and provides through
the language step-by-step instructions on how the task is to be done.
 Lisp, C++, and Python are multi-paradigm; you can write programs or libraries that are largely
procedural, object-oriented, or functional in all of these languages.
10
10
Procedural programming
 Benefits:
» Often a better choice than simple sequential or unstructured programming in many
situations which involve moderate complexity or require significant ease of
maintainability.
» The ability to re-use the same code at different places in the program without copying it.
» An easier way to keep track of program flow than a collection of “GOTO” or “JUMP”
statements (which can turn a large, complicated program into spaghetti code).
» The ability to be strongly modular or structured.
 The main benefit of procedural programming over first- and second-generation languages is
that it allows for modularity, which is generally desirable, especially in large, complicated
programs.
 Modularity was one of the earliest abstraction features identified as desirable for a
programming language like Python
11
11
Procedural programming
 Disadvantages
difficulty of reasoning about programs
difficulty of parallelization.
Tend to be relatively low level.
Sample code:
12
Object-oriented
programming paradigm
13
13
Object-oriented programming (OOP) is a programming paradigm that uses
“objects” – data structures encapsulating data fields and procedures together
with their interactions – to design applications and computer programs.
Associated programming techniques may include features such as data
abstraction, encapsulation, modularity, polymorphism, and inheritance.
Though it was invented with the creation of the Simula language in 1965, and
further developed in Smalltalk in the 1970s, it was not commonly used in
mainstream software application development until the early 1990s.
Many modern programming languages now support OOP.
Object-oriented programming
14
14
A class defines the abstract characteristics of a thing (object), including that
thing’s characteristics (its attributes, fields or properties) and the thing’s
behaviors (the operations it can do, or methods, operations or
functionalities).
One might say that a class is a blueprint or factory that describes the nature of
something.
Classes provide modularity and structure in an object-oriented computer
program.
Collectively, the properties and methods defined by a class are called its
members.
OOP concepts: class
15
15
» An object is an individual of a class created at run-time trough object
instantiation from a class.
» The set of values of the attributes of a particular object forms its state. The
object consists of the state and the behavior that’s defined in the object’s
class.
» The object is instantiated by implicitly calling its constructor, which is one
of its member functions responsible for the creation of instances of that
class.
OOP concepts: object
16
16
An attribute, also called data member or member variable, is the data
encapsulated within a class or object.
Attributes are an object’s variables that, upon being given values at
instantiation (using a constructor) and further execution, will represent the
state of the object.
A method is a subroutine that is exclusively associated either with a class (in
which case it is called a class method or a static method) or with an object (in
which case it is an instance method).
Like a subroutine in procedural programming languages, a method usually
consists of a sequence of programming statements to perform an action, a set
of input parameters to customize those actions, and possibly an output value
(called the return value).
OOP concepts: attributes & Methods
17
17
 Inheritance
 Abstraction
 encapsulation and information hiding
 polymorphism
OOP concepts:
18
Programming Paradigms: Object-Oriented
Example object oriented languages include: Java, C#, Smalltalk, Python, c++ etc
 Advantages
 Conceptual simplicity
 Models computation better
 Increased productivity.
Disadvantages
Can have a steep learning curve, initially
Doing I/O can be cumbersome
19
19
Procedural programming vs OOPS
 The focus of procedural programming is to break down a
programming task into a collection of variables, data
structures, and subroutines, whereas in object-oriented
programming it is to break down a programming task into objects
with each “object” encapsulating its own data and methods
(subroutines).
 The most important distinction is whereas procedural
programming uses procedures to operate on data structures,
object-oriented programming bundles the two together so an
“object” operates on its “own” data structure.
20
Event-driven
programming paradigm
21
Event
 Event-driven programming is a programming paradigm in which the flow of
program execution is determined by events – for example a user action such as a
mouse click, key press, or a message from the operating system or another
program. An event-driven application is designed to detect events as they occur,
and then deal with them using an appropriate event-handling procedure. The idea is
an extension of interrupt-driven programming of the kind found in early command
line environments such as DOS, and in embedded systems (where the application is
implemented as firmware).
 Event-driven programs can be written in any programming language, although
some languages(Visual Basic for example) are specifically designed to facilitate
event-driven programming, and provide an integrated development environment
(IDE) that partially automates the production of code, and provides a
comprehensive selection of built-in objects and controls, each of which can respond
to a range of events. Virtually all object-oriented and visual languages support
event-driven programming. Visual Basic, Visual C++ and Java are examples of
such languages.
22
Event Driven Programming
 User-Centric
 Computer User Determines the Order of Actions
 Programs are Interactive
 Flow of Control is Determined at Runtime
» User Clicks Mouse / Presses Key
» Object in Scene Moves to create a Condition
23
Event Handling
 Advantages
– It allows for more interactive programs. Almost all modern GUI programs
use event driven programming.
– It can be implemented using hardware interrupts, which will reduce the
power used by the computer.
– It allows sensors and other hardware to easily interact with software.
Disadvantages
– For simple programs, event driven programming is often more complex
and cumbersome than batch programming.
– The flow of the program is usually less logical and obvious.
24
Which Programming Paradigm is Best?
Which of these paradigms is the best?
 The most accurate answer is that there is no best paradigm.
 No single paradigm will fit all problems well.
 Human beings use a combination of the models represented by these paradigms.
 Languages with features from different paradigms are often too complex.
 So, the search of the ultimate programming language continues!
25
25
References
1. John von Neumann. First Draft Report on the EDVAC, 1945.
2. Harold Abelson, Gerald Jay Sussman. Structure and Interpretation of Computer Programs. The MIT
Press. 1996.
3. Roberts, Eric S. (2008). “Art and Science of Java; Chapter 7: Objects and Memory”. Stanford
University.
4. Programming paradigms, https://blog.newrelic.com/2015/04/01/python-programming-styles/

AssignmentTutorOnline

Debugging

Debugging

Debugging
Debugging is the process of finding and resolving defects or problems within a computer
program that prevent correct operation of computer software or a system. Debugging tactics
can involve interactive debugging, control flow analysis, unit testing, integration testing, log
file analysis, monitoring at the application or system level, memory dumps, and profiling.
Debugging Process
So the process of using the debugger involves
• setting breakpoints
• stepping through the source code one line at a time
• inspecting the values of variables as they change
• making corrections to the source as bugs are found
• rerunning the program to make sure the fixes are correct
Debugging under IDLE
IDLE has a debugger built into it. It is very useful for stepping through a program and
watching the variables change values.
In the Shell window, click on the Debug menu option at the top and then on Debugger. You
will see a “Debug Control” window like this
Notice that the Shell shows “[DEBUG ON]”.
Explanations of a few other things in the Debug Control window
• Over means that if the statement to be executed has a function call in it, go off and do
the function call without showing any details of the execution or variables, then return
and give the human control again, “step over the function”
• Out assumes you are in some function’s code, finish execution of the function at
normal speed, return from the function and then give the human control again, “step
out of the function”
• Quit stops the execution of the entire program
For the debugger to be most useful, you need to set a breakpoint in your source code before
you start running the program. A breakpoint is a marker on your code that tells the debugger
“run to this point at normal speed, then pause and let the human have control”. You can have
many of them; in more complex programs you would need them at different places. RIGHT
click on a line of your source and choose “set breakpoint”.
The background of the line you click on turns yellow to show the line marked with the
breakpoint.
If you don’t put any breakpoints in your source, when you run it with the debugger on, it will
pause at the first line of executable code (may be an import statement or a call to main()) but
then will run your program normally (i.e. with no pauses).
Now run the program with F5 as usual.
Note that the Debug Control window is opened and that the blue line states that the line “from
math import pi” is ready to be executed (the 7 is for line number 7 in the source file).
From this point you can click the Go button near the top of the window. This will make the
program run at normal speed until a breakpoint is encountered (or input is requested or the
program finishes). You can also use the Step button to step through your code, one line at a
time. This button is used quite a lot. If the line being stepped through has a function call,
execution will go to the first line of the function definition (you are “stepping into” the
function). If the line being stepped through doesn’t have a function call, the line is executed.
In either case, then control goes back to the human.
When you execute a line that has input in it, the debugger seems to shut down but it has not.
If you bring up the Shell window you can see that the program is waiting for input. Make
sure your cursor is in the right place on the window and give it some input and press Enter as
usual.
There are several things to note about this picture. The Shell window is in the background, it
shows that the user entered 23 and pressed Enter. The Debug Control window shows that
execution has moved on to the next line of code. Also, at the bottom of that window there is a
pane that says “Locals” and it shows the value of radius to be ’23’. This is useful in several
ways. It shows the values of variables as they change, and it shows the types of variables.
Note that the value of radius has quotes around it. This means that it is a string value.
Click the Step button again.
Note that the new variable height has been created and has a value of ’14’.
Click the Step button again.
Notice that there is an error in the run of the program at this point (the yellow bar in the
Debug Control window). The interpreter is saying that it cannot multiply “sequence by nonint of type ‘float’”. What it means is that the two input variables are of the wrong type to be
combined with numeric types. This has to be corrected before any more debugging takes
place. This is fixed by using the eval function in the two input statements.
“You can only toggle the debugger when idle” If you get this message in a window when you
try to turn the debugger on, try clicking on the Quit button in the Debug Control window. If
that does not help, try shutting down the program file and reopening it. If that does not help,
shut down Python completely and restart it from scratch.
Note that the bugs in the input statements have been fixed with the addition of “eval”. The
breakpoint was put in again. Breakpoint locations are not saved with the program; they have
to be set again for every debugging session.
Note the type of the Local variables in this image. They don’t have the quotes around the
values any more! They are numbers now.
As you step through the program one statement at a time, you can see more variables get
values. When we come to the next bug you can see that the variable Pi is not the same as the
variable pi, which is defined in the math library. This bug would need to be fixed, then the
debugging would continue.

AssignmentTutorOnline

Another Option:
The module pdb defines an interactive source code debugger for Python programs. It
supports setting (conditional) breakpoints and single stepping at the source line level,
inspection of stack frames, source code listing, and evaluation of arbitrary Python code
in the context of any stack frame. It also supports post-mortem debugging and can be
called under program control.
The debugger is extensible — it is actually defined as the class Pdb. This is currently
undocumented but easily understood by reading the source. The extension interface
uses the modules bdb and cmd.
The debugger’s prompt is (Pdb). Typical usage to run a program under control of the
debugger is:
>>> import pdb
>>> import mymodule
>>> pdb.run(‘mymodule.test()’)
> (0)?()
(Pdb) continue
> (1)?()
(Pdb) continue
NameError: ‘spam’
> (1)?()
(Pdb)
pdb.py can also be invoked as a script to debug other scripts. For example:
python -m pdb myscript.py
The typical usage to break into the debugger from a running program is to insert
import pdb; pdb.set_trace()
There are a few commands we can use with pdb are as follows.
Some useful ones to remember are:
• b: set a breakpoint
• c: continue debugging until you hit a breakpoint
• s: step through the code
• n: to go to next line of code
• l: list source code for the current file (default: 11 lines including the line being
executed)
• u: navigate up a stack frame
• d: navigate down a stack frame
• p: to print the value of an expression in the current context

Python programs

Python programs

Another Option:
The module pdb defines an interactive source code debugger for Python programs. It
supports setting (conditional) breakpoints and single stepping at the source line level,
inspection of stack frames, source code listing, and evaluation of arbitrary Python code
in the context of any stack frame. It also supports post-mortem debugging and can be
called under program control.
The debugger is extensible — it is actually defined as the class Pdb. This is currently
undocumented but easily understood by reading the source. The extension interface
uses the modules bdb and cmd.
The debugger’s prompt is (Pdb). Typical usage to run a program under control of the
debugger is:
>>> import pdb
>>> import mymodule
>>> pdb.run(‘mymodule.test()’)
> (0)?()
(Pdb) continue
> (1)?()
(Pdb) continue
NameError: ‘spam’
> (1)?()
(Pdb)
pdb.py can also be invoked as a script to debug other scripts. For example:
python -m pdb myscript.py
The typical usage to break into the debugger from a running program is to insert
import pdb; pdb.set_trace()
There are a few commands we can use with pdb are as follows.
Some useful ones to remember are:
• b: set a breakpoint
• c: continue debugging until you hit a breakpoint
• s: step through the code
• n: to go to next line of code
• l: list source code for the current file (default: 11 lines including the line being
executed)
• u: navigate up a stack frame
• d: navigate down a stack frame
• p: to print the value of an expression in the current context

AssignmentTutorOnline

Data Manipulation in SQL

Data Manipulation in SQL
Lecture 8

Learning Objectives
 On completion of this topic, you will be able to:
– Explain
– Distinguish
– Identify Data Manipulation in SQL
Be able to use manipulation and querying tools
 Data manipulation: query languages; visual tools;
typical tasks eg for database maintenance, inserts,
updates and amendments
 Queries and reporting: query languages and query by
example (QBE); formatting; functions/formulae; report
writing tools
4
Part 1 – Objectives
 Purpose and importance of SQL.
 How to retrieve data from database using
SELECT and:
– Use compound WHERE conditions.
– Sort query results using ORDER BY.
– Use aggregate functions.
– Group data using GROUP BY and HAVING.
– Use subqueries.
Part 1 – Objectives
– Join tables together.
– Perform set operations (UNION, INTERSECT,
EXCEPT).
 How to update database using INSERT,
UPDATE, and DELETE.
6
Objectives of SQL
 Ideally, database language should allow user to:
– create the database and relation structures;
– perform insertion, modification, deletion of
data from relations;
– perform simple and complex queries.
 Must perform these tasks with minimal user
effort and command structure/syntax must be
easy to learn.
 It must be portable.
7
Objectives of SQL
 SQL is a transform-oriented language with 2
major components:
– A DDL for defining database structure.
– A DML for retrieving and updating data.
 Until SQL:1999, SQL did not contain flow of
control commands. These had to be implemented
using a programming or job-control language, or
interactively by the decisions of user.
8
Objectives of SQL
 SQL is relatively easy to learn:
– it is non-procedural – you specify what
information you require, rather than how to get
it;
– it is essentially free-format.
Objectives of SQL
 Consists of standard English words:
1) CREATE TABLE Staff(staffNo VARCHAR(5),
lName VARCHAR(15),
salary DECIMAL(7,2));
2) INSERT INTO Staff VALUES (‘SG16’, ‘Brown’,
8300);
3) SELECT staffNo, lName, salary
FROM Staff
WHERE salary > 10000;
10
Objectives of SQL
 Can be used by range of users including DBAs,
management, application developers, and other
types of end users.
 An ISO standard now exists for SQL, making it
both the formal and de facto standard language
for relational databases.
Pearson Education © 2009
11
History of SQL
 In 1974, D. Chamberlin (IBM San Jose
Laboratory) defined language called ‘Structured
English Query Language’ (SEQUEL).
 A revised version, SEQUEL/2, was defined in
1976 but name was subsequently changed to SQL
for legal reasons.
Pearson Education © 2009
12
History of SQL
 Still pronounced ‘see-quel’, though official
pronunciation is ‘S-Q-L’.
 IBM subsequently produced a prototype DBMS
called System R, based on SEQUEL/2.
 Roots of SQL, however, are in SQUARE
(Specifying Queries as Relational Expressions),
which predates System R project.
Pearson Education © 2009
13
History of SQL
 In late 70s, ORACLE appeared and was probably first
commercial RDBMS based on SQL.
 In 1987, ANSI and ISO published an initial standard for
SQL.
 In 1989, ISO published an addendum that defined an
‘Integrity Enhancement Feature’.
 In 1992, first major revision to ISO standard occurred,
referred to as SQL2 or SQL/92.
 In 1999, SQL:1999 was released with support for objectoriented data management.
 In late 2003, SQL:2003 was released.
Pearson Education © 2009
14
Importance of SQL
 SQL has become part of application architectures
such as IBM’s Systems Application Architecture.
 It is strategic choice of many large and influential
organizations (e.g. X/OPEN).
 SQL is Federal Information Processing Standard
(FIPS) to which conformance is required for all
sales of databases to American Government.
Pearson Education © 2009
15
Importance of SQL
 SQL is used in other standards and even
influences development of other standards as a
definitional tool. Examples include:
– ISO’s Information Resource Directory System
(IRDS) Standard
– Remote Data Access (RDA) Standard.
Pearson Education © 2009
16
Writing SQL Commands
 SQL statement consists of reserved words and userdefined words.
– Reserved words are a fixed part of SQL and must
be spelt exactly as required and cannot be split
across lines.
– User-defined words are made up by user and
represent names of various database objects such
as relations, columns, views.
Pearson Education © 2009
17
Writing SQL Commands
 Most components of an SQL statement are case
insensitive, except for literal character data.
 More readable with indentation and lineation:
– Each clause should begin on a new line.
– Start of a clause should line up with start of
other clauses.
– If clause has several parts, should each appear
on a separate line and be indented under start
of clause.
Pearson Education © 2009
Writing SQL Commands
 Adding table rows
 Saving table changes
 Listing table rows
 Updating table rows
 Restoring table contents
 Deleting table rows
Data Manipulation
 Select: query data in the database
 Insert: insert data into a table
 Update: updates data in a table
 Delete: delete data from a table
Simple Query
 Select specifies which columns are to appear in the output
 From specifies the table(s) to be used
 Where filters the rows subject to some condition(2)
Simple Query
 Group By forms groups of rows with the same column value
 Having filters the groups subject to some condition
 Order By specifies the order of the output
Retrieve all Columns and all Rows
SELECT firstColumn, …,lastColumn
FROM tableName;
SELECT *
FROM tableName;
Use of Distinct
SELECT DISTINCT columnName
FROM tableName
Comparison Search Condition
= equals
< > is not equal to (ISO standard)
!= “ “ “ “ (allowed in some dialects)
< is less than
> is greater than
<= is less than or equal to
>= is greater than or equal to
Comparison Search Condition
 An expression is evaluated left to right
 Subexpressions in brackets are evaluated first
 NOTs are evaluated before ANDs and Ors
 ANDs are evaluated before ORs
Range Search Condition
SELECT columnName
FROM tableName
WHERE columnName BETWEEN 20
AND 30;
SELECT columnName
FROM tableName
WHERE columnName >=20
AND columName <=30:
Set Membership Search Condition
SELECT columnName
FROM tableName
WHERE columnName
IN (‘name1’, ‘name2’);
SELECT columnName
FROM tableName
WHERE columnName =‘name1’
OR columnName =‘name2’;
Pattern Matching Symbols

%:
_
‘h%’:
‘h_ _ _’:
‘%e’:
represents any sequence of zero or more characters (wildcard).
represents any single character
begins with the character h
four character string beginning with the character h.
any sequence of characters, of length at least 1, ending with the
character e.
‘%CS157B%’: any sequence of characters of any length containing
CS157B

AssignmentTutorOnline

LIKE ‘h%’: begins with the character h.
NOT LIKE ‘h%’: does not begin with the character h
Sorting
 The ORDER BY clause
– Consists of list of column identifiers that the result is to be
sorted on, separated by commas.
– Allows the retrieved rows to be ordered by ascending (ASC)
or descending (DESC) order
Sorting
 Column identifier may be
– A column name
– A column number (deprecated)
Sorting
SELECT type, rent
FROM tableName
ORDER BY type, rent ASC;
Aggregate Functions
 COUNT returns the number …
 SUM returns the sum …
 AVG returns the average …
 MIN returns the smallest …
 MAX returns the largest …
Value in a specified column
Group by Clause
 When GROUP BY is used, each item in the SELECT list must
be single-valued per group.
 The SELECT clause may contain only
– Column Names
– Aggregate functions
– Constants
– An expression involving combinations of the above
Grouping
SELECT dept, COUNT (staffNo) AS my count
SUM(salary)
FROM tableName
GROUP BY dept
ORDER BY dept;
35
Writing SQL Commands
 Use extended form of BNF notation:
– Upper-case letters represent reserved words.
– Lower-case letters represent user-defined words.
– | indicates a choice among alternatives.
– Curly braces indicate a required element.
– Square brackets indicate an optional element.
– … indicates optional repetition (0 or more).
Pearson Education © 2009
36
Literals
 Literals are constants used in SQL statements.
 All non-numeric literals must be enclosed in
single quotes (e.g. ‘London’).
 All numeric literals must not be enclosed in
quotes (e.g. 650.00).
Pearson Education © 2009
Literals
 Non-numeric data values must be enclosed in single quotes:
– ‘16 Holland Drive’
– ‘CS157B’
 Numeric data values must NOT be enclosed in single quotes:
– 6
– 600.00
38
SELECT Statement
SELECT [DISTINCT | ALL]
*

FROM
[WHERE
[GROUP BY
TableName [alias] [, …]
condition]
columnList] [HAVING condition]

[ORDER BY columnList]
Pearson Education © 2009
39
SELECT Statement
FROM Specifies table(s) to be used.
WHERE Filters rows.
GROUP BY Forms groups of rows with same
column value.
HAVING Filters groups subject to some
condition.
SELECT Specifies which columns are to
appear in output.
ORDER BY Specifies the order of the output.
Pearson Education © 2009
40
SELECT Statement
 Order of the clauses cannot be changed.
 Only SELECT and FROM are mandatory.
Pearson Education © 2009
41
Example 6.1 All Columns, All Rows
List full details of all staff.
SELECT staffNo, fName, lName, address,
position, sex, DOB, salary, branchNo
FROM Staff;
 Can use * as an abbreviation for ‘all columns’:
SELECT *
FROM Staff;
Pearson Education © 2009
42
Example 6.1 All Columns, All Rows
Pearson Education © 2009
43
Example 6.2 Specific Columns, All Rows
Produce a list of salaries for all staff, showing only
staff number, first and last names, and salary.
SELECT staffNo, fName, lName, salary
FROM Staff;
Pearson Education © 2009
44
Example 6.2 Specific Columns, All Rows
Pearson Education © 2009
45
Example 6.3 Use of DISTINCT
List the property numbers of all properties that
have been viewed.
SELECT propertyNo
FROM Viewing;
Pearson Education © 2009
46
Example 6.3 Use of DISTINCT
 Use DISTINCT to eliminate duplicates:
SELECT DISTINCT propertyNo
FROM Viewing;
Pearson Education © 2009
47
Example 6.4 Calculated Fields
Produce list of monthly salaries for all staff,
showing staff number, first/last name, and salary.
SELECT staffNo, fName, lName, salary/12
FROM Staff;
Pearson Education © 2009
48
Example 6.4 Calculated Fields
 To name column, use AS clause:
SELECT staffNo, fName, lName, salary/12
AS monthlySalary
FROM Staff;
Pearson Education © 2009
49
Example 6.5 Comparison Search Condition
List all staff with a salary greater than 10,000.
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary > 10000;
Pearson Education © 2009
50
Example 6.6 Compound Comparison Search Condition
List addresses of all branch offices in London or
Glasgow.
SELECT *
FROM Branch
WHERE city = ‘London’ OR city = ‘Glasgow’;
Pearson Education © 2009
51
Example 6.7 Range Search Condition
List all staff with a salary between 20,000 and
30,000.
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary BETWEEN 20000 AND 30000;
 BETWEEN test includes the endpoints of range.
Pearson Education © 2009
52
Example 6.7 Range Search Condition
Pearson Education © 2009
53
Example 6.7 Range Search Condition
 Also a negated version NOT BETWEEN.
 BETWEEN does not add much to SQL’s
expressive power. Could also write:
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary>=20000 AND salary <= 30000;
 Useful, though, for a range of values.
Pearson Education © 2009
54
Example 6.8 Set Membership
List all managers and supervisors.
SELECT staffNo, fName, lName, position
FROM Staff
WHERE position IN (‘Manager’, ‘Supervisor’);
Pearson Education © 2009
55
Example 6.8 Set Membership
 There is a negated version (NOT IN).
 IN does not add much to SQL’s expressive power.
Could have expressed this as:
SELECT staffNo, fName, lName, position
FROM Staff
WHERE position=‘Manager’ OR
position=‘Supervisor’;
 IN is more efficient when set contains many values.
Pearson Education © 2009
56
Example 6.9 Pattern Matching
Find all owners with the string ‘Glasgow’ in their
address.
SELECT ownerNo, fName, lName, address, telNo
FROM PrivateOwner
WHERE address LIKE ‘%Glasgow%’;
Pearson Education © 2009
57
Example 6.9 Pattern Matching
 SQL has two special pattern matching symbols:
– %: sequence of zero or more characters;
– _ (underscore): any single character.
 LIKE ‘%Glasgow%’ means a sequence of
characters of any length containing ‘Glasgow’.
Pearson Education © 2009
58
Example 6.10 NULL Search Condition
List details of all viewings on property PG4
where a comment has not been supplied.
 There are 2 viewings for property PG4, one with
and one without a comment.
 Have to test for null explicitly using special
keyword IS NULL:
SELECT clientNo, viewDate
FROM Viewing
WHERE propertyNo = ‘PG4’ AND
comment IS NULL;
Pearson Education © 2009
59
Example 6.10 NULL Search Condition
 Negated version (IS NOT NULL) can test for
non-null values.
Pearson Education © 2009
60
Example 6.11 Single Column Ordering
List salaries for all staff, arranged in descending
order of salary.
SELECT staffNo, fName, lName, salary
FROM Staff
ORDER BY salary DESC;
Pearson Education © 2009
61
Example 6.11 Single Column Ordering
Pearson Education © 2009
62
Example 6.12 Multiple Column Ordering
Produce abbreviated list of properties in order of
property type.
SELECT propertyNo, type, rooms, rent
FROM PropertyForRent
ORDER BY type;
Pearson Education © 2009
63
Example 6.12 Multiple Column Ordering
Pearson Education © 2009
64
Example 6.12 Multiple Column Ordering
 Four flats in this list – as no minor sort key
specified, system arranges these rows in any order
it chooses.
 To arrange in order of rent, specify minor order:
SELECT propertyNo, type, rooms, rent
FROM PropertyForRent
ORDER BY type, rent DESC;
Pearson Education © 2009
65
Example 6.12 Multiple Column Ordering
Pearson Education © 2009
66
SELECT Statement – Aggregates
 ISO standard defines five aggregate functions:
COUNT returns number of values in specified
column.
SUM returns sum of values in specified column.
AVG returns average of values in specified column.
MIN returns smallest value in specified column.
MAX returns largest value in specified column.
Pearson Education © 2009
67
SELECT Statement – Aggregates
 Each operates on a single column of a table and
returns a single value.
 COUNT, MIN, and MAX apply to numeric and
non-numeric fields, but SUM and AVG may be
used on numeric fields only.
 Apart from COUNT(*), each function eliminates
nulls first and operates only on remaining nonnull values.
Pearson Education © 2009
68
SELECT Statement – Aggregates
 COUNT(*) counts all rows of a table, regardless
of whether nulls or duplicate values occur.
 Can use DISTINCT before column name to
eliminate duplicates.
 DISTINCT has no effect with MIN/MAX, but
may have with SUM/AVG.
Pearson Education © 2009
69
SELECT Statement – Aggregates
 Aggregate functions can be used only in
SELECT list and in HAVING clause.
 If SELECT list includes an aggregate function
and there is no GROUP BY clause, SELECT list
cannot reference a column out with an aggregate
function. For example, the following is illegal:
SELECT staffNo, COUNT(salary)
FROM Staff;
Pearson Education © 2009
70
Example 6.13 Use of COUNT(*)
How many properties cost more than £350 per
month to rent?
SELECT COUNT(*) AS myCount
FROM PropertyForRent
WHERE rent > 350;
Pearson Education © 2009
71
Example 6.14 Use of COUNT(DISTINCT)
How many different properties viewed in May ‘04?
SELECT COUNT(DISTINCT propertyNo) AS myCount
FROM Viewing
WHERE viewDate BETWEEN ‘1-May-04’
AND ‘31-May-04’;
Pearson Education © 2009
72
Example 6.15 Use of COUNT and SUM
Find number of Managers and sum of their
salaries.
SELECT COUNT(staffNo) AS myCount,
SUM(salary) AS mySum
FROM Staff
WHERE position = ‘Manager’;
Pearson Education © 2009
73
Example 6.16 Use of MIN, MAX, AVG
Find minimum, maximum, and average staff
salary.
SELECT MIN(salary) AS myMin,
MAX(salary) AS myMax,
AVG(salary) AS myAvg
FROM Staff;
Pearson Education © 2009
74
SELECT Statement – Grouping
 Use GROUP BY clause to get sub-totals.
 SELECT and GROUP BY closely integrated:
each item in SELECT list must be single-valued
per group, and SELECT clause may only contain:
– column names
– aggregate functions
– constants
– expression involving combinations of the above.
Pearson Education © 2009
75
SELECT Statement – Grouping
 All column names in SELECT list must appear in
GROUP BY clause unless name is used only in an
aggregate function.
 If WHERE is used with GROUP BY, WHERE is
applied first, then groups are formed from
remaining rows satisfying predicate.
 ISO considers two nulls to be equal for purposes
of GROUP BY.
Pearson Education © 2009
76
Example 6.17 Use of GROUP BY
Find number of staff in each branch and their
total salaries.

SELECT branchNo,
COUNT(staffNo) AS myCount,

SUM(salary) AS mySum
FROM Staff
GROUP BY branchNo
ORDER BY branchNo;
Pearson Education © 2009
77
Example 6.17 Use of GROUP BY
Pearson Education © 2009
78
Restricted Groupings – HAVING clause
 HAVING clause is designed for use with GROUP
BY to restrict groups that appear in final result
table.
 Similar to WHERE, but WHERE filters
individual rows whereas HAVING filters groups.
 Column names in HAVING clause must also
appear in the GROUP BY list or be contained
within an aggregate function.
Pearson Education © 2009
79
Example 6.18 Use of HAVING
For each branch with more than 1 member of
staff, find number of staff in each branch and
sum of their salaries.
SELECT branchNo,
COUNT(staffNo) AS myCount,
SUM(salary) AS mySum
FROM Staff
GROUP BY branchNo
HAVING COUNT(staffNo) > 1
ORDER BY branchNo;
Pearson Education © 2009
80
Example 6.18 Use of HAVING
Pearson Education © 2009
81
Subqueries
 Some SQL statements can have a SELECT
embedded within them.
 A subselect can be used in WHERE and
HAVING clauses of an outer SELECT, where it
is called a subquery or nested query.
 Subselects may also appear in INSERT,
UPDATE, and DELETE statements.
Pearson Education © 2009
82
Example 6.19 Subquery with Equality
List staff who work in branch at ‘163 Main St’.
SELECT staffNo, fName, lName, position
FROM Staff
WHERE branchNo =
(SELECT branchNo
FROM Branch
WHERE street = ‘163 Main St’);
Pearson Education © 2009
83
Example 6.19 Subquery with Equality
 Inner SELECT finds branch number for branch
at ‘163 Main St’ (‘B003’).
 Outer SELECT then retrieves details of all staff
who work at this branch.
 Outer SELECT then becomes:
SELECT staffNo, fName, lName, position
FROM Staff
WHERE branchNo = ‘B003’;
Pearson Education © 2009
84
Example 6.19 Subquery with Equality
Pearson Education © 2009
85
Example 6.20 Subquery with Aggregate
List all staff whose salary is greater than the average
salary, and show by how much.
SELECT staffNo, fName, lName, position,
salary – (SELECT AVG(salary) FROM Staff) As SalDiff
FROM Staff
WHERE salary >
(SELECT AVG(salary)
FROM Staff);
Pearson Education © 2009
86
Example 6.20 Subquery with Aggregate
 Cannot write ‘WHERE salary > AVG(salary)’
 Instead, use subquery to find average salary
(17000), and then use outer SELECT to find those
staff with salary greater than this:
SELECT staffNo, fName, lName, position,
salary – 17000 As salDiff
FROM Staff
WHERE salary > 17000;
Pearson Education © 2009
87
Example 6.20 Subquery with Aggregate
Pearson Education © 2009
88
Subquery Rules
 ORDER BY clause may not be used in a
subquery (although it may be used in outermost
SELECT).
 Subquery SELECT list must consist of a single
column name or expression, except for
subqueries that use EXISTS.
 By default, column names refer to table name in
FROM clause of subquery. Can refer to a table
in FROM using an alias.
Pearson Education © 2009
Lab
 SQL Server

Computational problem solution

Computational problem solution

02/10/2017
1
ALGORITHMS &
PSEUDOCODE
Computational problem solution
■ Mastery of a language syntax is not sufficient
■ You must fully understand the problem and design a
solution before any code
– Develop the algorithms first (e.g. using
pseudocode, flow charts)
■ Experienced programmers spend most of the time
developing algorithms than coding.
02/10/2017
2
Problem solution steps
■ Problem definition: Understand the problem clearly first before
formulating a solution
■ Problem analysis: Develop mathematical formulation, define inputs,
outputs and their relationships, state any assumptions
■ Algorithm design: Formulate a series of step by step instructions for
solving the problem (pseudocode or flow charts)
■ Program development: Conversion of the algorithm into the desired
programming language
■ Testing and verification: Does the program perform its intended
function, debugging, verification
What is an algorithm
■ Algorithm is a step by step procedure for solving a problem in a finite
amount of time.
■ Typically, algorithms go with the data structures to manipulate the
data (e.g., the methods of a class).
■ Most algorithms transform input objects into output objects.
– Input – An algorithm has input values from a specified set
– Output – From each set of input values an algorithm produces
output values from a specified set. The output values are the
solution to the problem.
02/10/2017
3
Properties of algorithms
■ Algorithms must:
• Be definite – the algorithms must have a defined steps
• Be correct: always give a correct solution
• Terminate: must terminate in a finite time
• Be complete: always gives a solution when one exists
• Generic- The algorithm should be applicable for all
problems of the desired form, not just for a particular set of
input values.
Designing algorithms
■ A general approach is:
• Understand the problem
• Select an algorithm (e.g., brute force, divide and conquer
etc.…)
• Select appropriate data structures
• Prove the algorithm terminates, that it is complete and correct
02/10/2017
4
Pseudocode and
Flow charts
Pseudocode
■ Pseudocode is an English like presentation of the steps needed to
solve a problem.
■ Should make sense when read (though not necessarily
grammatically correct)
■ It allows the programmer to solve the problem at a level that hides
the detail while concentrating on the problem requirements, i.e.
concentrate on the big picture.
■ Pseudocode is language independent.
■ Pseudocode will be used to write the algorithm once the data
structure is fully understood.
■ Pseudocode is indented like code
■ Pseudocode is followed by implementation with the appropriate
language such as Python, C, C#, Java etc.
02/10/2017
5
Program components
Every program is only a combination of these structures: Sequence,
Selection & Repetition
• Sequence – statements executed one after the other
e.g. sum = sum+i;
i=i+1;
• Condition/decision – evaluation of a logical expression and execution
of different parts of the program, depending on the results of the
evaluation
e.g if…else statement & switch statement
• Repetition – implements looping processing
e.g. for statement
while statement
do…while statement
Pseudo code – example
■ Simple programming problem: Convert a price from British pounds
into Dollars.
■ Pseudocode
Compute the price of the item in dollars
Input: Price in pounds
Output: Price in dollars
BEGIN
Set DollarPrice = (3 /2) * PoundPrice
Print DollarPrice
END
02/10/2017
6
Pseudo code – example
BEGIN
get coordinates of measuring device
record measurement
check measurement for being within range
IF measurement is within range THEN
load measurement in database
ELSE ask another measurement
END IF
END
Reading data from a sensor
Input: base and exponent
Output: result
BEGIN
Check base and exponent are legal
result = 1
While exponent >0
result = result*base
exponent = exponent -1
End While
Print result
END
Pseudo code – example
Raising a number to a power
02/10/2017
7
Flow chart
Basic components:
• Boxes – contain processing step
(instructions to be executed)
• Diamonds – contain logical conditions
• Arrows – show the flow of the control
– one direction only
• Boxes with round corners – indicate the start and end
point
sum=sum+i
i=i+1
F T
Start
End
Flow chart – sequence
Sequence – two (or more) processing boxes connected
by an arrow
First task
Next task
More statements can be included in each box)
Fahrenheit = int(raw_input(“Enter a
temperature in Fahrenheit: “))
Celsius = (Fahrenheit – 32) *
5.0/9.0
print “Temperature:”, Fahrenheit, “F
= “, Celsius, ” C”
02/10/2017
8
Programming constructs: Sequence
structure
instruction 1;
instruction 2;
instruction 3;

Examples:
■ Getting ready in the morning to go to work
■ Recipe to make your favorite food
■ Assembly instructions for a toy
What is common about these activities?
Sequence
Programming constructs: Conditional
statements
■ Go to movie or study?
■ Eat salad or sandwich?
■ Go to job or go for higher studies?
What is the common thing here? Selection.
IF condition is true THEN
do this;
ELSE
do that;
ENDIF
02/10/2017
9
Programming constructs: Loops
■ Eat chips from a packet
■ Go on a shopping spree with lot of cash!
■ Take an exam that has several questions
What is the common thing here?
Repetition / Loops
WHILE (more items to process)
process the next item;
ENDWHILE
FOR month = 1 to 12
do monthly processing
ENDFOR
NEXT TOPIC…
INTRODUCTION TO
PROGRAMMING IN
PYTHON

AssignmentTutorOnline

development of applications

development of applications

22/11/2018
1

Evaluation on use of an
IDE for development of
applications contrasted
with not using an IDE
WEEK 9
Advantage on using IDE over Console
1. IDE like Python IDLE will highlight
simple syntax errors on compilation, so
you don’t have the experience of
searching the errors in coding for hours.
2. Python IDE have an “autocomplete”
feature, so that if you type a. and pause,
the IDE will present you with a list of the
various associated functions available,
so you don’t need to remember the
various available commands..
Require bracket

AssignmentTutorOnline

22/11/2018
2

More advantages
3. Most IDE including Python IDLE has a build
Automator of some kind that compiles the
code, and builds an executable program by
adding in any necessary libraries, some of
which you may not even be aware of. Those
same tools make it easier to organize and
include any optional libraries, or ones you’ve
created yourself.
4. Use of IDE like Python IDLE make use of
Text highlight, where it make use of different
colours to identify variables, function,
commands, etc. This gives an advantage to
the developer in findings errors.
More Advantages
5. In IDE, debugging becomes more friendly. For example in
Python IDLE, has in‐built debugging features has help to trace
the errors line by line and helps to find the logical errors
quickly.
6. Navigating becomes easier as it is easier to find compile‐
time error or run‐time exception error directly from the error
details.
7. Finally a few more tools that IDE’s offer can be resource
management and the ability to compile your code. When
writing a new program there are usually many different files
that have been referenced in specific path locations so it is
very important for the running of the program that these files
are in the correct locations.
8. Using an IDE makes it easy to see a visual representation of
the location of files and makes it more understandable for the
features to be installed for the user.

22/11/2018
3

Overall Advantages
1. Increased Efficiency – faster coding with less effort
2. Collaboration – A group of programmers can easily work together
within an IDE
3. Project Management – Program resources are easily.
4. Saves time: Helps to debug faster.
5. Quality: Helps to maintain quality standards.
Few slight disadvantages.
1. Sometimes IDE find errors but fails to point them to a correct location:
For example you’ll be confused at the debugger for telling you that
there’s a type mismatch on line 354 when the root of the problem is a
logic error on line 256.
2. IDE are resource hungry so it need more RAM and processing power to
run fast and smoothly.
3. Most of the sophisticated IDE are commercial and need to buy at a
heavy price.
4. Some IDE are difficult to learn all its functionality such as Visual Studio
enterprise edition.

22/11/2018
4

Features of Python IDLE
Features of Python IDLE
In Python IDLE, we can adjust all
the colors of idle under
opƟons→configure
idle→HighlighƟng. IDLE does not
support extensions, however, it’s
open source and probably some
forks with extra features are
available.

22/11/2018
5

Indentation and other preferences
Here the tabs allow us to modify fonts for
better vision and editing and the next tab
“Highlighting” is there for syntax highlighting,
keystrokes assigned to particular operations,
and a few other miscellaneous things.
Along with the fonts, Indentation tab is there
for increasing and decreasing the number of
spacing for indentation of the code while
writing in python IDLE.
Integrated Debugger
The in‐built debugger can be retrieved
by clicking to Debug‐>debugger.
The user interface in the new version
uses a paned window to separate the
controls and stack on the left from the
variable display on the right. Both the
stack and variable display are
implemented using tree view widgets.
This also provides a great deal of
control when it comes to how much
space each element will use.

22/11/2018
6

More to search through lengthy codes
Python has tools to search for any keyword in lengthy programs and can help you to
replace any words. The feature can be assessed by pressing CTRL+H. This saves a lot of
time and effort, if we need to change a variable name which is spread whole across the
program.
Links for more Learning/ Research
https://docs.python.org/3/library/idle.html (More on Python IDLE)
https://www.thewindowsclub.com/how‐to‐get‐started‐with‐visual‐studio (To learn more on
visual studio)
https://www.youtube.com/watch?v=_okUV47eM5c
https://www.youtube.com/watch?v=KHPoVpL7zHg
https://www.youtube.com/watch?v=uZGZNEyyeKs
https://www.youtube.com/watch?v=bO7wpzgy74A
https://www.youtube.com/watch?v=yc2CROtTsC0
https://www.youtube.com/watch?v=FJx5mutt1uk
(Above 6 Youtube links to learn using Python in visual studio )

Software Depression

Software Depression

23/10/2017
1

Lecture 7
2
Last few decades have seen proliferation
of software applications, many requiring
constant maintenance involving:
– correcting faults,
– implementing new user requirements,
– modifying software to run on new or upgraded
platforms.
 Effort spent on maintenance began to absorb
resources at an alarming rate.
Pearson Education © 2009
Software Depression

AssignmentTutorOnline

23/10/2017
2

3
 As a result, many major software projects were
– late,
– over budget,
– unreliable,
– difficult to maintain,
– performed poorly.
 In late 1960s, led to ‘software crisis’, now refer
to as the ‘software depression’.
Pearson Education © 2009
Software Depression
4
 Major reasons for failure of software projects
includes:
– lack of a complete requirements specification;
– lack of appropriate development methodology;
– poor decomposition of design into manageable
components.
 Structured approach to development was
proposed called Information Systems Lifecycle
(ISLC).
Pearson Education © 2009
Software Depression

23/10/2017
3

5
Resources that enable collection, management,
control, and dissemination of information
throughout an organization.
 Database is fundamental component of IS, and
its development/usage should be viewed from
perspective of the wider requirements of the
organization.
Pearson Education © 2009
Information System
6
 Database planning
 System definition
 Requirements collection and analysis
 Database design
 DBMS selection (optional)
Pearson Education © 2009
Database System Development Lifecycle

23/10/2017
4

7
 Application design
 Prototyping (optional)
 Implementation
 Data conversion and loading
 Testing
 Operational maintenance
Pearson Education © 2009
Database System Development Lifecycle
8
Pearson Education © 2009
Stages of the Database System Development
Lifecycle

23/10/2017
5

9
 Management activities that allow stages of
database system development lifecycle to be
realized as efficiently and effectively as
possible.
 Must be integrated with overall IS strategy of
the organization.
Pearson Education © 2009
Database Planning
10
 Mission statement for the database project
defines major aims of database application.
 Those driving database project normally define
the mission statement.
 Mission statement helps clarify purpose of the
database project and provides clearer path
towards the efficient and effective creation of
required database system.
Pearson Education © 2009
Database Planning – Mission Statement

23/10/2017
6

11
 Once mission statement is defined, mission
objectives are defined.
 Each objective should identify a particular task
that the database must support.
 May be accompanied by some additional
information that specifies the work to be done,
the resources with which to do it, and the
money to pay for it all.
Pearson Education © 2009
Database Planning – Mission Objectives
12
 Database planning should also include
development of standards that govern:
– how data will be collected,
– how the format should be specified,
– what necessary documentation will be needed,
– how design and implementation should proceed.
Pearson Education © 2009
Database Planning

23/10/2017
7

13
 Describes scope and boundaries of database
system and the major user views.
 User view defines what is required of a
database system from perspective of:
– a particular job role (such as Manager or
Supervisor) or
– enterprise application area (such as
marketing, personnel, or stock control).
Pearson Education © 2009
System Definition
14
 Database application may have one or more
user views.
 Identifying user views helps ensure that no
major users of the database are forgotten when
developing requirements for new system.
 User views also help in development of complex
database system allowing requirements to be
broken down into manageable pieces.
Pearson Education © 2009
System Definition

23/10/2017
8

15
Pearson Education © 2009
Representation of a Database System with Multiple User
Views
16
 Process of collecting and analyzing information
about the part of organization to be supported
by the database system, and using this
information to identify users’ requirements of
new system.
Pearson Education © 2009
Requirements Collection and Analysis

23/10/2017
9

17
 Information is gathered for each major user view
including:
– a description of data used or generated;
– details of how data is to be used/generated;
– any additional requirements for new database
system.
 Information is analyzed to identify requirements
to be included in new database system. Described
in the requirements specification.
Pearson Education © 2009
Requirements Collection and Analysis
18
 Another important activity is deciding how to
manage the requirements for a database
system with multiple user views.
 Three main approaches:
– centralized approach;
– view integration approach;
– combination of both approaches.
Pearson Education © 2009
Requirements Collection and Analysis

23/10/2017
10

19
 Centralized approach
– Requirements for each user view are merged
into a single set of requirements.
– A data model is created representing all user
views during the database design stage.
Pearson Education © 2009
Requirements Collection and Analysis
20
Pearson Education © 2009
Centralized Approach to Managing Multiple User Views

23/10/2017
11

21
 View integration approach
– Requirements for each user view remain as
separate lists.
– Data models representing each user view are
created and then merged later during the
database design stage.
Pearson Education © 2009
Requirements Collection and Analysis
22
 Data model representing single user view (or a
subset of all user views) is called a local data
model.
 Each model includes diagrams and
documentation describing requirements for one
or more but not all user views of database.
Pearson Education © 2009
Requirements Collection and Analysis

23/10/2017
12

23
 Local data models are then merged at a later
stage during database design to produce a global
data model, which represents all user views for
the database.
Pearson Education © 2009
Requirements Collection and Analysis
24
Pearson Education © 2009
View Integration Approach to Managing Multiple User
Views

23/10/2017
13

25
 Process of creating a design for a database that
will support the enterprise’s mission statement
and mission objectives for the required
database system.
Pearson Education © 2009
Database Design
26
 Main approaches include:
– Top-down
– Bottom-up
– Inside-out
– Mixed
Pearson Education © 2009
Database Design

23/10/2017
14

27
 Main purposes of data modeling include:
– to assist in understanding the meaning
(semantics) of the data;
– to facilitate communication about the
information requirements.
 Building data model requires answering
questions about entities, relationships, and
attributes.
Pearson Education © 2009
Database Design
28
 A data model ensures we understand:
– each user’s perspective of the data;
– nature of the data itself, independent of its
physical representations;
– use of data across user views.
Pearson Education © 2009
Database Design

23/10/2017
15

29
Pearson Education © 2009
Criteria to Produce an Optimal Data Model
30
 Three phases of database design:
– Conceptual database design
– Logical database design
– Physical database design.
Pearson Education © 2009
Database Design

23/10/2017
16

31
 Process of constructing a model of the data
used in an enterprise, independent of all
physical considerations.
 Data model is built using the information in
users’ requirements specification.
 Conceptual data model is source of
information for logical design phase.
Pearson Education © 2009
Conceptual Database Design
32
 Process of constructing a model of the data
used in an enterprise based on a specific data
model (e.g. relational), but independent of a
particular DBMS and other physical
considerations.
 Conceptual data model is refined and mapped
on to a logical data model.
Pearson Education © 2009
Logical Database Design

23/10/2017
17

33
 Process of producing a description of the
database implementation on secondary storage.
 Describes base relations, file organizations, and
indexes used to achieve efficient access to data.
Also describes any associated integrity
constraints and secuirty measures.
 Tailored to a specific DBMS system.
Pearson Education © 2009
Physical Database Design
34
Pearson Education © 2009
Three-Level ANSI-SPARC Architecture and Phases of
Database Design

23/10/2017
18

35
 Selection of an appropriate DBMS to support
the database system.
 Undertaken at any time prior to logical design
provided sufficient information is available
regarding system requirements.
 Main steps to selecting a DBMS:
– define Terms of Reference of study;
– shortlist two or three products;
– evaluate products;
– recommend selection and produce report.
Pearson Education © 2009
DBMS Selection
36
Pearson Education © 2009
DBMS Evaluation Features

23/10/2017
19

37
Pearson Education © 2009
DBMS Evaluation Features
38
Pearson Education © 2009
Example – Evaluation of DBMS Product

23/10/2017
20

39
 Design of user interface and application
programs that use and process the database.
 Database design and application design are
parallel activities.
 Includes two important activities:
– transaction design;
– user interface design.
Pearson Education © 2009
Application Design
40
 An action, or series of actions, carried out by a
single user or application program, which
accesses or changes content of the database.
 Should define and document the high-level
characteristics of the transactions required.
Pearson Education © 2009
Application Design – Transactions

23/10/2017
21

41
 Important characteristics of transactions:
– data to be used by the transaction;
– functional characteristics of the transaction;
– output of the transaction;
– importance to the users;
– expected rate of usage.
 Three main types of transactions: retrieval,
update, and mixed.
Pearson Education © 2009
Application Design – Transactions
42
 Building working model of a database system.
 Purpose
– to identify features of a system that work well,
or are inadequate;
– to suggest improvements or even new features;
– to clarify the users’ requirements;
– to evaluate feasibility of a particular system
design.
Pearson Education © 2009
Prototyping

23/10/2017
22

43
 Physical realization of the database and application
designs.
– Use DDL to create database schemas and empty
database files.
– Use DDL to create any specified user views.
– Use 3GL or 4GL to create the application
programs. This will include the database
transactions implemented using the DML,
possibly embedded in a host programming
language.
Pearson Education © 2009
Implementation
44
 Transferring any existing data into new database
and converting any existing applications to run on
new database.
 Only required when new database system is
replacing an old system.
– DBMS normally has utility that loads existing
files into new database.
 May be possible to convert and use application
programs from old system for use by new system.
Pearson Education © 2009
Data Conversion and Loading

23/10/2017
23

45
 Process of running the database system with intent
of finding errors.
 Use carefully planned test strategies and realistic
data.
 Testing cannot show absence of faults; it can show
only that software faults are present.
 Demonstrates that database and application
programs appear to be working according to
requirements.
Pearson Education © 2009
Testing
46
 Should also test usability of system.
 Evaluation conducted against a usability
specification.
 Examples of criteria include:
– Learnability;
– Performance;
– Robustness;
– Recoverability;
– Adaptability.
Pearson Education © 2009
Testing

23/10/2017
24

47
 Process of monitoring and maintaining
database system following installation.
 Monitoring performance of system.
– if performance falls, may require tuning or
reorganization of the database.
 Maintaining and upgrading database
application (when required).
 Incorporating new requirements into database
application.
Pearson Education © 2009
Operational Maintenance
48
 Support provided by CASE tools include:
– data dictionary to store information about
database system’s data;
– design tools to support data analysis;
– tools to permit development of corporate
data model, and conceptual and logical data
models;
– tools to enable prototyping of applications.
Pearson Education © 2009
CASE Tools

23/10/2017
25

49
Pearson Education © 2009
CASE Tools and Database System Development Lifecycle
50
Database Analysis and the
DreamHome Case Study
Pearson Education © 2009
Part 3

23/10/2017
26

51
 When fact-finding techniques are used in the database
application lifecycle.
 The types of facts collected in each stage of the database
application lifecycle.
 The types of documentation produced in each stage of the
database application lifecycle.
Pearson Education © 2009
Part 3 – Objectives
52
 The most commonly used fact-finding techniques.
 How to use each fact-finding technique and the advantages and
disadvantages of each.
 About a property rental company called DreamHome.
Pearson Education © 2009
Part 3 – Objectives

23/10/2017
27

53
 How to apply fact-finding techniques to the early stages of the
database application lifecycle.
Pearson Education © 2009
Part 3 – Objectives
54
 It is critical to capture the necessary facts to build the required
database application.
 These facts are captured using fact-finding techniques.
 The formal process of using techniques such as interviews and
questionnaires to collect facts about systems, requirements, and
preferences.
Pearson Education © 2009
Fact-finding techniques

23/10/2017
28

55
 Fact-finding used throughout the database application lifecycle.
Crucial to the early stages including database planning, system
definition, and requirements collection and analysis stages.
 Enables developer to learn about the terminology, problems,
opportunities, constraints, requirements, and priorities of the
organization and the users of the system.
Pearson Education © 2009
When Are Fact-Finding Techniques Used?
56
Pearson Education
© 2009
Examples of data captured and documentation
produced during the database application
lifecycle

23/10/2017
29

57
 A database developer normally uses several fact-finding
techniques during a single database project including:
– examining documentation
– interviewing
– observing the organization in operation
– research
– questionnaires
Pearson Education © 2009
Fact-Finding Techniques
58
 Can be useful
– to gain some insight as to how the need for a database arose.
– to identify the part of the organization associated with the
problem.
– To understand the current system.
Pearson Education © 2009
Examining documentation

23/10/2017
30

59
Pearson Education © 2009
Examples of types of documentation that should be
examined
Part 4
Methodology – Physical Database Design for
Relational Databases
Pearson Education © 2009

23/10/2017
31

61
 Purpose of physical database design.
 How to map the logical database design to a
physical database design.
 How to design base relations for target DBMS.
 How to design general constraints for target
DBMS.
Pearson Education © 2009
Part 4 – Objectives
62
 How to select appropriate file organizations
based on analysis of transactions.
 When to use secondary indexes to improve
performance.
 How to estimate the size of the database.
 How to design user views.
 How to design security mechanisms to satisfy
user requirements.
Pearson Education © 2009
Part 4 – Objectives

23/10/2017
32

63
 Sources of information for physical design
process includes logical data model and
documentation that describes model.
 Logical database design is concerned with the
what, physical database design is concerned
with the how.
Pearson Education © 2009
Logical v. Physical Database Design
64
Process of producing a description of the
implementation of the database on secondary
storage.
It describes the base relations, file
organizations, and indexes used to achieve
efficient access to the data, and any associated
integrity constraints and security measures.
Pearson Education © 2009
Physical Database Design

23/10/2017
33

65
 Step 3 Translate logical data model for target
DBMS
– Step 3.1 Design base relations
– Step 3.2 Design representation of derived data
– Step 3.3 Design general constraints
Pearson Education © 2009
Overview of Physical Database Design Methodology
66
 Step 4 Design file organizations and indexes
– Step 4.1 Analyze transactions
– Step 4.2 Choose file organizations
– Step 4.3 Choose indexes
– Step 4.4 Estimate disk space requirements
Pearson Education © 2009
Overview of Physical Database Design Methodology

23/10/2017
34

67
 Step 5 Design user views
 Step 6 Design security mechanisms
 Step 7 Consider the introduction of controlled
redundancy
 Step 8 Monitor and tune operational system
Pearson Education © 2009
Overview of Physical Database Design Methodology
68
To produce a relational database schema from the
logical data model that can be implemented in the
target DBMS.
 Need to know functionality of target DBMS such as how to
create base relations and whether the system supports the
definition of:
– PKs, FKs, and AKs;
– required data – i.e. whether system supports NOT
NULL;
– domains;
– relational integrity constraints;
– general constraints.
Pearson Education © 2009
Step 3 Translate Logical Data Model for Target DBMS

23/10/2017
35

69
To decide how to represent base relations
identified in logical model in target DBMS.
For each relation, need to define:
–the name of the relation;
–a list of simple attributes in brackets;
–the PK and, where appropriate, AKs and FKs.
–referential integrity constraints for any FKs
identified.
Pearson Education © 2009
Step 3.1 Design base relations
70
 From data dictionary, we have for each
attribute:
– its domain, consisting of a data type, length, and any
constraints on the domain;
– an optional default value for the attribute;
– whether it can hold nulls;
– whether it is derived, and if so, how it should be
computed.
Pearson Education © 2009
Step 3.1 Design base relations

23/10/2017
36

71
 Meaning of denormalization.
 When to denormalize to improve performance.
 Importance of monitoring and tuning the
operational system.
 How to measure efficiency.
 How system resources affect performance.
Pearson Education © 2009
Part 5 – Objectives
72
To determine whether introducing redundancy in
a controlled manner by relaxing normalization
rules will improve the performance of the system.
Pearson Education © 2009
Step 7 Consider the Introduction of Controlled
Redundancy

23/10/2017
37

73
 Result of normalization is a design that is
structurally consistent with minimal redundancy.
 However, sometimes a normalized database does
not provide maximum processing efficiency.
 May be necessary to accept loss of some benefits
of a fully normalized design in favor of
performance.
Pearson Education © 2009
Step 7 Consider the Introduction of Controlled
Redundancy
74
 Also consider that denormalization:
– makes implementation more complex;
– often sacrifices flexibility;
– may speed up retrievals but it slows down
updates.
Pearson Education © 2009
Step 7 Consider the Introduction of Controlled
Redundancy

23/10/2017
38

75
 Denormalization refers to a refinement to
relational schema such that the degree of
normalization for a modified relation is less
than the degree of at least one of the original
relations.
 Also use term more loosely to refer to
situations where two relations are combined
into one new relation, which is still normalized
but contains more nulls than original relations.
Pearson Education © 2009
Step 7 Consider the Introduction of Controlled
Redundancy
76
 Consider denormalization in following
situations, specifically to speed up frequent or
critical transactions:
– Step 7.1 Combining 1:1 relationships
– Step 7.2 Duplicating non-key attributes in
1:* relationships to reduce joins
– Step 7.3 Duplicating foreign key attributes
in 1:* relationships to reduce joins
Pearson Education © 2009
Step 7 Consider the Introduction of Controlled
Redundancy

23/10/2017
39

77
– Step 7.4 Duplicating attributes in *:*
relationships to reduce joins
– Step 7.5 Introducing repeating groups
– Step 7.6 Creating extract tables
– Step 7.7 Partitioning relations.
Pearson Education © 2009
Step 7 Consider the Introduction of Controlled
Redundancy
78
Pearson Education © 2009
Sample Relation Diagram

23/10/2017
40

79
Pearson Education © 2009
Sample Relations
80
Pearson Education © 2009
Step 7.1 Combining 1:1 relationships

23/10/2017
41

81
Pearson Education © 2009
Step 7.2 Duplicating non-key attributes in 1:* relationships to
reduce joins
82
Pearson Education © 2009
Step 7.2 Duplicating non-key attributes in 1:*
relationships: Lookup Table

23/10/2017
42

83
Pearson Education © 2009
Step 7.2 Duplicating non-key attributes in 1:*
relationships: Lookup Table
84
Pearson Education © 2009
Step 7.3 Duplicating FK attributes in 1:* relationship to reduce
joins

23/10/2017
43

85
Pearson Education © 2009
Step 7.4 Duplicating attributes in *:* relationships to reduce joins
86
Pearson Education © 2009
Step 7.5 Introducing repeating groups

23/10/2017
44

87
 Reports can access derived data and perform multi
relation joins on same set of base relations. However,
data the report is based on may be relatively static or
may not have to be current.
 Possible to create a single, highly denormalized
extract table based on relations required by reports,
and allow users to access extract table directly
instead of base relations.
Pearson Education © 2009
Step 7.6 Creating extract tables
88
 Rather than combining relations together,
alternative approach is to decompose them
into a number of smaller and more
mannageable partitions.
 Two main types of partitioning: horizontal
and vertical.
Pearson Education © 2009
Step 7.7 Partitioning relations

23/10/2017
45

89
Pearson Education © 2009
Step 7.7 Partitioning relations
90
Advantages and disadvantages of denormalization

23/10/2017
46

By the end of this topic/lecture the student should be able
to:
 1.Principles and Methods for User-Centered Design
 2. Requirements Gathering
 3. Task Analysis
 4. Structured HCI Design
91
10/23/2017
Requirement and user interface
Fundamentals
 The designer must understand the requirements of the product
» Looking at similar products
» Discussing the needs of the people
» Analyzing any existing systems
 Must develop the product
» Producing a variety of representations

23/10/2017
47

System & User Interface Design
 Functionality
 Reliability
 Consistency
 Performance
 Menu driven
Requirements Gathering
 Functional requirements
 Data requirements
 Usability requirements
10/23/2017
94

23/10/2017
48

Functional requirements
 Requirements gathering or analysis is the process of finding out
what a client requires from a software system.
 Functional requirements means both what the system must do and
what the human does.
 A data flow diagram describes a system from the point of view of
the data that is passed between processes.
10/23/2017
95
Data requirements
 Focus attention on structure as opposed to processing.
 Should specify the whole system and not simply the part that may
be computerized.
 Entity and entity relationship diagrams
10/23/2017
96

23/10/2017
49

Usability Requirements
 Is that any system designed for the people to use should be easy to
learn , useful, that is contain functions people really need in their
work and be easy and pleasant to use.
 Components: Learnability, throughput, flexibility, attitude.
 Relationship between requirements usability.
10/23/2017
97
Part 6
User Interface (UI)
Pearson Education © 2009

23/10/2017
50

Fundamentals
 The designer must understand the requirements of the product
» Looking at similar products
» Discussing the needs of the people
» Analyzing any existing systems
 Must develop the product
» Producing a variety of representations
10/23/2017
System & User Interface Design
 Functionality
 Reliability
 Consistency
 Performance
 Menu driven

23/10/2017
51

The Design of Software Systems
 The waterfall model:
– Processes:
» A – Initiation
» B – Analysis or requirements gathering
» C – Design
» D – Implementation
» E – Operations management
» F – validation, verification and testing
10/23/2017
The Design of Software Systems (Contd…)
– Representations:
» 1. Application Description
» 2. Requirements specification
» 3. System Design
» 4. Product
 Problems in the waterfall model
 Prototype systems were introduced.
 Examples of user centered design
10/23/2017

23/10/2017
52

The scope of Human-computer system design
 The four key factors in interaction design
– People
– Work
– Technology
– Environment.
 Different kinds of systems
10/23/2017
Methods for user- centered design
 4 key stages in development:
– Planning, designing, implementing and managing the system.
 Four approaches to system development
– Soft System Methodology (SSM)
– Open Systems Task Analysis(OSTA)
– Multiview
– Star Life cycle
10/23/2017

23/10/2017
53

Soft Systems Methodology
1. The problem situation
2. The problem situation expressed
3. Root definitions of relevant systems
4. Building conceptual models
5. Comparison of 4 with 2
6. Feasible and desirable changes
7. Action to improve the situation
10/23/2017
Multiview Methodology (user – centered)
1. Analyse human activity
2. Analyse information
3. Analyse and design sociotechnical aspects
4. Design HCI
5. Design technical aspects
10/23/2017

23/10/2017
54

An HCI design approach (Star Model)
 Requirement specification
 Task / functional analysis
 Conceptual / formal design
 Prototyping
 Implementation
 Evaluation
10/23/2017
Requirements Gathering
 Functional requirements
 Data requirements
 Usability requirements
10/23/2017

23/10/2017
55

Functional requirements
 Requirements gathering or analysis is the process of finding out
what a client requires from a software system.
 Functional requirements means both what the system must do and
what the human does.
 A data flow diagram describes a system from the point of view of
the data that is passed between processes.
10/23/2017
Data requirements
 Focus attention on structure as opposed to processing.
 Should specify the whole system and not simply the part that may
be computerized.
 Entity and entity relationship diagrams
10/23/2017

23/10/2017
56

Usability Requirements
 Is that any system designed for the people to use should be easy to
learn , useful, that is contain functions people really need in their
work and be easy and pleasant to use.
 Components: Learnability, throughput, flexibility, attitude.
 Relationship between requirements usability.
10/23/2017
 Conceptual Design
 From logical to physical design
 Physical Design
 Completing the Design
10/23/2017
Structured HCI Design

23/10/2017
57

Structured HCI Design
 The processes and representations involved in human-computer
system design, concentrating on a structured, or formal approach.
 Conceptual design and physical design components are considered
from both designers’ and users’ perspective.
10/23/2017
Lab
 SQL Server
– Design table
– Identify PK, FK constraints
– Insert record