Information literacy B Lecture 4 Database and Spreadsheet
Information literacy B Lecture 4 Database and Spreadsheet Takeshi Tokuyama Tohoku University Graduate School of Information Sciences System Information Sciences Design and Analysis of Information Systems
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
Database model Relational database model Link tables Hierarchical database model Network model
Relational Database • One of database model or database constructed on the model • Based on 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 ≧ 90 FROM Exam;
Example of Relational Database: Sales Database Sales: 2010 Jan Sales: 2010 Dec Item 1 Item 2 Item 3 Item 4 Item 5 Item 6 Item 7 Item 8 Item 9 Item 10 Hokkaido 136 84 192 102 174 73 51 76 84 105 Aomori 127 122 63 70 35 224 75 246 230 Item 1 Item 2 Item 3 Item 4 Item 5 Item 6 Item 7 Item 8 Item 9 Item 10 Hokkaido 110 218 85 133 25 33 117 111 233 65 253 Aomori 145 89 31 141 104 217 88 99 36 263 29 79 70 155 113 144 211 128 236 Iwate 24 225 120 214 170 142 252 80 253 124 Iwate 126 Miyagi 147 62 172 58 218 75 208 224 161 111 Miyagi 108 92 263 100 249 134 52 72 208 163 Akita 99 221 148 188 140 69 184 78 172 204 Akita 152 113 33 41 153 48 147 130 79 201 Yamagata 143 157 184 105 166 129 78 206 164 189 Yamagata 150 93 115 166 120 46 260 77 113 54 Fukushima 148 99 105 49 253 63 102 113 163 185 Fukushima 206 256 109 60 230 61 157 238 117 82 Ibaraki 49 62 240 51 107 223 147 199 107 140 Ibaraki 220 263 140 250 225 30 246 171 150 25 Tochigi 64 159 191 24 120 215 210 249 123 54 Tochigi 153 118 57 42 186 197 182 111 85 225 Gunma 59 87 131 211 83 249 36 221 263 138 Gunma 237 87 137 129 199 151 128 115 163 214 Saitama 92 131 99 193 240 105 184 52 74 144 Saitama 176 58 82 86 268 158 191 234 70 216 Chiba 118 58 60 46 245 206 93 240 55 153 Chiba 116 58 175 237 103 72 34 165 37 101 Price Item 1 Item 2 Item 3 Item 4 Item 5 Item 6 Item 7 Item 8 Item 9 Item 10 Cost price 874 574 785 250 184 385 456 784 890 458 Selling price 980 870 900 500 354 450 980 800 980 650
Development of Database Technology Data Collection, Database Construction 1960 s Database Management System 1970 s-Early 1980 s Developed Database System Mid 1980 s-Today Data Warehousing Data Mining Web Database System Late 1980 s-Today 1990 s-Today New Generation Connected Information System 2000 s-Today
What is EXCEL • Application software of Microsoft • Spreadsheet (idea was in 1960 th) – Vis. Calc 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
Startup Excel Start ↓ All Programs ↓ Microsoft Office Excel 2010
Data Entry Selected cell • Select a cell and enter • Active cell • Cell number – A 1, C 2 ○ Column – A, B, C, D, … ○ Row – 1, 2, 3, 4, … • Sheet – Sheet index ○ Sheet 1
Data Entry Math bar • Data in active cell is shown in math bar
Simple Calculation • Calc entered data(cell number) – A 1 – A 2 – A 3 – A 4 – A 6 – A 7 – A 8 – A 9 – A 10 50 75 5 8 =A 1+A 2 =A 2 -A 3 =A 3*A 4 =A 1/A 3 =(A 1+A 3)*A 4 – (A 2+A 4)/A 3
Simple Calculation • Enter numbers directly into cell – C 1 – C 2 – C 3 – C 4 – C 5 =39+34 =87 -34 =63*45 =56/7 =(45+34)/3 + (45/3)*12
Calculation by Math Function • Sum – E 1 – E 2 =A 1+A 2+A 3+A 4 =sum(A 1: A 4) – E 3 – E 4 – E 5 =(A 1+A 2+A 3+A 4)/4 =E 1/4 =average(A 1: A 4) – E 6 – E 7 =max(A 1: A 4) =min(A 1: A 4) • Average • Max, Min
Score Calculation • Download score data • Score data – Items • ID, Japanese, English, Math, Sum, Average – 20 student(1, 2 , …, 20)
Simple calculation! Sum and Average Sheet: simplecalc
Sum • Enter a formula below =B 2+C 2+D 2 Sheet: simplecalc
Average =E 2/3 or =(B 2+C 2+D 2)/3 Sheet: simplecalc
Format Cells Select a cell to format “Format Cells” Set decimal places Number ○ Select “Number” from “Category” and set Decimal places Sheet: simplecalc
Copying Cells • Copy – Formula and format are copied also. Sheet: simplecalc
Calculate using Math Functions! Sum, Average, Max and Min Sheet: mathfunction
Sum =sum(Cell range) =sum(B 2: D 2) Cell range：B 2 to D 2 Sheet: mathfunction
Average =average(Cell range) =average(B 2: D 2) Cell range：B 2 to D 2 Sheet: mathfunction
Math Function
Math Function “Search for a function”
- Slides: 24