UNIT II DATABASE DESIGN
Entity-Relationship model – E-R Diagrams –
Enhanced-ER Model – ER-to-Relational
Mapping – Functional Dependencies – Non-loss
Decomposition – First, Second, Third Normal
Forms, Dependency Preservation – Boyce/Codd
Normal Form – Multi-valued Dependencies and
Fourth Normal Form – Join Dependencies and
Fifth Normal Form
• Database Design
– defined as a set of procedures or collection of tasks involving
various steps taken to implement a database.
Following are some critical points to keep in mind to achieve a
good database design:
– Data consistency and integrity must be maintained.
– Low Redundancy
– Faster searching through indices
– Security measures should be taken by enforcing various
integrity constraints.
– Data should be stored in fragmented bits of information in the
most atomic format possible.
– However, depending on specific requirements above criteria
might change. But these are the most common things that
ensure a good database design.
• What are the Following Steps that can be taken
by a Database Designer to Ensure Good Database
Design?
• Step 1: Determine the goal of your database, and
ensure clear communication with the
stakeholders (if any). Understanding the purpose
of a database will help in thinking of various use
cases & where the problem may arise & how we
can prevent it.
• Step 2: List down all the entities that will be
present in the database & what relationships exist
among them.
• Step 3: Organize the information into different tables such that
no or very little redundancy is there.
• Step 4: Ensure uniqueness in every table.
– The uniqueness of records present in any relation is a very crucial part
of database design that helps us avoid redundancy. Identify the key
attributes to uniquely identify every row from columns.
– You can use various key constraints to ensure the uniqueness of your
table, also keep in mind the uniquely identifying records must
consume as little space as possible & shall not contain any NULL values.
• Step 5: After all the tables are structured, and information is
organized apply Normalization Forms to identify anomalies that
may arise & redundancy that can cause inconsistency in the
database.
Primary Terminologies Used in Database Design
• Redundancy
• Schema
• Records/Tuples
• Indexing
• Data Integrity & Consistency
• Data Models
• Functional Dependency
• Transaction
• Schedule
• Concurrency
Database Design Lifecycle
1. Requirement Analysis
2. Logical & Physical Design
1.Logical Data Model Design
1. Data Models
2. Entity
3.Relationships
4. Attributes:
5. Normalization
2.Physical Design of Data Modelconsistency & generate the actual
database)
3. Data Insertion and testing for various integrity Constraints
Logical Data Model Design
(structure of data and what relationship exists )
1.Data Models
2. Entity
3.Relationships
4. Attributes
5. Normalization
1. Data Models:
-> visual modeling technique
->used to get a high-level overview of our database.
->to understand the needs and requirements of our database
->by defining the design of our database through diagrammatic
representation.
Ex: model, Network model, Relational Model, object-oriented data model.
2. Entity:
• Entities are objects in the real world,
• have certain properties.
• 2 types of entities:
– Strong and weak entity,
– weak entity do not have a key attribute to identify them
• Weak entity example: Loan -> Loan will be given to
a customer (which is optional) & the load will be
identified by the customer_id to whom the lone is
granted.
3. Relationships:
• How data is logically related to each other?
• A relationship can be further categorized into – unary, binary, and
ternary relationships.
• Unary: the associating entity & the associated entity both are the
same. Ex: Employee Manages themselves, and students are also given
the post of monitor hence here the student themselves is a monitor.
• Binary: common relationship that you will come across while
designing a database.
Ex: Student is enrolled in courses, Employee is managed by different
managers, One student can be taught by many professors.
• Ternary: In this, we have 3 entities involved in a single relationship. Ex:
an employee works on a project for a client. Note that, here we have 3
entities: Employee, Project & Client.
4. Attributes:
• Attributes are nothing but properties of a
specific entity that define its behavior.
– For example, an employee can have unique_id,
name, age, date of birth (DOB), salary,
department, Manager, project id, etc.
5. Normalization:
• After all the entities are put in place and the
relationship among data is defined,
• we need to look for loopholes or possible
ambiguities that may arise as a result of CRUD
operations.
• To prevent various Anomalies such as
INSERTION, UPDATION, and DELETION
Anomalies.
• CRUD- CREATE,READ,UPDATE,DELETE
