Here is the content from image_8746c9.jpg organized and formatted as a clean, structured WordPress revision post for your students. Typos from the original image (such as “Customerr_Item” and missing commas in SQL statements) have been corrected to ensure valid code and clear understanding.

Correction of CGCE June 2025: Computer Science (AL CSC 795)

SECTION A: DATABASE

Task I: Schema & ER Diagram Design

(i) Relational Schema

• Customer($\underline{\text{CustomerID}}$, $\text{CustomerName}$)

• Item($\underline{\text{ItemCode}}$, $\text{ItemName}$, $\text{UnitPrice}$)

• Customer_Item($\underline{\text{CustomerID}}$, $\underline{\text{ItemCode}}$, $\text{Quantity}$, $\text{TotalPrice}$, $\text{TDate}$)

(ii) Completed ER Diagram

Below is the structural link representing the entity relationships between the tables:

[Customer] --------(1)-------< [Customer_Item] >-------(∞)-------- [Item]

Task II: Data Definition Language (DDL)

(i) Database Creation

CREATE DATABASE NinashopDB;

(ii) Table Creation Scripts

Customer Table:

CREATE TABLE Customer (
    CustomerID VARCHAR(5) NOT NULL,
    CustomerName VARCHAR(20),
    PRIMARY KEY (CustomerID)
);

Item Table:

CREATE TABLE Item (
    ItemCode VARCHAR(5) NOT NULL,
    ItemName VARCHAR(10),
    UnitPrice INT,
    PRIMARY KEY (ItemCode)
);

Customer_Item Table:

CREATE TABLE Customer_Item (
    CustomerID VARCHAR(5),
    ItemCode VARCHAR(5),
    Quantity INT,
    TotalPrice INT,
    TDate DATE,
    PRIMARY KEY (CustomerID, ItemCode),
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
    FOREIGN KEY (ItemCode) REFERENCES Item(ItemCode)
);

(iii) Modifying Table Structure (ALTER)

To rename a column within the database schema:

ALTER TABLE Customer_Item 
RENAME COLUMN Quantity TO Qty;

Task III: Data Manipulation Language (DML) & Querying

(i) Populating Tables (INSERT)

INSERT INTO Customer (CustomerID, CustomerName)
VALUES 
('C001', 'Ebai Paul'),
('C002', 'Eyere Esther');

(ii) Updating Records (UPDATE)

UPDATE Item 
SET UnitPrice = 200 
WHERE ItemCode = 'IT003';

(iv) Querying Data (SQL SELECT)

To extract transactional information for a specific item (IT002), you can use either an explicit INNER JOIN syntax or an implicit join via the WHERE clause:

• Approach 1: Explicit INNER JOIN

  SQL

  SELECT 
      Customer.CustomerID, 
      Customer.CustomerName, 
      Customer_Item.Qty, 
      Customer_Item.TotalPrice
  FROM Item 
  INNER JOIN Customer_Item ON Item.ItemCode = Customer_Item.ItemCode
  INNER JOIN Customer ON Customer.CustomerID = Customer_Item.CustomerID
  WHERE Item.ItemCode = 'IT002';
  

• Approach 2: Implicit Join (WHERE Clause)

  SQL

  SELECT 
      Customer.CustomerID, 
      Customer.CustomerName, 
      Customer_Item.Qty, 
      Customer_Item.TotalPrice
  FROM Customer, Item, Customer_Item
  WHERE Customer.CustomerID = Customer_Item.CustomerID 
    AND Item.ItemCode = Customer_Item.ItemCode 
    AND Item.ItemCode = 'IT002';
  

SECTION B: PROGRAMMING & ALGORITHMS

Task IV: Algorithm Execution Tracing

(i) Loop Evaluation 1

• (a) As stated in the description: 1 year.

• (b) Initial Value = $27$

  • Year 1: $27 + 9 – 6 = \mathbf{30}$ (Condition: $30 < 37$)

  • Year 2: $30 + 10 – 7 = \mathbf{43}$ (Condition: $43 \ge 37$)

  • Result: Hence, 2 years.

(ii) Loop Evaluation 2

• Initial Value = $4$

  • Year 1: $4 – 1 = 3$

  • Year 2: $3 – 0 = 3$

  • Year 3: $3 – 0 = 3$

  • Conclusion: The value stabilizes at 3; therefore, it will never drop to 0.

Task V: Pseudocode Implementation

The following function handles input validation to ensure that an entered population size is structurally sound and meets the minimum constraint criteria before processing:

FUNCTION InputStartSize()
    INTEGER population;
    
    PRINT("Enter the starting population size: ");
    READ(population);
    
    WHILE (population < 9) DO
        PRINT("The population size MUST be 9 or above");
        PRINT("Enter the starting population size: ");
        READ(population);
    ENDWHILE;
    
    RETURN population;
ENDFUNCTION



	

	
	

	
	
		

	 

	 PDF is loading please wait...