Lecture 22 Databases Numeric Symbolic Computing SG 11

Lecture 22 Databases Numeric & Symbolic Computing (S&G, §§ 11. 3– 11. 4) 11/10/2020 CS 100 - Lecture 22 1

Read S&G ch. 12 (Computer Networks) for next week 11/10/2020 CS 100 - Lecture 22 2

Data Organization • A database is a collection of related files – analogy: all the file cabinets in a business • A file is a collection of related records – analogy: all the folders in one drawer (holding, say, the personnel records) • A record is composed of fields – analogy: the folder for a particular employee, containing, for example their name, employment history, pay rate, insurance information, evaluations 11/10/2020 CS 100 - Lecture 22 3

Example File “Employee” ID Name Age Pay. Rate Hours 86 Janet Kay 51 16. 50 94 1560. 40 123 Francine Perreira 18 8. 50 185 1572. 50 149 Fred Takasano 43 12. 35 250 3087. 50 71 John Kay 53 17. 80 245 4361. 00 165 Butch Honou 17 6. 70 53 355. 10 Field Record 11/10/2020 Pay CS 100 - Lecture 22 4

How is this different from a spreadsheet? • Databases are typically oriented toward very large amounts of data – think of IRS databases, Wal-Mart employee & inventory databases • Therefore efficiency is critical: – efficiency of data storage – efficiency of retrieval • The data in a database is usually static – updated manually, not automatically 11/10/2020 CS 100 - Lecture 22 5

Relational Database Model • A file is viewed as a table • Each table contains information about a number of instances of some entity – an entity is a fundamental distinguishable object, such as “employee” • Each instance of the entity is represented by a tuple – e. g. , the data for a particular employee • Each tuple has a number of attributes – which characterize the instance (e. g. , a particular employee’s attributes) • Primary key: attribute(s) that uniquely identify a tuple 11/10/2020 CS 100 - Lecture 22 6

A Table for the “Employee” Entity ID Name Age 86 Janet Kay 51 16. 50 94 1560. 40 123 Francine Perreira 18 8. 50 185 1572. 50 149 Fred Takasano 43 12. 35 250 3087. 50 71 John Kay 53 17. 80 245 4361. 00 165 Butch Honou 17 6. 70 53 355. 10 11/10/2020 Primary Key Pay. Rate Hours Tuple CS 100 - Lecture 22 Pay Attribute 7

Query Languages • A query language allows users to: – – retrieve information from a database relate information in different files in a database update information in a database perform statistical and other data processing operations on selected information • SQL (Structured Query Language) – a standard query language – a textual language – sometimes used behind a graphical “front end” 11/10/2020 CS 100 - Lecture 22 8

Example Query >SELECT ID, NAME, AGE, PAYRATE, HOURS, PAY >FROM EMPLOYEE >WHERE ID = 123; 123 Francine Perreira $8. 50 185 $1572. 50 18 > 11/10/2020 CS 100 - Lecture 22 9

Example Query (2) >SELECT ID, NAME, AGE, PAYRATE, HOURS, PAY >FROM EMPLOYEE >WHERE NAME = ’John Kay’; 71 John Kay $4361. 00 53 $17. 80 245 > 11/10/2020 CS 100 - Lecture 22 10

Example Query (3) >SELECT NAME, PAY >FROM EMPLOYEE >WHERE NAME = ’John Kay’; John Kay $4361. 00 > 11/10/2020 CS 100 - Lecture 22 11

Example Query (4) >SELECT * >FROM EMPLOYEE >ORDER BY PAYRATE; ID 165 123 149 86 71 Name Butch Honou Francine Perreira Fred Takasano Janet Kay John Kay 11/10/2020 Age 17 18 43 51 53 Pay. Rate $6. 70 $8. 50 $12. 35 $16. 50 $17. 80 CS 100 - Lecture 22 Hours 53 185 250 94 245 Pay $355. 10 $1572. 50 $3087. 50 $1560. 40 $4361. 00 12

Example Query (5) >SELECT * >FROM EMPLOYEE >WHERE AGE > 21; ID 86 149 71 Name Janet Kay Fred Takasano John Kay 11/10/2020 Age 51 43 53 Pay. Rate $16. 50 $12. 35 $17. 80 CS 100 - Lecture 22 Hours Pay 94 $1560. 40 250 $3087. 50 245 $4361. 00 13

Modifying Databases • DELETE * FROM EMPLOYEE WHERE AGE < 21; • UPDATE EMPLOYEE SET PAYRATE = 8. 75 WHERE ID = 123; • INSERT INTO EMPLOYEE VALUES (456, ’Sandy Beech’, 13. 25, 0, 0); 11/10/2020 CS 100 - Lecture 22 14

Another Table Primary Key Insured. ID Plan. Type Date. Issued 86 A 4 02/23/78 123 B 2 12/03/91 149 A 1 06/11/85 71 A 4 10/01/72 149 B 2 04/23/90 11/10/2020 CS 100 - Lecture 22 15

Foreign Key • The “Insured. ID” attribute is a foreign key because it is a primary key into a different table (EMPLOYEE) • Foreign keys establish relationships between tables • E. g. , between the employee (with all his/her attributes) and the insurance plan (with all its attributes) 11/10/2020 CS 100 - Lecture 22 16

Example Query of Joined Tables >SELECT EMPLOYEE. NAME, INSURANCE. PLANTYPE >FROM EMPLOYEE, INSURANCE >WHERE EMPLOYEE. NAME = ’Fred Takasano’ AND EMPLOYEE. ID = INSURANCE. INSUREDID; NAME Fred Takasano PLANTYPE A 1 B 2 > 11/10/2020 CS 100 - Lecture 22 17

Computer Science Issues • SQL is a very high-level language – nonprocedural – problem-specific • Performance in a major issue • Consistency issues with simultaneous updates • Distributed databases (files stored in many locations) – access time & consistency problems 11/10/2020 CS 100 - Lecture 22 18

Numeric and Symbolic Computing 11/10/2020 CS 100 - Lecture 22 19

Numeric Computation • Applications that make heavy use of real arithmetic • Especially used in science, engineering, economics, statistics, animation • The motivation for the first computers • Still drives the development of supercomputers and parallel computers § a teraflop machine performs at least 1012 (a trillion) floating-point operations per second § 36 Tflops already achieved (Japan’s Earth Simulator, which cost $350– 500 M) 11/10/2020 CS 100 - Lecture 22 20

Computer Science Issues • Performance: – better algorithms – accessing of data in memory hierarchies – parallel computation – data communication in networks • Mathematical software libraries • Accuracy and stability of numerical approximations 11/10/2020 CS 100 - Lecture 22 21

Symbolic Computing • Manipulate mathematical formulas, equations, etc. much the way a mathematician would – automate processes that are mechanical, tedious, and error-prone • Examples: Macsyma, Mathematica, Maple, Mat. Lab 11/10/2020 CS 100 - Lecture 22 22

Example: Simplification • Simplify[(x-1)^2 + (x+2) + (2 x-3)^2 + x] • 12 - 12 x + 5 x 2 11/10/2020 CS 100 - Lecture 22 23

Example: Expansion • Expand[(1 + x + 3 y)^4] • 1 + 4 x + 6 x 2 + 4 x 3 + x 4 + 12 y + 36 x 2 y + 12 x 3 y + 54 y 2 + 108 xy 2 + 54 x 2 y 2 + 108 y 3 +108 xy 3 + 81 y 4 11/10/2020 CS 100 - Lecture 22 24

Example: Solving Equations • Solve[ {2 x + y == 11, 6 x - 2 y == 8}, {x, y}] • {{x -> 3, y -> 5}} 11/10/2020 CS 100 - Lecture 22 25

Typical Expansion Rules Hence, Expand[(n + 1)2] ÞExpand[(n + 1)] ÞExpand[(n + 1)n + (n + 1)1] ÞExpand[n n + 1 n + n 1 + 1 1] 11/10/2020 CS 100 - Lecture 22 26

Digression • Recall our discussion of formalized mathematics, and the idea of reducing mathematics to the mechanical application of formal rules • Formal rules: depend on the form of expressions, not their meaning • Symbolic computation is an application of the idea of a calculus 11/10/2020 CS 100 - Lecture 22 27

Computer Science Issues • Symbolic computation systems are: – very high-level languages – problem-specific – nonprocedural • Depend on many algorithms, e. g. : – pattern matching – efficient management of complex data structures representing formulas • Results should be presented in a form familiar and useful to the mathematically literate 11/10/2020 CS 100 - Lecture 22 28