Arbaminch polytechnic

college
Arbaminch Information UNIT
Database Administration
Polytechnic Sheet
MODULE
College Modeling Data Objects
College

Database Administration
(Level III)

Modeling Data Object version 1.0 Year 2012 prepared By: Awraja Page 2
 Arbaminch Information UNIT
Database Administration
Polytechnic Sheet
MODULE
College Modeling Data Objects
College

LEARNING GUIDE # 5
Module Title: - Modeling Data Objects
Module code: ICT DBA3 05 0710
SYMBOLS
These symbols are located at the left margin of the module. These illustrate the actions that should be
taken or resource to be used at a particular stage in the module.

LO Learning
Outcome Self-Check

Answer Key
Resources

Modeling Data Object version 1.0 Year 2012 prepared By: Awraja Page 2
Arbaminch Information UNIT
Database Administration
Polytechnic Sheet
MODULE
College Modeling Data Objects
College

Reading Assessment
Activity

Remember/Tips
Use Computer

Practice Task Safety

Modeling Data Object version 1.0 Year 2012 prepared By: Awraja Page 2
 NO: _____

LO
1. Identify entities and relationships
2. Develop normalization
3. Validate model

1. ER-Diagram components
Introduction

Conceptual Database Design

 Conceptual design revolves around discovering and analyzing organizational and user data requirements.
 The important activities are to identify
 Entities
 Attributes
 Relationships
 Constraints
 And based on these components develop the ER model using ER diagrams

The Entity Relationship (E-R) Model Components

 Entity-Relationship modeling is used to represent conceptual view of the database.
 The main components of ER Modeling are:
 Entities
 Corresponds to entire table, not row.
 Represented by Rectangle

What is entity in a database?

 An Entity is an object with a physical existence or a conceptual existence.
- It can be roles, events, locations, tangible things or concepts.
In database terms, an entity is a table which is responsible for storing data in the database.
Name of trainer: Fasil Date: ____/____/04
 NO: _____
 Entity type is the set of all possible values for an entity.
- An entity set is a set of entities of the same type (collection of instances represented by (e1, e2, e3, ... en).
- It is also called an extension of the entity type
 Entity Instance is a single occurrence of a particular entity type.
Entity type name EMPLOYEE PROJECT
Attributes name Name, Age, salary Pname, Pcode, Location
e1
P1
One entity instance
(Abebe, 25, 3500)
(Database, p001, Bahir Dar)
Entity set e2 One entity instance
(Extension) P2
(Daniel, 42, 2500) One entity instance
(Water, P002, Gonder)
e3
P3
*
*
(Aster, 32, 3000)
(Construction, P003, Gonder) *
In E-R Diagram, an entity type is represented by a rectangle, and the name is indicated in capital letters.
Example: DEPARTMENT EMPLOYEE

 Attributes
 Represents the property used to describe an entity or a relationship
 Represented by Oval
 Attributes
- An attribute is a property that describes an entity.
- Each attributes have a particular value based on the defined data type.
- The set of all possible (allowable) values of an attribute is called attribute domain.
- In E-R Diagram, attributes are named with an initial capital letter followed by lowercase letters.
- An attribute can be represented by an ellipse (oval) shape with a line connected it to the associated entity.
Example:

- It can also be represented by listing them within the entity rectangle, under the entity name(entity type).
Example: EMPLOYEE

Emp_Id

 Types of attributes Fname

An attribute can be: - simple or composite, single-value or multi-value, stored or derived
or Null Values.
1) Simple (Atomic) vs Composite attribute
. Simple attributes cannot be further divisible.
Example: SSN, Sex
Name of trainer: Fasil Date: ____/____/04
 NO: _____
. In E-R Diagram, simple attributes can be represented by an ellipse shape. Example:

. Composite attributes can be divided into smaller subparts.

Example: - address (street address, city, state, zip code)
- Name (first name, last name, initial name)
. In E-R Diagram, composite attributes can be represented by:
. The value of composite attribute is the concatenation of the values of its constituent simple attributes.
2) Single-valued vs Multi-valued attributes
- Most attributes have a single value for a particular entity.
- In some cases, an attribute can have a set of value for the same entity, called multi-valued.
In E-R Diagram, Multi-valued attributes can be represented by double ellipse (oval)
shape.
3) Stored vs derived attributes.
. In some cases, some attributes have a reference (derived from other attributes’ value).
In E-R Diagram, Derived attributes can be represented by dotted ellipse.

4) Null Values attributes

 Not applicable: e.g. Apartment Number, College Degree.
 Unknown: - the attribute value exists but is missing. E.g. Height
- It is not known whether the value exists. E.g. Home Phone
 Key attributes
- Attributes can be classified as identifiers or descriptors.
- A descriptor describes a non-unique characteristic of an entity instance.
- Identifiers (more commonly called keys or key attributes) uniquely identify each instance of an entity,
called candidate key. If such an attribute doesn't exist naturally, a new attribute is defined for that purpose.
- In some cases, more than one attribute is required to identify a unique entity, called composite candidate key.
- In E-R Diagram, key attributes (identifiers) can be represented by ellipse shape with underline.

 Relationships
1.2. Relationships
A relationship is an association that exist b/n two or more participating entities.
- In E-R Diagram, a relationship type is represented by a diamond shape
with the relationship verb in it.

There are three types of relationships b/n entities

* One-to-one: - one instance of an entity (A) is associated with one other instance of another entity (B).
Example: President to country, husband to wife, people to religion

* One-to-many: - one instance of an entity (A) is associated with zero, one or many instances of another entity
(B), but for one instance of entity (B) there is only one instance of entity (A).

Example: Country to people, mother to children, department to students

* Many-to-many: - one instance of an entity )A) is associated with zero, one or many instances of another entity

Name of trainer: Fasil Date: ____/____/04

 NO: _____
(B), and one instance of entity (B) is associated with zero, one or many instances of entity (A).

Example: Students to course, employee to projects, people to language

1.3. Documenting Entity relationship Diagram

An ERD is a model (theoretical view) that identifies the concepts or entities that exist in a system and the relationships
between those entities.

Entity relationship Diagram Documentation must include: Entities

- Relationship
- Optionality and Cardinality
- Bridge Entities
- Recursive Relationships
- Multiple Relationships between Entities
- Entity Subtypes
- Key Attributes of an entity
Non- key Attributes of an entity

 Represents the association that exist between entities

 Represented by Diamond
 Constraints
 Represent the constraint in the data
1.4. Identify the scope of the system
System Scope describes the current systems that the required application package is expected to replace or interface with.
The description contains a table that describes the functionality of each system, and relates systems to business activities.

1.2.1 Databases
A database is a collection of organized information so that it can easily be accessed, managed, and updated.
 A database management system is a suite of software applications that together make it possible for people or
businesses to store, modify, and extract information from a database.
1.2.2 Applications
Application software is the name given to a program or group of the programs designed for end-users (perform
specific tasks for end-users). Example: database programs, word processors, and antivirus programs are all applications.
1.2.1 Servers
A Server is a computer program that manages access to a centralized resource such as file, printer, and user group as
well as network traffic on the network or provide services to client software running on other computers (and their
users) in the same domain (in a network).
A single server machine can have several different server software packages running on it, thus providing many
different services to clients on the network.

There are many different types of servers:

 Application Servers  Mail Servers
 Message Servers  Proxy Servers
 FTP Servers  Web Servers
Name of trainer: Fasil Date: ____/____/04
 NO: _____
 File server
 Print server
 Database server
 Backup server
 DHCP server
 DNS Server

Name of trainer: Fasil Date: ____/____/04

Application server allows a client on the network to access and use extra computing power. Application
server is mainly used for the security.

• Message server includes Chat servers that enable a large number of users to exchange
information in the network.
• Mail server store and handles mail message over in the networks (via LANs) and across
the Internet (via WANs).
• A Proxy server is a server that can be sitting between the server and the client. When
any request is coming, client pass that request to the server, then server process that
request and send back to the client. Proxy server improves the performance of the
security by filtering the request.
• Web server provides the access to the internet using hypertext markup language. A Web
server get request from the web browser and send back the response and associated
HTML document.
• File servers helps user to exchange data (read data, write data) as well as manage shared
files or it control and manage overall user access.
• Print server control and manage printer resources over the network and also it offer fax
services. The print server allows multiple users to access printer resources and fax
services simultaneously.
• Database servers manage the database using database management system software.
Client request is sent in the form of queries to the server, then database server process the
incoming client query, access the stored data from the database and sent result back to the
client.
• A backup server responsible for backing up and restoring files, folders, databases on a
network in order to prevent the loss of data in the event of a hard drive failure, user error,
disaster or accident.
• A DHCP Server is used to assigns IP addresses to client computers automatically. All IP
addresses of all computers are stored in a database that resides on a server machine.
• DNS Server is needed for resolving hostnames to their IP addresses.
1.2.2 Operating systems
An operating system (OS) is a collection of software that enables the computer hardware to
communicate and operate with the computer software.
The operating system is a vital component of the system software in a computer system. Without
operating system, a computer would be useless. Application programs require an operating
system to function.
1.2.3 Gateways
A network gateway is an internetworking system capable of joining together two networks that
use different base protocols. A network gateway can be implemented completely in software,
completely in hardware, or as a combination of both.
A Gateway is - used between two dissimilar LAN s.
 LOGO INFORMATI UNIT Database Administration
ON SHEET MODULE Modeling Data Objects

- required to convert Data packets from one protocol format to another Protocol
format.
Router and Gateway both are sometimes used interchangeably but the difference is that Gateway
operates on 7th layer of the OSI Model and the Router works on the 3rd layer of the OSI model.
The OSI Layer consists of:
1. Physical layer
2. Data Link
3. Network, (performs establishment of connection between networks and the Routing and
selecting best path)
4. Transport layer
5. Session layer
6. Presentation layer
7. Application Layer. (Provides semantic exchange of data between application in an open system)
1.2.4 Application service provider
An application service provider (ASP) is a business that provides computer-based services to
customers over a network. I.e.: Application service provider is remote software that you access
through a web browser.

The goal of an application service provider is to reduce the cost of software distribution and
maintenance.

1.2.5 ISP
An Internet service provider (ISP) is an organization/company that provides internet access to
users or subscribers of its service, usually for a fee.
1.5. Reviewing business rules to determine impact
A business rule is a rule of a business, company, or corporation that defines/determine or constrains
some aspect of business and always resolves to either true or false.
1.5.1. Overview of business rules
Business rules describe the business polices that apply to the data stored on a company’s databases.

Business-rules constraints fall into two categories: field constraints within tables, and relationship
constraints between tables.

ER-Diagram construction
 Designing conceptual model for the database is not a one linear process but an iterative
activity where the design is refined again and again.
 To identify the entities, attributes, relationships, and constraints on the data, there are
different set of methods used during the analysis phase.

Modeling Data Objects version 1.0 Year 2019 prepared By: Ermias Page 13
 LOGO INFORMATI UNIT Database Administration
ON SHEET MODULE Modeling Data Objects

 These include information gathered by…

 Interviewing end users individually and in a group
 Questionnaire survey
 Direct observation
 Examining different documents
 The basic E-R model is graphically depicted and presented for review.
 The process is repeated until the end users and designers agree that the ER diagram is a fair
representation of the organization’s activities and functions.
 Checking the Redundant Relationships in the ER Diagram. Relationships between entities
indicate access from one entity to another - it is therefore possible to access one entity
occurrence from another entity occurrence even if there are other entities and relationships
that separate them - this is often referred to as Navigation' of the ER diagram
 The last phase in ER modeling is validating an ER Model against requirement of the user.

Graphical Representations in ER Diagramming

 Entity is represented by a RECTANGLE containing the name of the entity.

Strong Entity Weak Entity

 Connected entities are called relationship participants.

 Attributes are represented by OVALS and are connected to the entity by a line.

Attribute Multi-Valued Attribute Composite Attribute

Key
Composit Attribute

 A derived attribute is indicated by a DOTTED LINE. (………)

 PRIMARY KEYS are underlined.

Attribute Multi-Valued Attribute Composite Attribute

Key

Modeling Data Objects version 1.0 Year 2019 prepared By: Ermias Page 13
 LOGO INFORMATI UNIT Database Administration
ON SHEET MODULE Modeling Data Objects

 Relationships are represented by DIAMOND shaped symbols

 Weak Relationship is a relationship between Weak and Strong Entities.
 Strong Relationship is a relationship between two strong Entities

Strong RelationShip Weak RelationShip

Example 1: Build an ER Diagram for the following information:

 A student record management system will have the following two basic data object
categories with their own features or properties. Students will have an Id, Name, Dept, Age,
GPA and Course will have an Id, Name, Credit Hours
 Whenever a student enroll in a course in a specific Academic Year and Semester, the Student
will have a grade for the course

me pt B Credit
Na De Do ID Name
ID

GPA
Student Course
Ag
e Enrolled_in

Acedamic_year Semester
Grade

Example 2: Build an ER Diagram for the following information:

 A Personnel record management system will have the following two basic data object
categories with their own features or properties. Employee will have an Id, Name, DoB, Age,
Tel and Department will have an Id, Name, Location
 Whenever an Employee is assigned in one Department, the duration of his stay in the
respective department should be registered.

Structural Constraints on Relationship

Modeling Data Objects version 1.0 Year 2019 prepared By: Ermias Page 13
 LOGO INFORMATI UNIT Database Administration
ON SHEET MODULE Modeling Data Objects

1. Constraints on Relationship / Multiplicity/ Cardinality Constraints

 Multiplicity constraint is the number or range of possible occurrence of an entity
type/relation that may relate to a single occurrence/tuple of an entity type/relation through a
particular relationship.
 Mostly used to insure appropriate enterprise constraints.

One-to-one relationship
 A customer is associated with at most one loan via the relationship borrower
 A loan is associated with at most one customer via borrower

E.g. Relationship Manages between Staff ands Branch

 The multiplicity of the relationship is:
 One branch can only have one manager.
 One Employee could Manages either one or no branches.

1..1 Manages 0..1

Employee Branch

One-To-Many Relationships
 In the one-to-many relationship a loan is associated with at most one customer via borrower, a
customer is associated with several (including 0) loans via borrower

Modeling Data Objects version 1.0 Year 2019 prepared By: Ermias Page 13
 LOGO INFORMATI UNIT Database Administration
ON SHEET MODULE Modeling Data Objects

E.g. Relationship Leads between Staff and Project.

 The multiplicity of the relationship:
 One staff may lead one or more.
 One project is leaf by one staff.

1..1 Leads 0..*

Employee Project

Many-To-Many Relationship
 A customer is associated with several (possibly 0) loans via borrower.
 A loan is associated with several (possibly 0) customers via borrower.

E.g.: Relationship Teaches between Instructors and Course.

 The multiplicity of the relationship

Modeling Data Objects version 1.0 Year 2019 prepared By: Ermias Page 13
 LOGO INFORMATI UNIT Database Administration
ON SHEET MODULE Modeling Data Objects

 One Instructor teaches one or more Course(s).

 One course thought by zero or more Instructor(s).

0..* Teaches 0..*

Instructor Course

Modeling Data Objects version 1.0 Year 2019 prepared By: Ermias Page 13
 LOGO INFORMATI UNIT Database Administration
ON SHEET MODULE Modeling Data Objects

Normalization
A relational database is merely a collection of data, organized in a particular manner. As the
father of the relational database approach, Codd created a series of rules called normal forms
that help define that organization
Database normalization is a series of steps followed to obtain a database design that allows for
consistent storage and efficient access of data in a relational database. These steps reduce data
redundancy and the risk of data becoming inconsistent.
NORMALIZATION is the process of identifying the logical associations between data items
and designing a database that will represent such associations but without suffering the update
anomalies which are;
1. Insertion Anomalies
2. Deletion Anomalies
3. Modification Anomalies
Normalization may reduce system performance since data will be cross referenced from many
tables. Thus de-normalization is sometimes used to improve performance, at the cost of reduced
consistency guarantees.
Normalization normally is considered as good if it is lossless decomposition.
All the normalization rules will eventually remove the update anomalies that may exist during
data manipulation after the implementation. The update anomalies are; The type of problems that
could occur in insufficiently normalized table is called update anomalies which includes;
(1) Insertion anomalies
An "insertion anomaly" is a failure to place information about a new database entry into all the
places in the database where information about that new entry needs to be stored. In a properly
normalized database, information about a new entry needs to be inserted into only one place in
the database; in an inadequately normalized database, information about a new entry may need to
be inserted into more than one place and, human fallibility being what it is, some of the needed
additional insertions may be missed.
(2) Deletion anomalies

Modeling Data Objects version 1.0 Year 2019 prepared By: Ermias Page 13
 LOGO INFORMATI UNIT Database Administration
ON SHEET MODULE Modeling Data Objects

A "deletion anomaly" is a failure to remove information about an existing database entry when it
is time to remove that entry. In a properly normalized database, information about an old, to-be-
gotten-rid-of entry needs to be deleted from only one place in the database; in an inadequately
normalized database, information about that old entry may need to be deleted from more than
one place, and, human fallibility being what it is, some of the needed additional deletions may be
missed.
(3) Modification anomalies
A modification of a database involves changing some value of the attribute of a table. In a
properly normalized database table, what ever information is modified by the user, the change
will be effected and used accordingly.
The purpose of normalization is to reduce the chances for anomalies to occur in a database.

Example of problems related with Anomalies

EmpID FName LName SkillID Skill SkillType School SchoolAdd Skill level
12 Abebe Kebede 2 SQL Database AAU Sidist_killo 5
16 Lemma Alemu 5 C++ Programming NAC Saris 6
28 Mesfin Taye 2 SQL Database AAU Sidist_killo 10
25 Abera Belay 6 VB.6 Programming Helico Piazza 8
65 Almaz Abera 2 SQL Database Helico Piazza 9
24 Teddy Tamiru 8 Oracle Database NAC Saris 5
51 Selam Dereje 4 Prolog Programming Jimma Jimma_city 8
94 Taye Gizaw 3 Cisco Networking AAU Sidist_killo 7
18 Belay Abebe 1 IP Programming Jimma Jimma_city 4
13 Yared Tesfu 7 Java Programming AAU Sidist_killo 6

Deletion Anomalies:
If employee with ID 16 is deleted then ever information about skill C++ and the type of
skill is deleted from the database. Then we will not have any information about C++ and
its skill type.
Insertion Anomalies:
What if we have a new employee with a skill called Pascal? We can not decide weather
Pascal is allowed as a value for skill and we have no clue about the type of skill that
Pascal should be categorized as.

Modeling Data Objects version 1.0 Year 2019 prepared By: Ermias Page 13
 LOGO INFORMATI UNIT Database Administration
ON SHEET MODULE Modeling Data Objects

Modification Anomalies:
What if the address for Helico is changed from Piazza to Mexico? We need to look for
every occurrence of Helico and change the value of School_Add from Piazza to Mexico,
which is prone to error.
Database-management system can work only with the information that we put explicitly
into its tables for a given database and into its rules for working with those tables, where
such rules are appropriate and possible.
Functional Dependency (FD)
Before moving to the definition and application of normalization, it is important to have an
understanding of "functional dependency."
Data Dependency
The logical associations between data items that point the database designer in the direction of a
good database design are referred to as determinant or dependent relationships.

Two data items A and B are said to be in a determinant or dependent relationship if certain
values of data item B always appears with certain values of data item A. if the data item A is the
determinant data item and B the dependent data item then the direction of the association is from
A to B and not vice versa.

The essence of this idea is that if the existence of something, call it A, implies that B must exist
and have a certain value, and then we say that "B is functionally dependent on A." We also
often express this idea by saying that "A determines B," or that "B is a function of A," or that "A
functionally governs B." Often, the notions of functionality and functional dependency are
expressed briefly by the statement, "If A, then B." It is important to note that the value B must be
unique for a given value of A, i.e., any given value of A must imply just one and only one value
of B, in order for the relationship to qualify for the name "function." (However, this does not
necessarily prevent different values of A from implying the same value of B.)
X  Y holds if whenever two tuples have the same value for X, they must have the same value
for Y
The notation is: AB which is read as; B is functionally dependent on A.

Modeling Data Objects version 1.0 Year 2019 prepared By: Ermias Page 13
 LOGO INFORMATI UNIT Database Administration
ON SHEET MODULE Modeling Data Objects

In general, a functional dependency is a relationship among attributes. In relational databases,

we can have a determinant that governs one other attribute or several other attributes.
FDs are derived from the real-world constraints on the attributes.

Example
Dinner Type of Wine
Meat Red
Fish White
Cheese Rose

Since the type of Wine served depends on the type of Dinner, we say Wine is functionally
dependent on Dinner.
Dinner  Wine
Dinner Type of Wine Type of Fork
Meat Red Meat fork
Fish White Fish fork
Cheese Rose Cheese fork

Since both Wine type and Fork type are determined by the Dinner type, we say Wine is
functionally dependent on Dinner and Fork is functionally dependent on Dinner.
Dinner  Wine
Dinner  Fork
Partial Dependency
If an attribute which is not a member of the primary key is dependent on some part of the
primary key (if we have composite primary key) then that attribute is partially functionally
dependent on the primary key.
Let {A, B} is the Primary Key and C is no key attribute.

Then if {A, B}  C and B C or A C

Then C is partially functionally dependent on {A, B}

Full Dependency

Modeling Data Objects version 1.0 Year 2019 prepared By: Ermias Page 13
 LOGO INFORMATI UNIT Database Administration
ON SHEET MODULE Modeling Data Objects

If an attribute which is not a member of the primary key is not dependent on some part of the
primary key but the whole key (if we have composite primary key) then that attribute is fully
functionally dependent on the primary key.
Let {A, B} is the Primary Key and C is no key attribute
Then if {A, B}  C and B C and A C doesn’t hold (if B cannot determine C and B cannot
determine C) Then C Fully functionally dependent on {A, B}

Transitive Dependency
In mathematics and logic, a transitive relationship is a relationship of the following form: "If A
implies B, and if also B implies C, then A implies C."
Example:
If Mr X is a Human, and if every Human is an Animal, then Mr X must be an
Animal.
Generalized way of describing transitive dependency is that:
If A functionally governs B, AND
If B functionally governs C
THEN A functionally governs C
Provided that neither C nor B determines A i.e. (B / A and C / A)
In the normal notation:

{(AB) AND (BC)} ==> AC provided that B / A and C / A

Validation and documentation

Overview
The business data model, also known as the enterprise data model, conceptual data model or
logical data model, describes the major information of interest from a business perspective.
This model is rarely created as a single project - it is typically built incrementally as needed
to support new data structures. It is the unifying model, and all of the subsequently developed
data models must be consistent with this one.

Modeling Data Objects version 1.0 Year 2019 prepared By: Ermias Page 13
 LOGO INFORMATI UNIT Database Administration
ON SHEET MODULE Modeling Data Objects

Data Modeling Validation Criteria

The business data model is independent of specific application system needs and is best
developed by the data management group with input from business subject matter experts.
Validation activities for this model include:

 Completeness for use: Is all of the data needed to support the pertinent application
development project modeled?
 Completeness: Are the business areas and concepts represented by the model completely
addressed?
 Accuracy: Does the model correctly represent the entities, attributes and business
relationships of interest? Are the optionality and cardinalities correct?
 Standards conformance: Does the model and the supporting metadata store follow the
prescribed policies and practices?
 Generalization and specialization: Does the model represent the data at the appropriate
level of abstraction?
 Structure: Does the model follow the data modeling rules for the level of normalization
(typically third normal form) used?

How to validate data modeling

The business data model, also known as the enterprise data model, conceptual data model or
logical data model, describes the major information of interest from a business perspective.
This model is rarely created as a single project - it is typically built incrementally as needed
to support new data structures. It is the unifying model, and all of the subsequently developed
data models must be consistent with this one.

Modeling Data Objects version 1.0 Year 2019 prepared By: Ermias Page 13
 LOGO INFORMATI UNIT Database Administration
ON SHEET MODULE Modeling Data Objects

Data model validation basics

With the exception of validating aspects of standards conformance and structure, all of these
questions require comparing the business needs and environment to the way they are
described within the data model. Structure and standards compliance validation should be
performed by representatives from the data management group who are experienced data
modelers. The remaining validation, however, must clearly be performed by business
representatives - but which ones and how?

The data model addresses several subject areas and crosses organizational boundaries

Model validation by the business community can be performed in several ways. From the
modeler's perspective, the most straightforward approach is to print a copy of the diagram
and walk through it with the business representatives. This approach requires the
businessperson to understand a diagram that is IT oriented, and it is appropriate when the
validation is performed by the data stewardship council or people directly involved in IT
projects.

To some businesspeople, the entity relationship diagram is like a foreign language. These
people are likely to resist learning how to read the diagram, and a different approach for
validating the model is needed. For these people, the best approach is for the data modeler to
identify the portions of the model for each person to validate and to verbally interpret the
information in the model and the associated business rules. For instance, if the model
contains the entities and relationships in the example (see figure), the businessperson should
be asked to confirm that a person is always assigned to a specific department, but that some
departments may have no employees within them.

Modeling Data Objects version 1.0 Year 2019 prepared By: Ermias Page 13
 LOGO INFORMATI UNIT Database Administration
ON SHEET MODULE Modeling Data Objects

Further, since the metadata includes definitions, the definitions for "department" and
"employee" should be confirmed. Additionally, the data elements included within each of the
two entities should be listed, and the businessperson should be asked whether or not any
significant ones have been omitted.

The business data model describes the business. While the representation of the data can be
validated by representatives from data management, the content should be validated by
businesspeople. The review process itself should be tailored to the reviewer and alternatives
to an examination of the actual diagram may be needed if the reviewer is not familiar with
the notations.

Data validation rule

Data Validation

Data Validation is in computer science, data validation is the process of ensuring that a
program operates on clean, correct and useful data. It uses routines, often called "validation
rules" or "check routines", that check for correctness, meaningfulness, and security of data
that are input to the system. The rules may be implemented through the automated facilities
of a data dictionary, or by the inclusion of explicit application program validation logic.

This article explains how to add validation rules to a database. Validation rules restrict what
users can enter in a given field, and also help ensure that your database users enter the proper
types or amounts of data.

Modeling Data Objects version 1.0 Year 2019 prepared By: Ermias Page 13
 LOGO INFORMATI UNIT Database Administration
ON SHEET MODULE Modeling Data Objects

Understand validation rules

A validation rule limits or controls what users can enter in a table field or a control (such as a
text box) on a form. Microsoft Office Access 2007 provides a number of ways to validate data,
and you often use several of those techniques to define a validation rule. You can think of
validation rules as a set of layers — you can use some or all of the layers when you need to
ensure that your users enter data properly.

 Data types    Data types typically provide the first layer of validation. When you design a
database table, you define a data type for each field in the table, and that data type
restricts what users can enter. For example, a Date/Time field accepts only dates and
times, a Currency field accepts only monetary data, and so on.
 Field sizes    Field sizes provide another layer of validation. For example, if you create a
field that stores first names, you can set it to accept a maximum of 20 characters. Doing
so can prevent a malicious user from pasting in large amounts of gibberish text into the
field, or it can prevent an inexperienced user from mistakenly entering a first and last
name in a field designed only to hold a first name.
 Table properties    Table properties provide very specific types of validation. For
example, you can set the Required property to Yes and, as a result, force users to enter a
value in a field.

You can also use the Validation Rule property to require specific values, and the Validation
Text property to alert your users to any mistakes. For example, entering a rule such as >100 And
<1000 in the Validation Rule property forces users to enter values between 100 and 1,000. A
rule such as [EndDate]>=[StartDate] forces users to enter an ending date that occurs on or after
a starting date. Entering text such as "Enter values between 100 and 1,000" or "Enter an ending
date on or after the start date" in the Validation Text property tells users when they have made a
mistake and how to fix the error.

For the steps needed to add a validation rule to a table field, see the section Validate data

during entry in table fields, later in this article.

Modeling Data Objects version 1.0 Year 2019 prepared By: Ermias Page 13
 LOGO INFORMATI UNIT Database Administration
ON SHEET MODULE Modeling Data Objects

 Input masks    You can use an input mask to validate data by forcing users to enter
values in a specific way. For example, an input mask can force users to enter dates in a
European format, such as 2007.04.14.

You can use some or all of those techniques to validate your data. Some of those features, such
as data types, become part of your database by default, but you can use other techniques, such as
field properties, validation rules, and input masks, at your discretion.

This article explains how to use the Validation Text and Validation Rule properties in table
fields, queries, and form controls. A complete discussion of other validation tools, such as data
types, field sizes, and input masks, is beyond the scope of this article.

For more information about data types and field sizes, see the article Modify or change the

data type set for a field. For more information about input masks, see the article Create an
input mask to enter field or control values in a specific format.

Types of validation rules

You can create two basic types of validation rules:

 Field validation rules    Use a field validation rule to check the value that you enter in a
field when you leave the field. For example, suppose you have a Date field, and you enter
>=#01/01/2007# in the Validation Rule property of that field. Your rule now requires
users to enter dates on or after January 1, 2007. If you enter a date earlier than 2007 and
then try to place the focus on another field, Access prevents you from leaving the current
field until you fix the problem.
 Record (or table) validation rules    Use a record validation rule to control when you
can save a record (a row in a table). Unlike field validation rules, record validation rules
refer to other fields in the same table. You create record validation rules when you need
to check the values in one field against the values in another. For example, suppose your
business requires you to ship products within 30 days and, if you don't ship within that
time, you must refund part of the purchase price to your customer. You can define a

Modeling Data Objects version 1.0 Year 2019 prepared By: Ermias Page 13
 LOGO INFORMATI UNIT Database Administration
ON SHEET MODULE Modeling Data Objects

record validation rule such as [RequiredDate]<=[OrderDate]+30 to ensure that

someone doesn't enter a ship date (the value in the RequiredDate field) too far into the
future.

If the syntax for validation rules looks cryptic, the tables in the section What you can put in a

validation rule explain the syntax and provide some example validation rules.

Where you can use validation rules

You can define validation rules for tables and for controls on forms. When you define rules for
tables, those rules apply when you import data. To add validation rules to a table, you open the
table in Design view and set various table properties. To add validation rules to a form, you open
the form in Design view and add rules to the properties of individual controls.

The steps in the section Validate data during entry in table fields explain how to add validation
rules to the properties in table fields. The steps in the section Validate data during entry in forms,
later in this article, explain how to add rules to the properties in individual controls.

What you can put in a validation rule

Your validation rules can contain expressions — functions that return a single value. You can
use expressions to perform calculations, manipulate characters, or test data. When you create
validation rules, you use expressions primarily to test data. For example, an expression can check
for one of a series of values, such as "Addiss Ababa" Or "Moscow" Or "Paris" Or
"Helsinki". Expressions can also perform mathematical operations. For example, the expression
<100 forces users to enter values less than 100. The expression ([OrderDate] - [ShipDate])
calculates the number of days that elapsed between the time an order was placed and the time it
shipped.

Modeling Data Objects version 1.0 Year 2019 prepared By: Ermias Page 13
 LOGO INFORMATI UNIT Database Administration
ON SHEET MODULE Modeling Data Objects

Validation rule reference

The following tables provide reference information for validation rules, including the syntax that
the most common rules use, links to information about using wildcard characters in your rules,
and examples that you can adapt for use with your data.

Validation rule examples

The following table provides examples of field-level and record-level validation rules, plus
explanatory validation text. You can adapt these examples to fit your content.

Validation rule Validation text

<>0 Enter a nonzero value.

Value must be zero or greater.

>=0
-or-

You must enter a positive number.

0 or >100 Value must be either 0 or greater than 100.

Enter a value with a percent sign. (For use with a

BETWEEN 0 AND 1
field that stores number values as percentages).

<#01/01/2007# Enter a date before 2007.

>=#01/01/2007# AND <#01/01/2008# Date must occur in 2007.

<Date() Birth date cannot be in the future.

StrComp(UCase([LastName]), Data in a field named LastName must be

[LastName],0) = 0 uppercase.

>=Int(Now()) Enter today's date.

M Or F Enter M for male or F for female.

LIKE "[A-Z]*@[A-Z].com" OR "[A-Z]*@[A-Z].net"

Enter a valid .com, .net, or .org e-mail address.
OR "[A-Z]*@[A-Z].org"

Enter a required date that occurs no more than 30

[RequiredDate]<=[OrderDate]+30
days after the order date.

Modeling Data Objects version 1.0 Year 2019 prepared By: Ermias Page 13
 LOGO INFORMATI UNIT Database Administration
ON SHEET MODULE Modeling Data Objects

[EndDate]>=[StartDate] Enter an ending date on or after the start date.

Syntax for common validation rules

The expressions in your validation rules don't use any special syntax. The information in this
section explains the syntax for some of the more common types of validation rules. As you
proceed, remember that expressions and functions can be very complex, and a comprehensive
discussion is beyond the scope of this article.

For more information about expressions, see the article Create an expression. For more
information about functions, see the article Functions (arranged by category).

Keep these rules in mind as you create expressions:

 Surround the names of table fields with square brackets, like so:
[RequiredDate]<=[OrderDate]+30.
 Surround dates with pound signs (#), like so: <#01/01/2007#
 Surround text values with double quotation marks, like so: IN
("Tokyo","Paris","Moscow"). Also, note that you separate items with commas, and
you place lists inside parentheses.

In addition to those rules, the following table shows the common arithmetic operators and provides
examples of how you can use them.

Operator Function Example

NOT Tests for converse values. Use before any NOT > 10 (the same as <=10).

Modeling Data Objects version 1.0 Year 2019 prepared By: Ermias Page 13
 LOGO INFORMATI UNIT Database Administration
ON SHEET MODULE Modeling Data Objects

comparison operator except IS NOT NULL.

Tests for values equal to existing members in a

IN list. Comparison value must be a comma- IN ("Tokyo","Paris","Moscow")
separated list enclosed in parentheses.

Tests for a range of values. You must use two

comparison values — low and high — and you BETWEEN 100 AND 1000 (the same as
BETWEEN
must separate those values with the AND >=100 AND <=1000)
separator.

LIKE Matches pattern strings in Text and Memo fields. LIKE "Geo*"

Forces users to enter values in the field. This is

the same as setting the Required field property to
Yes. However, when you enable the Required
IS NOT property and a user fails to enter a value, Access
IS NOT NULL
NULL displays a somewhat unfriendly error message.
Typically, your database is easier to use if you use
IS NOT NULL and enter a friendly message in
the Validation Text property.

>= #01/01/2007# AND <=#03/06/2008#

Specifies that all the data that you enter must be

AND  Note   You can also use AND to
true or fall within limits that you specify. combine validation rules. For
example: NOT "UK" AND LIKE
"U*".
Specifies that one or more pieces of data can be
OR January OR February
true.

< Less than.

<= Less than or equal to.

> Greater than.

>= Greater than or equal to.

= Equal to.

<> Not equal to.

Modeling Data Objects version 1.0 Year 2019 prepared By: Ermias Page 13
 LOGO INFORMATI UNIT Database Administration
ON SHEET MODULE Modeling Data Objects

Modeling Data Objects version 1.0 Year 2019 prepared By: Ermias Page 13
 Self-Check 1

 Answer the questions on the following questionnaire; provide the answer sheet to your
trainer.
 Check your answers by looking at the feedback sheets; ask for the assistance of the
trainer whenever necessary.
Satisfactory
Questions
Response
 The trainee should answer the following questions YES NO
No Validation rule Validation text

1 Enter a nonzero value.

2 >=0

3 Value must be either 0 or greater than

100.

4 Enter a value with a percent sign. (For

use with a field that stores number
values as percentages).

5 >=Int(Now())

6 Enter M for male or F for female.

7 LIKE "[A-Z]*@[A-Z].com" OR
"[A-Z]*@[A-Z].net" OR "[A-
Z]*@[A-Z].org"
 I. Write the Correct Answer
1. What are the ER-Model Components?
2. In ER-Modeling _____________ is represented by Oval.
3. Types of Relationships
4. In ER-Modeling Relationship represented by ___________
Shapes
5. What is DATABASE NORMALIZATION
6. The logical associations between data items that point the
database designer in the direction of a good database
design are referred to as ___________
7. What are the Data modeling validation criteria?
8. Write at least four information gathering methods in order
to Design the ER-Model

9. this Represents
10. What is Data Validation?
11. What are the three Anomalies of Database
12. ______________rule limits or controls what users can
enter in a table field or a control (such as a text box) on a
form

 The trainee’s underpinning knowledge was

[ ] Satisfactory [ ] Not satisfactory

 Feedback to Trainee:

Trainee’s Signature: Date:

Instructor’s Signature: Date:
 Answer Key

1. ER-Model Components are:-

a. Entities
b. Attributes
c. Relationships
d. Constraints
2. Attribute
3. Relationship
4. Types of Relationships
a. One-to -One Relationship
b. One-to-Many Relationship
c. Many-to-Many Relationship
5. Is a series of steps followed to obtain a database design that allows for consistent storage
and efficient access of data in a relational database.
6. Information gathering Methods

a. Interviewing end users individually and in a group

b. Questionnaire survey
c. Direct observation
d. Examining different documents
7. Derived Attributes
8. Data validation criteria
a. Completeness for use
b. Completeness
c. Accuracy
d. Standards conformance
e. Generalization and specialization
f. Structure

9. Database Anomalies
o Insertion Anomalies
 Performance Criteria

Satisfactory
Assessment Criteria
Response
The trainee will be assessed through the following criteria: YES NO
 Answered all the interview questions clearly
 Performed all activities accordingly
 Followed all instructions in the activities

Trainees’ Performance is:

[ ] Satisfactory [ ] Not Satisfactory

Feedback to Trainee:

Trainee’s Signature: Date:

Instructor’s Signature: Date: