Here\'s the preceeding data information: CREATE PROCEDURE [dbo].[Create_eCommerc

Question

Here's the preceeding data information:

CREATE PROCEDURE [dbo].[Create_eCommerce_Tables]
AS

IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='customer')
CREATE TABLE Customer
(
CustID INT NOT NULL,
fname VARCHAR(20) NOT NULL,
lname VARCHAR(20) NOT NULL,
phone NUMERIC(15) NOT NULL,
email VARCHAR(20) NOT NULL,
DOB DATE NOT NULL,
Gender CHAR(10) NOT NULL,
PRIMARY KEY (CustID)
);
IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='orderitems')
CREATE TABLE orderitems
(
Oid INT NOT NULL,
prodid INT NOT NULL,
qty INT NOT NULL,
price NUMERIC(15) NOT NULL,
PRIMARY KEY (Oid, prodid)
);
IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='upsale')
CREATE TABLE upsale
(
prodid INT NOT NULL,
percentage NUMERIC(5) NOT NULL,
custid INT NOT NULL,
PRIMARY KEY (prodid, custid)
);
IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='shiippingAddress')
CREATE TABLE shiippingAddress
(
sid INT NOT NULL,
address1 VARCHAR(30) NOT NULL,
address2 VARCHAR(30) NOT NULL,
city VARCHAR(20) NOT NULL,
state VARCHAR(20) NOT NULL,
primary_YN CHAR(2) NOT NULL,
CustID INT NOT NULL,
PRIMARY KEY (sid),
FOREIGN KEY (CustID) REFERENCES Customer(CustID)
);
IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='shippmentMethos')
CREATE TABLE shippmentMethos
(
shipid INT NOT NULL,
company VARCHAR(20) NOT NULL,
method VARCHAR(20) NOT NULL,
frate NUMERIC(10) NOT NULL,
vrate NUMERIC(10) NOT NULL,
baseWeight NUMERIC(10) NOT NULL,
PRIMARY KEY (shipid)
);
IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='crossSell')
CREATE TABLE crossSell
(
prodid1 INT NOT NULL,
prodid2 INT NOT NULL,
percentage NUMERIC(10) NOT NULL,
PRIMARY KEY (prodid1, prodid2)
);
IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='department')
CREATE TABLE department
(
deptid INT NOT NULL,
name VARCHAR(20) NOT NULL,
description VARCHAR(35) NOT NULL,
PRIMARY KEY (deptid)
);
IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='paymentinfo')
CREATE TABLE paymentinfo
(
custid INT NOT NULL,
pname VARCHAR(20) NOT NULL,
cctype VARCHAR(20) NOT NULL,
ccnumber INT NOT NULL,
ccexpire DATE NOT NULL,
billAddress VARCHAR(30) NOT NULL,
city VARCHAR(20) NOT NULL,
state CHAR(20) NOT NULL,
zip VARCHAR(20) NOT NULL,
PRIMARY KEY (custid, pname)
);
IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='Orders')
CREATE TABLE Orders
(
Oid INT NOT NULL,
Orderdt DATE NOT NULL,
ShipId INT NOT NULL,
ShipCost NUMERIC(15) NOT NULL,
CustID INT NOT NULL,
shipid INT NOT NULL,
FOREIGN KEY (CustID) REFERENCES Customer(CustID),
FOREIGN KEY (shipid) REFERENCES shippmentMethos(shipid)
);
IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='category')
CREATE TABLE category
(
cid INT NOT NULL,
name VARCHAR(20) NOT NULL,
description VARCHAR(35) NOT NULL,
deptid INT NOT NULL,
PRIMARY KEY (cid),
FOREIGN KEY (deptid) REFERENCES department(deptid)
);
IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='product')
CREATE TABLE product
(
prodid INT NOT NULL,
productname VARCHAR(20) NOT NULL,
description VARCHAR(35) NOT NULL,
rPrice NUMERIC(10) NOT NULL,
sPrice NUMERIC(10) NOT NULL,
keywords VARCHAR(30) NOT NULL,
cid INT NOT NULL,
PRIMARY KEY (prodid),
FOREIGN KEY (cid) REFERENCES category(cid)
);

?VRIO Analysis. Ni DP gectTask 6(1).p Week 6Quiz-MG ? Begin Week 6 Qu project Task 6(1).p e chegg Study Gu xy D MGMT 329 PROEX X × X X x C | file:///C/Users/david/Desktop/MGMT%20329%20PROJECT%20TASK%2069620DATA%20LOADIN.pdf MGMT 329 PROJECT TASK 6 DATA LOADIN.pdf 1 /2 In project task 5, you were asked to write the stored procedures to create the entities, attributes and relationships in your databasc. The stored procedure should have created each table in in an order that took referential integrity constraints into consideration One possible order to creating these tables is as follows: 2. shippingAddress 3. paymentinfo 4. department 5. category 6. product 7. orders 8. orderltems 9. shippingMethods 10. upsells 11. crossSells paymentinfo and orders tables depend on the customer table, you Since the should create the customer table before creating these three. Same is true for the hierarchy of department to category and category to product. Once the product table has been created, you can then add the onders table followed by the orderltems and shipping Methods table. Last would be to create the upSells and cross Sells tables. When creating the upSells and crossSells tables, the order of these two doesn't matter as long as the custer, orderltems and product tables are created first. To ensure that everybody has the same database, your tables were dropped and re-created with the correct solution. If you were successful with Task 5 then your created. If you had some issues with your tables in task 5 or could not complete the task, then your database will be populated with the correct solution so that you can move forward and work on task 6, tables will be the same as you Show allX MGMT 329 PROJE..pdf Project Task 6(1),pdf A 6:31 AM O Type here to search 7/11/2018

Explanation / Answer

Please find the procedure below:

============================================================

CREATE PROCEDURE Insert_ShippingMethod_Data

@shipId INT,

@company VARCHAR(20),

@method VARCHAR(20),

@fRate NUMERUC(10),

@vRate NUMERUC(10),

@baseWeight NUMERUC(10)

AS

BEGIN

INSERT INTO shippmentMethods (shipid, company, method, frate, vrate, baseWeight)

VALUES

(

@shipId,

@company,

@method,

@fRate,

@vRate,

@baseWeight

)

END

To insert data, call the procedure as shown below.

exec Insert_ShippingMethod_Data

@shipId = 101,

@company = 'Fedex',

@method = 'Standard Ground',

@fRate = 10,

@vRate = 0.5,

@baseWeight = 20;

exec Insert_ShippingMethod_Data

@shipId = 102,

@company = 'Fedex',

@method = 'Standard Air',

@fRate = 20,

@vRate = 0.5,

@baseWeight = 10;

exec Insert_ShippingMethod_Data

@shipId = 103,

@company = 'Fedex',

@method = 'Express Air',

@fRate = 30,

@vRate = 1.0,

@baseWeight = 10;

NOTE: I have written code to execute the procedure to insert the first three rows. Similarly, you can call the procedure to insert the remaining rows.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---