Introduction to Microsoft access database. A database is a collection of information that is organized in a central location so that it can be accessed, managed and updated.
Databases are a collection of tables with data stored in rows and columns. A good database design has relationships between these tables.
A database application or Database Management System (DBMS) is a computer software application that is used to create databases.
Common DBMS applications include Microsoft Access, Oracle and MySQL.
Introduction to Database Applications
Learning outcomes
By the end of this session, you will be able to:
- Understand what a Database is
- Differentiate between data and information
- Understand the structure of a database
- Know the common uses of databases
- Steps when creating a database.
- Understand the role of the Database Administrator
Class Notes (session 1A and 1B):Module 6- Session 1A and 1B
When designing a database, the following principles apply:
- Avoid duplicate information (redundant data). Redundant data increases the likelihood of data inconsistency.
- Information stored in the database should be complete and correct.
The database design process consists of the following steps:
- Identify the purpose of the database
- Collect and organize the information required for the database.
- Divide the information into tables.
- Decide the information to be stored in each table to create the fields (columns).
- Specify primary keys.
- Set up the relationships between tables
Session 2A: Creating and Adding Records to a Database
By the end of this session, you will be able to:
- Start a database application and identify the interface
- Create a database and add database tables
- Add fields and records to a table
- Assign primary key and field properties
- Create a relationship between tables
- Save a database
Class Notes: Session 2A: Module 6- Session 2A
Session 2B: Creating table relationships
By the end of this session, you will be able to:
- Define a table relationship
- Create a table relationship
- Define table relationship rules
There are three types of relationships that can be created:
- One-to-One (1:1) relationships – when one record from the primary table matches one record from the related table.
- One-to-Many(1:M) relationships – one record from the primary table matches many records from the related table
- Many-to-Many (M:M) relationships – a number of records from the primary table match a number of records in the related table
Class Notes: Module 6- Session 2B
Session 3A: Working with Queries to Manage Databases
By the end of this session, you will be able to:
- Use queries and recordsets
- Create queries using the Query wizard
- Create a query in Design view
- Open a query
- Add a table to a query
- Join tables in a query
- Run a query
A query is a set of instructions that Access uses to select and display the desired records in a table.
- Database queries make it possible to filter data, perform calculations and summarize data.
- A query is a request for action on data or for data results or both. A query is a means of extracting information from tables.
- You can use queries to analyze the data in a table or to extract data for a form or report.
- Queries are commonly used to display data in related tables and enable you to control not only which records to display, but also which fields.
Class Notes: Module 6- Session 3A
Session 3B: Working with Forms
By the end of this session, you will be able to:
- Create and modify forms
- How to use form options and form properties
- How to save a form
Using forms in a database application makes data entry easier and more convenient. Each form can be designed to fit a specific criteria and also the data collection needs of the user.
Forms can be created from any table in the database. The form can then be used to display the data that is in the table and also used to add new data.
Design controls such as text boxes, buttons and labels are used to view and insert data to a database application. Controls can be bound, unbound or calculated.
Class Notes: Module 6- Session 3B
Session 4A: Reports Generation
By the end of this session, you will be able to:
- Understand the uses of reports
- Create and name a report based on a table or query
- Use report layout to change arrangement of fields
- Modify report headers and footers
A report is used to display data in an organized manner. Reports in a database can be viewed on the Navigation pane. Reports contain useful data for decision-making and analysis.
Class Notes: Module 6- Session 4A
SESSION 5 A & B: LAB EXERCISES
In this LAB, you are required to use the knowledge you have gained in the previous session to come up with a database.
For each of the tables, pick appropriate filed names and populate each table with records.
Class Notes: Module 6- Session 5A and 5B
Session 6B: Database Security
By the end of this session, you will be able to:
- Define Database Security
- Define Confidentiality, Integrity and Accessibility
- Define threats
- Role of the DBA
The need for database security depends on the value of what you have and whether you are trying to protect it from curious eyes, accidental damage, malicious destruction, or theft.
Your goal as a database developer is to provide adequate protection without imposing unnecessary restrictions on the people who should have access to your database.
Aspects of database security
The three main aspects of database security are confidentiality, integrity and availability.
Confidentiality is protecting the database from unauthorized users. For example in a secure database, employees should not be able to see each others’ salaries.
Integrity ensures that what authorized users are trying to do is correct. For example, a student should not be able to modify their own grades.
Availability ensures that data is accessible to a user when needed.
Database Security Problems
A threat can be defined as a hostile agent that can modify or delete the information managed by a DBMS.
It is any situation or event whether intentional or accidental that may adversely affect a system and consequently the organization.
Examples of threats include
- data corruption due to power loss or surge,
- viruses,
- hacking,
- trapdoor
Kinds of Threats
Non-fraudulent Threat –
these are natural or accidental disasters,
Errors or bugs in hardware or software
Human errors.
Fraudulent Threat
caused by authorized users who abuse their privileges and authority or
Hostile agents who may be improper users (outsider or insiders) who attack the software and/or hardware system, or read or write data in a database.
Class Notes: Module 6- Session 6B