Need to create a table im MySQL towards database CPS3740_2017S. Pulling tables f

Question

Need to create a table im MySQL towards database CPS3740_2017S.

Pulling tables from Database CPS3740

Create one new table Products_XXXX under the database CPS3740_2017S where XXXX is

your Kean Email ID. Your table names must follow the above format (case sensitive).

Table Products_XXXX should have the following fields in correct data types.

id: auto_increment integer, primary key

name: varchar(255), NOT NULL

description: varchar(255), NOT NULL

sell_price: float, NOT NULL

cost: float, NOT NULL

quantity: int, NOT NULL

user_id: int, NOT NULL, a foreign key to the id field in CPS3740.Users table.

vendor_id: int, NOT NULL, a foreign key to the V_Id field in CPS3740.Vendors table.

CPS 3740/Spring 2017 Database Management Systems Project Phase a2 Dr. Huang Name: Please submit this paperwhen you present the project 2. Project due date n class presentation on This is an individual project with 2 phases, and this is the 2nd phase. Phase 2 wi require you to develop the following functions (total 100 points). 5 pts) All phase #1 features and functions should be included in phase a2. 5 pts) Create one new table Pl roducts XXXX under the database CPS3740 2017s where 000 is your Kean Email ID. Your table names must follow the above format (case sensitive). Table Products 00XX should have the following fields in correct data types. id: auto increment integer, primary key archar(255) NOT NULL escription: varchar(255), NOT NULL sell price: float, NOTNULL ost: float, NOT NULL quantity: int, NOT NULL id: int, NOTNULL aforeign key to the id field in CPS3740.Users table. vendor id:int, NOT NULL a foreign key to the V ld field in CPS3740 Vendors table. Table CPS3740.vendors has fields (V_ld, Name, Zipcode, State 3. Add a search function on the project 2 home page with a "Search Products button after the keyword 1) which will call a new CGI program to do the following search functions after textbox (shown in Figure users click on the "Search Products" button, your program will use the keywords to do pattern match on both name and description columns in your Product table. (5 pts 3.1 the keyword is all product records should be displayed, the keyword is empty, an error message "no keyword entered" should be displayed and the program should not access database. 3.2 (5 pts) If no record found, please display "No record found with the search keywords: records found, please display all matched records in HTML TABLE tag format. 3.3 (5 pts) Search function should use GET method in the HTML cFORM .The search URL should like: http:lle eve kean. oduct.php? keywords the input keywords our IP: 57.83.156.42 Huang's CPs3740 project2. to Austin ou are NOT from Kean Unversity. elcome user: XYZ BBB 33 James St.,N,0733 Figure 1. New search product function Figure 2. Update link only available to staff The following functions should be available only after a user successfully login. All these CGI programs should use the POST method. 5 pts) Your login program should set cookie using the login ID as the cookie value. If the user doesn't successfully login, your program should not set the cookie. link to clean/expire the cookie. 5 pts) On the welcome page, you should have a logout 5 pts) f the user role is "Staff", display additional 3 links "Add products", "Display products". Update products" as shown in Figure 2.

Explanation / Answer

The SQL query to create this table is as follows:

CREATE TABLE `Products_emailid` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`description` varchar(255) NOT NULL,
`sell_price` float NOT NULL,
`cost` float NOT NULL,
`quantity` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`vendor_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_Vendor_idx` (`vendor_id`),
KEY `fk_User_idx` (`user_id`),
CONSTRAINT `fk_User` FOREIGN KEY (`user_id`) REFERENCES `Users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_Vendor` FOREIGN KEY (`vendor_id`) REFERENCES `Vendors` (`V_Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Note: Replace in the table name, email id with the string mentioned in the question.

So here I have written the create statement exactly as per the specifications.

It has 1 primary key and 2 foreign keys.

For the foreign keys, I have used ON DELETE CASCADE and ON UPDATE CASCADE.

This ensures that whenever the entry from the primary key is deleted, the referring entries from the foreign key are also deleted.

For example, if in the Users table, user id 4 is deleted, then the entries in this table will also get deleted where User_id = 4.

Do comment if there is any query. Thank you. :)

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

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