Answer to Question #214286 in Databases | SQL | Oracle | MS Access for Hassam

Question

Calculate Affinity Matrix on the base of given database, usage and access frequency information.

 

Database Information

Item

ID

Name

Price

Type

Expiry

 

Usage Information:

q1=select id,name,price from item

q2=select name,type,expiry from item

q3=select id,type from item where price>400

q4=select expiry,type,price from item where type=’soap’

q5=select name,type from item where expiry>15

q6=select name,expiry,id from item

Access Frequency Information

Site 1: q1(150), q2(000), q3(120), q4(300), q5(030), q6(500)

Site 2: q1(010), q2(230), q3(050), q4(250), q5(030), q6(140)

Site 3: q1(150), q2(130), q3(200), q4(110), q5(010), q6(220)

Site 4: q1(130), q2(700), q3(010), q4(230), q5(210), q6(100)

Answer 1

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  b4 INT DEFAULT 0
);

delimiter |

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW
  BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END;
|

delimiter ;

INSERT INTO test3 (a3) VALUES
  (NULL), (NULL), (NULL), (NULL), (NULL),
  (NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES
  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);