%---------------------------------------------------------------Answer1----------------------------------------------------------------------
%List the song along with its cdTitle and trackNumber for top 10 songs written by artists born after 1970 (artist.yearBorn > 1970).
%(songCode, songTitle, cdCode, cdTitle, trackNumber)
top10songs:= {T | topSongs(T) and T.rating<=10};
%Find out more information 
top10songs_tmp1:={ T.songCode, T.year, T.rating, W.artistID |top10songs(T) and writtenBy(W) and T.songCode = W.songCode};
top10songs_tmp2:={ T.songCode, T.year, T.rating, A.artistID, A.firstName, A.lastName, A.yearBorn |top10songs_tmp1(T) and artist(A) and T.artistID = A.artistID};
%find out artist.yearBorn <= 1970
top10songs_tmp3:={ T.songCode |top10songs_tmp2(T) and T.yearBorn<=1970};
%Find out artist.yearBorn > 1970
top10songs_tmp4:={ T.songCode |top10songs_tmp2(T) };
top10songs_tmp5:={T| top10songs_tmp4(T) and not top10songs_tmp3(T)};
%Find out all information using songCode
top10songs_tmp6:={ T.songCode, C.cdCode, C.trackNumber |top10songs_tmp5(T) and composedOf(C) and T.songCode = C.songCode};
top10songs_tmp7:={ T.songCode, S.songTitle, T.cdCode, T.trackNumber |top10songs_tmp6(T) and song(S) and T.songCode = S.songCode};
final_Question1:={ T.songCode, T.songTitle, T.cdCode, C.cdTitle, T.trackNumber |top10songs_tmp7(T) and cd(C) and T.cdCode = C.cdCode};
%===================================================================================
%---------------------------------------------------------------Answer2----------------------------------------------------------------------
% Find the oldest artist, who is currently in a group. A group member is current if the member.toDate attribute is 0.
% (groupCode, groupName, artistID, lastName, yearBorn)

%Find out all group member who is current
current_member:= {M |member(M) and M.toDate = 0};
%merge more info
current_member_temp1:= {T.groupCode, T.artistID, A.lastName, A.yearBorn |current_member(T) and artist(A) and T.artistID = A.artistID};
%Find oldest artist
oldest := { T | current_member_temp1(T) and  not (exists E) (current_member_temp1(E) and E.yearBorn  <  T.yearBorn) }; 
%merge group name
final_Question2:= {O.groupCode, M.groupName,O.artistID, O.lastName, O.yearBorn | oldest(O) and musicalGroup(M) and O.groupCode = M.groupCode};
%===================================================================================

%---------------------------------------------------------------Answer3----------------------------------------------------------------------
% Find the list of musical groups such that every client in Detroit rented at least one of their CDs. (The same division query as appeared in HW1).
% (groupCode, groupName)
%Find all clients in Detroit
clients_detroit := { C| client(C) and C.address ='Detroit'};
%Find relationship between groupCode, clientID.
info_tmp1:= {C,A |cd(C) and availableCopy(A) and C.cdCode = A.cdCode};
info_tmp2:= {I,R |info_tmp1(I) and rentedBy(R) and I.cdCode = R.cdCode};
%the groupCode such that every client in Detroit rented at least one of their CDs.  inforo/clients_detroit. Reference: http://winrdbi.asu.edu/samples/abstractDivision/abstractDivision.trc
gCode:= { T.groupCode | info_tmp2(T) and not (exists B)( not (not clients_detroit(B) or  (exists AB) (info_tmp2(AB) and AB.groupCode=T.groupCode and AB.clientID=B.clientID) ) )};
% Find the list musical groups such that every client in Detroit rented at least one of their CDs.  (groupCode, groupName)
final_Question3:= { G.groupCode, M.groupName | gCode(G) and musicalGroup(M) and G.groupCode = M.groupCode};
%===================================================================================

%---------------------------------------------------------------Answer4----------------------------------------------------------------------
%List the names of rental clients who have never rented a CD rated as number one.
%(clientID, firstName, lastName)
%njoin rentedBy and topCDs
topCD_clientID:={ R.clientID, T.cdCode, T.rating|rentedBy(R) and topCDs(T) and R.cdCode = T.cdCode};
%Find out all clients who have rented a CD rated as number one
clientID_top1:={T | topCD_clientID(T) and T.rating =1};
%List the names of rental clients who have never rented a CD rated as number one.
client_ID:={C.clientID|client(C)};
clientID_top1_client_ID:= {C.clientID|clientID_top1(C)};
client_ID_final:={C|client_ID(C) and not clientID_top1_client_ID(C)};
%njoin firstName and lastName to clientID
final_Question4:={F.clientID, C.firstName, C.lastName |client(C) and client_ID_final(F) and C.clientID =F.clientID};
%===================================================================================

%---------------------------------------------------------------Answer5----------------------------------------------------------------------
%Which musical groups currently consist of only one artist?
% (groupCode, groupName, artistID, lastName)
%select artistID and groupCode
g1(artist1, group1):= {M.artistID, M.groupCode |member(M)};
%copy g1 into g2
g2(artist2, group2):=g1;
%all possible situations
possible:= { G1.artist1,G1.group1,G2.artist2,G2.group2|g1(G1) and g2(G2)};
%Which musical groups currently consist of more than one artist
more_than_one:= {P.artist1,P.group1,P.artist2,P.group2 |possible(P) and P.artist1 <> P.artist2 and P.group1 = P.group2};
more_than_one_final(groupCode):= { M.group1|more_than_one(M) };
% Which musical groups currently consist of only one artist
all_groups:={M.groupCode |member(M)};
only_one:={T |all_groups(T) and not more_than_one_final(T) };
%njoin more information, (groupCode, groupName, artistID, lastName)
only_one_temp1:={O.groupCode, M.groupName|only_one(O) and musicalGroup(M) and O.groupCode =M.groupCode };
only_one_temp2:={O.groupCode, O.groupName, M.artistID|only_one_temp1(O) and member(M) and O.groupCode =M.groupCode };
final_Question5:= { O.groupCode, O.groupName, O.artistID, A.lastName|only_one_temp2(O) and artist(A) and O.artistID = A.artistID};
%===================================================================================