Course Code:05.03
Course Subject:Advanced Database
Course Title:ECDL Advanced (2v0) Database (3-Day)
Aims:On completion, the candidate will understand key database concepts and use a relational database application to create an advanced database structure and outputs.
Introduction:The candidate shall be able to: Understand key concepts of database development and usage; Create a relational database using advanced table creation features and complex relationships between tables; Design and use queries to create a table, update, delete and append data. Refine queries using wildcards, parameters and calculations; Use controls and subforms to enhance forms and improve functionality; Create report controls to perform calculations. Create subreports and enhance report presentation; Enhance productivity by working with macros and use linking and importing features to integrate data..
  

Objectives

The learner will be able to:

Assessment Criteria

The learner has achieved this objective because he/she can:

1. Understand Database Development and Use

  1. Know that different types of database models exist like: hierarchical, relational, object-oriented.

  2. Know the life cycle stages of a database: logical design, database creation, data entry, data maintenance, information retrieval.

  3. Recognize common business applications of databases like: dynamic website, customer relationship management systems, enterprise resource planning systems, website content management systems.

  4. Know the term SQL (structured query language) and understand its main use in querying database content.

2. Manage Tables and Relationships (Fields/Columns)

  1. Create, modify, delete a lookup in a field/column.

  2. Create, modify, delete an input mask in a field/column.

  3. Set data entry for a field/column: required, not required.

3. Manage Relationships and Joins

  1. Create, modify, delete a one-to-one, one-to-many relationship between tables.

  2. Create, modify a many-to-many relationship using a junction table.

  3. Apply referential integrity between tables.

  4. Apply automatic update of related fields.

  5. Apply automatic deletion of related records.

  6. Apply, modify an inner join, outer join.

  7. Create, modify a subtract join.

  8. Apply a self join.

4. Manage Queries

  1. Create, run a query to update data in a table.

  2. Create, run a query to append records to a table.

  3. Create, run a query to delete records in a table.

  4. Create, run a query to save selected data as a new table.

  5. Create, run a crosstab query.

  6. Create, run a query to show duplicated records within a table.

  7. Create, run a query to show unmatched records in related tables.

5. Refine Queries

  1. Create, modify, run a one, two variable parameter query.

  2. Use wildcards in a query: [ ], !, -, #.

  3. Show highest, lowest range of values in a query.

  4. Create and name a calculated field that performs arithmetic operations.

  5. Group information in a query using functions: sum, count, average, max, min.

6. Manage Form Controls

  1. Create, modify, delete bound controls: text box, combo box, list box, check box, option groups.

  2. Apply, remove bound control properties like: limit to list, distinct values.

  3. Create, modify, delete unbound controls containing arithmetic, logical expressions.

  4. Modify sequential tab order of controls on a form.

  5. Create, delete a linked subform.

7. Manage Report Controls

  1. Format arithmetic calculation controls in a report: percentage, currency, to a specific number of decimal places.

  2. Apply a running sum for a group, over all.

  3. Concatenate fields in a report.

8. Manage Report Presentation

  1. Insert, delete a data field in group, page, report headers and footers.

  2. Sort, group records in a report by field(s).

  3. Force page breaks for groups in a report

  4. Create, delete a linked subreport.

9. Enhance Productivity through Lining and Importing

  1. Link external data to a database: spreadsheet, text (.txt, .csv), existing database files.

  2. Import spreadsheet, text (.txt, .csv), XML, existing database files into a database.

10. Enhance Productivity through Automation

  1. Create a simple macro like: close an object and open another object, open and maximize an object, open and minimize an object, print and close an object.

  2. Assign/attach a macro to a command button, object, control.