Mada za sehemu hiiImplementing database modelling and relationshipsMada 3
- Designing relations
- Designing entity relationships
- Designing entity relationship diagrams
Entity Relationship Design
When we design a database, we first identify the things we want to store information about and then figure out how these things connect to each other. This process of finding entities and describing their relationships is called entity relationship (ER) design. It helps us organize data so that it can be stored efficiently and retrieved correctly.
An entity is a object or concept in the real world that we want to store data about. Each entity represents a distinct subject or topic.
Examples of Entities
- A student
- A product
- A customer
- An order
- A teacher
- A book
In a database, each entity becomes a table. For example, the entity "Student" would become a table called "Student" that holds all student records.
An attribute is a characteristic or detail that describes an entity. Attributes become the columns in a database table.
Example: Student Entity
For the Student entity, attributes might include:
- Student ID
- First name
- Last name
- Date of birth
- Gender
- Class
Each attribute holds a single piece of information about the entity.
A relationship describes how two entities are connected or related to each other. Relationships show how data in one table links to data in another table.
There are three main types of relationships in database design:
1. One-to-One (1:1)
One record in the first entity is related to exactly one record in the second entity.
Example: Each student has one identification card, and each ID card belongs to one student.
2. One-to-Many (1:N)
One record in the first entity is related to many records in the second entity, but each record in the second entity is related to only one record in the first.
Example: One teacher teaches many students. Each student has one class teacher.
3. Many-to-Many (M:N)
Many records in the first entity can be related to many records in the second entity.
Example: A student takes many subjects, and each subject is taken by many students.
Note: In database design, a many-to-many relationship is usually broken down into two one-to-many relationships using a junction table.
Consider a small duka (shop) in Arusha that wants to track its sales. Let us design the entity relationships for this system.
Step 1: Identify the Entities
From the problem statement, we identify the main things:
- Product
- Customer
- Order
Step 2: List Attributes for Each Entity
Product
- Product ID (primary key)
- Product name
- Unit price (TSh)
- Quantity in stock
Customer
- Customer ID (primary key)
- Customer name
- Phone number
- Address
Order
- Order ID (primary key)
- Order date
- Total amount (TSh)
- Customer ID (foreign key)
Step 3: Define Relationships
- Customer to Order: One customer can place many orders, but each order belongs to one customer. This is a one-to-many relationship.
- Order to Product: In a simple system, one order includes one product. This would be a one-to-one relationship. If we want one order to contain multiple products, we would need a junction table (OrderItem) creating a many-to-many relationship.
Entity Relationship Diagram (Simplified)
Customer (1) ----< (N) Order
This notation shows that one Customer relates to many Orders.
- Read and understand the problem statement — What data needs to be stored?
- Identify all entities — Look for nouns in the problem (student, product, order, etc.)
- List attributes for each entity — Determine what information describes each entity
- Select a primary key — Choose a unique identifier for each entity (like Student ID)
- Determine relationships — Identify how entities connect using the three types
- Draw the ER diagram — Use boxes for entities and lines for relationships
- An entity should have more than one instance. If you only have one of something, it may not need its own table.
- The primary key must have a unique value for each record. Common primary keys include ID numbers, registration numbers, or phone numbers.
- When in doubt, ask: "Can this thing exist on its own, or does it belong to another thing?" This helps identify one-to-many relationships.
A Tanzanian small business owner managing a duka in Morogoro can use entity relationship design to organize their inventory database. By identifying entities like Product, Supplier, and Sale, they can track which items are selling fast, which supplier provides the best prices, and total daily sales in Tanzanian shillings. This helps them make better decisions about what to restock and when to pay suppliers, improving both record-keeping and profit management.
Jinsi utakavyotathminiwaTathmini ya mchakato · Tathmini ya matokeo · Tathmini ya maarifa
Tathmini ya mchakato
- Identify entities and their attributes.
- Define relationships between entities (e.g., foreign keys).
- Validate the relationships.
Tathmini ya matokeo
An entity relationship that effectively represents the problem domain and shows logical connections between entities is designed.
Tathmini ya maarifa
Detailed knowledge of: Method used: The student should explain how to:
- design entity relationships.
Principles: The student should explain the principle of:
- primary keys, foreign keys, and cardinality.
Theories: The student should explain:
- The concepts and importance of relational/referential integrity
Circumstantial knowledge:
Detailed knowledge about:
- Impact of poorly defined relationships.
Swali
In entity relationship design, what is an entity?
Ingia ili kuwasilisha jibu lako na lihesabiwe katika umahiri wako.
Ingia ili kufanya mazoeziMwalimu
Umekwama? Niulize chochote kuhusu mada hii.
Ingia ili kumuuliza Mwalimu wa AI wa Sonza kuhusu swali hili.
Ingia ili kuulizaMajadiliano
Hakuna maswali bado