Chapter Three
Conceptual Database Design and E-R
Modeling
Database Development Life cycle(DDLC)
There are several steps in designing a database system.
The major steps in database design are;
1. The planning and analysis is an investigation phase, during
which all the needed information are gathered and analyzed.
Problem is analyzed and the following points are identified
Objects
Interaction between objects
User Forms and Reports
Example on Problem Analysis
The problem is to design a database system for “XYZ Software Share
Company” based on the following information.
The company runs various projects with a total of 68 full-time
employees and over 120 part-time employees.
A project has a unique id and a name that may be designed for a
new software development or for a release of a new version of
software that had been developed by the company.
The projects are having start date, due date, complete date and
status that describe their progress. Every project is lead by a senior
manager organized into teams of five to eight programmers
coordinated by a team leader.
Conti…
The owners of the projects are the customers of the company.
A single customer can own one or more projects.
The customers have unique id, name and address.
The company is organized into departments that are identified by a
unique name and lead by department heads. A department head
can only lead a single department in his/her employment by the
company.
An employee can only belong to one department. Every employee is
identified by an Id, a name, an address, and a position. In addition
full-time employees have monthly salary and allowance rate; and
part-time employees have contract period and hourly rate. Working
schedule of both full-time employees and part-time employees is
maintained on weekly bases.
Conti…
2. Design: in database development more emphasis is given to
this phase.
The phase is further divided into three sub-phases.
Conceptual Design: concise description of the data, data type,
relationship between data and constraints on the data.
Logical Design: a higher level conceptual abstraction with selected
specific data model to implement the data structure.
Physical Design: physical implementation of the logical design of
the database with respect to internal storage and file structure of the
database.
Conti…
3. Implementation: the testing and deployment of the
designed database for use.
4. Operation and Support: administering and maintaining
the operation of the database system and providing support
to users.
Database Design
Database design describe
how the data should be perceived at different levels and
Finally how it is going to be stored in a computer system.
In general, one has to go back and forth between
these tasks to refine a database design.
Conti…
In developing a good design, one should answer such
questions as:
What are the relevant Entities for the Organization
What are the important features of each Entity
What are the important Relationships
What are the important queries from the user
What are the other requirements of the
Organization and the Users
Conceptual Design
Conceptual design revolves around
Discovering, analyzing organizational and user data requirements
It is the source of information for logical phase.
Mostly uses Entity relationship model to describe the data at this le
The important activities are to identify
Entities
Attributes
Relationships
Constraints
The Entity Relationship (ER) Model
Entity-Relationship modeling is used to represent
conceptual view of the database.
It is popular because
It is very simple to create and read
It can be used directly to create a relational model and
transform its elements into database elements.
Conti…
The main components of ER Modeling are:
Entities
Corresponds to entire table, not row
Represented by Rectangle
Attribute
Represents the property used to describe an entity or a
relationship
Represented by Oval
Conti…
Relationships
Represents the association that exist between entities
Represented by Diamond
Constraints
Represent the constraint in the data
Cardinality and Participation Constraints
Developing an E-R Diagram
To identify the main component of ER modeling, there are
different set of methods used during the analysis phase.
These include information gathered by the following methods.
Interviewing end users individually and in a group
Questionnaire survey
Direct observation
Examining different documents
Conti…
The basic E-R model is graphically depicted and presented
for review(examination).
The process is repeated until it have fulfill a fair
representation of the organization’s activities and functions.
The ER data model views the real world as a set of basic
entities and relationships among these them.
Conti…
Relationships between entities indicate access from one entity
to another .
i.e It is possible to access one entity occurrence from another
entity occurrence
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.
A strong entity is one that does not rely on other entities
for identification.
A weak entity is one that relies on other entities for
identification.
Example
Strong entity:- Student can exist without needing any other
entity in the schema
Course also can exist independently.
Strong entity have primary key.
Weak Entity:- does not have sufficient attributes for unique
identification of its record called partial discriminator key.
Conti…
Connected entities are called relationship participants.
Attributes are represented by OVALS and are connected to
the entity by a line.
Conti…
A derived attribute is indicated by a DOTTED LINE(….)
Primary keys are underlined.
Conti…
Example
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
Mapping of ER diagram to Relational Tables
There are some points for converting ER to the Tables
In the given ER diagram, LECTURE, STUDENT, SUBJECT and
COURSE forms individual tables.
All single-valued attribute becomes a column for the table.
In the STUDENT entity, STUDENT_NAME and STUDENT_ID
form the column of STUDENT table.
Similarly, COURSE_NAME and COURSE_ID form the column of
COURSE table and so on.
Conti…
A key attribute of the entity type represented by the primary
key.
In the given ER diagram, COURSE_ID, STUDENT_ID,
SUBJECT_ID, and LECTURE_ID are the key attribute of the
entity.
Conti…
The multivalued attribute is represented by a separate table.
In the student table, a hobby is a multivalued attribute.
So it is not possible to represent multiple values in a single
column of STUDENT table.
Hence we create a table STUD_HOBBY with column name
STUDENT_ID and HOBBY.
Conti…
Composite attribute represented by components.
In the given ER diagram, student address is a composite
attribute.
It contains CITY, PIN, DOOR#, STREET, and STATE.
In the STUDENT table, these attributes can merge as an
individual column.
Conti…
Derived attributes are not considered in the table.
In the STUDENT table, Age is the derived attribute.
It can be calculated at any point of time by calculating the
difference between current date and Date of Birth.
Using these rules, you can convert the ER diagram to
tables and columns and assign the mapping between the
tables.
Table structure for the given ER diagram is as below
