Bahir Dar Poly Technic

College
Department of ICT
Database Administration Level III
• MODULE TITLE: Designing A Database
• MODULE CODE: ICT DBA3 M03 0811
• NOMINAL DURATION: 40 hrs

By
Dejenie A.

October, 2015 E.C

 Learning Outcomes
• At the end of the module the trainee will cover
the following LOs:
LO1 Determine database requirements
LO2 Develop logical data model
LO3 Design data structures
LO4 Design queries, screens and reports
LO5 Design access and security systems
LO6 Confirm database design
O1 Determine database requirements
 Conducting user need analysis to determine database functionality
 Identifying technical requirements
 Developing conceptual model of the database
 Submitting conceptual model to the client for review
 Evaluating and changing the client feedback
LO2 Develop logical data model
 Identifying attributes
 Determining data types
 Undertaking normalization of attributes
 Developing Entity Relationship to clarify cardinality of relationships.
 Normalizing data and entity relationships diagrams
 Forwarding documentation to client
 LO3 Design data structures
 Confirming primary and foreign keys for tables
 Reviewing client business rules
 Identifying referential integrity constraints
 Establishing DBMS constraints
 Developing validation rules for data
 Designing indexes
 Developing data dictionary
LO4 Design queries, screens and reports
 Designing user interface for database
 Designing queries based on requirements
 Designing output reports based on requirements
 Comparing physical design with conceptual model/user needs
 Incorporating changes
LO5 Design access and security systems
 Reviewing business security plan
 Designing password and access system
 Identifying multiple-user requirements
 Developing client access profiles
LO6 Confirm database design
 Identifying database back-up and recovery requirements
 Developing and documenting database back-up and recovery
procedures
 Submitting database and documentation to client for final approval
 LO1: Determine Database Requirements
• Introduction
– Database Design is a collection of processes that facilitate the
designing, development, implementation and maintenance of
enterprise data management systems.
– Designing a database is a very important phase in the database life
cycle, which precedes all other phases except the requirements
collection and the analysis.
– Properly designed database are easy to maintain, improves data
consistency and are cost effective in terms of disk storage space.
– If the database design is created merely intuitively and without any
plan, the resulting database will most likely not meet the user
requirements.
– Another consequence of a bad database design is data redundancy,
which in itself has two disadvantages:
• The existence of data anomalies and
• The use of an unnecessary amount of disk space.
 Conducting user need analysis to
determine database functionality
• The first task in database design is to determine the
database functionality.
• You will need to meet with the client in order to
determine what the database needs to do.
• The required functionality may already be specified
in the requirements document.
• In that case you need to confirm that this document
truly represents what needs to be done.
• Requirements documents may not contain enough
information for you to start your design.
• If this is the case you will need to conduct a user
needs analysis.
• A user needs analysis involves discovering and
documenting the goals a user has and the
capabilities needed from technology to assist the
user in meeting those goals.
• The activities involved in a user needs analysis
include:
– Understanding the target audience
– Understanding their typical tasks
– Understanding their specific constraints
• The techniques used to elicit this information are
usually a combination of observational techniques,
including:
– Interviews
– Surveys
– Consulting with experts
• The results provide user interface objectives,
system requirements.
• In a user needs analysis you will identify business
rules and associations that relate to the database
that you will design.
• Example: business rules for a library system
• Business rules about borrowing:
– There is no maximum number of books that can be
borrowed
– Books must be returned within 4 weeks
– Borrowers can borrow a maximum of 4 videos
– Videos must be returned within 2 weeks
 Identifying Technical Requirements
• A requirement is a statement that specifies what
an intended product should do, or how it should
perform.
• Technical requirements, are the factors required to
deliver a desired function or behavior from a
system to satisfy a user’s standards and needs.
• Technical requirements can refer to systems
like software, electronic hardware devices or
software-driven electronic devices.
• The factors considered in technical requirements include
accessibility, adaptability, usability, auditability,
maintainability and performance.
• The combination of factors and the individual emphasis of
each to most effectively meet the needs of users are
determined through a consultation process.
• The other side of requirements analysis is functional
requirements, which define the desired output or behavior of
a system.
• Though a system may achieve the desired output or behavior
without meeting technical requirements, it will likely be
unusable.
• For example, a program might not meet reasonable
performance goals. On the other hand, if a system does not
meet functional requirements, it can have all the
performance in the world but can’t deliver the expected
 Developing conceptual model of the database

• Modeling implies identifying and defining entities

and their relationships for a business solution.
• It requires a good understanding of the desired
business outcome and is the foundation for
creating a robust software solution.
• The different model types have different levels of
detail and are used at different stages of the
software development process.
• Database modeling traditionally includes a
well established three tiered approach:
1. Conceptual Level– this documents the basic
entities of a proposed system and relationships
between them.
2. Logical Level – this specifies entities and their
relationships without implementation details.
3. Physical Level – this defines the database
structure for a technology specific format (a
DBMS).
 Conceptual Modeling
• A conceptual data model usually just includes the main
concepts (entities) required to store information and the
relationships that exist between these entities.
• We don’t usually include any details about each piece of
information.
• We can consider the conceptual stage as an initial model,
without all the details required to create a database.
• Conceptual data models are represented using the well-
known entity-relationship diagrams (ERD).
• There a ER Diagram is an important top-down analysis
technique which is used to show the structure of the
data used by the system.
• Below we can see a very simple conceptual data
model that represents authors, books, and
publishers.
 LO2 Develop logical data model
• A logical data model establishes the structure of
data elements and the relationships among
them.
• The logical data model serves as a blueprint for
used data.
• The logical data model takes the elements of
conceptual data modeling a step further by
adding more information to them.
• A logical data model has three main components:
– Entities: Each entity represents a set of things, persons,
or concepts relevant to a business
– Relationships: Every relationship represents an
association between two of the above entities
– Attributes: Each attribute is a descriptive piece,
characteristic or any other information that is useful to
further describe an entity.
– ER diagrams are used to model and design relational
databases in conceptual and logical data model .
 Entity Relationship Diagram(ERD)
• Entity Relationship (ER) Diagram is a type of flowchart that
illustrates how “entities” such as people, objects or concepts
relate to each other within a system.
• ER Diagrams are most often used to design or debug
relational databases in the fields of software engineering,
business information systems, education and research. It is
also also known as ERDs or ER Models, they use a defined set
of symbols such as rectangles, diamonds, ovals and
connecting lines to depict the interconnectedness of entities,
relationships and their attributes.
• Peter Chen developed ER modeling for database design in
the 1970s.
• This diagram also called Chen ER diagram.
• The components and features of an ER diagram
– ER Diagrams are composed of entities, relationships
and attributes.
– They also depict cardinality, which defines
relationships in terms of numbers.
• Entity
– A definable thing—such as a person, object, concept
or event—that can have data stored about it. Think
of entities as nouns. Examples: a customer, student,
car or product.
– Typically shown as a rectangle in ERD.
• Entity type: A group of definable things, such as
students or athletes, whereas the entity would
be the specific student or athlete. Other
examples: customers, cars or products.
• Entity set: Same as an entity type, but defined
at a particular point in time, such as students
enrolled in a class on the first day.
• Entity categories: Entities are categorized as
strong, weak .
• A strong entity can be defined solely by its own
attributes, while a weak entity cannot.
Chen ER diagram Symbols for Entity

Strong Entity

Weak Entity
• Relationships: Relationships are used to
document the interaction between two entities.
• Relationships are usually verbs such as assign,
associate, or track and provide useful
information that could not be discerned with
just the entity types. Weak Relationships are
connections between a weak entity and its
owner.
• Relationship describes how entities interact.
For example, the entity “Carpenter” may be
related to the entity “table” by the relationship
“builds” or “makes”. Relationships are
represented by diamond shapes and are
labeled using verbs.
Attribute
• An attribute is a property, trait, or characteristic of
an entity, relationship, or another attribute.
• For example, the attribute Inventory Item Name is
an attribute of the entity Inventory Item.
• An entity can have as many attributes as
necessary. Meanwhile, attributes can also have
their own specific attributes. For example, the
attribute “customer address” can have the
attributes number, street, city, and state.
• These are called composite attributes. Note that
some top level ER diagrams do not show attributes
for the sake of simplicity.
• Several types of attributes occur in the ER
model: simple versus composite, single valued
versus multivalued, and stored versus derived.
• First we define these attribute types and
illustrate their use via examples.
Composite versus Simple (Atomic) Attributes
• Attributes that are not divisible are called
simple or atomic attributes.
– E.g. Age, gender
• Composite attributes can be divided into
smaller subparts, which represent more basic
attributes with independent meanings.
– For example, the Address attribute of the EMPLOYEE
entity shown in can be subdivided into Street_address,
City, State,
– Composite attributes can form a hierarchy; for example,
Street_address can be further subdivided into three
simple component attributes: Number, Street, and
Apartment_number.
• Single-Valued versus Multivalued Attributes.
• Most attributes have a single value for a
particular entity; such attributes are called
single-valued.
– For example, Age is a single-valued attribute of a
person.
• A multivalued attribute may have lower and
upper bounds to constrain the number of values
allowed for each individual entity.
– In some cases an attribute can have a set of values
for the same entity for instance, a colors attribute
for a car, or a College_degrees attribute for a person.
• Such attributes are called multivalued.
Stored versus Derived Attributes
• Stored : not possible to derive or compute
– E.g. Name, Address
• Derived: The value may be derived (computed) from
the values of other attributes.
• E.g. Age (current year – year of birth)
– Length of employment (current date- start date)
– Profit (earning-cost)
• G.P.A (grade point/credit hours)
Null Values
• NULL applies to attributes which are not applicable or
which do not have values.
• You may enter the value NA (meaning not applicable)
• Value of a key attribute can not be null.
 Types of Relationship
• There are three types of relationships between entities
• One-to-one: - one instance of an entity (A) is associated with
one instance of another entity (B).
• Example: - the manages relationship between department
and employee
• President to country and husband to wife are examples of one-
to-one relationship.
• One-to-many: - one instance of an entity (A) is associated with
many instances of another entity (B), but for one instance of
entity (B) there is only one instance of entity (A).
• Example: - the has relationship between department and
employee
• Country to people, mother to children, department to students,
and people to religion are of one-to- many relationships.
• Many-to-many: - one instance of an entity )A) is
associated with zero, one or many instances of
another entity (B), and one instance of entity (B)
is associated with zero, one or many instances of
entity (A).
• Example: - the works_on relationship between
employee and project , students to course,
employee to projects, and people to language are
of many-to- many relationship.
 ERD
• Shows entities and the relationship that link
them
• The entities represent the data items needed
by the system and the relationships shows
how the entities are related to one another
• Entity type can be defined as a group of
objects with the same properties which are
identified by the enterprises as having an
independent existence
 ERD
• Entity: real-world object or thing with an
independent existence and which is distinguishable
from other objects.
– Examples are a person, car, customer, product, gene,
book etc.
• Attributes: an entity is represented by a set of
attributes (its descriptive properties), e.g., name,
age, salary, price etc.
• Attribute values that describe each entity become a
major part of the data eventually stored in a
database.
 ERD
• With each attribute a domain is associated,
– i.e., a set of permitted values for an attribute.
– Possible domains are integer, string, date, etc.
• Entity Type: Collection of entities that all have the
same attributes,
– e.g., persons, cars, customers etc.
• Entity Set:
– Collection of entities of a particular entity type at any
point in time;
– entity set is typically referred to using the same name as
entity type.
ERD