1 / 47

Frosted

Ian Bickel AJ Rehn Lacey Williams Janice Barlow. Frosted. Stay Frosty!. Freedom to create the perfect tasting cupcake through our unique creation and decoration process. What is Frosted?. Self-serve Cupcakery. Floor Plan. Retail Floor. Bakery.

pelham
Download Presentation

Frosted

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Ian Bickel AJ Rehn Lacey Williams Janice Barlow Frosted Stay Frosty!

  2. Freedom to create the perfect tasting cupcake through our unique creation and decoration process. What is Frosted? Self-serve Cupcakery

  3. Floor Plan Retail Floor Bakery

  4. We offer a relaxed, modern and social environment that boosts creativity through sweet treats; specifically, cupcakes.  We strive to create a positive atmosphere to ensure the happiness, safety and overall satisfaction of our valued customers, employees and everyone in our Frosted family. Project Scenario

  5. Our largest objective is to create a database that does not include data redundancies, maintains its data integrity, and includes all necessary entities in their most simplified normalized form. Objectives

  6. The process of creating a self-serve cupcake includes: Selecting cupcake batter flavor Using automated frosting machines Selecting favorite toppings Purchasing your cupcake creation at our checkout counter Decoration Process

  7. Seven Tables of Frosted: Employee – to maintain our organizational structure Customer – to keep track of our customers Order – to link our customers with their orders OrderProduct – to determine quantity of products in each order Product – to keep track descriptions and selling costs of products Supply – to keep track of which products come from which vendors Vendor – to store vendor name and locations SDLC: Analysis

  8. Relational DiagramSDLC: Design

  9. Normalized Dependency DiagramSDLC: Design

  10. Normalized Dependency DiagramSDLC: Design

  11. Normalized Dependency DiagramSDLC: Design

  12. Entity Relationship DiagramSDLC: Design

  13. Employee TableSDLC: Design

  14. Customer TableSDLC: Design

  15. Order TableSDLC: Design

  16. Product TableSDLC: Design

  17. OrderProduct TableSDLC: Design

  18. Vendor TableSDLC: Design

  19. Supply TableSDLC: Design

  20. SQL> DROP TABLE supply CASCADE CONSTRAINTS; Table dropped. SQL> DROP TABLE vendor CASCADE CONSTRAINTS; Table dropped. SQL> DROP TABLE orderproduct CASCADE CONSTRAINTS; Table dropped. SQL> DROP TABLE product CASCADE CONSTRAINTS; Table dropped. SQL> DROP TABLE orders CASCADE CONSTRAINTS; Table dropped. SQL> DROP TABLE customer CASCADE CONSTRAINTS; Table dropped. SQL> DROP TABLE employee CASCADE CONSTRAINTS; Table dropped. Drop TablesSDLC: Implementation

  21. SQL> CREATE TABLE EMPLOYEE 2 (Employee# NUMBER(3), 3 first_name VARCHAR2(15), 4 last_name VARCHAR2(20), 5 position VARCHAR2(15), 6 date_hired DATE, 7 constraint employee_emp#_pk PRIMARY KEY (employee#)); Table created. SQL> SQL> CREATE TABLE CUSTOMER 2 (customer# NUMBER(4), 3 first_name VARCHAR2(15), 4 last_name VARCHAR2(20), 5 address VARCHAR2(20), 6 city VARCHAR2(15), 7 state CHAR(2), Create TablesSDLC: Implementation

  22. 8 zip NUMBER(5), 9 phone NUMBER(10), 10 dob DATE, 11 constraint customer_cust#_pk PRIMARY KEY (customer#)); Table created. SQL> SQL> CREATE TABLE ORDERS 2 (order# NUMBER(4), 3 customer# NUMBER(4), 4 order_date DATE, 5 weight NUMBER(4), 6 employee# NUMBER(4), 7 constraint order_ord#_pk PRIMARY KEY (order#), 8 constraint order_cust#_fk FOREIGN KEY (customer#) 9 REFERENCES CUSTOMER (customer#), 10 constraint order_emp#_fk FOREIGN KEY (employee#) Create TablesSDLC: Implementation

  23. 11 REFERENCES EMPLOYEE (employee#)); Table created. SQL> SQL> SQL> CREATE TABLE PRODUCT 2 (product# NUMBER(2), 3 retail_cost NUMBER(6,2), 4 description VARCHAR2(40), 5 constraint product_prod#_pk PRIMARY KEY (product#)); Table created. SQL> SQL> SQL> CREATE TABLE ORDERPRODUCT 2 (order# NUMBER(4), 3 product# NUMBER(2), Create TablesSDLC: Implementation

  24. 4 quantity NUMBER(2), 5 constraint orderproduct_product2#_fk FOREIGN KEY (product#) 6 REFERENCES PRODUCT (product#), 7 constraint orderproduct_ord#prod#_pk PRIMARY KEY (order#, product#)); Table created. SQL> SQL> CREATE TABLE VENDOR 2 (vendor# NUMBER(2), 3 vendor_name VARCHAR2(25), 4 address VARCHAR2(25), 5 city VARCHAR2(25), 6 state CHAR(2), 7 zip NUMBER(5), 8 constraint vendor_ven#_pk PRIMARY KEY (vendor#));  Table created. Create TablesSDLC: Implementation

  25. SQL> SQL> CREATE TABLE SUPPLY 2 (product# NUMBER(2), 3 vendor# NUMBER(2), 4 unit_cost NUMBER(6,2), 5 constraint supply_vendor#product#_pk PRIMARY KEY (vendor#, product#), 6 constraint supply_product1#_fk FOREIGN KEY (product#) 7 REFERENCES PRODUCT (product#), 8 constraint supply_vendor#_fk FOREIGN KEY (vendor#) 9 REFERENCES VENDOR (vendor#)); Table created. Create TablesSDLC: Implementation

  26. SQL> SQL> INSERT INTO employee 2 VALUES (101, 'IAN', 'BICKEL', 'JANITOR', '05-AUG-08'); 1 row created. SQL> SQL> INSERT INTO employee 2 VALUES (102, 'AJ', 'REHN', 'CEO', '12-MAY-07'); 1 row created. SQL> SQL> INSERT INTO employee 2 VALUES (103, 'JANICE', 'BARLOW', 'CASHIER', '21-APR-07'); 1 row created. SQL> SQL> INSERT INTO employee 2 VALUES (104, 'LACEY', 'WILLIAMS', 'BAKER', '12-MAY-07'); 1 row created. SQL> SQL> INSERT INTO employee 2 VALUES (105, 'JANCY', 'CHENSE', 'ENGINEER', '15-JAN-09'); 1 row created. SQL> SQL> SQL> INSERT INTO customer 2 VALUES (1001, 'CARSON', 'THOMPSON', '12 E PINE ST', 'SPOKANE', 'WA', 99202, 4255553424, '05-MAR-91'); Insert Rows Into TablesSDLC: Implementation

  27. 1 row created.  SQL> SQL> INSERT INTO customer 2 VALUES (1002, 'TAYLOR', 'PACE', '329 E MISSION AVE', 'SPOKANE', 'WA', 99202, 4255034470, '08-OCT-91'); 1 row created. SQL> SQL> INSERT INTO customer 2 VALUES (1003, 'KEANAN', 'BEATTY', '345 W MORMAN AVE', 'SALT LAKE CITY', 'UT', 84101, 8015189390, '06-JAN-92'); 1 row created. SQL> SQL> INSERT INTO customer 2 VALUES (1004, 'LIV', 'GROUT', '1030 E BOONE AVE', 'SPOKANE', 'WA', 99202, 4256790495, '07-OCT-91'); 1 row created. SQL> SQL> INSERT INTO customer 2 VALUES (1005, 'DYLAN', 'EMDE', '808 S PINEAPPLE LN', 'KAPAULA', 'HI', 96761, 8088088080, '03-AUG-91'); 1 row created. SQL> SQL> INSERT INTO customer 2 VALUES (1006, 'ALEX', 'MCKELVEY', '34 KITTY ST', 'SAN FRANCISCO', 'CA', 95070, 4083452345, '05-NOV-67'); 1 row created. SQL> Insert Rows Into TablesSDLC: Implementation

  28. SQL> INSERT INTO customer 2 VALUES (1007, 'COUNT', 'SANCHEZ', '345 34TH COURT', 'LOS ANGELES', 'CA', 95228, 6502345675, '06-JUN-92'); 1 row created. SQL> SQL> SQL> INSERT INTO orders 2 VALUES (1001, 1001, '12-AUG-12', 1.2, 103); 1 row created. SQL> SQL> INSERT INTO orders 2 VALUES (1002, 1006, '27-FEB-11', 2.3, 103); 1 row created. SQL> SQL> INSERT INTO orders 2 VALUES (1003, 1005, '11-MAY-08', 1.4, 103); 1 row created. SQL> SQL> INSERT INTO orders 2 VALUES (1004, 1007, '05-JUN-07', 1.4, 102); 1 row created. SQL> SQL> INSERT INTO orders 2 VALUES (1005, 1006, '17-OCT-09', 0.3, 103); 1 row created. Insert Rows Into TablesSDLC: Implementation

  29. SQL> SQL> INSERT INTO orders 2 VALUES (1006, 1002, '13-JAN-10', 0.5, 102);  1 row created. SQL> SQL> INSERT INTO orders 2 VALUES (1007, 1005, '15-FEB-12', 1.5, 103); 1 row created. SQL> SQL> INSERT INTO orders 2 VALUES (1008, 1001, '24-MAY-11', 3.1, 103); 1 row created. SQL> SQL> INSERT INTO orders 2 VALUES (1009, 1006, '04-JAN-09', 2.1, 103); 1 row created. SQL> SQL> SQL> SQL> SQL> INSERT INTO product 2 VALUES (01, '2.99', 'VANILLA CAKE'); 1 row created. Insert Rows Into TablesSDLC: Implementation

  30. SQL> SQL> INSERT INTO product 2 VALUES (02, '2.99', 'CHOCOLATE CAKE');  1 row created. SQL> SQL> INSERT INTO product 2 VALUES (03, '3.49', 'RED VELVET CAKE'); 1 row created. SQL> SQL> INSERT INTO product 2 VALUES (04, '4.99', 'BACON CAKE'); 1 row created. SQL> SQL> INSERT INTO product 2 VALUES (05, '3.99', 'PEANUT BUTTER CAKE'); 1 row created. SQL> SQL> INSERT INTO product 2 VALUES (06, '0.45', 'STRAWBERRY FROSTING'); 1 row created. SQL> SQL> INSERT INTO product 2 VALUES (07, '0.60', 'NUTELLA FROSTING'); 1 row created. Insert Rows Into TablesSDLC: Implementation

  31. SQL> SQL> INSERT INTO product 2 VALUES (08, '0.99','MAPLE FROSTING'); 1 row created. SQL> SQL> INSERT INTO product 2 VALUES (09, '0.99', 'TIGERS BLOOD FROSTING'); 1 row created. SQL> SQL> INSERT INTO product 2 VALUES (10, '0.20', 'PEPPERMINT TOPPINGS'); 1 row created. SQL> SQL> INSERT INTO product 2 VALUES (11, '0.30', 'BACON BITS'); 1 row created. SQL> SQL> INSERT INTO product 2 VALUES (12, '0.20', 'SPRINKLES'); 1 row created. SQL> SQL> INSERT INTO product 2 VALUES (13, '0.59', 'BANANAS'); 1 row created. Insert Rows Into TablesSDLC: Implementation

  32. SQL> SQL> SQL> INSERT INTO orderproduct 2 VALUES (1001, 05, 1); 1 row created. SQL> SQL> INSERT INTO orderproduct 2 VALUES (1001, 08, 1); 1 row created. SQL> SQL> INSERT INTO orderproduct 2 VALUES (1002, 01, 1); 1 row created. SQL> SQL> INSERT INTO orderproduct 2 VALUES (1002, 09, 1); 1 row created. SQL> SQL> INSERT INTO orderproduct 2 VALUES (1003, 13, 2); 1 row created. SQL> SQL> INSERT INTO orderproduct Insert Rows Into TablesSDLC: Implementation

  33. 2 VALUES (1004, 04, 1); 1 row created. SQL> SQL> INSERT INTO orderproduct 2 VALUES (1005, 08, 6); 1 row created. SQL> SQL> INSERT INTO orderproduct 2 VALUES (1006, 11, 1); 1 row created. SQL> SQL> INSERT INTO orderproduct 2 VALUES (1007, 02, 2); 1 row created. SQL> SQL> INSERT INTO orderproduct 2 VALUES (1008, 11, 2); 1 row created. SQL> SQL> INSERT INTO orderproduct 2 VALUES (1009, 13, 4); 1 row created. SQL> Insert Rows Into TablesSDLC: Implementation

  34. SQL> SQL> INSERT INTO vendor 2 VALUES (01, 'SHIBBY SPRINKLES', '123 GUMDROP LN', 'CANDYLAND', 'WA', 99207); 1 row created. SQL> SQL> INSERT INTO vendor 2 VALUES (02, 'SUGAR RUSHERS', '612 ROCK CANDY RD', 'LITTLE PINOCHE', 'CA', 95070); 1 row created. SQL> SQL> SQL> INSERT INTO supply 2 VALUES (01, 01, '0.99'); 1 row created. SQL> SQL> INSERT INTO supply 2 VALUES (02, 01, '0.99'); 1 row created. SQL> SQL> INSERT INTO supply 2 VALUES (03, 01, '1.49'); 1 row created. SQL> Insert Rows Into TablesSDLC: Implementation

  35. SQL> INSERT INTO supply 2 VALUES (04, 01, '2.99'); 1 row created. SQL> SQL> INSERT INTO supply 2 VALUES (05, 01, '1.99'); 1 row created. SQL> SQL> INSERT INTO supply 2 VALUES (06, 02, '0.23'); 1 row created. SQL> SQL> INSERT INTO supply 2 VALUES (07, 02, '0.30'); 1 row created. SQL> SQL> INSERT INTO supply 2 VALUES (08, 02, '0.50'); 1 row created. SQL> SQL> INSERT INTO supply 2 VALUES (09, 02, '0.50'); 1 row created. SQL> Insert Rows Into TablesSDLC: Implementation

  36. SQL> INSERT INTO supply 2 VALUES (10, 02, '0.10'); 1 row created. SQL> SQL> INSERT INTO supply 2 VALUES (11, 02, '0.15'); 1 row created. SQL> SQL> INSERT INTO supply 2 VALUES (12, 02, '0.10'); 1 row created. SQL> SQL> INSERT INTO supply 2 VALUES (13, 02, '0.30'); 1 row created. SQL> COMMIT; SQL> SQL> spool off Insert Rows Into TablesSDLC: Implementation

  37. Query 1: What customers (displayed by order#) has/have spent more than $2.00 on product# 08? Query 2: What products were not supplied by Shibby Sprinkles? Query 3: How many orders have been placed by Dylan Emde? Query 4: What is the total charge for each customer? Query 5: What customer(s) ordered a vanilla cake and spent more than $3.00? Query 6: What supplier(s) supplied the products Taylor Pace purchased? QueriesSDLC: Implementation

  38. SQL> --Oracle SQL, Group Project: Group 2 Frosted SQL> --BMIS441 Business Database Systems SQL> SQL> --Views SQL> SQL> CREATE OR REPLACE VIEW customer_receipt(order#, customer_name, order_date, product#, description, charge) 2 AS SELECT o.order#, c.first_name || ' ' ||c.last_name, order_date, p.product#, p.description, (quantity * retail_cost) 3 FROM customer c, orders o, product p, orderproduct op 4 WHERE c.customer# = o.customer# 5 AND o.order# = op.order#; View created. SQL> SQL> CREATE OR REPLACE VIEW customer_vendor(customer#, first_name, last_name, order#, product#, vendor#, vendor_name) 2 AS SELECT c.customer#, c.first_name, c.last_name, o.order#, p.product#, s.vendor#, v.vendor_name 3 FROM customer c, orders o, product p, orderproduct op, supply s, vendor v 4 WHERE c.customer# = o.customer# 5 AND o.order# = op.order# 6 AND op.product# = s.product# 7 AND s.vendor# = v.vendor#; View created. QueriesSDLC: Implementation

  39. SQL> --Q#1 SQL> --What customers (displayed by order#) has/have spent more than $2.00 on product# 08. SQL> SQL> SELECT customer_name, product#, COUNT(order#) as num_of_orders 2 FROM customer_receipt 3 WHERE charge > 2 4 AND product# = 08 5 GROUP BY customer_name, product#;  CUSTOMER_NAME PRODUCT# NUM_OF_ORDERS ------------------------------------ ---------- ------------- ALEX MCKELVEY 8 2 QueriesSDLC: Implementation

  40. SQL> --Q#2 SQL> --What products were not supplied by Shibby Sprinkles SQL> SQL> SELECT product.description AS Product 2 FROM PRODUCT, SUPPLY, VENDOR 3 WHERE product.product# = supply.product# 4 AND supply.vendor# = vendor.vendor# 5 AND vendor.vendor_name <> 'SHIBBY SPRINKLES'; PRODUCT ---------------------------------------- STRAWBERRY FROSTING NUTELLA FROSTING MAPLE FROSTING TIGERS BLOOD FROSTING PEPPERMINT TOPPINGS BACON BITS SPRINKLES BANANAS 8 rows selected. QueriesSDLC: Implementation

  41. SQL> --Q#3 SQL> --How many orders have been placed by Dylan Emde? SQL> SQL> SELECT first_name, last_name, COUNT(orders.order#) AS num_of_orders 2 FROM CUSTOMER, ORDERS 3 WHERE customer.customer# = orders.customer# 4 AND customer.first_name = 'DYLAN' 5 AND customer.last_name = 'EMDE' 6 GROUP BY first_name, last_name; FIRST_NAME LAST_NAME NUM_OF_ORDERS --------------- -------------------- ------------- DYLAN EMDE 2 QueriesSDLC: Implementation

  42. SQL> --Q#4 SQL> --What is the total charge for each customer? SQL> SQL> SELECT customer_name, TO_CHAR(SUM(charge), '$999.99') "TOTAL" 2 FROM customer_receipt 3 GROUP BY customer_name; CUSTOMER_NAME TOTAL ------------------------------------ -------- CARSON THOMPSON $91.08 COUNT SANCHEZ $22.77 DYLAN EMDE $91.08 ALEX MCKELVEY $273.24 TAYLOR PACE $22.77 QueriesSDLC: Implementation

  43. SQL> --Q#5 SQL> --What customer(s) ordered a vanilla cake with tigers blood frosting? SQL> SQL> SET LINESIZE 75 SQL> SET PAGESIZE 45 SQL> SELECT customer_name, description, TO_CHAR(charge, '99.99') AS charge 2 FROM customer_receipt 3 WHERE description = 'VANILLA CAKE' 4 AND charge > 3; CUSTOMER_NAME ------------------------------------ DESCRIPTION CHARGE ---------------------------------------- ------ DYLAN EMDE VANILLA CAKE 5.98 ALEX MCKELVEY VANILLA CAKE 17.94 DYLAN EMDE VANILLA CAKE 5.98 CARSON THOMPSON VANILLA CAKE 5.98 ALEX MCKELVEY VANILLA CAKE 11.96 QueriesSDLC: Implementation

  44. SQL> --Q#6 SQL> --What vendor(s) supplied the products Taylor Pace bought? SQL> SQL> SELECT DISTINCT vendor_name 2 FROM customer_vendor 3 WHERE first_name = 'TAYLOR' 4 AND last_name = 'PACE' 5 GROUP BY vendor_name; VENDOR_NAME ------------------------- SUGAR RUSHERS SQL> SQL> SQL> SQL> spool off QueriesSDLC: Implementation

  45. Routine check ups • Updating system if business model / strategy changes • Create a system that parses errors within data to create a self maintaining effort • Collaboration with managers to ensure data is up to date and accurate • Aim for continuous improvement and use feedback • Maintaining a well structured database SDLC: Maintenance

  46. VISIT US ONLINE

  47. We hope to add you to our Frosted customer table soon! Frosted Stay frosty my friends!

More Related