1. At this point in the design process, you would have a detailed physical schema created for a database. How would you approach converting your schema into a database, complete with sample data? Explain your approach in two to three paragraphs.
2. Test your SQL code using Oracle XE. Once you are certain that your SQL code performs the desired tasks
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 in the following table. (Note that the JOB_CODE is the FK to JOB.)
| ATTRIBUTE(FIELD)NAME |
DATA DECLARATION
|
|
EMP_NUM
|
CHAR(3)
|
|
EMP_L NAME
|
VARCHAR( 1 5)
|
|
EMP_F NAME
|
VARCHARI 151
|
|
EMP _INITIAL
|
CHAR(1)
|
|
EMP_HIREDATE
|
DATE
|
|
JOB_CODE
|
CHAR(3)
|
2. Having created the table structure in Problem 1, write the SQL code to enter the first two rows for the table shown in Figure P7.2.
3. Assuming that the data shown in the EMP_1 table have been entered, write the SQL code that will list all attributes for a job code of 502.
4. Write the SQL code that will save the changes made to the EMP_1 table.
5. Write the SQL code to change the job code to 501 for the person whose employee number (EMP_NUM) is 107. After you have completed the task, examine the results and then reset the job code to its original value.
6. Write the SQL code to delete the row for William Smithfield. who was hired on June 22. 2004. and whose job code is 500. (Hint: Use logical operators to include all of the information given in this problem.)
7. Write the SQL code that will restore the data to its original status; that is. the table should contain the data that existed before you made the changes in Problems 5 and 6.
8. Write the SQL code to create a copy of EMP_ 1. naming the copy EMP_2. Then write the SQL code that will add the attributes EMP_PCT and PROJ_NUM to the structure. The EMP_PCT is the bonus percentage to be paid to each employee. The new attribute characteristics are:
EMP_PCT NUMBER(4,2)
PROJ_NUM CHAR(3)