Page 507 - Computer_Science_F5

Computer Science (e) Reflection summary: A brief
examples of data entries for the
“farmers” and “crops” tables.
summary reflecting on the
FOR ONLINE READING ONLY
experience, any challenges faced
during the activity, and what was
learned from creating and managing
the SQL database.
Finally, make sure you document your
work in a portifolio.
Activity 8.24:
Creating an index and view in SQL
Aim:
To learn how to optimise data retrieval
using indexes and how to create a
view to combine and display related
data from multiple tables in a SQL
database.
Materials
1. Computer with Internet access
2. SQL database management system
(such as MySQL, PostgreSQL)
3. SQL client software or an online
SQL editor (e.g., phpMyAdmin,
MySQL Workbench, or an online
platform like W3Schools)
4. Sample dataset for the “farmers”
and “crops” tables
Instructions
To add an index to a column in the
“crops” table and create a view based
on the data in the database, follow
these steps:
498
Computer Science Form 5.indd 498
Step 1: Create an index
Add an index to the ‘name’ column
in the ‘crops’ table to speed up data
retrieval operations, the code looks as:
CREATE INDEX idx_crops_name ON
crops(name);
This statement creates an index called
“idx_crops_name” on the “name”
column in the “crops” table. Indexes
improve the speed of data retrieval
operations on specific columns.
Step 2: Create a view
Create a view to combine data from
the “farmers” and “crops” tables based
on their relationship, codes look as:
CREATE VIEW crop_farmer_
information AS
SELECT c.id AS crop_id, c.name
AS crop_name, c.planting_date,
c.harvest_date, c.yield, c.price,
f.id AS farmer_id, f.name AS
farmer_name, f.age, f.location,
f.contact
FROM crops c
JOIN farmers f ON c.farmer_id = f.id;
Note: This statement creates a view
named “crop_farmer_information”
that combines data from the “crops”
and “farmers” tables. The view
includes columns from both tables and
joins the data based on the “farmer_
id” foreign key relationship.
Deliverables
Write a comprehensive report
including the following:
for Advanced Secondary Schools
23/07/2024 12:35