Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit


CS1106 Introduction to Relational Databases



Question 1  [10% ]

Draw a suitable ER diagram to model the following academic system. Each lecturer has a staff number, a name, an email address and an office number. Each student has an id number, name, phone number, email address and home address.  Each module on the books has a module code, title and a credit weighting. Modules may be run many times; the term “offering” refers to the version of a module that takes place during a particular semester.  Each module offering has a unique id, a semester, a year and an instructor (just one).  Each lecturer may be the instructor for some number of module offerings. Each individual student is registered for some number of module offerings and receives a letter grade (A to F) at its conclusion.

Question 2  [36 % ]

Consider the following structure for a single-table database.

countries  (code, name,  continent,  independance_date, population, life_expectancy,  gnp)

Give a complete SQL statement to accomplish each of the following tasks.

(i)  Create a table with the structure indicated above.                                          (6% )

(ii) Insert three rows into the table. (Invent reasonable values for the GNP, population

and so on.)                                                                                                        (6% )

(iii) Update the population of all the countries in Asia to increase each by 15%. (6% ) (iv) Delete all countries whose name begins with the letter ’Z’.                            (6% )

(v) List all the countries in Africa which gained independence during the 1960s. (6% )

(vi) List the name and GNP of all countries by continent and by decreasing order of

GNP within each continent.                                                                             (6% )

Question 3  [54 % ]

Consider the following enhanced version of the countries database introduced in Question 2 that also captures information about the cities of the world and the languages spoken in the various countries.

cities(id, name,  country_code,   population)

countries(  code, name,  continent,  region,  surface_area,  indep_year, population,  life_expectancy,  gnp, head_of_state,  capital)

country_languages(country_code,  language,  is_official, percentage) Note that in this version we replace the date of independence with the year.     Please give complete SQL queries to complete each of the following tasks.

(i) List all the languages spoken in India and list for each language the percentage of the population who speak it.                                                                            (6% )

(ii) List all the cities in the world with a population of at least five million.  List the

cities by continent and country and by descending order of population within each country.                                                                                                             (6% )

(iii) List the name of the city with the greatest population.                                  (9% )

(iv) For all those individuals who are head of state of at least five countries, list the

name of the individual and the countries for which he/she is head of state.  (9% )

(v) List the English-speaking country with the earliest date of independence. We con- sider a country to be English speaking if either English is an official language or if that language is spoken by more than 50% of the population.                      (12% )

(vi) List the languages in decreasing order of the number of speakers (include only those

with at least twenty million speakers).  The number of speakers of a language in a single country can be derived from the total population of the country and the percentage that speak the language.                                                               (12% )