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 key-based, 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 and to create a complete data model.
2. Use the data modeling software to generate the DDL to create the database you have designed.
CSCI 3287: Design and Analysis of Data Systems Page 1
HW- 2 Data Modelling Design
Deliverables
1. A key-based, fully-attributed Data Model / ERD depicting your database design with inputs given below. Your model should include:
◦ 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.
INPUT
Please use below input (entities and attributes) for this assignment:
Department (dept_no, dept_name)
Employee (employee_no, employee_name)
Project (project_code, project_title, project_manager, project_budget)
Skill (skill_code, skill_type)
Additionally, there are 2 attributes:
Hourly_rate (amount) is functionally dependent on project and employee.
Skill_level (number) is functionally dependent on skill and employee.
CSCI 3287: Design and Analysis of Data Systems Page 2
HW- 2 Data Modelling Design
Here are assumptions on relationships among tables:
• Department has zero, one or more employees, i.e. Department may exists 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 belongs to zero, one or more employee. Make sure to resolve many-to-many relationship, if any.
CSCI 3287: Design and Analysis of Data Systems Page 3
