Classified adverts are organised into classifications, for example motor cars, rooms for rent, baby
clothes etc. and each classification uses different features to describe items. Cars have make and
model, clothes have sizes, etc. Note that each user can only have one account – there is no need for
a buyer’s account and a separate seller’s account for the same person.
a. Items for sale in a classified advert system have different features depending on
their classification. Explain why this is a problem for a relational database and
suggest two possible solutions to the problem. Compare the two solutions, giving
advantages and disadvantages of each. Say which you would choose.
b. Design a set of normalised relations for storing data about people, objects for sale,
transactions and messages. For each relation choose a primary key and explain the
reasons behind the choice. Highlight the foreign keys in your design. Explain
whether your design is in second or third normal form and say why you chose that
level of normalisation. You needn’t include every possible field (don’t worry about
product features, for example), just show some example fields for each table.
c. Draw an entity relation (ER) diagram for the normalised relations you have designed.
There is no need to show non-key fields, but show primary and foreign keys and the
cardinality of their relationship. Clearly state any assumptions you have made.
d. Give an example of the SQL code needed to select the names of the buyer and the
seller of an example item if the item ID is known. The code should implement a join.
2. Now consider a NoSQL solution to the same problem.
a. Referring to your answer to part a. in question 1, explain how a document database
such as MongoDB offers a solution to the classified advert problem.
b. Describe two use cases for your database in terms of the task a user will want to
perform, the data that will be accessed, and how often the task will be performed.
c. People, objects and classifications are three potential aggregates that could be used
for the database. Explain what this means and compare the advantages and
disadvantages of using each of the suggested aggregates in a classified advert
database. Refer to the use cases you gave in part b. above.
d. MongoDB is a schemaless database. Describe what that means and then discuss
what, if any, restrictions you might choose to impose on documents describing
objects for sale. Say how these restrictions would be imposed and discuss the
advantages and disadvantages of doing so.
e. Thinking about the entries for objects for sale, discuss which fields you would index
on. What would indexing gain for your website and why wouldn’t you simply index
f. Imagine your database grows very large and is stored over a cluster. You will need to
employ sharding to split the data up. Explain what this means and discuss which
field(s) it would be sensible to use as a shard key. If you now realise that you need a
field that you haven’t mentioned yet, that is fine. Introduce it into the design now
and explain why it is needed.
g. Messages can be sent between two people (usually a buyer and a seller). Discuss
different options for storing messages and linking each message to the people who
sent and received it. Propose three different options and say which of the three you
would choose, and why. In your discussion, refer to particular use cases such as
listing all the messages a person has sent and consider the efficiencies of the
different design choices you propose.
3. Now that you have two designs, one for SQL and one for NoSQL, discuss the relative merits
of each and say which approach you would choose for the database behind a classified