Operate Database Application
Operate Database Application
Operate Database Application
TTLM 2 Page 1 of 32
This module defines the competency required to operate database applications and perform basic
operations.
At the end of the course / Module, the trainee will able to:
1) Create database objects
2) Customize basic settings
3) Create reports
4) Create forms
5) Retrieve information
Duty (Chapter)
I.
1.
2.
II
1.
2.
Course / Module Assessment Methods:
References:
1. www.google.com
2.
TTLM 2 Page 1 of 32
An organization must have accurate and reliable data for effective decision making. To this end,
the organization maintains records on the various facets of maintaining relationships among
them. Such related data are called a database. A database system is an integrated collection of
related files, along with details of the interpretation of the data contained therein. The word
'DATA' means a fact or more especially value of the properties of an object. A 'DATABASE'
can be conceived as a system whose base, whose key concept, is simply a particular way of
handling those data.
So, a database is any collection of related data. A database is a persistent, logically coherent
collection of inherently meaningful data, relevant to some aspects of the real world. Simply, The
database system is nothing more than a computer-based record-keeping system i.e. a system
whose overall purpose is to record and maintain information/data.
Database Applications:
TTLM 2 Page 1 of 32
Telecom: There is a database to keeps track of the information regarding calls made,
network usage, customer details, etc. Without the database systems, it is hard to maintain
that huge amount of data that keeps updating every millisecond.
Before going into the principle part, let’s first discuss what is information. Information is a
valuable organizational asset and according to its value and scope, it must be organized, stored,
secured, and should be readily available in a usable format for daily operations and analysis by
individuals, groups, and processes, both today and in the future.
TTLM 2 Page 1 of 32
Usability: Any information which we are storing in any organization should be meaningful for
that organization. If we are storing those factors which are not fit with an organization’s
requirement then this is just a waste of resources.
Extensibility: As we know that everyday new business requirements come up and every day
there is a need to change or enhance the information system to capture new requirements. So
information design should be extensible so that it can adopt new requirements without much
effort or major breaking changes.
Data Integrity: Now at this point we understand that information is very much important for
any organization. Based on historic information, every organization makes different strategies,
decisions for growth. One small mistake in data can lead to major issues with any organization’s
key decision and hence a big risk for growth. Data integrity is the overall accuracy,
completeness, and consistency of data. Data integrity also ensures that your data is safe from
any outside forces.
Entity Integrity: Involves the structure (primary key and its attributes) of the entity. Entity
integrity relies on the creation of primary keys, or unique values that identify pieces of data, to
ensure that data isn’t listed more than once and that no field in a table is null. It’s a feature of
relational systems which store data in tables that can be linked and used in a variety of ways.In
the physical schema, the table’s primary key enforces entity integrity.
Domain Integrity: It defines that data should be of the correct type and we should correctly
handle optional data. We should apply Nullability to those attributes which are optional for the
organization. We can define proper data types for different attributes based on the organization’s
requirement so that correct format data should present in the system. In this context, a domain is
a set of acceptable values that a column is allowed to contain. It can include constraints and other
measures that limit the format, type, and amount of data entered.
Referential Integrity: This defines if any entity is dependent on another one then the parent
entity should be there in the system and should be uniquely identifiable. We can do this by
TTLM 2 Page 1 of 32
implementing foreign keys. Rules embedded into the database’s structure about how foreign
keys are used ensure that only appropriate changes, additions, or deletions of data occur.
User-defined integrity: There are few business rules which we cannot validate just by primary
keys, foreign keys, etc. There has to be some mechanism so that we can validate complex rules
for integrity. We can implement these rules in the following ways:
Security: For any organizational asset, the level of security must be secured depending on its
value and sensitivity. Sometimes organizations have suffered a lot because of data leaks which
result in loss of faith and tend to business risk. So security is one of the most important aspects of
good database design.
Introduction:
A database can best be described as a way of storing large amounts of information. The data can
be retrieved and we can even ask questions of the data and get answers.
E.g.: You may want to know how many students enrolled in every occupational level.
A database management system (DBMS) is system software for creating and managing
databases. A DBMS makes it possible for end-users to create, read, update and delete data in a
database. Some other DBMS examples include:
MySQL SQL Server
TTLM 2 Page 1 of 32
TTLM 2 Page 1 of 32
Divide the information into tables- Divide the information items into major entities or
subjects, such as Products or Orders. Each subject then becomes a table.
Turn information items into columns- Decide what information to store in each table.
Each item becomes a field and is displayed as a column in the table. For example, an
Employee table might include fields such as Last Name and Hire Date.
Specify primary keys- The primary key is a column that is used to uniquely identify
each row. An example might be Product ID or Order ID.
Set up the table relationships- decide how the data in one table is related to other tables.
Add fields to tables or create new tables to clarify the relationships, as necessary.
Refine your design- Analyze your design for errors. Create the tables and add a few
records of sample data.
Apply the normalization rules. Apply the data normalization rules to see if your tables
are structured correctly.
TTLM 2 Page 1 of 32
TTLM 2 Page 1 of 32
Format The format property specifies how the field Graduatiuon date will be displayed
should be displayed and printed. as “May 1, 2006”.
Input The input mask property specifies a pattern Zip code field can be entered as
Mask for data entry into a field. follows: ####-####.
Caption The caption property provides the label for a The fields’s label is “Employee’s
field used on a form or report. hire date”.
Default The default value property provides the The order date is always today’s
value default information for all new records. date uless edited.
Validatio The validation rule property validates data The quantity ordered must be
n rule entered by users before saving data. greater than 1.
Validatin The validation text property displays a “please eneter an ordere quantity
text message where data is rejected as invalid. grater than 1.”
Required The required property defines a field as A purchase order must have a ship-
requiring data for the record to be complete. to address.
Allow The allow zero length property allows a A student email address, and some
Zero record to be complete with a field that records will not contain any data
Length contains Null (not data). Only text, memo and that field.
hyperlink fields can accept zero-length
strings,
Indexed The indexed property speeds up searching and Frequently used items, such as
can ensure that data is unique. student first and last name.
TTLM 2 Page 1 of 32
Both of these are valuable goals as they reduce the amount of space a database consumes and
ensure that data is logically stored.
1.6 Modifying Database Object
Modify Your New Database Created from a Template
Every template that is included with Access is a complete tracking application that contains
predefined tables, forms, reports, queries, macros, and relationships. So, that you can create a
new database that is based on a template and get up and running quickly. You can easily add a
field to a table in Datasheet view. However, you can also add a field to a table in Design view.
If possible, you should avoid deleting a field from a database that was generated from one of the
supplied templates — it is likely that the field is employed in other database objects, such as
forms and reports. Thus, deleting the field will create consequences when you attempt to use the
other database objects that employ the field — the database objects will not work as expected.
Before you can delete a field, you must ensure that it doesn't participate in any table
relationships. If you try to delete a field for which relationships exist, Access warns you that you
must first delete the relationships.
1.7. Create Database Relationship
Table Relationship
They see tables of data and recognize that databases allow you to organize and query data in new
ways, but fail to grasp the significance of the relationship that gives relational database
technology & its name.
Relationships allow you to describe the connections between different database tables
in powerful ways.
Once you’ve described the relationships between your tables, you can later leverage
that information to perform powerful cross-table queries, known as joins.
A relationship is a logical connection between two tables.
Keys are fields that are part of a table relationship. There are two kinds of keys
Primary key
A table can have only one primary key.
A primary key is used to identify each record that you store in the table.
It will not allow a duplication of the Primary Key thus make it unique.
TTLM 2 Page 1 of 32
Customer Employee
c-id e- id
c-name e- name
c-age e- age
c-sex e- sex
c-phone e- address
c-address e- salary
c-id
Primary key Foreign key
You add a record to your database when you have a new item to track,
You also change fields to stay up-to-date, such as a new address or last name.
TTLM 2 Page 1 of 32
Finally, you can delete a record when it is no longer relevant and to save space.
Data entry forms can provide an easier, faster, and more accurate way to enter data. Forms can
contain any number of controls such as lists, text boxes, and buttons.
Datasheets are grids of data that look like Excel worksheets. You can change data by working
directly in Datasheet view. You can change data in tables, query result sets, and forms that
display datasheets. Typically, you use datasheets when you need to see many records at once.
TTLM 2 Page 1 of 32
database, data and all, with a new filename as a backup, or in a different format, such as an
earlier Access file format, a database template, or a compiled database (a database where you
can't change the design). You can also save individual database objects as new objects.
TTLM 2 Page 19 of 32
TTLM 2 Page 19 of 32
Note: Satisfactory rating – 12.5 and above points Unsatisfactory - below 12.5 points
You can ask you teacher for the copy of the correct answers.
TTLM 2 Page 19 of 32
TTLM 2 Page 19 of 32
Formula bar - Bar in a spreadsheet program that allows you to edit a formula.
Menu bar - A bar at the top of the screen that gives access to all of the menus.
Navigation bar - Gives access to all navigation features in a browser.
Places bar - A pane that shows common places to access files.
Scroll bar - A bar on the bottom or side of the window to scroll through a page.
Split bar - A bar that divides the window into multiple sections.
Status bar - One of the few bars at the bottom of the window that shows the status.
2.3 Formatting Fonts
A font is a graphical representation of text that may include a different typeface, point size,
weight, color, or design.
Formatting Fonts Sometimes users want to specify or change the appearance of a group of words
or characters, or even of a single word or character, and they are not familiar enough with all of
Word's font formatting options to know how to achieve the effect that they are seeking. Although
most users are probably familiar with some different font families and sizes and with the italic and
bold typefaces, many users do not know how to determine and apply the settings that are needed
to add a colored background to their text or to type
TTLM 2 Page 19 of 32
You can ask you teacher for the copy of the correct answers.
TTLM 2 Page 19 of 32
Decide how to lay out your reportwhen you design a report, you must first consider how
you want the data arranged on the page and how the data is stored in the database.
Make a sketch of your report: This step is not required — you might find that the
Access Report Wizard or the Report tool(both of which are available on the Create
tab, in the Reports group) provide a sufficient starting design for your report
Decide which data to put in each report
Decide how to arrange the detail
Types of lay out
Tabular layout: it is similar to a spreadsheet. Labels are across the top, and the
data is aligned in columns below the labels. Tabular refers to the table-like
appearance of the data. This is the type of report that Access creates when you
click Report in the Reports group of the Create tab. The tabular layout is a good
one to use if your report has a relatively small number of fields that you want to
display in a simple list format.
Stacked layout resembles a form that you fill out when you open a bank account
or make a purchase from an online retailer. Each piece of data is labeled, and the
fields are stacked on top of each other. This layout is good for reports that contain
too many fields to display in a tabular format — that is, the width of the columns
would exceed the width of the report.
Mixed layout: You can mix elements of tabular and stacked layouts. For example,
for each record, you can arrange some of the fields in a horizontal row at the top of
the Detail section and arrange other fields from the same record in one or more
stacked layouts beneath the top row. The following illustration shows an employee
report that was created by using a mixed layout. The ID, Last Name, and First
Name fields are arranged in a tabular control layout, and the Job Title and Business
TTLM 2 Page 19 of 32
Phone fields are arranged in a stacked layout. In this example, gridlines are used to
provide a visual separation of fields for each employee.
Justified layout: If you use the Report Wizard to create your report, you can
choose to use a justified layout. This layout uses the full width of the page to
display the records as compactly as possible. Of course, you can achieve the same
results without using the Report Wizard, but it can be a painstaking process to align
the fields exactly. The following illustration shows an employee report that was
created by using the Report Wizard's justified layout.
Creating Reports using a wizard: it is a fastest and easy way to create a report by
following the series of steps and choosing a pre-defined template for your report.
Creating Reports using design view: it is a user customized way setting-up your report
so to meet the users desired output and format.
TTLM 2 Page 19 of 32
TTLM 2 Page 19 of 32
You can ask you teacher for the copy of the correct answers.
TTLM 2 Page 19 of 32
TTLM 2 Page 19 of 32
Objects: A Form object refers to a particular Microsoft Access form. A Form object is a member of
the Forms collection, which is a collection of all currently open forms. Within the Forms
collection, individual forms are indexed beginning with zero.
TTLM 2 Page 19 of 32
8. Is a screen that allows you to enter, change, and view the data in a database?
11. List & Discuss on the different types of Controls in MS access? (12 points)
You can ask you teacher for the copy of the correct answers.
LO 5: Create database Queries
Operate Database Application Page 25 of 31
Institution Name Document No.
TTLM 2 Page 19 of 32
The techniques that you can use to search and filter records are very useful for finding specific
records for the case at hand. However, you might want to perform the same search or filter
operation regularly. Instead of reproducing a set of search and filter steps every time, you can
create a query. A query is a powerful and flexible way to locate specific records because it lets
you perform customized searches, apply customized filters, and sort records. You can build your
own queries to help you focus on specific records and to answer specific questions. Once created,
a query can be saved and reused, and can also be used in building forms and reports.
5.2 Creating Simple Query to Retrieve Information
A query is a request for data results, for action on data, or for both. You can use a query to answer
a simple question, to perform calculations, to combine data from different tables, or even to add,
change, or delete table data. A query is a derived item in the database meant to answer specific
questions that relate to theinformation in the database. It is the means to retrieve relevant
information in one or more tables.
Types of Queries
Select Query: it is the simplest type of query and because of that, it is also the most
commonly used one in Microsoft Access databases.
A select query is the most common type of query. It retrieves data from one or more tables and
displays the results in a datasheet where you can update the records (with some restrictions).
TTLM 2 Page 19 of 32
You can also use a select query to group records and calculate sums, counts, averages, and other
types of totals. It can be used to select and display data from either one table or a series of them
depending on what is needed. In the end, it is the user-determined criteria that tell the database
what the selection is to be based on. After the select query is called, it creates a "virtual" table
where the data can be changed, but at no more than one record at a time.
Action Query: Action queries are very popular in data management because they allow
for many records to be changed at one time instead of only single records like in a select
query.
When the action query is called, the database undergoes a specific action depending on what was
specified in the query itself. This can include such things as creating new tables, deleting rows
from existing ones and updating records or creating entirely new ones.
Four kinds of action queries are:
Append Query
An append query adds a group of records from one or more tables to the end of one or more
tables. For example, suppose that you acquire some new customers and a database containing a
table of information on those customers. To avoid typing all this information into your own
database, you'd like to append it to your Customers table.
Delete Query
A delete query deletes a group of records from one or more tables. For example, you could use a
delete query to remove products that arediscontinued or for which there are no orders. With delete
queries, you always delete entire records, not just selected fields within records.
Update Query
It allows for one or more field in your table to be updated. An update query makes global changes
to a group of records in one or more tables. For example, you can raise prices by 10 percent for
TTLM 2 Page 19 of 32
all dairy products, or you can raise salaries by 5 percent for the people within a certain job
category. With an update query, you can change data in existing tables.
Parameter Query
In Microsoft Access, a parameter query works with other types of queries to get whatever results
you are after. This is because, when using this type of query, you are able to pass a parameter to a
different query, such as an action or a select query. It can either be a value or a condition and will
essentially tell the other query specifically what you want it to do.
It is often chosen because it allows for a dialog box where the end user can enter whatever
parameter value they wish each time the query is run. The parameter query is just a modified
select query.A parameter query is a query that when run displays its own dialog box prompting
you for information, such as criteria for retrieving records or a value you want to insert in a field.
You can design the query to prompt you for more than one piece of information; for example, you
can design it to prompt you for two dates. Access can then retrieve all records that fall between
those two dates. Parameter queries are also handy when used as the basis for forms, reports, and
data access pages.
5.3 Developing Query With Multiple Criteria
Introduction to query criteria
A criterion is similar to a formula — it is a string that may consist of field references, operators,
and constants. Query criteria are also referred to as expressions in Access. The following tables
shows some sample criteria and explains how they work.
Simple criteria for all data types:
Criteria Name Write it like... Function
Equals "x" Searches for values equal to x
Does Not Equal Not in ("x") Searches for all values except those equal to x
Null Is Null Searches for empty fields
Not Null Is Not Null Searches for non-empty fields
TTLM 2 Page 19 of 32
order.
Searches for all values that come before x in alphabetical
Comes Before <= "x"
order.
The AND operator: tells Access that both criteria have to be true in order for the record to be
displayed or used. E.g. where LastName starts with C and the City field is Phoenix:
The OR operator: The OR operator tells Access that either one thing or another can be true for
the record to be displayed or used. e.g. where LastName starts with C OR City is Phoenix.
Examples of wildcard characters
To locate a specific item when you can't remember exactly how it is spelled, try using a wildcard
character in a query.
Wildcards are special characters that can stand in for unknown characters in a text value and are
handy for locating multiple items with similar, but not identical data. Wildcards can also help with
getting data based on a specified pattern match.
Operate Database Application Page 29 of 31
Institution Name Document No.
TTLM 2 Page 19 of 32
1. ------is defined as the process of determining the appropriate data type and source.
A. Data selection B. Data processes C. A&B
2. It is important to display data accurately.
TTLM 2 Page 19 of 32
A. True B. False
3. A criterion is similar to:
A. a formula B. String C. A&B
4. --------is a string that may consist of field references, operators, and constants.
A. Criterion B. Value
5. .--- is a request for data results, for action on data, or for both.
A. A query B. Table C. Form D. None
6. ---deletes a group of records from one or more tables (2)
A. delete query B. Append Query C. Parameter query
7. Allows for one or more field in your table to be updated.(2)
A. Update Query B. Parameter query C. Select query
8. You can browse through records by using the--- key(2)
A. TAB B. ALT C. Delete D. None
9. The -----buttons are available at the bottom of the table or form.(2)
A. record navigation B. Save button C. A&B
10. -----is a powerful and flexible way to locate specific records & customized
searches.
A. True B. False
You can ask you teacher for the copy of the correct answers.