A search engine company hires you to maintain a database that records the searched words per customers and the advertisements paid per advertisers.
You must store all sentences searched by customers, the timestamp of the query, and customer identifier (e.g., using browser cookies). These sentences consist of different words (called keyword). You should also determine the language the customer used to search each sentence based on the keywords used. Note that a given keyword may appear in one or more languages.
Advertisers will also share their pages to be displayed once customers type a keyword related to their page. The price paid by advertisers is proportional to the probability to return their page first when queried by customers.
(Q1): Draw an ER diagram, and provide the database schema. Underline the primary keys.
(Q2): Write the CREATE TABLE statements including all the constraints.
(Q3): List the top 20 most common keywords searched per customer, in decreasing fre- quency order.
(Q4): Returns all languages that have never been used in any searched query.
(Q5): Write a SQL query to show bilingual customers. Return the customer identifier, his name ordered by decreasing number of languages that he potentially speaks. We assume that a customer speaks a language if he searched at least once a sentence from the language.