Database Management Systems Design

DATABA Module HW Questions Page 1 of 3 Database Management Systems Design Week 3 Home Work Questions Information for questions: Relational Schema Table name: JOB Table name: EMPLOYEE Table name: ASSIGNMENT Table name: PROJECT DATABA Module HW Questions Page 2 of 3 As you examine Figure P7.1, note that the ASSIGNMENT table stores the JOB charge per hour the employee job code values as a “native” attribute to maintain historical accuracy of the data. The JOB_CHG_HOUR values are likely to change over time. In fact, we have made sure that there is a JOB_CHG_HOUR change that will be reflected in the ASSIGNMENT table. And, naturally, the employee primary job assignment may change, so the ASSIGN_JOB is also stored. Because these attributes are required to maintain the historical accuracy of the data, they are not redundant. For example, note that the JOB table was updated on 24-Mar-2004 for JOB_CODE 502 (Database Designer) and shows a JOB_CHG_HOUR of $125.00. The ASSIGNMENT table shows this new charge in its ASSIGN_CHG_HR, starting on 24-Mar-2004. (Look at records 1020 and 1023 in the ASSIGNMENT table. The “old” ASSIGN_CHG_HR was $105.00 per hour – see records 1011 and 1016 for 23-Mar-2004.) Given the Ch07_Review database’s structure and contents shown in Figure P7.1, use SQL commands to answer questions 10th Edition International Edition 9th Edition Page 283, Problem 1 Page 365, Question 1 Page 279, Problem 1 Write the SQL code that will create the table structure for a table named EMP_1. This table is a subset of the EMPLOYEE table. The basic EMP_1 table structure is summarized below: (Note that the JOB_CODE is the FK to JOB.) ATTRIBUTE (FIELD) NAME DATA DECLARATION EMP_NUM CHAR(3) EMP_LNAME VARCHAR(15) EMP_FNAME VARCHAR(15) EMP_INITIAL CHAR(1) EMP_HIREDATE DATE JOB_CODE CHAR(3) 10th Edition International Edition 9th Edition Page 283, Problem 2 Page 365, Question 2 Page 279, Problem 2 Having created the table structure in Question 1, write the SQL code to enter the...