1 / 15

“Spreadsheet as a Relational Database Engine”

“Spreadsheet as a Relational Database Engine”. - Harsh Dhabhai. 1. To manage home budget. Uses of Spreadsheet. 2. Manage data and model in business and research. 3 . Time tables and schedules. RELATIONAL DATABASE ENGINE. Application Scenarios. Microsoft Excel 2003.

cedric
Télécharger la présentation

“Spreadsheet as a Relational Database Engine”

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. “Spreadsheet as a Relational Database Engine” - Harsh Dhabhai

  2. 1. To manage home budget. Uses of Spreadsheet 2. Manage data and model in business and research. 3.Time tables and schedules. RELATIONAL DATABASE ENGINE

  3. Application Scenarios Microsoft Excel 2003 OpenOffice Calc Gnumeric Google Docs

  4. R1C1 notation: Notations and Functions Used RmCn, R[i]Cm, RmC[j], R[i]C[j], RCm, RC[i], RmC, R[i]C Example – R[1]C7 and RC[3] 1. IF function 2. SUMPRODUCT function 3. MATCH and INDEX function

  5. Syntax : IF(condition,true_branch,false_branch) IF function Advantages : 1. Protect functions 2. Trap Errors 3. Speed up execution of queries

  6. Syntax : SUMPRODUCT(R1C1:R5C1=R1C3)*(R1C2:R5C2=R1C4) SUMPRODUCT function How to calculate this? 1. Range is compared with R1C3 – sequence of 5 Boolean values. 2. Range is compared with R1C4 – sequence of 5 Boolean values. 3. Two sequence are multiplied – conversion from Boolean to integer, normal multiplication. 4. 5 numbers are summed to give a single number. Number = count of rows having same pair (C1 and C2), (R1C3:R1C4)

  7. Syntax : SUMPRODUCT(R1C1:R5C1=R1C3)*(R1C2:R5C2=R1C4)*(R1C5:R5C5) SUMPRODUCT function How to calculate this? 1. First 3 steps are the same as before. 2. Previous Boolean sequence * R1C5:R5C5 coordinate wise – sequence of 5 numbers. 3. Two sequence are multiplied – conversion from Boolean to integer, normal multiplication. 4. 5 numbers are summed to give a single number. Number = sum of values in C5, calculated over those rows, in which C1 and C2 contain same pair of numbers as in R1C3:R1C4

  8. Syntax : MATCH(range,cell,0) or MATCH(range,cell,1) MATCH and Index functions 1. Returns relative position of the first value in range equal to the value in the cell. 2. Returns relative position of the largest value in the range which is less than or equal to the value in the cell. Syntax : INDEX(range,cell) Returns the value from range whose relative position is given by the value from cell.

  9. - External Program (Query Compiler) • - Database – One workbook, Data Table – One worksheet • Implementation of relational algebra and SQL • Query Worksheets (not to be edited) • Result column and intermediate column Architecture of a Database Implemented in a Spreadsheet

  10. Empty String formulas: =“” Relational Algebra Relational algebra query Q of arity m is a group of l+m columns Semantics : Set and Bag Representation : Loose and Standard Semantics : Set and Bag

  11. Sorting Relational Algebra Operators Duplicate Removal Selection Error Trapping and Standardization Projection Union Difference Cartesian Product Grouping with aggregation

  12. CLOUMNS < =FORMULA CLOUMNS << =FORMULA Notation, Error Trapping and Projection Error Trapping: Suppose the formula is =F Replace the formula with =IF(ISERROR(F),””,F Projection: Omit a few columns from the input relation/query

  13. NULL values – represented by the string NULL DDL – (CREATE TABLE statement) Practical Level:SQL Create separate data table and input table (query table) Input table – filtering, data table –fetches and does -standardization Addition and Deletion of elements Function TYPE and LEN – enforce data type declarations Duplicate elimination operator enforces UNIQUE and PRIMARY KEY Transactions – no concurrency control, COMIT and ROLLBACK

  14. Example

  15. - The only limitation is that it is not scalable. Conclusion - Relational Algebra can be naturally expressed in a spreadsheet. - Useful as an end-user desktop database. - Spreadsheets can be used as Relational Database Engine by utilizing spreadsheet formulas. THANK YOU

More Related