Information literacy B Lecture 4 Database and Spreadsheet

1. Information literacy BLecture 4Database and Spreadsheet Takeshi TokuyamaTohoku University Graduate School of Information SciencesSystem Information SciencesDesign and Analysis of Information Systems

2. Database • Database • Organized collection of data for one or more purposes • Mechanism to store large size of data to utilize(search and extract) easily • DNA database, Digital Library, Internet search database , Medical record, Music database, Address book • Operation • Data collection • Database construction • Data management • Storing data, search, transaction • Data analysis and understanding • Major database structures • Relational Database, Functional Database, XML Database

3. Database model Relational database model Linktables Hierarchical database model Network model

4. Relational Database • One of database model or database constructed on the model • Based on the theory of relational data model proposed by Edgar F. Codd in IBM(1970) • Single data contains one or more values and a set of data form a table • Easy to combine or extract data using key such as ID or name • Generally, SQL(Structured Query Language) is used in operation of database SELECT Math≧ 80 AND English≧ 90FROM Exam;

5. Example of Relational Database: Sales Database

6. Development of Database Technology Data Collection, Database Construction 1960s Database Management System 1970s-Early 1980s Developed Database System Data Warehousing Data Mining Web Database System Mid 1980s-Today Late 1980s-Today 1990s-Today New Generation Connected Information System 2000s-Today

7. What is EXCEL • Application software of Microsoft • Spreadsheet (idea was in 1960th) • VisCalc on Apple II (1979) changed the use of PC • Specialized for data analysis • Calculation • Simple calculation • Math Functions • Data to graph • Data collection to Database(small DB) • Numeric data, character data • Macro programming by VBA • Software programming • Accounting software, game and etc

8. Startup Excel Start ↓ All Programs ↓ Microsoft Office ↓ Microsoft Office Excel 2010

9. Data Entry • Select a cell and enter • Active cell • Cell number • A1, C2 • Column • A, B, C, D, … • Row • 1, 2, 3, 4, … • Sheet • Sheet index • Sheet1 Selected cell

10. Data Entry • Data in active cell is shown in math bar Math bar

11. Simple Calculation • Calc entered data(cell number) • A1 50 • A2 75 • A3 5 • A4 8 • A6 =A1+A2 • A7 =A2-A3 • A8 =A3*A4 • A9 =A1/A3 • A10 =(A1+A3)*A4 – (A2+A4)/A3

12. Simple Calculation • Enter numbers directly into cell • C1 =39+34 • C2 =87-34 • C3 =63*45 • C4 =56/7 • C5 =(45+34)/3 + (45/3)*12

13. Calculation by Math Function • Sum • E1 =A1+A2+A3+A4 • E2 =sum(A1:A4) • Average • E3 =(A1+A2+A3+A4)/4 • E4 =E1/4 • E5 =average(A1:A4) • Max, Min • E6 =max(A1:A4) • E7 =min(A1:A4)

14. Score Calculation • Download score data • Score data • Items • ID, Japanese, English, Math, Sum, Average • 20 student(1, 2 ,…, 20)

15. Simple calculation! Sum and Average Sheet: simplecalc

16. Sum • Enter a formula below =B2+C2+D2 Sheet: simplecalc

17. Average =E2/3 =(B2+C2+D2)/3 or Sheet: simplecalc

18. Format Cells Set decimal places • Select a cell to format • “Format Cells” • Number • Select “Number” from “Category” and set Decimal places Sheet: simplecalc

19. Copying Cells • Copy • Formula and formatare copied also. Sheet: simplecalc

20. Calculate using Math Functions! Sum, Average, Max and Min Sheet: mathfunction

21. Sum =sum(Cell range) =sum(B2:D2) Cell range：B2 to D2 Sheet: mathfunction

22. Average =average(Cell range) =average(B2:D2) Cell range：B2 to D2 Sheet: mathfunction

23. Math Function

24. Math Function “Search for a function”