9: Chapter Sample Applications
Outline
Spreadsheets Databases Numeric and Symbolic Computations Computer Networks
Social Issues Applications Software Virtual Machine Hardware Algorithmic Foundations
1
Spreadsheets
An electronic spreadsheet combines elements of: a calculator a word processor a database manager a graphing tool a modeling tool ... Spreadsheet programs:
Widely used Examples:
VisiCalc MS Excel
2
Spreadsheets
A spreadsheet is a 2dimensional grid of cells:
Only a portion of the spreadsheet in visible on the screen
Rows: 1, 2, 3, ... Columns: A, B, C, ...
Cell: specifies a row and a column:
window Window can be scrolled down/up Activated using mouse or cursor Example:
D2 means the cell at 2nd row and 4th column
3
Spreadsheets
Information in each cell may be:
Label Numeric value Mathematical formula
Labels
Text information that appear on the screen in a cell Any cell can contain a label (row numbers and columns letters are also labels) Format can be chosen: Font size, boldface, ... Example
1 Item1 2 3.25
A
B
Item2 5.75
C
Total labels 9.00 numeric values
D
E
4
Spreadsheets
Numeric values:
Mathematical formulas
Like labels can be formatted e.g. only 2 digits after the decimal point negative value in parentheses Do not appear on the screen Entering a formula usually require some extra keystroke be done first Example: C2 = A2 + B2 Total (C2) is computed automatically Error message if A2 or B2 are not numeric values
Example: Payroll of a company
5
Spreadsheets
1 ID 2 101 3 102 4 103 5 104 6 105 7
A
B
Name Janet K Fred L John A
C
Age 51 43 53
D
Rate 16.60 8.50 12.35 17.80 6.70
E
Hours 94 185 250 245 53
F
Pay
G
Adam R 18
Butch H 17
Pay of Janet D2*E2 formula needed for Pay Entering the formulas:
Enter D2*E2 in cell F2 Copy (automatically supported) to other cells in column F
6
Spreadsheets
What you enter: (formulas entered) A B C D 1 ID Name Age Rate 2 101 Janet K 51 16.60 3 102 Adam R 18 8.50 4 103 Fred L 43 12.35 5 104 John A 53 17.80 6 105 Butch H 17 6.70 What you see: (values computed) A B C D 1 ID Name Age Rate 2 101 Janet K 51 16.60 3 102 Adam R 18 8.50 4 103 Fred L 43 12.35 5 104 John A 53 17.80 6 105 Butch H 17 6.70
E
Hours 94 185 250 245 53
F
Pay D2*E2 D3*E3 D4*E4 D5*E5 D6*E6
E
Hours 94 185 250 245 53
F
Pay 1560.40 1572.50 3087.50 4361.00 355.10 7
Spreadsheets
Other Features Builtin functions for: Average Maximum Minimum User selects the desired cells and apply function Graphics:
Multiple sheets can be handled at one time Formulas can be propagated to all sheets in use (if possible) 8 Create 3dimensional sheets
Data can be presented in graphical form Line graph Bar graph Pie graph etc.
Spreadsheets
Other Features (contd)
User can write macros Macro:
Example of a macro
A series of instructions called by name "Like" a function ... The name serves as a shortcut notation Use of macros saves time Select spreadsheet Select chart type Print sheet every time you call the macro the 3 tasks are done automatically
Some database functions are also included in some spreadsheet programs
9
Spreadsheets
Spreadsheet as a modeling tool
Spreadsheet software does more than just:
edit spreadsheets Perform simple calculations ...
Spreadsheets allow quick data modification and result presentation Suppose the owner of the payroll spreadsheet wants to give his/her employees a raise (in a good year) For example the increment should be 2% for each employee a new cell in the spreadsheet to hold the fixed increment: 2% a new column headed New Pay is also needed to store the incremented pay for each employee
10
Spreadsheets
1 ID 2 101 3 102 4 103 5 104 6 105
7 8 9
A
B
Name Janet K Adam R Fred L John A Butch H Base Increase %
C
Age 51 18 43 53 17 2
D
Rate 16.60 8.50 12.35 17.80 6.70
E
Hours 94 185 250 245 53
F
Pay 1560.40 1572.50 3087.50 4361.00 355.10
G
New Pay 1591.61 1603.95 3149.25 4448.22 362.20
Totals
$10936.50
$11155.23
G: new column for increased pay C8: stores the 2% value F9 and G9: store the total pay
11
Spreadsheets
Needed formulas:
D2*(1 + $C$8/100)*E2 (entered in cell G2) G3 ... G6: inserted automatically (by copying) after inserting G2 same formula is used $C$ in order to prevent indexing the C column for G2 ... G6 (constant value!) To compute the total in F9:
The nice thing is now that if the owner wants to examine an increased pay using another percent, say 3%, only cell C8 needs to be modified! the new column G and the total pays are adjusted automatically
12
SUM(F2:F6) (entered in cell F9) this means sum up all values in cells between F2 and F6 By copying to cell G9, the corresponding formula SUM(G2:G6) is automatically generated
Spreadsheets
The owner may also use another more realistic formula for increments:
Each employee is given a "merit" percentage over a fixed base rate
1 ID 2 101 3 102 4 103 5 104 6 105
7 8 9
A
B
Name Janet K Adam R Fred L John A Butch H Base Increase %
C
Age 51 18 43 53 17 2
D
Rate 16.60 8.50 12.35 17.80 6.70
E
Hours 94 185 250 245 53
F
Pay
G
Merit
H
New Pay 1638.42 1635.40 3241.87 4535.44 365.75
1560.40 3 1572.50 2 3087.50 3 4361.00 2 355.10 1
Totals
$10936.50
$11416.80
13
Spreadsheets
Formulas needed to be typed in (for "merits" example):
Moreover, some spreadsheet program can perform "goal seeking"
First, a column (we use G) is created to model the merits Now column H is for new pay D2*(1 + ($C$8 + G2)/100)*E2 (entered in H2) Formulas in H3...H6 are generated automatically after copying Suppose the owner only knows:
Owner types in these values AND spreadsheet software seeks the amount of base increase percentage automatically For example:
What merits each employee is worth The amount of money reserved for salaries (in the current year)
Suppose amount for this year is $12000.00 Spreadsheet software will assign to cell C3 the value 7.33 automatically Owner is now happy to know what is the base increment in this year (that does not exceed his/her expectation)
14
Spreadsheets
Imagine more complicated examples:
Company may vary the price of a product or the cost of supply and see immediately the effect on the profit A chemist can experiment with the amount of additives necessary to obtain a smooth flow of a liquid in a pipe An economist can track revenue impacts of a proposed tax increase ... spreadsheet programs have become modeling and forecasting tools! Spreadsheets can only perform "numeric" modeling Time dependence of data is not directly supported (but can be achieved)
However:
15
Spreadsheets
Programming levels of spreadsheets:
Macro programming ( highest level): Here a real programming language including (sequential, conditional, and interactive) instruction is provided in order to develop "programs" that simplify the work (of inputting formulas etc.) Visual programming ( intermediate level): Spreadsheet program acts like a (visual) language interpreter it waits for the user to change something, and then delivers new results "eventdriven programming" Can be compared to an (interpreted) functional language, since only formulas (functions!) are used Formulas can:
Formulas (programming) ( lowest level): Use of the basic arithmetic operations: e.g. A1*B1*C1 Use of builtin functions: e.g. SUM(B2:B10), ABS(A1), etc.
Explicitly use if (statement): e.g. IF(A3 > B3, A3B3, B3A3) Implicitly use loops: e.g. when determining a base (input) value given a target one (like when we use target total pay 12000.00 to determine the base percentage)
16
Databases
Since Herman Hollerith demonstrated the advantages of mechanizing the processing of large amounts of data (in the US census of 1890), data processing emerged and evolved to a very common task at almost each desktop computer in the world Large amounts of data are stored in permanent storages (disks, tapes, ...) Related data are organized in files in background storage:
Common file types:
A file has a name and further attributes, and It includes the (user) data themselves
File manager:
Text files: produced by e.g. a word processor Graphic files: produced by e.g. drawing program Program files: produced by e.g. a compiler (which is also stored in a program file) ... Often part of the operating system
17
Databases
Is a program that offers operations for:
A directory is a list of records consisting of:
Creating a new file in a directory Reading information in a directory Updating information in a directory Deleting a file from a directory
File manager has elementary capabilities:
File name File size Time of last update Access rights ...
More than that is needed ... ( data organization)
A file is for the file manager a black box File manager cannot even distinguish file types
But file manager is indispensable, since access to background storage is always through it(s operations).
18
Databases
Data organization
Let us confine us to (simple) user data files (no program files) Data are based on bits and bytes
Data can be better organized in:
but these are too small quantities in real life Fields: a collection of bytes (e.g. employee name) Records: a collection of fields (e.g. employee information name, phone#, ...) Data files: a collection of records (e.g. all employees in a company) Database: a collection of data files (e.g. employees, inventory, ...)
Structure of a database (consisting of 1 file) Field1 (e.g.ID) Field2 (e.g Name) Field3 (e.g. Age) Field4 (e.g. PayRate)
Record1 Record2 Record3 Record4 19
Database
Attention: A record is unlike an array, since it may include fields of different data types and those fields are not accessed via indexes! Database management system (DBMS)
A program that manages files in a databases Codd E. F. observed records in a file as one entity: 2dimensional table He introduced the relational database model: Now an employee file is not a collection of individual records but it is a 2 dimensional table He suggested new terminology (now widely used): Entity: is what the table represents e.g. employees file Tuple: represents one instance of this entity (the old record or a row in a table) Attribute: Heading (or name) of a column in a table (e.g. employee name, age, ...) Primary key: An attribute (or a collection of attributes) that uniquely identifies a tuple (e.g. SSN of an employee) Relation: Same as entity from the point of view of "related" attributes
20
Databases
A DBMS is more than a file manager:
A DBMS is really a complex program:
It works on the level of attributes and relations It knows how data are organized and how to access them the best (using primary keys) User data is a glass box for a DBMS (not a black box) It has its own data definition language (DDL) It has its own data manipulation language or query language (DML)
After defining the data using DLL, the query language can be used to perform complex operations on the data SQL: Structured Query Language Examples of queries in SQL:
Get all information about employee 123, the user poses the following query: SELECTID, Name, Age, Payrate, Hours, Pay FROM Employee WHERE ID = 123;
21
Databases
Get pays of a specific employee: SELECT Name, Pay FROM Employee WHERE Name = `John Kay'; Get all information about employees ordered by their IDs: SELECT * FROM Employee ORDERED BY ID; Get all information about employees older then 21 years: SELECT * FROM Employee WHERE Age > 21; A query using two tables: SELECT Employee.Name, Insurance.PanType FROM Employee, Insurance WHERE Employee.Name = `Fred James' AND Employee.ID = Insurance.ID;
22
Databases
Issues in databases:
Transactions:
Multimedia data:
Allornothing... Audio Video ...
WWW
Distributed Databases
Accessing databases using browsers (hypermedia) Data distributed among nodes Replication and fault tolerance Security
23
Numeric and Symbolic Computation
Historically, the first application of computers is numeric computation:
Today: numeric computation still a challenging task
Baggage Analytic Engine for mathematical equations Hollerith solved statistical problems (US census 1940's computers motivated by militarybased problems mathematical Problems with up to 1015 mathematical operations are not uncommon Typical areas:
Weather forecasting Molecular analysis Realtime imaging Simulation Natural language processing
24
Numeric and Symbolic Computation
Mentioned challenges yielded to the development of supercomputers and highly parallel computers Machines with 1010 (and more) floating point operation per second have been constructed Example: virtual reality ( realtime imaging)
Computer generates images in the same time frame and with the same orientation as when seen in real life Images are displayed on glasses and headsets are used to feel like in a real scene For example: as you are moving your arms, legs, and eyes, the computer may be generating and displaying simulated images of what you would see during a stroll through a forest. High demands on computation ability:
about 24 images / sec each image = e.g. one million of pixels (picture elements) for each image: hundreds or thousands of mathematical operations
25
Numeric and Symbolic Computation
Computer determines repeatedly:
In another rather esoteric area: quantum chromodynamics
Thus: 24 images/sec, 1000 pixels each, 1000s of operations a pixel more than 24 billion of mathematical operations per second 100 trillion (1014) of operations are needed for a single result! A regular computer (25 MIPS) would work 1.5 moths to generate result A supercomputer: 1 hour A teraflop machine: less than 2 minutes
26
How far you have moved (since last image) How your eyes/head is positioned What is visible (what colors etc.) and what not from the current perspective
Numeric and Symbolic Computation
Even after the emergence of nonnumeric applications (like word processing, databases, ...), numeric computation are still very demanding, and in particular the field of symbolic computing Symbolic Computing
Traditional numeric problems are based on "numeric values": e.g. 13.57/1.8897 *sin(1.2*) cos(1.34*)*104 Symbolic computing works on quantities that represent numbers (like unknown variables of high school mathematics) Examples:
Spreadsheets formula: D2*E2 Simplify: x2 + 3x 4 + 3x2 x + 1 Solve: x3 + 2x2 + 10x 13 = 0 Factor: x3 + x2 3x 3 Plot: sin(3x) for 0 <= x <= 2
27
Numeric and Symbolic Computation
There is a variety of software tools for symbolic computation (e.g. Mathematica, Maple) Of course these tools are able to do numeric computations as well In general the tools are interactive:
Example: N[expr, i]
User: enters some request (here boldface) Program: displays result (here italic)
Entered when a numeric computation is wanted Arithmetic expression expr is evaluated with the precision I N[((13.1842/1.976) Sin[2.1 Pi])^(1.0/3.0) + 0.0406893, 6] 1.31346
28
Numeric and Symbolic Computation
Most symbolic systems work with ASCII representation of numbers and not with their binary representation:
"10" = 1010 (4 bits) "10" = `1''0' (2 bytes) they can achieve high precision (but need more memory) Examples: Compute with 250 precision:
Compute the factorial of 200:
N[Pi, 250] 3.1415926535...52271201909
200! 78865786479050...737472000...00000
29
Numeric and Symbolic Computation
However the strength of these systems is in symbolic computing Examples
Simplify expression: Simplify[expr]
12 12 x + 5 x2 Factor polynomial: Factor[polynomial]
Simplify[(x-1)^2 + (x+2) + (2x-3)^2 + x]
(-1 + x) (1 + x) (1 x + x2 - x3 + x4) (1 + x + x2 + x3 + x4) Expand expression: Expand[expr]
Factor[x^10 -1]
1 + 4x + 6x2 + 4x3 + x4 + 12y + 36xy + 36x2y + 12x3y + 54y2 + 108xy2 + 54x2y2 + 108y3 + 108xy3 + 81y4
30
Expand[(1 + x + 3y)^4]
Numeric and Symbolic Computation
Solve equations: Solve[equation, unknown]
Solve transcendental equations like ex 1.5 == 0
Solve[x^2 5x + 4 == 0, x] (Note: "==" means equal) {{x 4} { x 1}} Solve[Exp[x] 1.5 == 0, x] {{x 0.405465}
Solve system of linear equations:
Solve system of linear equations:
Solve[{2x + y == 11, 6x 2y == 8}, {x, y}] {{x 3, y 5}} Solve[{2x + y == 11, 2x + y == 8}, {x, y}] {}
31
Numeric and Symbolic Computation
Calculus operations:
Differentiation:
D[x^3+6x-7, x]
6+ 3x2 Integration:
Integrate[x^4 - 2, x]
1/5 x5 - 2x Summation of (convergent) infinite series:
N[Sum[1/2^i, {i, 1, Infinity}]]
1.0 Summation of (divergent) infinite series:
N[Sum[1/k, {k, 2, Infinity}]]
Sum diverges
32
Numeric and Symbolic Computation
Plotting functions:
Plot[x^2 + x 2, {x, -3, +2}]
Plot[5 Sin[3x], {x, 0, 2 Pi}]
33
Numeric and Symbolic Computation
Various options for plots are available:
Process of performing user requests:
Discrete: only some points 3dimensional: e.g. Plot3D[Sin[x*y]. {x, 0, 3}, {y, 0, 3}] ... Get and analyze request Activate the appropriate program to handle the request Receive results Display results Algorithms for symbolic computation Exploiting parallelism Distribution in a network
34
Issues:
Computer Networks
A computer network consists of:
Types of networks:
Computers Peripheral devices (printers, disks, ...) An interconnection network local area network: LAN e.g. within buildings Wide area network: WAN e.g. across countries Share physical resources: e.g. one printer in a department Share logical resources: e.g. access to files, databases, ... Fault tolerance: e.g. if one printer fails, another can be used Parallelism: e.g. print two documents on two different printers Communication: e.g. email
35
Benefits of networks:
Computer Networks
Further benefits:
Use of supercomputers in a WAN Groupware: Joint editing of documents Electronic data interchange: Data transfer from a program to a program; e.g. orders as output from a program at company X are transmitted to another program (that handles bills and shipping) at company Y ( no human intervention) Use of networkcentric applications:
WWW Ecommerce Search engines ...
36
Computer Networks
Internet
Advantages (of Internet)
One of the largest computer networks Outgrowth of ARPANET (US DoD) ARPANET was developed in 1970s Internet is a network of networks Email Voice mail Cellular phones Teleconferencing ...
Issues:
Reliability of networks Efficiency Privacy and confidentiality
37
Computer Networks
More about the Internet
Vision: "information superhighway"
global information access from everywhere by everyone at every time Information should be a basic infrastructure good Information should flow like current/voltage flows from plugs
Information is accessible through services Internet is a big WAN (actually a WAN of WANs/LANs) Internet is big collection of nodes connected by wire, each node is either a individual computer (e.g. mainframe) or a switching station
38
Computer Networks
User connects to the internet using:
Connection:
Workstation PC Laptop ...
Internet services
Direct: user connects by telephone line to a "host" (already connected to Internet) Over LAN: user machine is in a LAN that is connected to an internet host
Email:
In order to communicate with someone via email you must know his/her email address
39
Computer Networks
Addressing scheme is hierarchical: [e-mail]
Problems with Email:
"jones" identifies an individual account on a host computer "ournode&q...