1. Create a new table called CATEGORIES. The columns should be CatCode and CatDesc. The CatCode...

 

Transcribed Image Text:

1. Create a new table called CATEGORIES. The columns should be CatCode and CatDesc. The CatCode column should store a maximum of 2 characters, and the CatDesc column should store a maximum of 10 characters. 2. Create a new table called WOKERS containing four columns: Emp#, Lastname, Firstname, and Job_class. The Job_class column should be able to store character strings up to a maximum length of four, but the column values shouldn't be padded if the value has less than four characters. The Emp# column contains a numeric ID and should allow a five-digit number. Use column sizes you consider suitable for the Firstname and Lastname columns. 3. Add two columns to the WOKERS table. One column, named EmpDate, contains the date of employment for each employee, and its default value should be the system date. The second column, named EndDate, contains employees' date of termination. 4. Modify the Job_class column of the WOKERS table so that it allows storing a maximum width of two characters. 5. Delete the EndDate column from the WOKERS table. 6. Rename the WOKERS table as EMPS. 7. Create a new table called NOVEL: It will have the following columns: ISBN, Cost, Retail, and Category. 8. Rename the new table PRICING_OF_BOOK. 9. Mark the Category column of the PRICING_OF_BOOK table as unused. Verify that the column is no longer available. Add some data into PRICING_OF_BOOK table. 10. Truncate the PRICING_OF_BOOK table, and then verify that the table still exists but no longer contains any data. 11. Delete the PRICING_OF_BOOK table permanently so that it isn't moved to the recycle bin. Question 2 (5 marks) Create two tables based on the E-R model shown in Figure 4-41 and the business rules in the following list for a work order tracking database. Include all the constraints in the CREATE TABLE statements. You should have only two CREATE TABLE statements and no ALTER TABLE statements. Name all constraints except NOT NULLs. Project Proj P_name P desc P_budget FIGURE 4-41 Workorders ● Woll Proj Wo_desc Wo_assigned Wo hours Wo start. Wo due Wo complete Will hold a name for the person assigned Workorders E-R model • Use your judgment for column datatypes and sizes. Proj# and Wo# are used to uniquely identify rows in these tables. • Each project added must be assigned a name, and no duplicate project names are allowed. Dates Will hold a Y or N • Each work order must be assigned to a valid project when added and be assigned a description and number of hours. • Each work order added must have a different description. • The number of hours assigned to a work order should be greater than zero. • If data is provided for the Wo_complete column, only Y or N are acceptable values. • Each table should have 5 data records in them Create and execute the SQL statements needed to enforce the data relationships among these tables. 1. Create a new table called CATEGORIES. The columns should be CatCode and CatDesc. The CatCode column should store a maximum of 2 characters, and the CatDesc column should store a maximum of 10 characters. 2. Create a new table called WOKERS containing four columns: Emp#, Lastname, Firstname, and Job_class. The Job_class column should be able to store character strings up to a maximum length of four, but the column values shouldn't be padded if the value has less than four characters. The Emp# column contains a numeric ID and should allow a five-digit number. Use column sizes you consider suitable for the Firstname and Lastname columns. 3. Add two columns to the WOKERS table. One column, named EmpDate, contains the date of employment for each employee, and its default value should be the system date. The second column, named EndDate, contains employees' date of termination. 4. Modify the Job_class column of the WOKERS table so that it allows storing a maximum width of two characters. 5. Delete the EndDate column from the WOKERS table. 6. Rename the WOKERS table as EMPS. 7. Create a new table called NOVEL: It will have the following columns: ISBN, Cost, Retail, and Category. 8. Rename the new table PRICING_OF_BOOK. 9. Mark the Category column of the PRICING_OF_BOOK table as unused. Verify that the column is no longer available. Add some data into PRICING_OF_BOOK table. 10. Truncate the PRICING_OF_BOOK table, and then verify that the table still exists but no longer contains any data. 11. Delete the PRICING_OF_BOOK table permanently so that it isn't moved to the recycle bin. Question 2 (5 marks) Create two tables based on the E-R model shown in Figure 4-41 and the business rules in the following list for a work order tracking database. Include all the constraints in the CREATE TABLE statements. You should have only two CREATE TABLE statements and no ALTER TABLE statements. Name all constraints except NOT NULLs. Project Proj P_name P desc P_budget FIGURE 4-41 Workorders ● Woll Proj Wo_desc Wo_assigned Wo hours Wo start. Wo due Wo complete Will hold a name for the person assigned Workorders E-R model • Use your judgment for column datatypes and sizes. Proj# and Wo# are used to uniquely identify rows in these tables. • Each project added must be assigned a name, and no duplicate project names are allowed. Dates Will hold a Y or N • Each work order must be assigned to a valid project when added and be assigned a description and number of hours. • Each work order added must have a different description. • The number of hours assigned to a work order should be greater than zero. • If data is provided for the Wo_complete column, only Y or N are acceptable values. • Each table should have 5 data records in them Create and execute the SQL statements needed to enforce the data relationships among these tables.

Answer rating: 100% (QA)

It seems you need assistance with SQL tasks related to creating and modifying database tables as well as establishing data relationships Lets go through the steps individually based on the instruction... View the full answer