Consider the above ER Diagram – all columns for the table are described below an

Question

Consider the above ER Diagram – all columns for the table are described below and are not shown on the diagram because of space. The Product table consists of the following columns/datatypes:: item_no numeric(4) not null item_name varchar(30) not null price numeric(9,2) not null inventory numeric not null //Number of this item currently in stock (Assume 5 for all) All of the row for the Product table table can be found in the product descriptions provided in Programming Assignment 2. Create the table and select an appropriate primary key constraint for this table. Make sure the item_no, item_name, price, and inventory columns are not null. Create all insert statements to initialize all products for this table. Use the prices and names from Programming Assignment 2. Assume 5 for the inventory. The Customer table consists of the following columns: cc_no numeric(16) exp_mo numeric(2) not null exp_yr numeric(4) not null name_first varchar(20) not null name_last varchar(20) not null email varchar(20) not null address1 varchar(50) not null address2 varchar(50) city varchar(20) not null state varchar(2) not null zip numeric(5) not null country varchar(20) phone varchar(15) not null fax varchar(15) not null mail_list numeric(1) //Contains 1 – On mailing list // 0 - Off mailing List Select an appropriate primary key constraint for the Customer table. The Customer table and Order table have no rows initially and will be populated by the checkout.html form implemented completely in Programming Assignment 7. Orders table is the relationship table between Product and Customer. Quantity is the amount purchased for a given product. Date_Sold is the current date as returned from mysql. Assume a customer can purchase the same items on different dates. The Registration Table is an independent table with no relationships. It’s E-R diagram is shown below: Consider the above ER Diagram – all columns for the table are described below and are not shown on the diagram because of space. The Product table consists of the following columns/datatypes:: item_no numeric(4) not null item_name varchar(30) not null price numeric(9,2) not null inventory numeric not null //Number of this item currently in stock (Assume 5 for all) All of the row for the Product table table can be found in the product descriptions provided in Programming Assignment 2. Create the table and select an appropriate primary key constraint for this table. Make sure the item_no, item_name, price, and inventory columns are not null. Create all insert statements to initialize all products for this table. Use the prices and names from Programming Assignment 2. Assume 5 for the inventory. The Customer table consists of the following columns: cc_no numeric(16) exp_mo numeric(2) not null exp_yr numeric(4) not null name_first varchar(20) not null name_last varchar(20) not null email varchar(20) not null address1 varchar(50) not null address2 varchar(50) city varchar(20) not null state varchar(2) not null zip numeric(5) not null country varchar(20) phone varchar(15) not null fax varchar(15) not null mail_list numeric(1) //Contains 1 – On mailing list // 0 - Off mailing List Select an appropriate primary key constraint for the Customer table. The Customer table and Order table have no rows initially and will be populated by the checkout.html form implemented completely in Programming Assignment 7. Orders table is the relationship table between Product and Customer. Quantity is the amount purchased for a given product. Date_Sold is the current date as returned from mysql. Assume a customer can purchase the same items on different dates. The Registration Table is an independent table with no relationships. It’s E-R diagram is shown below: The Registration table has the following schema: username varchar(16) password varchar(16) not null email varchar(50) not null This table provides information needed when user’s register for online newsletters and email alerts. This table will be populated by a new form that will be created later in Programming Assignment 8. Create a file name create_db.sql. This is a sql script that will perform the following operations: 1. Drop all tables in the correct order. 2. Create all tables with appropriate not null constraints 3. Create all primary key constraints 4. Create all foreign key constraints 5. Insert all initial rows in product table for all products You must use student account and student_space database when implementing this program. The Registration table has the following schema: username varchar(16) password varchar(16) not null email varchar(50) not null This table provides information needed when user’s register for online newsletters and email alerts. This table will be populated by a new form that will be created later in Programming Assignment 8. Create a file name create_db.sql. This is a sql script that will perform the following operations: 1. Drop all tables in the correct order. 2. Create all tables with appropriate not null constraints 3. Create all primary key constraints 4. Create all foreign key constraints 5. Insert all initial rows in product table for all products You must use student account and student_space database when implementing this program.
price out Product Programningassignmente doc (Compatibility Medel Word view view LEa CD View Side by Side a Multiple Pages New Areange u Synchronous scroling o age width window All split BR Reset window position windows. Window item name Orders Customer date sold

Explanation / Answer

NOTE : Since you have not given me the data to be inserted, I didn't create Insert statements

CREATE TABLE `product` (
`product_id` int(4) NOT NULL,
`product_name` varchar(30) NOT NULL,
`price` float(9,2) NOT NULL,
`inventory` int(11) NOT NULL,
PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `student_space`.`customer` (
`customer_id` INT NOT NULL,
`exp_mo` INT(2) NOT NULL,
`exp_year` INT(4) NOT NULL,
`name_first` VARCHAR(20) NOT NULL,
`name_last` VARCHAR(20) NOT NULL,
`address1` VARCHAR(50) NOT NULL,
`address2` VARCHAR(50) NULL,
`city` VARCHAR(20) NOT NULL,
`state` VARCHAR(2) NOT NULL,
`zip` INT(5) NOT NULL,
`country` VARCHAR(20) NULL,
`phone` VARCHAR(15) NOT NULL,
`fax` VARCHAR(15) NOT NULL,
`mail_list` TINYINT(1) NULL,
PRIMARY KEY (`customer_id`));

CREATE TABLE `student_space`.`order` (
`order_id` INT NOT NULL,
`order_date` DATE NOT NULL,
`customer_id` INT NOT NULL,
PRIMARY KEY (`order_id`),
INDEX `customer_id_idx` (`customer_id` ASC),
CONSTRAINT `customer_id`
    FOREIGN KEY (`customer_id`)
    REFERENCES `student_space`.`customer` (`customer_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE `student_space`.`order_product` (
`product_id` INT NOT NULL,
`order_id` INT NOT NULL,
`quantity` DOUBLE NULL DEFAULT 0,
PRIMARY KEY (`product_id`, `order_id`),
INDEX `order_id_FK_idx` (`order_id` ASC),
CONSTRAINT `product_id_FK`
    FOREIGN KEY (`product_id`)
    REFERENCES `student_space`.`product` (`product_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
CONSTRAINT `order_id_FK`
    FOREIGN KEY (`order_id`)
    REFERENCES `student_space`.`order` (`order_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE `student_space`.`registration` (
`registration_id` INT NOT NULL,
`user_name` VARCHAR(16) NOT NULL,
`password` VARCHAR(16) NOT NULL,
`email` VARCHAR(50) NOT NULL,
PRIMARY KEY (`registration_id`),
UNIQUE INDEX `user_name_UNIQUE` (`user_name` ASC));

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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