What is Database Design?
Database Design
is the process of organizing data into tables
so that data can be stored, managed, and retrieved
efficiently.
Simple Definition
Database Design is the process of deciding
what data to store, how to store it, and how different data is related.
--------------------------------------------------------------------------------------------------------
Real-Life Example
Suppose you want to create a College Management
System.
Before creating tables in Oracle SQL,
you must decide:
- What
information should be stored?
- Which
tables are required?
- How
are the tables related?
This planning process is called Database
Design.
What is an E/R Model?
E/R stands for Entity-Relationship
Model.
It is a graphical way to
design a database before creating tables.
Simple Definition
An E/R Model shows the entities (objects),
their attributes (properties), and the relationships between them.
Components of E/R Model
The E/R Model has three main components:
1.
Entity
2.
Attribute
3.
Relationship
1. Entity
Definition
An Entity is a real-world
object about which information is stored.
Simple Meaning
Entity = Object
Examples
- Student
- Employee
- Customer
- Product
- Book
- Teacher
College Example
Entity: Student
2. Attribute
Definition
An Attribute describes
the properties of an entity.
Simple Meaning
Attribute = Details of an object
Example
Entity: Student
Attributes:
Student ID
Student Name
Course
Mobile No
Address
Email
Diagram
Student
/ | \
ID Name
Course
3. Relationship
Definition
A Relationship shows how
two or more entities are connected.
Simple Meaning
Relationship = Connection between entities
Example
Student
↓
Enrolled In
↓
Course
Diagram
Student -------- Enrolled In -------- Course
Practical Example (College Database)
Suppose a college wants to store
- Student
details
- Course
details
- Faculty
details
Step 1: Identify Entities
Student
Course
Faculty
Step 2: Identify Attributes
Student
Student_ID
Student_Name
Mobile
Course_ID
Course
Course_ID
Course_Name
Duration
Faculty
Faculty_ID
Faculty_Name
Department
Step 3: Identify Relationships
Student
|
Enrolled In
|
Course
Faculty
|
Teaches
|
Course
Complete E/R Diagram
Enrolled In
Student -------------------- Course
| |
Student_ID Course_ID
Name Course_Name
Mobile Fees
Email Duration
Teaches
Faculty ------------------- Course
|
Faculty_ID
Faculty_Name
Department
Database Design Using the E/R Model
After creating the E/R Diagram, convert
it into database tables.
Student Table
|
Student_ID |
Student_Name |
Mobile |
Course_ID |
|
101 |
Rahul |
9876543210 |
C101 |
|
102 |
Priya |
9876543222 |
C102 |
Course Table
|
Course_ID |
Course_Name |
Fees |
|
C101 |
BCA |
25000 |
|
C102 |
MCA |
40000 |
Faculty Table
|
Faculty_ID |
Faculty_Name |
Department |
|
F01 |
Dr. Sharma |
Computer Science |
SQL Example: Create Course Table
CREATE TABLE Course
(
Course_ID VARCHAR2(10) PRIMARY KEY,
Course_Name VARCHAR2(30),
Fees NUMBER(8,2)
);
Create Student Table
CREATE TABLE Student
(
Student_ID NUMBER(5) PRIMARY KEY,
Student_Name VARCHAR2(50),
Mobile VARCHAR2(10),
Course_ID VARCHAR2(10),
FOREIGN KEY (Course_ID)
REFERENCES Course(Course_ID)
);
Insert Records
INSERT INTO Course
VALUES('C101','BCA',25000);
INSERT INTO Student
VALUES(101,'Rahul','9876543210','C101');
Display Data
SELECT * FROM Student;
Output
|
Student_ID |
Student_Name |
Mobile |
Course_ID |
|
101 |
Rahul |
9876543210 |
C101 |
Another Example (Library Management System)
Entities
- Book
- Member
- Librarian
Attributes
Book
- Book_ID
- Book_Name
- Author
Member
- Member_ID
- Name
- Mobile
Relationship
Member
|
Borrows
|
Book
Another Example (Hospital Management System)
Entities
- Patient
- Doctor
Relationship
Doctor
|
Treats
|
Patient
Steps for Designing a Database Using an E/R Model
Step 1 Identify the entities.
Example:
Student
Course
Faculty
Step 2 Find the attributes.
Example
Student_ID
Student_Name
Course
Step 3 Identify the relationships.
Example
Student
Enrolled In
Course
Step 4 Draw the E/R Diagram.
Step 5 Convert the E/R Diagram into database tables.
Step 6 Create the tables using SQL.
Advantages of E/R Model
- Easy
to understand.
- Helps
design the database before implementation.
- Reduces
data redundancy.
- Clearly
shows relationships.
- Improves
database structure.
- Easy
to convert into SQL tables.
- Saves
development time.
Difference Between Entity,
Attribute, and Relationship
|
Entity |
Attribute |
Relationship |
|
Real-world object |
Property of an entity |
Connection between entities |
|
Student |
Name, Roll No |
Student enrolls in Course |
|
Employee |
Salary, Department |
Employee works in Department |
|
Book |
Title, Author |
Member borrows Book |
Real-Life Example (Online Shopping System)
Imagine an online shopping website:
Entities
- Customer
- Product
- Order
Attributes
- Customer:
Customer_ID, Name, Email
- Product:
Product_ID, Product_Name, Price
- Order:
Order_ID, Order_Date
Relationships
- Customer
places an Order.
- An
Order contains Products.
Customer ---- Places ---- Order ---- Contains ----
Product
-----------------------------------------------------------------------------------
Here, we define how to create E-R diagram?
for example, College Management system,
-----------------------------------------------------------------------------------
Quick Revision
|
Topic |
Simple Meaning |
Example |
|
Database Design |
Planning how data will be stored |
College Management System |
|
E/R Model |
Diagram used to design a database |
Student–Course Diagram |
|
Entity |
Object |
Student |
|
Attribute |
Property of an object |
Student_Name |
|
Relationship |
Connection between entities |
Student enrolls in Course |
|
Database Design using E/R |
Convert the E/R diagram into tables |
Student and Course tables |
-----------------------------------------------------------------------------------
One-Line Memory Trick
- Entity
= Object (Student)
- Attribute
= Details (Student Name, Mobile)
- Relationship
= Connection (Student Enrolls in Course)
- E/R
Model = Blueprint of the Database before
creating tables
Or follow my blog from the below link
)){kind=link}
0 Comments