Question: DROP VIEW bb _ basketitem _ vu;DROP VIEW bb _ ship _ vu;DROP TABLE bb _ department CASCADE CONSTRAINTS;DROP TABLE bb _ product CASCADE CONSTRAINTS;DROP

DROP VIEW bb

_

basketitem

_

vu;DROP VIEW bb

_

ship

_

vu;DROP TABLE bb

_

department CASCADE CONSTRAINTS;DROP TABLE bb

_

product CASCADE CONSTRAINTS;DROP TABLE bb

_

productoption CASCADE CONSTRAINTS;DROP TABLE bb

_

productoptiondetail CASCADE CONSTRAINTS;DROP TABLE bb

_

productoptioncategory CASCADE CONSTRAINTS;DROP TABLE bb

_

basketitem CASCADE CONSTRAINTS;DROP TABLE bb

_

basket CASCADE CONSTRAINTS;DROP TABLE bb

_

shopper CASCADE CONSTRAINTS;DROP TABLE bb

_

basketstatus CASCADE CONSTRAINTS;DROP TABLE bb

_

tax CASCADE CONSTRAINTS;DROP TABLE bb

_

shipping CASCADE CONSTRAINTS;DROP TABLE bb

_

promolist CASCADE CONSTRAINTS;DROP TABLE bb

_

promo CASCADE CONSTRAINTS;DROP TABLE bb

_

test

1

CASCADE CONSTRAINTS;DROP TABLE bb

_

test

2

CASCADE CONSTRAINTS;DROP TABLE bb

_

prod

_

sales CASCADE CONSTRAINTS;DROP TABLE bb

_

shop

_

sales CASCADE CONSTRAINTS;DROP TABLE bb

_

audit

_

logon CASCADE CONSTRAINTS;DROP TABLE bb

_

product

_

request CASCADE CONSTRAINTS;DROP TABLE bb

_

blocktest CASCADE CONSTRAINTS;DROP TABLE bb

_

trans

_

log CASCADE CONSTRAINTS;DROP SEQUENCE bb

_

prodid

_

seq;DROP SEQUENCE bb

_

shopper

_

seq;DROP SEQUENCE bb

_

poption

_

seq;DROP SEQUENCE bb

_

idbasket

_

seq;DROP SEQUENCE bb

_

idbasketitem

_

seq;DROP SEQUENCE bb

_

status

_

seq;DROP SEQUENCE bb

_

prodreq

_

seq;CREATE TABLE BB

_

Department

(

idDepartment number

(2),

DeptName varchar

2 (25),

DeptDesc varchar

2 (100),

DeptImage varchar

2 (25),

CONSTRAINT dept

_

_

pk PRIMARY KEY

(

idDepartment

))

;insert into bb

_

department values

(1,

'Coffee','Many types of coffee beans','coffee.gif'

)

;insert into bb

_

department values

(2,

'Equipment and Supplies','Coffee makers to coffee filters available','machines.gif'

)

;insert into bb

_

department values

(3,

'Coffee Club','Benefits of our club membership?

',

'club.gif'

)

;CREATE TABLE BB

_

Product

(

idProduct number

(2),

ProductName varchar

2 (25),

Description varchar

2 (100),

ProductImage varchar

2 (25),

Price number

(6, 2),

SaleStart date, SaleEnd date, SalePrice number

(6, 2),

Active number

(1),

Featured number

(1),

FeatureStart date, FeatureEnd date, Type char

(1),

idDepartment number

(2),

CONSTRAINT prod

_

_

pk PRIMARY KEY

(

idProduct

),

CONSTRAINT prod

_

idDept

_

fk FOREIGN KEY

(

idDepartment

)

REFERENCES BB

_

Department

(

idDepartment

))

;insert into bb

_

product

(

idProduct

,

type, ProductName, Description, ProductImage, Price, Active, idDepartment

)

values

(1,'

',

'CapressoBar Model #

351',

'A fully programmable pump espresso machine and

10 -

cup coffee maker complete with GoldTone filter', 'capresso.gif',

99.99, 1, 2)

; insert into bb

_

product

(

idProduct

,

type, ProductName, Description, ProductImage, Price, Active, idDepartment

)

values

(2,'

',

'Capresso Ultima', 'Coffee and Espresso and Cappuccino Machine. Brews from one espresso to two six ounce cups of coffee', 'capresso

2 .

gif',

129.99, 1, 2)

; insert into bb

_

product

(

idProduct

,

Type, ProductName, Description, ProductImage, Price, Active, idDepartment

)

values

(3,'

',

'Eileen

4 -

cup French Press', 'A unique coffeemaker from those proud craftsmen in windy Normandy.

',

'frepress.gif',

32.50, 1, 2)

; insert into bb

_

product

(

idProduct

,

Type, ProductName, Description, ProductImage, Price, Active, idDepartment

)

values

(4,'

',

'Coffee Grinder', 'Avoid blade grinders! This mill grinder allows you to choose a fine grind to a coarse grind.

',

'grind.gif',

28.50, 1, 2)

; insert into bb

_

product

(

idProduct

,

Type, ProductName, Description, ProductImage, Price, Active, idDepartment

)

values

(5,'

',

'Sumatra', 'Spicy and intense with herbal aroma.

',

'sumatra.jpg

', 10.50, 1, 1)

; insert into bb

_

product

(

idProduct

,

Type, ProductName, Description, ProductImage, Price, Active, idDepartment

)

values

(6,'

',

'Guatamala', 'heavy body, spicy twist, aromatic and smokey flavor.

',

'Guatamala.jpg

', 10.00, 1, 1)

; insert into bb

_

product

(

idProduct

,

Type, ProductName, Description, ProductImage, Price, Active, idDepartment

)

values

(7,'

',

'Columbia', 'dry, nutty flavor and smoothness', 'columbia.jpg

', 10.80, 1, 1)

; insert into bb

_

product

(

idProduct

,

Type, ProductName, Description, ProductImage, Price, Active, idDepartment

)

values

(8,'

',

'Brazil', 'well

-

balanced mellow flavor, a medium body with hints of cocoa and a mild, nut

-

like aftertaste', 'brazil.jpg

', 10.80, 1, 1)

; insert into bb

_

product

(

idProduct

,

Type, ProductName, Description, ProductImage, Price, Active, idDepartment

)

values

(9,'

',

'Ethiopia', 'distinctive berry

-

like flavor and aroma, reminds many of a fruity, memorable wine.

',

'ethiopia.jpg

', 10.00, 1, 1)

; insert into bb

_

product

(

idProduct

,

Type, ProductName, Description, ProductImage, Price, Active, idDepartment

)

values

(10,'

',

'Espresso', 'dense, caramel

-

like sweetness with a soft acidity. Roasted somewhat darker than traditional Italian.

',

'espresso.jpg

', 10.00, 1, 1)

;CREATE SEQUENCE bb

_

prodid

_

seq start with

15

;Alter table bb

_

product ADD

(

stock number

(5, 1),

ordered number

(3),

reorder number

(3))

;UPDATE bb

_

product SET stock

= 23,

ordered

= 0,

reorder

= 12

WHERE idproduct

= 1

;UPDATE bb

_

product SET stock

= 15,

ordered

= 0,

reorder

= 9

WHERE idproduct

= 2

;UPDATE bb

_

product SET stock

= 30,

ordered

= 0,

reorder

= 15

WHERE idproduct

= 3

;UPDATE bb

_

product SET stock

= 26,

ordered

= 0,

reorder

= 25

WHERE idproduct

= 4

;UPDATE bb

_

product SET stock

= 41,

ordered

= 0,

reorder

= 45

WHERE idproduct

= 5

;UPDATE bb

_

product SET stock

= 42,

ordered

= 0,

reorder

= 35

WHERE idproduct

= 6

;UPDATE bb

_

product SET stock

= 61,

ordered

= 0,

reorder

= 35

WHERE idproduct

= 7

;UPDATE bb

_

product SET stock

= 53,

ordered

= 0,

reorder

= 35

WHERE idproduct

= 8

;UPDATE bb

_

product SET stock

= 54,

ordered

= 0,

reorder

= 35

WHERE idproduct

= 9

;UPDATE bb

_

product SET stock

= 50,

ordered

= 50,

reorder

= 50

WHERE idproduct

= 10

;UPDATE bb

_

product set salestart

=' 01 -

JUN

- 2012',

saleend

=' 15 -

JUN

- 2012',

SalePrice

= 8.00

WHERE idProduct

= 6

;CREATE TABLE bb

_

Product

_

request

(

idRequest NUMBER

(4),

idproduct number

(2),

dtrequest DATE DEFAULT SYSDATE, dtrecd DATE, cost number

(6, 2),

qty number

(3),

idvender number

(3),

CONSTRAINT prodreq

_

_

pk PRIMARY KEY

(

idrequest

),

CONSTRAINT prodreq

_

idprod

_

fk FOREIGN KEY

(

idProduct

)

REFERENCES bb

_

product

(

idproduct

))

;CREATE TABLE BB

_

Shopper

(

idShopper number

(4),

FirstName varchar

2 (15),

LastName varchar

2 (20),

Address varchar

2 (40),

City varchar

2 (20),

State char

(2),

ZipCode varchar

2 (15),

Phone varchar

2 (10),

Fax varchar

2 (10),

Email varchar

2 (25),

UserName varchar

2 (8),

Password varchar

2 (8),

Cookie number

(4)

DEFAULT

0,

dtEntered date DEFAULT SYSDATE, Province varchar

2 (15),

Country varchar

2 (15),

CONSTRAINT shopper

_

_

pk PRIMARY KEY

(

idShopper

))

;create sequence bb

_

shopper

_

seq start with

30

;insert into bb

_

shopper values

(21,

'John', 'Carter',

' 21

Front St

.',

'Raleigh',

'

',' 54822',' 9014317701',

NULL, 'Crackjack@aol.com', 'Crackj',

'

flyby

', 1,' 13 -

JAN

- 2012',

NULL, 'USA'

)

;insert into bb

_

shopper values

(22,

'Margaret', 'Somner',

' 287

Walnut Drive', 'Cheasapeake',

'

',' 23321',' 7574216559',

NULL, 'MargS@infi.net', 'MaryS', 'pupper',

1,' 03 -

FEB

- 2012',

NULL, 'USA'

)

;insert into bb

_

shopper values

(23,

'Kenny', 'Ratman',

' 1

Fun Lane', 'South Park',

'

',' 54674',' 9015680902',

NULL, 'ratboy@msn

.

net', 'rat

55',

'kile',

0,' 26 -

JAN

- 2012',

NULL, 'USA'

)

;insert into bb

_

shopper values

(24,

'Camryn', 'Sonnie',

' 40162

Talamore', 'South Riding',

'

',' 20152',' 7035556868',

NULL, 'kids

2

@xis.net', 'kids

2',

'steel',

1,' 19 -

MAR

- 2012',

NULL, 'USA'

)

;insert into bb

_

shopper values

(25,

'Scott', 'Savid',

' 11

Pine Grove', 'Hickory',

'

',' 22954',' 7578221010',

NULL, 'scott

1

@odu.edu', 'fdwell', 'tweak',

1,' 19 -

FEB

- 2012',

NULL, 'USA'

)

;insert into bb

_

shopper values

(26,

'Monica', 'Cast',

' 112

. 4

',

'Greensburg',

'

',' 27754',' 7573217384',

NULL, 'gma@earth.net', 'gma

1',

'goofy',

1,' 09 -

FEB

- 2012',

NULL, 'USA'

)

;insert into bb

_

shopper values

(27,

'Pete', 'Parker',

' 1

Queens', 'New York',

'

',' 67233',' 1013217384',

NULL, 'spider@web.net',

'','', 0,' 14 -

FEB

- 2012',

NULL, 'USA'

)

;ALter table bb

_

shopper ADD

(

promo CHAR

(1))

;CREATE TABLE bb

_

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Programming Questions!

Answer ALL questions as they go together using oracle syntax database! Construct the database schema, insert the rows and write PL/SQL statements for the problems given. The results that you should...

Assignment 2 (Due is April 20 th ) Question 1 Identifying Customers Assignment 2 (Due is April 20th) Question 1 Identifying Customers Brewbeans Coffee Company wants to offer an incentive of free...

SQL - Oracle Version 4.1.4.21: Need someone to fix my answers so they run without any errors. Thnx. -------------------------------- DECLARE lv_tax_num NUMBER(2,2); BEGIN CASE 'NJ' WHEN 'VA' THEN...

B.B. Lean, a catalog retailer, created a wildly popular, especially among strategy professors, Penguin Fleece Jacket over 10 years ago. Over that time period, B.B. Lean has built strong relationships...

Hands-On Assignments: Lesson 12 Create a view called EMP_VU based on the employee number, employee name, and department number from the EMP table. Change the heading for the employee name to...

Oracle 11g PL/SQL question. The tables needed can be created with the c10_BBcreate.sql listed below. Using Dynamic SQL to create a procedure named add_row_sp for inserting a row into a table with...

Oracle 11g PL/SQL question. The tables needed can be created with the c10_BBcreate.sql listed below. Question: Create a stored function called ORDER_COUNT_SF to retrieve the total number of orders in...

Create a block that retrieves the movie title and rental count based on a movie ID provided via an initialized variable. The block should display the movie title, rental count, and rental rating...

PL/SQL Assignment 3-11: Retrieving and Displaying Pledge Data Create a PL/SQL block to retrieve and display data for all pledges made in a specified month. One row of output should be displayed for...

Assignment 4-11: Adding Cursor Flexibility An administration page in the DoGood Donor application allows employees to enter multiple combinations of donor type and pledge amount to determine data to...

Beb's Trinity Store's annual income statement for 2019 is shown in the table below. Beb worked full-time in the business and invested Php300,000 to acquire it and stock it with goods. turned down a...

An automobile manufacturer is interested in the fuel efficiency of a proposed new car design. Six nonprofessional drivers were selected, and each one drove a prototype of the new car from Phoenix to...

Use this info to fill out the table! thanks! Jan. 2 Purchased 1 2 gallons @ $ 2 . 2 5 each Jan. 6 Purchased 6 gallons @ $ 2 . 4 0 each Jan. 8 Sold 7 gallons of milk to a customer Jan. 1 3 Purchased 3...

You return from a break and notice that a table of customers to which you slowed service, has been given a fresh round of drinks. What should you have done before going on break? Let your manager and