Develop a database with geographic and other information about the countries of the world. As a first step you need to design an ER diagram that captures relevant information about the countries of the world.
A country has a number of attributes, including a name, area, population, and GDP. A country has also a capital city, which itself has a name, population, and geographical coordinates (latitude, longitude). With each country there can be several spoken languages and several predominant religions.
Countries are located on continents, the latter having a name and area. Any two countries may have a border between them of a particular length.
A city can be part of country, and can also be situated on an island, or at a lake, river, or sea. A lake has a name, area, maximal depth, and altitude; a river has a name, length, and can flow in or out of a lake, or into a sea; a sea has a name, (maximal) depth, and may be connected with another sea. An island has a name, area, and may be part of one or more countries.
Here are the specific tasks you have to address based on the above information:
(a) Design a conceptual database schema (i.e., an Entity-Relationship diagram) for the above scenario based on the given information. Be sure to indicate primary key attributes for each entity type! Also, indicate derived (computed, not stored) attributes, if any. For each relationship type, clearly specify the semantics of a relationship type using the (min,max) notation presented in class. Note that an edge without a (min,max) specification is assumed to have the default specification (0,). Use the modeling concepts discussed in class. You dont have to specify the data types (domains) for the attributes.
(b) Identify (reasonable) constraints that you are unable to capture using the standard ER modeling constructs. Do not specify trivial domain constraints, e.g., attribute X is a positive number. Formulate these constraints in plain English. For each constraint, give a brief explanation why you need the constraint and why you cannot express it in the ER diagram, e.g., using cardinalities.
(c) Translate your ER schema into a relational schema: For each table, list all attributes. To specify the tables, use the following notation, i.e.,
tablenamei (attribute1i, . . . , attributeni) …
Plus the notation for foreign key constraints (where needed). You do not have to specify any integrity constraints other than foreign key constraints, nor the attribute domains for the tables.
