Database Analysis and Design
Implement the initial database design for the Enterprise Resource Planning conceptual model described by the following requirements in your selected RDBMS. You need to create the Entity-Relationship model to Database analysis, define tables, primary keys, foreign keys, additional descriptive fields (at least 2 for each entity – relationship tables do NOT need additional fields in all cases), and any attribute value constraints you need.
A Company SCHEDULEs one or more EMPLOYEEs to work zero, one, or two of three possible SHIFTs on a weekly basis.
An EMPLOYEE serves in one and only one of the following POSITIONs for any single shift : operator, supervisor, quality, designer.
Each EMPLOYEE accumulates HOURS WORKED for a four-week PAY PERIOD.
Each POSITION applies a weighting factor to the hourly rate assigned to the EMPLOYEE. You will be evaluated on:
1. The Feasibility, Flexibility, and Appropriateness of the Relationship you select between entities, including indication of the type of relationship (20 points)
Submit your Entity-Relationship Diagram generated by the DBMS in file named: lastname.firstname.Project 01.jpg (or png or pdf or gif)
2. The Attributes and KEY fields you identify for each entity (20 points)
Value Constraints you define for fields you select (10 points)
Please save all of your EXPORT DATABASE CREATE code in a notepad (text) file with the extension .sql , and submit your file(s) using your last name as part of the file name in the following format: lastname.firstname.Project 01.sql
Data Population of at least 3 values for each table illustrating the relationships (20 points)
Please run a SELECT *FROM <table>; for each table, saving the results in a notepad (text) file with the extension .dat , and submit your file(s) using your last name as part of the file name in the following format:
lastname.firstname.Project 01.dat
Supply the SQL and result for the following queries:
Please save all of your EXPORT DATABASE CREATE code in a notepad (text) file with the extension .sql , and submit your file(s) using your last name as part of the file name in the following format: lastname.firstname.Project 01.rpt
List all the employees of type “operator” with their corresponding schedule for the next weekly shift(s). (10 points)
Total the number of hours per week for the last four weekly shifts per operator and sort by the operator’s last name. (10 points)
Update Employee ID = 101 to Employee ID =1001. Display the Employee information and next weekly shift schedule before the update and after the update. (10 points)
