Database Design:
The steps involved
in designing a database are as follows:
1. Requirements
Collection and Analysis – Process of Collecting and analyzing the information
that is supported by the organization and providing details about how the data
is to be generated.
2. Conceptual
Database Design (Uses ER Model) – Process of Constructing a model of the data
used in an enterprise, independent of all physical considerations. Conceptual
model is the source of information for the logical design phase.
3. Logical Database
Design (Uses Relational Model) – Process of constructing a model of the data
used in an enterprise based on a specific data model, but independent of
particular DBMS.
4. Physical Database
Design – Process of DB design implementation on the secondary storage devices.
Introduction to
DataModeling
How the data is
organized in database?
There are many
basic structures that exist in a database system. They are called the database
models.
A database model
defines:
- the
logical data structure
- Data
relationships
- Data
Consistency constraints
Types of Data
Models:
1. Object based Data
Model - Entity-Relationship Model
2. Record based Data
Model – Hierarchical Model, Network Model, Relational Model, Objected Oriented
Databases
Entity-Relationship
Model:
is a collection of
real world objects called entities and their relationships. It is mainly
represented in graphical form using E-R diagrams. This is very useful in
Database design.
Hierarchical Data
Model:
defines data and relationships through hierarchy of
data values.
Relationships are represented as parent-child
relationships.
To access a
low-level record, start at the root, move down until the target record is
reached.
Disadvantages:
Many to Many
relationships can't be represented.
Data access is
slow.
Network Model:
data is
represented as records and relationships as links.
Internal
representation is done using linked lists
It is a
pointer-based model.
Data Access is
sequential.
Disadvantages:
Wastage of space
because uses a pointer based model
Data Access is
slow
Relational Model:
is the most widely used database model.
Data is
represented in table as collection of rows and columns.
Relationships are
implemented using Keys.
It uses relational
algebra of mathematical theory.
Object Oriented
Databases:
A database which
stores data as well as procedures that processes the data.
Entity
Relationship(ER) Model:
1. ER is a high-level
conceptual data model.
2. It allows us to
describe the data involved in a real-world in terms of entities and their
relationships.
3. It is widely used
to develop an initial design of the database.
4. It describes the
data as a collection of entities, relationships and attributes.
An Example of
Database Application – College Database
College database
keeps track of students, faculty, Departments and Courses organized by various
departments.
Following is the
description of College Database:
- College
contains various departments like Department of English, Department of
Computer Sciences etc. Each department is assigned by a unique ID and
Name. Some faculty members are also appointed to each department and one
of them works as Head of the Department.
- There
are various courses conducted by each department. Each Course is assigned
a unique id, name and duration.
- Faculty
information contains name, address, department, basic salary etc. A
faculty member is assigned to only one department but can teach various
courses of other departments also.
- Student's
information contain Roll No, Name, Address etc. A student can opt only for
one course.
- Parent
(Guardian) information is also kept along with each student. We keep
guardian's name, age, sex and address.
ENTITY:
An entity is an
object used to represent the things in the real world, e.g., car, table, book
etc.
An entity need not
be a physical entity, it can also represent a entity in real world e.g.,
project, loan etc.
It represents a
class of things, not any one instance.
Entity Set or
Entity Type:
A collection of a
similar kind of entities is called an Entity Set or Entity Type.
Entity Identifier:
An entity type
which can be identified uniquely based on one of the attributes values uniquely
Strong Entity:
An entity whose
existence is not depending on the existence of any other entity is called
strong entity. These entity types containing a key attribute.
Weak Entity:
An entity whose
existence is depending on the existence of other entity. These entity type does
not contain a key attribute and can not be identified independently.
ATTRIBUTE:
An attribute is a
property used to describe the specific feature of the entity.
Each attribute
contains a possible set of values that can be attached to it. This is called
the domain of the attribute. An attribute can not contain a value outside this
domain.
Types of
Attributes
Simple Attribute: The attribute
that can not be divided further into smaller units
Ex: Age, ID
Composite
Attribute: The attribute that can be divided into smaller
units and each unit contains a specific meaning.
Ex: Name can be
sub divided into FirstName, LastName, MiddleName
Single Valued
Attribute: Attributes having a single value for a particular
entity.
Ex: Age, ID
Multivalued
Attribute: Attributes that have more than one values for a
particular entity. For Multivalued attributes, we must specify the minimum and
maximum number of values that can be attached.
Ex: Phone Number,
Address, Qualification
Stored Attribute: Attributes that
are stored directly in the database
Ex: BirthDate
Derived Attribute: Attributes that
are not stored directly, but can be derived from stored attributes.
Ex: Experience (Current
Date – Date of Joining)
RELATIONSHIPS
A relationship is
an association or connection among entities.
Ex: STUDENT opts
COURSE.
Degree of
Relationship:
The number of
entities participating in a relationship is called as Degree of Relationship.
If an entity has a
relationship with itself is called Unary Relationship.
Ex: Student
marries another Student
The relationship
between two entities is called Binary Relationship.
Ex: Student opts
Course
A relationship
among three entities is called Ternary Relationship.
Ex: Customer
purchasing Item from ShopKepper.
A relationship
among 'n' entities is called N-ary Relationship.
Relationship
Cardinality
Cardinality
specifies the number of instances of an entity associated with another entity
participating in a relationship.
One-to-One: An entity in A is
associated with at most one entity in B, and an entity in B is associated with
at most one entity in A.
Ex: College has
principal, University has Vice Chancellor.
One-to-Many: An entity in A is
associated with any number of entities in B and an entity in B is associated
with at most one entity in A.
Ex: Department and
Faculty
Many-to-One: An entity in A is
associated with at most one entity in B. An entity in B is associated with any
number of entities in A.
Ex: Course and
Instructor
Many-to-Many: Entities in A and
B are associated with any number of entities from each other
Ex: Book and
Author, Course and Faculty
E-R Diagram
ER diagrams are
composed of:
- rectangles representing entity sets
- ellipses representing attributes
- diamonds representing relationship
sets.
RELATIONAL MODEL
- The
data is logically represented as tables.
- Each
table should have a unique name.
- Tables
are required to have at least one column.
- The
total number of columns or attributes that comprise a table known as
degree of the table.
- The
notion of key is the fundamental concept in relational model.
- A
Relational model allows only one primary key per table.
- A
Primary key may be composed of one or more attributes.
- In a
relation, NULL value is used to represent some missing information,
unknown.
- A NULL
value is not a zero value.
CONVERSION OF E-R
DIAGRAM TO RELATIONAL DATABASE
Following are the
set of steps used for conversion of ER diagram to a relational database.
Conversion of
entity sets:
1. For each strong
entity type, we create a relation R. The primary key of the relation R will be
one of the key attributes.
2. For each weak
entity type, we create another relation R and the key attribute of its owner
entity is also included in R. This key attribute is a foreign key attribute of
R.
Conversion of
relationship sets:
1. One-to-One
relationship: Include a primary key of partial participation table in total
participation table as a foreign key.
2. One-to-many
relationship: Include a primary key of partial participation table in total
participation table as a foreign key.
3. Many-to-many
relationship: Create a separate table and include the primary keys of both the
relations.
Try this
assignment
1. An employee works
for a department. If the employee is a manager, then s/he manages the
department. As an employee the person works for the project, and the various
departments of a company control those projects. An employee can have many
dependents. Draw an E-R diagram for the above company. Try to find out all
possible entities and relationships among them.
2. A supplier located
in only one-city supplies various parts for the projects to different companies
located in various cities. Let us name this database as “supplier-and-parts”.
Draw the E-R diagram for supplier and parts database.
3. There are multiple
banks and each bank has many branches. Each branch has multiple customers.
Customers have various types of accounts. Some Customers also had taken
different types of loans from these bank branches. One customer can have
multiple accounts and Loans. Draw an E-R diagram for the Banking Business Case
Study . Try to find out all possible entities and relationships among them.
