Frosted
Download
1 / 47

Frosted - PowerPoint PPT Presentation


  • 113 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Frosted' - pelham


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Frosted

Ian Bickel

AJ Rehn

Lacey Williams

Janice Barlow

Frosted

Stay Frosty!


What is frosted

Freedom to create the perfect tasting cupcake through our unique creation and decoration process.

What is Frosted?

Self-serve Cupcakery


Floor Plan unique creation and decoration process.

Retail Floor

Bakery


Project scenario

We offer a unique creation and decoration process.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


Objectives

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


Decoration process

The process of creating a include

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


Sdlc analysis

Seven Tables of Frosted: include

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


Relational diagram sdlc design
Relational Diagram include SDLC: Design


Normalized dependency diagram sdlc design
Normalized Dependency Diagram include SDLC: Design


Normalized dependency diagram sdlc design1
Normalized Dependency Diagram include SDLC: Design


Normalized dependency diagram sdlc design2
Normalized Dependency Diagram include SDLC: Design


Entity relationship diagram sdlc design
Entity Relationship Diagram include SDLC: Design


Employee table sdlc design
Employee Table include SDLC: Design


Customer table sdlc design
Customer Table include SDLC: Design


Order table sdlc design
Order Table include SDLC: Design


Product table sdlc design
Product Table include SDLC: Design


Orderproduct table sdlc design
OrderProduct include TableSDLC: Design


Vendor table sdlc design
Vendor Table include SDLC: Design


Supply table sdlc design
Supply Table include SDLC: Design


Drop tables sdlc implementation

SQL> DROP TABLE supply CASCADE CONSTRAINTS; include

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


Create tables sdlc implementation

SQL> CREATE TABLE EMPLOYEE include

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


Create tables sdlc implementation1

8 zip NUMBER(5), include

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


Create tables sdlc implementation2

11 REFERENCES EMPLOYEE (employee#)); include

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


Create tables sdlc implementation3

4 quantity NUMBER(2), include

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


Create tables sdlc implementation4

SQL> include

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


Insert rows into tables sdlc implementation

SQL> include

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


Insert rows into tables sdlc implementation1

1 row created.  include

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


Insert rows into tables sdlc implementation2

SQL> INSERT INTO customer include

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


Insert rows into tables sdlc implementation3

SQL> include

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


Insert rows into tables sdlc implementation4

SQL> include

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


Insert rows into tables sdlc implementation5

SQL> include

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


Insert rows into tables sdlc implementation6

SQL> include

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


Insert rows into tables sdlc implementation7

2 VALUES (1004, 04, 1); include

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


Insert rows into tables sdlc implementation8

SQL> include

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


Insert rows into tables sdlc implementation9

SQL> INSERT INTO supply include

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


Insert rows into tables sdlc implementation10

SQL> INSERT INTO supply include

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


Queries sdlc implementation

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


Queries sdlc implementation1

SQL> --Oracle SQL, Group Project: Group 2 Frosted more than $2.00 on product# 08?

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


Queries sdlc implementation2

SQL> --Q#1 more than $2.00 on product# 08?

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


Queries sdlc implementation3

SQL> --Q#2 more than $2.00 on product# 08?

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


Queries sdlc implementation4

SQL> --Q#3 more than $2.00 on product# 08?

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


Queries sdlc implementation5

SQL> --Q#4 more than $2.00 on product# 08?

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


Queries sdlc implementation6

SQL> --Q#5 more than $2.00 on product# 08?

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


Queries sdlc implementation7

SQL> --Q#6 more than $2.00 on product# 08?

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


Sdlc maintenance

  • Routine check ups more than $2.00 on product# 08?

  • 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


Visit us online

VISIT US ONLINE more than $2.00 on product# 08?


Frosted1

We hope to add you to our Frosted customer table soon! more than $2.00 on product# 08?

Frosted

Stay frosty my friends!