Mada za sehemu hiiImplementing database modelling and relationshipsMada 3
- Designing relations
- Designing entity relationships
- Designing entity relationship diagrams
Designing Relations in Databases
A relation in a database is a way of organizing data into tables where each table represents a real-world entity (like a person, product, or transaction). Relations show how data in one table connects to data in another table. When we design relations properly, we avoid problems like duplicated data and make it easy to find and update information.
In database design, we use three main types of relationships between tables:
One-to-One (1:1) Relationship
One record in the first table relates to only one record in the second table, and vice versa. For example, each student in a school has one unique identification number, and each ID number belongs to one student.
One-to-Many (1:N) Relationship
One record in the first table can relate to many records in the second table, but each record in the second table relates to only one record in the first table. For example, one teacher can teach many students, but each student has one main teacher.
Many-to-Many (M:N) Relationship
Records in both tables can relate to many records in the other table. For example, a student can take many subjects, and each subject can be taken by many students. This relationship usually requires a third connecting table.
To create relationships between tables, we use keys:
- Primary Key (PK): A unique identifier for each record in a table. No two rows can have the same primary key value.
- Foreign Key (FK): A column in one table that refers to the primary key in another table, creating the connection between them.
Follow these steps when designing database relations from a real-world scenario:
- Identify the entities – Determine the main objects or things the database will store information about (e.g., Student, Subject, Teacher).
- Define attributes – List the details or properties for each entity (e.g., Student has name, age, and address).
- Choose primary keys – Select a unique identifier for each entity (e.g., StudentID for Student).
- Identify relationships – Determine how the entities connect to each other (one-to-many, many-to-many, or one-to-one).
- Add foreign keys – Place foreign keys in the table to establish the relationships identified in step 4.
A Tanzanian secondary school wants to track books and students who borrow them. Design the database relations.
Step 1: Identify entities
- Book
- Student
- Borrower (connection between Book and Student)
Step 2: Define attributes
- Book: BookID (PK), Title, Author, Category
- Student: StudentID (PK), Name, Class
- Borrower: BorrowerID (PK), StudentID (FK), BookID (FK), BorrowDate, ReturnDate
Step 3 and 4: Relationships
- One Student can borrow many Books (1:N) – but we need the Borrower table to track each borrowing.
- One Book can be borrowed by many Students over time (1:N through Borrower).
- The Borrower table acts as a connecting table, storing foreign keys from both Book and Student.
Step 5: Foreign keys
- In Borrower table: StudentID references Student(StudentID)
- In Borrower table: BookID references Book(BookID)
This design ensures that:
- Each book is recorded once in the Book table
- Each student is recorded once in the Student table
- Every borrowing is tracked with the correct book and student linked together
In Tanzania, market vendors and small shop owners can use database relations to manage inventory. For example, a duka in Arusha can create tables for Products (with product name, price, and quantity) and Sales (recording what was sold, when, and which product). The ProductID links the two tables so the owner can quickly see which items are running low and track daily sales, helping with restocking decisions and budgeting with Tanzanian shillings.
Jinsi utakavyotathminiwaTathmini ya mchakato · Tathmini ya matokeo · Tathmini ya maarifa
Tathmini ya mchakato
- Identify entities
- Identify attributes and keys.
- Apply normalization rules to organize data into relations.
- Create relational tables.
Tathmini ya matokeo
Database relations are correctly designed.
Tathmini ya maarifa
Detailed knowledge of:
Method used: The student should explain how to:
- process of designing relations.
Principles: The student should explain the principle of:
- Normalization and data redundancy
Theories: The student should explain:
- The concepts of entities, attributes, relationships
- Types of relationships (1:1, 1:M, M:N).
Circumstantial knowledge:
Detailed knowledge about:
- Data integrity and dependencies.
Swali
Which statement correctly describes a Primary Key in a database table?
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