Just Decorate Pty. Ltd. is a local store that sells decorative items for home and office in Melbourne metropolitan area.
Posted: Fri Jun 10, 2022 11:55 am
Just Decorate Pty. Ltd. is a local store that sells decorative
items for home and office in Melbourne metropolitan area. The owner
of the company, Sandy Foster, has envisaged the importance of
technology in supporting business operations and decision making.
At the moment, there are four stores in Chadstone, Box Hill,
Richmond and Melbourne CBD, and there are about 50 staff including
part-timers employed by Sandy.
Sandy is keen to keep a track of the customer profiles and their
purchase details. Currently, each store purchases the decorative
items from over 20 different suppliers across the world. The stores
stock a wide range of elegant decorative items, mainly focussing on
vases, artificial plants, mirrors, clocks, paintings and photo
frames. The store managers are responsible for the purchases and
they deal directly with the suppliers. Once purchases are made,
these items are then delivered by the suppliers directly to the
stores.
While all brands are displayed in the shopfront of each store,
each store also has a storeroom to keep extra stock (for example,
items in different sizes, shapes and colours). In the storerooms,
each brand of item is stored in distinct spots and shelf locations.
Items are delivered using various mediums including ship, air,
rail, and courier van. Sandy would like the database management
system to be able to collect data and produce business reports that
could support her and her employees for their business
decision-makings. The database should be able to encapsulate
current sales, invoice details, customer profiles, supplier
details, shipping details, item details, and employee details.
To date, Sandy has managed this all herself using Google Sheets
and emails to keep track of all of the details. However, as her
business has expanded, it has become difficult to manage everything
and extract valuable insights about her business. As such, she
wants a dedicated database management system built specifically to
her needs, which has the capability of capturing all of the current
and future data associated with the business. She has contracted
Best Innovative Solutions (BIS) Pty. Ltd. to carry out this work,
with you assigned to design and build a database that meets her
specification.
Assume that you are employed as a business analyst at Best
Innovative Solution (BIS) Pty Ltd
Question: Create 3 queries that have the following
requirements. Note that each of the query can cover one or more
of
the requirements.
o One of the 3 queries should have a calculation.
o One of the 3 queries should make use of Group By.
o One of the 3 queries should include a scalar function (one that
returns
a value).
o One of the 3 queries should demonstrate Nested Query (could
be
either standard or correlated sub-query).
o One of the 3 queries should demonstrate joining tables.
o One of the 3 queries should demonstrate the creation and testing
of Trigger with test data.
For each query,
o Explain and justify its business purpose and business value or
impact.
o Provide the SQL script.
o Explain the design of SQL script.
o Make use of a table(s) with sample data to show the potential
result/
outcome of the query.
items for home and office in Melbourne metropolitan area. The owner
of the company, Sandy Foster, has envisaged the importance of
technology in supporting business operations and decision making.
At the moment, there are four stores in Chadstone, Box Hill,
Richmond and Melbourne CBD, and there are about 50 staff including
part-timers employed by Sandy.
Sandy is keen to keep a track of the customer profiles and their
purchase details. Currently, each store purchases the decorative
items from over 20 different suppliers across the world. The stores
stock a wide range of elegant decorative items, mainly focussing on
vases, artificial plants, mirrors, clocks, paintings and photo
frames. The store managers are responsible for the purchases and
they deal directly with the suppliers. Once purchases are made,
these items are then delivered by the suppliers directly to the
stores.
While all brands are displayed in the shopfront of each store,
each store also has a storeroom to keep extra stock (for example,
items in different sizes, shapes and colours). In the storerooms,
each brand of item is stored in distinct spots and shelf locations.
Items are delivered using various mediums including ship, air,
rail, and courier van. Sandy would like the database management
system to be able to collect data and produce business reports that
could support her and her employees for their business
decision-makings. The database should be able to encapsulate
current sales, invoice details, customer profiles, supplier
details, shipping details, item details, and employee details.
To date, Sandy has managed this all herself using Google Sheets
and emails to keep track of all of the details. However, as her
business has expanded, it has become difficult to manage everything
and extract valuable insights about her business. As such, she
wants a dedicated database management system built specifically to
her needs, which has the capability of capturing all of the current
and future data associated with the business. She has contracted
Best Innovative Solutions (BIS) Pty. Ltd. to carry out this work,
with you assigned to design and build a database that meets her
specification.
Assume that you are employed as a business analyst at Best
Innovative Solution (BIS) Pty Ltd
Question: Create 3 queries that have the following
requirements. Note that each of the query can cover one or more
of
the requirements.
o One of the 3 queries should have a calculation.
o One of the 3 queries should make use of Group By.
o One of the 3 queries should include a scalar function (one that
returns
a value).
o One of the 3 queries should demonstrate Nested Query (could
be
either standard or correlated sub-query).
o One of the 3 queries should demonstrate joining tables.
o One of the 3 queries should demonstrate the creation and testing
of Trigger with test data.
For each query,
o Explain and justify its business purpose and business value or
impact.
o Provide the SQL script.
o Explain the design of SQL script.
o Make use of a table(s) with sample data to show the potential
result/
outcome of the query.