Database design and ER Diagrams

1) Database design is the structured process of defining, organizing, and structuring data
within a database management system (DBMS) to ensure data integrity, minimize
redundancy, and optimizing performance.

2) It involves creating conceptual, logical, and physical models to map entities, attributes,
and relationships, typically utilizing SQL for implementation.

3) The Key Aspects of Database Design

A) Data Integrity & Consistency:
Ensures accuracy and reliability of data by defining rules and constraints (e.g.,
primary/foreign keys).
B) Reduced Redundancy (Normalization):
Organizes data into subject-based tables to eliminate duplicate information.
C) Performance Optimization:
Facilitates faster data retrieval through efficient indexing and table structures
D) Scalability:
Allows the database to handle increased data volumes and user traffic effectively.

4) The Steps in the Database Design Process are:

A) Requirements Analysis:
Gather and document the specific needs of users and applications.
B) Conceptual Design:
Identify key entities (e.g., Customers, Products) and their relationships to create
an Entity-Relationship (ER) diagram.
C) Logical Design:
Map the conceptual model into a relational schema (tables, columns, keys) and
normalize the data to 3NF or higher.
D) Physical Design:
Implement the schema in a specific DBMS (e.g., MySQL, PostgreSQL),
defining data types, indexing, and storage structures.
E) Testing and Refinement:
Validate the design with sample data and optimize queries for performance.
5) The Common Techniques used in database design are:
A) Normalization:
The process of organizing data to reduce redundancy and improve data integrity
(1NF, 2NF, 3NF, BCNF).
B) Entity-Relationship (ER) Modeling:
A graphical representation of entities and their relationships to one another.
C) Data Typing:
Selecting appropriate data types (e.g., integer, varchar, date) for each attribute to
save space and ensure accuracy.
6) A well-designed database serves as the foundation for effective data management,
supporting business intelligence and analytical needs.
 Entities
1) An entity is a real-world object, person, place, or concept that is distinguishable from
others, like a Customer, a Product, a Car, a Bank Account, or a Course, often
represented as a table in a database with specific characteristics (attributes).
2) Entities can be tangible (physical, like a "Laptop") or intangible (conceptual, like
an "Order" or "Email ID")
3) Examples of entities in Databases (DBMS)
A) Tangible: Employee, Vehicle, Building.
B) Intangible: Bank Account, Enrollment Number, Product category,
Transaction.
4) An entity in DBMS represents a real-world object or concept that can be uniquely
identified and stored in a database.
5) It forms the foundation of data modeling in an Entity-Relationship (ER) model.
6) Example: A student, An employee, or bank a/c, etc. all are entities.
7) The following diagram shows entities:

Attributes and Entity sets

1) Attributes in a DBMS are the properties, characteristics, or traits that define and
describe an entity (e.g., table) in a database,
2) Attributes often representing columns in a relational model.
3) Attributes define the structure of data, such as a student's name, age, or ID, and are
classified into types like simple, composite, derived, and multi-valued.
4) The Key Types of Attributes in DBMS are:
A) Simple Attributes:
Atomic, indivisible units that cannot be broken down further (e.g., Age).
B) Composite Attributes:
Composed of multiple simple attributes (e.g., Name broken into First Name, Last
Name; Address broken into City, Street).
C) Single-Valued Attributes:
Attributes that contain only one value for a specific entity instance (e.g., Date
of Birth)
D) Multi-valued Attributes:
 Attributes that can have multiple values for a single entity (e.g., Phone Number,
Email).
E) Derived Attributes:
Attributes not directly stored but calculated from other attributes (e.g., Age derived
from Date of Birth).
F) Key Attributes (Primary Key):
Unique identifiers for an entity (e.g., Student ID).
G) Complex Attributes: A combination of composite and multi-valued attributes.
5) In Entity-Relationship (ER) models, attributes are represented by ellipses, while
composite attributes are nested ellipses, and multi-valued attributes are double
ellipses.

Entity sets
1) An entity set is a collection of similar types of entities that share the same attributes in
a database.
2) Each entity in the set represents a real-world object or concept. It can be classified into
two types:
3) The Strong Entity Set
A) A strong entity set is an independent entity with a unique primary key that
distinguishes each of its instances.
B) Example of Strong Entity includes:
a) Car Registration Number
b) Model
c) Name etc.
4) Following is the diagram for Strong Entity:

5) The Weak Entity Set

A) A weak entity set depends on a strong entity for identification. It lacks a unique
primary key and uses a partial key combined with the strong entity's key.
B) Example of Weak Entity Set includes:
 a) Laptop Color
b) RAM, etc.
6) Following diagram shows the Weak Entity Set:

7) The Entity Types in DBMS. There are two types of Entity:

A) Strong Entity Types: These are entities that exist independently and have a
completely unique identifier.
B) Weak Entity Types: These entities depend on another entity for his or her
lifestyles and do now not have a completely unique identifier on their own.
C) Example of Strong and Weak Entity Types in DMBS is:

8) The Difference Between Entity and Entity Set and Here is a comparison of Entity
and Entity Set based on various features:
 Relationships
1) In a Database Management System (DBMS), relationships are the logical associations
between different tables that define how data points are connected.
2) These connections transform isolated datasets into a cohesive system, allowing for
efficient data retrieval and maintaining data integrity.
3) The Main Types of Relationships are:
Relational databases primarily use three types of cardinality to define how records in
one table relate to records in another:
A) One-to-One (1:1)
a) Definition: Each record in Table A is linked to exactly one record in Table
B, and vice-versa.
b) Usage: Often used for security (splitting sensitive data) or normalization
purposes.
c) Example: A Person and their Passport. One person has one passport; one
passport belongs to one person.
B) One-to-Many (1:N)
a) Definition: A single record in Table A can be associated with multiple
records in Table B, but each record in Table B relates back to only
one in Table A.
b) Usage: The most common relationship in database design.
c) Example: A Department and its Employees. One department can have
many employees, but each employee works for only one
department.

C) Many-to-Many (M:N)
 a) Definition: Multiple records in Table A can relate to multiple
records in Table B.
b) Implementation: These cannot be directly linked; they require
a junction table (or bridge table) that holds foreign
keys for both tables.
c) Example: Students and Courses. A student can have in many
subjects , and a single subject can have many students

4) The Specialized Relationship Types in DBMS are:

A) Self-Referencing (Recursive): A table has a relationship with itself. For

example, an employee table where one column lists the Manager_ID, which is
the Employee_ID of another person in the same table.

B) Weak Relationship: Exists when an entity (the "weak entity") cannot be

uniquely identified by its own attributes alone and depends on a "strong
entity" for its existence (e.g., Orders and Order Items)

Relationship sets
1) In a Database Management System (DBMS), a relationship set is a collection of
relationships of the same type that exist between entities from one or more entity sets.
2) It represents how different real-world objects or concepts (entities) are associated with
each other.
3) The Key Concepts in Relationship Sets are:

A) Entity:
A real-world object or concept about which data is stored (e.g., a "Student" or a
"Course").
B) Relationship:
A single association between two or more entities (e.g., a specific student,
"Alice," is associated with a specific course, "Math").
C) Relationship Set:
The collection of all individual relationships of a specific type (e.g., the set of all
current "Enrollments" in a university).
D) Entity Set:
A collection of all entities of a given type at any given time (e.g., all students
currently in the database).
4) The Characteristics of Relationship Sets. Relationship sets are defined by the
following characteristics:
A) Degree: The number of entity sets participating in the relationship set.
a) Unary: Involves one entity set (e.g., an Employee managing
another Employee within the same table).
b) Binary: Involves two entity sets (e.g., a Student enrolling in a Course).
c) Ternary: Involves three entity sets (e.g., an Employee working on
a Project and for a specific Client).
d) N-ary: Involves 'n' entity sets.
B) Cardinality: Defines the maximum number of entity instances from one
entity set that can be associated with an entity instance in
another set through the relationship. The primary types are:
a) One-to-One (1:1): One entity in set A relates to exactly one entity in set
B (e.g., a Person and a Passport).
b) One-to-Many (1:N): One entity in set A relates to multiple entities in set
B, but each entity in B relates to only one in A (e.g.,
a Customer placing multiple Orders).
c) Many-to-Many (M:N): Multiple entities in set A can relate to multiple
entities in set B (e.g., Students enrolling in
multiple Courses). This is implemented using a
junction table in relational databases.
C) Participation Constraints: Specifies whether the participation of an entity
set a relationship is mandatory (total
participation) or optional (partial participation).
a) Total: Every entity in the set must participate in at least one
relationship instance (e.g., every Student must be enrolled in
a Course).

b) Partial: Some entities may not participate in the relationship (e.g., not
all Employees are assigned a Parking Spot).
5) The Implementation of Relationship sets in RDBMS is as follows:

A) In a relational database, relationship sets are typically implemented using primary

keys and foreign keys to link data across different tables.

B) This ensures data integrity and consistency, reduces data redundancy, and enables
efficient data retrieval using languages like SQL.