2: Entities and Attributes
unit will cover following topics:
for designing a Database.
Database Keys (Primary Keys, Foreign Keys, Candidate Keys)
the completion of this unit the learner will be able to :
are the steps in designing
Identify data Requirement.
Identify entities and relationships.
Identify the key attributes
Identify the key attributes
Normalize the data.
Resolve the relationships.
Verify the design
database design links:
and state the expectations of the application that is being developed. Identify
the general activities the database will be used for.
For example, if a client wants to
develop a database to keep track of information about his company's employees, he
needs to determine what
type of employee information should be stored in the database? What data will be retrieved
from the database? Who will retrieve it? What
types of reports are
expecting to be generated by the application? Will
the reports be produced daily, weekly,
monthly, or annually? How often will the data be accessed?
all the data that you will need to keep track in the system. The data will describes
the entities and answers the questions who, what, where, when, and why.
List the available data for each entity, as it seems appropriate that can
used later for testing. For example, employee name,
address, department etc.
down the high-level activities
into lower-level activities in order to identify all the
activities that is to be performed by the application. For example,
a high-level activity
such as "maintain employee information" can be broken down
into categories such
adding new employees,
change existing employee information, delete terminated employees
at the business description and list all the business rules that need to be
our example, one of the business rules might be that a department can have one
and only one manager. Another one may be that a manager can have one or
more employee and so on. These rules will be used to build the structure of
entities and relationships
requirements have been defined, the next step is to identify the entities, its
attributes and the
relationships between entities.
the list of activities, identify the subject areas you need to maintain
information about. These
will become tables. An entity
may be an object with a physical existence - a particular person,
car, house, or employee - or it may be an object with a conceptual existence
- a company,
a job, or a university course.
example, to develop a company's database for maintaining information
on employees, the
application should be able to store and provide data on employee such as
when was the
employee was hired; is the employee still with the company; if the
employee has left the
company when did he leave the company; which department does employee
for; who is his/her manager; what is his/her skill level etc. In
this example, the entities
are company, department, employee, manager.
is a property that describes an entity. In
the above example, the employee is the entity and employee’s name,
salary and job etc are the attribute.
step is to determine the relationships
between the entities. Give each the relationship
a name. In the above
example, there is a relationship between departments
and employees, so we will name this relationship dept_employee. Type
key is one or more attribute that uniquely identifies an entity. Every entity
in relational database must
have at least one candidate key but it is possible that some
may have two or more.
For example, his social security number, employee number or driver
license number may identify an employee. All of them
candidate keys for an employee.
key is an attribute, or set of attributes, that allows each information
an entity to be uniquely identified. Every
entity in a relational database must have
a primary key. For
example, if an employee has attribute such as name, phone
number, social security number, department name etc., then
the employee number or social security number can be used as a primary key.
one cannot use both attributes as a primary key since one attribute
itself can identify the employee. Using both candidate key sas the primary
cause redundancy and increase error in data.
For any given relationship or entity, primary key is one of the
and the remainder (if any) are called alternate keys.
entities are related to
each other through foreign
foreign key references a
particular attribute of
an entity containing the corresponding primary key. For
example, an employee entity with employee number as its primary key for
an employee and
department entity with department number as its primary key
for department information
can be related to each other through employee number.
Therefore, employee number will be a foreign key for department entity where
as the employee number will be a primary key for the employee entity.
Assignment: Read chapter 3 and
Assignment: Write the
requirements for the project. Use the worksheet
to help you
write up the requirements for the project.