Managing Multiuser Databases - Database Processing: Fundamentals, Design, and Implementation - page 426

Assume that the owners of The Queen Anne Curiosity Shop have hired you as a
database consultant to develop an operational database having the same tables
described at the end of Chapter 7:

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, PurchaseDate, ItemCost, ItemPrice, VendorID )
SALE ( SaleID , CustomerID , EmployeeID , SaleDate, SubTotal, Tax, Total)
SALE_ITEM ( SaleID , SaleItemID , ItemID , ItemPrice)

The referential integrity constraints are:

CustomerID in SALE must exist in CustomerID in CUSTOMER
VendorID in ITEM must exist in VendorID in VENDOR
CustomerID in SALE must exist in CustomerID in CUSTOMER
EmployeeID in SALE must exist in EmployeeID in EMPLOYEE
SaleID in SALE_ITEM must exist in SaleID in SALE
ItemID in SALE_ITEM must exist in ItemID in ITEM

Assume that CustomerID of CUSTOMeR, employeeID of eMPLOyee, ItemID of ITeM,
SaleID of SALe, and SaleItemID of SALe_ITeM are all surrogate keys with values as
follows:

CustomerID Start at 1 Increment by 1

EmployeeID Start at 1 Increment by 1

VendorID Start at 1 Increment by 1

ItemID Start at 1 Increment by 1

SaleID Start at 1 Increment by 1

A vendor may be an individual or a company. If the vendor is an individual,
the CompanyName field is left blank, while the ContactLastName and
ContactFirstName fields must have data values. If the vendor is a company,
the company name is recorded in the CompanyName field, and the name of
the primary contact at the company is recorded in the ContactLastName and
ContactFirstName fields.

A. Assume that The Queen Anne Curiosity Shop personnel are the two owners, an office
administrator, one full-time salesperson, and two part-time salespeople. The two owners
and the office administrator want to process data in all tables. Additionally, the full-time
salesperson can enter purchase and sales data. The part-time employees can only read
sales data. Prepare a three- to five-page memo for the owner that addresses the following
issues:

1. The need for database administration at The Queen Anne Curiosity Shop.

2. Your recommendation as to who should serve as database administrator. Assume
that The Queen Anne Curiosity Shop is not sufficiently large that it needs or can
afford a full-time database administrator.

3. Using Figure 9-1 as a guide, describe the nature of database administration activi-
ties at The Queen Anne Curiosity Shop. As an aggressive consultant, keep in mind
that you can recommend yourself for performing some of the DBA functions.

B. For the employees described in part A, define users, groups, and permissions on data in
these six tables. Use the security scheme shown in Figure 9-15 as an example. Create a
table like that in Figure 9-14. Don't forget to include yourself.