Home

Using Databases – OpenOffice.org Base

1. List E Index field G Disallow duplicates H Sort order Image No 8 Actions with indexes To index a table field 31 Module 5 Open the Indexes dialogue box Click the New Index button Enter a name for the index Ye In the menu select the field to be indexed SYS IDX_46 Unique artist Fields index field Artist Ascending a Help Close the dialogue box by clicking Close 6 Confirm saving by clicking Yes Note Indexing too many fields can slow the application down Record Sorting Records in tables forms and queries can be easily sorted in ascending or descending order by a selected field Sorting by multiple conditions is also possible first sort by one field then continue sorting by another Record sorting is performed similarly in the previously mentioned database objects and it is easy to do by using the toolbar buttons Zz AL Zz Eh uz WA A B C A Sort button to sort by several fields B Sort Ascending button to sort in alphabetical order C Sort Descending button to sort in reverse alphabetical order Image No 9 Buttons for record sorting To sort table records in ascending order by a selected field Open a database table form query in data view Select the field by which to sort Click the Sort Ascending button Save the changes in the table a a ae To sort table records by several fields 1 Open a database table in data view 2
2. 9 6 Left click on the selected text 9 7 While holding the mouse button drag the selected field to the next column 9 8 Release the mouse button Close the report 10 1 Perform the menu command File gt Exit 49 Module 5 10 2 In the dialogue box confirm saving the changes by clicking Yes 11 Close the database 11 1 Click the Close button in the title bar 11 2 Confirm the changes by clicking Yes Data Export Export commands are used to transfer data to a different application Although Base does not have direct commands for export of database tables and queries the integrated OpenOffice org environment provides many possibilities for saving data in a different file format Note Data export is performed from the structure of the actual database and is usually executed with direct database commands or with an executable script written in a programming language This type is not examined in this material as it requires special knowledge Tables and Queries To save data in a different format 1 They must be copied into OpenOffice org Writer or Calc 2 Then use the menu commands File gt Save As or File gt Export or File gt Export as PDF In Base these actions can be performed with e Tables e Query results e Individual table and query result records The easiest way to do this is by dragging It is also possible to use the standard commands Copy and Paste Task 5 8 Save database table dat
3. In the Report Wizard dialogue box 3 1 In step one Field Selection in the Tables or queries menu select the table or query and the fields to be included in the report 3 2 If necessary change the position of the selected field by using the Up and Down buttons Report Wizard Steps Which fields do you want to have in your report 1 Field selection Tables or queries 2 Labeling fields Table tbl_albums 3 Grouping Available Fields Fields in report Pen ID 4 Sort options Album 5 Choose layout _ Format Year 6 Create report Label On shelf Notes lt lt Binary Fields cannot be displayed in the report Help lt Back Next gt N Finish Cancel 3 3 Step two Labeling Fields allows changing the Labels field names of the fields displayed in the report 45 Module 5 A Report Wizard Steps How do you want to label the fields 1 Field selection Field Label 2 Labeling Fields Artist Artist 3 Grouping Album Album 4 Sort options Format frormat SSS 5 Choose layout ela 6 Create report ol Year Label Label On shelf on shelf Notes Notes Help lt Back pE Finish Cancel 3 4 In step three Grouping if necessary select the fields by which to group the records in the report e g Artist Report Wizard Steps Do you want to add grouping levels 1 Field selection Fields Groupings 2 Labeling Fields Format 3 Grouping Year L
4. Table Data toolbar E Field name F Selected record G Field Image No 1 A Base table in data view A table is organized in horizontal rows and vertical columns e Field contains information about a table object fields form table columns Fields are normally given a name in the column header indicating what kind of data they contain A particular data type can be assigned to fields e g numbers text dates The application will not allow entering data that does not match the type defined for the field In addition it is possible to define field properties such as text length limitations format and number of decimal places A single data object is entered in each field for example artist or band name in one field album name in another e Record a table row with related data contains information about a single table object It is possible to create a database with a single large table but it is easier to process information when data is stored in several tables The larger the database the more important it is to place thematically different data groups in separate tables For example in a database of music records it is better to store artist data separately To add new information to a database it is more convenient to create a new table without modifying the existing table structure Primary key To ensure interlinking of specific database tables one or several primary key fields are defined in e
5. tbl for table frm for form qry for query rpt for report or use prefixes chosen by the user for different database objects Creating a form by using a wizard 7 Inthe Database pane select Forms 8 Inthe Tasks pane click on Use Wizard to Create Form w Create Form in Design View Description The wizard will guide you through the steps necessary to create a form izard to Create Form 9 Step one select the table fields to be included in the form 9 1 In the Tables or queries menu select the database table 9 2 In the Available fields menu select the fields for the form 9 3 Move the selected fields or all fields to the pane Fields in the form Databases E Form Wizard Steps Select the fields of your Form Tables or queries 2 Set up a subform Table tb_Library 3 Add subform fields Available fields Fields in the form 4 Get joined fi De Notes A 4 Get joined Fields Gia Pages 5 Arrange controls Title gt gt Genre 6 Set data entry BookID i AuthorID 7 Apply styles Publisher A ci a lt lt ISBNNumber Sores CopyrightYear L Peeeebh A a Binary fields are always listed and selectable From the left list IF possible they are interpreted as images Help lt Back Next gt N Finish Cancel 9 4 Click Next 10 Step two setting up a subform not examined in this study material 10 1 If necessary add a subform 10 2 Click Next 11 Step five if
6. 2 5 Right click inside the text document 2 6 In the menu choose the Paste command 2 7 1In the dialogue box Insert database Columns insert all fields in the Table column s pane Database columns Table column s 3 Artist s a L Ta 2 8 Click OK in the dialogue box 3 Save the text document as PDF 3 1 In the Writer window perform the menu command File gt Export as PDF 3 2 In the PDF Options dialogue box confirm the default settings by clicking Export 3 3 In the Name field enter the filename artists 3 4 Choose the Documents folder as the save location if necessary 3 4 1 Select the Documents folder in the Place pane of the Save dialogue box 3 5 Click Save 4 Close the Writer window 4 1 Click the Close button in the title bar 4 2 Click Discard in the dialogue box 5 Copy the results of the database query qry_artists into a new Writer document 5 1 In the Database pane select the object Queries 5 2 In the Queries pane select the query qry_artists 5 3 Perform the menu command Edit gt Copy 5 4 In the database window perform the menu command File gt New gt Text Document 5 5 In the Writer window perform the menu command Edit gt Paste 5 6 In the dialogue box Insert database Columns move the fields Artist Country Notes to the Table Column s pane Database columns Table column s Year founded 59 Artist Country Notes 5 7 Click OK 6 Set page format A4 55 Module 5 6 1
7. 5 1 Understanding 5 1 1 a Concepts 5 1 1 1 Understand what a database is _ 5 1 1 2 Understand the difference between data a a ee 5 1 1 3 Understand how a database is organized in a a 5 1 1 4 Know some of the common uses of large Error scale databases like airline booking systems Bookmark government records bank account records not hospital patient details defined 5 1 2 Database 5 1 2 1 Understand that each table in a database Organization should contain data related to a single subject type 5 1 2 2 Understand that each field in a table should ee ee ee 5 1 2 3 Understand that field content is associated with an appropriate data type like text number date time yes no 5 1 2 4 Understand that fields have associated field Error properties like field size format default Bookmark value not defined PY S25 Understand what primary keys 5 1 2 6 Understand what an index is Understand how it allows for faster data access 5 1 3 Relationships 5 1 3 1 Understand that the main purpose of relating tables in a database is to minimize duplication of data 5 1 3 2 Understand that a relationship is built by matching a unique field in one table with a field in another table 5 1 3 3 Understand the importance of maintaining the integrity of relationships between tables 5 1 4 Operation 5 1 4 1 Know that professional databases are Error designed and created by database Bookmark specialists not defined 5 1 4 2 Know t
8. In the Database Wizard click Open Select the database file on the computer s hard drive 4 Complete the action by clicking Finish Actions with Database Objects OpenOffice org Base objects are used for actions in a database tables forms queries reports Two view modes are used for objects e Data view Used to enter output data view reports e Design view Used to design database objects add elements edit set properties and object features To open a database object in data view 1 Select a database object type in the Database pane of the Base window 2 Activate the database object 2 1 Double left click on the icon in the object pane or 2 1 Click the button Open Database Object in the object toolbar course_database odb OpenOffice org Base File Edit View Insert Tools Window Help A z 7 a J aa A 5 Wz Aa E Q n a 4 p e Open Database Object E Create Table in Design View Description Use Wizard to Create Table Create View Queries OK Forms Course Nr Course name Reports 1 BGP Configuring BGP on Cisco 2 Advanced Services Cisc 3 CUDN Cisco Unity Design and Net 4 CVOICE Cisco Voice over IP CVOIC 5 CWAAS Cisco Wide Area Applicatio 6 DESGN Designing for Cisco Interne 7 ICND1 Interconnecting Cisco Netv 8 ICND2 Interconnecting Cisco Netv 9 IP6FD IPv6 Fundamentals 1 IUWNE Implementing Cisco Unifiec Embedded databkse HSQL database engine A B C D
9. STUDENT S BOOK th 5 module Using Databases OpenOffice org Base This work is licensed under a Creative Commons Attribution ShareAlike 3 0 Unported License http creativecommons org license s by sa 3 0 This document has been produced with the financial assistance of the European Union The views expressed herein are those of the author and can in no way be taken to reflect the official opinion of the European Union The material prepared for ECDL certification in accordance with Sylabus 5 requirements The project is implemented in collaboration with er F Education and Culture DG Lifelong Learning Programme Project N2 2010 1 LV1 LEO05 00868 Table of Contents DATABASES 4 Introduction to Databases 4 Tables 5 Forms 7 Queries 8 Reports 9 Creating a Database 9 Creating Databases and Objects with a Wizard 9 Saving a Database 16 Actions in a Database 19 Opening a Database File 19 Actions with Database Objects 20 Creating a Table in Design View 22 Modifying a Form in Design View 26 Entering Modifying and Sorting Data 28 Entering Data in a Database 28 Creating a New Record 29 Deleting a Record Data 29 Field Index Management 30 Record Sorting 31 Record Search 32 Data Retrieval 35 Applying Filters 35 Creating Queries 37 Creating a Query with a Wizard 38 Creating a Query in Design View 41 Information Output 43 Reports 43 Data Export 49 Printing 51 Additional Actions in the Application 55 App
10. Year founded and Country from the table tbl_ groups Field Artist hd Alia tbl_albums ima tbl_albums ID Table tbl_albums Artist tbl_albums Album E tbl_albums Format visible ef tbl_albums Year tbl_albums Price Function tbl_groups nes tbl_groups Artist ene tbl_groups Year founded Or tbl qroups Country A 15 9 In the Field row of the query table select the Album field from the table tbl_albums 15 10 Remove the tick from the Visible checkbox of the Country field 15 11 Enter UK in the Country field of the Criterion cell 15 12 Press the Enter key on the keyboard Databases 68 E tbl albums E tbl groups P i 7 ID Artist Artist Year found Album Country Format Active i Year founded Country Album tbl _ albums tbl groups tbl groups tbl_ albums visible 4 Sj Function Criterion UK 15 13 Save the query 15 14 Click the Save button in the toolbar of the query window 15 15 In the Query name field of the Save As dialogue box enter qry_UK 15 16 Click OK 15 17 Close the query window by clicking the Close button in the title bar 16 Change the record selection criterion LP OR CD to CD in the query qry_CD_LP 1 point 16 1 Open the query qry_CD_LP in editing mode 16 2 In the Database pane select the database object Queries if necessary 16 3 In the Queries pane select the query qry_CD_LP 16 4 Click the Edit button in the toolbar A o i alle 16 5 In the Format co
11. but if necessary it is easy to switch to the help of other applications of the OpenOffice org suite The window of the help system is divided in two sections the selection pane and the text search result area To bookmark a topic Click the button Add to Bookmarks Add to Bookmarks Displaying and Hiding Toolbars While working in the application it is possible to accidentally close a necessary toolbar To open a Base toolbar 1 Perform the menu command View gt Toolbars 2 Select the necessary toolbar in the menu In this module you learned e General understanding of databases e Actions in OpenOffice org Base e Creation of a simple database 57 Module 5 Actions with database objects Entry of information in a database Selection of data Export of records in a different file format Preparation for printing Databases 58 Supplement 1 ECDL requirements for Module 5 Databases e Understand what a database is and how it is organized and operated e Create a simple database and view the database content in various modes e Create a table define and modify fields and their properties enter and edit data in a table e Sort and filter a table or form create modify and run queries to retrieve specific information from a database e Understand what a form is and create a form to enter modify and delete records and data in records e Create routine reports and prepare outputs ready for distribution
12. gt Print Layout Perform the menu command Insert gt Header Default Click inside the header area Enter edit text Save the changes oe a a 6 1 Perform the menu command File gt Save or 6 1 Click the Save button in the toolbar A Artists from my collection D a Artist SaaS a Year founded ers Country Sy e aa Active Date added Notes B Remember always check data before adding to database A Header area B Footer area Picture No 7 A form in design view with text in the header and footer in print layout 27 Module 5 Task 5 3 Add header text to a form in a database Format the background of the form Remove the footer text Delete the form 1 Open the database form odb 1 1 Perform the menu command Places gt Documents in the top panel of the desktop 1 2 Open the folder 5_ databases with a double left click 1 3 Open the folder 5 3_form_design with a double left click 1 4 Open the file form odb with a double left click 2 Open a form in design view 2 1 In the Database pane select the object type Forms 2 2 In the Forms pane select the form frm_artists 2 3 Click the Edit button in the toolbar E eh el 3 Change the form view to print layout view 3 1 Perform the menu command View gt Print Layout in the form window 4 Add text to the header 4 1 Perform the menu command Insert gt Header Default 4 2 Enter the following text in the header Artists fr
13. uppercase Creating a Query in Design View Like the other database objects queries can also be created in design view by including one or several database tables Design view is convenient for modifying existing queries adding new fields and changing criteria Design view allows specifying a selection criterion for a field without including the field itself in the results table To create a query in design view Select the object type Queries in the database Perform the command Create Query in design view Select and add the tables from which to retrieve data for the result Select the table fields that are to be included in the query result Set the query criteria for the fields ae SP Save the query entering a name for it Task 5 6 Create a query from two interlinked tables which will select albums of UK bands released after 1979 in the database 1 Open the database albums odb from the subfolder 5 6 query_design of the folder 5 databases 1 1 Perform the menu command Places gt Documents Databases 42 1 2 Perform the file browser menu command Go gt 5_ databases 1 3 Perform the file browser menu command Go gt 5 6_query_design 1 4 Select the file albums odb 1 5 Press Enter on the keyboard 2 Create a query in design view 2 1 In the Database pane select the database object type Queries 2 2 In the Tasks pane click on Create Query in design view 2 3 In the dialogue box Add Table or Query add table
14. 2 Press the Delete key on the keyboard 22 3 In the dialogue box confirm deleting by clicking Delete 23 Assign the name This is part of my collection to the report rpt_header 1 point 23 1 Select the report rpt_header in the Reports pane 23 2 Click the Edit button in the toolbar oN d ml Edit 23 3 Perform a left click inside the header field Title 23 4 Enter the text This is part of my collection 24 Save the report rpt_header in Adobe Acrobat format 1 point 24 1 Perform the report window menu command File gt Export as PDF 24 2 Confirm the default settings by clicking Export 24 3 In the Name field of the Export dialogue box enter export 24 4 Click Save 24 5 Click the Close button in the title bar 24 6 In the dialogue box confirm saving the changes by clicking Save 25 Close the database 1 point 25 1 Click the Save button in the toolbar of the application window 25 2 Click the Close button in the title bar
15. A Selected database object type B Selected object C Open object in data view D Object preview Image No 7 Previewing and opening a table 21 Module 5 Useful tip It is possible to enable preview for the selected database object D in Image No 7 Document 7 To open a database object in design view 1 Select the database object type in the Database pane of the Base window Select a database object 3 Open the object in design view 3 1 Click the Edit button in the object toolbar or 3 1 Perform the menu command Edit gt Edit To delete a database object 1 Select the database object type in the Database pane of the Base window Select a database object Delete the object 3 1 Click the Delete button in the object toolbar or 3 2 Perform the menu command Edit gt Delete To rename a database object Select the database object type in the Database pane of the Base window Select a database object Rename the object 3 1 Click the Rename button in the object toolbar SF TN or 3 2 Perform the menu command Edit gt Rename Useful tip Commands for actions with a database object are easy to find in the menu that opens upon right clicking on the object Databases 22 EER EE Copy Delete Rena me Edit E Select All Database gt Creating a Table in Design View Like the other database objects tables can also be created manually in design view F
16. Click the Sort button 3 Inthe first Field Name menu select the first field by which to sort Databases 32 4 In the Order menu choose the sort order Ascending for alphabetical order Descending for reverse alphabetical order 5 Repeat the steps for the following sort conditions 6 Confirm by clicking OK Sort Order Operator Field name Order Artist ascending Cancel and then N ascending m oH elp and then lt none gt ascending m Record Search When working with data in large tables it may be difficult to locate specific data for viewing modification or replacement with a different value Like in other applications of the OpenOffice org suite it is possible to use a search command To find a value in a database table 1 Open a table in data view 2 Click the Find Record button in the toolbar 3 In the Record Search dialogue box enter the value to search for and the search settings amp Record Search Text 1977 m Field content is NULL A Close Field content is not NULL Help Where to search All Fields Single Field Year founded ettings Position anywhere in the field v Apply field format Search backwards wildcard expression Match case From top Regular expression Similarity Search State Record 9 D A The value to search for B Search in All Fields or a Single Field C Optional search settings D The number of the re
17. Description aq Use Wizard to Create Q very Lslotsieairhajeah Prt ie rough the steps so Create Query in SQL View 2 3 In step one 2 3 1 In the Tables menu under Select the fields columns for your query select the table tbl_ artists 39 Module 5 2 3 2 In the pane Fields in the Query add table fields Artist Year founded and Notes 2 3 3 Click Next Query wizard Steps Select the fields columns for your query Tables 2 Sorting order Table tbl_artists 3 Search conditions Available Fields Fields in the Query ID tbl_artists Artist Country tbl_artists Year founded 5 Grouping Active tbl_artists Notes 4 Detail or summary Date added 6 Grouping conditions 7 Aliases 8 Overview Finish 2 4 Step two Select the sorting order 2 4 1 In the Sort by menu select the Artist field 2 4 2 Choose reverse alphabetical order Descending 2 4 3 Click Next 2 5 Step three Select the search conditions 2 5 1 In the Fields menu select the Country field 2 5 2 In the Condition menu select like 2 5 3 In the Value field enter UK in uppercase and without apostrophes 2 5 4 In the next line choose Year founded in the Fields menu 2 5 5 Set the Condition to is greater than 2 5 6 In the Value field enter 1970 2 5 7 Make sure that Match all of the following has been selected logical operator AND E Query Wizard Steps Select the search conditions 1 Field selection Ma
18. Wizard to Create Query Select a table in the Tables menu Specify what fields from the table to display in the query result Select ascending or descending sorting order oe S i a Specify the selection criteria The wizard allows specifying three fields and creating conditions for them 7 If necessary set additional mathematical operations to be performed with the values in the returned fields 8 If necessary assign aliases alternative explanatory names to the table fields 9 Inthe last step check the query overview and name the query 10 Finish the wizard by clicking Finish Task 5 5 By using the wizard create a query in the artist database table tbl artists e Artists from the United Kingdom UK e With year founded after 1970 e The selected records must be sorted in descending order by the band name the Artist field e The results must display the fields Artist Year founded Country and Notes 1 Open the database uk odb in the subfolder 5 5 query_from_wizard of the folder 5 databases 1 1 Perform the menu command Places gt Documents 1 2 Open the folder 5_ databases with a double left click 1 3 Open the folder 5 5_query_from_wizard with a double left click 1 4 Open the database uk odb with a double left click 2 Create a query as specified in the task 2 1 In the Database pane select the object type Queries 2 2 In the Tasks pane click on Use Wizard to Create Query Create Query in Design View
19. be inserted before the number 1979 and the letters UK will be enclosed in apostrophes 3 Runthe query 3 1 Click the Run Query button in the toolbar Ni gt Run Query 4 View the result of the query The wall double LP 1979 Pink Floyd Coda LP 1982 Led Zeppelin One Step Beyond LP 1979 Madness Keep Moving LP 1988 Madness J Tull Dot Com cD 1999 Jethro Tull Note Although the query also uses the Country field of the table tbl_artists it will not be displayed in the result as the checkbox of the property Visible has not been ticked 5 Sort the query results in ascending order by artist name 5 1 Left click on an Artist field 5 2 Click the Sort Ascending button in the toolbar 6 Save the query 6 1 Click the Save button in the toolbar 6 2 Name the query qry_selection_1979 6 3 Confirm by clicking OK Information Output Reports Databases 44 OpenOffice org Base allows creating and printing reports including in them specific selected data or all the information stored in a database The built in wizard makes it easier to create and design a report A report is saved as a database object and can be reused with modifications if necessary A report can be created both from a database table and from a query with selected results To create a report with a wizard In the Database pane of the Base window select the object type Reports In the Tasks pane click on Use Wizard to Create Report
20. for completion of the tasks it is also possible to use other methods that are more convenient and familiar to you Try to complete the tasks on your own without following the instructions The work has been completed successfully if the number of points obtained exceeds 24 75 of the maximum possible 32 1 Answer questions and enter the correct answer versions in a text file 7 points 1 1 Open the text file questions odt 1 1 1 Perform the top panel menu command Places gt Documents 1 1 2 Open the folder 5_datubazes with a double left click 1 1 3 Open the folder5 10_test with a double left click 1 1 4 0pen the file questions odt with a double left click 1 2 Open the text document file answers odt 1 2 1 0pen the folder 5 10_test by clicking the respective button in the bottom panel 5 10 test 5 10_test Ee 1 2 2 Open the file answers odt with a double left click 1 3 By switching between the windows of the text files answer the questions and enter the letters of the correct answer versions in the document answers odt 1 4 Save the changes in the document answers odt 1 4 1 Click the Save button in the toolbar 1 5 Close both documents 1 5 1 Click the Close button in the title bar of the documents 2 Open the database file music odb 1 point 2 1 If necessary open the window of the folder 5 10_test similarly as in task 1 2 2 Perform a left click on the database file music odb 2 3 Press the Enter key
21. on the keyboard 3 Create a new table called tbl_media in the database 1 point Data type Field properties oe AutoValue Yes Description Length 50 Date added MMM D YY 3 1 In the Database pane of the application select the object type Tables 3 2 In the Tasks pane click on the action Create Table in Design View 3 3 In the Field Name field first row enter the field name Media ID 3 4 In the Field type field choose Integer 3 5 Choose Yes in the AutoValue menu of the Field Properties pane Databases 62 Field Name Field Type Media 10 ID Integer INTEGEI AutoValue CE 3 6 In the next row enter the name Description in the Field Name field 3 7 Enter the number 50 in the Length field of the Field Properties pane replacing the default value Integer INTEGER Text VARCHAR Entry required No al Length 50 3 8 1n the third row enter the field name Date added in the Field Name field 3 9 In the Field type field choose the Date format 3 10 In the Field Properties pane click the browse button of the Format example field Format example 01 01 00 EN 3 11 In the Format pane of the Field Format dialogue box select the date format MMM D YY Category Format All a 12 31 99 User defined Friday December 31 1999 Number 12 31 99 a Percent TETTE C Dec 31 1999 Time 31 ala 1999 Scientific ee Options Decimal places 0 Negative nu Leading zeroes 0 Th
22. system Base Version 3 of the software uses integrated database engine HSQL but also allows connecting to other types of databases As relatively small databases use this type of integrated solution the term database is used to refer to both the software and the integrated data storage mechanism at the same time Base uses the following objects for data management e Tables e Forms e Queries e Reports Tables Data in a database are stored in a table or multiple interlinked tables The table is the fundamental structure of a database so it is important to understand how it works Databases 6 H o tbl_albums parskats Final OpenOffice org Base Table Data View File Edit View Insert Tools Window Help C ee BN F D o D Att Album Format Year label n 1 Pink Floyd Ummagumma double LP 1969 EMI Harve a2 Pink Floyd The Dark Side of the Moon LP 1973 EMI Harve Seeks Pink Floyd Animals Pt 4 Pink Floyd The Wall double LP 1979 as Led Zeppelin Led Zeppelin LP 1969 Atlantic R ac Led Zeppelin Led Zeppelin IV LP 1971 Atlantic R a7 Led Zeppelin Physical Graffiti double LP 1975 J8 Led Zeppelin Presence LP 1976 Swan Son l9 Led Zeppelin Coda LP 1982 Lf G 10 Inxs Kick LP 1987 WEA E Inxs x LP 1990 a 12 Inxs Elegantly Wasted LP 1997 ais Inxs Original Sin cD 2010 14 Inxs Listen Like Thieves LP 1985 15 Rammstein Herzeleid cD 1995 Record 3 lo Z29 m B A Record selection cell B Navigation C Menus D
23. table wizard Click Finish to save the database Help lt lt Back Next gt gt Cancel a In the Name field of the Save dialogue box enter a name for the database If necessary specify the location in the computer s file system 11 Save Name Module 5 Browse for other Folders 4 dace Documents Create Folder Places Name v Size Modified Q Search 4_izklajlapas 01 26 2011 Recently Used 6_prezentacijas Sunday course_database odb 34 8 KB Monday dace test_database odb 16 9 KB Monday E Desktop O File System 000 Documents W Music m Pictures E Videos Downloads gt File type ODF Database v 7 Save by clicking the Save button Ss File Tables N Queries Forms Reports Embeddedidatabase A music_base odb OpenOffice org Base o Edit View Insert Tools Window Help E Create Table in Design View Description E G Use Wizard to Create Table create View None HSQL database engine B C D A Database objects B Pane of the selected object C Data storage engine D Preview of the selected object E Menu bar F Toolbar G Task description H Creation of an object Image No 6 OpenOffice org Base Creating a table by using a wizard 1 Inthe Database pane select the object type Table 2 Inthe Tasks pane click on Use Wizard to create Table Databases 12 Create T
24. to the table Artist Year Country Active Date added Notes founded 1977 Feb 8 2011 leader Mark Knopfler Databases 34 Useful tip The content of identical fields can be copied and pasted with the commands Copy and Paste The format of the Date added field is set as MMM D YYYY the first three letters of the month the date the year with four digits There is a checkbox in the Active field 4 Delete the record about Monty Python 4 1 Click inside the table field containing the text Monty Python 4 2 Perform the menu command Edit gt Delete record 4 3 In the dialogue box confirm the deletion by clicking Yes 5 By using the search tool in the table change an incorrect record with the value 1937 to 1977 5 1 Open the search tool by clicking the Find Record button in the Table Data toolbar of the table window 5 2 In the Text field of the search dialogue box enter 1937 5 3 Click Search 5 4 Close the search dialogue box by clicking Close 5 5 Change the data in the field of the record to 1977 5 6 Do not close the table 6 Create new records by using the database form frm_artists Artist Year Country Active Date added Notes founded 6 1 Switch to the database window 6 1 1 Click on the button of the window in the bottom panel of the desktop E data odb Op tbl artists da 6 2 In the Database pane choose the object Forms 6 3 Select the form frm_artists 6 4 Press Ent
25. will automatically add a Primary key to the record 5 2 Tick the checkbox Auto value Table wizard Steps Set primary key 1 Select Fields A primary key uniquely identifies each record in a database table Primary keys ease the linking of information in separate tables and it is recommended that you have a 2 Set types and formats primary key in every table Without a primary key it will not be possible to enter data into this table 4 Create table Create a primary key Automatically add a primary key Use an existing field as a primary key Fieldname Auto value Define primary key as a combination of several Fields Available Fields Primary key fields Notes TaskID id EndDate StartDate Help lt Back Next gt p Finish Cancel 13 Module 5 5 3 Click Next 6 Step four finishing the table wizard 6 1 Enter the table name the field What do you want to name your table 6 2 Click Finish Table Wizard Steps Create table 1 Select Fields What do you want to name your table tb_Libra 2 Set types and formats rary 3 Set primary key 4 Create table Congratulations You have entered all the information needed to create your table What do you want to do next Insert data immediately Modify the table design Create a form based on this table Useful tip When assigning names to database objects it is recommended to add a short prefix describing the object type
26. 0 1 Close the table saving the changes 10 1 1 Click the Close button in the title bar of the database window 10 1 2 In the dialogue box click Save 10 2 Close the form 10 2 1 Click the Close button in the title bar of the database window 10 2 2 In the dialogue box click Save Delete k 10 3 Close the database saving the changes 10 3 1 Click the Close button in the title bar of the database window 10 3 2 In the dialogue box click Save Data Retrieval There would be little point in storing information in databases if it was not possible to view the data and use it further Applying Filters Databases 36 Using filters is one of the simplest ways of data selection In Base filters can be applied to records in tables forms and queries Filters are applied by using toolbar buttons A AutoFilter B Apply filter C Standard filter D Remove filter a ES 4d A B C D Image No 11 Filters The automatic filter selects data by determining whether they match the value of the selected field To use the automatic filter 1 Open a database object in data view 2 Select the field by which to select records 3 Click the AutoFilter button in the toolbar Useful tip The automatic filter can be applied successively several times selecting records by multiple conditions An applied filter is indicated by a depressed Apply Filter button The standard filter in Base allows selecting reco
27. 1 oF 1 4 p a Ok OK r 1 Da R lt xO m tt a j Page 1 1 Default so 100 Saving a Database OpenOffice org Base saves databases with the filename extension odb The default location is the Documents folder created by the operating system in the user account unless a different location is specified The location specified in the default settings of the application can be changed by the user A database file includes all of the database s objects and data If changes have been made to a database object or data in a table the application will ask to confirm saving them also when closing individual objects 17 Module 5 Task 5 1 Create a music CD database by using the built in wizards of Base with the default settings Create a table and form for data entry and display 1 Create a database called music_cd odb in the Documents folder 1 1 0pen Base with the menu command Applications gt Office gt OpenOffice org Database 1 2 In the Database Wizard make sure that Create a new database has been selected 1 3 Click Next in the dialogue box 1 4 In step two accept the default settings by clicking Finish 1 5 In the Name field of the Save dialogue box enter database name music_cd 1 6 Click Save 2 Create a table called tbl_CD Collection by using a wizard 2 1 In the Database pane select the object Tables if necessary 2 2 In the Tasks pane click on Use Wizard to Create Table 2
28. 3 Select the category Personal 2 4 Select the table CD Collection in the Sample tables menu 2 5 Use all the sample table fields Category Business Personal Sample tables CD Collection Available Fields Selected Fields Notes z Artist Format Rating Review Producer S AlbumTitle PurchasedAt at oo lt 2 6 Click Next to move on to the next step of the wizard 2 7 Confirm the default field settings by clicking Next 2 8 Set the field CollectionID as the primary key 2 8 1 Click on the radio button Use an existing field as primary key 2 8 2 In the Fieldname menu choose CollectionID 2 8 3 Tick the Auto value checkbox Create a primary key Automatically add a primary key Auto value Use an existing Field as a primary key Fieldname CollectionID Auto value 2 8 4 Click Next 2 8 5 Enter tbl_CD Collection in the field What do you want to name your table 2 8 6 Click Finish 3 Create a form called frm_CD Collection 3 1 In the Database pane select the object Forms 3 2 In the Tasks pane click on Use Wizard to Create Form 3 3 Insert all fields of the table tbl_CD Collection in the form Databases 18 gt Ns 3 4 Move CollectionID to the top of the list of fields in the form 3 4 1 In the pane Fields in the form select the field CollectionID 3 4 2 Move it to the top Fields in the form Notes Artist Format m Rating A N Review Y Producer AlbumTitle PurchasedAt M
29. 4 Sort options Align Left Modern Drafting w Align Left Red amp Blue Finances 5 Choose layout Default Flipchart Outline Borders Formal with Company Logo i Outline Compact Generic Outline Elegant Worldmap Outline Highlighted Outline Modern Nunblina Dad 2 Rina Orientation Note The dummy text will be Landscape replaced by data from the database i when the report is created Portrait o Help lt Back cee Finish Cancel 3 7 In step six Create report finish the creation of the report by choosing Static report if the report is to include the current records of the database Dynamic report if the report is to include the records from the database as at the time when the report is opened refreshing it each time 4 Save the report entering a name for it Task 5 7 Create a report for the table tbl_ albums of the database report odb including all fields grouping by the Artist field and sorting in ascending order Data layout style Align left Modern landscape orientation dynamic refreshing 1 Open the database report odb from the subfolder 5 7_reports of the folder 5 databases 47 Module 5 1 1 Perform the menu command Places gt Documents 1 2 In the file browser perform the menu command Go gt Locations 1 3 In the Location field of the file browser add 5_databases 5 7_reports to the address Useful tip The folders automatically entered by the file browser can be confirmed by pre
30. 7 Module 5 14 6 In the Field row sequentially select the fields from the table tbl_ albums in the menus 14 7 Tick the Visible checkbox for the first four selected table fields 14 8 In the Criterion cell create a record selection criterion for the Year field 14 8 1 Click inside the Criterion cell corresponding to the Year field 14 8 2 Enter gt 1981 14 8 3 Press the Enter key on the keyboard Artist Album Year Format Price ID Field Alias Table tbl_albums tbl_albums tbl_albums tbl_albums Sort visible 4 4 4 4 Function Criterion gt 1981 14 9 Click the Save button in the toolbar of the query window 14 10 In the respective field of the Save As dialogue box enter the query name qry_after_81 14 11 Click OK 14 12 Click the Close button in the title bar of the query window Tip Test if the created query works by running it 15 Create a query that displays the names and years founded of UK bands from the table tbl_groups and album names from the table tbl_albums 1 point 15 1 In the Database pane select the object type Queries if necessary 15 2 In the Tasks pane click on the task Create Query in Design View 15 3 In the dialogue box Add Table or Query select the table tbl_ albums 15 4 Click Add 15 5 In the dialogue box Add Table or Query select the table tbl_ groups 15 6 Click Add 15 7 Click Close 15 8 In the Field row of the query table sequentially select the fields Artist
31. Add artist data to the existing database music cd odb Set the field ID as the automatic primary key 23 1 Module 5 Open the database musisc_cd odb from the subfolder 5 2 table of the folder 5 databases 1 1 0pen OpenOffice org Base with the menu command Applications gt Office gt OpenOffice org Database 1 2 In the Database Wizard dialogue box select Open an existing database file What do you want to do San a new database Recently used music_cd Open h 1 3 Click Open 1 4 In the Places pane of the Open dialogue box select the user folder Documents 1 5 In the folder content pane select the folder 5_databases 1 6 Click Open 1 7 Select the folder 5 2_table 1 8 Click Open 1 9 Select the file music_cd odb 1 10 Click Open Create a new table tbl_ artists 2 1 In the Database pane select Tables 2 2 In the Tasks pane click on Create Table in Design View 2 3 Create table fields 2 3 1 Click on a cell 2 3 2 Enter text in the cells of the column Field name FieldName Field type Text VARCHAR Text VARCHAR Text VARCHAR Text VARCHAR Text VARCHAR Text VARCHAR Change the field type of the field ID 3 1 Click on the cell opposite to ID under Field Type 3 2 In the menu choose INTEGER a Integer INTEGE HES Text fix CHAR Number NUMERIC Decimal DECIMAL Small Integer SMALLIN Float FLOAT Real REAL Double DOUBLE Text VARCHA
32. Mark Knopfler Austria dead in auto accident 1976 UK USA USA 01 02 10 musician and composer 1977 Australia 1967 UK 1 02 04 11 Scheduled to play concerts in Australia in 2011 Led Zeppeli gt 1968 UK 01 04 10 disbanded after Bonham s death Linkin Park 1996 USA 1 02 08 11 1976 UK 1991 UK 1 12 1 born 3 december 1948 1965 UK 1 01 02 11 Wright and Waters left the group 1971 UK 1 without late Freddy 1994 Germany 1 25 Run D M C 1981 USA QO 02 10 11 Hip Hop x Sheet1 4 gt l Sheet 1 3 Default STD 9 Sum 0 QO ce 100 3 Save the Calc document in csv format 3 1 Perform the menu command File gt Save 3 2 In the Name field of the Save dialogue box enter the filename artists 3 3 In the File Type menu select Text CSV csv Text CSV csv Select whic ch types o F files are showr 3 4 Confirm saving by clicking Save 3 5 In the alert dialogue box confirm the selection of the format Keep Current Format 3 6 In the dialogue box Export of text files confirm the action by clicking OK 51 Module 5 Export of text files Field options Field delimiter Cancel Text delimiter i x Help Save cell content as shown Fixed column width 3 7 In the next dialogue box confirm saving the worksheet by clicking OK Forms and Reports To save form or report data in a different format 1 Open the form report 2 Perform the menu command File gt Save Copy As 3 Specify the file type in the dialogue b
33. Perform the menu command Format gt Page 6 2 Open the Page tab of the dialogue box by clicking on it 6 3 In the Paper format menu select page format A4 6 4 Close the dialogue box by clicking OK Save the text document 7 1 Perform the menu command File gt Save 7 2 In the Save dialogue box select the Documents folder as the save location if necessary 7 3 In the Name field enter the filename selected 7 4 Press Enter on the keyboard 7 5 Close the Writer window Close the database without saving the changes 8 1 Click the Close button in the title bar 8 2 In the dialogue box click Discard Open the file artists pdf 9 1 Perform the menu command Places gt Documents if necessary 9 2 Double click on the file artists pdf Additional Actions in the Application Application Help Base has a built in user manual with a search feature To open the help feature of OpenOffice org Perform the command Help gt OpenOffice org Help Or Press the function key F1 on the keyboard Databases 56 g OpenOffice org Help OpenOffice org Base OpenOffice org Base gt we T amp E5 Fat Contents Index Find Bookmarks Text Documents Using Databases in OpenOffice org amp HTML Documents B Presentations and Drawings ase Macros and Programming In OpenOffice org Base you can access data that is stored in a wide variety of installation database file formats OpenOffice org Base nativel
34. R Text VARCHAR_IGNORECASE Yes No BOOLEAN Date DATE Time TIME Date Time TIMESTAMP OTHER OTHER Databases 24 3 3 In the Field Properties pane set the property AutoValue to Yes Field Properties AutoValue a N Length 10 Format example 0 4 Set the Artist field as mandatory 4 1 Select the Artist field 4 2 n the Field Properties pane set Entry required to Yes Entry required ves ON 5 Change the type of the field Year founded 5 1 Choose the type NUMBER under Field Type 5 2 In the Field Properties pane set the Length value to 4 characters 6 Change the type of the field Active tick the checkbox 6 1 Change the type Yes No BOOLEAN under Field Type 7 Change the type of the field Date added 7 1 Set the field type to Date 7 2 In the Field properties pane click on the date format selection button Field Properties Entry required No Default value Format example 01 01 00 eI 7 3 In the Field Format dialogue box choose the date format MMM D YYYY Field Format Format Alignment Category Format Language All 12 31 99 English USA User defined Friday December 31 1999 gt Number 12 31 99 Percent 12 31 1999 Currency Dec 31 99 Dec 31 1999 Time 31 Dec 1999 k p sE December 31 1999 May 18 1903 scientific 24 Marambhar 1000 3 Options Decimal places 0 Negative numbers red Leading zeroes 0 Thousands separator F
35. Save Entering Modifying and Sorting Data Databases store data in tables Changes in the data can be made both by directly entering them in the table and by using forms linked directly and indirectly with a query with the table Entering Data in a Database To enter modify data in a table Select a table Open the table in data view Select a data field in a record oe Enter edit values text numbers or other To enter modify data in a form 1 Select a form Open the form in data view 3 Select a record 3 1 Enter the number of the record in the Absolute Record field Record 14 I of 418 Absolute Record 29 Module 5 3 2 Press Enter on the keyboard or 3 1 Use the navigation buttons Image No 4 4 Click inside the corresponding field 5 Enter modify data Creating a New Record To create a new record while using a table in data view no additional actions are required The fields of a new record are inserted below the last record in the table To create a new record while using a form 1 Open a form in data view 1 1 Go to the last record 1 2 Click the navigation button Next Record or 1 1 Click the New Record button image No Deleting a Record Data Records can be deleted both in a table and in a form linked to it To delete a record in a table 1 Open a table in data view 1 1 Click on the selection cell to select the record image No 1 2 Press Delete on t
36. To view a form in print layout In the form window perform the menu command View gt Print Layout To print database information in form view 1 Open a database form in data or design mode 2 Change the form view to print layout if necessary 3 Perform the menu command File gt Print 4 Select the printer 5 Inthe Print dialogue box set 5 1 All Pages to print all records in form layout 5 2 Pages to print specific records and or a range of records Specify the Number of copies in the respective field 7 Click Print Tables and Queries Tables and query results to be printed in full or in part are first copied into an OpenOffice org Writer document After that the editing and printing features of this application are used The steps for printing tables and queries are similar 53 Module 5 To print a database table or part thereof Select a database table Copy the table Ctrl C Open a new Writer document Perform the paste command using one of the available methods Ctrl V Select the pasting type table field text Select all or individual database fields for pasting into the text document Complete the pasting Edit and format the text in the document if necessary oo ee ee Print from the Writer environment To change page settings in Writer Open the Page Style dialogue box with the menu command Format gt Page Frequently used page settings in the Page tab of the dialogue box e Format t
37. a in comma separated values csv format 1 Open the database csv_export odb from the subfolder 5 8 export of the folder 5 databases 1 1 Perform the menu command Places gt Documents 1 2 Select the folder 5_ databases 1 3 Press Enter on the keyboard 1 4 Select the folder 5 8 export 1 5 Press Enter on the keyboard 1 6 Select the file csv_export odb 1 7 Press Enter on the keyboard 2 Copy a table into Calc 2 1 Select the table tbl_artists Databases 50 2 2 In the Database pane select the database object Tables 2 3 In the Tables pane select the table tbl_ artists 2 4 Perform the menu command Edit gt Copy 2 5 Create a new Calc document 2 5 1 Perform the menu command File gt New gt Spreadsheet csv_eksports odb OpenOffice org Base Edit View Insert Tools Window Help New Text Document Open cis ESSE HIR i A Recent Documents Presentation 2 6 Perform the menu command Edit gt Paste in the newly opened spreadsheet window g Untitled 1 OpenOffice org Calc File Edit View Insert Format Tools Data Window Help 6 222 saa Vie HEE 2 a rr OF MM BLA DejaVu Sans v 10 J Aa A A Slza E ah w w P FA classified as hard rock 1970 USA 1 1 born in Jakarta Indonesia 1983 UK 01 02 10 1989 USA 1 1966 UK 02 08 11 1980 UK 1 Dave Gahan lead vocals Germany 1 first album released in 1997 1977 UK 02 08 11 leader
38. a subform is not created selecting the design of the form 11 1 Choose the Label placement labels are field names 11 2 Choose the Arrangement of the main form _ Form Wizard Steps Arrange thee controls on your form 1 Field selection Label placement 2 Set up a subform Align left Align right 3 Add subform fields o Arrangement of the main Form 4 Get joined Fields 5 Arrange controls HE 6 Set data entry OO Columnar Labels Left 7 Apply styles Arrangement of the subform 8 Set name As Data Sheet Help lt Back Next gt IN Finish Cancel 11 3 Click Next 12 Step six selection of the data display and entry mode 12 1 Make sure that The form is to display all data is selected 12 2 Click Next 13 Step seven selection of the form style 13 1 In the Apply styles pane select the colour fill of the form 13 2 Under Field border choose the appearance of fields in the form 15 Module 5 Form Wizard Steps Apply the style of your form 1 Field selection Apply styles Field border 2 Set up a subform Bright Blue O No border 3 Add subform Fields Light Gray 3D look Dark Aan 4 Get joined fields Orange P Ice Blue 5 Arrange controls Grey Water 6 Set data entry Red Violet 7 Apply styles 8 Set name Help lt Bak G cinch canel 13 3 Click Next Useful tip A preview of the form can be seen right away i
39. abel 4 Sort options On shelf Notes g 5 Choose layout 3 6 Create report Note The dummy text will be replaced by data from the database when the report is created Help lt Back OO Neta Finish Cancel 3 5 In step four Sort Options choose the data sorting order Databases 46 Report Wizard Steps According to which Fields do you want to sort the data 1 Field selection Sort by eling Field Ascendin 2 Labeling Fields artist A g 3 Grouping Descending Then by 4 Sort options l Ascending undefined an 5 Choose layout ati Descending 6 Create report Then by Ascending undefined Descending Then by Ascenaing undefined a Descending Help lt Back Next gt Finish Cancel 3 6 In step five Choose Layout choose the Layout of data and Layout of headers and footers Default Author Dace Date 2 13 11 N i Artist Ut wisi enim ad minim veniam quis nostrud exerci tation Album Notes Ut wisi enim ad Ut wisi enim ad Ut wisi enim ad Ut wisi enim ad minim veniam minim veniam minim veniam minim veniam i amp Report Wizard Steps _ How do you want your report to look 1 Field selection Layout of data Layout of headers and footers ih Align Left Border Bubbles 2 Labeling fields r ing Align Left Compact Cinema 3 Grouping Align Left Elegant Controlling Align Left Highlighted Default
40. able in Design View Description Use Wizard to Create Table Choose from a selection of business and personal c te Vi table samples which you customize to create a Create View tabli 3 Step one select fields to be included in a table from a sample 3 1 Select the database category 3 2 Choose a table from the Sample tables list 3 3 Select the necessary fields in the table sample for the creation of the table 3 4 Click the respective button to insert the selected fields in the new table Table wizard Steps Select fields for your table 1 Select Fields This wizard helps you to create a table for your database After selecting a table category and a sample table choose the fields you want to include in your table You 2 Set types and formats can include fields from more than one sample table 3 Set primary key Category 4 Create table Business Personal Sample tables Library gt Available fields Selected fields Notes s CoverType ES Pages Translator Title PurchasedAt Genre DatePurchased i BookID EditionNumber AuthorID Publisher ISBNNumber Help lt Back Next gt N Finish Cancel 3 5 Click Next 4 Step two Set the data type of the selected fields if necessary 4 1 Select a field in the Selected fields pane 4 2 n the Field information pane change the field properties 4 3 Click Next 5 Step three set an automatic primary key 5 1 Make sure that the application
41. ach table The primary key uniquely identifies a table record within the entire database Primary keys are usually e A field specified by the user for example a catalogue number that does not repeat 7 Module 5 e A field created by the programme and filled automatically for example in the form of growing numbers The primary key is usually marked with a little key symbol Field index Indexing in programmes is used to ensure that records are found faster By default the primary key field is indexed but this property can be assigned to any field Relations Multiple tables are created in databases in order to prevent unnecessary duplication of data The linkage of tables is called relation In the example of a music records catalogue it is possible to create linkage between a particular artist in one table and the artist s albums in another table tbl_artists El tbl_albums Artist TID Year founded Ei Artist i Country Album Active Format Date added Year Notes Label On shelf Notes Image No 2 Linkage between tables For example the table tbl_artists contains information about artists band name year founded country etc the other table tbl_ albums lists the albums in the collection One record of the first table can be linked with several records in the list of this band s albums Such a relationship is called one to many and denoted by 1 n Databases also use a one to on
42. cord found Image No 10 Record search 33 Module 5 Click Search 5 View the result marked in the table with a red border perform the necessary actions 6 To continue searching click Search again Stop searching by clicking Close in the Record Search dialogue box Searching in a form is done in a similar way Task 5 4 Modify the database data odb 1 Open the database data odb from the subfolder 5 4 data of the folder 5 databases 2 Perform the menu command Places gt Search for Files in the top panel of the desktop 2 1 In the Name contains field of the search dialogue box enter the database filename dati 2 2 Click Find 2 3 Double left click on the file data odb that has been found 5 4 data dace Documents 5 databases data odb N dace Documents 5_databases 5 4_data i data_final odb dace Documents 5 databases 5 4 data final 3 Add new records to the table tbl_artists 3 1 In the Database pane select the object Table 3 2 In the Tables pane select the table tbl_ artists 3 3 Open the table in data view 3 3 1 Click the button Open Database Object in the Table toolbar O pen Database Object 3 4 Adjust the width of the Artist and Notes columns so that the entire content of the fields is visible 3 4 1 Double click on the dividing line to the right of the field s name aris or 3 4 1 Click on the dividing line 3 4 2 Drag right until the entire content of the fields is visible 3 5 Add records
43. d with the help of a wizard or in design view by manually setting the information selection criteria and determining the selection values Mathematical and logical operators negation and combinations thereof are used to set the criteria in queries e Equal e Smaller than lt e Greater than gt e Like equal to the specified text e Null empty field e And e Or Negation is created by adding the word Not Wildcard characters are used to substitute a part of text or number e asterisk or percent sign substitutes any number of characters e question mark or _ underscore substitutes any single character For example selection by the value end will return all data ending with the characters end For a query to be performed it is not necessary to include all the fields of a table and it is also possible to include fields from a different table Databases 38 Creating a Query with a Wizard The built in wizards of OpenOffice org allow creating a query quickly and easily A query is given a name and saved just like any other database object A query is usually created in such a way as to ensure that it can be reused After supplementing information in a database the query will include in its results the newly added records as well The wizard consists of eight steps To create a query by using a wizard In the database window select the object Query In the Tasks pane click on Use
44. e relationship which means that a table record can be linked with only a single field in another table This is denoted by 1 1 Such linkage in addition to its direct purpose also ensures integrity of information The application will not allow deleting a linked field while the link to a different table is active Forms In databases it is possible to use forms for the creation display and modification of records Data entry in a form is visually easier to understand than in a table and is simpler for the user Forms may contain fields for data entry field descriptions names action buttons menus pictures and design elements Databases 8 o course_database odb Table1 read only OpenOffice org Base Form Design File Edit View Insert Format Table Tools Window Help om ou ABC ABC A Zoom B Menus C Standard toolbar D Data field E Control toolbar F Drawing toolbar Image No 3 A Base form in data view displaying a record Note In data view many toolbar buttons and form design toolbars are inactive and greyed out as they are not applicable in this view 3 A B C D E F GH A Find record B Absolute record C Total number of records D Go to first record E Go to previous IQ Record 14 of 418 He gt E P i a me az J K record F Go to next record G Go to last record H Create a new record Delete record J Ref
45. er on the keyboard 6 5 Click the New Record button in the Form Controls toolbar New Record 6 6 Fill the fields of the record 6 7 Click the navigation button New Record 6 8 Repeat the steps for the other records 7 Make sure that the data entered in the form appear in the table tbl_ artists 35 Module 5 7 1 Switch to the table tbl_artists 7 1 1 Click the corresponding button of the table window in the bottom panel of the desktop 7 2 Click the Refresh button to refresh the table Xl k fear Founded Refresh 7 3 View the new records 8 Supplement record No 18 in the database 8 1 Switch to the form window by clicking the corresponding button in the bottom panel of the desktop 8 2 In the Record field enter record number 18 8 3 Press Enter on the keyboard 8 4 In the Country field enter USA 8 5 Press Enter on the keyboard 8 6 In the Date added field enter 03 23 11 8 7 Press Enter on the keyboard 9 Delete the record of the artist Falco 9 1 Find the record that contains the word Falco in the Artist field 9 1 1 Click the Find Record button in the form navigation bar 9 1 2 In the Text field of the search dialogue box enter Falco 9 1 3 Click Search 9 1 4 Close the search dialogue box by clicking Close 9 2 Click the Delete Record button in the form navigation bar oe X 9 3 Confirm the deletion of the record by clicking Yes in the dialogue box 10 Close the database 1
46. ew Fields in the Query Artist tbl_artists Artist Year Founded tbl_artists Year 5 Grouping Founded Country tbl_artists Country Notes tbl_artists Notes 6 Grouping conditions Sorting order Artist DESC 7 Aliases Search conditions Country like UK and Year founded is greater than 1970 0 INo Groups were assigned No grouping conditions were assigned Next gt Finish p Cancel 3 View the result of the query 3 1 Switch to the query window by clicking the corresponding button in the bottom panel of the desktop Help lt Back 41 Module 5 Arist Yearfounded County T St whitencke i Formed by David TET The Clash without late Freddy a Madness Dire Straits 197 JK leader Mark Knopfler Depeche Mode 198 JK Dave Gahan lead vocals Art OF Noise 3 2 Close the query window by clicking the Close button in the title bar 4 Open the newly created query in design view 4 1 Select the query qry_artists in the database pane Queries if necessary 4 2 Perform the menu command Edit gt Edit 5 View the selection criteria 6 Close the database saving the changes 6 1 Close the query window by clicking the Close button in the title bar 6 2 Close the database window 6 3 In the dialogue box confirm saving the changes by clicking Yes Note In Linux the letter case matters For example in selection criteria the value uk lowercase is not the same as UK
47. hat data entry data maintenance and information retrieval are carried out by users 59 Module 5 Gategory e Reh CTaskitem Sage Pf 7 Pf k administrator Error data that a database access Know for Bookmark specific provides to not defined appropriate users Know that the database administrator is responsible for recovery of a database after a crash or major errors 5 2 Using the 5 2 1 Working with 5 2 1 1 Open close a database application Application Databases Ld 5 2 1 2 Open close a database 5 2 1 3 Create a new database and save to a location on a drive PP e 5 2 3 Common 5 2 3 1 Tasks o r 5 3 Tables 5 3 1 Records 5 3 1 1 a ea E ae 5 3 2 Design 5321 5 2 3 2 wn N W pP 5 3 2 2 5 3 2 3 5 3 2 4 5 3 2 5 5 3 2 6 5 4 Retrieving 5 4 1 Main 5 4 1 1 Information Operations 5 4 1 2 5 4 1 3 5 4 2 1 5 4 2 2 5 4 2 3 5 4 2 4 Add delete records in a table 29 Add modify delete data in a record 28 Understand consequences of changing data types field properties in a table Set a field as a primary key Index a field with without duplicates allowed 5 3 2 7 Add a field to an existing table 5 3 2 8 Change width of columns in a table Apply a filter to a table form Remove the application of a filter from Understand that a query is used to extract Create a named single table query using Create a named two table query us
48. he corresponding fields from the first record of the table provided in the task 18 5 Repeat the steps for the second record 19 Delete record 14 by using the form frm_collection 1 point 19 1 In the Record navigation field of the form frm_collection enter the number 14 iQ Record 14 of 36 19 2 Press Enter on the keyboard 19 3 Click the Delete Record button in the navigation bar d H d E ete Record 19 4 Confirm deleting the record by clicking Yes in the warning dialogue box 19 5 Close the form by clicking the Close button in the title bar of the form window Tip Make sure that the action was successful by activating the table tbl_albums Databases 70 36 Falco Best OF CD 1998 12 00 37 Madness Wonderful cD 1999 15 00 20 Create a report for the table tbl_ full Include the fields Artist Album Year 1 point 20 1 In the Database pane select object type Reports 20 2 In the Tasks pane click on Use Wizard to Create Report 20 3 In the Report Wizard dialogue box 20 3 1 Select the table tbl_ full in the menu Tables or queries Which fields do you want to have in your report Tables or queries able tbl_Full m as 20 3 2 Select fields in the report Available Fields Fields in report ID Artist Format Album Price Year Comments 20 3 3 Click Next 20 3 4 Click Next again until the fifth step of the wizard 20 3 5 In step five select Portrait orientation Orientation La
49. he keyboard 11 3 2 Press the Enter key on the keyboard SYS IDX 65 11 4 In the Fields pane select the Album field 11 5 Tick the Unique checkbox Index details SM Unique Fields index field ee eae J Ascending Album 11 6 Click the Close button in the Indexes dialogue box 11 7 In the dialogue box confirm saving the changes by clicking Yes 11 8 Close the table window 11 8 1 Click the Close button in the title bar 11 8 2 Confirm saving the changes by clicking Yes Tip You can check if the index has been added and duplicate records are prohibited by attempting to create a new record in the table tbl_albums and trying to enter in the Album field an album name that already exists in the table 12 In the table tbl_albums set the width of the Artist column to 1 2 inches 3 cm 1 point Databases 66 12 1 Open the table tbl_albums in data mode 12 1 1 In the Tables pane select the table tbl_ albums 12 1 2 Press the Enter key on the keyboard 12 2 Perform a right click on the name of the Artist column 12 3 In the right click menu select the command Column Width i Column Format in Column Width in k a Hide Column SC Se peg 12 4 In the Width field of the Column Width dialogue box enter 1 2 3 12 5 Click OK 12 6 Close the table by clicking the Close button in the title bar of the column window Tip OpenOffice org Base does not provide the possibility
50. he keyboard or 1 1 Click inside any field of the record 1 2 Perform the menu command Edit gt Delete Record E view Insert Toc it Restore Cut Ctrl X Copy Ctrl C Paste Ctrl V Delete Record v Edit Data 2 Confirm the deletion in the dialogue box by clicking Yes To delete a record in a form Open a form in data view Go to the necessary record 2 1 Use the navigation buttons or 2 1 Enter the number of the record in the Record field Databases 30 2 2 Confirm the entered number by pressing the Enter key 3 Click the Delete Record button in the control toolbar Mage No 4 Confirm the deletion in the dialogue box by clicking Yes OpenOffice org 3 2 f You intend to delete 1 record IF you click Yes you won t be able to undo this operation Do you want to continue anyway The deletion of individual data from a database is performed in a table or form by deleting values in the selected field To delete data in a record field 1 Select a field value 2 Press Delete on the keyboard Note The deletion of a record or individual data is irreversible Field Index Management Field indexing makes finding records faster To manage index fields 1 Open a table in design view 2 Perform the command Tools Index Design Indexes index details G Fields SYS IDX_56 index field ID Ascending H C D E A New index B Delete C Rename D
51. he size of the paper sheet in the printer e Orientation horizontal Landscape or vertical Portrait orientation of the page e Margins the size of the page margins in units of measurement by default inches Page Style Default Organizer Page Background Header Footer Borders Columns Footnote Paper format Format Width 8 50 m Height 11 00 m Orientation Portrait Landscape Paper tray From printer settings gt Margins Layout settings Left 0 79 Page layout Right and left m Right 0 79 Format a Top 0 79 Register true Bottom 0 79 a Reference Style heen Cancel Help Reset Task 5 9 Prepare for printing the results of the query qry artists and the table tbl_artists of the database print odb 1 Open the database print odb from the subfolder 5 9 print of the folder 5 databases 1 1 Perform the menu command Places gt Search for Files in the top panel of the desktop Databases 54 1 2 In the Name contains field of the dialogue box enter the database filename print 1 3 Click Find 1 4 Double left click on the file print odb that has been found 2 Copy the database table tbl_artists into a new Writer document 2 1 In the Database pane select the object Tables 2 2 In the Tables pane right click on the table tbl_ artists 2 3 In the menu choose the Copy command Eamtbl_artists Delete Rename 2 4 In the database window perform the menu command File gt New gt Text Document
52. ick OK 7 Close the table window 7 1 Click the Close button in the title bar of the table window 8 Close the database saving the changes 8 1 Perform the menu command File gt Exit 8 2 Confirm the changes by clicking Yes in the dialogue box Actions in a Database Opening a Database File Naturally to work with a database it must first be opened As with other actions on the computer there are several ways to open an existing database To open a file from the Base environment 1 Perform the menu command File gt Open In the Open dialogue box select a file in the default folder or find a file in a different location 3 Complete the action by clicking Open To open a file from the Documents folder 1 Open the Documents folder by performing the operating system menu command Places gt Documents Places System amp 4 Home Folder Hi Desktop jag null P Documents h Open home dace Documents 2 Double left click on the icon of the database file To open a file by using search Perform the operating system menu command Places gt Search for Files In the Name contains field of the Search for Files dialogue box enter the full or partial name of the file 3 Click Find In the list of results double left click on the necessary file Databases 20 To open a Base file by using a wizard 1 Open the application with the menu command Applications gt Office gt OpenOffice org Database
53. ields are added to a table in the form of vertical columns and it is possible to set the field type and other properties Field type determines the type of data to be entered e g number text date checkbox Each field type also has additional properties such as the amount of characters in a number format length of text mandatory optional filling of the field The properties are changed in the Field Properties pane Field types depend on the database mechanism used Frequently used field types in OpenOffice org Base e Text with a fixed amount of characters Text fix the field saves the amount of characters set by the user e Text with a maximum amount of characters Text the default maximum amount of characters is 100 e Text with a maximum amount of characters Memo similar to Text but the default maximum amount of characters is 2147483647 e Yes No a checkbox is displayed in the field The user can tick it with a mouse Click e Number the field must contain a number otherwise the application will display an error e Integer a number to which automatic actions can be applied such as automatic numbering e Date month day and year information in a specific format To add a field to a table Open a table in design mode Enter the Field Name in the respective column Choose the Field Type in the respective column ak ae ne oe Set the Field properties in the respective pane Task 5 2
54. in the title bar of the report window 5 Open the newly created report in design view 7 10 Databases 5 1 Select the report in the Reports pane 5 2 Click the Edit button in the toolbar lew AISLE Edit Insert text in the header of the report 6 1 Click inside the header after the word Title 6 2 Enter Albums in collection Albums in collection Author Dace Date 2 13 11 Ut wisi enim ad minim veniam quis nostrud exerci tation Ut wisi enim ad Ut wisi enim ad 9876 5 Ut wisi enim ad Ut wisi enim ad minim veniam minim veniam minim veniam minim veniam Change the page format to A4 7 1 Perform the menu command Format gt Page 7 2 Open the Page tab 7 3 In the Paper Format menu of the Page Style dialogue box select A4 Organizer Page Background Header Foot Paper format Format X 7 4 Open the Organizer tab of the Page Style dialogue box 48 7 5 In the Next Style menu select First Page to apply the changes to the following pages as well Name First Page Next Style 7 6 Close the dialogue box by clicking OK Delete the Format field in the report 8 1 Click on the Format field 8 2 Click the Delete Column button in the table toolbar A oe Delete column Switch the fields Artist and Album 9 1 Select the text Artist 9 2 Perform the menu command Edit gt Cut 9 3 Click after the Album field 9 4 Perform the menu command Edit gt Paste 9 5 Select the text Album
55. ine edt ab al 3 5 Click Next 3 6 Click Next in the next step 3 7 Select the arrangement Columnar Labels Left Arrangement of the main Form ipi i i Tall Tatil i Columnar Labels Left 3 8 Click Next 3 9 In the next step make sure that the form can be used to view and enter all data The form is to display all data must be selected 3 10 Click Next 3 11 Select form style Red in the Apply styles pane 3 12 Select Field border 3D look 3 13 Click Next 3 14 In the field Name of the form enter frm_CD Collection 3 15 Click Finish Create a record in the database 4 1 In the Artist field of the form enter Joe Cocker 4 2 In the Format field enter CD 4 3 In the ReleaseYear field enter 2010 Close the form window 5 1 Click the Close button in the title bar of the form window 5 2 In the dialogue box confirm saving the entered data by clicking Yes Check the records in the database table 6 1 In the Database pane select the object Tables 6 2 In the Tables pane double left click on the table tbl_CD Collection 6 3 Increase the width of the Artist field 6 3 1 Right click on the name of the Artist field 6 3 2 In the right click menu choose the command Column Width Format R Column Format Ls Column Width Hide Column 6 3 3 In the Column Width dialogue box set the column width to 3 cm 19 Module 5 column Width Automatic Cancel Help 6 3 4 Cl
56. ing 4 Display hide built in toolbars Restore 56 minimize the ribbon Use available Help functions 55 Open save and close a table query form report Switch between view modes in a table 21 query form report Delete a table query form report Navigate between records in a table query form Sort records in a table form query output in N 21 31 ascending descending numeric alphabetic order Create and name a table and specify fields with their data types like text number date time yes no N Apply field property settings field size number format date time format default value N N N Not applicable Create a validation rule for number date time currency to Base Use the search command for a specific 22 24 30 22 18 34 word number date in a field 36 37 37 38 1 Add criteria to a query using one or 37 more of the following operators Equal lt gt Not equal to lt Less than lt Less than or equal to gt Greater than gt Greater than or equal Databases Add criteria to a query using one or Category v 9 0a M OD 5 4 2 5 WwW N more of the following logical operators AND OR NOT po 5 4 2 6 Use a wildcard in a query or or WwW N 5 4 2 7 Edit a query add modify remove criteria 42 5 4 2 8 Edit a query add remove move hide 42 unhide fields 5 5 1 Forms 5 5 1 1 Unders
57. ional registries banks patient registration in hospitals Some examples of databases e MySQL e PostgreSQL e Oracle e DB2 e SQL Server These databases are essentially a mechanism of information storage and processing For the user to be able to easily use a database a database management system is employed a programme or group of programmes that are able to modify delete add and retrieve information create database objects and perform other actions in the 5 Module 5 database Large branching databases for professional use are normally developed by specialists Users work with the visible side of a database by entering data in forms and receiving back processed results For example when purchasing an airline ticket online e The user fills a form enters the required mandatory and optional data and confirms it e The database system performs information processing selection ticket reservation e The database system executes changes in other related systems at the airport and the airline s registration system e The user receives a boarding pass or other confirmation In this case the user does not have to understand the entire database system The maintenance technical implementation and integration of databases are ensured by specialists Administrators of the service using the data system are able to establish the user s access level to the database The OpenOffice org suite includes database management
58. le tbl_media 5 1 3 Click the button Open Database Object in the toolbar Databases 64 Open Database Obj ect 5 2 Click inside the Description field of the first record 5 3 Enter text as specified in the data table of the task 5 4 Click inside the Date added field 5 5 Enter the date added observing the format 5 6 Repeat the steps for the other records 6 To continue adjust the width of the Description field to make its entire content visible 1 point 6 1 Position the mouse pointer between the field names Description and Date added 6 2 Perform a Genie left click eda eseiption ae ae Oo Led Zeppeli mera LP Jun 13 01 H My experiments Studio tape 8 tracks Dec 11 09 F Mike Oldfield s first sessions recordings Jan 23 11 lt AutoField gt 6 3 Close the table by clicking the Close button in the title bar of the table window 7 Add a mandatory field to the existing table tbl_artists 1 point Data type Field properties 7 1 If necessary select the database object Tables in the Database pane 7 2 1 n the Tables pane select the table tbl_artists 7 3 Click the Edit button in the toolbar 7 4 Enter the field name Country in a new Field Name row of the table 7 5 In the Length field of the Field Properties pane enter 15 replacing the default value 7 6 Save the changes by clicking the Save button in the toolbar 7 7 Close the table window by clicking the Close button in the title bar 8 In
59. lication Help 55 Displaying and Hiding Toolbars 56 MODULE 5 DATABASES In this module you will learn to e Understand how databases work e Work in OpenOffice org Base e Create database objects e Modify database objects e Retrieve information from a database e Select information according to set criteria e Print out data Introduction to Databases Imagine that you have a large collection of music records with more than 10 000 units On top of that albums are sometimes loaned to friends leased out or loaned to an archive How would you go about organizing and managing this collection Countless unorganized notes probably will not help and sooner or later everything will be a disarray of unrelated facts such as the artist band s founding year lists of guest appearances on albums or the name of the person who has borrowed the album On the other hand when data are arranged in a specific system a database they start becoming useful information Information in a database is interlinked rather than being simply individual pieces of data Databases are a powerful tool for information processing They store information allow updating and changing it retrieve specific selected data Database engines vary some verify and maintain data integrity or allow sharing others provide different possibilities Databases are used for processing large volumes of interrelated information such as in ticket booking nat
60. lumn of the Criterion Or field delete the text LP 16 6 Close the query dialogue box by clicking the Close button in the title bar 16 7 Confirm the changes by clicking Yes 17 Create a simple form for input output of data of the database table tbl_groups 1 point 17 1 In the Database pane select the database object type Forms 17 2 In the Tasks pane click on Use Wizard to Create Form 17 3 In the pane Select the fields of Your form of the Form Wizard dialogue box select the table tbl_ groups in the menu Tables or queries Tables or queries Table tbl_groups 17 4 Add all fields 17 4 1 Click the button to add all table fields gt gt 69 Module 5 17 5 Click Next in the Form Wizard dialogue box 17 6 Click Next again 17 7 Choose the form arrangement In Blocks Labels Above Arrangement of the main form In Blocks Labels Above 17 8 Click Next in the Form Wizard dialogue box 17 9 Click Next again 17 10 Click Next 17 11 In step eight Set the name of the form enter the name frm_groups in the field Name of the form 17 12 Click Finish 17 13 Close the form window by clicking Close in the title bar 18 a a the music collection by using the form frm_collection 1 pono 18 1 Select the form N in the Forms pane 18 2 Press the Enter key on the keyboard 18 3 Click the New Record button in the navigation bar of the form gt H New Record 18 4 Enter data in t
61. n the Form design window under the dialogue box OOG Untitled 1 OpenOffice org Base Form Design i File Edit V Form Wizard m Steps Apply the style of your form _Defau 14 Field selection Apply styles Field border Beige 2 Set up a subform Bright Blue CO No border 3 Add subform fields Light Gray 3D look Dark Se 4 Get joined fields Orange ra Ice Blue 5 Arrange controls Grey Water 6 Set data entry Red Violet 7 Apply styles 8 Set name Hep l lt Back Next gt Binish Cancel o Y Gein 4 W ii 100 ral SiR S464 Omot ib ET eivmena Page 1 1 Default INsRT STD 14 Finish the creation of the form 14 1 Enter the name of the form in the field Set the name of the form 14 2 Make sure that Work with the form has been selected 14 3 Click Finish Databases 16 Form Wizard Steps Setthe name ofthe form 1 Field selection Name of the form frm _Libra 2 Set up a subform pE ry 3 Add subform fields How do you want to proceed after creating the Form 4 Get joined fields J work with the form 5 Arrange controls O Modify the Form 6 Set data entry 7 Apply styles 8 5eC name music_base odb frm_Library read only OpenOffice org Base Form Design File Edit View Insert Format Table Tools Window Help t Se ABJ SF y A N gt IQ Record
62. ndscape Porigit 20 3 6 Click Next 20 3 7 In the field Title of report enter the name rpt_albums 20 3 8 Click Finish 20 4 Close the report window by clicking the Close button in the title bar 21 Create a report for the table tbl_ groups including in the report all fields of the table and additionally grouping by country 1 point 21 1 In the Tasks pane click on Use Wizard to Create Report 21 2 In the Report Wizard dialogue box 21 2 1 Select the table tbl_ groups in the menu Tables or queries 21 2 2 Select all fields in the report Which Fields do you want to have in your report Tables or queries Table tbl_groups Available fields Fields in report Artist Year Founded gt Country Active BS Date added i Notes 21 2 3 Click Next 21 2 4 Click Next 71 22 Module 5 21 2 5 In step three Grouping select the Country field Do you want to add grouping levels Fields Groupings Artist Country Year founded Active Date added Notes 21 2 6 Click Next 21 2 7 Click Next again 21 2 8 In the Layout of data pane choose layout type Outline Elegant 21 2 9 Choose Portrait orientation 21 2 10 Click Next 21 2 11 In the Title of report field enter the name rpt_grouped 21 2 12 Click Finish 21 3 Close the report window by clicking the Close button in the title bar Delete a database object the report rpt_test 1 point 22 1 Select the report rpt_test in the Reports pane 22
63. ng samples and modify them as needed To create a new database by using a wizard and the default settings Create a new database 1 Open the application Base 2 10 Databases w G33 FriFeb 4 11 17 Applications Places System Q Accessories eP Games PE Graphics S Internet Fl Office W Sound amp Video p mal Dictionary Evolution Mail and Calendar OpenOffice org Database x Ubuntu Software Center oe OpenOffice org Presentation Manage databases create queries and reports to track and manage your information OO f OpenOffice org Spreadsheet Perform the menu command Applications gt Office gt OpenOffice org Database Note The application OpenOffice org Base may not be available on the computer by default If that is the case it must first be installed Installation of software is described in Module 2 of the ECDL study material 5 6 In the Database Wizard make sure that Create a new database is selected and click Next In the next step click Finish Database Wizard Steps Decide how to proceed after saving the database 1 Select database Do you want the wizard to register the database in OpenOffice org 2 Save and proceed Yes register the database for me No do not register the database After the database file has been saved what do you want to do Open the database for editing Create tables using the
64. om my collection 5 Format the text to make it red with font size 16 5 1 Select the header text 5 2 Click the toolbar button Font Color 5 3 Choose Light red b S E Font color Automatic ___ EEREEEES E nm LE 5 4 In the Size menu choose font size 16 i a TN 6 Centre the header text 6 1 Click the Centered button in the Formatting toolbar 7 Remove the background fill of the form 7 1 Perform the menu command Format gt Page 7 2 Open the Background tab by clicking on it 7 3 Set the Background color to No fill Background color E K E OOO A 8 Remove the footer from the form 8 1 In the Page Style dialogue box open the Footer tab by clicking on it 8 2 Remove the tick from the Footer On checkbox Databases 28 Footer 8 3 Click OK in the dialogue box 9 Close the form 9 1 Click the Close button in the title bar of the form window 9 2 In the dialogue box click Save 10 Delete the form frm_CD Collection 10 1 Select the form frm_CD Collection in the Forms pane 10 2 Press the Delete key on the keyboard 10 3 In the dialogue box confirm the deletion of the database object by clicking Delete Confirm Delete Entry Frm_CD Collection Are you sure you want to delete the selected data Delete All Do Not Delete Cancel 11 Close the database 11 1 Click the Close button in the title bar of the database window 11 2 In the dialogue box click
65. ormat code MMM D YYYY aiana Cancel Help Reset 7 4 Confirm the selection of the format by clicking OK 8 Move the table field Active to the end 8 1 Left click on the selection cell of the field Text VARCHAR Yes No BOOLEAN h ate added Date DATE 25 10 11 Module 5 8 2 Perform the menu command Edit gt Cut 8 3 Left click on the selection cell of an empty field at the end of the table 8 4 Perform the menu command Edit gt Paste Make sure that the field ID is set as the primary key of the table records 9 1 Right click on the selection cell of the ID field 9 2 Make sure that there is a tick in the menu at Primary Key FieldName Field Type Date DATE Cut t VARCHAR Copy nber NUMERIC Delete t VARCHAR Insert Rows No BOOLEAN e DATE Primary Key NULES Tet VARCHAR Save the table in the database 10 1 Click the Save button in the toolbar or 10 2 Perform the menu command File gt Save in the table window 10 3 In the Save As dialogue box enter the table name tbl_ artists 10 4 Confirm by clicking OK Close the table 11 1 Click the Close button in the title bar of the table window Useful tip If no primary key has been defined for the table a dialogue box called No primary key appears B OpenOffice org Base No primary key A unique index or primary key is required for data record identification in this da
66. ousands s Format code MMM D YY 3 12 Click OK in the dialogue box 63 Module 5 FieldName Field Type Media ID Integer INTEGER Description Text VARCHAR Dateadded Date DATE i Entry required No Default value Format example Jan 1 00 4 To continue set the field Media ID as the primary key 1 point 4 1 Check if Base has automatically set the field Media ID as the primary key Fietdnieme Ftd ype 3 Jmedia ii ID Integer INTEGER Description Text VARCHAR p Dateadded Date DATE i 4 2 Set the primary key if necessary 4 2 1 Perform a right click on the selection cell of the field Media ID 4 2 2 In the right click menu choose Primary Key FieldName Field Type edi Integer INTEGER xt VARCHAR Copy ite DATE Delete Insert Rows Primary Key 4 3 Save the table 4 3 1 Click the Save button in the toolbar of the table window 4 3 2 In the Table Name field of the Save As dialogue box enter tbl_ media 4 3 3 Click OK 4 4 Close the table 4 4 1 Click the Close button in the title bar of the table window 5 Add records to the table tbl_ media 1 point a 0 Led Zeppelin bootleg LP Jun 13 01 1 My experiments Studio Dec 11 09 ee 2 Mike Oldfield s first Jan 23 11 5 1 Open the table tbl_media for data entry 5 1 1 In the Database pane select the object type Tables 5 1 2 In the Tables pane select the tab
67. ox To export in Adobe Acrobat format perform the command File gt Export as PDF Note When saving form data in a different format the database record saved will be the one that is opened in the form Printing Reports Printing data in Base is easiest in the form of a report As a report can include all table data and fields only specific fields or selected results this approach will ensure the best results Query results are also best printed as a report To print a report for a table or query 1 Perform the command File gt Print Set the additional options in the Print dialogue box 2 1 Select the printer 2 2 Select All or specific Pages Databases 52 2 3 Set the number of copies 3 Click Print Print Printer Name Kyocera Mita KM 2550 s Properties Status Default printer LCL C Y C Type CUPS Kyocera Mita KM 2550 Location kuochera Comment Kyocera Mita KM 2550 Print to file Print range Copies D All pages Number of copies Pages 1 gE 1513 Collate Selection es g Col Options PS Cancel Help A B A Optional settings B Selecting the print range C Selecting the printer D Number of copies Image No 13 Printing a report Forms OpenOffice org Base allows printing database information by using form layout Useful tip Before printing a form choose print layout view Then the form will appear the same in the display as when printed out on paper
68. rds successively by three conditions in tables and queries To use the standard filter 1 Open a database table query in data view 2 Click the Standard Filter button 3 Inthe Standard Filter dialogue box 3 1 Select a table field for the first criterion 3 2 Choose a condition 3 3 Enter the values to search for 3 4 Create the next condition 3 5 Confirm by clicking OK E Standard Filter Criteria Operator Field name Condition Year Founded i Cancel J z Help none B C A Logical operator for connecting conditions B Field C Condition D Field value Image No 12 Creating a filter 37 Module 5 For example the filter shown in the picture selects records that contain the value 1965 in the Year founded field and the text UK in the Country field Ei 5 A O ae en RAT F p D at Year founded County Date added lo Pink Floyd 1965 UK _ 01 02 11 Wright anc lt Autok iw To remove a filter Click the Remove Filter Sort button D in Image No 11 Note Text in filters and queries see below when specifying the values to search for is placed inside apostrophes e g UK Creating Queries A query selects records in a database according to requirements set by the user The result of a query can be set to include either all the fields of database table records or only specified fields As with the other database objects queries can be create
69. resh K Sort records Image No 4 Form Navigation toolbar Queries The purpose of a database is not only to store and accumulate information but also to ensure information retrieval according to specific criteria Queries are used for this purpose Once created a query can be saved and later reused A query can be used for selection of information in one or several tables Mathematical and logical actions and wildcards are used to establish criteria The result of a query can be displayed in data table form and report views it is possible to specify the table fields to be included The result obtained from a query can be displayed in a form 9 Module 5 Reports Reports are usually used to print out information with a particular design Reports are created using query or table data Title Albums in collection Author Dace Date 2 13 11 Cream Album Label On shelf Notes Goodbye Fresh Cream Reaction Records scratched Days of Twang Natural fake Daily Lama Album On shelf Notes Original Sin different singers Page 1 4 Image No 5 A report page prepared for printing in landscape orientation The layout of reports can be modified it is possible to add pictures and other design features Creating a Database Creating Databases and Objects with a Wizard There are built in templates in OpenOffice org Base that make the creation of a new database easier The user can select database tables from existi
70. s 2 3 1 Select the table tbl_ albums 2 3 2 Click the Add button 2 3 3 Repeat the actions for the table tbl_ artists 2 3 4 Click the Close button to close the dialogue box o albums odb Query OpenOffice org Base Query Design File Edit View Insert Tools Window Help S a A a0 2 8 piery tbl_albums tbl_artists 8 ID Artist Album Format xt Artist gt Year found Country Active Visible Function Criterion 2 4 In the Field menu select the table fields to be included in the query 2 4 1 From the table tbl_albums the fields Album Format Year 2 4 2 From the table tbl_artists the fields Artist Country 2 5 In the Country column remove the tick from the Visible checkbox Field Album Format Year Artist N Alias Table tbl_artists Sort Visible E 4 Mv Mw a Useful tip The selected table can be changed in the menu of the Table field 2 6 Create the data selection criteria In the Criterion field enter 2 6 1 In the Year column enter gt 1979 albums released in or after 1979 2 6 2 In the Artists column enter UK uppercase bands from the United Kingdom 43 Module 5 Field Album Format ee lartist Country Alias Table tbl_albums tbl_albums tbl_albums tbl_artists tbl_artists Soret Visible amp amp Mi Mi Function Note OpenOffice org Base will change the entered criteria to suit the syntax of the programme a space will
71. ssing Tab on the keyboard 1 4 Press Enter on the keyboard 1 5 Double left click to open the file report odb 2 Create a report as specified in the task 2 1 In the Database pane select the object Reports 2 2 In the Tasks pane click on Use Wizard to Create Report 2 3 In step one of the wizard 2 3 1 In the Tables or queries menu select the database table tbl_ albums 2 3 2 Include all fields in the report Tables or queries Table tbl_albums bd Available fields Fields in report ID Artist Album Format Year N Label On shelf Notes 2 3 3 Click Next 2 4 Step two allows changing the names of the fields in the report Leave the default names 2 4 1 Click Next 2 5 Click Next to accept the default grouping settings 2 6 In step four Sort options 2 6 1 In the Sort by menu select the Artist field 2 6 2 Make sure that sorting in Ascending order has been chosen 2 6 3 Click Next 2 7 Choose the layout in step five 2 7 1 In the Layout of data pane select the style Align left Modern 2 7 2 Make sure that Landscape orientation has been chosen 2 7 3 Click Next 2 8 Finish the creation of the report 2 8 1 In the field Title of report enter the name rpt_albums 2 8 2 Make sure that dynamic refreshing of content Dynamic report has been selected 2 8 3 Click Finish 3 View a print preview of the report 3 1 Perform the menu command File gt Page Preview 4 Close the report by clicking the Close button
72. tabase You can only enter data into this table when one of these two structural conditions has been met Should a primary key be created now Upon clicking Yes a primary key field called ID is automatically added to the table If No is chosen a primary key is not created 12 Rename the table CD Collection 12 1 Select the table CD Collection in the Tables pane 12 2 Perform the menu command Edit gt Rename 12 3 In the Table Name field of the Rename to dialogue box change the table name to tbl_ CD Collection 12 4 Click OK 13 Close the database music_cd odb 13 1 Click on the window close button in the title bar F D o music_cd Final odb OpenOffice org Base Fil Edit View Insert Tools Window Help 13 2 Confirm the changes by clicking Save in the dialogue box Databases 26 or 13 1 Perform the menu command File gt Save 13 2 Close the application window by clicking the Close button Modifying a Form in Design View Existing database forms can be supplemented with pictures graphical objects a header and footer creating a form in design view is not examined in this material Opening a form in design view activates the toolbars Form Design and Form Controls with form elements Useful tip A form is displayed in web layout view by default It is easier to edit a form in print layout view To add a header to a form Open the selected form in design view Perform the menu command View
73. tand that a form is used to display and maintain records 5 5 1 4 Use a form to delete records 2 po oe 5 5 1 5 Use a form to add modify delete data 30 a in a record 5 5 1 6 Add modify text in headers footers in 26 a 5 6 1 1 20 7 13 9 9 5 6 1 Reports Data Export Understand that a report is used to print Error selected information from a table or query Bookmark not defined 44 48 5 6 1 2 Create and name a report based on a table 5 6 1 3 Change arrangement of data fields and 5 6 1 4 Present specific fields in a grouped report by Not sum minimum maximum average count applicable at appropriate break points to Base 5 6 1 5 Add modify text in headers footers in a report 5 6 1 6 Export a table query output in spreadsheet ms o0 P O ion WwW text txt csv XML format to a location on a drive 5 6 2 Printing 5 6 2 1 Change the orientation portrait landscape of a table form query output report Change paper size 5 6 2 2 Print a page selected record s complete 53 oe 5 6 2 3 Print all records using form layout specific 52 a pages using form layout 5 6 2 5 Print specific page s in a report print 52 61 Module 5 Task 5 10 Test your knowledge by completing the assignment Notes The assignment includes 25 tasks each of which is evaluated with points The work must be completed within 45 minutes Although there are instructions provided
74. tch all of the Following 2 Sorting order Match any of the Following 3 Search conditions Fields Condition Value 4 Detail or summary tbl_artists Country i like m UK 5 Grouping 6 Grouping conditions Fields Condition Value 7 Aliases tbl_artists Year founde is greater than gt 1970 8 Overview Fields Condition Value z is equal to Sa Finish Cancel Databases 40 2 5 8 Click Next 2 6 In step four it is possible to perform additional arithmetical operations in the query fields 2 7 Step seven allows assigning alternative names to the fields to be included in the results by entering them in the corresponding Alias fields Query Wizard _ ma i Steps Assign aliases if desired 1 Field selection Field Alias 2 Sorting order tbl_artists Artist Artist 3 Search conditions tbl_artists Year Founded Year Founded 4 Detail or summary tbl_artists Country Country 5 Grouping ini 7 tbl_artists Notes Notes 6 Grouping conditions 8 Overview Help lt Back Next gt Finish Cancel 2 8 Step eight overview 2 8 1 In the Name of the query field enter qry_artists 2 8 2 Click Finish e Query Wizard Steps Check the overview and decide how to proceed 1 Field selection Name of the query How do you want to proceed after fary artists creating the query 2 Sorting order ae Display Query 3 Search conditions Modify Query 4 Detail or summary Overvi
75. the database table tbl_albums set the Artist field as mandatory 1 point 8 1 If necessary select the database object Tables in the Database pane 8 2 In the Tables pane select the table tbl_ albums 8 3 Click the Edit button in the toolbar 8 4 Click on the name of the Artist field 8 5 In the Entry required menu of the Field properties pane choose Yes 9 Delete the Comments field 1 point 9 1 Perform a right click on the selection cell of the Comments field 9 2 In the right click menu choose Delete 10 Change the currency symbol of the Price field to 1 point 10 1 Click on the name of the Price field 10 2 In the Field Properties pane click the browse button of the Format example field 65 Module 5 10 3 In the Format menu of the Field Format dialogue box select EUR English Eire Category Format All SER EUR English Eire User defined 1 234 2 _ 1 234 00 Percent 1234 Date 1 234 Time 1 234 00 EUR Scientific 1 234 00 EUR 10 4 Make sure that you have made the right selection in the Format example field Format example 0 00 n 10 5 Click OK in the dialogue box 11 Set up indexing that does not allow repeat records for the Album field 1 point 11 1 Perform the table window menu command Tools Index Design 11 2 In the Indexes dialogue box click the button New Index Indexes 11 3 Change the default index record to album 11 3 1 Enter the word album using t
76. to move data table fields It can be done directly with the database commands If a different arrangement of database table fields is needed it is possible to create a view for a table or tables A view does not change the actual order of table fields 13 Create a view for the table tbl_artists where the Active field is immediately after the Artist field 1 point 13 1 In the Database pane select the database object Tables if necessary 13 2 In the Tasks pane click on the task Create View 13 3 In the Add Tables dialogue box select the table tbl_artists 13 4 Click Add 13 5 Click Close 13 6 In the Field row of the table set the fields to be displayed in the order specified in the task Field Artist Year founded Country Alias tbl_artists Artist Table tbl_artists Year founded tbl artists tbl artists Country k 13 7 Click the Save button in the toolbar 13 8 In the Table name field of the Save As dialogue box enter the view name custom_view 13 9 Click OK 13 10 Click the Close button in the title bar of the window 14 Create a query called qry_after_81 that will select from the table tbl_albums all albums released after 1981 1 point 14 1 In the Database pane select the object type Queries 14 2 In the Tasks pane click on the task Create Query in Design View 14 3 In the dialogue box Add Table or Query select the table tbl_albums 14 4 Click Add 14 5 Click Close 6
77. y supports some flat file amp Common Help Topics database formats such as the dBASE format You can also use OpenOffice org a ADatabase Functionality Base to connect to external relational databases such as databases from Formulas W MySQL or Oracle Spreadsheets The following database types are read only types in OpenOffice org Base p From within OpenOffice org Base it is not possible to change the database Charts and Diagrams structure or to edit insert and delete database records for these database types Spreadsheet files Text files Address book data Using a Database in OpenOffice org To create a new database file choose File New Database The Database Wizard helps you to create a database file and to register a new database within OpenOffice org as well as a link to the database where the records are stored SA The database file contains queries reports and forms for the database Formatting information is also stored in the database file To open a database file choose File Open In the File type list box select to view only Database documents Select a database document and click Open Related Topics Viewing a Database Contents The table of contents of the OpenOffice org help system Index Searchable index of topics Find Topic search feature Bookmarks User created bookmarks Image No 13 The dialogue box of the Help feature The help system of Base opens by default

Using Databases – OpenOffice.org Base

Contents

Download Pdf Manuals

Related Search

Related Contents

Using Databases &ndash; OpenOffice.org Base

Contents

Download Pdf Manuals

Related Search

Related Contents

Using Databases – OpenOffice.org Base