write a 500 words 2 page report on Database
byorv20n7
scenario_2.zipScenario #2/Database Scheme & SQL Queries.docx
Relational Database Schema
client ( Id, Name, Address, Contact, Deleted )
product ( Id, CategoryId, Name, Description, Inventory, Price, Deleted )
productCategory ( Id, Name, Deleted )
service ( Id, Name, Description, Price, Deleted )
order ( Id, ClientId, Total Amount Due, Payment, Change, Date )
orderDetails ( Id, OrderId, ItemType, ItemId, Quantity, Unit Price, Total )
workshop ( Id, Name, Description, Location, DateStarted, DateEnded, Status )
Single Underline = Primary Key
Double Underline = Foreign Key
SQL Queries
1. SELECT * FROM product INNER JOIN productCategory ON product.CategoryId = productCategory.Id ORDER BY product.name
2. SELECT * FROM order INNER JOIN orderDetails ON order.Id = orderDetails.OrderId ORDER BY date DESC
3. INSERT INTO client(Name, Address, Contact, Deleted) VALUES(‘Saint Louis University’, ‘Baguio City, Philippines’, ‘+639957525187’, ‘No’)
4. INSERT INTO workshop(Name, Description, Location, StartDate, EndDate, Status) VALUES(‘Geeks On The Go’, ‘New service center’, ‘La Union, Philippines’, ‘2017-08-05’, ‘2017-08-07’, ‘Active’)
5. UPDATE service SET name = ‘Installation of Computers’ WHERE Id = ‘1’
Scenario #2/database.sql
/* Navicat MySQL Data Transfer Source Server : localhost_3306 Source Server Version : 50505 Source Host : localhost:3306 Source Database : database Target Server Type : MYSQL Target Server Version : 50505 File Encoding : 65001 Date: 2017-08-05 06:47:54 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for `client` -- ---------------------------- DROP TABLE IF EXISTS `client`; CREATE TABLE `client` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(255) DEFAULT NULL, `Address` varchar(255) DEFAULT NULL, `Contact` varchar(255) DEFAULT NULL, `Deleted` varchar(255) DEFAULT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; -- ---------------------------- -- Records of client -- ---------------------------- INSERT INTO `client` VALUES ('1', 'Saint Louis University', 'Baguio City, Philippines', '+639957525187', 'No'); -- ---------------------------- -- Table structure for `order` -- ---------------------------- DROP TABLE IF EXISTS `order`; CREATE TABLE `order` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `ClientId` int(11) DEFAULT NULL, `TotalAmountDue` decimal(10,0) DEFAULT NULL, `Payment` decimal(10,0) DEFAULT NULL, `Change` decimal(10,0) DEFAULT NULL, `Date` date DEFAULT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; -- ---------------------------- -- Records of order -- ---------------------------- INSERT INTO `order` VALUES ('1', '1', '250', '300', '50', '2017-08-05'); -- ---------------------------- -- Table structure for `orderdetails` -- ---------------------------- DROP TABLE IF EXISTS `orderdetails`; CREATE TABLE `orderdetails` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `OrderId` int(11) DEFAULT NULL, `ItemType` varchar(255) DEFAULT NULL, `ItemId` int(11) DEFAULT NULL, `Quantity` int(11) DEFAULT NULL, `UnitPrice` decimal(10,0) DEFAULT NULL, `Total` decimal(10,0) DEFAULT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; -- ---------------------------- -- Records of orderdetails -- ---------------------------- INSERT INTO `orderdetails` VALUES ('1', '1', 'Product', '1', '1', '250', '250'); -- ---------------------------- -- Table structure for `product` -- ---------------------------- DROP TABLE IF EXISTS `product`; CREATE TABLE `product` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `CategoryId` int(11) DEFAULT NULL, `Name` varchar(255) DEFAULT NULL, `Description` varchar(255) DEFAULT NULL, `Inventory` int(11) DEFAULT NULL, `Price` decimal(10,0) DEFAULT NULL, `Deleted` varchar(255) DEFAULT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; -- ---------------------------- -- Records of product -- ---------------------------- INSERT INTO `product` VALUES ('1', '1', 'Asus ROG DLV324', 'Gaming Laptop', '10', '250', 'No'); -- ---------------------------- -- Table structure for `productcategory` -- ---------------------------- DROP TABLE IF EXISTS `productcategory`; CREATE TABLE `productcategory` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(255) DEFAULT NULL, `Deleted` varchar(255) DEFAULT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; -- ---------------------------- -- Records of productcategory -- ---------------------------- INSERT INTO `productcategory` VALUES ('1', 'Laptop', 'No'); INSERT INTO `productcategory` VALUES ('2', 'Personal Computer', 'No'); -- ---------------------------- -- Table structure for `service` -- ---------------------------- DROP TABLE IF EXISTS `service`; CREATE TABLE `service` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(255) DEFAULT NULL, `Description` varchar(255) DEFAULT NULL, `Price` decimal(10,0) DEFAULT NULL, `Deleted` varchar(255) DEFAULT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; -- ---------------------------- -- Records of service -- ---------------------------- INSERT INTO `service` VALUES ('1', 'Installation of Computers', 'Installing computers', '450', 'No'); -- ---------------------------- -- Table structure for `workshop` -- ---------------------------- DROP TABLE IF EXISTS `workshop`; CREATE TABLE `workshop` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(255) DEFAULT NULL, `Description` varchar(255) DEFAULT NULL, `Location` varchar(255) DEFAULT NULL, `StartDate` date DEFAULT NULL, `EndDate` date DEFAULT NULL, `Status` varchar(255) DEFAULT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; -- ---------------------------- -- Records of workshop -- ---------------------------- INSERT INTO `workshop` VALUES ('1', 'Geeks On The Go', 'New service center', 'La Union, Philippines', '2017-08-05', '2017-08-07', 'Active');
Scenario #2/Scenario #2 - ER Diagram.vsdx
Client Product Product Category Service Workshop Order Order Details Id int FK PK Name int FK PK Address int FK PK Contact int FK PK Deleted int FK PK Id int FK PK Category Id int FK PK Name int FK PK Description int FK PK Inventory int FK PK Price int FK PK Deleted int FK PK Id int FK PK Name int FK PK Id int FK PK Name int FK PK Description int FK PK Price int FK PK Deleted int FK PK Deleted int FK PK Id int FK PK Name int FK PK Description int FK PK Location int FK PK Date Started int FK PK Date Ended int FK PK Status int FK PK Id int FK PK Total Amount Due int FK PK Payment int FK PK Change int FK PK Date int FK PK Id int FK PK Order Id int FK PK Item Id int FK PK Quantity int FK PK Unit Price int FK PK Total int FK PK Client Id int FK PK M1 M2 M3 M4 M1 M2 M3 M4 M1 M2 M3 M4 M1 M2 M3 M4 M1 M2 M3 M4 The Task Scenario#2: Geeks-on-the-go Entity Relationship Diagram using crow’s foot Item Type int FK PK
