Using the finalized ERD from above as your starting point, you must create the S

Question

Using the finalized ERD from above as your starting point, you must create the SQL scripts for creating the database. This requires the development of the DDL scripts for creating the tables, attributes and relationships. Primary keys and foreign keys must be identified.

Your task is to create a stored procedure with scripts for creating all the tables for the database. Due to referential integrity, the order in which you create the tables is important. Your script must create the tables, assign the primary and foreign keys, and declare the data types. Use the final ERD in the Bonus Step to identify the tables and attributes that need to be created. Use the exact names as they appear in the diagram. Future steps will use this database design so it’s important to declare your tables, attributes and relationships correctly.

The stored procedure has been started below. You are to finish the stored procedure and write an SQL CREATE statement for each table displayed in the ERD. The data types for each attribute are below the stored procedure. You must select the appropriate data type when writing the query. You may use the CREATE_ALL_TABLES stored procedure from your student DB as a guide.

Note: The data types next to the attributes on the next page are generic data types used to describe the data. You must select the correct syntax for data types when writing your scripts.

After creating writing the stored procedure, you must copy it into Visual Studio and create it on the database. Next, you should execute the CREATE_cCommerce_Tables that you created. Refresh your tables folder to make sure that all the tables appear and are correct.

Upload a Microsoft Word document that says “Task Complete” to UNM Learn to show you have completed the assignment. This will trigger the grading process. Your assignment will graded based on the results displayed in vLab.

***Copy Below***

/****** Procedure: Creates New StoredProcedure [dbo].[Create_eCommerce_Tables] ******/ 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, CONSTRAINT PK_customer PRIMARY KEY (custID)

);

orderitems OID prodID qty price upSells custID prodID percentage PK custiD FK shiplD shipCost FK shippingAddress shippingMethods product crossSells prodID1 prodID2 percentage FK custID address1 address2 company PK description FK primaryYN baseWeight keywords paymentlnfo department category catID deptID custID deptiD PK description ccExpire billAddress description

Explanation / Answer

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)
);

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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