分布式数据库技术.作业13

作业1

针对world数据库、进行如下查询：

1. 查询Asia的所有国家名，国家代码，城市名

SELECT country.`Name` AS CountryName,country.`Code`,city.`Name` as CityName
FROM COUNTRY,city
WHERE country.`Code`=city.CountryCode AND country.Continent='Asia';

2.查询Asia中每个国家的城市数量，按查询结果降序排列

SELECT country.`Name` AS CountryName,country.`Code`,COUNT(city.`Name`) AS CityNum
FROM COUNTRY,city
WHERE country.`Code`=city.CountryCode AND country.Continent='Asia'
GROUP BY `Code`
ORDER BY CityNum DESC;

3. 查询CHN中每个District的人口总数，按降序排列

SELECT District,SUM(Population) AS DistrictPopulationSum
FROM CITY
WHERE CountryCode="CHN"
GROUP BY District
ORDER BY DistrictPopulationSum DESC;

作业2

针对 world 数据库、进行如下查询：

USE world;

1、查询每个 Continent 中 GNP 最高和最低的国家信息及其对应的 GNP 的值

SELECT country.*
FROM country
RIGHT JOIN
  (SELECT Continent,
          MAX(GNP) AS GNP_MAX
   FROM country
   WHERE GNP!=0
     AND Population!=0
   GROUP BY Continent) AS assist ON country.GNP=assist.GNP_MAX
AND country.Continent=assist.Continent
ORDER BY GNP DESC;

SELECT country.*
FROM country
RIGHT JOIN
  (SELECT Continent,
          MIN(GNP) AS GNP_MIN
   FROM country
   WHERE GNP!=0
     AND Population!=0
   GROUP BY Continent) AS assist ON country.GNP=assist.GNP_MIN
AND country.Continent=assist.Continent
ORDER BY GNP DESC;

2、查询每个 Continent 中人均 GNP 最高和最低的国家信息及其对应的人均 GNP 的值

SELECT country.*
FROM
  (SELECT GNP/Population AS PerCapitaGNP,
          country.*
   FROM country) AS country
RIGHT JOIN
  (SELECT Continent,
          MAX(GNP/Population) AS PerCapitaGNP
   FROM country
   WHERE GNP!=0
     AND Population!=0
   GROUP BY Continent) AS assist ON country.PerCapitaGNP=assist.PerCapitaGNP
AND country.Continent=assist.Continent
ORDER BY PerCapitaGNP DESC;

SELECT country.*
FROM
  (SELECT GNP/Population AS PerCapitaGNP,
          country.*
   FROM country) AS country
RIGHT JOIN
  (SELECT Continent,
          MIN(GNP/Population) AS PerCapitaGNP
   FROM country
   WHERE GNP!=0
     AND Population!=0
   GROUP BY Continent) AS assist ON country.PerCapitaGNP=assist.PerCapitaGNP
AND country.Continent=assist.Continent
ORDER BY PerCapitaGNP DESC;

3、查询每个 Continent 中总面积（SurfaceArea）最高和最低的国家及其对应的 SurfaceArea 的值

SELECT country.*
FROM country
RIGHT JOIN
  (SELECT Continent,
          MAX(SurfaceArea) AS SurfaceArea_MAX
   FROM country
   WHERE GNP!=0
     AND Population!=0
   GROUP BY Continent) AS assist ON country.SurfaceArea=assist.SurfaceArea_MAX
AND country.Continent=assist.Continent
ORDER BY SurfaceArea DESC;

SELECT country.*
FROM country
RIGHT JOIN
  (SELECT Continent,
          MIN(SurfaceArea) AS SurfaceArea_MIN
   FROM country
   WHERE GNP!=0
     AND Population!=0
   GROUP BY Continent) AS assist ON country.SurfaceArea=assist.SurfaceArea_MIN
AND country.Continent=assist.Continent
ORDER BY SurfaceArea DESC;

4、查询每个 Continent 中人均面积（SurfaceArea）最高和最低的国家及其对应的人均 SurfaceArea 的值

SELECT country.*
FROM
  (SELECT SurfaceArea/Population AS PerCapitaSurfaceArea,
          country.*
   FROM country) AS country
RIGHT JOIN
  (SELECT Continent,
          MAX(SurfaceArea/Population) AS PerCapitaSurfaceArea
   FROM country
   WHERE GNP!=0
     AND Population!=0
   GROUP BY Continent) AS assist ON country.PerCapitaSurfaceArea=assist.PerCapitaSurfaceArea
AND country.Continent=assist.Continent
ORDER BY PerCapitaSurfaceArea DESC;

SELECT country.*
FROM
  (SELECT SurfaceArea/Population AS PerCapitaSurfaceArea,
          country.*
   FROM country) AS country
RIGHT JOIN
  (SELECT Continent,
          MIN(SurfaceArea/Population) AS PerCapitaSurfaceArea
   FROM country
   WHERE GNP!=0
     AND Population!=0
   GROUP BY Continent) AS assist ON country.PerCapitaSurfaceArea=assist.PerCapitaSurfaceArea
AND country.Continent=assist.Continent
ORDER BY PerCapitaSurfaceArea DESC;

*注意：population 为 0 不记录

作业3

1、将关系Country分成了两个子关系，Region=‘Middle East’和Region=‘Southern and Central Asia’

分片条件 C1: Region='Middle East'

C2: Region=‘Southern and Central Asia’

证明：和是Country水平分片

欲证明，和是Country水平分片，须证明如下三点：
C_1，C_2 和Country关系模式相同；
C_1∪C_2=Country
C_1∩C_2=Ф

2. 有Country关系如下

Country { code , Name , Continent , Region , population ,GNP }

假设 C1{ code , Name , Continent , Region }

C2{code , population ,GNP}

证明：C1和C2是Country垂直分片
根据垂直分片条件，可知：
C_1和C_2是Country的无损分解。
Attr(C_1 )∪Attr(C_2 )=Attr(Country)
Attr(C_1 )∩Attr(C_2 )={Code}
因此，C_1和C_2是Country的垂直分片