University of London CO2209 Database Systems Assignment Solution

This assignment solution covers a database systems coursework, addressing various aspects of database design and implementation. Part A analyzes an Entity-Relationship Diagram (ERD), discussing cardinality, strong and weak entities, and relationships between tables. Part B presents SQL queries to retrieve specific data, such as countries meeting GDP and infant mortality criteria and cities with populations exceeding New York's. Part C focuses on database normalization, while Part D involves creating an ERD and answering related questions. Part E provides brief definitions of database concepts, and Part F explores database hacking. The solution includes detailed explanations, SQL queries, and database analysis, providing a comprehensive understanding of the subject.

Running head: GRAPHICAL OBJECT-ORIENTED AND INTERNET PROGRAMMING
GRAPHICAL OBJECT-ORIENTED AND INTERNET PROGRAMMING
Name of the Student
Name of the University
Author Note

1GRAPHICAL OBJECT-ORIENTED AND INTERNET PROGRAMMING
Table of Contents
Part A...............................................................................................................................................2
Part A, Question 1........................................................................................................................2
Part A, Question 2........................................................................................................................3
Part A, Question 3........................................................................................................................3
Part A, Question 4......................................................................................................................10
Part B: Query.................................................................................................................................12
Part C: Normalization....................................................................................................................19
Part C, Question 1......................................................................................................................19
Part C, Question 2......................................................................................................................19
Part C, Question 3......................................................................................................................20
Part D Entity Relationship Diagram..............................................................................................20
Part D, Question 1......................................................................................................................21
Part D, Question 2......................................................................................................................22
Part D, Question 3......................................................................................................................23
Part E: Brief Definitions................................................................................................................24
Part F: Database hacking...............................................................................................................25
Referencing....................................................................................................................................28

2GRAPHICAL OBJECT-ORIENTED AND INTERNET PROGRAMMING
Part A
Part A, Question 1
i. In terms of the Cardinality representation the downloaded ERD does not show any cardinality
between the tables. Whereas the ERD in subject guide uses (min, max) notation for showing the
cardinality between the tables.
ii. A strong entity has a primary key which is denoted by a Rectangle in ERD. On other hand, the
weak entities only have partial keys which is used to discriminate between tuples. It is denoted
by the double rectangle in ERD.
iii. Yes, the weak entities can be identified by the double rectangle used for the tables in the ER
diagram. Weak entities are the entities which do not have any key attribute or depend on the
other entities.
iv. The relationship between the Country and Religion is created in a way that the Country table
is the Parent table for the ‘country’ attribute in the Religion table. It is possible to enter any
country without even entering any religion record for that country. On other hand, a religion
record cannot be created for a country which has not be entered in the Country table as the
‘country’ in the Religion table is a foreign key referenced to the ‘code’ attribute in the Country
table (Powers 2019). Entering religion without inputting country will invoke the foreign key
constrain on Religion table.
v. In order to convert the Religion table into a strong entity type, key attributes have to be added
or created in the table. Currently there are no key attributes present in the table which can be
identified uniquely. The conversion can be done by adding a ReligionID attribute as primary key

3GRAPHICAL OBJECT-ORIENTED AND INTERNET PROGRAMMING
or creating the religion name as primary key. In both the cases, the values cannot be repeated in
the religion table. In this scenario, any value can be recorded into the Religion table without
having respective Country. As the religion table will become string entity, to maintain the
relationship with the Country table, the primary keys of both the tables must be added into
another table (Countryhasreligion) as foreign keys and composite primary keys (Schwichtenberg
2018).
Part A, Question 2
i. There are total 43 tables in the diagram.
ii. Total 33 table listed after running SHOW TABLES; command in Part A Question
3.
iii. The attributes are Country, dependent,independence ,wasdependent and government.
iv. The attributes are the Country, dependent,independence and government.
v. The conclusion can be drawn about the database documentation can have more
relations on a conceptual level and it can explain the structure of the database. The
dependencies, set of relations and schema can be very helpful in understanding the
database. However, the physical schema of the database can be little different from
the conceptual schema.
Part A, Question 3
Show Tables
MariaDB [mondial]> show tables;
+-------------------+
| Tables_in_mondial |
+-------------------+

4GRAPHICAL OBJECT-ORIENTED AND INTERNET PROGRAMMING
| borders |
| city |
| continent |
| country |
| desert |
| economy |
| encompasses |
| ethnicgroup |
| geo_desert |
| geo_estuary |
| geo_island |
| geo_lake |
| geo_mountain |
| geo_river |
| geo_sea |
| geo_source |
| island |
| islandin |
| ismember |
| lake |
| language |
| located |
| locatedon |
| mergeswith |
| mountain |
| mountainonisland |
| organization |
| politics |
| population |
| province |

5GRAPHICAL OBJECT-ORIENTED AND INTERNET PROGRAMMING
| religion |
| river |
| sea |
+-------------------+
33 rows in set (0.002 sec)
ECONOMY
MariaDB [mondial]> DESCRIBE economy;
+-------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+-------+
| Country | varchar(4) | NO | PRI | NULL | |
| GDP | float | YES | | NULL | |
| Agriculture | float | YES | | NULL | |
| Service | float | YES | | NULL | |
| Industry | float | YES | | NULL | |
| Inflation | float | YES | | NULL | |
+-------------+------------+------+-----+---------+-------+
6 rows in set (0.039 sec)
MariaDB [mondial]> select count(*) from economy;
+----------+
| count(*) |
+----------+
| 238 |
+----------+
1 row in set (0.001 sec)
MariaDB [mondial]> show index from economy;

6GRAPHICAL OBJECT-ORIENTED AND INTERNET PROGRAMMING
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------
+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality |
Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------
+------+------------+---------+---------------+
| economy | 0 | PRIMARY | 1 | Country | A | 214 | NULL | NULL |
| BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------
+------+------------+---------+---------------+
1 row in set (0.002 sec)
ISMEMBER
MariaDB [mondial]> describe ismember;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| Country | varchar(4) | NO | PRI | NULL | |
| Organization | varchar(12) | NO | PRI | NULL | |
| Type | varchar(35) | YES | | member | |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.046 sec)
MariaDB [mondial]> select count(*) from ismember;
+----------+
| count(*) |
+----------+
| 8008 |
+----------+
1 row in set (0.006 sec)
MariaDB [mondial]> show index from ismember;

7GRAPHICAL OBJECT-ORIENTED AND INTERNET PROGRAMMING
+----------+------------+----------+--------------+--------------+-----------+-------------+----------
+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality |
Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+--------------+-----------+-------------+----------
+--------+------+------------+---------+---------------+
| ismember | 0 | PRIMARY | 1 | Country | A | 454 | NULL | NULL |
| BTREE | | |
| ismember | 0 | PRIMARY | 2 | Organization | A | 7732 | NULL | NULL
| | BTREE | | |
+----------+------------+----------+--------------+--------------+-----------+-------------+----------
+--------+------+------------+---------+---------------+
2 rows in set (0.002 sec)
LANGUAGE
MariaDB [mondial]> describe language;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| Country | varchar(4) | NO | PRI | NULL | |
| Name | varchar(50) | NO | PRI | NULL | |
| Percentage | float | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.035 sec)
MariaDB [mondial]> select count(*) from language;
+----------+
| count(*) |
+----------+
| 144 |
+----------+

8GRAPHICAL OBJECT-ORIENTED AND INTERNET PROGRAMMING
1 row in set (0.001 sec)
MariaDB [mondial]> show index from language;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------
+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality |
Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------
+------+------------+---------+---------------+
| language | 0 | PRIMARY | 1 | Name | A | 144 | NULL | NULL |
| BTREE | | |
| language | 0 | PRIMARY | 2 | Country | A | 144 | NULL | NULL |
| BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------
+------+------------+---------+---------------+
2 rows in set (0.002 sec)
ORGANIZATION
MariaDB [mondial]> select count(*) from organization;
+----------+
| count(*) |
+----------+
| 153 |
+----------+
1 row in set (0.001 sec)
MariaDB [mondial]> show index from organization;
+--------------+------------+---------------+--------------+--------------+-----------+-------------+----------
+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+---------------+--------------+--------------+-----------+-------------+----------
+--------+------+------------+---------+---------------+

9GRAPHICAL OBJECT-ORIENTED AND INTERNET PROGRAMMING
| organization | 0 | PRIMARY | 1 | Abbreviation | A | 153 | NULL |
NULL | | BTREE | | |
| organization | 0 | OrgNameUnique | 1 | Name | A | 153 | NULL |
NULL | | BTREE | | |
+--------------+------------+---------------+--------------+--------------+-----------+-------------+----------
+--------+------+------------+---------+---------------+
2 rows in set (0.002 sec)
POPULATION
MariaDB [mondial]> describe population;
+-------------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+------------+------+-----+---------+-------+
| Country | varchar(4) | NO | PRI | NULL | |
| Population_Growth | float | YES | | NULL | |
| Infant_Mortality | float | YES | | NULL | |
+-------------------+------------+------+-----+---------+-------+
3 rows in set (0.042 sec)
MariaDB [mondial]> select count(*) from population;
+----------+
| count(*) |
+----------+
| 238 |
+----------+
1 row in set (0.001 sec)
MariaDB [mondial]> show index from population;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------
+--------+------+------------+---------+---------------+

10GRAPHICAL OBJECT-ORIENTED AND INTERNET PROGRAMMING
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality |
Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------
+--------+------+------------+---------+---------------+
| population | 0 | PRIMARY | 1 | Country | A | 238 | NULL | NULL |
| BTREE | | |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------
+--------+------+------------+---------+---------------+
1 row in set (0.002 sec)
Part A, Question 4
i. The Size of the Mondial database is approximate 1.4 Megabytes.
MariaDB [mondial]> select table_schema as `Database`,
-> table_name AS `Table`,
-> round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
-> FROM information_schema.TABLES
-> WHERE table_schema = 'mondial'
-> ORDER BY (data_length + index_length) DESC ;
+----------+------------------+------------+
| Database | Table | Size in MB |
+----------+------------------+------------+
| mondial | ismember | 0.44 |
| mondial | city | 0.27 |
| mondial | province | 0.19 |
| mondial | geo_sea | 0.08 |
| mondial | located | 0.08 |
| mondial | islandin | 0.05 |
| mondial | religion | 0.05 |
| mondial | geo_island | 0.05 |
| mondial | ethnicgroup | 0.05 |
| mondial | locatedon | 0.05 |

11GRAPHICAL OBJECT-ORIENTED AND INTERNET PROGRAMMING
| mondial | organization | 0.03 |
| mondial | country | 0.03 |
| mondial | borders | 0.02 |
| mondial | island | 0.02 |
| mondial | sea | 0.02 |
| mondial | geo_source | 0.02 |
| mondial | river | 0.02 |
| mondial | geo_river | 0.02 |
| mondial | geo_mountain | 0.02 |
| mondial | population | 0.02 |
| mondial | geo_lake | 0.02 |
| mondial | politics | 0.02 |
| mondial | geo_estuary | 0.02 |
| mondial | mountainonisland | 0.02 |
| mondial | geo_desert | 0.02 |
| mondial | mountain | 0.02 |
| mondial | mergeswith | 0.02 |
| mondial | encompasses | 0.02 |
| mondial | economy | 0.02 |
| mondial | desert | 0.02 |
| mondial | language | 0.02 |
| mondial | lake | 0.02 |
| mondial | continent | 0.02 |
+----------+------------------+------------+
33 rows in set (0.003 sec)
ii. The largest relation is ‘ismember’ of total 0.44 Megabytes and smallest are the, continent,
lake, language, desert, economy etc. of 0.02 Megabytes.