ISYS2038 Database Design and Development Semester |Individual Assignment Case Study Just Decorate Pty. Ltd.

 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 Chad-stone, 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 focusing 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 colors). 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.

Question 1 Data Modelling

  • Provide an Entity Relationship Diagram (ERD). For each entity, provide at least one attributes/ fields with a maximum of two attributes (excluding primary key).
  • Provide a Relational Model and show the most appropriate attributes/ fields, primary keys and foreign keys. In addition, include the data type for each attribute/ field listed in each table/ entity. Apply third normal form (3NF) and show consistency in the use of notation.

Question 2 SQL Scripts

 Based on data modelling from Question 1, create 3 queries that have the following requirements. Note that each of the query can cover one or more of the requirements.
  • One of the 3 queries should have a calculation
  • One of the 3 queries should make use of Group By
  • One of the 3 queries should include a scalar function (one that returns a value)
  • One of the 3 queries should demonstrate Nested Query (could be either standard or correlated sub-query) 

For each query,

  • Explain and justify its business purpose
  • Provide the SQL script
  • Explain the design of SQL script
  • Make use of table(s) with sample data to show potential result/ outcome of query

SOLUTION:

 QUESTION 1

    ERD

 

 

RELATIONAL MODEL

.

Business Rules:

There are 4 stores, 50 staff, and 20 suppliers.
  1. A store manager can request and receive orders from any of the suppliers.
  2. All the 50 staff are employed in the 4 stores.
  3. Each store contains a storeroom where items are stored on the basis of feature, size, and color.
  4. Items are stored in distinct spots and shelf locations for fast retrieval.
  5. A customer must have purchased at least one product.
  6. Two copies of the invoice are generated, one for the customer and one for the store manager.
  7. The weight of a shipped product must not exceed 40 Kgs.
  8. A customer can replace a product within 7 days.
  9. A customer cannot get a refund after 2 days.

Relationship constraints:

  1. A supplier supplies many items.
  2. A item has only one supplier.
  3. A shipment ships many items.
  4. A item is shipped by one shipment.
  5. A customer can have many purchases.
  6. A purchase is done by one customer.
  7. A employee can have many sales.
  8. A sale is done by one employee.
  9. A invoice must contain one item.
  10. A sold item has one invoice.

Business Assumptions:

  1. A store should have supplies from atleast 5 suppliers.
  2. A supplier should supply atleast 3 stores.
  3. The invoice should contain the name of the salesman.
  4. The invoice should contain the time of payment  and payment type.
  5. A store should have at least 400 sales in a month.
  6. A store should have 100 regular customers.
  7. Some amount of discount can be given to new customers.
  8. The supplier must be informed of the quality of the supplied product.
  9. An item must be in good condition to be eligible for a refund.
  10. The shipment should be delivered within 3 working days.


QUESTION 2 SQL SCRIPTS

Query 1

Business Purpose:-
Count the items with price greater than 3000.

Query:
       SELECT COUNT(*) FROM ITEM WHERE PRICE>3000;
Query Design and Outcomes:
    The query fetches records of items in the table ‘Item’. A condition is added which queries only the items with a price greater than 3000.  The query result will contain one cell which contains the number of such items. With the help of such query in business, one can get the count the items which lies in a certain budget.

 

.

 Query 2
Business Purpose:-
    Find the total valuation of items supplied by each supplier.

Script :
       SELECT SUPPLIERNO, SUM(PRICE) FROM ITEM GROUP BY SUPPLIERNO;
Query design and outcome:
    The query fetches records of table Item. The records are grouped by Supplier using GROUP BY. After grouping, the SUM function is applied which calculates the sum of prices of items supplied by each supplier. So, the query result will contain the Supplier No and the total price of items of supplied by the supplier. In business, such type of query can be helpful in finding the valuation of a supplier and giving preferences and incentives accordingly.


Example:

 

.

 Query 3
Business Purpose:-
    Find the sales of items supplied by Supplier with Supplier No 1234.

 Script :
       SELECT SALENO, PAYMENT_TYPE FROM SALE WHERE ITEMNO IN (SELECT ITEMNO FROM ITEM WHERE SUPPLIERNO=1234);

Query design and outcome:-
    The query fetches the records of table Sales. The query only fetches the sale details of items that are supplied by the given supplier. A correlated query is used to achieve this result. The inner query fetches all the items which are supplier by the given supplier. The outer query then filters out the sales which have the sale details of that item. In business, this query can help in maintaining the quality of items supplied by a supplier or for releasing commissions and bonuses to the supplier.

Example:-

 

 

Share:

No comments:

Post a Comment