How to Perform Data Modeling?

Data modeling is a process of designing a structure for your database to ensure

1. Data integrity.
2. Reduce Redundancy
3. Support efficient querying

Key Components of Data Modeling:

1. Entities: Represents objects or things in the database.
2. For example Student, Course, Department
3. Attributes:
4. Details or characteristics of an enitity.
5. Examples: For a student entity, Name, age & address.
6. Relationships:
7. Define how entities are related.
8. Students enrolls in course (Many to Many)
9. Cardinality: Specifies the number of instances of one entity related to another
10. One to One
11. One to Many
12. Many to Many

Data modeling involves conceptual, logical and physical design phase.

1. Conceptual Data Model: DBMS independent.
2. High level data model
3. ER diagram: old & prevalent method of data modelling
4. UML Diagram: More recent approach, used for the whole software engineering process.
5. Low level data model
6. Relational Datamodel
7. Find relational low level data model?
8. Logical Data Model: DBMS specific
9. for example taking conceptual data model and converting into mysql.
10. Physical Data Model

High level Modeling:

These are conceptual representation using diagrams like Entity-Relationship diagram.

Tools: ER Diagram, UML class diagram

Let's see how we can design a conceptual data model i.e both high level and low level data models with examples.

then we can build the low level conceptual modal i.e relational model:

This is what the ER diagram represents:

Let's see what an ER diagram represents?

1. Company makes this product
2. Company employs person.
3. Company has name and stock price
4. Person buys products
5. Person has name, ssn and address

Any time an entity has many to many relationship with another entity, we need to introduce another table to reduce the data redundancy.

Each entity must have a key attribute

Symbols of Cardinality:

how can we create low level data model from this ER diagram?

Scenario 1: Library Management System

Problem Statement:

A library maintains records of books, members, and borrowing information:

• Each book has an ID, title, author, and genre.
• Each member has an ID, name, and contact information.
• Members can borrow multiple books, and each book can be borrowed by multiple members.
• Borrowing includes details such as the borrow date and return date.

Scenario 2: E-Commerce System

Problem Statement:

An e-commerce system tracks customers, products, and orders:

• Customers have an ID, name, and email.
• Products have an ID, name, description, and price.
• Each order has an ID, order date, and is associated with one customer.
• Each order includes one or more products, and a product can appear in multiple orders.

Scenario 3: University Database

Problem Statement:

A university tracks departments, courses, instructors, and students:

• Departments have a code, name, and location.
• Courses have a code, name, and are offered by a department.
• Instructors have an ID, name, and belong to a department.
• Students have an ID, name, and enroll in courses taught by instructors.

So what are the takeaways?

How can we design a conceptual data models?

1. Focus on entities, draw a table of entity first
2. Build there attributes along with Pk
3. Once you have all the entities, now focus on the relationship.
4. Is it 1 to 1 ?
5. is it 1 to many?
6. Take the PK of 1 entity and create it as FK in many entity
7. is it many to many?
8. create a new table for this kinda relationship
9. Once the entities are drawn and relationship are drawn then we can create low level data modeling.