Consider the following set of normalized relations from a database used by a mob
ID: 3688360 • Letter: C
Question
Consider the following set of normalized relations from a database used by a mobile service provide to keep track of its users and advertiser customers.
• USER(UserID, UserLName, UserFName, UserEmail, UserYearOfBirth, UserCategoryID, UserZip)
• ADVERTISERCLIENT(ClientID, ClientName, ClientContactID, ClientZip)
• CONTACT(ContactID, ContactName, ContactEmail, ContactPhone)
• USERINTERESTAREA(UserID, InterestID, UserInterestIntensity)
• INTEREST(InterestID, InterestLabel)
• CATEGORY(CategoryID, CategoryName, CategoryPriority)
• ZIP(ZipCode, City, State)
Assume that the mobile service provider has frequent need for the following information:
• List of users sorted by zip code
• Access to a specific client with the client’s contact person’s name, e-mail address, and phone number
• List of users sorted by interest area and within each interest area user’s estimated intensity of interest
• List of users within a specific age range sorted by their category and within the category by zip code.
• Access to a specific user based on their e-mail address.
Based on these needs, specify the types of indexes you would recommend for this situation. Justify your decisions based on the list of information needs above. Please identify possible opportunities for denormalizing these relations as part of the physical design of the database. Which ones would you be most likely to implement?
Explanation / Answer
SELECT USER.* FROM USERS
INNER JOIN ZIP ON ZIP.ZIPCODE=USER.USERZIP
ORDERBY ZIP.ZIPCODE ASC
__________________________________________________________________________________________
2.SELECT CONTACT.CONTACTNAME.CONTACT.CONTACTEMAIL,CONTACT.CONTACTPHONE FROM CONTACT
INNER JOIN ADVERTISERCLIENT ON ADVERTISERCLIENT.CLIENTID=CONTACT.CONTACTID
WHERE ADVERTISERCLIENT.CLIENTID ?
You need to repalce the ? with the value to get the specific client
_________________________________________________________________________________________
3.SELECT USERINTERESTAREA.UserId,USERINTERESTAREA.UserInterestIntensity,count of UserId,UserInterestIntensity
FROM USERINTERESTAREA
INNER JOIN INTEREST ON INTEREST.InterestId=USERINTERESTAREA.InterstId
GROUP BY USERINTERESTAREA.UserInterestIntensity,USERINTERESTAREA.UserId
ORDER BY INTEREST.InterestId asc
____________________________________________________________________________________________________
4. SELECT USER.UserId,USER.UserZip count of UserId,UserZip
FROM USER
INNER JOIN ZIP ON ZIP.Zipcode=USER.UserZip
INNER JOIN CATEGORY ON CATEGORY.CategoryId=USER.UserCategoryId
WHERE USER.UserYearofbirth ??
GROUP BY USER.userzip
ORDER BY USER.UserCategoryId
Here in place of ?? you need specify the min. and max. age limit.
__________________________________________________________________________________________________________
5.
SELECT UserId FROM USER
WHERE UserEmail ?
Here also you need to specify the email address
___________________________________________________________________________________________________
Based on these needs i recommend these indexes are non-clusterd.
possibly oppurtunities for denormalizing relations
i. In USER table usercategory and userzip are reduandant data.
ii.Like In ADVERTISERCLIENT clientcontactid and contact id are reduandant.