QUEEN ANNE CURIOSITY SHOP PROJECT QUESTIONS Assume that The Queen Anne Curiosity

Question

QUEEN ANNE CURIOSITY SHOP PROJECT QUESTIONS Assume that The Queen Anne Curiosity Shop designs a database with the following tables. CUSTOMER (CustomerlD, LastName, FirstName, EmailAddress, EncyptedPassword, City, State, ZIP, Phone) EMPLOYEE (EmploveolD, LastName, FirstName, Phone, Email) VENDOR VendorlD, CompanyName, ContactLastName, ContactFirstName, Address, City, State, ZIP, Phone, Fax, Email) ITEM (ItemlD, ItemDescription, PurchaseDate, ItemCost, ItemPrice, VendorID) SALE (SalelD, CustomerlD, EmployeelD, SaleDate, SubTotal, Tax, Total) SALE ITEM The referential integrity constraints are: (SalelR, SaleltemD, I ItemlD, ItemPrice) CustomerlD in SALE must exist in CustomeriD in CUSTOMER VendorlD in ITEM must exist in VendoriD in VENDOR EmployeelD in SALE must exist in EmployeelD in EMPLOYEE SalelD in SALE ITEM must exist in SalelD in SALE ItemID in SALE ITEM must exist in ItemID in ITEM Assume that CustomerlD of CUSTOMER, EmployeelD of EMPLOYEE, VendoriD of VENFOR, ItemID of ITEM, and SalelD of SALE are all surrogate keys with values as follows: CustomerlD Start at1 Increment by 1 EmployeelD Start at Increment by 1 VendorlD Start at 1 Increment by 1 ItemID SalelD Start at 1 Increment by 1 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 Page 7-1 Copyright2016 Pearson Education, Inc.

Explanation / Answer

ANS:-

Given that,

Read

Update

Read

Insert

Update

Delete

Read

Delete

Grant Rights

Revoke Rights

In this the sales people is nothing to do with item insert and delete. It is enough for them just to read the item to sale and the to update the database if the item was sold.

Management can do anything with the sale_item as they are people who decide the items to sell.

Administrative people can read and delete the items as they have nothing to do with update and insert of sale_ item as that will be decided by management

System administrator can be grant and revoke permissions to all the remaining people.

Read

Update

Read

Update

Delete

Insert

Read

Grant Rights

Revoke rights

As the same sales people just sell the item's so it is enough for them to read and update the item

Management can do anything with the items so they are having all the permissions.

Administrative people can have option to just read as they are nothing to do with the items. They are just responsible to provide the service.

System administrator is just responsible for giving permissions and revoke permissions.

PROGRAM:-

CREATE TABLE CUSTOMER
(
CustomerID Int PRIMARY KEY,
LastName Char (35) NOT NULL,
FirstName Char (35) NOT NULL,
Address Varchar2 (200),
City Char (20),
State Char (35) NOT NULL,
ZIP Varchar2 (35) NOT NULL,
Phone Number,
Email Varchar2 (35)
);

CREATE TABLE EMPLOYEE
(
EmployeeID Int PRIMARY KEY,
LastName Char (35) NOT NULL,
FirstName Char (35) NOT NULL,
Phone Number,
Email Varchar2 (35)
);

CREATE TABLE VENDOR
(
VendorID Int PRIMARY KEY,
CompanyName Char (40) NOT NULL,
ContactLastName Char (35) NOT NULL,
ContactFirstName Char (35),
Address Varchar2 (200),
City Char (20),
State Char (35) NOT NULL,
ZIP Varchar2 (35) NOT NULL,
Phone Number,
Fax Number,
Email Varchar2 (35)
);

CREATE TABLE ITEM
(
ItemID Int,
ItemDescription PRIMARY KEY Char (35) NOT NULL,
PurchaseDate Date NOT NULL,
ItemCost Money NOT NULL,
ItemPrice Money NOT NULL,
VendorID Int NOT NULL
);

CREATE TABLE SALE
(
SaleID Int PRIMARY KEY,
CustomerID Int NOT NULL,
EmployeeID Int NOT NULL,
SaleDate Date NOT NULL,
SubTotal Money NOT NULL,
Tax Money NOT NULL,
Total Money NOT NULL
);

CREATE TABLE SALE_ITEM
(
SaleID Int PRIMARY KEY SaleItemID Int NOT NULL,
ItemID Int NOT NULL,
ItemPrice Money NOT NULL
);

SALE_ITEM

Read

Update

Read

Insert

Update

Delete

Read

Delete

Grant Rights

Revoke Rights

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

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