Question

Someone Please Help Me modify this in PHP I'm in desperate need I cant figure this out ...

Make the following modifications:

1. For the vendors table:
   1. Comment out the table-level primary key
   2. Change the VendorIDcolumn to be a column-level primary key
   3. Add a VendorEmail column to the bottom of the table definition (define the data type for the column as variable character and set it to not exceed 45 characters)
2. After the lineItems table, add code to create a table named audit:
   1. Create the following columns in the audit table
   2. auditID (integer, nulls not accepted, and make it an auto-incrementing primary key)
   3. auditorFirstName (nulls not accepted, variable character data type with a max of 45 characters)
   4. auditorLastName (nulls not accepted, variable character data type with a max of 45 characters)
   5. auditDate (date data type)
   6. auditTime (time data type)
   7. auditExtra (integer, max display size is 8, nulls not accepted)
3. At the bottom of the file, create an index for the vendorName column in the vendors file
4. Use MySQL comments to add your name, date, and WEB-182 in a comment block at the top of the file
5. Use MySQL comments to _clearly_ mark each of the modifications to the ap-mod.sql file (such as /* --- Mod #1 --- */)
6. Save your new ap-mod.sql file.

BELOW IS THE FILE THAT WE ARE MODIFYING .....

ap-mod.sql-

DROP DATABASE IF EXISTS ap;
CREATE DATABASE ap;
USE ap;

CREATE TABLE IF NOT EXISTS vendors (
vendorID INT NOT NULL AUTO_INCREMENT,
vendorName VARCHAR(45) NOT NULL UNIQUE,
vendorAddress VARCHAR(45) NOT NULL,
vendorCity VARCHAR(45) NOT NULL,
vendorState VARCHAR(45) NOT NULL,
vendorZipCode VARCHAR(10) NOT NULL,
vendorPhone VARCHAR(20) NOT NULL,
PRIMARY KEY (vendorID)
);

CREATE TABLE IF NOT EXISTS invoices (
invoiceID INT NOT NULL AUTO_INCREMENT,
vendorID INT NOT NULL,
invoiceNumber VARCHAR(45) NOT NULL,
invoiceDate DATETIME NOT NULL,
invoiceTotal DECIMAL NOT NULL,
paymentTotal DECIMAL,
PRIMARY KEY (invoiceID),
CONSTRAINT invoicesFkVendors
FOREIGN KEY (vendorID) REFERENCES vendors (vendorID)
);

CREATE TABLE IF NOT EXISTS lineItems (
lineItemID INT NOT NULL AUTO_INCREMENT,
invoiceID INT NOT NULL,
description VARCHAR(45) NOT NULL,
quantity INT NOT NULL,
price INT NOT NULL,
lineItemTotal DECIMAL NOT NULL,
PRIMARY KEY (lineItemID),
CONSTRAINT lineItemsFkInvoices
FOREIGN KEY (invoiceID) REFERENCES invoices (invoiceID)
);

CREATE INDEX vendorID
ON invoices (vendorID);

CREATE INDEX invoiceNumber
ON invoices (invoiceNumber);

CREATE INDEX invoiceID
ON lineItems (invoiceID);

GRANT SELECT, INSERT, UPDATE, DELETE
ON *
TO mgs_user
IDENTIFIED BY 'pa55word';

Please Help I'm running out of time.

Answer 1

Hi,

Please find the code below.

ap-mod.sql-
DROP DATABASE IF EXISTS ap;
CREATE DATABASE ap;
USE ap;

CREATE TABLE IF NOT EXISTS vendors (
/* --- Mod #1 --- */
/*adding column level primary key*/
vendorID INT NOT NULL PRIMARY_KEY AUTO_INCREMENT ,
vendorName VARCHAR(45) NOT NULL UNIQUE,
vendorAddress VARCHAR(45) NOT NULL,
vendorCity VARCHAR(45) NOT NULL,
vendorState VARCHAR(45) NOT NULL,
vendorZipCode VARCHAR(10) NOT NULL,
vendorPhone VARCHAR(20) NOT NULL,
vendorEmail VARCHAR(40)
/* --- Mod #2 --- */
/*PRIMARY KEY (vendorID)*//* Tablelevel primary key is commented*/
);

CREATE TABLE IF NOT EXISTS invoices (
invoiceID INT NOT NULL AUTO_INCREMENT,
vendorID INT NOT NULL,
invoiceNumber VARCHAR(45) NOT NULL,
invoiceDate DATETIME NOT NULL,
invoiceTotal DECIMAL NOT NULL,
paymentTotal DECIMAL,
PRIMARY KEY (invoiceID),
CONSTRAINT invoicesFkVendors
FOREIGN KEY (vendorID) REFERENCES vendors (vendorID)
);

CREATE TABLE IF NOT EXISTS lineItems (
lineItemID INT NOT NULL AUTO_INCREMENT,
invoiceID INT NOT NULL,
description VARCHAR(45) NOT NULL,
quantity INT NOT NULL,
price INT NOT NULL,
lineItemTotal DECIMAL NOT NULL,
PRIMARY KEY (lineItemID),
CONSTRAINT lineItemsFkInvoices
FOREIGN KEY (invoiceID) REFERENCES invoices (invoiceID)
);

/* --- Mod #3 --- */
CREATE TABLE IF NOT EXISTS audit (
auditID INT NOT NULL AUTO_INCREMENT PRIMARY_KEY,
auditorFirstName VARCHAR(45) NOT NULL,)
auditorLastName VARCHAR(45) NOT NULL,
auditDate DATE,
auditTime TIME,
auditExtra INT(8) NOT NULL);

CREATE INDEX vendorID
ON invoices (vendorID);
CREATE INDEX invoiceNumber
ON invoices (invoiceNumber);
CREATE INDEX invoiceID
ON lineItems (invoiceID);
/* --- Mod #4 --- */
CREATE INDEX vendorName
ON vendors (vendorName);

GRANT SELECT, INSERT, UPDATE, DELETE
ON *
TO mgs_user
IDENTIFIED BY 'pa55word';ap-mod.sql-
DROP DATABASE IF EXISTS ap;
CREATE DATABASE ap;
USE ap;

CREATE TABLE IF NOT EXISTS vendors (
/* --- Mod #1 --- */
/*adding column level primary key*/
vendorID INT NOT NULL PRIMARY_KEY AUTO_INCREMENT ,
vendorName VARCHAR(45) NOT NULL UNIQUE,
vendorAddress VARCHAR(45) NOT NULL,
vendorCity VARCHAR(45) NOT NULL,
vendorState VARCHAR(45) NOT NULL,
vendorZipCode VARCHAR(10) NOT NULL,
vendorPhone VARCHAR(20) NOT NULL,
vendorEmail VARCHAR(40)
/* --- Mod #2 --- */
/*PRIMARY KEY (vendorID)*//* Tablelevel primary key is commented*/
);

CREATE TABLE IF NOT EXISTS invoices (
invoiceID INT NOT NULL AUTO_INCREMENT,
vendorID INT NOT NULL,
invoiceNumber VARCHAR(45) NOT NULL,
invoiceDate DATETIME NOT NULL,
invoiceTotal DECIMAL NOT NULL,
paymentTotal DECIMAL,
PRIMARY KEY (invoiceID),
CONSTRAINT invoicesFkVendors
FOREIGN KEY (vendorID) REFERENCES vendors (vendorID)
);

CREATE TABLE IF NOT EXISTS lineItems (
lineItemID INT NOT NULL AUTO_INCREMENT,
invoiceID INT NOT NULL,
description VARCHAR(45) NOT NULL,
quantity INT NOT NULL,
price INT NOT NULL,
lineItemTotal DECIMAL NOT NULL,
PRIMARY KEY (lineItemID),
CONSTRAINT lineItemsFkInvoices
FOREIGN KEY (invoiceID) REFERENCES invoices (invoiceID)
);

/* --- Mod #3 --- */
CREATE TABLE IF NOT EXISTS audit (
auditID INT NOT NULL AUTO_INCREMENT PRIMARY_KEY,
auditorFirstName VARCHAR(45) NOT NULL,)
auditorLastName VARCHAR(45) NOT NULL,
auditDate DATE,
auditTime TIME,
auditExtra INT(8) NOT NULL);

CREATE INDEX vendorID
ON invoices (vendorID);
CREATE INDEX invoiceNumber
ON invoices (invoiceNumber);
CREATE INDEX invoiceID
ON lineItems (invoiceID);
/* --- Mod #4 --- */
CREATE INDEX vendorName
ON vendors (vendorName);

GRANT SELECT, INSERT, UPDATE, DELETE
ON *
TO mgs_user
IDENTIFIED BY 'pa55word';