Design a Database for a Classified Adverts Website

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 every field? 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 advert website.