Kexi/Håndbog/Introduktion til databaser/Database og regneark
Database og regneark
Du har sandsynligvis allerede brugt et regneark som Calligra Sheets, LibreOffice Calc eller Microsoft Excel. I så fald tænker du nok: da både regneark og databaser har tabeller, hvorfor skulle jeg så bruge en database?
Under sammenligningen af regneark og databaser vil du måske støde på følgende emner, som du senere vis se i større detalje.
Hvordan er en database forskellig fra et regneark
Udvid din tabel Kontakter ved at tilføje en søjle (et felt) kaldet Adresse. Tilføj flere telefonnumre (kontor og hjem) for hver person og tilføj efternavne til navnet. For at forenkle tingene antager vi følgende:
- Tabellen er begrænset til to personer (der kunne selvfølgelig være hundrede eller tusinder af dem i en virkelig database)
- Der er ikke mere end en person med et givet efternavn
| Navn og efternavn | Tlf | Adresse |
|---|---|---|
| Joan Smith | 699 23 43 12 | Western Gate 1, Warsaw |
| Adam Willson | 711 19 77 21 | London, Frogs Drive 5 |
| Joan Smith | 110 98 98 00 | Western Gate 1 |
| Smith Joan | 312 43 42 22 | Warsaw, Western Gate 1 |
| ADAM Willson | 231 83 02 04 | Frogs Drive 5, London |
Sådan en tabel kan både laves i et regneark og i en database. Det er selvfølgelig meget let at bruge et regneark. Hvilke problemer støder vi på i dette trin?
Referentiel dataintegritet
Antag, at du bruger et regneark og skal ændre en persons adresse. Du har så et lille problem: du vil ofte skulle ændre adressen flere steder. For eksempel findes Joan i tre rækker. Hvis du glemmer at ændre en af disse rækker, så opstår der et reelt problem — adressen knyttet til denne person vil være uvis; du har mistet dataintegritet.
Desuden er der ingen enkel måde at slette eller vælge en person fra tabellen, da du skal huske at slette alle de rækker, hvor vedkommende findes.
Data redundans
Dette er indirekte forbundet med det foregående problem. I felterne Navn og efternavn og Adresse er de samme data opgivet mange gange. Det er typisk for et regnearks ineffektive måde at gemme data; databasen vokser unødvendigt og kræver således flere computerressourcer (data fylder mere og er langsommere at tilgå).
Hvordan kan du løse disse problemer med en database? Du kan dele informationen i mindre bidder ved at lave endnu en tabel, Personer med kun to søjler: Navn og efternavn og Adresse:
| Navn og efternavn | Adresse |
|---|---|
| Joan Smith | Western Gate 1, Warsaw |
| Adam Willson | Frogs Drive 5, London |
Hver række i tabellen Personer svarer til én person. Tabellen Kontakter er herefter en relation til tabellen Personer.
Data integritet and gyldighed
Bemærk, hvordan data er indskrevet i felterne Navn og efterbavn og Adresse. De, der indskriver data er fejlbarlige, nogle gange endda skødesløse. I vore eksempeldata har vi både forskellige rækkefølger ved angivelse af navn og efternavn (Joan Smith og Smith Joan; Adam og ADAM) og mange flere måder at angive den samme adresse. Du kan utvivlsomt komme på andre måder.
Ovennævnte problem viser, at når du f.eks. søger efter telefonnummeret for en person, hvis adresse er "Western Gate 1, Warsaw", så finder du ikke det fulde resultat. Du vil kun finde en række, ikke tre. På tilsvarende måde vil du ikke telefonnumre hvis du søger på værdien "Joan Smith" i feltet Navn og efternavn, fordi "Smith Joan" ikke matcher med "Joan Smith".
How can you solve these problems using a database? You can do this by changing the design of the table Persons by:
- Dividing data in the field Name and surname into two separate fields: Name and Surname.
- Dividing data in the field Address into three separate fields: Street, House number and City.
- Guaranteeing data correctness: by ensuring that no fields are empty, e.g. you must always enter house number.
A modified table looks something like this:
| Name | Surname | Street | House number | City |
|---|---|---|---|---|
| Joan | Smith | Western Gate | 1 | Warsaw |
| Adam | Willson | Frogs Drive | 5 | London |
| Conditions | ||||
| required field | required field | required field | required field | required field |
Thanks to introducing the condition required field we can be sure that the entered data is complete. In case of other tables you may of course allow omitting certain fields while entering data.
Limiting data view
A spreadsheet displays all rows and columns of the table which is bothersome in case of very large data sheets. You may of course filter and sort rows in spreadsheets, however you must be extra careful while doing so. Spreadsheet users are in risk of forgetting that their data view has been filtered what can lead to mistakes. For example, while calculating sums you may think you have 100 rows of data while in fact there are 20 rows more hidden.
If you want to work on a small subset of data, e.g. to send it for others to edit, you can copy and paste it to another spreadsheet and after editing paste the changed data back to the main spreadsheet. Such "manual" editing may cause data loss or incorrect calculations.
To limit the data view database applications offer queries, forms and reports.
A very practical way of limiting is the following extended version of the previously described table Persons:
| Name | Surname | Street | House number | City | Income |
|---|---|---|---|---|---|
| Joan | Smith | Western Gate | 1 | Warsaw | 2300 |
| Adam | Willson | Frogs Drive | 5 | London | 1900 |
Let's assume that the newly introduced column Income contains confidential data. How can you share e.g. contact details of the persons with your coworkers but without revealing their income? It is possible if you share only a query and not the whole table. The query could select all columns except for the column Income. In database world such a query is often known as a view.
Performance and capacity
Your computer is probably quite fast, however you will easily see that it doesn't help with slow, large spreadsheets. Their low efficiency is first of all due to lack of indexes accelerating the process of data search (databases do offer them). Moreover if you use things like system clipboard, even copying data may become troublesome with time.
Spreadsheets containing large data sets may take ages to open. A spreadsheet loads lots of data to the computer's memory while opening. Most of the data loaded are probably useless/unnecessary for you at the moment. Databases unlike spreadsheets load data from computer storage only when needed.
In most cases you will not have to worry how the database stores its data. This means that unlike spreadsheets, databases do not care about:
- The sequence of rows since you can order the rows according to your needs. Moreover, you can view the same data in many views with different orders.
- The same goes for columns (fields) of the table.
Together with Limiting data view described in the previous paragraph these qualities constitute the advantage of databases.
Data entry
The latest editions of applications for creating spreadsheets enable you to design data-entry forms. Such forms are most useful if your data cannot be conveniently displayed in tabular view, e.g. if the text occupies too many rows or if all the columns do not fit on the screen.
In this case the very way the spreadsheet works is problematic. Fields for data entry are placed loosely within the spreadsheet and very often are not secure against the user's (intentional or accidental) intervention.
Reports
Databases enable grouping, limiting and summing up data in a form of a report. Spreadsheets are usually printed in a form of small tables without fully automatic control over page divisions and the layout of fields.
Programming
Applications for creating databases often contain full programming languages. Newer spreadsheets have this capability too, however calculations come down to modifying the spreadsheet's fields and simple data copying, regardless of the relevance and integrity rules mentioned in previous paragraphs.
Data processing within a spreadsheet is usually done via a graphical user's interface which may slow down the data processing speed. Databases are capable of working in background, outside of graphical interfaces.
Multiuse
It is hard to imagine a multiuse of one spreadsheet. Even if it is technically possible in the case of the latest applications, it requires a lot of discipline, attention and knowledge from the users, and these cannot be guaranteed.
A classical way to sharing data saved in a spreadsheet with other person is to send a file as a whole (usually using e-mail) or providing a spreadsheet file in a computer network. This way of work is ineffective for larger groups of people - data that could be needed in a particular time may be currently locked by another person.
On the other hand, databases have been designed mainly with multiuser access in mind. Even for the simplest version locking at a particular table row's level is possible, which enables easy sharing of table data.
Security
Securing a spreadsheet or its particular sections with a password is only symbolic activity. After providing a spreadsheet file in a computer network, every person being able to copy the file can try to break the password. It is sometimes not so hard as the password is stored in the same file as the spreadsheet.
Features for edit locking or copy locking of a spreadsheet (or its part) is equally easy to break.
Databases (except these saved in a file instead of a server) do not need to be available in a single file. You're accessing them using a computer network, usually by providing a user name and a password. You are gaining access only to these areas (tables, forms or even selected rows and columns) which were assigned to you by setting appropriate access rights.
Access rights can affect ability of data editing or only data reading. If any data is not available to you, it will not be even sent to your computer, so there is no possibility of making a copy of the data in such easy way as in case of spreadsheet files.