A Protocol to Convert Spreadsheets into Web Accessible Databases

1
A Protocol to Convert Spreadsheets into Web
Accessible Databases

• Paul Juell
• Computer Science Department
• North Dakota State University
• Fargo, ND

2
About this Presentation

• Designed a protocol
• To convert spreadsheet form of data storage into
  web accessible databases
• Motivation
• Through a Food Safety Project
• Data stored in spreadsheets
• Single user file
• Web access desired
• Two or more users want to share information

3
About this Presentation

• Solution to the Objective
• Design a protocol
• To construct ER diagrams from the already
  existing spreadsheets
• Using the protocol
• Developed an online database system
• Evaluated
• Effective with another set of spreadsheet data
• Helpful
• In the biological field
• Spreadsheet conversion

4
Food Safety Project and the Need for an Automated
System

• Food Safety Project
• Salmonella Campylobacter in turkey
• Different samples tested against different
  antimicrobials
• Multiple spreadsheets for data storage
• Manual retrieval
• Time consuming
• Leads to errors

5
Advantages of Databases over Spreadsheets

• Both store and manipulate data
• Databases
• Better security
• Capacity to increase data integrity
• Ease of reporting and sharing data
• Spreadsheets
• Stand-alone applications
• Limits the number of concurrent users
• Inability to structure and label data
• Inability to query
• Large amount of hard-drive space

6
Web Access of Data

• Client/server architecture
• Requires relational databases
• That serve as database servers (SQL)
• Not all relational databases are built on the
  client/server architecture
• E.g. MS access
• Spreadsheets-not a relational database
• ER diagrams blue print for RDBMS
• Designed a protocol
• Construct ER diagrams
• Existing spreadsheets

7
Protocol Used

• Analyze the spreadsheet data file
• Identify entities the corresponding attributes
• Usually the first column
• Unique values
• An entity
• Break down composite fields
• Composite field entity
• Constituent parts - attributes

8
Protocol Used
9
Protocol Used

• Link the entities with the relationship among
  them

10
Protocol Used

• Discuss with the investigator
• Footnotes, abbreviations, etc.
• Identify key attributes for each table, and
  create links between tables
• Complete the ER diagram with the tables and links
  developed

11
Complete ER diagram
12
Requirements

• The administrator
• The principal investigator of the research
  project
• Administrator
• Authorize, delete instructors
• Instructors include research staff
• Instructors
• Add, delete, modify, and search data
• Instructors
• Authorize, and delete students
• Students
• search the database

13
Architecture for Web Access

• Three tier
• Client tier
• Web browser software
• Middle tier
• php
• Database tier
• MySQL
• ER diagrams translated into tables
• Web access forms built
• Online database application developed

14
Evaluation

• Protocol
• Different steps
• Applied to another spreadsheet data
• Different samples subjected to different tests
  for quality assessment
• Constructed ER diagram

15
Evaluation
16
Results

• Designed a protocol
• ER diagrams from existing spreadsheets
• Developed web accessible database
• Can be accessed at http//webdb2.cs.ndsu.nodak.edu
  /vijayaku/juell/entrance/enter.htm
• Used by the Vet. Micro Dept.
• Helpful in the biological field
• Applying different tests for different samples