Get Assured Top-Notch Grades With ICT211 Database Design Assignment Help Service And Professional Tutors..!!

Home   Course   University of the Sunshine Coast Assignment Help
Previous << || >> Next

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

Database Design Assignment.png

Relational Schema - including Primary and Foreign Keys

Table

Attributes

Data Types

Constraints

Client

Client ID

INT

Primary Key

 

Name

TEXT

 

 

Email

TEXT

 

 

Address

TEXT

 

 

Pin code

INT

 

Nursery 

Nursery code

INT

Primary Key

 

Nursery name

TEXT

 

 

address

TEXT

 

Member

Member ID 

INT

Primary Key

 

Name

TEXT

 

 

Contact Name

TEXT

 

 

Date_of_Start

DATE

 

 

Date_of_End

DATE

 

 

Phone 

INT

 

 

Email 

TEXT

 

 

Nursery Code

INT

 

Plants

PlantID

INT

Primary Key

 

Botanical Name

TEXT

 

 

Common Name

TEXT

 

 

Description

TEXT

 

Inventory 

InventryID

INT

Primary Key

 

PlantID

INT

Foreign Key

 

MemberID

INT

Foreign Key

 

In Stock

INT

 

 

InvenDate

DATE

 

Shipping

ShippingReferralNo  

INT

Primary Key

 

CourierName

TEXT

 

Order

orderID

INT

Primary Key

 

ClientID 

INT

Foreign Key

 

member ID

INT

Foreign Key

 

Order Date

DATE

 

 

OrderStatus

TEXT

 

 

Shipping Date

DATE

 

 

ShippingReferralNo  

INT

Foreign Key

 

Shipping Cost

INT

 

Order Items 

order_ItemID 

INT

Primary Key

 

PlantID

INT

Foreign Key

 

Order ID

INT

Foreign Key

 

UnitCost

INT

 

 

Quantity

INT

 

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;

Database Design Assignment1.jpg

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');

Database Design Assignment2.jpg

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', 'dick1291@gmail.com', 'street-1281,rius', 2277),

(112, 'david', 'dickkk@gmail.com', '766 Road', 2299),

(113, 'Jimmy', 'jim@gmail.com', 'opposite of avenue Risus. Ave', 6655),

(114, 'charly', 'charly@gmail.com', '6655-street 7 St', 8877),

(115, 'kicky', 'kickky@gmail.com', '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, 'rashmisingh.cse@gmail.com', '37 Greenock Road Greenock 5360 SA', 'Greenock Australian Native Nursery '),

(2, 'Brenda Coen', 'Conn', '2019-05-01', '0000-00-00', 65365223, 'conn@gmail.com', '45 Frederick Street Broome WA 6725', 'Kimberly Native Plants'),

(3, 'Jaden Bess', 'Bessy', '2019-03-01', '0000-00-00', 454545542, 'tyu@gmail.com', '22 Summerleas Road Kingston Tas 7050', 'Native to Kingston'),

(4, 'Adam Ross', 'Rosy', '2019-05-01', '0000-00-00', 563532098, 'kjhkjhj@gmail.com', '34 Lowe Street Queanbeyan NSW 2620', 'Highland Native plants'),

(5, 'Susan Gahn', 'KLJ ', '2013-05-01', '2019-05-02', 53654542, 'eshjh@gmail.com', '13 Sternberg Cresent 2903 ACT', 'Australian Capital Native Plants'),

(6, 'Peter Back', 'Peter', '2017-10-01', '2019-05-01', 0, 'peter@gmail.com', '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;

Database Design Assignment3.jpg

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;

Database Design Assignment4.jpg

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
Tag This :- TG51PAV65DAT ICT211 Database Design Assignment Help

get assignment Quote

Assignment Samples

    Policy Assignment Help

    policy assignment help - The paper is related to the healthcare polcies and the desired implementation within an organization.

    Anatomy Of Disaster Assignment Help

    anatomy of disaster assignment help - Read the drafts of Patty Seyburn's poem "Anatomy of Disaster" and her explanatory essay wherein she describes her process.

Get Academic Excellence with Best Skilled Tutor! Order Assignment Now! Submit Assignment