Database Design for Classified Adverts Website

Designing a Relational Database for Classified Adverts

1. Imagine you choose a relational database to store the data from the classified adverts website. a. List the entities and relationships in the scenario described above and draw an ER diagram to represent them. You may use any valid notation you like, but you should include a key to explain what the notation means. You do not need to list all the fields, just some examples. Model the scenario given in the introduction – do not add other elements that you think an e-commerce system might need, like billing. 

b. 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. Suggest two possible relational database designs that attempt to represent the many different classes of object that might be for sale. Compare the two solutions, giving advantages and disadvantages of each. 

2. Now consider a NoSQL solution to the same problem.

a. Referring to your answer to part b. 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. Create a document model design for a MongoDB database for the classified adverts website. Consider what can be embedded in sub-documents and what can be linked with references. Include an example of each of these things in your design. Draw the design in a format of your choosing (for example, in UML).

d. MongoDB is a schemaless database. Describe what that means and then discuss what, 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. 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 now and explain why it is needed. 

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. 

4. Load the file provided, called AssgCars.json into a MongoDB collection and give the MongoDB code you would use to make the following queries. Also show the result of running each query.

a. List the prices (and no other variables, not even the ID) of all cars for sale where the make is Skoda and Model is Octavia.

b. Calculate the average price of all Skoda cars for sale.

c. Calculate the average price of each different model from the Skoda cars for sale.

d. Find the model of Skoda car with the highest average price.

e. Use the aggregation pipeline to count the number of different colours each manufacturer offers for their cars. Do not report the colours, just each manufacturer and the number of colours they offer.

f. All cars have a field called Extras which holds an array of strings (e.g. {“Extras”:[“ABS”,”PAS”,”Aircon”]}. Write code to list all of the extras ever mentioned for each manufacturer in turn (i.e., the union of the Extras arrays grouped by manufacturer).

g. Use the aggregation pipeline to count the number of extras each car has and calculate the average number of extras per model.

h. Each car in the data has a price and a mileage. Adding price to mileage for a car gives a measure of how cheap and new a car is. Write a map reduce query to find the car with the lowest value of price plus mileage for each manufacturer.