Question

You have been asked by the owner of a pre-owned car dealership to set up a way to track the cars in his inventory. He often needs to search his records for specific makes and models or cars made in certain years. He also wants to be able to generate reports based on his sales and reports on which sales representatives are doing the most business

1.Create a new a table named SALES_REP. Be sure to create attributes, set a primary key, select data types, and add data. Use the following information:

Rep Number	Last Name	First Name	Address	City	State	ZIP	Phone
1	Perry	Jim	3453 E. State	Jacksonville	FL	32231	904-555-4353
2	Bauer	Evan	5347 W. Grand	Jacksonville	FL	32232	904-555-3423
3	Dickens	Billy	2351 S. Ferguson	Jacksonville	FL	32232	904-555-5632

2. Create a table named INVENTORY and add data. Use the data shown below, with the following guidelines:

1. Add a column inventoryid as surrogate key and create primary key on that column. Set up an auto number to use for a primary key (auto_increment).
2. Create check constraint on carcondition column for list of values = Excellent, Good, Fair, and Poor.

3. Create a one-to-many relationship between SALES_REP and INVENTORY using RepNumber. Create referential integrity on INVENTORY table on RepNumber column to reference SALES_REP on RepNumber.

Answer 1

>First we create SALES_REP TABLE so in that projectmanagment_db is schema name

CREATE TABLE `projectmanagment_db`.`sales_rep` (
`Rep Number` INT NOT NULL AUTO_INCREMENT,
`Last Name` VARCHAR(45) NULL,
`First Name` VARCHAR(45) NULL,
`Address` VARCHAR(45) NULL,
`City` VARCHAR(45) NULL,
`ZIP` INT NULL,
`Phone` BIGINT(20) NULL,
PRIMARY KEY (`Rep Number`));

> I forget State column so in that case i add state column

ALTER TABLE `projectmanagment_db`.`sales_rep`
ADD COLUMN `State` VARCHAR(45) NULL AFTER `Phone`;

> So here i create INVENTORY table with apply check constraint for car condition column and apply the forgien key referential integrity column

CREATE TABLE `projectmanagment_db`.`inventory` (
`inventoryid` INT NOT NULL AUTO_INCREMENT,
`carcondition` VARCHAR(45) NULL,
`referential integrity` INT NULL,CHECK(Car Condition in ('Excellent','Good','Fair','Poor')),
PRIMARY KEY (`inventoryid`),
INDEX `RepNumber_idx` (`referential integrity` ASC) VISIBLE,
CONSTRAINT `RepNumber`
FOREIGN KEY (`referential integrity`)
REFERENCES `projectmanagment_db`.`sales_rep` (`Rep Number`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);

> Adding record into first table SALES_REP

INSERT INTO `projectmanagment_db`.`sales_rep` (`Last Name`, `First Name`, `Address`, `City`, `State`, `ZIP`, `Phone`) VALUES ('Perry', 'Jim', '3453 E. State', 'Jacksonville', 'FL', '32231', '9045554353');

INSERT INTO `projectmanagment_db`.`sales_rep` (`Last Name`, `First Name`, `Address`, `City`, `State`, `ZIP`, `Phone`) VALUES ('Bauer', 'Evan', '5347 W. Grand', 'Jacksonville', 'FL', '32232', '9045555632');

INSERT INTO `projectmanagment_db`.`sales_rep` (`Last Name`, `First Name`, `Address`, `City`, `State`, `ZIP`, `Phone`) VALUES ('Dickens', 'Billy', '2351 S.Ferguson', 'Jacksonville', 'FL', '32232', '9045555632');