You are allowed to complete this assignment in teams or pairs if you wish (optio

Question

You are allowed to complete this assignment in teams or pairs if you wish (optional). If you do so, clearly state the names of all contributors. Answer all questions within this Word document.

Name(s):

Database Administration: Part 2 (20 pts)

Background: Bev’s Boutique is a small, local business that sells hand-made products in-store only (no online sales). The database they use as part of their custom-built point-of-sale system is designed as follows:

CUSTOMER (CustomerID, LastName, FirstName, Address, City, State, ZIP, Phone, Email)

EMPLOYEE (EmployeeID, LastName, FirstName, Phone, Email)

VENDOR (VendorID, CompanyName, ContactLastName, ContactFirstName, Address, City, State, ZIP, Phone, Fax, Email)

ITEM (ItemID, ItemDescription, UnitCost, UnitPrice, QuantityOnHand, VendorID)

SALE (SaleID, CustomerID, EmployeeID, SaleDate, SubTotal, Tax, Total)

SALE_ITEM (SaleID, SaleItemID, ItemID, Quantity, ItemPrice, ExtendedPrice)

Note: SALE_ITEM represents a line item on a receipt/invoice. It shows details about items sold in a specific sale—namely, the quantity sold, the price sold (sometimes this is different from the UnitPrice in the ITEM table), and the extended price (quantity x price). Essentially, this is an association table between SALE and ITEM to track information about specific items sold in a specific sale.

Some common transactions at Bev’s (each is independent from the other two):

When new items from vendors arrive, the office personnel unpack the items, put them in the stockroom, and run an Item Quantity Received Transaction that adds the quantity received to QuantityOnHand. If the item has never been stocked before, the transaction also adds the item to the ITEM table in addition to adding the correct QuantityOnHand.

When item pricing needs to be changed, another transaction called an Item Price Adjustment Transaction is run to adjust UnitCost and UnitPrice.

Sales may occur at any time, and when a sale occurs, the Sale Transaction is run. A new record is added to the SALE table, and every time a SALE_ITEM line is entered, the input Quantity is subtracted from QuantityOnHand in ITEM and then the ItemPrice is set to the UnitPrice.

(Incidentally, these transactions are run through a database application, such as sales software, an Access Form, or a cash register. The applications then read/insert/update/delete data in the database. For the sake of the assignment, we are just thinking about what happens at the database level, even if employees aren’t directly editing the database.)

Part 2: Security and Backup/Recovery

Suppose that management at Bev’s Boutique identifies four groups of users: sales personnel, managers, administrative personnel, and system administrators. Managers and administrative personnel can perform Item Quantity Received Transactions, but only managers can perform Item Price Adjustment Transactions. Sales personnel and managers can complete sales transactions, but administrative personnel are able to look up previous sales if customers have questions. Only management is allowed to remove data about transactions. Fill out the table below to indicate the rights that you think would be appropriate for this situation. Use the following rights in your answer: read, insert, update, delete, modify structure, grant rights. There are examples in the textbook or online materials that might help you. (12 pts)

(You may need to make some of your own assumptions. If so, state them here.)

DATABASE RIGHTS GRANTED

Table

Sales

Management

Administrative

System Administrator

SALE

SALE_ITEM

ITEM

Bev’s Boutique has developed the following procedure for backup and recovery. The company backs up the entire database from the server to tape every Saturday night. The tapes are then taken to a safety deposit box at a local bank on the following Thursday. Printed paper records of all sales are kept in the shop for 5 years. If the database is ever lost, the plan is to restore the database from the last full backup and reprocess all the sales records.

(a) What problems might occur? (2 pts)

(b) Do you think “reprocessing” is sufficient for Bev’s Boutique? (2 pts)

(c) What would be the pros and cons of using a rollback/rollforward system instead? (2 pts)

(d) Describe any changes you think the company should make to this backup/recovery system. (2 pts)  

DATABASE RIGHTS GRANTED

Table

Sales

Management

Administrative

System Administrator

SALE

SALE_ITEM

ITEM

Explanation / Answer

DATABASE RIGHTS GRANTED

Table

Sales

Management

Administrative

System Administrator

SALE

insert

insert, delete

read

have all rights

SALE_ITEM

read, update

read, update, delete

read

have all rights

ITEM

read, update

read, update, delete

read, update

have all rights

Assuming System Administrator are the one who have built this database and have all kinds of rights and Sales, Management, Admistrative can read data from SALES_ITEM and ITEM.

(a) Problem that might occur is to take backup in a tape drive beacuse of human errors (such as failure to change tapes, forgetting to remove a cleaning tape), software errors (such as device busy, in use), tape media error due to which unable to recover data.

(b) Yes it is sufficient but data from Sunday till the day on which database is lost will not be recover like if database is lost on Friday then data from Sunday to Friday will be lost.

(c) Pros : When an instance/transaction is terminated abruptly due to some error then rollback/rollforward systems ensures database consistency. Rollback will rollback the transaction to the beginning of the transaction and Rollforward will re-do the changes made by a transaction.

(d) Start taking backup more frequently so that if data is lost then it won't be of longer time period, add another backup system so that if one fails then other will be active as well as it will help to recover data if data is lost from one backup system.

DATABASE RIGHTS GRANTED

Table

Sales

Management

Administrative

System Administrator

SALE

insert

insert, delete

read

have all rights

SALE_ITEM

read, update

read, update, delete

read

have all rights

ITEM

read, update

read, update, delete

read, update

have all rights

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.