Chapter 2

Conceptual Design

Overview of the Database Design Process

Database design is the process of creating a structured and efficient database that supports data storage, retrieval, and
management. A well-designed database ensures data integrity, minimizes redundancy, and supports application needs.

1. Requirements Analysis

• Objective: Understand the data needs of the users and the system.

• Activities:

o Conduct interviews, surveys, and document analysis.

o Identify entities (objects) and relationships.

o Gather functional and non-functional requirements.

2. Conceptual Design

• Objective: Create a high-level model of the data without considering technical implementation.

• Tools: Entity-Relationship Diagram (ERD)

• Activities:

o Define entities, attributes, and relationships.

o Identify primary keys and cardinalities (one-to-one, one-to-many, etc.).

o Validate with users to ensure correctness.

3. Logical Design

• Objective: Translate the conceptual model into a logical model suitable for a specific type of DBMS
(e.g., relational).

• Activities:

o Convert ER diagrams into tables (relations).

o Define foreign keys and constraints.

o Normalize data to eliminate redundancy and dependency issues (1NF, 2NF, 3NF, etc.).

4. Physical Design

• Objective: Determine how data will be stored and accessed physically on the hardware.

• Activities:

o Define indexes for performance.

o Choose storage structures (e.g., heap, clustered).

o Consider partitioning and denormalization if necessary for performance.

o Estimate storage requirements.

5. Implementation
 • Objective: Create the actual database in the chosen DBMS.

• Activities:

o Write SQL scripts to create tables, constraints, and indexes.

o Set up users, roles, and permissions.

o Load initial data (if applicable).

6. Testing and Evaluation

• Objective: Ensure the database meets user requirements and performs efficiently.

• Activities:

o Conduct data validation and integrity checks.

o Test queries and transactions.

o Perform stress testing and performance tuning.

7. Maintenance and Evolution

• Objective: Keep the database reliable and up-to-date as requirements evolve.

• Activities:

o Monitor performance and logs.

o Apply patches and updates.

o Modify schema as business needs change.

Summary Diagram:

Requirements Analysis

Conceptual Design (ER Model)

Logical Design (Relational Model)

Physical Design (Storage & Indexes)

Implementation (SQL)

Testing & Evaluation

Maintenance & Evolution

Introduction to data models: -

The structure of a database is the data model. A collection of conceptual tools for describing data, data relationships, data
semantics, and consistency constraints. A data model provides a way to describe the design of a database at the physical,
logical, and view levels.

The data models can be classified into four different categories:

• E-R model
• Relational model
• Network model
• Hierarchical model

Entity Relationship Model:

What is an Entity?

An Entity represents a real-world object, concept or thing about which data is stored in a database. It
act as a building block of a database. Tables in relational database represent these entities.

Example of entities:

• Real-World Objects: Person, Car, Employee etc.

• Concepts: Course, Event, Reservation etc.

• Things: Product, Document, Device etc.

1. Entity

• Definition: A real-world object that is distinguishable from other objects.

• Types:

o Strong Entity: Can exist independently.

Example: Student, Employee

o Weak Entity: Cannot exist without a related strong entity.

Example: Dependent (depends on Employee)
 • Representation: Rectangle

2. Attribute

• Definition: A property or characteristic of an entity.

• Types:

o Key: Atomic value (e.g., Name, Age)

o Composite: Divided into sub-parts (e.g., FullName → FirstName + LastName)

o Derived: Calculated from other attributes (e.g., Age from DOB)

o Multivalued: Can have multiple values (e.g., PhoneNumbers)

▪ Representation: Oval ◯

3. Entity Set

• A collection of similar entities.

Example: All Students in a database form a Student entity set.
4. Relationship

• Definition: Association between two or more entities.

• Types:

o One-to-One (1:1)

o One-to-Many (1:M)

o Many-to-Many (M:M)

• Representation: Diamond ◇

5. Relationship Set

• A set of relationships of the same type is known as a relationship set.

• The following relationship set depicts S1 as enrolled in C2, S2 as enrolled in C1, and S3 as
registered in C3.

• Example: The relationship Enrolled between Students and Courses

Degree of a Relationship Set

The number of different entity sets participating in a relationship set is called the degree of a
relationship set.
1. Unary Relationship(Degree 1): When there is only ONE entity set participating in a relation,
the relationship is called a unary relationship Degree of relationship 1. For example, one person is
married to only one person.

2. Binary Relationship(Degree 2): When there are TWO entities set participating in a relationship,
the relationship is called a binary relationship Degree of relationship 2. For example, a Student is
enrolled in a Course.

3. Ternary Relationship(Degree 3): When there are three entity sets participating in a relationship,
the relationship is called a ternary relationship and Degree of relationship 3.

4. N-ary Relationship: When there are n entities set participating in a relationship, the relationship is
called an n-ary relationship. There is one limitation of the N-ary relationship, as there are many
entities so it is very hard to convert into an entity, rational table. So, this is very uncommon, unlike
binary which is very much popular.

6. Key Attribute

• Uniquely identifies an entity.

Example: RollNo in a Student entity

• Underlined in ER diagrams.

Cardinality in ER Model:

The maximum number of times an entity of an entity set participates in a relationship set is known
as cardinality.

Cardinality can be of different types:

1. One-to-One

When each entity in each entity set can take part only once in the relationship, the cardinality is one-
to-one. Let us assume that a male can marry one female and a female can marry one male. So the
relationship will be one-to-one.

2. One-to-Many

In one-to-many mapping as well where each entity can be related to more than one entity. Let us
assume that one surgeon department can accommodate many doctors. So the Cardinality will be 1
to M. It means one department has many Doctors.

3. Many-to-One

When entities in one entity set can take part only once in the relationship set and entities in other
entity sets can take part more than once in the relationship set, cardinality is many to one.

Let us assume that a student can take only one course but one course can be taken by many
students. So the cardinality will be n to 1. It means that for one course there can be n students but
for one student, there will be only one course.

4. Many-to-Many

When entities in all entity sets can take part more than once in the relationship cardinality is many to
many. Let us assume that a student can take more than one course and one course can be taken by
many students. So the relationship will be many to many.
Extended Features: -

Generalization, Specialization, Aggregation

• Generalization: Generalization is the process of extracting common properties from a set of

entities and creating a generalized entity from it. It is a bottom-up approach in which two or
more entities can be generalized to a higher-level entity if they have some attributes in
common.

• Specialization: In specialization, an entity is divided into sub-entities based on its

characteristics. It is a top-down approach where the higher-level entity is specialized into two
or more lower-level entities.

• Aggregation: Relationship between a relationship and an entity (higher abstraction).

Network Model:

The Network Model in a Database Management System (DBMS) is a data model that allows
the representation of many-to-many relationships in a more flexible and complex structure compared
to the Hierarchical Model. It uses a graph structure consisting of nodes (entities) and edges
(relationships) to organize data, enabling more efficient and direct access paths.

Example:

Now, in the above figure, SALES-MAN, CUSTOMER, PRODUCT, INVOICE, PAYMENT, INVOICE-
LINE are the types of records for the sales of a company. Now, as you can see in the given figure,
INVOICE-LINE is owned by PRODUCT & INVOICE. INVOICE has also two owners SALES-MAN &
CUSTOMER.

Hierarchical Model:

A hierarchical database model is a data model in which the data are organized into a tree-like
structure. The data are stored as records which are connected to one another through links. A record
is a collection of fields, with each field containing only one value. The type of a record defines which
fields the record contains. The hierarchical database model mandates that each child record has only
one parent, whereas each parent record can have one or more child records. In order to retrieve data
from a hierarchical database, the whole tree needs to be traversed starting from the root node.
 In the above-given figure, we have few students and few course-enroll and a course can be assigned

to a single student only, but a student can enroll in any number of courses and with this the relationship
becomes one-to-many.

Constraints: -

Constraints in a Database Management System (DBMS) are rules or restrictions applied to the data in
tables to ensure its validity, integrity, and consistency. They prevent invalid or corrupted data from
being entered into the database and help maintain accurate relationships between tables.

Purpose of Constraints

• Enforce data integrity.

• Maintain relationships between tables.

• Prevent accidental or incorrect data entry.

• Ensure consistent data across the database.

Types of Constraints in DBMS:

• Domain constraints

• Key constraints

• Entity Integrity constraints

• Referential integrity constraints

➢ Domain constraints: -
• Every domain must contain atomic values(smallest indivisible units) which means composite
and multi-valued attributes are not allowed.
• We perform a datatype check here, which means when we assign a data type to a column we
limit the values that it can contain. Eg. If we assign the datatype of attribute age as int, we
can't give it values other than int datatype.
 Example: -

Phone
EID Name

123456789
01 Bikash Dutta 234456678

➢ Key constraints: -

A key constraint in a Database Management System (DBMS) refers to a set of rules applied to one or
more columns in a database table to ensure the uniqueness and integrity of data. Keys are used to
uniquely identify rows in a table, and they play a fundamental role in establishing relationships
between tables. There are several types of key constraints, each serving a specific purpose:

1. Primary key
2. Unique
3. Not null
4. Check
5. Reference
6. Default
7. Foreign key

1. Primary Key Constraint:

• A primary key is a column or a set of columns that uniquely identifies each row in a table.

• The primary key constraint ensures that the values in the specified columns are unique and
not NULL.

• There can be only one primary key in a table.

Example: Let’s consider a simple example of a primary key constraint in a relational database using
a Students table:

CREATE TABLE Students (

StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT,
-- Other columns
);

In this example:

1. StudentID is the primary key for the Students table.

2. FirstName, LastName, Age, and other columns are attributes associated with each student.

Here’s how this primary key constraint works:

a. Inserting Data:

Inserting two students with unique StudentID values

INSERT INTO Students (StudentID, FirstName, LastName, Age)
 VALUES (1, 'John', 'Doe', 20);

INSERT INTO Students (StudentID, FirstName, LastName, Age)

VALUES (2, 'Jane', 'Smith', 22);

This is valid because each StudentID is unique.

-- Trying to insert a student with a duplicate S

2. Unique Constraint:

• A unique constraint ensures that all values in a column or a set of columns are unique.

• Unlike the primary key, a unique constraint allows NULL values.

Example: Suppose we have a Products table where we want to ensure that each product has a
unique product code:

CREATE TABLE Products (

ProductID INT PRIMARY KEY,
ProductCode VARCHAR(20) UNIQUE,
ProductName VARCHAR(100),
Price DECIMAL(10, 2),
-- Other columns
);

In this example:

1. ProductID is the primary key that uniquely identifies each product.

2. ProductCode is a column with a unique key constraint.

The UNIQUE constraint on the ProductCode column ensures that each value in this column must be
unique across all rows in the Products table. It means that we cannot have two products with the
same product code.

Here’s how this unique key constraint works:

a. Inserting Data:

Inserting two products with different product codes

INSERT INTO Products (ProductID, ProductCode, ProductName, Price)
VALUES (1, 'P001', 'Product A', 49.99);

INSERT INTO Products (ProductID, ProductCode, ProductName, Price)

VALUES (2, 'P002', 'Product B', 29.99);

This is valid because the product codes (‘P001’ and ‘P002’) are unique.

Trying to insert a product with a duplicate product code

INSERT INTO Products (ProductID, ProductCode, ProductName, Price)

VALUES (3, 'P001', 'Product C', 19.99);

This would result in a constraint violation error since ‘P001’ is already used as a product code.

3.Foreign Key Constraint:

 • A foreign key is a column or a set of columns in a table that refers to the primary key of
another table.

• It establishes a relationship between the two tables, enforcing referential integrity.

• The foreign key constraint ensures that values in the foreign key column(s) match values in
the referenced primary key column(s).

• The foreign key constraint ensures referential integrity, meaning that relationships between
tables are maintained, and it helps prevent inconsistencies in the data. It’s a powerful tool
for enforcing relationships between tables in a relational database.

Example: Let’s consider a simple example to illustrate the use of a foreign key constraint in a
relational database. Suppose we have two tables: Customers and Orders.

1. Customers Table:

CREATE TABLE Customers (

CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100) UNIQUE,
-- Other columns
);

2. Orders Table:

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
TotalAmount DECIMAL(10, 2),
-- Other columns
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

In this example:

1. In the Customers table, CustomerID is the primary key that uniquely identifies each
customer. It is also referenced by the foreign key in the Orders table.

2. In the Orders table, OrderID is the primary key that uniquely identifies each order. The
CustomerID column is a foreign key that establishes a relationship with the Customers table.

3. The FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) statement in the

Orders table specifies that the CustomerID column in the Orders table is a foreign key, and
it must refer to a valid CustomerID in the Customers table.

Here’s how this foreign key constraint works:

a. Inserting Data:

Insert an order referencing the customer

INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount)
VALUES (101, 1, '2024-01-06', 150.00);

This ensures that when you insert an order into the Orders table, the CustomerID must exist in the
Customers table.
 b. Updating Data:

Update the CustomerID in the Orders table

UPDATE Orders
SET CustomerID = 2
WHERE OrderID = 101;

If you try to update the CustomerID in the Orders table to a value that does not exist in the
Customers table, it will violate the foreign key constraint.

c. Deleting Data:

Delete a customer
DELETE FROM Customers WHERE CustomerID = 1;

If you try to delete a customer who has orders in the Orders table, it will violate the foreign key
constraint. Typically, you need to handle such cases by either preventing deletion or cascading the
deletion to related records.

4. NOT NULL Key Constraint

• Definition: Ensures that a column cannot contain NULL values.

• Use case: Mandatory fields like IDs, usernames, etc.

Example:

CREATE TABLE Employee (

ID INT NOT NULL,

Name VARCHAR(50) NOT NULL

);

5. CHECK Key Constraint

• Definition: Ensures that values in a column meet a specific condition.

• Use case: To restrict values within a range.

Example:

Create table student(

S_id int NOT NULL CHECK(s_id>100),

Name varchar(50) Not Null, Age int

);

6. Default Key constraint: -

It takes default values (if user won’t provide any value to a columns then default provides default
values to columns).  It won’t allow sub queries and user defined functions.
Entity Integrity Constraint: -

The Entity Integrity Constraint is essentially a subset of the Key constraint in a database. While the Key
constraint ensures that Primary Key attributes are unique and non-null, the Entity Integrity Constraint
specifically emphasizes that no attribute of a Primary Key should contain null values. This constraint
highlights the perspective that allowing null values in Primary Key attributes could lead to multiple null
entries, violating the uniqueness requirement for each tuple in the Primary Key. Therefore, the Entity
Integrity Constraint reinforces the importance of non-null values within the Primary Key to maintain
the uniqueness of each record in a relational database.

Example: Let’s consider an example to illustrate entity constraints in a database. Assume we have a
table named Employees with the following structure:

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(100) UNIQUE,
-- Other columns
);

Now, let’s insert some data to demonstrate the entity integrity constraints:

-- Valid data insertion

INSERT INTO Employees (EmployeeID, FirstName, LastName, Email)
VALUES (1, 'John', 'Doe', '[email protected]');

-- Invalid data insertion (violates NOT NULL constraint)

INSERT INTO Employees (EmployeeID, FirstName, LastName, Email)
VALUES (2, NULL, 'Smith', '[email protected]');
-- This would result in an error since FirstName cannot be NULL

-- Invalid data insertion (violates PRIMARY KEY constraint)

INSERT INTO Employees (EmployeeID, FirstName, LastName, Email)
VALUES (1, 'Jane', 'Doe', '[email protected]');
-- This would result in an error since EmployeeID must be unique

-- Valid data insertion

INSERT INTO Employees (EmployeeID, FirstName, LastName, Email)
VALUES (2, 'Alice', 'Johnson', '[email protected]');

In this example, the entity integrity constraint ensures that the EmployeeID attribute, serving as the
primary key, cannot contain null values. Attempting to insert data with a null FirstName or violating
the uniqueness of the primary key results in constraint violation errors, thereby enforcing the entity
integrity of the database.

Referential Integrity Constraint: -

Referential integrity in a database is a crucial concept ensuring data consistency among related tables
through primary and foreign keys.

The referential integrity constraint is established when a foreign key references the primary key of
another table, requiring the referencing attribute to be a subset of the referred attribute.
 This ensures that records cannot be inserted in the referencing relation unless they exist in the
referenced relation. Furthermore, any record present in the referencing relation cannot be updated or
deleted from the referenced relation, maintaining the accuracy and coherence of the relational
database.

Example: Let’s consider an example with two tables: Orders and Customers.

-- Customers table
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100) UNIQUE,
-- Other columns
);

-- Orders table
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
TotalAmount DECIMAL(10, 2),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Now, let’s perform some operations to demonstrate referential integrity:

-- Valid data insertion

Schema:
A schema defines the structure of the database: tables, fields, data types, and
relationships. Often represented diagrammatically as an ERD (Entity-Relationship Diagram).

OR

A schema defines the logical structure and organization of a database. It acts as a container or
namespace for various database objects, such as

• Overall design of the database is called as Schema.

• Database systems have several schema, partitioned according to the levels of data
abstraction.
▪ Physical Schema
▪ Logical Schema
• A schema is the complete design of database it is also known as intension.
• It is the collection of named objects.
• The names of tables, columns of each table, datatype, triggers, functions views packages
and other objects are included in the schema.
• The changes in a schema are not applied so frequently, but occasionally changes need to be
applied as the requirements of application changes.
The schema modification or alteration is known as schema revolution.
 In database management, a logical schema defines the structure of the data and its
relationships,
while a physical schema describes how that data is stored and accessed on the
underlying hardware. The logical schema focuses on the user's view of the data, independent
of physical storage, whereas the

Logical Schema:
• Focus: Defines the structure of the data, including tables, columns, data types, and
relationships between them.
• Abstraction: High-level view, independent of the specific database system or hardware.
• Purpose: Represents how users perceive the data and its organization.
• Examples: Entity-Relationship diagrams, Unified Modeling Language (UML) diagrams.

Physical Schema:

• Focus: Describes how data is physically stored on the storage devices (e.g., hard drives).

• Abstraction: Low-level view, specific to the database system and hardware.

• Purpose: Dictates storage structures, indexing strategies, and access methods.

• Examples: Data definition language (DDL) specifications, file organization, indexing

techniques.

• Tables: The fundamental units for storing data, defined with columns, data types, and
constraints.

• Views: Virtual tables based on the result set of a query, providing a simplified or restricted
view of the underlying data.

• Stored Procedures and Functions: Reusable blocks of SQL code that perform specific tasks or
return values.

• Indexes: Structures that improve the performance of data retrieval operations.

 • Triggers: Special stored procedures that automatically execute in response to data
modification events (INSERT, UPDATE, DELETE).

Syntax: -

CREATE SCHEMA schemaname

[AUTHORIZATION ownername]GO

EX: -

CREATE SCHEMA geeks_sch;

GO

Structure of Relational Databases:

In Database Management Systems (DBMS), the relational database structure is based on the relational
model. This model uses tables (relations) to store and manage data, and it defines how data is organized
and how relationships between data are maintained.

Tables (Relations)

• A table is the core structure in a relational database.

• Each table represents one entity type (e.g., Customers, Orders, Products).

Structure: -

Rows = Records / Tuples

Columns = Fields / Attributes

Example: Customers Table

CustomerID Name Email City

1 Alice [email protected] New York

2 Bob [email protected] Chicago

Case Studies on ER model:

The ER (Entity-Relationship) model is a fundamental concept in database management, used to

represent the logical structure of a database. It visually depicts entities, their attributes, and the
relationships between them

EX: -

Hospital Management System:

Example: -

Suppose you are given the following requirements for a simple database for the
National Hockey League (NHL):

• the NHL has many teams,

• each team has a name, a city, a coach, a captain, and a set of players,
• each player belongs to only one team,
• each player has a name, a position (such as left wing or goalie), a skill level, and
a set of injury
records,
• a team captain is also a player,
• a game is played between two teams (referred to as host_team and guest_team)
and has a date
Example: -

Construct an ER diagram for online Bookstore: