1
BYGB/ISGB 7973: Practice problems/exercises
DATABASE MANAGEMENT
Using a Data Model to Implement Database.
DUE: December 5
Question 1
A database will have table Customer in which each customer is identified by CustomerID, which is a
Numeric (3) field (i.e., it can take values from 0 to 999). The table is to be implemented as hash file using
divisor-remainder method. The usage map indicates that will be about 80 records. The prime number for
the divisor-remainder algorithm is chosen as 13 (i.e., pages will be numbered from 0 to 12 and the page
where a record will be located is given by the remainder after dividing CustomerID by 13.) the size of
each page is chosen so as to contain up to 6 records.
For the following CustomerID values, find the page where the row will be stored.
439
487
23
157
141
144
82
2
283
184
463
269
125
15
251
275
453
70
19
92
471
140
231
27
327
159
211
Do you see any page overflow (i.e., get more than 6 records?) Are there any pages that do not get any
record?
Question 2 (Based on exercise 8-51)
Consider the following set of normalized relations from a database used by a mobile service provide to
keep track of its users and advertiser clientss.
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àcontact personàname, e-mail address, and phone
number.
? List of users sorted by interest area and within each interest area userà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.
2
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.
Question 3 (Based on exercises 8-59)
Consider Figure 8-7b. Assuming that that each index node can contain three pointers to subtrees and the
leaf nodes can contain four records. What might happen to leaf nodes and index nodes as new records for
Angels, Gators, Hoofers, Gardeners, Highfliers are inserted in the leaf nodes? What happens if addition
records for Highlanders Hoyas, Hostlers, Hummers are inserted?

Purchase answer to see full
attachment