Database design and implementation

BodyCare Case Study

Posted on December 10, 2020

1. Introduction

In this project we will revise some of the key concepts in data modelling and database architecture. The topics covered include relational database modelling, Entity Relationship diagrams for the conceptual and logical designs of a database, the SQL language and its use for creating, reading, updating, deleting (CRUDE) information to and from a database.

Our case study is based on BodyCare Ltd (BCL), a company that specialises in supplying beauty and fragrance boxed gift sets in the UK. We will have to carefully analyse BCL's requirements for the design of a conceptual model and in the mapping process to create a logical model for the database application.

At the end of this case study we will have:

  • Designed a data model which conceptually captures the key architecture of a database;
  • Revised the conceptual data model and mapped it into a logical diagram ready to be implemented;
  • Used the SQL language for basic database implementation;

TABLE OF CONTENTS

  1. Introduction
  2. Case study guidelines
  3. Entity Relationship Diagram
  4. Primary and foreign keys
  5. Relationships assumptions
  6. SQL tables creation
  7. Inserting sample data
  8. Sample queries

2. Case study guidelines

Following are the guidelines provided for this scope:

BodyCare Ltd (BCL) specialises in supplying quality beauty & fragrance boxed gift sets for men and women in the UK. Its reputation for using only the best products has resulted in rapid expansion in sales over the last three years.

Skin care sets, make-up sets, fragrance sets, hair care sets, etc. are sourced from several suppliers who deliver goods to BCL’s warehouse in Uxbridge where the sets are assembled to order by a staff of 10. A standard range of around 50 sets is offered, but every so often a new set is produced for a special occasion. The marketing department is always on the lookout for new ideas and business opportunities. The company also has a policy of donating 2% of its annual total sales value to local charities.

Each product making up a set is supplied by a single supplier and is given a unique product number and description by BCL. Products are always sold as part of a set, never on their own. A minimum stock level is recorded for each product and as soon as the stock falls below this, a purchase order is raised to replenish stock.

The sets are sold via a team of salesmen who are paid a nominal salary with the opportunity to increase their earnings from commissions on sales. Commission is paid at 10% of the value of a set, but then goes up to 20% after a salesman’s annual sales target is reached.

The full list of sets offered is contained in the company’s catalogue. Prices range from the ‘Deluxe Recreation’ priced at £650 to the more affordable ‘Regular Care’ set priced at £25.99. Each set has an imaginative description to entice customers to part with their money. The bulk of BCL customers are corporate clients who buy the gift sets as presents for their employees and clients. There is also good demand from young professionals who just fancy splashing out from time to time. The business is enjoying rapid growth and the current system of recording everything on Excel Spreadsheets is proving time consuming and leading to errors.

The owners are thinking of setting up an integrated sales order processing system but would like to do it in stages. The first stage will be a Customer Order Processing system as a ‘proof of concept’. This will enable the company to keep a record of customer details and sales for promotional purposes as well as to identify ‘high spenders’. If that proves successful than the supplier side too will be added later on. Payments are handled through PayPal due to security and fraud concerns and no changes are planned here.

The guidelines offered are not 100% clear. In a real-life scenario we should communicate with the relevant stakeholders to fully understand the needs and the use cases of the newly to be implemented system. Communication is the key to any successful project development, because it is a process of transmitting ideas and information.

Tasks details

  • We are required to produce an Entity Relationship Diagram using UML notation.
  • The ERD should represent customer order processing only.
  • For each of the entities in our model, we should clearly identify appropriate attributes and relevant keys.
  • Relationships between entities should be clearly identified and labelled with any assumptions fully documented.
  • We are also required to implement our design as a relational database using MySQL database by creating appropriate SQL scripts and executing them.
  • We should populate our tables with sufficient data to demonstrate execution of four relevant and useful queries.

Let's go!

3. Entity Relationship Diagram

We have identified five key entities: Agent, Customer, Giftset, Sale and Product. It is important to notice that the ERD should represents customer order processing only. The Agent entity in not necessary in the context of this case study, although it would give off a better starting point in the case of an actual implementation of the database. Furthermore, it will be used at a later stage for the purpose of calculating commission on sales.

Fig. 1 - Customer order processing ERD

In order to resolve the many to many relationships we have created the Sale_Detail and Gifteset_Product entities. We have also included what would be the attributes for a basic build.

The diagram has been designed with diagrams.net, an open source technology stack for building diagramming applications.

4. Primary and Foreign keys

We can refer to the table below for the relevant keys identified in each of the entities in the model.

Entity Primary Key Foreign Key Rationale for Primary Key
Agent agentId N/A agentId is unique to each Agent.
Customer custId agentId custId is uniquely assigned to a Customer.
Sale saleRefNo custId saleRefNo is a unique identifier for each Sale. PayPalTransId is a possible alternative but it is dependent on another service.
Giftset gsetId N/A gsetId is unique for every Giftset. gsetName is also unique but could have issues scaling up.
Product prodNo N/A prodNo is unique to each Product.
Sale_Detail saleRefNo, gsetId saleRefNo, gsetId Sale_Detail is a link entity; a minimum PK is a composite key made up of parent PKs as well as being FKs.
Giftset_Product gsetId, prodNo gsetId, prodNo Giftset_Product is a link entity; a minimum PK is a composite key made up of parent PKs as well as being FKs.

5. Relationships assumptions

One last step before moving on to the SQL implementation will be to outline the assumptions made, in terms of participation and multiplicity, between our entities:

  • Agent - Customer
    One agent can represent no customer. In the case of a newly hired agent or in the case of a temporarily leave there could be no customers represented by the agent. One agent can represent many customers. It is not feasible to have one agent represent only one customer. One customer can have no agent representing them. It could be that the customer has made an order through the online shop, in that case no agent is involved. One customer must be represented by at most one agent. It is company policy that a customer must not have more than one agent representing them, this has to do with how commission is applied.
  • Customer - Sale
    One customer could not make a sale. It could be that the customer has registered on the online shop but has not made a sale yet. One customer can make many sales. It would not be a profitable business model if each customer were allowed at most one sale. A sale needs to be made by at least one customer. If a sale is not made by a customer there would be no sale to begin with. A sale must be made by at most one customer. It is company policy that a sale can be made by one and only one customer to avoid any sorts of issues that may arise.
  • Sale - Giftset
    One sale must contain at least one giftset. In the case of a sale containing no giftsets there would be no sale. One sale can have many giftsets. It is possible and encouraged by BodyCare Ltd. to have more giftsets ordered for the same sale, this is to reduce delivery costs and simplify the logistics. One giftset could be in no sale. It could be that a giftset has not been ordered in any sale yet, for example in the case of a brand-new giftset. One giftset could be included in many sales. A particular type of giftset could be ordered by different customers. Sale_Detail is the link entity used to resolve this many to many relationship.
  • Giftset - Product
    One giftset needs to contain at least one product. Products are not sold stand alone but only as part of a giftset. One giftset could contain many products. A giftset is by definition a set of products packaged together for individual sale. One product could not be part of a giftset. It could be that the product has not been made part of a giftset yet, for example in the case of a new product. One product can make up many giftset. The business operates so that products are included in the same type of giftset and the same product could also be shared by different giftsets. Giftset_Product is the link entity used to resolve this many to many relationship.

6. SQL Tables creation

Let' now see the SQL scripts used for the creation on the relational Database. I used phpMyAdmin, a free and open source administration tool for MySQL and MariaDB, running in a Docker container. The official image can be found here.

CREATE TABLE IF NOT EXISTS Agent 
(
  agentId           VARCHAR(4),
  agentFullName     VARCHAR(100) NOT NULL,
  agentSalary       DECIMAL(8, 2) NOT NULL, 
  agentCommission   DECIMAL(7, 2),
  annualSalesTarget DECIMAL(7, 2) NOT NULL,
  CONSTRAINT a_ano_pk PRIMARY KEY (agentId)
);

Fig. 2 - Agent table structure on phpMyAdmin

CREATE TABLE IF NOT EXISTS Customer
( 
  custId            VARCHAR(5),
  custFullName      VARCHAR(100) NOT NULL,
  custTelNo         VARCHAR(20),
  custEmail         VARCHAR(100) UNIQUE NOT NULL,
  custAddress       VARCHAR(200) NOT NULL,    
  custPostCode      VARCHAR(10) NOT NULL,
  custCity          VARCHAR(50) NOT NULL,
  agentId           VARCHAR(4),
  CONSTRAINT c_cid_pk PRIMARY KEY (custId),
  CONSTRAINT c_aid_fk FOREIGN KEY (agentId)
  REFERENCES Agent (agentId)
);

Fig. 3 - Customer table structure on phpMyAdmin

CREATE TABLE IF NOT EXISTS Sale
(
  saleRefNo         INTEGER(7), 
  saleDate          DATE NOT NULL,
  saleTotal         DECIMAL(7, 2) NOT NULL, 
  deliveryDate      DATE,
  PayPalTransId     VARCHAR(9) NOT NULL,
  custId            VARCHAR(5) NOT NULL,
  CONSTRAINT s_srno_pk PRIMARY KEY (saleRefNo),
  CONSTRAINT s_cid_fk FOREIGN KEY (custId)
  REFERENCES Customer (custId)
);

Fig. 4 - Sale table structure on phpMyAdmin

CREATE TABLE IF NOT EXISTS Giftset
(
  gsetId            VARCHAR(5),
  gsetName          VARCHAR(100) UNIQUE NOT NULL,
  gsetPrice         DECIMAL(6, 2) NOT NULL,
  gsetDesc          VARCHAR(500) NOT NULL,
  isLimited         BOOLEAN,
  CONSTRAINT gs_gsid_pk PRIMARY KEY (gsetId)
);

Fig. 5 - Gifset table structure on phpMyAdmin

CREATE TABLE IF NOT EXISTS Product
(
  prodNo            INTEGER(7),    
  prodName          VARCHAR(100) NOT NULL,
  prodUnitPrice     DECIMAL(6, 2) NOT NULL,     
  prodStockLevel    INTEGER(5),
  prodDesc          VARCHAR(500),
  CONSTRAINT p_pno_pk PRIMARY KEY (prodNo)
);

Fig. 6 - Product table structure on phpMyAdmin

CREATE TABLE IF NOT EXISTS Giftset_Product
(
  gsetId            VARCHAR(5) NOT NULL,
  prodNo            INTEGER(7) NOT NULL,
  prodQuantity      INTEGER(2) NOT NULL,
  CONSTRAINT gsp_gsidpno_pk PRIMARY KEY (gsetId, prodNo),
  CONSTRAINT gsp_gsid_fk FOREIGN KEY (gsetId)
  REFERENCES Giftset (gsetId),
  CONSTRAINT gsp_pno_fk FOREIGN KEY (prodNo)
  REFERENCES Product (prodNo)  
);

Fig. 7 - Giftset_Product table structure on phpMyAdmin

CREATE TABLE IF NOT EXISTS Sale_Detail
(
  saleRefNo         INTEGER(7) NOT NULL, 
  gsetId            VARCHAR(5) NOT NULL,
  gsetQuantity      INTEGER(3) NOT NULL,
  CONSTRAINT sd_srnogsid_pk PRIMARY KEY (saleRefNo, gsetId),
  CONSTRAINT sd_srno_fk FOREIGN KEY (saleRefNo)
  REFERENCES Sale (saleRefNo),
  CONSTRAINT sd_gsid_fk FOREIGN KEY (gsetId)
  REFERENCES Giftset (gsetId)
);

Fig. 8 - Sale_Detail table structure on phpMyAdmin

Our tables are ready to be tested, we will need to populate them with some sample data and then we will be able to try some queries out.

7. Inserting sample data

Following is the sample data used in the implementation of this model. Some data has been randomly genarated while some was scraped off different retail websites.

INSERT INTO Agent 
  (agentId, agentFullName, agentSalary, agentCommission, annualSalesTarget) 
VALUES
  ('A034', 'Anton Gutierrez', 22500.00, 0, 1000.00),
  ('R133', 'Reginald Sadler', 35000.00, 2100.32, 2000.00),
  ('Y011', 'Yaqub Moreno', 22500.77, 0, 1000.00),
  ('D057', 'Derrick Blackmore', 25000.00, 132.71, 10000.00),
  ('G101', 'Gordon Velazquez', 33650.00, 0, 1800.00);

Click here to see the rest of the INSERT statements 
INSERT INTO Agent 
      (agentId, agentFullName, agentSalary, agentCommission, annualSalesTarget) 
    VALUES
      ('A034', 'Anton Gutierrez', 22500.00, 0, 1000.00),
      ('R133', 'Reginald Sadler', 35000.00, 2100.32, 2000.00),
      ('Y011', 'Yaqub Moreno', 22500.77, 0, 1000.00),
      ('D057', 'Derrick Blackmore', 25000.00, 132.71, 10000.00),
      ('G101', 'Gordon Velazquez', 33650.00, 0, 1800.00);
    
    INSERT INTO Customer 
      (custId, custFullName, custTelNo, custEmail, custAddress, custPostCode, custCity, agentId) 
    VALUES
      ('0A131', 'Ivor Sharpe', '01905450717', '7bbr28886@liadisma.cf', '1 Birchwood Way', 'AL2 2SE', 'Lyndhurst', 'A034'),
      ('1B556', 'Rikki Ratliff', '+447461044821', 'laldom@usapodcasd.com', '5 Primley Gardens', 'EX10 9LE', 'Sidmouth', 'G101'),
      ('0C001', 'Yusha Hester', '01539-491043', 'yusha.he@googleappmail.com', '51 Well Lane', 'L16 5ET', 'Childwall', 'A034'),
      ('1A945', 'Jagoda Gregory', '0750 4961742', 'qthinkerh@qzick.com', '9 Gerald Road', 'BN11 5QG', 'Worthing', NULL),
      ('0L990', 'Montell Lake', '01 6702 06586', 'montell@getthemp3.com', 'Flat 20, George Belt House, Smart Street', 'RM2 6BU', 'Romford', 'R133'),
      ('0V321', 'Patryk Lowry', '07863233367', 'gcameron.ransom.j@fridaymovo.com', '15 Chalforde Gardens', 'SA73 3QZ', 'Nailsea', 'R133'),
      ('1V478', 'Rhonda West', '01672 267 995', 'rhmad.hawlery.7h@tol.net', '221 Eccles Old Road', 'M6 8JH', 'Salford', NULL),
      ('0P55A', 'Viola Ayers', '+44797 8107958', 'viola@bookabus.org', 'West View, Reddings Lane', 'B46 2DN', 'Nether Whitacre', 'Y011'),
      ('1D100', 'Alister McDaniel', '01297-311561', 'amd@walmartnet.com', '15 Co Operative Row', 'NN10 0RJ', 'Rushden', 'R133'),
      ('0J867', 'Salahuddin Mercado', '07 753233837', 'salah@dpics.fun', '47 Holtwood Drive', 'PL21 9TH','Ivybridge', NULL);
    
    INSERT INTO Sale 
      (saleRefNo, saleDate, saleTotal, deliveryDate, PayPalTransId, custId) 
    VALUES
      (1003323, '2020-02-25', 425.97, '2020-02-28', 'TA4387247', '0A131'),
      (1006326, '2018-03-05', 849.99, '2018-03-08', 'TB8743293', '1B556'),
      (1007329, '2018-05-08', 761.98, '2018-05-11', 'TC3874293', '1A945'),
      (1002312, '2020-06-23', 541.98, '2020-06-26', 'TD9384209', '1B556'),
      (1004425, '2018-08-05', 1011.50, '2018-08-08', 'TE8349248', '1V478'),
      (1009928, '2020-09-20', 596.74, '2020-09-23', 'TF3492484', '0P55A'),
      (1007834, '2019-03-05', 927.97, '2019-03-08', 'TG3482394', '0L990'),
      (1009926, '2020-02-13', 650.00, '2020-02-16', 'TH3884334', '0V321'),
      (1004072, '2018-08-21', 431.65, '2018-08-24', 'TI8372947', '1D100'),
      (1002008, '2018-10-12', 574.05, '2018-10-15', 'TJ3984209', '0J867'),
      (1007799, '2018-01-08', 887.50, '2018-01-12', 'TK8237492', '0A131'),
      (1002127, '2018-10-09', 468.98, '2018-10-13', 'TL3240922', '1D100'),
      (1003582, '2018-02-18', 235.99, '2018-02-21', 'TM3948023', '0C001'),
      (1001169, '2018-09-15', 148.44, '2018-09-18', 'TN2394803', '0L990'),
      (1003187, '2018-10-17', 678.87, '2018-10-20', 'TO3924809', '0A131'),
      (1009090, '2020-03-02', 311.98, '2020-03-05', 'TP3240932', '1V478'),
      (1002221, '2018-02-07', 564.75, '2018-02-14', 'TQ2394849', '0V321'),
      (1003781, '2019-08-21', 68.86, '2019-08-25','TR3294820', '0P55A'),
      (1009903, '2019-08-30', 189.75, '2019-09-05', 'TS3942802', '1A945'),
      (1000102, '2018-09-27', 214.68, '2018-09-30', 'TT3294843', '0C001');
    
    INSERT INTO Giftset 
      (gsetId, gsetName, gsetPrice, gsetDesc, isLimited) 
    VALUES
      ('PB222', 'Protect + Hydrate Skincare Collection', 199.99, 'Powerful protection. Intense, refreshing 24-hour hydration. Discover these high-performance formulas to defend against signs of premature aging and diminish their appearance.', False),
      ('PC435', 'Deluxe Recreation', 650.00, 'Discover the most amazing products for health and wellbeing.', False),
      ('LB278', 'Provence - Hand Cream Trio Gift Set', 566.00, 'This gift contains three of our most-loved hand creams, perfect for treating your hands & nails on the go.', True),
      ('PC515', 'High Impact Favourites Eye Essentials Makeup', 459.99, 'Bold eye essentials, plus our #1 makeup remover. What It Does: Ramp up the drama with a get-noticed eye look. Full-size High Impact Mascara kicks up the volume and length of each and every lash.', False),
      ('BB669', 'Firm + Glow Skincare Collection Gift Set', 385.99, 'Help unlock your skin s youth potential. Discover these high-performance formulas to deeply nourish skin and reduce the look of lines and wrinkles. Skin feels firmer, its density and elasticity significantly improved.', False),
      ('CB555', 'Eau Dynamisante Collection', 155.99, 'Vitality, freshness, firmness. Clarins Eau Dynamisante is the first fragrance to combine the benefits of aromatherapy and skin care by harnessing the natural aromas and active ingredients of plants.', False),
      ('LB385', 'Pure Color Envy Lip Luxuries', 375.99, 'For lips to envy, baby them with Pure Colour Envy Lip Care. This lip spa duo includes 2 must-haves to hydrate lips with a personalised tint, plus soothe and soften lips every night.', True),
      ('CP819', 'Regular Care', 25.99, 'An essential for daily body care.', False),
      ('PP257', 'No7 Men Energising Collection', 40.30, 'This collection contains: No7 Men Energising Face, Beard & Stubbble Moisturiser SPF15 50ml No7 Men Energising Gradual Tan Moisturiser 50ml No7 Men Energising Face Wash 150ml No7 Men Energising Hair & Bodywash 200ml', False),
      ('LL841', 'Working Hands & Healthy Feet', 151.07, 'The highly-concentrated formulations have been developed to provide both instant relief and long-lasting results. And this multipack has everything you need.', True),
      ('LL686', 'Men Anti Ageing Collection', 210.75, "As men's skin ages, it's structure is weakened, resulting in lines, wrinkles and a dull appearance. The renowned Protect & Perfect Intense technology has now been re-energised to deliver even better anti-ageing results.", True),
      ('PP858', 'Grooming Wash Bag', 46.75, 'This fragrance is an invigorating citrus herbal blend, with amber and musk.', False),
      ('BL135', 'Whiskey Cooling Stones Kit', 11.05, 'Whiskey Stones are a great alternative to ice.  Drop them in your whiskey, or any tipple, to chill your drink without diluting.  Made from 100% Soapstone, which harbours less bacteria and fewer impurities than ice. These whiskey stones can be reused time and time again, just wash and freeze again in their bag.', False),
      ('PB345', 'Dyson Airwrap styler Complete', 455.44, 'The complete set of stylers from the Dyson collection. Reach new lenghts!', False),
      ('PC736', 'Bellamianta Bella Babes', 235.24, 'Apply using your luxurious Bellamianta tanning mitt in circular motions.', False),
      ('PB485', 'The Skin Savers Edit', 120.50, 'Leaving no stone unturned on our quest to discover the grooming world’s latest and greatest, we trawl the globe to curate our incredible edit of results-driven heroes. Housed in an eye-catching, 20s-inspired gift box that unites 10 ‘Holy Grail’ heroes, The Skin Savers Edit boasts an array of potent, results-driven must-haves, from a fan favourite cleanser to an A-List-approved targeted treatments', False),
      ('CL230', 'Pillow Talk Lip Secrets', 38.2, "Make up artists don’t come much more legendary than Charlotte Tilbury; with twenty years in the industry, she’s a true trailblazer – creating looks for some of film and fashion’s most iconic faces. Epitomising modern glamour, her eponymous range stars high-performance formulas, luxury make up bags, daring eyeshadow palettes and the most exquisite, boudoir-worthy lipsticks. It's stylish, decadent and fabulously feminine.", False),
      ('BC658', 'Vegan Brush Collection', 79.67, 'Worshipped for their incandescent highlighters, buttery blushers, ultra-soft make up brushes and refillable lipsticks, Hourglass combines science, luxury and beauty to create the most magnificent make up. Blending ground-breaking active ingredients with innovative technology, the products facilitate seamlessly beautiful, ‘second skin’ finishes – and the sleek, chic packaging is yet another reason to adore it.', False),
      ('CL686', 'Rae Morris Personal Set', 381.67, 'One of the most influential make up artists in the world, Rae Morris has written the book on beauty (in fact, she’s written several of them…) Having perfected endless famous faces, she turned her hand to creating the most luxurious and effective brushes money can buy – and the results will change the way you apply make up forever.', False),
      ('LP692', 'GloPRO Holographic Set', 152.57, 'An all-in-one bundle to keep you glowing through the season, this kit includes: a limited edition, holographic GloPRO Microneedling Regeneration Tool, a Sanitizing Spritzer Bottle, a GloPRO BODY MicroTip Attachment Head, five GloPRO Skin Prep Pads, The Sculptor Skin Firming Body Cream and RADIANCE Brightening Facial Oil.', True);
    
    INSERT INTO Product 
      (prodNo, prodName, prodUnitPrice, prodStockLevel, prodDesc)
    VALUES
      (1, 'Pro-Collagen Spf 30 Anti-Wrinkle Day Cream', 43.46, 133, 'This clinically proven formulation blends a powerful combination of ingredients, including padina pavonica and Ginkgo Biloba, shown to reduce the appearance of fine lines and wrinkles in 14 days'),
      (2, 'Face 810 Facial Epilator', 26.99, 158, 'Epilator for face, body & leg .Never wait again for your body hair to grow so that wax can get it.'),
      (3, 'Advanced Ceramide Capsules Daily Youth Restoring Serum', 53.35, 129, "Experience a triple-powered capsule of youth that focuses on enhancing skins' barrier repair function, increasing moisturization within the surface of the skin and minimize the appearance of aging signs."),
      (4, 'Associates Discovery Wellbeing Miniature Bath & Shower Oil', 5.88, 344, 'Artisans in the field of Aromatherapy, we harness the incredible powers of the finest essential oils, purest extracts and natural ingredients to include in our award-winning products.'),
      (5, 'Obsession for Women Eau de Parfum', 79.88, 12, 'Obsession for Women Eau de Parfum is made up of a provocative fragrance that returns us to an era of sensual femininity. A bold, aromatic scent that empowers with refreshing mandarin bergamot.'),
      (6, 'Chloe Eau De Parfum For Women', 85.05, 14, 'This amber floral is meant to embody the classic scent. It features a bouquet of powdered florals composed of peony, lychée and freesia.'),
      (7, 'Bluesky Gel Nail Polish Beauty Advent Calendar', 55.71, 88, 'All I want for Christmas is.... The Advent Calendar! With 25 days of gel polish & Nail accessories to keep your nails looking fabulous for the party season!'),
      (8, 'UniqONE Professional Hair Treatment', 8.49, 331, 'The first treatment of its kind to launch into the Professional Haircare market. The unique solution that women have been looking for. When applied to damp hair it gives smooth, soft, shiny, hair without weight.'),
      (9, 'Coconut Miracle Oil Conditioner for Damaged', 7.99, 371 ,'Wishes can come true with this Tahitian inspired extra strength reviving conditioner. Help transform dry course hair into princess perfection with this ultra-rich blend with Coconut Oil, Vanilla Bean extract and essence of Tiare – a tropical flower from Tahiti.'),
      (10, 'Hair Mask with Keratin and Milk Protein for Dry, Damaged and Chemically Treated Hair', 6.15, 0, 'Special mask / conditioner with keratin for treated hair.'),
      (11, 'Honey Bath', 42.00, 66, 'The über-luxurious Honey Bath (complete with your very own wooden honey dipper) is renowned as the ultimate calm-restorer, lulling you into a state of blissful relaxation while protecting skin and balancing moisture levels. Sweet almond proteins, hydrolyzed rice, and oat proteins along with pro-vitamin B-5 offer strengthen skin while enhancing hydration. Perfect as a pick-me-up treat or as a present for your favourite boujee bath lover and get ready for some serious R&R.'),
      (12, 'Multi-Miracle Glow', 10.03, 192, "This Multi Miracle Glow balm is the ultimate does-everything skin care essential. Enriched with nourishing, organic ingredients, it makes a sensuous (effortless) cleansing balm, unctuous hydrating treatment and intensive, overnight mask to replenish moisture and recover skin's glow."),
      (13, 'Mini Body Souffle Quartet', 40.88, 125, "This strictly limited edition Mini Body Souffle Quartet unites of four of the brand's iconic body creams for sumptuous self-care Sundays."),
      (14, 'Biggie Biggie Bum Bum Cream', 72.44, 165, 'Pronounced ‘boom boom’ (who knew?), Sol de Janeiro’s best-smelling body cream defies succinct description. The scent is sensational – like holidays and piña coladas and coconut rolled into one gorgeous cocktail of scrumptiousness (yum!). Totally intoxicating, this limited edition, ‘Biggie Biggie’ tub is the perfect gift for all those in need of some sunshine-inspired body care (say “aye!”); this potion is Copacabana distilled into one mega, marigold pot.'),
      (15, 'The Cream', 205.12, 86, 'Needing no introduction, Augustinus Bader’s potent potions are your one-stop, skin-transforming shop! Harnessing cutting-edge stem cell research to repair signs of damage, these do-it-all formulas work overtime to revitalise skin that’s in need of a jump-start. Developed by pioneering stem cell and biomedical scientist Professor Augustinus Bader, The Cream and The Rich Cream are powdered by his patented Trigger Factor Complex (TFC8) – a ground-breaking compound that works to ‘unlock’ skin’s innate code and activate regeneration.');
    
    INSERT INTO Giftset_Product
      (gsetId, prodNo, prodQuantity)
    VALUES
      ('PB222', 1, 1),
      ('PB222', 2, 1),
      ('PB222', 3, 1),
      ('PB345', 11, 1),
      ('PB345', 5, 1),
      ('BB669', 12, 2),
      ('PC435', 13, 1),
      ('PC435', 14, 1),
      ('PC435', 15, 1),
      ('LL686', 3, 1),
      ('LL686', 1, 1);
    
    INSERT INTO Sale_Detail
      (saleRefNo, gsetId, gsetQuantity)
    VALUES 
      (1000102, 'PB222', 2),
      (1000102, 'CP819', 1),
      (1003323, 'CB555', 1),
      (1003323, 'LB385', 1),
      (1006326, 'PC435', 2),
      (1006326, 'PB222', 1),
      (1007329, 'LB385', 1),
      (1007329, 'BB669', 1),
      (1002312, 'CB555', 1),
      (1002312, 'BB669', 1),
      (1004425, 'PB485', 3),
      (1004425, 'PC435', 1),
      (1009928, 'BB669', 1),
      (1009928, 'LL686', 1),
      (1007834, 'CB555', 1),
      (1007834, 'BB669', 2),
      (1009926, 'PC435', 1);

Fig. 9 - Populated Agent table
Fig. 10 - Populated Customer table
Fig. 11 - Populated Sale table
Fig. 12 - Populated Giftset table
Fig. 13 - Populated Product table
Fig. 14 - Populated Sale_Detail table
Fig. 15 - Populated Giftset_Product table

8. Sample queries

Finally, we can use some queries to test out the database.

Query 1: Find low in stock

Display a list of product numbers, product names and stock level for those products for which the stock level is less than 30. This query should be used to check if a product has reached its minimum stock level, if it has, a purchase order will be raised to replenish stock.

SELECT prodNo, prodName, prodStockLevel AS 'In Stock'
FROM Product
WHERE prodStockLevel < 30;

Fig. 16 - Output of Query 1

Query 2: Populate catalogue

Display giftset names, prices and descriptions for all giftsets. This query is used to retrieve all giftsets in order to populate the company’s catalogue.

SELECT gsetName, gsetPrice, gsetDesc
FROM Giftset
ORDER BY gsetName;

Fig. 17 - Output of Query 2

Query 3: Charity donations calculator

Display the total amount of money to donate to charity for each year ordered by year. BodyCare Ltd. has a policy of donating 2% of its annual total sales value to local charities.

SELECT YEAR(saleDate) AS Year, SUM(saleTotal), 
  ROUND(SUM(saleTotal)*2/100, 2) AS 'For charity'
FROM Sale
GROUP BY Year
ORDER BY Year;

Fig. 18 - Output of Query 3

Query 4: Commission for sale calculator

Calculate the commission on sale for sale number 1007834. Commission is paid at 10% of the value of a set, but then goes up to 20% after a agent’s annual sales target is reached.

SELECT s.saleRefNo, s.saleDate, a.agentFullName, IF (a.agentCommission > a.annualSalesTarget, SUM(s.saleTotal)*0.2, SUM(s.saleTotal)*0.1) AS 'Commission'
FROM Agent AS a
JOIN Customer AS c ON a.agentId = c.agentId
JOIN Sale AS s ON c.custId = s.custId
GROUP BY s.saleRefNo
HAVING s.saleRefNo = 1007834;

Fig. 19 - Output of Query 4

We have gone a long way, from specifications and reqirements to a functional database architecture. It is now up to potential investors, managers, and other stakeholders to see wether the project has enough potential to be fully developed.

We have revised some key concepts in database modelling, architecture and implementation. Hope you found it useful and enjoyed the journey!