North Lake College Computer Science Database Design Worksheet

North Lake College Computer Science Database Design Worksheet

Computer Science Database Design Worksheet

Database Design
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-trialflowchartsoftware
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
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.