Mada za sehemu hiiBuilding structures of the softwareMada 3
- Designing system architecture
- Designing data models
- Designing user interface
Designing Data Models
When we build an information system, we need to decide how to organise and store data properly. Data modelling is the process of creating a visual plan that shows what data we need, how it is related, and how it will be stored in a database. Think of it like drawing a map before building a house – it helps everyone understand the structure before construction begins.
Data modelling is a structured process that translates system requirements into a well-organised database design. It involves creating visual representations that illustrate how data elements interact. Good data modelling ensures that:
- Data is stored efficiently without unnecessary repetition
- Information can be retrieved quickly
- Different parts of the system share the same understanding of data
- The database can handle growth and changes over time
Data modelling happens in three progressive stages. Each stage provides more detail than the previous one, building toward a complete database design.
1. Conceptual Data Model
The conceptual data model provides a high-level view of what data the system needs. It focuses on what data are needed rather than how they are stored. This model helps all stakeholders – including analysts, developers, and users – share a common understanding of the system.
The main deliverable is the Entity Relationship Diagram (ERD), which shows:
- Entities: Real-world objects that we need to store information about
- Attributes: Properties that describe each entity
- Relationships: Connections between entities
- Cardinality: How many of one entity relates to how many of another
2. Logical Data Model
The logical data model builds on the conceptual model by adding more detail. At this stage:
- Entities become database tables
- Attributes become table columns with data types
- We identify primary keys (unique identifiers) and foreign keys (links between tables)
- We apply rules to keep data accurate (called constraints)
This model is independent of any specific database technology.
3. Physical Data Model
The physical data model shows exactly how the database will be built in a specific system like MySQL or MariaDB. It includes:
- Exact data types for each column
- Database constraints
- SQL statements to create tables
- Storage considerations
Entities
An entity represents a real-world object in the system. Entities can be tangible (like Student, Book, Doctor) or intangible (like Subject, Appointment, Payment).
Example: In a school system, entities include Student, Teacher, Class, and Subject.
Attributes
An attribute is a property that describes an entity. Each attribute holds specific information about the entity.
Example: The Student entity might have attributes:
- student_id
- first_name
- last_name
- date_of_birth
- address
- phone_number
Relationships
A relationship shows how entities connect to each other.
Example: A Student enrols in a Class. A Teacher teaches a Subject.
Cardinality
Cardinality defines the numerical relationship between entities. The three main types are:
- One-to-One (1:1): One entity relates to exactly one other entity
- One-to-Many (1:M): One entity relates to many other entities
- Many-to-Many (M:N): Many entities relate to many other entities
There are two common ways to draw ERDs:
Chen Notation
- Entities are shown as rectangles
- Attributes are shown as ovals
- Relationships are shown as diamonds
- Cardinality is written on the relationship lines
Crow's Foot Notation
- Entities are shown as rectangles
- Symbols show cardinality:
- A single line means "one"
- A crow's foot (three-pronged symbol) means "many"
- This notation is more commonly used in database design
Let's apply what we've learned to design a data model for Y2K Hospital, which needs to manage patient records, doctor assignments, and treatment history.
Step 1: Identify Entities and Attributes
From the requirements, we identify these entities:
- Patient: patient_id, name, sex, date_of_birth, contact, address
- Doctor: doctor_id, name, speciality, contact
- Appointment: date, time, patient_id, doctor_id
- Treatment: treatment_id, diagnosis, prescription, patient_id, doctor_id
Step 2: Identify Relationships and Cardinality
- A patient makes one or more appointments (1:M)
- A doctor attends multiple appointments (1:M)
- A doctor administers multiple treatments (1:M)
- A patient receives multiple treatments (1:M)
Step 3: Create the Logical Model
Now we convert entities to tables and specify data types:
Patient Table
| Column | Data Type | Constraints |
|---|---|---|
| patient_id | INT | PRIMARY KEY, AUTOINCREMENT |
| first_name | VARCHAR(30) | NOT NULL |
| last_name | VARCHAR(30) | NOT NULL |
| sex | VARCHAR(6) | NOT NULL |
| date_of_birth | DATE | NOT NULL |
| phone_number | VARCHAR(30) | |
| VARCHAR(30) | ||
| address | TEXT |
Doctor Table
| Column | Data Type | Constraints |
|---|---|---|
| doctor_id | INT | PRIMARY KEY, AUTOINCREMENT |
| first_name | VARCHAR(30) | NOT NULL |
| last_name | VARCHAR(30) | NOT NULL |
| speciality | VARCHAR(30) | NOT NULL |
| phone_number | VARCHAR(30) | |
| VARCHAR(30) |
Appointment Table
| Column | Data Type | Constraints |
|---|---|---|
| appointment_id | INT | PRIMARY KEY, AUTOINCREMENT |
| appointment_date | DATETIME | NOT NULL |
| patient_id | INT | FOREIGN KEY REFERENCES Patient(patient_id) |
| doctor_id | INT | FOREIGN KEY REFERENCES Doctor(doctor_id) |
Treatment Table
| Column | Data Type | Constraints |
|---|---|---|
| treatment_id | INT | PRIMARY KEY, AUTOINCREMENT |
| diagnosis | TEXT | NOT NULL |
| prescription | TEXT | NOT NULL |
| patient_id | INT | FOREIGN KEY REFERENCES Patient(patient_id) |
| doctor_id | INT | FOREIGN KEY REFERENCES Doctor(doctor_id) |
Step 4: Physical Model (SQL Implementation)
CREATE TABLE patient (
patient_id INT AUTOINCREMENT PRIMARY KEY,
first_name VARCHAR(30),
last_name VARCHAR(30),
date_of_birth DATE,
address TEXT,
phone_number VARCHAR(30),
email VARCHAR(30)
);
CREATE TABLE doctor (
doctor_id INT AUTOINCREMENT PRIMARY KEY,
first_name VARCHAR(30),
last_name VARCHAR(30),
specialty VARCHAR(50),
address TEXT,
phone_number VARCHAR(30),
email VARCHAR(30)
);
CREATE TABLE appointment (
appointment_id INT AUTOINCREMENT PRIMARY KEY,
appointment_date DATETIME,
patient_id INT,
doctor_id INT,
FOREIGN KEY(patient_id) REFERENCES patient(patient_id),
FOREIGN KEY(doctor_id) REFERENCES doctor(doctor_id)
);
CREATE TABLE treatment (
treatment_id INT AUTOINCREMENT PRIMARY KEY,
diagnosis TEXT,
prescription TEXT,
patient_id INT,
doctor_id INT,
FOREIGN KEY(patient_id) REFERENCES patient(patient_id),
FOREIGN KEY(doctor_id) REFERENCES doctor(doctor_id)
);
- PRIMARY KEY (PK): A column that uniquely identifies each record. Cannot be NULL or duplicated.
- FOREIGN KEY (FK): A column that links to a primary key in another table, creating a relationship.
- NOT NULL: Ensures a column must have a value (cannot be empty).
- UNIQUE: Ensures no duplicate values in a column.
- AUTOINCREMENT: Automatically generates a new number for each new record.
In Tanzania, data modelling is used when shops and small businesses computerise their systems. For example, a duka in Arusha wanting to track sales and inventory would need a data model with entities like Product, Customer, Sale, and Transaction. A proper data model ensures that when a customer buys items worth TZS 50,000, the system correctly records which products were sold, reduces the stock quantity, and keeps customer purchase history for future reference – just like the systems used in supermarkets and pharmacies across Tanzania.
Jinsi utakavyotathminiwaTathmini ya mchakato · Tathmini ya matokeo · Tathmini ya maarifa
Tathmini ya mchakato
- Identify entities, attributes, and relationships
- Translate ERDs into normalized tables and relational schema.
- Use appropriate notations and conventions.
Tathmini ya matokeo
Completed ERDs and relational schemas that correctly represent the data structure and relationships.
Tathmini ya maarifa
Detailed knowledge of:
Method used: The student should explain how to
- develop data models using ERDs
Principles: The student should explain the principles of:
- eliminating redundancy and ensuring data integrity.
Theories: The student should explain:
- database design principles and data modeling techniques.
Circumstantial knowledge:
Detailed knowledge about:
- Best practices in designing ERD
Swali
What are the three progressive stages of data modelling?
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