Note: You are currently viewing documentation for Moodle 3.9. Up-to-date documentation for the latest stable version of Moodle may be available here: Building Database.

Building Database: Difference between revisions

From MoodleDocs
(added links)
m (→‎Field types: Added information about how to input data for the Lat/Long field type)
 
(25 intermediate revisions by 6 users not shown)
Line 1: Line 1:
{{Improve}}
{{Database}}
{{Database}}
After creating a database, as explained in [[Database activity settings]]...
After creating a database, as explained in [[Database activity settings]]...
* Next you define the kind of [[Database fields|fields]] that define the information you wish to collect. For example a database of famous paintings may have a picture field called ''painting'', for uploading an image file showing the painting, and two text fields called ''artist'' and ''title'' for the name of the artist and the painting.
* Next you define the kind of fields that define the information you wish to collect. For example a database of famous paintings may have a picture field called ''painting'', for uploading an image file showing the painting, and two text fields called ''artist'' and ''title'' for the name of the artist and the painting. Alternatively, you can use a database preset (see below).
* It is then optional to edit the [[Database templates|database templates]] to alter the way in which the database displays entries.
* It is then optional to edit the [[Database templates|database templates]] to alter the way in which the database displays entries.
* Note that if you later edit the fields in the databases you must use the Reset template button, or manually edit the template, to ensure the new fields are added to the display
* The teacher and/or students can start entering data and (optionally) commenting on and grading other submissions. These entries can be viewed alone, viewed as a list or searched and sorted.
* Data can be uploaded into the database from a .csv file:
** Click the 'Add Entry' tab
** At the bottom of the page there is a link 'Upload entries from a file' which provides help in assigning data in a csv file to the relevant fields in your database.


You can add many kinds of fields to your database. After you have created some fields, you will be able to add information to the database.
==Database fields==
 
A '''field''' is a named unit of information. Each entry in a [[Database activity module|database activity module]] can have multiple fields of multiple types e.g. a text field called 'favourite color' which allows you to type in your favourite shade, or a menu called 'state' that lets you choose one from a list of the 50 that make up the United States of America. By combining several fields with appropriate names and types you should be able to capture all the relevant information about the items in your database.
 
Note that if you later edit the fields in the databases you must use the Reset template button, or manually edit the template, to ensure the new fields are added to the display.
 
=== Field name and description ===
 
The name is what is shown when an entry is added. It must be unique and short. The description is for your benefit to help identify that field.
 
===Required field===
If you need to ensure one or several particular fields are always completed, then tick the box 'Required field' when setting up the fields for your database. The field(s) will display a red asterisk when students contribute to  the database, and they will not be able to save their work until they have entered the relevant information. A new column will show the teacher which fields are required and not:
 
[[File:databaserequiredfields.png]]
 
=== Field types ===
 
The following screenshots show "before" and "afters" of setting up fields. The selection in orange shows the field as it is displayed for the user when they add an entry.
 
;Checkbox
:This allows you to offer checkboxes for the user to select. Add the options one under the other. The word will appear next to a checkbox when the user clicks to add an entry. They can check more than one box.
 
[[File:Checkboxfield1.png]]
 
;Date
:This allows a user to enter a date by picking a day, month and year from a drop down list.
 
[[File:Datefield1.png]]
 
;File
:Users can upload a file from their computer. If it is an image file then the picture field may be a better choice.
 
[[File:Filefield.png]]
 
;Latitude/longitude
:Users can enter a geographic location using latitude and longitude. For example, [http://moodle.com/hq/ Moodle HQ] is at latitude -31.9545, longitude 115.877. When viewing the record, links are automatically generated linking to geographic data services such as [http://earth.google.com Google Earth], [http://www.openstreetmap.org/ OpenStreetMap], [http://www.geabios.com/ GeaBios],[http://www.mapstars.com/extern/deltaworks-holland Mapstars] and more. (The teacher can choose which of those links appear, if any.)
 
:Data input requirements: Data for this field must be input relative to North and East coordinates. To convert coordinates from West (e.g. 120W) to East, simply input them into the field with a '-' in front (e.g., -120).  Also, data must also be input in decimal format, not degree format.  Simple converters are a web search away.
[[File:Latlongfield.png]]
 
;Menu
:The text entered in the ''options'' area will be presented as a drop-down list for the user to choose from. Each line become a different option.
 
[[File:Menufield.png]]
 
;Menu (Multi-select)
:The text entered in the ''options'' area will be presented as a list for the user to choose from and each line become a different option. By holding down control or shift as they click, users will be able to select multiple options. This is a fairly advanced computer skill so it may be wise to use multiple checkboxes instead.
 
[[File:Multiselectfield.png]]
 
;Number
:This allows users to enter any number. For example:
* 42
* -1000
* 0
* 0.123
* 3.0e8
:(For those who care about the technical details, the field stores floating point numbers.)
 
[[File:Numberfield.png]]


==Creating a single entry==
;Picture
:This allows a user to upload and display an image file. "Single view" is when the image is viewed on its own; "list view" is when it is viewed in with other images. Single view can be larger than list view.


Go to the "Add entry" tab and fill in the form.
[[File:Picturefield.png]]


==Importing many entries==
;Radio buttons
:The user gets radio buttons and can choose only one. They can only submit the entry when they have clicked on one button. (Note:If you only have two options and they are opposites (true/false, yes/no) then you could simply use a single checkbox instead. However checkboxes default to their unchecked status and so people could submit without actively selecting one of the options. This may not always be appropriate.)


At the bottom of the page "Add a database entry", you'll find a place to upload a csv file, though there are no help documents on what the CSV is supposed to look like. Please test and post your results.
[[File:Radiofield.png]]


[[image:Upload_entries.png]]
;Text input
:Users can enter text up to 60 characters in length. For longer text, or for text that requires formatting such as headers and bullet points, you can use a text area field.


Here's a sample of what a very simple file will look like.
[[File:Textfield.png]]


[[Image:Upload_csv_sample.png]]
;Text area
:This allows users to enter a long piece of text using the text editor.


Do not put spaces after your commas or upload will fail!
[[File:Textareafield.png]]


After upload page import.php comes back blank if it failed.  
;URL
If successful you'll read a message like, "1 entries saved".
:The user can add a link to a website here. If you select ''autolink'' then the URL becomes clickable.. If you also enter a ''forced name for the link'' then that text will be used for the hyperlink. For example in a database of authors you may wish people to enter the author's website. If you enter the text 'homepage' as a forced name then clicking on text "homepage" will take you to the entered URL.
You can choose to have the URL open in a new window if so desired.


==Activity import==
[[File:Urlfield.png]]
Database activity entries may be imported by uploading a text file. The easiest way to determine the format of the text file is to manually add an entry to the database and then export it. The resulting export text file may then be edited and used for importing entries.


CSV means Comma-Separated-Values and is a common format for text interchange.
==Presets==


The expected file format is a plain text file with a list of field names as the first record. The data then follows, one record per line.
To avoid the necessity of always having to create a new database from scratch, the database activity module has a presets feature. An image gallery preset is included in the database module or you can browse and download one from the [https://moodle.net/mod/data/view.php?id=22 Database presets database on Moodle.net]. You can create your own presets as well and share them with others.


The field delimiter defaults to a comma character and the field enclosure is not set by default (field enclosures are characters that surround each field in each record).


Records should be delimited by new lines (usually generated by pressing RETURN or ENTER in your text editor). Tabs can be specified using \t and newlines by \n.
===Importing a preset===


Sample file:
[[Image:databasepresetsnew.png|thumb|Database presets page]]
To use a preset:


  name,height,weight
# On the database page, click the Presets tab.
  Kai,180cm,80kg
# The usual way to import a preset is clicking the "Choose file" button and browsing to the ZIP file containing the preset (eg. "preset.zip") stored on the server. If there does not yet exist a preset ZIP file on the server, upload it from your local machine. Choose the desired preset file by clicking on its action "Choose". Finally, press the "Import" button.
  Kim,170cm,60kg
# Alternatively, you can choose a previously loaded preset.
  Koo,190cm,20kg
# After the import, you can start to add or import entries. All the hard work of setting up the fields and templates has been done for you. If you desire, you can still customize the fields and templates.


Warning: all field types may not be supported.
You will see a "Overwrite current settings" checkbox after the "Import" button has been pressed. If checked, the database activity overwrites some of its current general settings by those stored in the preset:
* the "Introduction" HTML text
* "Required entries"
* "Entries required before viewing"
* "Maximum entries"
* "Comments"
* "Require approval?"
* rssarticles


==Exporting Database==
===Exporting a preset===
=== Choose the format you wish to export to ===


The Database activity module provides an Export tab that allows users to export database entries to either the CSV (comma separated values) or ODS (OpenOffice Calc) formats. Previously, it was possible to export to the XLS (Microsoft Excel) format; however, problems with creating that format have led to it being removed. The workaround is to use either CSV or ODS formats both of which can be opened with the latest version of Microsoft Excel.
If you wish to share your database presets with others, you have two options:


When using the CSV format, the user can select either the comma, semicolon, or tab to separate the fields. The selection of the proper character is important. If users select to use a comma to separate the fields and some of the fields contain data with commas then the number of columns is going to be misaligned and likely cause confusion.  
#Export as a ZIP file, which can then be imported to another course or Moodle site.
#Save as a preset, which publishes the database for other teachers on your site to use. It will then appear in the preset list. (You can delete it from the list at any time.)


=== Choose the fields you wish to export ===
Note: Only the fields and templates of the database are copied when exporting or saving it as a preset, not the entries.


Users can select which fields from the database they wish to have exported. By default, all fields are checked to be included. Choose the fields that you wish to have included in the export.
===Presets for download===


===Export entries ===
The [https://school.moodledemo.net/course/view.php?id=57 Activity Examples course] on the School demo site has a number of database activities with presets for download.


Once the export type and the fields to be exported have been selected, clicking on the Export entries pushbutton will generate the file. The user will usually have the option of either opening or saving the file.
==See also==


Forum discussions:
*[http://moodle.org/mod/forum/discuss.php?d=84050 Can't get columns to line up in list view] including moodle.org presets for download
*[http://moodle.org/mod/forum/discuss.php?d=87005 New preset, please review, and make suggestions!] including FAQ preset for download
*[http://moodle.org/mod/forum/discuss.php?d=87560 Issue 53: Encyclopedia database preset] including encyclopedia-style preset for download
*[http://moodle.org/mod/forum/discuss.php?d=88727 Issue 51 - Design a Database Preset for Document Sharing] including preset for download
*[http://moodle.org/mod/forum/discuss.php?d=94854#p457252 Looking for some stellar examples of Moodle courses] including book reviews preset for download
*[http://moodle.org/mod/forum/discuss.php?d=89117#p511029 Restricting access to database content by user?] with research diary preset for download


==Exporting to an external portfolio==
[[de:Datenbank erstellen]]
If an external [[Portfolios|portfolio]] such as [[Mahara_portfolio|Mahara]] has been enabled by the administrator then users have the option to export individual entries to that portfolio. They will see at the bottom of a database entry an "export" icon to click on and select the portfolio to export to:
[[es:Construyendo BasedeDatos]]
[[File:Exportdatabasentry.png]]
[[fr:Construire une base de données]]

Latest revision as of 07:44, 12 January 2020

After creating a database, as explained in Database activity settings...

  • Next you define the kind of fields that define the information you wish to collect. For example a database of famous paintings may have a picture field called painting, for uploading an image file showing the painting, and two text fields called artist and title for the name of the artist and the painting. Alternatively, you can use a database preset (see below).
  • It is then optional to edit the database templates to alter the way in which the database displays entries.

Database fields

A field is a named unit of information. Each entry in a database activity module can have multiple fields of multiple types e.g. a text field called 'favourite color' which allows you to type in your favourite shade, or a menu called 'state' that lets you choose one from a list of the 50 that make up the United States of America. By combining several fields with appropriate names and types you should be able to capture all the relevant information about the items in your database.

Note that if you later edit the fields in the databases you must use the Reset template button, or manually edit the template, to ensure the new fields are added to the display.

Field name and description

The name is what is shown when an entry is added. It must be unique and short. The description is for your benefit to help identify that field.

Required field

If you need to ensure one or several particular fields are always completed, then tick the box 'Required field' when setting up the fields for your database. The field(s) will display a red asterisk when students contribute to the database, and they will not be able to save their work until they have entered the relevant information. A new column will show the teacher which fields are required and not:

databaserequiredfields.png

Field types

The following screenshots show "before" and "afters" of setting up fields. The selection in orange shows the field as it is displayed for the user when they add an entry.

Checkbox
This allows you to offer checkboxes for the user to select. Add the options one under the other. The word will appear next to a checkbox when the user clicks to add an entry. They can check more than one box.

Checkboxfield1.png

Date
This allows a user to enter a date by picking a day, month and year from a drop down list.

Datefield1.png

File
Users can upload a file from their computer. If it is an image file then the picture field may be a better choice.

Filefield.png

Latitude/longitude
Users can enter a geographic location using latitude and longitude. For example, Moodle HQ is at latitude -31.9545, longitude 115.877. When viewing the record, links are automatically generated linking to geographic data services such as Google Earth, OpenStreetMap, GeaBios,Mapstars and more. (The teacher can choose which of those links appear, if any.)
Data input requirements: Data for this field must be input relative to North and East coordinates. To convert coordinates from West (e.g. 120W) to East, simply input them into the field with a '-' in front (e.g., -120). Also, data must also be input in decimal format, not degree format. Simple converters are a web search away.

Latlongfield.png

Menu
The text entered in the options area will be presented as a drop-down list for the user to choose from. Each line become a different option.

Menufield.png

Menu (Multi-select)
The text entered in the options area will be presented as a list for the user to choose from and each line become a different option. By holding down control or shift as they click, users will be able to select multiple options. This is a fairly advanced computer skill so it may be wise to use multiple checkboxes instead.

Multiselectfield.png

Number
This allows users to enter any number. For example:
  • 42
  • -1000
  • 0
  • 0.123
  • 3.0e8
(For those who care about the technical details, the field stores floating point numbers.)

Numberfield.png

Picture
This allows a user to upload and display an image file. "Single view" is when the image is viewed on its own; "list view" is when it is viewed in with other images. Single view can be larger than list view.

Picturefield.png

Radio buttons
The user gets radio buttons and can choose only one. They can only submit the entry when they have clicked on one button. (Note:If you only have two options and they are opposites (true/false, yes/no) then you could simply use a single checkbox instead. However checkboxes default to their unchecked status and so people could submit without actively selecting one of the options. This may not always be appropriate.)

Radiofield.png

Text input
Users can enter text up to 60 characters in length. For longer text, or for text that requires formatting such as headers and bullet points, you can use a text area field.

Textfield.png

Text area
This allows users to enter a long piece of text using the text editor.

Textareafield.png

URL
The user can add a link to a website here. If you select autolink then the URL becomes clickable.. If you also enter a forced name for the link then that text will be used for the hyperlink. For example in a database of authors you may wish people to enter the author's website. If you enter the text 'homepage' as a forced name then clicking on text "homepage" will take you to the entered URL.

You can choose to have the URL open in a new window if so desired.

Urlfield.png

Presets

To avoid the necessity of always having to create a new database from scratch, the database activity module has a presets feature. An image gallery preset is included in the database module or you can browse and download one from the Database presets database on Moodle.net. You can create your own presets as well and share them with others.


Importing a preset

Database presets page

To use a preset:

  1. On the database page, click the Presets tab.
  2. The usual way to import a preset is clicking the "Choose file" button and browsing to the ZIP file containing the preset (eg. "preset.zip") stored on the server. If there does not yet exist a preset ZIP file on the server, upload it from your local machine. Choose the desired preset file by clicking on its action "Choose". Finally, press the "Import" button.
  3. Alternatively, you can choose a previously loaded preset.
  4. After the import, you can start to add or import entries. All the hard work of setting up the fields and templates has been done for you. If you desire, you can still customize the fields and templates.

You will see a "Overwrite current settings" checkbox after the "Import" button has been pressed. If checked, the database activity overwrites some of its current general settings by those stored in the preset:

  • the "Introduction" HTML text
  • "Required entries"
  • "Entries required before viewing"
  • "Maximum entries"
  • "Comments"
  • "Require approval?"
  • rssarticles

Exporting a preset

If you wish to share your database presets with others, you have two options:

  1. Export as a ZIP file, which can then be imported to another course or Moodle site.
  2. Save as a preset, which publishes the database for other teachers on your site to use. It will then appear in the preset list. (You can delete it from the list at any time.)

Note: Only the fields and templates of the database are copied when exporting or saving it as a preset, not the entries.

Presets for download

The Activity Examples course on the School demo site has a number of database activities with presets for download.

See also

Forum discussions: