ARE YOU LOOKING FOR RELIABLE DATA MANAGEMENT AND GOVERNANCE ASSIGNMENT HELP SERVICES? EXPERTSMINDS.COM IS RIGHT CHOICE AS YOUR STUDY PARTNER!

CI7300 - Data Management and Governance - Kingston University London

All About Furniture' DB Design Exercise

Question: Design a class diagram that will capture the data, associations and constraints capable of supporting the requirements outlined

Convert the class diagram into a relational model. The relational model should include all primary keys, foreign keys, datatypes and any other required constraints you have identified.

Implement your design using Oracle. You will need to perform the following activities:

Convert your relational model into an SQL database.

Populate your database with some sample data

Write 5 queries that demonstrate that your database meets the data requirements of the system.

SAVE YOUR HIGHER GRADE WITH ACQUIRING DATA MANAGEMENT AND GOVERNANCE ASSIGNMENT HELP & QUALITY HOMEWORK WRITING SERVICES OF EXPERTSMINDS.COM

Solution:

Chapter 1: Introduction

In this assignment we will discuss and design the database of an e commerce company named AAF (All About Furniture), where customer can buy the latest furniture with wide range of the variety online and AAF (All About Furniture) will deliver the product to the given address by the customer. So we have designed a data base system where we can store all product detail with its category. In this data base we will store inventory record of the system that will tell number of product which is available in the store at current date. To build the customer relationship, it is necessary to store the address of the customer for further contact and feedback. So we make a storage space to save the user information such as his name, his age, his gender, his email ID, his local address and permanent address and the contact number of the customer, when they sign up in the e commerce website of the AAF (All About Furniture) and they will allotted a unique user ID and set password. For keep all these record we have designed a database where we can store details of customers, product, order and many other related data.We also store the details of payment .When the customer order something online then an invoice will be created in the system in which all the details like customer name, product name, order date and payment mode all the details will be there in the system and these details can be used in future for retrieving the details of order on particular date.This data base also saves the information of the products that are in the stock in the warehouse and those products which are going to be launched in the market soon. This data base also keeps updating itself according to the present stock in the warehouse and keeps a track record of the data. For designing process we have to provide different category of the furniture that AAF (All About Furniture) will provide to its customer. In each category there will be sub category which will show the similar product with variety of price range. Like in the kitchen section storage self will be mentioned in the category list and in the sub category we will mention the different variety of storage selves with different design pattern and of different price range should be mentioned.

Chapter 2: The Class Diagram

DO YOU WANT TO EXCEL IN DATA MANAGEMENT AND GOVERNANCE ASSIGNMENT? HIRE TRUSTED TUTORS FROM EXPERTSMINDS AND ACHIEVE SUCCESS!

Chapter 3: A discussion
In the diagram shown above is about the working All around Furniture .All the process and classes of the system is included in it. The classes in system like product, payment, customer, users and many more are included with its relationship. In the system we try to record the details of order and shipment and all customer details as per the requirement of the user.

All the payment details with its mode are stored in the system and with the order all shipment details are stored. All the order details like order number, product name, customer number and its date has been recorded in the system. And the method in order class are AddNewOrder(),ModifyOrder(),DeleteOrder() and all the shipping records are also monitored in the system.

In the Invoice class,Payment Class and PaymentMode Class have different methods like AddInvoice(), DeleteInvoice() and many more methods are included in the system.

• Customers need to register to the system and create a user ID for further login process.
• In the system we try to store all the retails of product with its category so that we can easily retrieve product details with its category.
• For payment customer can use credit card, debit card or cash on delivery all these details will be stored in the system.

Chapter 3: The Relational Model.

Data Dictionary

EXPERTSMINDS.COM ACCEPTS INSTANT AND SHORT DEADLINES ORDER FOR DATA MANAGEMENT AND GOVERNANCE ASSIGNMENT - ORDER TODAY FOR EXCELLENCE!

Chapter 4: Implementation
DROP TABLE SHIPPING;
DROP TABLE PAYMENT;
DROP TABLE ORDERS;
DROP TABLE INVENTORY;
DROP TABLE PRODUCT;
DROP TABLE CATEGORY;
DROP TABLE CUSTOMER;
CREATE TABLE CUSTOMER
(
C_ID NUMBER NOT NULL
, FNAME VARCHAR2(50)
, LNAME VARCHAR2(50)
, ADDRESS VARCHAR2(100)
, ZIP NUMBER
, PHONENO NUMBER
, CONSTRAINT CUSTOMER_PK PRIMARY KEY
(
C_ID
)
ENABLE
);

CREATE TABLE CATEGORY
(
CATEGORYID NUMBER NOT NULL
, CATEGORYNAME VARCHAR2(200)
, CONSTRAINT CATEGORY_PK PRIMARY KEY
(
CATEGORYID
)
ENABLE
);

CREATE TABLE PRODUCT
(
PRODUCTNO NUMBER NOT NULL
, PRODUCTNAME VARCHAR2(50)
, DESCRIPTION VARCHAR2(100)
, CATEGORYID NUMBER
, PRICE NUMBER
, CONSTRAINT PRODUCT_PK PRIMARY KEY
(
PRODUCTNO
)
ENABLE
);

CREATE TABLE INVENTORY
(
INVENTORYNO NUMBER NOT NULL
, PRODUCTNO NUMBER
, STOCK NUMBER
, CHECKINGDATE DATE
, CONSTRAINT INVENTORY_PK PRIMARY KEY
(
INVENTORYNO
)
ENABLE
);

CREATE TABLE ORDERS
(
ORDERSID NUMBER
, C_ID NUMBER
, PRODUCT NUMBER
);

CREATE TABLE PAYMENT
(
PAYMENT_ID NUMBER NOT NULL
, INVOICED NUMBER
, PAYMENT_MODE VARCHAR2 (100)
, DESCRIPTION VARCHAR2(100)
, AMOUNT NUMBER
, CONSTRAINT PAYMENT_PK PRIMARY KEY
(
PAYMENT_ID
)
ENABLE
);

CREATE TABLE SHIPPING
(
SHIPPINGID NUMBER NOT NULL
, ORDERS NUMBER
, CONSTRAINT SHIPPING_PK PRIMARY KEY
(
SHIPPINGID
)
ENABLE
);

ALTER TABLE PRODUCT
ADD CONSTRAINT PRODUCT_FK1 FOREIGN KEY
(
CATEGORY
)
REFERENCES CATEGORY
(
CATEGORYID
)
ENABLE;

ALTER TABLE ORDERS
ADD CONSTRAINT ORDERS_FK1 FOREIGN KEY
(
CUSTID
)
REFERENCES CUSTOMERSS
(
CUSTID
)
ENABLE;
ALTER TABLE ORDERS
ADD CONSTRAINT ORDERS_FK2 FOREIGN KEY
(
PRODUCT
)
REFERENCES PRODUCT
(
PRODUCTNO
)
ENABLE;

---Inserting Data
Insert into CATEGORY values (11,'Bathroom');
Insert into CATEGORY values (12,'Bedroom');
Insert into CATEGORY values (13,'Floors and Doors');
Insert into CATEGORY values (14,'Garden Shed and Garage');
Insert into CATEGORY values (15,'Home Office');
Insert into CATEGORY values (16,'In the Kitchen');
Insert into CATEGORY values (17,'Living Room');
Insert into CATEGORY values (18,'On Your Travels');
Insert into CATEGORY values (19,'Protect It');
Insert into CATEGORY values (10,'Recycling and Waste Bags');

Insert into PRODUCT values (111,'Broom','',11, 50);
Insert into PRODUCT values (112,'Wipes','',16, 70);
Insert into PRODUCT values (113,'Fresh Cloths','',16, 90);
Insert into PRODUCT values (114,'Big Clip','',16, 80);
Insert into PRODUCT values (115,'Brabantia Slim line Bin', 'Waste bins.',16, 100);
Insert into PRODUCT values (116,'Bed','',12, 200);

Insert into CUSTOMER values (111,'Thomas', 'Dic','Street-2343,Near Hospital',12254,964444555);
Insert into CUSTOMER values (112,'Jimmy','Mar','Street-2343,Near Hospital',2574,885421245);
Insert into CUSTOMER values (113,'SalilF','Sam','Street-2343,Near Hospital',4441,425272588);
Insert into CUSTOMER values (114,'John', 'Ray','Street-2343,Near Hospital',1114,936544442);
Insert into CUSTOMER values (115,'David','K','Street-2343,Near Hospital',4441,456969977);

Insert into ORDERS values (122,112, 111);
Insert into ORDERS values (123,111,116);
Insert into ORDERS values (124,115,116);
Insert into ORDERS values (125,111,116);
Insert into ORDERS values (126,112,112);
Insert into ORDERS values (127,116,116);
INSERT INTO INVENTORY (INVENTORYNO, PRODUCTNO, STOCK, CHECKINGDATE) VALUES ('11', '111', '333', TO_DATE('2019-05-01 16:11:45', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO INVENTORY (INVENTORYNO, PRODUCTNO, STOCK, CHECKINGDATE) VALUES ('12', '112', '432', TO_DATE('2019-05-03 16:11:49', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO INVENTORY (INVENTORYNO, PRODUCTNO, STOCK, CHECKINGDATE) VALUES ('13', '114', '211', TO_DATE('2019-05-04 16:11:53', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO INVENTORY (INVENTORYNO, PRODUCTNO, STOCK, CHECKINGDATE) VALUES ('14', '115', '332', TO_DATE('2019-05-02 16:11:57', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO INVENTORY (INVENTORYNO, PRODUCTNO, STOCK, CHECKINGDATE) VALUES ('15', '111', '111', TO_DATE('2019-05-04 16:12:00', 'YYYY-MM-DD HH24:MI:SS'));

NEVER LOSE YOUR CHANCE TO EXCEL IN DATA MANAGEMENT AND GOVERNANCE ASSIGNMENT - HIRE BEST QUALITY TUTOR FOR ASSIGNMENT HELP!

Chapter 5: Queries

Select C.FNAME,C.LNAME ,P.ProductName
From CUSTOMER C JOIN Orders O
ON C.C_ID=O. C_ID
JOIN Product P
ON O.Product=P.PRODUCTNO ;

Select P.Productname,C.CATEGORYNAME
FROM Product P JOIN CATEGORY C
ON P.CATEGORYID = C.CATEGORYID;

Select P.Productname, C.FName,C.LName, P.ProductName, Count(P.ProductNO)
FROM Product P JOIN ORDERS O
ON P. PRODUCTNO = o.product
JOIN CUSTOMER C
ON O.C_ID=C. C_ID
GROUP by P.Productname, C.FName,C.LName, P.ProductName
ORDER ByP.Productname;

Select P.Productname, SUM (I. STOCK)AsStock_Available, I.CHECKINGDATE
FROM Product P JOIN INVENTORY I
ON P. PRODUCTNO = i.productno
GROUP by P.Productname, I. CHECKINGDATE
ORDER ByP.Productname;

Select C.FName, Count(P.ProductName)AS No_OF_Product,SUM(P.PRICE) as Total
FROM Product P JOIN ORDERS O
ON P. PRODUCTNO = O.PRODUCT
JOIN CUSTOMER C
ON O.C_ID=C. C_ID
GROUP by C.FName
ORDER ByC.FName;

ORDER NEW DATA MANAGEMENT AND GOVERNANCE ASSIGNMENT & GET 100% ORIGINAL SOLUTION AND QUALITY WRITTEN CONTENTS IN WELL FORMATS AND PROPER REFERENCING.

Chapter 6: Conclusion.

In this report we have discussed about the businessall about furniture and try to show its different aspect to the users. Here we have designed Entity relational Diagram for the company so that user can understand the user of each entity and their relationship. We have also showed the class diagram of the system and shown all possible class in the system. Here we discussed about different style of database creation. We have designed database as per the user requirement and searched many database book for the steps. This database will provide the required support to this e commerce web site of the AAF (All about Furniture). This website will work as a typical e commerce website. As a user enters the AAF website he will land to the home page of the website. To place an order from the website, customer needs to sign up and create a unique user ID and password. From this page we will store all the necessary information of the user for further contact to customer and to take the feedback from the customer. Customer can choose the product by selecting his desired category and then the subcategory of the product. AAF provides variety of furniture like kitchenware, cleaning products, storage solutions etc. Customer will reach to his desired product by first selecting the listed category in the left side of the home page, then it need to click on the shown sub category. This landing page will show the picture of the product as well as the name of the product, description of the product, reference number of the product and the quoted price of the product. Then he will be provided an option to place the order in the shopping basket by mentioning the required quantity of the product.

AAF will need to do the SEO (Search Engine Optimization) and SMO (Social Media Optimization) of each individual product page as user might reach to the website by just placing the appropriate key word in the search engine. Then he will automatically redirect to the product page and not on the home page of the AAF website. Customer can also reach to the product page by just enter the product reference number and the quantity required for the product and then simply add it to their shopping basket and move towards the payment section. After adding the product to the shopping basket there is also an option to remove the product from the basket or add another product to the shopping basket. We can also change the number of quantity added in the shopping basket. Customer can simply click purchase and will land towards the payment page.

We have to store the payment details of the product as we have to automatically generate the invoice for that product. We need to store the delivering address of the customer as well. By default it will take the delivery address as the home address. Here AAF have provided to choose the option for the delivery address, if the customer wants the product to be delivered on the other address then the home address then he can just mention in the option provided for to choose the delivery address. Our data base will store this address and present it to the delivery mail address. AAF has total of ten warehouses across the country and it also provide the facility to choose the warehouse which is nearest ton the delivery address or it will be suitable for customer to just pick up his product from the warehouse itself.

Our designed data base is fully functional and able to fully support this e commerce website. We have provided the relational model with the ERD diagram, which tells all linkages between the category list and the subcategory list. There are products which are more than two category list as of their functionality. Then we have provided all the implementation coding and maximum possible queries for this data base architecture.

GET GUARANTEED SATISFACTION OR MONEY BACK UNDER DATA MANAGEMENT AND GOVERNANCE ASSIGNMENT HELP SERVICES OF EXPERTSMINDS.COM - ORDER TODAY NEW COPY OF THIS ASSIGNMENT!

Acquire our Kingston University London Assignment help services for related Units in which our professional tutors precisely deal with:

• Modelling Enterprise Architectures Assignment Help
• Agile Project Development Assignment Help
• Project Dissertation Assignment Help
• Data Management and Governance Assignment Help
• eBusiness Strategy and Implementation Assignment Help