CSCI3287 Homework 2 Solution

Homework Two – Database Design

Overview
This project will give you hands-on practice in working with MySQL Workbench (or similar tool) to create a key-based, fully attributed, 3 NF data model. In this project you will design a database, 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 professional, industrial-strength data modeling tool
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 using the output of homework number one as your input. Your model should include:
• All tables with primary key attributes defined
• All attributes with data type, length, and constraints defined
• Proper table names, key names and attribute names
• All relationships between tables showing captions both ways, and proper optionality and cardinality
2. The DDL necessary to create the database you have designed.
3. Documentation of any assumptions you made regarding unclear or missing requirements.
For example:

CSCI 3287 Database Systems Page 1


CSCI3287 Database Systems
Homework Two – Database Design

• If you are creating surrogate keys, name the key and explain why you are creating the surrogate.
• Note the fact that you are using auto-increment for any created surrogate keys.

Submission
Use the submission link found in the Homework # 2 Assignment section of the Week 4 module in Canvas -- which is the same place where you got this file.
Your results for this project assignment can 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. The final deliverable document you submit for this project must consist of three sections:
The first section is a picture (screen shot) of your complete 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. You can copy the DDL as text from MySQL workbench, and paste it into your document. 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. You can remove any extraneous DDL statements, such as the “SET” statements to capture and reset system variables, and/or the “Engine=INNODB” parameter settings.
Third is list (bullet points) of any assumptions you found necessary to support decisions you made about the process and/or database design.
INPUT
For your data model drawing, please use the 3NF result from your homework number one.

CSCI 3287 Database Systems Page 2