Ban 453: Data management & Sql | fall 2022 | boston
Project 1: database conceptual & logical modeling
In this assignment, you will develop a business/organizational case study and design a
database that will meet the needs (or a subset of needs) of your business or organization.
You can be creative with your case study; your case study can align with a past business
idea you’ve developed, an industry you find interesting, and/or an industry that you would
like to pursue a career in post-graduation. Below are some sample industries and business
ideas that may inspire ideas:
• Banking
• Academia
• Healthcare
• Client Management
• Event Management
• Project Management
Your database must ultimately include at least 6 tables and at least 30 columns in total
(it can contain more tables/columns if you would like or if your business case requires).
You will only need to provide three rows of sample data for each database table (more on
that later). However later in the semester you will need incorporate 20+ rows of data into
your database as part of Project #2.
You will produce a report that describes the business needs that have led to the need for
your database, details your business rules. Your report will also include both a conceptual
and logical model of your database, as well as data dictionaries.
Detailed components of the report are as follows:
1. Overview: This section should include:
a. An overview of your business (and/or the component of the business that
you will be creating a database for) (short paragraph)
. A summary of your business’s data needs and the motivation for creating a
database (short paragraph)
c. Mission Statement (Please clearly label your Mission Statement with a
header that says “Mission Statement:” on a new line following the above
paragraphs. Your mission statement should be roughly one to three
sentences.
d. Mission Objectives (Please clearly label your Mission Objective with a header
that says “Mission Objectives:” on a new line following the above Mission
Statement section. You should have roughly 4-10 Mission Objectives that are
oughly 1-2 sentences each.
e. Business Rules
i. In this section you will detail all of the applicable business rules for
your business that will inform your relationships (e.g., each employee
has an employee ID that is unique to them; each employee works in
one and only one department)
ii. This can be formatted within a table or in bullets
2. Conceptual Data Model
a. This should be formatted as an ER-Diagram, displaying all of your entities,
columns, relationships, and notations for multiplicity.
. Below your Conceptual Data Model, please
iefly describe the function of
each of your entities and a general overview of the type of information they
contain.
3. Conceptual Data Dictionary
a. This dictionary will contain definitions for terminology that may not be clear
(e.g., you do not need to define “first name”, but you would need to define
something like “employee ID” (e.g., employee ID = unique ID assigned to each
employee at date of hire. Does not change and cannot be shared by multiple
employees).
. You do not need to define column names specifically, just key terms that will
e incorporated into your database
4. Normalized (to 3NF), Logical Data Model
a. This should include all final, normalized tables with their column names
(primary keys and foreign keys labeled), relationships, and multiplicity
notations.
. Below your Logical Data Model diagram, please describe in a couple of
paragraphs that process that you went through to transform your model
from a conceptual data model to a normalized logical data model, and why it
is important to transform a normalized logical data model prior to building a
physical database.
5. Logical Data Dictionary
a. This should be in the form of an Excel Workbook that you will submit with
your report. You should have a tab for each table of your database. Within
each tab, you should list the names of your table’s columns going down (this
seems counterintuitive because your columns will actually display as rows in
your dictionary, but this makes it more readable), and sample data
associated with each column going across. See below as an example:
Lastly, please keep the following reminders in mind:
• Each figure should be clear, readable and should have a title
• When you explain how you transformed your conceptual model to your logical,
normalized model, please ensure that your explanation is specific to your database
and your entities/tables (i.e., don’t just explain the general steps that should be
taken to create a logical model or to normalize to 3NF).