Homework #2 – Data Modeling

This project will give you hands-on practice in working with MySQL Workbench (or similar tool) to create
a key-based, fully attributed, third normal form data model. In this project you will use the data that
you normalized in the first homework assignment, draw a data model to represent the design, then
create a “physical model” of your design in the format of DDL (table create statements.)
Objectives
1. Become familiar with a data modeling tool of your choice
2. Demonstrate ability to create a complete data model.
3. Use the data modeling software to generate the DDL to create the database you have designed
Deliverables
1. A key-based, fully-attributed data model depicting your database design
2. The DDL necessary to create the database you have designed.
3. Documentation of any assumptions you made regarding unclear or missing requirements
Submission
Use the submission link in the Homework Assignment section of the Week 6 Moodle October 1 – 7,
which is the same place where you got this file.
Your results for this project assignment should be captured in a document (such as a .txt file, MS Word
or similar tool.) Please then save your final deliverable document as a PDF for submission. Use the link
found in the Homework Assignment section of WEEK SIX in the Moodle site to submit your work for
grading. If you are doing “PAIR PROGRAMMING” on this assignment, please be sure to identify the
name of your “programming” partner on your submission. Maximum team size is TWO people. You
must EACH submit your own final deliverable document for this homework.

The final deliverable document you submit for this project must consist of three sections:
The first section is a picture of your entire data model. The second section is text containing all DDL
generated by your data modeling software tool necessary to create the database you have designed.
The DDL must include create statements for all tables in your database (including definition of all data
columns.) Primary and foreign keys must be defined. DDL must include all constraints, including foreign
key references. DDL may include create statements for important secondary indexes. Third is a list
(bullet points) of any assumptions you found necessary to support decisions you made about the
process and/or database design.
Guidelines, Step-by-Step
1. Draw a DATA MODEL to depict your database design for this business application. In this data
model be sure to include and account for the following:
a. Identify all the ENTITIES listed in HW # 1.
b. Define a unique identifier (primary key attribute) for each of the entities in this
scenario. Surrogate keys (auto_increment) may be assigned where useful.
c. Define and name the relationships between the entities described in this scenario and
identify the cardinality and optionality of each relationship. Names should appear on
both sides of the relationship and should be read clockwise.
d. Resolve any many-to-many relationships described in this scenario.
e. Identify all non-key attributes, data type, length, and constraints.
f. Identify all foreign keys.
2. Once the data model is complete, using the modeling tool, export the DDL necessary to create
the database including all tables, keys, constraints and any indexes. You should edit the DDL to
remove any generated statements that you do not understand or cannot explain what the
statement is for.