Fetch Product Details with Brand Name

The provided database schema consists of seven tables: brand, category, customer, order, product, and wishList. The tables contain data related to car brands, categories, customers, orders, products, and product images. There are also eight SQL queries that can be used to fetch data from the tables, including fetching all customer details, all product details, and product information with their corresponding images.

Description And Architecture
I choose to develop the database of used car application. In my application
two types of user can access the application with their particular usability in
the application. First type of user is end user who use this application to
buy the products, first user have to login or signup in the application than
he can perform many task like see the products, add them into their wishlist
and buy them. The second type of user is admin which will control the
details of end user and products. In this application admin can add new
product , edit detail of existing product, delete the product, can access
details of end user and manage the process of buy product.
The architecture of my application has seven tables first one is table of
product which contains the information regarding the product. Second table
is product brand which will has all the product brand available in the
market. Third table is product category which lists all the type of category
available in the market. Fourth table is product image in which there is
images regarding any product, the purpose of make different table for
product image is that a product can contain more than one image. Fifth
table is customer table which contain all the records regarding customer.
Sixth table is customer wish list in this table record of that product which
customer put in his wishlist. Seventh table is order table in this table record
of order placed by the customer is recorded.

E-R Diagram

Use Case Diagram

In this use case there are two actors which will access the application and
their functionality are described below
Customer - Customer can login or sign up in this application by filling up the
form containing its personal details. Customer can see all the products with
its detailed description. Customer can search or filter the products on the
basis of brand and category. Customer can place order of any product with
the quantity he required and after placing the order he can able to see the
status of the order he paced.
Admin - Admin will able to see the details of the customer. He can add,
update and delete any product. Admin can able to see the status of every
product placed by any customer and will update the status of the order as
required.

SQl Queries To Create Tables
Table structure for table `brand`
CREATE TABLE IF NOT EXISTS `brand` (
`brandID` int(11) NOT NULL AUTO_INCREMENT,
`brandName` varchar(50) NOT NULL,
PRIMARY KEY (`brandID`)
)
Table structure for table `category`
CREATE TABLE IF NOT EXISTS `category` (
`categoryID` int(11) NOT NULL AUTO_INCREMENT,
`categoryName` varchar(50) NOT NULL,
PRIMARY KEY (`categoryID`)
)
Table structure for table `customer`
CREATE TABLE IF NOT EXISTS `customer` (
`customerID` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`email` varchar(50) NOT NULL,
`password` varchar(50) NOT NULL,
`address` varchar(200) NOT NULL,
`contact` varchar(20) NOT NULL,
PRIMARY KEY (`productID`)
)
Table structure for table `order`
CREATE TABLE IF NOT EXISTS `order` (
`orderID` int(11) NOT NULL AUTO_INCREMENT,

`customerID` int(11) NOT NULL,
`productID` int(11) NOT NULL,
`quantity` int(11) NOT NULL,
`price` decimal(8,2) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`orderID`)
)
Table structure for table `product`
CREATE TABLE IF NOT EXISTS `product` (
`productID` int(11) NOT NULL AUTO_INCREMENT,
`productName` varchar(50) NOT NULL,
`brandID` int(11) NOT NULL,
`categoryID` int(11) NOT NULL,
`modelYear` date NOT NULL,
`description` text NOT NULL,
`price` decimal(8,2) NOT NULL,
PRIMARY KEY (`productID`)
)
Table structure for table `productImage`
CREATE TABLE IF NOT EXISTS `productImage` (
`imageID` int(11) NOT NULL AUTO_INCREMENT,
`productID` int(11) NOT NULL,
`imageName` varchar(50) NOT NULL,
PRIMARY KEY (`imageID`)
)
Table structure for table `wishList`
CREATE TABLE IF NOT EXISTS `wishList` (
`wishListID` int(11) NOT NULL AUTO_INCREMENT,
`customerID` int(11) NOT NULL,
`productID` int(11) NOT NULL,

PRIMARY KEY (`wishListID`)
)

SQL Queries To Insert Data Into Tables
Dumping data for table `brand`
INSERT INTO `brand` (`brandID`, `brandName`) VALUES
(1, 'ford'),
(2, 'Lincoln '),
(3, 'General Motors');
Dumping data for table `category`
INSERT INTO `category` (`categoryID`, `categoryName`) VALUES
(1, 'Mini'),
(2, 'Micro'),
(3, 'Sedan');
Dumping data for table `customer`
INSERT INTO `customer` (`customerID`, `name`, `email`, `password`,
`address`, `contact`) VALUES
(1, 'john', '[e-mail]', 'john@123', '1740 Buffalo Creek
Road,Tennessee,37201', '615-827-3727'),
(2, 'Patti C Hodges', '[e-mail]', 'paatiabc@12345', '1951 Lindale
Avenue,California,94107', '510-493-8821');
Dumping data for table `order`
INSERT INTO `order` (`orderID`, `customerID`, `productID`, `quantity`,
`price`, `date`) VALUES
(1, 1, 2, 1, 800.00, '2016-09-21'),
(2, 1, 5, 1, 2000.00, '2016-11-21'),

(3, 2, 6, 1, 750.00, '2016-11-01');
Dumping data for table `product`
INSERT INTO `product` (`productID`, `productName`, `brandID`,
`categoryID`, `modelYear`, `description`, `price`) VALUES
(1, 'Endevour', 1, 3, '2004-08-09', 'Perfect condition One owner car',
1000.00),
(2, 'Mettaliv', 1, 1, '2011-12-07', 'Perfect condition One owner car', 800.00),
(3, 'Mercury', 2, 2, '2009-12-01', 'Perfect condition One owner car',
1200.00),
(4, 'Zephyr', 2, 1, '2008-10-11', 'Perfect condition One owner car', 1500.00),
(5, 'Hummer', 3, 2, '2012-12-22', 'Perfect condition One owner car',
2000.00),
(6, 'Saturn', 3, 3, '2005-12-13', 'Perfect condition One owner car', 750.00);
Dumping data for table `productImage`
INSERT INTO `productImage` (`imageID`, `productID`, `imageName`)
VALUES
(1, 1, 'Endevour1.jpg'),
(2, 1, 'Endevour2.jpg'),
(3, 2, 'Mettaliv1.jpg'),
(4, 2, 'Mettaliv2.jpg'),
(5, 3, 'Mercury1.jpg'),
(6, 3, 'Mercury2.jpg'),
(7, 4, 'Zephyr1.jpg'),
(8, 4, 'Zephyr2.jpg'),
(9, 5, 'Hummer1.jpg'),
(10, 5, 'Hummer2.jpg'),
(11, 6, 'Saturn1.jpg'),
(12, 5, 'Saturn2.jpg');
Dumping data for table `wishList`

INSERT INTO `wishList` (`wishListID`, `customerID`, `productID`) VALUES
(1, 1, 3),
(2, 1, 6),
(3, 2, 5);

SQL Queries To Fetch Data
1. To fetch details of all the customers.
SELECT * FROM `customer`;
2. To fetch all product details.
SELECT * FROM `product`;
3. To fetch all images of particular product.
SELECT * FROM `productImage` WHERE `productID` = 1;
4. To fetch product with their images.
SELECT product.*, productImage.*
FROM product
INNER JOIN productImage
ON product.productID=productImage.productID;
5. To fetch fetch product with brand name.
SELECT product.*, brand.brandName
FROM product
INNER JOIN brand
ON product.brandID=brand.brandID;
6. To fetch product with their category name.
SELECT product.*, category.categoryName
FROM product
INNER JOIN category
ON product.categoryID=category.categoryID;

7. To fetch customer wishlist with the product name.
SELECT product.productName, customer.name
FROM wishList
INNER JOIN product
ON product.productID=wishList.productID
INNER JOIN customer
ON customer.customerID=wishList.customerID;
8. To fetch detail of customer order.
SELECT `order`.*,product.productName, customer.name
FROM `order`
INNER JOIN product
ON product.productID=`order`.productID
INNER JOIN customer
ON customer.customerID=`order`.customerID;