$30
HW- 2 Data Modelling Design CSCI 3287: Design and Analysis of Data Systems
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.