database hw

Using the ERD above for a company that sells big ticket items to commercial enterprises, you will be describing a security policy and database backup plan that you feel is appropriate. Business rules include the following:

· Sales to individual customers are relatively infrequent (we are talking about sales of major items, like dump trucks, plane engines or supercomputers) but there are about one hundred sales employees who may have up to 50 customers each.

· It is critically important that no finalized sales transactions or updates are ever lost. But some (limited) downtime is acceptable. There is little use of the system after normal North American working hours.

· Only the salesmen can record or update transactions, and then only for their own customers.

· Each company (through a designated purchasing agent who has the appropriate access credentials) can view its own transactions.

· Territory managers (designated though the job title) should be able to see summary data on orders for their own territories so they can manage their sales staff. They do not also sell to customers.

· Regional managers (also designated through the job title) should be able to see summaries for their regions only. They may also reassign employees to different territories within their region.

· Regional managers are the only ones who can add new products or change prices.

1. Describe a general company security policy for the database “layer” of security only (avoid getting into network security protocols or application level security for this exercise, and assume that there are security mechanisms in place that allow end-user connections to the database with the appropriate user credentials.)

2. Specify the SQL you will use to carry out your security policy for two of the business rules or areas of security concern. This may include creating a VIEW and/or ROLES and/or privilege GRANTs. Specify the rule, and BRIEFLY describe how your SQL carries this out. Please limit yourself to two areas of concern, but try to make sure you demonstrate you knowledge of how to efficiently and effectively organize your security controls.

3. Describe your general database backup plans for this system. More important than the plan itself is the rationale you provide for your decisions about the timing, backup types and backup media choices in your plan, which should be grounded in the facts provided in the business case.

GENERAL NOTES: I used a model I found and then modified a bit. Yes, there is a mix of symbols, which I don’t like – but you should be able to follow the mixed notation. If you have questions about how to interpret the model, post them in the Ask the Instructor forum for all to share.

Any ambiguity or interpretation issues you have can be resolved with clear statements of assumptions or outlining additional database tables/objects, as long as what you state is not in direct conflict with the case. I will take your assumptions into consideration when grading your work.