reate a database containing the following two tables: Customer(CustomerNumber, C

Question

reate a database containing the following two tables:

Customer(CustomerNumber, CustomerLastName, CustomerFirstName, CustomerBalance,EmailAddress)

Invoice(InvoiceNumber,CustomerNumber, InvoiceDate,InvoiceAmount)

CustomerNumber is a surrogate key that starts at 2000 and increments by 1

InvoiceNumber is a surrogate key that starts at 7000 and increments by 1

The database has the following referential integrity constraints:

CustomerNumber in Invoice must exist in CustomerNumber in Customer table

The relationship between Customer and Invoice table is 1: N

The column characteristics for the wto tables are shown below

Surrogate Key:

Initia Value = 2000

Increment = 1

INVOICE

Surrogate Key:

Initial Value= 7000

Increment = 1

the data are shown as below

CustomerNumber         CustomerFirstName        CustomerBalance

-----------------------------------------------------------------------------------------------

2000                        Frank                          204

2001                        Holly                         500

2002                       Joseph                      700

2003                      Ralph                       900

Modify the table while including two new attributes: USERNAME and CustomerDateOfBirth

Question. write an SQL to update the date of birth in the Customer table for the following two customers.

a. Customer 2000 was born on February 27, 2005

b. Customer 2001 was born on June 7, 2006

Use a SQL statement to confirm that table is created.

Write a procedure to add a new customer to the CUstomer Table. Use the following value in the new record:

      'Laurel' , 'James, 2.00

Use a SQL statement to confirm that the record has been added.

3. Write an SQL statement to create a view(view_customer_sales) based on the Customer and Invoice table. In this view, include the values of CustomerNumber.(LastName,FirstName) AS CustomerName, userName, invoiceNumber, and invoiceDate in order. Run the statement and write and run an SQL SELECT statement for this question.

That is Database, the answers are needed to be accurate. Please be accurate and I need them immediately in an hour. Thank You

Column Name Type Key Required Remarks CustomerNumber INT Primary Key Yes

Surrogate Key:

Initia Value = 2000

Increment = 1

CustomerLName Varchar(20) NO Yes CustomerFName Varchar(20) No Yes CustomerBalance Number No Yes EmailAddress Varchar(50) No No

Explanation / Answer

Note:- Use Mysql DB

1) Create Costumer table:-

CREATE TABLE `Customer` (
`CustomerNumber` INT NOT NULL AUTO_INCREMENT,
`CustomerLName` VARCHAR(20) NOT NULL,
`CustomerFName` VARCHAR(20) NOT NULL,
`CustomerBalance` DECIMAL(2) NOT NULL,
`EmailAddress` VARCHAR(50) ,
PRIMARY KEY (`CustomerNumber`)
)AUTO_INCREMENT=2000;
  

--------------------------------------------------------

2) Create Invoice table:

CREATE TABLE `Invoice` (
`InvoiceNumber` int(11) NOT NULL AUTO_INCREMENT,
`CustomerNumber` int(11) NOT NULL,
`InvoiceDate` date NOT NULL,
`InvoiceAmount` DECIMAL(2) NOT NULL,
PRIMARY KEY (`InvoiceNumber`),
KEY `CustomerNumberFK_idx` (`CustomerNumber`),
CONSTRAINT `CustomerNumberFK` FOREIGN KEY (`CustomerNumber`) REFERENCES `Customer` (`CustomerNumber`)
) AUTO_INCREMENT=7000;

--------------------------------------------------------------------------

3) Modifiy Costumer table toade user name and date of birth

ALTER TABLE `Customer`
ADD COLUMN `USERNAME` VARCHAR(25) NULL AFTER `EmailAddress`,
ADD COLUMN `CustomerDateOfBirth` DATE NULL AFTER `USERNAME`;

------------------------------------------------------------------------------------------------------------------------

4) Updating costumer date of birth

update Customer set CustomerDateOfBirth = '2005-02-27' WHERE CustomerNumber = 2000;

update Customer set CustomerDateOfBirth = '2006-05-07' WHERE CustomerNumber = 2001;

------------------------------------------------------------------------------------------------------------------------------------

5) Procedure tostore customer detail

DELIMITER $$

CREATE PROCEDURE `ADD_NEW_CUSTOMER` (

IN CLastName VARCHAR(512),

IN CFirstName VARCHAR(512),

IN CBalance DECIMAL(2)

)

BEGIN

INSERT INTO Customer(CustomerLName, CustomerFName, CustomerBalance)

VALUES (CLastName, CFirstName, CBalance);

END

$$

;

example to call this procedure:-

call devticks.ADD_NEW_CUSTOMER('Laurel', 'James', 2.00);

-------------------------------------------------------------------------------------------------------------

6) Create View view_customer_sales :-

CREATE VIEW `view_customer_sales` AS
SELECT c.CustomerNumber,
concat(c.CustomerLName, ",", c.CustomerFName) AS CustomerName,
c.USERNAME,
i.InvoiceNumber,
i.InvoiceDate
FROM Customer c INNER JOIN Invoice i ON c.CustomerNumber = i.CustomerNumber;

Example to select From This view:-

SELECT * FROM view_customer_sales;

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

---

---

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