CSC343 A1

Q02

group: q2 - Customer, Order

LineItem = {
	IID:number, OID:number, quantity:number
	1, 4, 1
	1, 3, 1
	2, 3, 1
}

Order = {
	YEAR:number, MONTH:number, CID:number, OID:number
	2016, 02, 1, 3
	2016, 12, 1, 4
	
}

Customer = {
	membership:String, CID:Number
	gold, 1
}

NotSingleton = pi a.OID (sigma a.OID = b.OID and a.IID != b.IID (rho a LineItem x rho b LineItem))
NotGoldOrder = pi OID (sigma Customer.membership != 'gold' (Customer ⨝ Order))
Singleton2016 = pi OID (sigma YEAR=2016 (Order)) - NotSingleton
GoldSingleton2016 = pi CID, YEAR, MONTH, OID ((Singleton2016 - NotGoldOrder) ⨝ Customer ⨝ Order)

NotLastGS16 = pi a.CID, a.YEAR, a.MONTH, a.OID (sigma a.CID = b.CID and a.OID != b.OID and a.MONTH < b.MONTH (rho a GoldSingleton2016 x rho b GoldSingleton2016))

LastGS16 = GoldSingleton2016 - NotLastGS16

NotFirstGS16 = pi a.CID, a.YEAR, a.MONTH, a.OID (sigma a.CID = b.CID and a.OID != b.OID and a.MONTH > b.MONTH (rho a GoldSingleton2016 x rho b GoldSingleton2016))

FirstGS16 = GoldSingleton2016 - NotFirstGS16

sigma first.CID = last.CID (rho first (FirstGS16) ⨯ rho last (LastGS16))

Q04

group: q2 - Customer, Order

Customer = {
	membership:String, CID:number
	gold, 1
	silver, 2
	none, 3
	silver, 4
}

Order = {
	YEAR:String, CID:number, OID:number
	
	2014, 2, 1
	2014, 2, 2
	2015, 2, 3
	2016, 4, 4
	
}

silverCusts = pi CID (sigma membership=silver (customer))
a2014Orders = sigma YEAR='2014' (Order)
a2015Orders = sigma YEAR = '2015' Order
a2016Orders = sigma YEAR = '2016' Order
a2014GE2 = pi a.CID (sigma a.CID = b.CID and a.OID < b.OID (rho a a2014Orders ⨯ rho b a2014Orders))
a2015GE2 = pi a.CID (sigma a.CID = b.CID and a.OID < b.OID (rho a a2015Orders ⨯ rho b a2015Orders))
a2015LT2 = pi CID (a2015Orders) - pi CID (a2015GE2)
a2016None = pi CID (Order) - pi CID (a2016Orders)

a2014GE2 ∩ a2015LT2 ∩ a2016None

Q05

LineItem = {
	IID:number, OID:number
	1, 1
	2, 1
}

Item = {
	NAME:String, IID:number, PRICE:number
	itemA, 1, 10
	itemB, 2, 20
	itemC, 3, 30
}

Order = {
	YEAR:String, CID:number, OID:number
	
	2014, 2, 1
	2014, 2, 2
	
}

OrderInfo = pi OID, CID, IID, PRICE ((LineItem ⨝ Item) ⨝ Order)
MostExpensiveItemOrder = OrderInfo - pi a.OID, a.CID, a.IID, a.PRICE (sigma a.OID = b.OID and a.IID != b.IID and a.PRICE < b.PRICE (rho a OrderInfo ⨯ rho b OrderInfo))
MostExpensiveItemOrder - pi a.OID, a.CID, a.IID, a.PRICE (sigma a.CID = b.CID and a.OID != b.OID and a.PRICE > b.PRICE (rho a MostExpensiveItemOrder x rho b MostExpensiveItemOrder))

Q06

WRONGGGGG
ZeroReviewed = pi IID Review - pi IID (sigma RATING≠0 Review)
FiveReviewed = pi IID Review - pi IID (sigma RATING≠5 Review)
ZeroItems = LineItem ⨝ ZeroReviewed
FiveItems = LineItem ⨝ FiveReviewed
ValidOrders = pi a.OID (sigma a.OID=b.OID (rho a ZeroItems ⨯ rho b FiveItems))
pi CID, LASTNAME, FIRSTNAME, OID, DATE (Customer ⨝ (ValidOrders ⨝ Order))

Order = {
	OID:number, DATE:date, CID:number
	1, 2017-01-01, 1
	2, 2016-12-12, 2
	3, 2016-06-01, 3
}

LineItem = {
	OID:number, IID:number
	1, 1
	1, 2
	1, 3
}

Review = {
	CID:number, OID:number, IID:number, RATING:number
	1, 1, 1, 0
	1, 1, 2, 5
	1, 1, 3, 5
}

Customer = {
	CID:number, FIRSTNAME:String, LASTNAME:String
	1, A, V
}

NotZeroRated = sigma RATING != 0 Review
ZeroRated = Review - NotZeroRated

NotFiveRated = sigma RATING != 5 Review
FiveRated = Review - NotFiveRated

OrdersWithAZero = pi Order.OID, DATE (sigma Item.IID = Review.IID (Item x Order x ZeroRated))
OrdersWithAFive = pi Order.OID, DATE (sigma Item.IID = Review.IID (Item x Order x FiveRated))

ValidOrders = pi a.OID, a.DATE (rho a OrdersWithAZero ⨝ a.OID = b.OID rho b OrdersWithAFive)
pi CID, LASTNAME, FIRSTNAME, OID, DATE (Customer ⨝ ValidOrders)

Q07

Helpfulness = {
	REVIEWER:number, ITEM:number, READER:number, HELPFUL:String
	1, 1, 2, yes
	1, 2, 2, yes
	2, 1, 3, no
}

NotHelpful = pi REVIEWER, READER (sigma HELPFUL='no' Helpfulness)

pi REVIEWER, READER (Helpfulness) - NotHelpful

Q08

Order = {
	OID:number, DATE:date, CID:number
	1, 2017-01-01, 1
	2, 2016-12-12, 2
	3, 2016-06-01, 3
}

LineItem = {
	OID:number, IID:number
	1, 1
	2, 1
	3, 1
}

NotLatest = pi a.IID, a.CID, a.OID (sigma a.IID = b.IID and a.DATE < b.DATE (rho a (Order ⨝ LineItem) ⨯ (rho b (Order ⨝ LineItem))))

pi IID, CID (pi IID, CID, OID (Order ⨝ LineItem) - NotLatest)

Q10

Review = {
	CID:number, IID:number, DATE:date, RATING:number
	1, 1, 2016-01-01, 5
	1, 2, 2016-02-02, 4
}

Customer = {
	CID:number
	1
}

ThreeReviews = pi a.CID (sigma a.CID = b.CID and b.CID = c.CID and a.IID != b.IID and b.IID != c.IID (rho a Review x rho b Review x rho c Review))

IncreasedReview = pi a.CID (sigma a.DATE > b.DATE and a.RATING >= b.RATING (rho a Review x rho b Review))

ValidCust = pi CID (ThreeReviews - IncreasedReview)

ValidCustNotLastReview =  (ValidCust ⨝ pi a.CID, a.DATE, a.IID (sigma a.CID = b.CID and a.DATE < b.DATE (rho a Review x rho b Review)))

ValidCustLastReview = (ValidCust ⨝ Customer) ⨝ (pi CID, DATE, IID Review - ValidCustNotLastReview)

pi CID, LASTNAME, EMAIL, IID ValidCustLastReview

Q11

Review = {
	CID:number, IID:number
	1, 1
	1, 2
}

Item = {
	NAME:String, IID:number, PRICE:number, TYPE:String
	itemA, 1, 10, book
	itemB, 2, 100, table
	itemC, 3, 20, mag
}

Subcategory = {
	a:string, b:string
	mag, book
	table, surface
}

NotTopTypes = pi b Subcategory
NotTopItemType = pi IID ((NotTopTypes) ⨝ NotTopType.b = Item.type (Item))
(pi CID (Review)) - (pi CID Reviews ⨝ NotTopItemTypes) -- Every category??

Q12

group: q2 - Customer, Order

LineItem = {
	IID:number, OID:number, quantity:number
	1, 1, 1
	2, 2, 1
	3, 3, 1
}

Item = {
	NAME:String, IID:number, PRICE:number, TYPE:String
	itemA, 1, 10, book
	itemB, 2, 100, table
	itemC, 3, 20, mag
}

Subcategory = {
	a:string, b:string
	mag, book
	table, surface
}

GEOneItem = pi OID, IID, TYPE (LineItem ⨝ Item)
BookSubs = sigma b='book' Subcategory
notBookOrSub = pi OID (sigma a.TYPE ≠ b.a and a.TYPE ≠ 'book' (rho a GEOneItem ⨯ rho b BookSubs))
pi OID (GEOneItem) - notBookOrSub