Starting from:
$24.99
Homework #2 – Data Modeling
Database Systems
Overview
This project is worth 100 points (out of 1000) toward your final grade. It is due on Sunday, October 14,
at 11:59 p.m. Late submissions will be penalized 50% during a 3-day grace period up until Wednesday,
October 14, 11:59 p.m. After that time, no late work will be accepted. Your submission should be a PDF
document submitted as a file via the link found in the Project Assignment section of the Week 6 Moodle
October 1 – 7, which is the same place where you got this file.
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 Project 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.
3. Feel free to work with our course CA (David Du) via email or during his office hours with specific
questions about your model or your modeling software.
Overview
This project is worth 100 points (out of 1000) toward your final grade. It is due on Sunday, October 14,
at 11:59 p.m. Late submissions will be penalized 50% during a 3-day grace period up until Wednesday,
October 14, 11:59 p.m. After that time, no late work will be accepted. Your submission should be a PDF
document submitted as a file via the link found in the Project Assignment section of the Week 6 Moodle
October 1 – 7, which is the same place where you got this file.
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 Project 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.
3. Feel free to work with our course CA (David Du) via email or during his office hours with specific
questions about your model or your modeling software.
1 file (702.4KB)
