ICT CLASS- DATABASE

 

 

KINANGO TECHNICAL & VOCATIONAL COLLEGE  

P O BOX 6-80120  

SAMBURU- MOMBASA  

  

 MANAGE DATABASE SYSTEMS(MS ACCESS)  

TRAINER:  MR MWASI  

EMAIL(assignments):teachermwasi8@gmail.com   

FOR MORE NOTES VISIT  

BLOG SITE: http://tmwasi.blogspot.com  

  

 

 

 

 

 

 

 

 

MANAGE DATABASE SYSTEMS (Ms Access)

 

UNIT CODE: IT/OS/ICT/CR/4/5 

 

UNIT DESCRIPTION

This unit covers the competencies required to carry out management of Basic databases systems. It involves identification of database concepts, designing of database, Creation and manipulation of database, database testing e.g. using dummy data, implementation of the designed database, establishing transaction and concurrency mechanism and managing database security.

 

ELEMENTS AND PERFORMANCE CRITERIA 

ELEMENT	PERFORMANCE CRITERIA (Bold and italicised terms are elaborated in the Range)
1. Identify database concepts	1.1 Database concepts are defined 1.2 Database models are identified 1.3 Identification of merits and demerits of database is done
2. Design Basic database	2.1 Database design concepts are identified 2.2 Appropriate database structures are determined 2.3 Database design is implemented 2.4 Database operations are performed
3. Create and manipulate database objects	3.1 Database objects are identified 3.2 Appropriate data Attributes are applied 3.3 Data relationships are established as per the tables created 3.4 Data is extracted from database using Access.
4. Perform database testing	4.1 Test data is prepared 4.2 Run the test data  4.3 Check the test results 4.4 Validate the results 4.5 Report the findings
ELEMENT	PERFORMANCE CRITERIA (Bold and italicised terms are elaborated in the Range)
5. Print Database Objects	5.1  Database tables are printed 5.2 Database queries are printed 5.3 Database forms and reports are printed

 

RANGE

Variable	Range May include but is not limited to:
1. Database Models	ü  Relational ü  Referential ü  Entity Integrity ü  Network ü  Star schema
2.  Database structures	Refers to a collection of record type and field type definitions that comprise your database: ü  Record Types. These define the type of entities or research objects you wish to capture (e.g. Person). ü  Fields. These are the properties or attributes that describe your record types (e.g. Gender, Age, Height etc.)
3. Database operations	ü  INSERT ü  SELECT ü  UPDATE ü  DELETE
4. data Attributes	ü  Atomic Attribute ü  Composite Attribute ü  Single Valued Attribute ü  Multi Valued Attribute ü  Stored Attribute ü  Derived Attribute ü  Null Valued Attribute

 

 

REQUIRED KNOWLEDGE AND UNDERSTANDING

The individual needs to demonstrate knowledge and understanding of: 

Database concepts

Database design concepts

Database objects

Procedures of printing database objects

 

 

 

 

 

 

 

 

 

 

FOUNDATION SKILLS

The individual needs to demonstrate the following foundation skills:

•      Communications (verbal and written); •      Proficient in ICT;  •      Time management; •      Analytical •      Faults troubleshooting; •      Problem solving; •      Planning;  •      Decision making; •      Report writing;

 

EVIDENCE GUIDE   

This provides advice on assessment and must be in conjunction with the performance criteria, required knowledge and understanding and range.

1. Critical Aspects of Competency	Assessment requires evidence that the candidate: 1.1 Identified database components 1.2 Performed Database operations  1.3 Applied Appropriate Data Attributes  1.4 Extracted data from database using Access
	1.5 Performed test data and validated the results 1.6 Performed printing of database objects
2. Resource Implications	The following resources must be provided: 1.      Computer 2.      Database software 3.      Printer 4.      Stationery
3. Methods of Assessment	Competency may be assessed through: 3.1 Oral questioning 3.2 Practical demonstration 3.3 Observation
4. Context of Assessment	Competency may be assessed individually in the actual workplace or through a simulated work place environment
5. Guidance information for assessment	Holistic assessment with other units relevant to the industry sector, workplace and job role is recommended.

 

  

 

 

 

 

 

 

 

 

 

 

 

 

1.1 Database Concepts are Defined

A database is an organized collection of data, generally stored and accessed electronically from a computer system. In Microsoft Access, a database is a single file that can contain multiple tables, queries, forms, reports, and other objects.

1.2 Database Models are Identified

Database models define the logical structure of a database. Common models include:

•       Hierarchical Model: Data is organized into a tree-like structure.

•       Network Model: Data is organized as a graph with nodes and edges.

•       Relational Model: Data is organized into tables (relations), which is the model used by Microsoft Access.

•       Object-Oriented Model: Data is represented as objects, similar to object-oriented programming.

1.3 Identification of Merits and Demerits of Database

Merits:

•       Data Integrity: Ensures data accuracy and consistency.

•       Data Security: Controls access to data.

•       Data Management: Facilitates data retrieval, updating, and deletion.

•       Data Sharing: Allows multiple users to access data simultaneously.         Data Redundancy Reduction: Minimizes duplicate data storage.

Demerits:

•       Complexity: Requires understanding of database concepts and design.

•       Cost: Can be expensive to set up and maintain.

•       Performance: Large databases can suffer from performance issues.

•       Security Risks: Sensitive data can be vulnerable if not properly secured.

1.4 Database Design Concepts are Identified

Database design involves creating a detailed data model of a database. Key concepts include:

•       Entity-Relationship (ER) Model: Diagrams representing entities and their relationships.

•       Normalization: Process of organizing data to reduce redundancy.

•       Primary Key: Unique identifier for each record in a table.

•       Foreign Key: Field in one table that uniquely identifies a row in another table.

•       Indexing: Improves the speed of data retrieval operations.

1.5 Appropriate Database Structures are Determined In Microsoft Access, appropriate database structures include:

•       Tables: Store data in rows and columns.

•       Queries: Retrieve and manipulate data.

•       Forms: User-friendly interfaces for data entry.

•       Reports: Formatted printouts of data.

•       Macros: Automate repetitive tasks.

•       Modules: Contain VBA (Visual Basic for Applications) code for advanced functions.

1.6 Database Design is Implemented

Implementation involves creating the database objects defined during design:

•       Tables: Define fields, data types, and relationships.

•       Queries: Create SQL queries to retrieve and manipulate data.

•       Forms: Design user interfaces for data entry and updates.

•       Reports: Design layouts for data presentation.

•       Macros/Modules: Write automation scripts and functions.

1.7 Database Operations are Performed Database operations include:

•       Data Entry: Adding new records.

•       Data Update: Modifying existing records.

•       Data Deletion: Removing records.

•       Data Retrieval: Querying data to produce results.

1.8 Database Objects are Identified

In Microsoft Access, the main database objects are:

•       Tables: Store raw data.

•       Queries: Ask questions of the data.

•       Forms: Provide a user interface.

•       Reports: Format and print data.

•       Macros: Automate tasks.

•       Modules: Advanced functions written in VBA.

 

1.9 Appropriate Data Attributes are Applied

Data attributes define the properties of data fields in tables, such as:

•       Data Type: Specifies the kind of data (e.g., Text, Number, Date/Time).

•       Field Size: Limits the size of data.

•       Default Value: Predefined value for new records.

•       Validation Rule: Ensures data meets specific criteria.

•       Required: Indicates whether a field must have a value.

1.10 Data Relationships are Established as per the Tables Created

Relationships between tables are created to link data logically:

•       One-to-One: Each record in one table corresponds to one record in another table.

•       One-to-Many: Each record in one table corresponds to multiple records in another table.

•       Many-to-Many: Records in one table correspond to multiple records in another table and vice versa, typically implemented with a junction table.

1.11 Data is Extracted from Database Using Access

Data extraction involves creating and running queries to retrieve specific information from the database tables. Access provides various query types:

•       Select Queries: Retrieve data from one or more tables.

•       Action Queries: Modify data (e.g., Update, Delete).

•       Parameter Queries: Prompt the user for input to use in the query.

•       Aggregate Queries: Perform calculations on data (e.g., SUM, AVG).

1.12 Test Data is Prepared

Preparing test data involves:

•       Creating Sample Records: Adding representative data to tables.

•       Setting Up Test Cases: Defining scenarios to verify database functionality.

•       Ensuring Data Variety: Including different data types and edge cases.

1.13 Run the Test Data

Running the test data involves:

•       Executing Queries: Testing data retrieval and manipulation.

•       Using Forms: Verifying data entry and updates.

•       Generating Reports: Checking data presentation and formatting.

•       Testing Macros/Modules: Ensuring automation scripts work correctly.

1.14 Check the Test Results

Checking test results involves:

•       Comparing Outcomes: Ensuring the database behaves as expected.

•       Identifying Errors: Spotting discrepancies and bugs.

•       Validating Data: Ensuring data integrity and accuracy.

1.15 Validate the Results

Validation ensures the database meets requirements:

•       Consistency Checks: Verifying data consistency across the database.

•       Functional Tests: Ensuring all functionalities work as intended.

•       Performance Tests: Checking database performance under load.

1.16 Report the Findings

Reporting findings involves:

•       Documenting Test Cases: Describing test scenarios and outcomes.

•       Highlighting Issues: Detailing any errors or bugs found.

•       Recommending Improvements: Suggesting fixes and enhancements.

1.17 Database Tables are Printed

Printing database tables involves:

•       Selecting Tables: Choosing which tables to print.

•       Formatting Data: Ensuring the print layout is clear and readable.

•       Printing: Using Access’s print features to produce hard copies.

1.18 Database Queries are Printed

Printing database queries involves:

•       Running Queries: Generating the desired data set.

•       Reviewing Results: Ensuring the query results are correct.

•       Printing Output: Formatting and printing the query results.

1.19 Database Forms and Reports are Printed

Printing forms and reports involves:

•       Designing Layouts: Creating print-friendly designs.

•       Previewing: Checking the layout before printing.

•       Printing: Using Access’s print features to produce the final output.

By following these steps and concepts, you can effectively design, implement, and manage a database using Microsoft Access, ensuring it meets the needs and requirements of its users.