DatabaseDesign

Assignment #2

Due Date: Friday, Feb. 19, 2021

For this assignment you have to use a software tool to draw the ERM

of the following problem.:

The following links provide some ER drawing tools; hence you may

investigate them and pick one of them that works for you.

http://dia-installer.de/shapes/ER/index.html.en

https://products.office.com/en-us/Visio/visio-professional-2013-free-trialflowchart-

software

https://www.omnigroup.com/omnigraffle

https://www.gliffy.com/

Or any software that you may find and suitable for drawing ERM

Hand drawing ERM are not accepted.

The Problem

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

http://dia-installer.de/shapes/ER/index.html.en

https://products.office.com/en-us/Visio/visio-professional-2013-free-trialflowchart-software

https://products.office.com/en-us/Visio/visio-professional-2013-free-trialflowchart-software

https://www.omnigroup.com/omnigraffle

https://www.gliffy.com/

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 don’t
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.