ARE YOU LOOKING FOR RELIABLE ICT211 DATABASE DESIGN ASSIGNMENT HELP SERVICES? EXPERTSMINDS.COM IS RIGHT CHOICE AS YOUR STUDY PARTNER!

ICT211 Database Design Assignment - University of the Sunshine Coast, Australia

Case Study - Australian Native Plants Ltd - Online CRM Database

Part A - Submitted as a MS Word Document: Entity Relationship Diagram in Crows Foot Notation. Relational Schema - including Primary and Foreign Keys. Supplementary Design requirements - for example but not limited to:  information on length of identifiers, postcodes, names, data attribute information (compulsory, variable length / type, etc.). Assumptions.

Introduction

Here we will discuss about the Australian Native Plants Ltd (ANP), they are involved in plants selling plants. As per the case study ANP is facing problem of redundant data in their system for that they have contacted us to design systems which have related data and duplicate data reduces. From their websites most of the clients register then self for buying the plants from different nursery and the nursery owner also add plants detail on the website. Now a day ANP is having six nurseries for their orders and to keep their details they need some support. So have decided to design a system for them where they can store details of clients, plants and other data which are related to it.

Entity Relationship Diagram in Crows Foot Notation

Relational Schema - including Primary and Foreign Keys

SAVE YOUR HIGHER GRADE WITH ACQUIRING ICT211 DATABASE DESIGN ASSIGNMENT HELP & QUALITY HOMEWORK WRITING SERVICES OF EXPERTSMINDS.COM!

Assumptions

This database is design as per the given data to us ,but for making relationship between each we have created some new tables .We have included nursery table to keep the details of all the nurseries who are supplying plants to ANP. We also include shipping table which stored the details shipped plants from ANP.

Part B - Submitted as a single plain text file with name <student Number> _crm .sql, containing all your SQL implementation: CREATE TABLE statements for all tables including integrity constraints, CREATE TRIGGER statements. CREATE FUCNTION/PROCEDURE.

CREATE TABLE

1. Client Table

CREATE TABLE 'client' (

'ClientID' int(11) NOT NULL,

'Name' varchar(100) NOT NULL,

'Email' varchar(50) NOT NULL,

'Address' varchar(100) NOT NULL,

'Pin code' int(11) NOT NULL

);

2. Inventory table

CREATE TABLE 'inventory' (

'InventryID' int(11) NOT NULL,

'PlantID' varchar(50) NOT NULL,

'MemberID' int(11) NOT NULL,

'InStock' int(11) NOT NULL,

'InvenDate' date NOT NULL

);

3. Member Table

CREATE TABLE 'member' (

'MemberID' int(11) NOT NULL,

'Name' varchar(100) NOT NULL,

'ContactName' varchar(100) NOT NULL,

'Date_of_Start' date NOT NULL,

'Date_of_End' date NOT NULL,

'Phone' int(11) NOT NULL,

'Email' varchar(100) NOT NULL,

'Address' varchar(200) NOT NULL,

'NurseryName' varchar(50) NOT NULL

);

DO YOU WANT TO EXCEL IN ICT211 DATABASE DESIGN ASSIGNMENT? HIRE TRUSTED TUTORS FROM EXPERTSMINDS AND ACHIEVE SUCCESS!

4. Nursery Table

CREATE TABLE 'nursery' (

'Nurserycode' varchar(50) NOT NULL,

'Nursery_Name' varchar(100) NOT NULL,

'address' varchar(200) NOT NULL

);

5. Order

CREATE TABLE 'order' (

'orderID' int(11) NOT NULL,

'ClientID' int(11) NOT NULL,

'MemberID' int(11) NOT NULL,

'OrderDate' date NOT NULL,

'OrderStatus' varchar(100) NOT NULL,

'ShippingDate' date NOT NULL,

'ShippingReferralNo' int(11) NOT NULL,

'ShippingCost' int(11) NOT NULL);

6. Order Items Table

CREATE TABLE 'order_items' (

'order_ItemID' int(11) NOT NULL,

'PlantID' varchar(50) NOT NULL,

'OrderID' int(11) NOT NULL,

'UnitCost' int(11) NOT NULL,

'Quantity' int(11) NOT NULL

);

EXPERTSMINDS.COM ACCEPTS INSTANT AND SHORT DEADLINES ORDER FOR ICT211 DATABASE DESIGN ASSIGNMENT - ORDER TODAY FOR EXCELLENCE!

7. Plants

CREATE TABLE 'plants' (

'PlantID' varchar(50) NOT NULL,

'Botanical_Name' varchar(100) NOT NULL,

'Common_Name' varchar(100) NOT NULL,

'Description' varchar(200) NOT NULL

);

8. Shipping Table

CREATE TABLE 'shipping' (

'ShippingReferralNo' int(11) NOT NULL,

'Courier_Name' varchar(100) NOT NULL,

'ANPNurseryState' varchar(100) NOT NULL,

'DestinationClientState' varchar(100) NOT NULL,

'ShippingCostMultiplier' float NOT NULL

);

INDEXES in theTables

ALTER TABLE 'client'

ADD PRIMARY KEY ('ClientID');

ALTER TABLE 'inventory'

ADD PRIMARY KEY ('InventryID'),

ADD KEY 'Inv_Fk' ('PlantID'),

ADD KEY 'INV_fk2' ('MemberID');

ALTER TABLE 'member'

ADD PRIMARY KEY ('MemberID');

ALTER TABLE 'nursery'

ADD PRIMARY KEY ('Nurserycode');

ALTER TABLE 'order'

ADD PRIMARY KEY ('orderID');

ALTER TABLE 'order_items'

ADD PRIMARY KEY ('order_ItemID'),

ADD KEY 'PlantID' ('PlantID'),

ADD KEY 'OrderID' ('OrderID');

ALTER TABLE 'plants'

ADD PRIMARY KEY ('PlantID');

ALTER TABLE 'shipping'

ADD PRIMARY KEY ('ShippingReferralNo');

ALTER TABLE 'inventory'

MODIFY 'InventryID' int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

ALTER TABLE 'member'

MODIFY 'MemberID' int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

ALTER TABLE 'order'

MODIFY 'orderID' int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1114;

NEVER LOSE YOUR CHANCE TO EXCEL IN ICT211 DATABASE DESIGN ASSIGNMENT - HIRE BEST QUALITY TUTOR FOR ASSIGNMENT HELP!

Constraints

ALTER TABLE 'inventory'

ADD CONSTRAINT 'INV_fk2' FOREIGN KEY ('MemberID') REFERENCES 'member' ('MemberID'),

ADD CONSTRAINT 'Inv_Fk' FOREIGN KEY ('PlantID') REFERENCES 'plants' ('PlantID');

ALTER TABLE 'order_items'

ADD CONSTRAINT 'fk1' FOREIGN KEY ('PlantID') REFERENCES 'plants' ('PlantID');

ORDER NEW ICT211 DATABASE DESIGN ASSIGNMENT & GET 100% ORIGINAL SOLUTION AND QUALITY WRITTEN CONTENTS IN WELL FORMATS AND PROPER REFERENCING.

CREATE TRIGGER

CREATE TRIGGER 'ClientmsgTrigger' AFTER INSERT ON 'Order'

FOR EACH ROW

BEGIN

INSERT INTO 'Notifications'

('ClientID', 'New Client has been added')

VALUES

(NEW.'ClientID');

END$$

DELIMITER;

CREATE FUCNTION / PROCEDURE

DELIMITER $$ ;

CREATE PROCEDURE plantcost()

SELECT P.Botanical_Name,(o.shippingcost*(I.Quantity*I.UnitCost)) as Total

FROM 'order' O

JOIN order_items I

ON O. OrderID=I. OrderID

JOIN plants P

ON I. PlantID=P. PlantID;

 $$

GET GUARANTEED SATISFACTION OR MONEY BACK UNDER ICT211 DATABASE DESIGN ASSIGNMENT HELP SERVICES OF EXPERTSMINDS.COM - ORDER TODAY NEW COPY OF THIS ASSIGNMENT!

 INSERT INTO

Insert data in Plant table:

INSERT INTO 'plants' ('PlantID', 'Botanical_Name', 'Common_Name', 'Description') VALUES

('Plant1', 'Eutaxia obovata', 'eggs and bacon ', 'Native shrub grows in WA'),

('Plant10', 'Grevillea pauciflora ', 'Pt Lincoln grevillea ', 'Native shrub grows in SA'),

('Plant11', 'Eucalyptus sepulcralis ', 'weeping mallee ', 'Native WA tree'),

('Plant2', 'Eremophila glabra', 'common emu bush ', 'Native bush grows in SA'),

('Plant3', 'Acacia floribunda  ', 'gossamer wattle Large', 'native tree grows in Qld, NSW and Vic'),

('Plant4', 'Goodenia amplexans', 'clasping goodeni', 'Native shrub grows in SA and Vic'),

('Plant5', 'Eucalyptus viridis ', 'green mallee ', 'Large native tree grows in Qld, NT and Vic'),

('Plant6', 'Grevillea banksii ', 'red silky oak ', 'Large native shrub grows in Qld'),

('Plant7', 'Eutaxia diffusa\r\n', 'spreading malleepea ', 'Native shrub grows in SA'),

('Plant8', 'Acacia gracilifolia ', 'graceful wattle ', 'Native shrub grows in every state'),

('Plant9', 'Tetratheca ciliata ', 'pink bells ', 'Native shrub grows in SA, NT, Tas and Vic');

Insert data in Shipping Table:

INSERT INTO 'shipping' ('ShippingReferralNo', 'Courier_Name', 'ANPNurseryState', 'DestinationClientState', 'ShippingCostMultiplier') VALUES

(1, 'XYZ1', 'NSW', 'NSW', 1),

(2, 'XYZ2', 'NSW', 'QLD', 1.5),

(3, ' XYZ 3', 'NSW', 'NT', 1.2),

(4, ' XYZ 4', 'NSW', 'VIC', 1.6),

(5, ' XYZ 5', 'NSW', 'SA', 1.7),

(6, ' XYZ 6', 'NSW', 'ACT', 1.8),

(7, ' XYZ 7', 'NSW', 'WA', 1.5),

(8, ' XYZ 8', 'QLD', 'TAS', 2),

(9, ' XYZ 9', 'QLD', 'NT', 1),

(10, ' XYZ 10', 'QLD', 'VIC', 1.5);

DO WANT TO HIRE TUTOR FOR ORIGINAL ICT211 DATABASE DESIGN ASSIGNMENT SOLUTION? AVAIL QUALITY ICT211 DATABASE DESIGN ASSIGNMENT WRITING SERVICE AT BEST RATES!

Insert data in Client Table:

INSERT INTO 'client' ('ClientID', 'Name', 'Email', 'Address', 'Pin code') VALUES

(111, 'Dick', '[email protected]', 'street-1281,rius', 2277),

(112, 'david', '[email protected]', '766 Road', 2299),

(113, 'Jimmy', '[email protected]', 'opposite of avenue Risus. Ave', 6655),

(114, 'charly', '[email protected]', '6655-street 7 St', 8877),

(115, 'kicky', '[email protected]', '98 park Avenue', 7887);

Insert data in Inventory Table:

INSERT INTO 'inventory' ('InventryID', 'PlantID', 'MemberID', 'InStock', 'InvenDate') VALUES

(1, 'Plant1', 4, 450, '2019-05-01'),

(2, 'Plant2', 1, 400, '2019-05-03'),

(3, 'Plant4', 2, 250, '2019-05-04'),

(4, 'Plant3', 4, 400, '2019-05-03'),

(5, 'Plant5', 5, 250, '2019-05-04');

Insert data in member Table:

INSERT INTO 'member' ('MemberID', 'Name', 'ContactName', 'Date_of_Start', 'Date_of_End', 'Phone', 'Email', 'Address', 'NurseryName') VALUES

(1, 'Anna Zikov', 'ABC1', '2015-05-07', '0000-00-00', 986843410, '[email protected]', '37 Greenock Road Greenock 5360 SA', 'Greenock Australian Native Nursery '),

(2, 'Brenda Coen', 'Conn', '2019-05-01', '0000-00-00', 65365223, '[email protected]', '45 Frederick Street Broome WA 6725', 'Kimberly Native Plants'),

(3, 'Jaden Bess', 'Bessy', '2019-03-01', '0000-00-00', 454545542, '[email protected]', '22 Summerleas Road Kingston Tas 7050', 'Native to Kingston'),

(4, 'Adam Ross', 'Rosy', '2019-05-01', '0000-00-00', 563532098, '[email protected]', '34 Lowe Street Queanbeyan NSW 2620', 'Highland Native plants'),

(5, 'Susan Gahn', 'KLJ ', '2013-05-01', '2019-05-02', 53654542, '[email protected]', '13 Sternberg Cresent 2903 ACT', 'Australian Capital Native Plants'),

(6, 'Peter Back', 'Peter', '2017-10-01', '2019-05-01', 0, '[email protected]', '158 Bunya Highway Dalby Qld 4405', 'Dalby native plants');

GETTING STUCK WITH SIMILAR ICT211 DATABASE DESIGN ASSIGNMENT? ENROL WITH EXPERTSMINDS'S ICT211 DATABASE DESIGN ASSIGNMENT HELP SERVICES AND GET DISTRESSED WITH YOUR ASSIGNMENT WORRIES!

Insert data in Order table:

INSERT INTO 'order' ('orderID', 'ClientID', 'MemberID', 'OrderDate', 'OrderStatus', 'ShippingDate', 'ShippingReferralNo', 'ShippingCost') VALUES

(1111, 111, 1, '2019-05-01', 'Shipped', '2019-05-08', 1, 455),

(1112, 112, 3, '2019-05-03', 'Not shipped', '0000-00-00', 2, 67),

(1113, 113, 4, '2019-05-04', 'shipped', '2019-05-08', 3, 80);

Insert data in Order Items table:

INSERT INTO 'order_items' ('order_ItemID', 'PlantID', 'OrderID', 'UnitCost', 'Quantity') VALUES

(1, 'Plant1', 1111, 6, 25),

(2, 'Plant2', 1112, 5, 20),

(3, 'Plant3', 1113, 6, 25);

NEVER BE CAUGHT IN PLAGIARISM, AVAIL ICT211 DATABASE DESIGN ASSIGNMENT HELP SERVICE OF EXPERTSMINDS.COM AND SAVE HIGHER MARKS!

Queries:

Query 1:

SELECT C.Name,C.Address,O.OrderDate,O.OrderStatus,I.Quantity,I.UnitCost,O.shippingcost

FROM client C JOIN 'order' O

ON C. ClientID=O. ClientID

JOIN order_items I

ON O. OrderID=I. OrderID

JOIN plants P

ON I. PlantID=P. PlantID;

Query 2:

SELECT C.Name,C.Address,O.OrderDate,O.OrderStatus,(I.Quantity*I.UnitCost) as Total,O.orderstatus

FROM client C JOIN 'order' O

ON C. ClientID=O. ClientID

JOIN order_items I

ON O. OrderID=I. OrderID

JOIN plants P

ON I. PlantID=P. PlantID;

EXPERTSMINDS.COM GIVES ACCOUNTABILITY OF YOUR TIME AND MONEY - AVAIL TOP RESULTS ORIGINATED ICT211 DATABASE DESIGN ASSIGNMENT HELP SERVICES AT BEST RATES!

Access our University of the Sunshine Coast Assignment Help Services for its related courses and academic units such as:-

• ICT702 Data Wrangling Assignment Help
• ICT700 Systems Analysis Assignment Help
• ICT706 Machine Learning Assignment Help
• ICT707 Data Science Practice Assignment Help
• ICT352 Project Management Assignment Help
• ICT351 ICT Professional Practice Assignment Help
• ICT704 Cloud Database Systems Assignment Help
• ICT701 Relational Database Systems Assignment Help
• ICT705 Data and System Integration Assignment Help
• ICT710 ICT Professional Practice and Ethics Assignment Help
• ICT120 Introduction to Computer Networks Assignment Help
• ICT703 Network Technology and Management Assignment Help