Unit 2: Entities and Attributes


 

OBJECTIVE

 

This unit will cover following topics:

  • Steps for designing a Database.

  • Entities and Attributes

  • Relational Database Keys (Primary Keys, Foreign Keys, Candidate Keys)

At the completion of this unit the learner will be able to :

  • Define the attributes of an entities, keys and relationships between entities and attributes


 

Designing a Database

 

Following are the steps in designing a database:

1. Identify data Requirement.

2. Identify entities and relationships.

2.1. Identify entities

2.2. Identify attributes

2.3. Identify relationships

2.4. Identify the key attributes

3. Identify the key attributes

4. Normalize the data.

5. Resolve the relationships.

6. Verify the design

 

Other database design links:

 

http://www.siue.edu/~dbock/cis564/ermodel.htm

http://cairns.cs.jcu.edu.au/teaching/Subjects/cp2500/1998/Lecture_Notes/er_model/er_diagrams.html

http://www.ucop.edu/pathways/plan/erdnote.html

http://cs.une.edu.au/~comp382/Slides/LECTURE12/sld001.htm

http://www.utexas.edu/cc/database/datamodeling/dm/design.html

http://www.nova.edu/~girdhar/MMIS630/SystemRequirements.html

Identify data requirements

 

Gather requirement 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? 

 

Identify supporting data

List 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.

 

               

 

Break 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 as  adding new employees,  change existing employee information, delete terminated employees etc.

Identify business rule

Look at the business description and list all the business rules that need to be followed. In 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 the database.

Identify entities and relationships

 

Once the requirements have been defined, the next step is to identify the entities, its attributes and the relationships between entities.

 

Identify entities

uFor 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. 

 

For 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 work for; who is his/her manager; what is his/her skill level etc.  In this example, the entities are company, department, employee, manager.

Identify Attributes

An Attribute is a property that describes an entity. In the above example,  the employee is the entity and employee’s name, age,  address, salary and job etc are the attribute. 

Identify Relationships

Next 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 of relationship.

Identify Key Attributes

 

Candidate Key

A candidate 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 are considered candidate keys for an employee.

Primary Key

A primary key is an attribute, or set of attributes, that allows each information for 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, employee number, social security number, department name etc.,  then the employee number or social security number can be used as a primary key.  However, one cannot use both attributes as a primary key since one attribute by itself can identify the employee. Using both candidate key sas the primary key could cause redundancy and increase error in data. 

 

Note: For any  given relationship or entity, primary key is one of the candidate keys and the remainder (if any) are called alternate keys.

Foreign Key

The entities are related to each other through foreign keys. uA 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.


Reading Assignment: Read chapter 3 and 4.


Project Assignment: Write the requirements for the project. Use the worksheet to help you

                    write up the requirements for the project.