1

Lo1. Create database object

• What is database?
• A collections of Related and Organized
information/data.
• A database is a data structure that stores
organized information.

2
• Database, also called electronic database, any
collection of data, or information , that is specially
organized for rapid search and retrieval by
a computer.

• A software program for storing ,Retrieving and

Manipulating a database.

3
• Databases are structured to facilitate the
storage, retrieval, modification, and
deletion of data in conjunction with
various data-processing operations.
A database management system (DBMS)
extracts information from the database in
response to queries.

4
 Data Vs Information
Data Information
Data is raw, unorganized facts that o When data is processed, organized,
need to be processed.
o structured or presented in a given
• Data can be something simple and context so as to make it useful, it is
seemingly random and useless until called information.
it is organized.
o Example: The average score of a
• Example: Each student's test score class or of the entire school is
is one piece of data. information that can be derived
from the given data

5
 Advantages of database
Reduced data redundancy
Reduced updating errors and increased consistency
Greater data integrity and independence from
applications programs
Improved data access to users through use of host
and query languages
Improved data security
Reduced data entry, storage, and retrieval costs
Facilitated development of new applications
program
6
 Disadvantages Database
Database systems are complex, difficult, and time-
consuming to design
Extensive hardware and software start-up costs
Damage to database affects virtually all applications
programs
Extensive conversion costs in moving form a file-
based system to a database system
Initial training required for all programmers and
users
7
What is Ms Access
• Ms Access is a database management tool that
enables you to store relevant data. This also
has the capabilities to retrieve, sort,
summarize and report results immediately and
effectively.
• It can combine data from various files (tables)
through creating relationships, and can make
data entry more efficient and accurate
through the use of forms.

8
• Microsoft Access (MS Access) enables one to
manage all important information from a
single database file.
• Within the file, one can use the different
objects:
Tables - A table is a collection of data
about a specific topic, such as products or
suppliers.
A table is a collections of Rows and
Columns.
9
the basic components of a table

Meta Data – Database Structure

Field – Column – data
Record - Row - Information

10
 Field properties
• Field size defines the number of characters you’re a text field
can contain.
• Format is normally use for a field that has a data type of Number
or Date. Click on it to define custom settings you want.
• Input Mask was good in setting-up initial constant values.
Setting an input mask for ID number field to “MOE-999999” would
mean that when you enter a value for that field it would initially
give you a value of MOE-_ _ _ _ _ _ . “999999” allows you to
enter any 6-digit numbers. So, you don’t need to type MOE-
because it would automatically appear. You just need to add a 6-
digit value to it to complete its value. For example: typing
“000123” gives you “MOE-000123”
11
• Caption defines the label of a field. Writing “Last
name” on a Caption of the Lname. See the
Caption on the table input view later.
• Default Value gives you an initial value. If you are
expecting more Male students, you better put
“Male” on the Default Value of your Gender field
so that when you input a record, it would
automatically appear a Male on your Gender field
so you don’t need to type it anymore. In case of a
Female gender, you can alter it anyway. You would
save time for all Male inputs since you don’t need
to type it anymore
12
• Validation Rule allows you to filter the values
you just want to input for a particular field.
Setting a Gender field Validation Rule to “Male”
or “Female” would mean that it would only
accept a Male and Female value for Gender
field. Typing F or M to it would give you an
error-message.
• Validation Text is the counterpart of Validation
Rule. Instead of displaying a computer
generated error-message when you input a
wrong value to a field, you can set your own
error-message. You just type the text you want.
Example: “Wrong Input!”
13
• Required field when set to Yes would not allow
you to skip a certain field leaving it blank.
• Indexed when set to OK (Duplicates OK) would
sort out a field alphabetically and it would
accept duplicate values, when set to OK (No
Duplicate) would sort out a filed alphabetically
but would not allow you to enter the same value
(you cannot enter an ID number that already
exist).

14
Queries - You use queries to view,
change, and analyze data in different
ways. You can also use them as a source
of records for forms, reports, and data
access pages
oUsed for filtering specific data and
performing the calculations.

15
Queries may be created by either:
1) Query wizard
2) Design view
3) SQL View

1) Creating Queries using a wizard

• Creation of a query by using a wizard is a fastest and
easy way to extract information you need from one or
more tables.
2) Creating Queries using design view
• Creation of a query by design view is a user
customized way of setting-up criteria to filter the
necessary information you need from one or more
tables.
16
Forms - A form is a type of a database
object that is primarily used to enter or
display data in a database.
You can also use a form as a switchboard that
opens other forms and reports in the
database, or as a custom dialog box that
accepts user input and carries out an action
based on the input.

17
•Forms may be created by either:
• Form wizard
• Design view

18
Reports - A report is an effective way
to present your data in a printed
format. Because you have control
over the size and appearance of
everything on a report, you can
display the information the way you
want to see it

19
• Reports may be created by either:
1) Report wizard
2) Design view
• Creating Reports using a wizard
Creation of a report by using a wizard 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
Creation of a report by design view is a user customized
way setting-up your report so to meet the users desired
output and format.

20
Modules - A module is essentially a collection
of declarations, statements, and procedures
stored together as one named unit to organize
your Microsoft Visual Basic code. Microsoft
Access has two types of modules: standard
modules and class modules.

21
Data access pages - A data access page is a
special type of Web page designed for viewing
and working with data from the Internet or an
intranet— data that is stored in a Microsoft
Access database or a Microsoft SQL Server
database. The data access page may also
include data from other sources, such as
Microsoft Excel

22
Establishment of Ms Access database
• All Ms Access databases files are saved with
extension .mdb. A database should have a separate
table for every major subject, such as Students
records, Students grades, etc..
• Production data or Treatment information. Data
should not be duplicated in multiple tables.

23
Microsoft Access provides three methods to
create a database

• Database Wizard (though easy, the wizard offers

limited options to customize the database)
• Using a template (This method works best if one can
find and use a template that closely matches the
specific requirements)
• Creating a database directly (This is the most
flexible method, but it requires one to define each
database element separately).

24
Database Elements

• Data are raw facts. It tells the truth about

something; a person, a place, an object, etc.
• Information is a collection of data (raw facts)
which is contained in 1 file

25
Naming Convention

Do not use spaces in object names

Avoid using reserved words
Do not use dashes in database names.
Start object names with a letter
Keep names short but meaningful
 Use sensible case
Avoid Hungarian notation

26
Data Types
• A database object used to specify what kinds of data
are to be stored in this field.
• The different kinds of Ms Access 2003 data types
are:
• Text: allows for the storage of any kind of data,
characters, digits and special characters. It has a
defect length of 50 characters with a maximum
length of 255. It is normally used to store data such
as names, addresses, or any number not used in
calculations, like telephone numbers or zip codes.
27
•Memo: is used for texts of more than 255
characters such as comments or explanations. It
has a maximum length of 65,536 characters.
Access recommends that to store formatted text
or large documents, rather to create an OLE
Object field than a Memo field.
• Date/Time: for the introduction of date and
time from the year 100 to 9999

28
• Currency: For monetary values and numerical
data used in mathematical calculations in which the
data involved contains between one and four
decimals. The accuracy is up to 15 digits to the left
of the decimal separator and up to 4 digits to the
right of the same.
• Auto number: a unique sequential number
(increasing one by one) that Access assigns every
time it adds a new record to a table. Auto number
fields cannot be updated.

29
• Yes/No: Yes and No values, and fields that
contain one of two values (Yes/No, True/False
or Activated/Deactivated).
• OLE Object: an object such as a Microsoft Excel
spreadsheet, a Microsoft Word document, graphics,
images, sounds, or other binaries.

30
• Hyperlink: text or a combination of text and
numbers stored as text and used as a hyperlink
address. A Hyperlink is a text or graphic that you
click to go to a file, a location in a file, a web page
on the Internet, or a web page on an intranet.

31
 What is Primary key
• Primary Key is the unique identification of one record.
It will not allow a duplication of the Primary Key thus
make it unique.
• Define a Primary Key field (You don't have to define a
primary key, but it's usually a good idea.
• If you don't define a primary key, Microsoft Access asks
if you want Access to create one for you when you
save the table)

32
What is Relationship?

• The way in which two or more people or things are

connected, or the state of being connected.
• An Association b/n to two tables in a database
• Relationship types
1) One to one
2) One to many
3) Many to Many

33
 ONE TO ONE
only one record of a table is Associated with one
record from the other table.
Simplest type of relationship
Least common relationship
Tables Link by primary keys
Link each record in one table to a single record in
another table
Example:- Manager and employee
34
 ONE TO MANY RELATIONSHIP
only one record of a table is Associated with many records
from the other but not to the inverse.
Is the most common relationship
Link each record in one table to several record in another
table
Only one of the field being linked can be primary key
Primary key must be in the table that contain one record for
many records in the other table
 Example:- Department and Student
35
 MANY TO MANY RELATIONSHIP

many records from the tables are associated

with many other records.
Requires junction table
Two one to many Relationships
Example:- Student , Course and Stu-Course

36
37