IT-344 Assignment-3 Database management
smartman1212
IT344Assignment3-Answers-Database.docx|
Pg. 05 |
|
Question Three |
|
|
|
|
Assignment 3
Deadline: Day 06/04/2019 @ 23:59
[Total Mark for this Assignment is 6]
Database Management Systems
IT 344
College of Computing and Informatics
|
|
|
|
|
|
|
|
Question One
1.5 Marks
Learning Outcome(s):
LO3
Draw the family tree corresponding to the following Prolog Based on following rules:
female(Mary).
female(Sandra).
female(Juliet).
female(Lisa).
male(Peter).
male(Paul).
male(Tom).
male(Bob).
male(Harry).
parent(Bob, Lisa).
parent(Bob, Paul).
parent(Bob, Mary).
parent(Juliet, Lisa).
parent(Juliet, Paul).
parent(Juliet, Mary).
parent(Peter, Harry).
parent(Lisa, Harry).
parent(Mary, Tom).
parent(Mary, Sandra)
parent(x,y) means x is parent of y
Juliet
Bob
Peter Lisa b
Lisa Lisa b
Paul
Mary
Tom
Sandra
Harry Lisa b
Question Two
1.5 Marks
Learning Outcome(s):
LO3
Apply the Apriory Algorithms to the following data set to find frequent (Large) itemsets using minimum support value 0.5.
|
Transaction ID |
Items Purchased |
|
101 |
Milk, Bread, Cookies, Juice |
|
102 |
Milk, Juice |
|
103 |
Milk, Eggs |
|
104 |
Bread, Cookies, Coffees |
The miminum sypport count is 2 (4*.5)
The candidate 1
|
Item |
Count |
respective supports |
|
Milk |
3 |
.75 |
|
Bread |
2 |
.5 |
|
Cookies |
2 |
.5 |
|
Juice |
2 |
.5 |
|
Eggs |
1 |
.25 |
|
Coffees |
1 |
.25 |
The first four items qualify for L1 since each support is greater than or equal to 0.5. are
L1={Milk,Bread,Cookies,Juice}
|
Item |
Count |
respective supports |
|
Milk,Bread |
1 |
.25 |
|
Milk,Cookies |
1 |
.25 |
|
Milk,Juice |
2 |
.5 |
|
Bread,Cookies |
2 |
.5 |
|
Bread,Juice |
1 |
.25 |
|
Cookies,Juice |
1 |
.25 |
The candidate 2
The second items qualify for L2 since each support is greater than or equal to 0.5. are
L2={(Milk.Juice),(Bread,Cookies)}
The supports for the six sets contained in C2 are 0.25, .25, 0.25, 0.5, 0.25, and 0.25 and are computed by scanning the set of transactions.Only the third 3-itemset {milk, juice} and the fourth 2-itemset {bread,cookies} have support greater than or equal to 0.5. These two 2-itemsets form the frequent 2-itemsets, L2
Question Three
2 Marks
Learning Outcome(s):
LO3
Consider these two tables:
Daily_avg_tempreature(year, month_nber, day_nber, avg_temp )
Monthly_maximas(year, month_nber,max_temp )
a. Using Oracle notation, write a trigger that allows to automatically update table Monthly_maximas on each insert of a new record in table Daily_avg_tempreature
create trigger Update_Maximas
after insert on daily_avg_temperature
FOR EACH ROW
declare @year int,@month_nber int,@max int,@count int;
begin
select @year=year,@month_nber=month_nber from daily_avg_temperature;
select @max=max(avg_temp) from daily_avg_temperature where month_nber=@month_nber;
select @count=count(*) from daily_avg_temperature where month_nber=@month_nber;
if(@count=1)
insert monthly_maximas values(@year,@month_nber,@max);
else
update monthly_maximas set max_temp=@max where month_nber=@month_nber;
end;
b. Using Oracle notation, write a trigger that allows to automatically update table Monthly_maximas on each update of records in table Daily_avg_tempreature
create trigger Update_Maximas_On_Update
after update on daily_avg_temperature
FOR EACH ROW
declare @year int,@month_nber int,@max int,@count int;
begin
select @year=year,@month_nber=month_nber from daily_avg_temperature;
select @max=max(avg_temp) from daily_avg_temperature where month_nber=@month_nber;
select @count=count(*) from daily_avg_temperature where month_nber=@month_nber;
if(@count=1)
insert monthly_maximas values(@year,@month_nber,@max);
else
update monthly_maximas set max_temp=@max where month_nber=@month_nber;
end;
Question Four Chapter 26 page 946
1 Marks
Learning Outcome(s):
LO3
What are the differences between valid time, transaction time, and bitemporal databases?
· Valid time is the time period during which a fact is true in the real world.
· Transaction time is the time period during which a fact stored in the database was known.
A bi-temporal database has two axes of time.
· valid time.
· transaction time or decision time.
