HW- 2 Data Modelling Design

HW- 2 Data Modelling Design
CSCI 3287: Design and Analysis of Data Systems Page 1
Overview
• Assignment points: 10 points or 10% toward your final grade.

• Submission: submit in Canvas in pdf or word doc.
For this assignment you will need to download and install MySQL workbench on your computer.
Downloads are available at: https://dev.mysql.com/downloads/workbench/
Choose the download file that matches your computer’s OS and version. We will be installing
MySQL workbench during class also. Windows based installation files are also available in
Canvas.
This project will give you hands-on practice in working with MySQL Workbench to create a keybased, fully attributed 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 data modeling tool (MySQL) and to create a complete data
model/ERD.
2. Generate DDL using MySQL data modeling forward engineer.
HW- 2 Data Modelling Design
CSCI 3287: Design and Analysis of Data Systems Page 2
Deliverables
1. A key-based, fully-attributed Data Model / ERD depicting your database design with inputs
given below. Your model should include: (Grade weight: 90%)
• All tables with proper entity name, primary key and foreign key attributes defined.
• All attributes with data type, length, and constraints defined.
• All relationships showing captions and proper optionality/cardinality relationship.
2. The DDL generated by your data modeling software tool necessary to create the database
you have designed. (Grade weight: 10%)
INPUT
Please use below input (entities and attributes) for this assignment:
Yellow highlighted are PKs.
Department (dept_no, dept_name, dept_address)
Employee (employee_no, employee_name)
Project (project_code, project_title, project_manager, project_budget)
Skill (skill_code, skill_type)
HW- 2 Data Modelling Design
CSCI 3287: Design and Analysis of Data Systems Page 3
Additionally, there are 2 other attributes, add these attributes into appropriate entity:
Hourly_rate is functionally dependent on project and employee.
Skill_level is functionally dependent on skill and employee.
Here are assumptions on relationships among tables:
- Department may have zero, one or more employees, i.e. Department may exist
without any employees.
- Each employee must belong to only one department.
- Each project must include one or many employees.
- Each employee must work on one or many projects.
- Each employee may have zero, one or more skills.
- Each skill may belong to zero, one or more employee.
Make sure to resolve many-to-many relationship, if any.