Question

Written Assignment#1: Preliminary Database Design

Preliminary Database Design
The purpose of this project is to give you some experience in database design. We will explore both theoretical and
practical aspects of the database design steps. Once you master it well, it is a simple matter of translating the
theoretical concepts you developed into the terms of a concrete DBMS to implement the database.
Conceptual/Logical Database Design
You must choose some facet of real life that you feel needs to be modeled by a database. Then, you must design a
relational database schema that adequately represents this information. In the process, you must use standard
relational modeling tools and techniques we examined in class. In concrete terms, this part of the project consists of
the following steps:
Begin by choosing an application, e.g. the database for a local volleyball league or the database for a Society of
Roasted-Shrimp Fans. It must be non-trivial, but not huge. As a general guideline, you should probably have about
five or more entity sets and similar number of relationship sets.
1. (30 points) Give an ER diagram, which will reflect your choice of the entity sets, their relevant attributes,
and the relationships among them. Again, make your relationships realistic and meaningful.
2. (30 points) Specify the assumptions about the database in English (informally). Here you talk about
attributes, keys, the nature of relationships between entities, etc. Do not discuss something that is obvious
(e.g., that a student can take several courses). In addition, don't make too many simplifying assumptions.
3. (40 points) Convert the ER diagram into a relational database schema. (No normalization Yet. We
will cover normalization later.)

Answer 1

Since you asked not to make too many simplifying assumptions I've taken a case that can be understandable and all the assumptions in this case are general and can be understanded by anyone.

Obvious assumptions and ER diagram of Bank database:

Now converting this ER diagram into relational: