Unit 4: Database Design

&
Development

ALLPPT.com _ Free PowerPoint Templates, Diagrams and Charts

LO 1: Use an appropriate design tool to design a
relational database system for a substantial
problem

– The role of database systems e.g. as back-end systems, in

e-commerce, for data mining applications
– Determining user and system requirements
– Logical design for relational databases e.g. tables, data el-
ements, data types, indexes, primary/foreign keys, entity
relationship modelling, referential integrity, data normalisa-
tion to third normal form.
– Designs for data integrity, data validations, data security
and data controls.
– User interface design
– Output designs for user requirements
– Overview of object-oriented databases and their design
LO 2: Develop a fully functional relational data-
base system, based on an existing system design

– Consideration of database and platform options for sys-

tem development.
– Examination of different software development options for
developing the relational database system.
– Implementation of the physical data model based on the
logical model.
– Data stores, internal storage and external storage (e.g.
the cloud)
– Implementation of security elements in databases
– Relational databases with controls like data validation us-
ing; input masks, drop down lists, option buttons
– User interface for requirements, functionality, reliability,
consistency and performance
– Consideration of interface links with other systems e.g. in-
ternet-based applications
– Data manipulation using appropriate query tools, includ-
ing complex queries to query across multiple tables, and
using functions and formulae.
– Database maintenance and data manipulation: inserts,
updates, amendments, deletions, data backup and recov-
ery
– System reports using report writing tools and report gen-
erators, dashboards
LO3: Test the system against user and system
requirements

• Identify elements of the system that need to be

tested
• Consider data that should be used to fully test the
system.
• Match tests against user and system requirements
• Test procedures to be used: test plans, test models
e.g. white box, black box; testing documentation.
• Functional and system testing and testing the ro-
bustness of the system, including help menus, pop-
ups, hot-spots, data validation checks
LO4: Produce technical and user documentation

• Technical and user documentation and their contents.

In daily life of human, they are using many activities.

For example:
to deposit or withdraw funds in bank,
to make a hotel or airline reservation,
to access a library catalog to search for a bibliographic item,
to purchase something online—such as a book, toy, or com-
puter

In these application, they are needed to store data in relevant

form.
Manual File System

– to keep track of data

– used tagged file folders in a filing cabinet

– organized according to expected use

e.g. file per customer

– easy to create, but hard to

locate data
aggregate/summarize data
Computerized File System

– to accommodate the data growth and information need

– manual file system structures were duplicated in the

computer

– Data Processing (DP) specialists wrote customized pro-

grams to
write, delete, update data (i.e. management)
extract and present data in various formats (i.e. report)
File System Eaxmple
Drawbacks of using file systems to store data:

– Data redundancy and inconsistency

Multiple file formats, duplication of information in differ-
ent files

– Difficulty in accessing data

Need to write a new program to carry out each new task

– Data isolation — multiple files and formats

– Integrity problems
Integrity constraints (e.g. account balance > 0) be-
come part of program code
Hard to add new constraints or change existing ones
Drawbacks of using file systems to store data:

– Atomicity of updates
Failures may leave database in an inconsistent state with
partial updates carried out
E.g. transfer of funds from one account to another should
either complete or not happen at all

– Concurrent access by multiple users

Concurrent accessed needed for performance
Uncontrolled concurrent accesses can lead to inconsisten-
cies
E.g. two people reading a balance and updating it at the same time

– Security problems

Database systems offer solutions to all the above problems

Database

– is collection of related data and its metadata organized in

a structured format
– for optimized information management

Purpose of Database

– Optimizes data management

– Transforms data into information
Databases and database systems are an essential compo-
nent of life in modern society: most of us encounter several
activities every day that involve some interaction with a
database.

For example : bank, airline system

The interactions in some are examples of called traditional

database applications, in which most of the information that
is stored and accessed is either textual or numeric.
Database System vs. File System
Functions of DBMS/Database System

– Stores data and related data entry forms, report defini-

tions, etc.

– Hides the complexities of relational database model from

the user
facilitates the construction/definition of data elements and
their relationships
enables data transformation and presentation

– Enforces data integrity

– Implements data security management

access, privacy, backup & restoration
The logical model concentrates on the data requirements
and the data to be stored or where it will be stored physi-
cally.

The physical data design model involves translating the log-

ical design of the database into physical media using hard-
ware resources and software systems such as database
management systems (DBMS)
A computerized database may be created and maintained
either by a group of application programs written specifi-
cally for that task or by a database management system.
DBMS

A database management system (DBMS) is system software

for creating and managing databases.

The DBMS provides users and programmers with a system-

atic way to create, retrieve, update and manage data.
Here are some examples of popular DBMS used these days:
• MySql
• Oracle
• SQL Server
• IBM DB2
• PostgreSQL
• Amazon SimpleDB (cloud based) etc.
History

Early 1960s: first general purpose database by Charles Bach-

man from GE. Used the network data model.

Late 1960s: IBM developed Information Management System

(IMS). Used the hierarchical data model. Led to SABRE, the air-
line reservation system developed by AA and IBM. Still in use
today.

1970: Edgar Code of IBM developed the relational data model.

Led to several DBMS based on relational model, as well as im-
portant theoretical results. Code wins Turing award.

1980s: relational model dominant. SQL standard.

Late 1980s, 1990s: DBMS vendors extend systems, allowing

more complex data types (images, text).
The DBMS is a general-purpose software system that facilitates
the processes of defining, constructing, manipulating, and
sharing
databases among various users and applications.

Defining a database involves specifying the data types, struc-

tures, and constraints of the data to be stored in the database.

Constructing the database is the process of storing the data

on some storage medium that is controlled by the DBMS.

Manipulating a database includes functions such as querying

the database to retrieve specific data, updating the database
to reflect changes in the miniworld, and generating reports
from the data.

Sharing a database allows multiple users and programs to ac-

cess the database simultaneously.
Other important functions provided by the DBMS include pro-
tecting the database and maintaining it over a long period of
time.

Protection includes system protection against hardware or

software malfunction (or crashes) and security protection
against unauthorized or malicious access.

A typical large database may have a life cycle of many years,

so the DBMS must be able to maintain the database system by
allowing the system to evolve as requirements change over
time.
Characteristics of Database Management Sys-
tem

Data stored into Tables: Data is never directly stored into

the database. Data is stored into tables, created inside the
database. DBMS also allows to have relationships between
tables which makes the data more meaningful and con-
nected.

.
Characteristics of Database Management System

Reduced Redundancy: In the modern world hard drives are

very cheap, but earlier when hard drives were too expensive,
unnecessary repetition of data in database was a big problem.
But DBMS follows Normalisation which divides the data in
such a way that repetition is minimum.

Data redundancy is a condition created within a database

or data storage technology in which the same piece of data
is held in two separate places.

Data Consistency: On Live data, i.e. data that is being con-

tinuously updated and added, maintaining the consistency of
data can become a challenge. But DBMS handles it all by it-
self.
Characteristics of Database Management System

Support Multiple user and Concurrent Access: DBMS allows

multiple users to work on it(update, insert, delete data) at the
same time and still manages to maintain the data consistency.

Query Language: DBMS provides users with a simple Query

language, using which data can be easily fetched, inserted,
deleted and updated in a database.

Security: The DBMS also takes care of the security of data, pro-
tecting the data from un-authorized access. In a typical DBMS, we
can create user accounts with different access permissions, using
which we can easily secure our data by restricting user access.

DBMS supports transactions, which allows us to better handle

and manage data integrity in real world applications where
multi-threading is extensively used.
Why Use a DBMS?

• Data independence and efficient access.

• Reduced application development time.
• Data integrity and security.
• Uniform data administration.
• Concurrent access, recovery from crashes.
Purpose of DBMS

1. Data redundancy and inconsistency

2. Difficulty in new program to carry out each new task

• Same information may be duplicated in several places

• All copies may not be updated properly

3. Data isolation
• Data in different formats

• Difficult to write new application programs

• files and formats

Purpose of DBMS

4. Security problems
Every user of the system should be able to access only
the data they are permitted to see.
– E.g. payroll people only handle employee records, and
cannot see customer accounts; tellers only access ac-
count data and cannot see payroll data.
– Difficult to enforce this with application programs

5. Integrity problems
– Data may be required to satisfy constraints
– E.g. no account balance below $25.00
– Again, difficult to enforce or to change constraints with
the file-processing approach
Data Independence

• Application Program
 Data Structure
• Immunity of application to change in storage structure and access
strategy.
Data Dependence vs. Data Independence

• Data Dependent
e.g. SELECT CITY S
FROM S
WHERE ITEM = 'X'; S#
S1
• Linked list: TOP
S2
s1 s2 sn ：
S Sn
Top

if item = TOP  . item then .........

• Tree:

if item < root.data then root := root.left ..........

• Array:

if A[I] = item then ............

• Storage structure changed  program changed
Functions of the DBMS

• Data Dictionary Management,

• Data Storage Management,
• Data Transformation and Presentation,
• Security Management,
• Multi user Access Control,
• Backup and Recovery Management,
• Data Integrity Management,
• Database Access Languages and Application Program-
ming Interfaces and
• Database Communication interfaces.
1. Data Dictionary Management

• DBMS stores definitions of the data elements and

their relationships (metadata) in a data dictionary.
• So, all programs that access the data in the database
work through the DBMS.
• Additionally, any changes made in a database struc-
ture are automatically recorded in the data dictio-
nary, thereby freeing you from having to modify all of
the programs that access the changed structure.
2. Data Storage Management

• A modern DBMS system provides storage not only for the

data, but also for related data entry forms or screen defi-
nitions, report definitions, data validation rules, proce-
dural code, structures to handle video and picture for-
mats, and so on.

• Data storage management is also important for database

performance tuning.
3. Data transformation and presentation

• The DBMS transforms entered data in to required data

structures.
• For example, imagine an enterprise database used by a
multinational company. An end user in England would
expect to enter data such as July 11, 2009, as
“11/07/2009.” In contrast, the same date would be en-
tered in the United States as “07/11/2009.” Regardless of
the data presentation format, the DBMS system must
manage the date in the proper format for each country.
4. Security Management

Security rules determine which users can access the data-

base, which data items each user can access, and which
data operations (read, add, delete, or modify) the user can
perform. This is especially important in multiuser database
systems.
5. Multi User Access Control

To provide data integrity and data consistency, the DBMS

uses sophisticated algorithms to ensure that multiple users
can access the database concurrently without compromis-
ing the integrity of the database.
6. Backup and Recovery Management

Current DBMS systems provide special utilities that allow

the DBA to perform routine and special backup and restore
procedures.
Recovery management deals with the recovery of the data-
base after a failure, such as a bad sector in the disk or a
power failure.
Such capability is critical to preserving the database’s in-
tegrity.
7. Data Integrity Management

• The DBMS promotes and enforces integrity rules, thus

minimizing data redundancy and maximizing data consis-
tency.

• The data relationships stored in the data dictionary are

used to enforce data integrity.

• Ensuring data integrity is important DBMS functionality in

transaction-oriented database systems.
8. Database Access Languages and Applica-
tion Programming Interfaces

• A query language is a non procedural language—one that

lets the user specify what must be done without having to
specify how it is to be done.

• Structured Query Language (SQL) is the defacto query

language and data access standard supported by the ma-
jority of DBMS vendors.
9. Database Communication Interfaces

• Current-generation DBMS's accept end-user requests via

multiple, different network environments.

• For example, the DBMS might provide access to the data-

base via the Internet through the use of Web browsers such
as Mozilla Firefox or Microsoft Internet Explorer.
• In this environment, communications can be accomplished
in several ways:

• - End users can generate answers to queries by filling in screen

forms through their preferred Web browser.
•
- The DBMS can automatically publish predefined reports on a
Website.
•
- The DBMS can connect to third-party systems to distribute in-
• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Security and Integrity
• Data Recovery and Concurrency
• Data Dictionary
• Performance
Data Definition Language (DDL)

DDL is a syntax similar to a computer programming lan-

guage for defining data structures, especially database
schemas.

DDL statements create, modify, and remove database ob-

jects such as tables, indexes, and users.

Common DDL statements are CREATE, ALTER, and DROP.

Data Definition Language (DDL)

• ALTER TABLE
• CREATE INDEX
• CREATE TABLE
• DROP INDEX
• DROP TABLE
• CREATE USER
• ALTER USER
• DROP USER
Data Manipulation Language (DML)

A data manipulation language (DML) is a family of com-

puter languages including commands permitting users to
manipulate data in a database.

It is used for adding (inserting), deleting, and modifying

(updating) data in a database.

A DML is often a sublanguage of a broader database lan-

guage such as SQL, with the DML comprising some of the
operators in the language.
SQL

In SQL, the data manipulation language comprises the

SQL-data change statements, which modify stored data
but not the schema or database objects.

Insert
Update
Delete
Select
Functions of the DBMS

• DBMS performs several important functions that guaran-

tee the integrity and consistency of the data in the data-
base.
Advantages of the DBMS

• It lets end users and application programmers access and use the
same data while managing data integrity.

• Data is better protected and maintained when it can be shared us-

ing a DBMS instead of creating new iterations of the same data
stored in new files for every new application.

• The DBMS provides a central store of data that can be accessed by

multiple users in a controlled manner.

• It can be used to impose a logical, structured organization on

Central storage and management of data within the DBMS
provides:
• Data abstraction and independence
• Data security
• A locking mechanism for concurrent access
• An efficient handler to balance the needs of multiple ap-
plications using the same data
• The ability to swiftly recover from crashes and errors, in-
cluding restartability and recoverability
• Robust data integrity capabilities
• Logging and auditing of activity
• Simple access using a standard application programming
interface (API)
• Uniform administration procedures for data
• DBMS must perform additional work to provide these ad-
vantages, thereby bringing with it the overhead.

• A DBMS will use more memory and CPU than a simple file
storage system.

• And, of course, different types of DBMS will require differ-

ent types and levels of system resources.
Database development life cycle

• The database development life cycle has a number of stages

that are followed when developing database systems.

• The steps in the development life cycle do not necessary

have to be followed religiously in a sequential manner.

• On small database systems, the database system develop-

ment life cycle is usually very simple and does not involve a
lot of steps.
Requirements analysis

• Planning - This stages concerns with planning of entire

Database Development Life Cycle It takes into considera-
tion the Information Systems strategy of the organization.

• System definition - This stage defines the scope and

boundaries of the proposed database system.
User requirements

• referred to as user needs

• describe what the user does with the system, such as what
activities that users must be able to perform

• are generally documented in a User Requirements Document

(URD) using narrative text

• are generally signed off by the user and used as the primary
input for creating system requirements

• It is need to ensure that the requirements meet certain qual-

ity characteristics
System requirements

• are the building blocks developers use to build the system

• the traditional “shall” statements that describe what the sys-

tem “shall do.”

• are classified as either functional or supplemental require-

ments

• functional requirement specifies something that a user

needs to perform their work. For example, a system may be
required to enter and print cost estimates
Some types of supplemental requirements are

• Accessibility
• Accuracy
• Audit, control, and reporting
• Price
• Security
• Testability
• etc
Book & DVD Store outline

The Book & DVD Store is an online warehouse. The purpose of

the system is to make it possible for the customers to browse
and search products (i.e. books and DVDs) and to place orders
for these products online. Customers can choose their pre-
ferred payment method; new payment methods should be easy
to add. The organization operating the online store does not
have its own delivery solution therefore a courier company like
FedEx or DHL will perform the delivery to the customer. All
courier companies have their own software system which the
online bookstore needs to exchange data with in order to pro-
vide information for the customers about the shipment of their
newly acquired property if requested. The ordering system is
responsible for issuing invoices but there is a separate account-
ing system at the organization which will receive detailed in-
formation on each sale.
Database designing

•
Logical model - This stage is concerned with developing a
database model based on requirements. The entire design is
on paper without any physical implementations or specific
DBMS considerations.

• Physical model - This stage implements the logical model

of the database taking into account the DBMS and physical
implementation factors.
Implementation

• Data conversion and loading - this stage is concerned

with importing and converting data from the old system into
the new database.

• Testing - this stage is concerned with the identification of

errors in the newly implemented system .It checks the
database against requirement specifications.
Types of Database

Depending upon the usage requirements, there are follow-

ing types of databases available in the market
• Centralised database.
• Distributed database.
• Personal database.
• Commercial database.
• NoSQL database.
• Operational database.
• Relational database.
• Cloud database.
• Object-oriented database.
• Graph database.
1. Centralised Database

• The information(data) is stored at a centralized location

and the users from different locations can access this
data.

• This type of database contains application procedures

that help the users to access the data even from a re-
mote location.
2.Distributed Database

• The data is not at one place and is distributed at var-

ious sites of an organization.
• These sites are connected to each other with the help
of communication links which helps them to access
the distributed data easily.
• The databases which have same underlying hardware
and run over same operating systems and application
procedures are known as homogeneous DDB, for eg.
All physical locations in a DDB.
• Whereas, the operating systems, underlying hard-
ware as well as application procedures can be differ-
ent at various sites of a DDB which is known as het-
erogeneous DDB.
3.Personal Database

• Data is collected and stored on personal computers which

is small and easily manageable.
•
• The data is generally used by the same department of an
organization and is accessed by a small group of people.
4.Commercial Database

• These are the paid versions of the huge databases de-

signed uniquely for the users who want to access the in-
formation for help.

• These databases are subject specific, and one cannot af-

ford to maintain such a huge information.

• Access to such databases is provided through commercial

links.
5.NoSQL Database

• These are used for large sets of distributed data.

• There are some big data performance issues which are ef-
fectively handled by relational databases, such kind of is-
sues are easily managed by NoSQL databases.

• There are very efficient in analyzing large size unstruc-

tured data that may be stored at multiple virtual servers
of the cloud.
6.Operational Database

• Information related to operations of an enterprise is

stored inside this database.

• Functional lines like marketing, employee relations, cus-

tomer service etc. require such kind of databases.
7.Relational Databases

• These databases are categorized by a set of tables where

data gets fit into a pre-defined category.

• The table consists of rows and columns where the column

has an entry for data for a specific category and rows con-
tains instance for that data defined according to the cate-
gory.
•
• The Structured Query Language (SQL) is the standard
user and application program interface for a relational
database.

• There are various simple operations that can be applied

over the table which makes these databases easier to ex-
tend
8.Cloud Databases

• Data has been specifically getting stored over clouds also

known as a virtual environment, either in a hybrid cloud,
public or private cloud.

• A cloud database is a database that has been optimized

or built for such a virtualized environment.

• There are various benefits of a cloud database, some of

which are the ability to pay for storage capacity and
bandwidth on a per-user basis, and they provide scalabil-
ity on demand, along with high availability.
9.Object-Oriented Databases

• An object-oriented database is a collection of object-ori-

ented programming and relational database.

• There are various items which are created using object-

oriented programming languages like C++, Java which
can be stored in relational databases, but object-oriented
databases are well-suited for those items.

• An object-oriented database is organized around objects

rather than actions, and data rather than logic.

• For example, a multimedia record in a relational database

can be a definable data object, as opposed to an al-
phanumeric value.
9.Object-Oriented Databases

• Object-oriented databases follow the fundamental princi-

ples of object-oriented programming (OOP). The com-
bination of relational model features (concurrency, transac-
tion and recovery) with object-oriented principles results in
an object-oriented database model.

• The object-oriented database model (OODBM) is an

alternative implementation to that of a relational model.
An object-oriented database is similar in principle to an ob-
ject-oriented programming language. An object-oriented
database management system is a hybrid application that
uses a combination of object-oriented and relational data-
base principles to process data. That said, we can use the
following formula to outline the OODBM:
• Object-Oriented Programming + Relational Data-
base Features = Object-Oriented Database Model
#
Fundamental Features of Object-Ori-
ented Databases
Objects and Classes
Encapsulation
This hides the implementation details from
the end-users and displays only the needed
descriptions.
Inheritance
Association
Association refers to links between the
various entities of an application. In an ob-
ject-oriented database, association is de-
noted as references between various ob-
jects.
Complex Objects
Persistence

The object-oriented database allows for the

creation of persistent objects. A persis-
tent object is one that lives in computer
memory even after completing its execu-
tion. This is different from the lifespan of
normal objects which expire after execu-
tion, are destroyed immediately and freed
from memory. Object persistence solves
the database challenges of concurrency
and recovery.
Types of Database Management Systems

Large enterprises needed to build many independent data

files containing related and even overlapping data, often in
quite different formats to fulfill different purposes
Hierarchical Database Management System

• A hierarchical database is a one in which the data ele-

ments have a one-to-many relationship (1:N).

• The data are stored as records which are connected to

one another through links.

• This kind of database model uses a tree-like structure

which links a number of dissimilar elements to one pri-
mary record – the "owner" or "parent".

• They are very simple and fast.

Advantage
• can be accessed and updated rapidly because in this
model, the data structure is like that of a tree, and the re-
lationships between records are defined in advance

• allow easy addition and deletion of records

• Data at the top of the hierarchy is accessed with great

speed
Disadvantage

• Permits each child a relationship with only one parent,

and relationships or linkages between children are not
permitted

• making queries very slow

• The lower the required data in the hierarchy, the longer it

takes to retrieve it

• Adding a new field or record requires the entire database

to be redefined
Network Database Management System
• A network database model is one in which multiple mem-
ber records or files are linked to multiple owner files and
vice versa.

• Allows each record to have multiple parents as well as

multiple child records, which can be visualized as a web-
like structure of networked records.

• Instead of using a single-parent tree hierarchy, the net-

work model uses set theory to provide a tree-like hierar-
chy with the exception that child tables are allowed to
have more than one parent.

• supports many-to-many relationships and can be visual-

ized as a cobweb or interconnected network of records
Advantages

• conceptually simple and easy to design

• data access is easier and more flexible as compared to

a hierarchical model

• does not allow a member to exist without a parent

• it can handle more complex data because of its many-

to-many relationship

• easier to navigate and search for information in a net-

work database

• isolates the management programs from the complex

Disadvantages

• All the records in the database need to be maintained

using pointers, making the whole database structure
very complex

• The insertion, deletion and updating operations of

any record require an adjustment of a large number
of pointers

• Difficult to use by first time users

• Structural changes to the database are very difficult

to implement.
Relational Databases Management System

• in which data is stored in the form of tables, using rows

and columns

• it easy to locate and access specific data within the data-

base
• It is “relational” because the data within each table are re-
lated to each other.

• tables or files containing data are called relations (tuples),

and are defined by rows (or records), and columns (or at-
tributes) referred to as fields

• Each table has a key field that mainly identifies each

record (row), and on the basis of which records in different
tables are related (or linked)
• it possible to run queries that need to retrieve data from
multiple tables simultaneously

• provide a visual representation of the data.

• Examples include Oracle Database, MySQL, Microsoft SQL

Server, and IBM DB2.

• Some of these programs support non-relational data-

bases, but they are primarily used for relational database
management.
• The older hierarchical data management systems are be-
ing replaced by relational database management.

• Reliable for large mainframe systems as well as worksta-

tions and personal computers
Advantages

• Any data organized as tables consisting of rows

and columns is much easier to understand
• Huge amounts of data are segmented, making
management and retrieval easier and faster
• Different tables from which information has to
be linked and extracted can be easily managed
• Security and authorization control can also be
implemented more easily
• Multiple users can access the database simul-
taneously
• offers better backup and recovery options
Disadvantages

• its reliance on machine performance

• If the number of tables between which relationships are to

be established is large, then the performance in respond-
ing to the SQL queries is affected

• The overall cost of implementing RDBMS because the re-

quired hardware is complex and software expensive
Object-Oriented Database Management
System

• A recent development in database technology is the in-

corporation of the object concept
• all data are objects
• Objects may be linked to each other by an “is-part-of” re-
lationship to represent larger, composite objects
• Multimedia databases, in which voice, music, and video
are stored along with the traditional textual information,
provide a justification for viewing data as objects.
• Become more important because of their structure is
most flexible and adaptable
Advantages

• combine the object oriented principle with the database

management principle to give a hybrid system that is
more powerful than the conventional relational database
management system
• working with objects in the programming language is
similar to working with objects in the database
• Each object is the database is identified by an object
identifier called the OID which is generated by the sys-
tem
• is more powerful than the RDBMS if you are used to ob-
ject oriented programming
• Any update or deletion is done to the in-memory object
and these changes can later be saved to the database
• this helps to avoid the frequent access to the database
while updating, deleting, etc
Disadvantages

• it lacks a theoretical foundation which makes it compara-

ble to pre-relational systems

• is relatively limited because we do not yet have the level

of experience that we have with traditional systems

• The increased functionality provided by the OODBMS

makes the system more complex than the traditional
DBMSs

• OODBMSs do not provide adequate security mechanisms

– the database manager cannot grant access rights on in-
dividual objects or classes.
What is Database Design?

• Database Design is a collection of processes that facilitate

the designing, development, implementation and mainte-
nance of enterprise data management systems

• It helps produce database systems

to meet the requirements of the users
to have high performance

• The main objectives of database designing are to produce

logical and physical designs models of the proposed data-
base system.
Why Database Design is Important ?

Database designing is crucial to high performance

database system.

Different approach needed for different types of data-

bases

Avoid data redundancy & ensure data integrity

Apart from improving the performance, properly designed

database are easy to maintain, improve data consistency
and are cost effective in terms of disk storage space.

Poorly designed database generates errors

The logical model concentrates on the data requirements
and the data to be stored independent of physical consid-
erations.

It does not concern itself with how the data will be stored
or where it will be stored physically.

The physical data design model involves translating the

logical design of the database onto physical media using
hardware resources and software systems such as data-
base management systems (DBMS).
Two Types of Database Techniques
– Normalization
– ER Modeling
A good database design is a pre-requisite
to work with the database in an efficient
way as it reduces the maintenance efforts,
minimizes chances of error-prone tasks
Different Phases of Database Designing

• #1) Collecting Requirements and their analysis: In

this phase, the requirements are collected and their anal-
ysis is done.
• #2) Initiating Conceptual Design: After the require-
ments are collected, a creation of conceptual schema for
the database takes place, the result of which is an ERD i.e.
• #3) Initiating Logical Design: Here, designing of rela-
tional schemas takes place. This simply means that the
concept of primary keys and foreign keys comes into the
picture as a part of this phase.
• #4) Moving towards Normalization Process: After
completion of the logical design, the normalization
process is followed to remove the redundancy from the
database
• #5) Physical Design: Finally, the physical design of the
database takes place.
Top Database Designing Tools

#1) Visual Paradigm ERD Tools

This is the top-most Database Design tool that helps in
designing of the database by following a powerful ap-
proach of Entity Relationship Diagram Tools (ERD).

Visual Paradigm ERD Tools are mainly used for personal

usage focusing on non-commercial purposes.

This is best suitable for UML Modeling.

#2) Vertabelo

This is a Commercial product which is preferably used by

Business Customers.

For smaller projects, it's free service can be used but ob-
viously, users need to pay for working with the profes-
sional one which is quite complex as well in its usage.
#3) dModelAid

It is an online modeling tool for documenting a complex

database design in a simple, interactive diagram with a
focus on ease of use from a-z and improved productivity.

It’s a commercial product by a Swedish company target-

ing business customers.

However, can get started with a fully featured free plan.

#4) Lucidchart

This is one of the most widely used Database Design tools

which will help you in quickly creating database diagrams
online with its collaborative database design tools.

Lucidchart supports cloud-based feature and hence it

does not requires any downloads and its web-based ser-
vice allows its users to collaborate for working on real-
time projects.

This is very popular among business for diagramming

purposes.

This was established in 2008 and has come a long way

and has also received Start2Cloud Editor’s Choice
#5) SQL Server Database Modeler

This is one of the commonly used Database Design tools

that is used to design your database online by importing
the existing database.

This follows the concepts of Forward Engineering and

Reverse Engineering.
• Unlike some of the other expensive commercial data-
base tools, this simply needs a browser without any
need for database engines and database modeling
tools.
• This tool is suitable for both small as well as large busi-
ness.
#6) DeZign for Databases

This is one of the very useful Database Designing

tools which supports the feature of Data Modeling
for database professionals.

This tool is from a Company named ‘Datanamic’

which was found in 1999 and is appreciated for pro-
viding easy-to-use tools for database designing.

This supports more than 15 databases and is best if a

user wants to buy for him/her personal usage.
#7) Erwin Data Modeler

This is also one of the very useful database designing

tools which support the feature of Data Modeling by un-
locking the value out of the Enterprise Data.

This is available in Free Community Edition and is consid-

ered to be one of the best enterprise solutions which
are available in the market.
#8) Aqua Data Studio ER Modeler

This supports Entity Relationship Modeling by provid-

ing a feature to design physical models for all major Rela-
tional Database Management Systems.

This uses Forward and Reverse Engineer mechanisms

to model entities and to convert them into the form of
SQL Scripts.

This supports a 14-day free download

Aquafold is one of the leading US Software Company

which deals in building innovative solutions to over-
come the real-world problems and provides a great sup-
port service.
#9) DbWrench

This tool supports synchronization along with designing

of the databases with the guarantee to save the time of
the users in handling multiple database tasks.

This tool also has the feature to forward and reverse en-
gineer the databases.

DbWrench is a tool from Nizara Systems which deals in

producing reliable and easy to use the software.

Various versions of DbWrench has been released in 2017,

the latest being 4.1.
#10) IBM InfoSphere Data Architect

This is a database designing solution tool from IBM

that helps in discovering, modeling and standardizing the
distributed data.

This is built on IDE platform.

This simplifies and accelerates the integration design for

Business Intelligence and helps the users in every step of
the designing process.

This also facilitates forward and reverse engineering for a

variety of databases with the features of UML Modeling and
designing of ERDs.
#11) DbDesigner.net

• This is one of the popular database designing tools

which supports online designing of the Database
Schema.

• This provides the facility to design a database schema

without taking the pain of writing SQL which is consid-
ered as a great feature of this tool.

• This is a Web-Application and is very simple to use

but this is not ideal for business solutions.

• This supports various database engines like PostgreSQL,

SQLite, Oracle, MySQL, and MSSQL.
LOGICAL DATABASE DESIGN
Logical database design is the process of deciding how to arrange the
attributes of the entities in a given business environment into database
structures, such as the tables of a relational database. The goal of logical
database design is to create well structured tables that properly reflect
the company's business environment. The tables will be able to store
data about the company's entities in a non-redundant manner and foreign
keys will be placed in the tables so that all the relationships among the
entities will be supported. Physical database design, which will be
treated in the next chapter, is the process of modifying the logical
database design to improve performance.

The process of deciding how to arrange the attributes of the entities in

the business environment into database structures, such as the tables of a
relational database.

The goal is to create well structured tables that properly reflect the com-
pany’s business environment.
ER diagrams are a visual tool which is helpful to
represent the ER model.

It was proposed by Peter Chen in 1971 to create a

uniform convention which can be used for rela-
tional database and network.

He aimed to use an ER model as a conceptual

modeling approach.
Entity relationship diagram displays the rela-
tionships of entity set stored in a database.

In other words, we can say that ER diagrams

help you to explain the logical structure of
databases.

At first look, an ER diagram looks very similar

to the flowchart.

However, ER Diagram includes many special-

ized symbols, and its meanings make this
model unique.
Example ER Diagram
Facts about ER Diagram Model:

• ER model allows you to draw Database Design

• It is an easy to use graphical tool for modeling

data
• Widely used in Database Design

• It is a GUI representation of the logical structure

of a Database

• It helps you to identifies the entities which exist

in a system and the relationships between
those entities
Why use ER Diagrams?

• to define terms related to entity relationship modeling

• to provide the preview the connection between ta-

bles, what fields are going to be on each table

• to describe entities, attributes, relationships

• ER diagrams are translatable into relational tables which

allows for building databases quickly

• can be used as a blueprint for implementing data in

specific software applications

• is allowed to communicate with the logical structure of

Components of the ER Diagram
Components of the ER Diagram
Entity

It may be a physical thing or simply a fact about

the enterprise or an event that happens in the
real world.

It is anything in the enterprise that is to be repre-

sented in our database.

An entity can be place, person, object, event or a

concept, which stores data in the database.

The characteristics of entities are must have an

attribute, and a unique key.
Examples of entities:

• Person: Employee, Student, Doctor

• Place: College, Building
• Object: Machine, Item, and Car
• Event: Sale, Registration, Renewal
• Concept: Account, Course
•
A college may have some departments. All these de-
partments employ various lecturers and offer several
programs.
Some courses make up each program.
Students register in a particular program and enroll in
various courses.
A lecturer from the specific department takes each
course, and each lecturer teaches a various group of stu-
Relationship

Relationship is nothing but an association among

two or more entities.

E.g., Tom works in the Chemistry department.

For example:

• You are attending this lecture

• I am giving the lecture
• Just look entities, we can classify relationships
according to relationship-types:
• A student attends a lecture
• A lecturer is giving a lecture.
Recursive Relationship
• If the same entity participates more than
once in a relationship it is known as a re-
cursive relationship. In the below exam-
ple an employee can be a supervisor and
be supervised, so there is a recursive re-
lationship.
Weak Entities

A weak entity is a type of entity which doesn't

have its key attribute.
It can be identified uniquely by considering the
primary key of another entity.
For that, weak entity sets need to have participa-
tion.
Weak Relationship

A weak relationship depicts the connection be-

tween an entity type that is weak and its corre-
sponding owner.
Attributes

• It is a single-valued property of either an entity-

type or a relationship-type.
• For example, a lecture might have attributes:
time, date, duration, place, etc.
• An attribute is represented by an Ellipse
Types of Attributes Description

Simple attributes can't be divided any fur-

Simple attribute ther. For example, a student's contact num-
ber. It is also called an atomic value.

It is possible to break down composite at-

tribute. For example, a student's full name
Composite attribute
may be further divided into first name, sec-
ond name, and last name.

This type of attribute does not include in

the physical database. However, their val-
ues are derived from other attributes
Derived attribute present in the database. For example, age
should not be stored directly. Instead, it
should be derived from the DOB of that
employee.
Multivalued attributes can have more than
one values. For example, a student can
Multivalued attribute
have more than one mobile number, email
address, etc.
Multivalued Attribute
• If an attribute can have more than one
value it is called a multi-valued attribute.
It is important to note that this is different
from an attribute having its own at-
tributes. For example, a teacher entity
can have multiple subject values.
Derived Attribute
• An attribute based on another attribute.
This is found rarely in ER diagrams. For
example, for a circle, the area can be de-
rived from the radius.
Cardinality

• Defines the numerical attributes of the re-

lationship between two entities or entity
sets.
• Different types of cardinal relationships
are:
• One-to-One Relationships
• One-to-Many Relationships
• May to One Relationships
• Many-to-Many Relationships
E
1. One to One Relationship
• When a single instance of an entity is as-
sociated with a single instance of another
entity then it is called one to one rela-
tionship. For example, a person has only
one passport and a passport is given to
one person.
One student can register for numerous
courses. However, all those courses have a
single line back to that one student.
2. One to Many Relationship
• When a single instance of an entity is as-
sociated with more than one instances of
another entity then it is called one to
many relationship. For example – a cus-
tomer can place many orders but a order
cannot be placed by many customers.
For example, one class is consisting of mul-
tiple students
3. Many to One Relationship
• When more than one instances of an en-
tity is associated with a single instance of
another entity then it is called many to
one relationship. For example – many
students can study in a single college but
a student cannot study in many colleges
at the same time.
or example, many students belong to the
same class.
4. Many to Many Relationship
• When more than one instances of an en-
tity is associated with more than one in-
stances of another entity then it is called
many to many relationship. For example,
a can be assigned to many projects and a
project can be assigned to many stu-
dents.
For example, Students as a group are asso-
ciated with multiple faculty members, and
faculty members can be associated with
multiple students.
• ER- Diagram Notations
• ER- Diagram is a visual representation of
data that describe how data is related to
each other.
• Rectangles: This symbol represent entity
types
• Ellipses : Symbol represent attributes
• Diamonds: This symbol represents relation-
ship types
• Lines: It links attributes to entity types and
entity types with other relationship types
• Primary key: attributes are underlined
• Double Ellipses: Represent multi-valued at-
tributes
• (1) The conversion of E-R diagrams into
relational tables.

• (2) The data normalization technique.

• (3) The use of the data normalization

technique to test the tables resulting
from the E-R diagram conversions.
• The music database stores details of a
personal music library, and could be used
to manage your MP3, CD, or vinyl collec-
tion. Because this database is for a per-
sonal collection, it’s relatively simple and
stores only the relationships between
artists, albums, and tracks. It ignores the
requirements of many music genres, mak-
ing it most useful for storing popular mu-
sic and less useful for storing jazz or clas-
sical music.
• The collection consists of albums.
• An album is made by exactly one artist.
• An artist makes one or more albums.
• An album contains one or more tracks
• Artists, albums, and tracks each have a name.
• Each track is on exactly one album.
• Each track has a time length, measured in sec-
onds.
• When a track is played, the date and time the
playback began (to the nearest second) should
be recorded; this is used for reporting when a
track was last played, as well as the number of
times music by an artist, from an album, or a
track has been played.
• There’s no requirement to capture composers, gr
• The university database stores details
about university students, courses, the
semester a student took a particular
course (and his mark and grade if he
completed it), and what degree program
each student is enrolled in.
• The university offers one or more programs.
• A program is made up of one or more courses.
• A student must enroll in a program.
• A student takes the courses that are part of her program.
• A program has a name, a program identifier, the total
credit points required to graduate, and the year it com-
menced.
• A course has a name, a course identifier, a credit point
value, and the year it commenced.
• Students have one or more given names, a surname, a
student identifier, a date of birth, and the year they first
enrolled. We can treat all given names as a single object—
for example, “John Paul.”
• When a student takes a course, the year and semester he
attempted it are recorded. When he finishes the course, a
grade (such as A or B) and a mark (such as 60 percent) are
recorded.
• Each course in a program is sequenced into a year (for ex-
• The flight database stores details about
an airline’s fleet, flights, and seat book-
ings. Again, it’s a hugely simplified ver-
sion of what a real airline would use, but
the principles are the same.
• The airline has one or more airplanes.
• An airplane has a model number, a
unique registration number, and the ca-
pacity to take one or more passengers.
• An airplane flight has a unique flight
number, a departure airport, a destina-
tion airport, a departure date and time,
and an arrival date and time.
• Each flight is carried out by a single air-
plane.
• A passenger has given names, a sur-
name, and a unique email address.
• A passenger can book a seat on a flight.