It is VERY important to consider that altering tables can require a bit of time for very large tables, and that while the table is locked, other users and processes cannot operate. Consequently, this kind of modification should not be done during peak operating hours in a production operation( as a student in a lab exercise, working on your own database, you may do this at any time) but ideally in hours during which the business does not normally operate. In cases where round-the-clock, high availability of a database is required, other approaches may be required. Addressing this problem in a high-availability, high-demand environment is an advanced topic, study of which is outside the scope of this course. Use the outline below to construct your script. Show all commands in your answer sheet along with the output of the commands.
lock table customers write; set foreign _ key _ checks = 0;
– Replace this comment with your ALTER TABLE command to add the auto _ increment feature to the PK field
set foreign _ key _ checks = 1; unlock tables; – statements to insert two rows into the table – verify auto _ increment with a select statement
2. The Vice President of Marketing for your firm wants the firm’ s sales representatives to be able to directly view and edit customer details, but only for the state to which a particular sales representative is assigned. You have suggested that this need can be addressed with a view. For example, a view could be created for one particular state, and user account permissions for accessing that view granted only to sales representatives from that state. The VP has asked you to quickly create a simple proof-of-concept demonstrating how this might work. Complete the following steps:
a. Construct a view on the customers table called CA _ CUSTOMERS that consists of all data about customers that live in California.
b. Display the data using this view to verify that only customers that reside in California are visible.
c. Prove that It is possible to add or update records through this view by updating the record for Karina Lacy to change the spelling of Karina’ s last name to Lacie.