Cooltunes media bmis441 g1 fall 2012
This presentation is the property of its rightful owner.
Sponsored Links
1 / 33

Cooltunes Media BMIS441-G1 Fall 2012 PowerPoint PPT Presentation


  • 84 Views
  • Uploaded on
  • Presentation posted in: General

Cooltunes Media BMIS441-G1 Fall 2012. Alex Herreid Austin Carrillo Collin Parker William Greenburg. Introduction. CoolTunes Media, Music Distribution Website Used the Systems Development Life Cycle (SDLC). Planning. Familiar topic: Music Develop company that focuses on “Indie” music

Download Presentation

Cooltunes Media BMIS441-G1 Fall 2012

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


Cooltunes media bmis441 g1 fall 2012

Cooltunes MediaBMIS441-G1 Fall 2012

Alex Herreid

Austin Carrillo

Collin Parker

William Greenburg


Introduction

Introduction

  • CoolTunes Media, Music Distribution Website

  • Used the Systems Development Life Cycle (SDLC)


Planning

Planning

  • Familiar topic: Music

  • Develop company that focuses on “Indie” music

  • Develop database that can support a media distribution company and all of its needs


Analysis

Analysis

  • Select necessary entities

  • Analyze the Relationships

  • Select Rules

  • Analyze Cardinalities


Analysis1

Analysis

Relevant Entities

  • Customer

  • BillingInfo

  • ShippingInfo

  • Orders

  • OrderItems

  • Products

  • Artists

  • Labels


Analysis2

Analysis

  • The CUSTOMER table will hold the basic information for our customers. The attributes will be Customer#, LastName, FirstName, and Email. The Customer# attribute will be the primary key and a unique number for each customer. This is just the baseline data about our customers, the rest of their information will be included in other entities.

Customers


Analysis3

Analysis

  • The ORDERS table will include Order#, Customer#, OrderDate, OrderType, and ShipDate. In this entity Order# will be the primary key and customer# will be the foreign key.

  • Function of OrderType

  • Keeping track of our customers

Orders


Analysis4

analysis

  • The BILLINGINFO table will include Customer#, Address, City, State, Zip, and CardType. The customer# attribute will be a foreign key in the table linking to the CUSTOMERS table. This will also include their billing information in case it is different from their shipping information.

BillingInfo


Analysis5

Analysis

  • The SHIPPINGINFO table will include their Customer#, Address, City, State, and Zip Code. This entity is very similar to the BILLINGINFO table in that it is essentially just used to hold their information with the Customer# as a reference. This table allows us not only to have our customers purchase downloadable content but also the physical albums.

ShippingInfo


Analysis6

Analysis

  • The ORDERITEMS table will only have one purpose, which is to have a detailed list of what exactly customers have purchased in each of their orders. We will also be able to have a record of what a customer was supposed to receive if there is ever a time were an order is lost or damaged in the shipping process.

OrderItems


Analysis7

Analysis

  • The PRODUCTS table keeps track of each product, a product can be a either a song or an album. Item_ID will be the primary key for this entity. The rest of the attributes in this entity will be Artist_ID, title, ItemType, Price, and Track_Qty. Artist_ID is a foreign key from the ARTISTS table that we will discuss next. ItemType will specify whether the product is an individual song or a whole album.

Products


Analysis8

analysis

  • The ARTISTS table will include Artist_ID, ArtistName, Genere, and Label_ID. Artist_ID is the primary key for this table and Label_ID is a foreign key. This would just be recording basic information about the artists that will be pulled from when people are search for music from our company.

Artists


Analysis9

analysis

  • The LABELS table will mainly be used for internal company purposes, it will include Label_ID, LabelName, ContactName, and ContactEmail. This will list the information for our company contacts at the various labels we are in business with. This will mainly be for communicating any issues with tracks that they have released to us and other administrative issues.

Labels


Design

Design

Explain/illustrate:

1. Functional dependency,

2. Normalization process with 3NF


Implementation

implementation

  • Three stages

    • DROP

    • BUILD

    • INSERT

  • Because of the dependencies, we had to enter the tables in the following order: LABELS, ARTISTS, PRODUCTS, CUSTOMERS, BILLINGINFO, SHIPPINGINFO, ORDERS, ORDERITEMS.


Implementation1

IMPLEMENTATION

DROP TABLE BILLINGINFO CASCADE CONSTRAINTS;

DROP TABLE CUSTOMERS CASCADE CONSTRAINTS;

DROP TABLE SHIPPINGINFO CASCADE CONSTRAINTS;

DROP TABLE ORDERS CASCADE CONSTRAINTS;

DROP TABLE ORDERITEMS CASCADE CONSTRAINTS;

DROP TABLE PRODUCTS CASCADE CONSTRAINTS;

DROP TABLE ARTISTS CASCADE CONSTRAINTS;

DROP TABLE LABELS CASCADE CONSTRAINTS;

DROP SEQUENCE orders_order#_seq;


Implementation2

IMPLEMENTATION

CREATE TABLE LABELS

(LABEL_ID NUMBER(4),

LABELNAME VARCHAR2(40),

CONTACTNAME VARCHAR2(25) NOT NULL,

CONTACTEMAIL VARCHAR2(40),

CONSTRAINT LABELS_LABEL_ID_PK PRIMARY KEY(LABEL_ID));


Implementation3

IMPLEMENTATION

INSERT INTO LABELS

VALUES(8000,'Island Records', 'Jack Oliver', [email protected]');

INSERT INTO LABELS

VALUES(8001,'RCA Music', 'Eva Green', [email protected]');

INSERT INTO LABELS

VALUES(8002,'Sony Music', 'Tony Stark', [email protected]');

INSERT INTO LABELS

VALUES(8003,'Warner Brothers Group', 'Bradley Johnson', [email protected]');


Implementation4

IMPLEMENTATION

CREATE TABLE ARTISTS

(ARTIST_ID NUMBER(4),

LABEL_ID NUMBER(4) NOT NULL,

ARTISTNAME VARCHAR2(30),

GENRE VARCHAR2(15),

CONSTRAINT ARTISTS_ARTIST_ID_PK PRIMARY KEY(ARTIST_ID),

CONSTRAINT ARTISTS_LABEL_ID_FK FOREIGN KEY (LABEL_ID)

REFERENCES LABELS (LABEL_ID));


Implementation5

IMPLEMENTATION

INSERT INTO ARTISTS

VALUES(6000, 8004, 'Pink', 'Pop');

INSERT INTO ARTISTS

VALUES(6001, 8004, 'Johan Flozart', 'Classical');

INSERT INTO ARTISTS

VALUES(6002, 8005, 'Rihanna', 'Pop');

INSERT INTO ARTISTS

VALUES(6003, 8005, 'Avicii', 'Electronic');


Implementation6

IMPLEMENTATION

CREATE SEQUENCE orders_order#_seq

INCREMENT BY 1

START WITH 4000

NOCACHE

NOCYCLE;

CREATE TABLE ORDERS

(ORDER# NUMBER(4),

CUSTOMER# NUMBER(4) NOT NULL,

ORDERTYPE CHAR(4) NOT NULL,

ORDERDATE DATE NOT NULL,

SHIPDATE DATE,

CONSTRAINT ORDERS_ORDERTYPE_CK

CHECK (ORDERTYPE IN ('DIGI', 'PHYS')),

CONSTRAINT ORDERS_ORDER#_PK PRIMARY KEY(ORDER#));


Implementation7

IMPLEMENTATION

CREATE TABLE ORDERITEMS

(ORDER# NUMBER(4),

ITEM_ID NUMBER(4),

CONSTRAINT ORDERITEMS_ORDER#_FK FOREIGN KEY (ORDER#)

REFERENCES ORDERS (ORDER#),

CONSTRAINT ORDERITEMS_ITEM_ID_FK FOREIGN KEY (ITEM_ID)

REFERENCES PRODUCTS (ITEM_ID));


Implementation8

IMPLEMENTATION

INSERT INTO ORDERS

VALUES (orders_order#_seq.NEXTVAL,1000,'DIGI',TO_DATE('10/20/2012','MM/DD/YYYY'),NULL);

INSERT INTO ORDERITEMS

VALUES (orders_order#_seq.CURRVAL,3015);

INSERT INTO ORDERITEMS

VALUES (orders_order#_seq.CURRVAL,3018);

INSERT INTO ORDERS

VALUES (orders_order#_seq.NEXTVAL,1005,'PHYS',TO_DATE('10/15/2012','MM/DD/YYYY'),TO_DATE('10/17/2012','MM/DD/YYYY'));

INSERT INTO ORDERITEMS

VALUES (orders_order#_seq.CURRVAL,3000);

INSERT INTO ORDERITEMS

VALUES (orders_order#_seq.CURRVAL,3005);


Implementation9

IMPLEMENTATION

  • Query 1: Display the Artist name, album name and price for albums that are greater than 10$. Please list them in descending order.

    SQL> SET LINESIZE 75

    SQL> SET PAGESIZE 45

    SQL> COLUMN Artistname FORMAT A15 HEADING 'Artist Name'

    SQL> COLUMN title FORMAT A35 HEADING 'Album Title'

    SQL> COLUMN Price FORMAT $99.99 HEADING 'Price'

    SQL> SELECT a.ARTISTNAME, p.Title, p.Price

    2 FROM ARTISTS a JOIN PRODUCTS p USING (Artist_ID)

    3 WHERE Price > 10

    4 ORDER by Price DESC;


Implementation10

IMPLEMENTATION

Artist Name Album Title Price

--------------- ----------------------------------- -------

Johan Flozart Shadows $15.99

Johan Flozart Nature $15.99

Johan Flozart Volcano $15.99

Swag Worldwide Swag $15.99

Swag All Up In It $13.99

Swag Me And Your Sister $13.99

Pink This Is Real Life Vol. 2 $13.99

The Eagles Heart On $13.99

Muse Origin Of Symmetry $12.99

Katy Perry Essentially $12.99

Muse Absolution $12.99

23 Rows Selected


Implementation11

IMPLEMENTATION

  • Query 2: Display the average time it takes for the order to ship in hours.

    SQL> SELECT (AVG(shipdate-orderdate)*24) "Shipping_Delay_Hours"

    2 FROM orders

    3 WHERE (shipdate-orderdate) IS NOT NULL;

    Shipping_Delay_Hours

    --------------------

    81.6


Implementation12

IMPLEMENTATION

  • Query 3: Create a view that only displays digital orders. In this view please include the Order#, Item_ID, ORDERDATE, and price.

    SQL> CREATE VIEW Digital_Orders

    2 AS SELECT Order#, Item_ID, Price, Orderdate,

    3 ROUND(

    4 Price/

    5 (SELECT SUM(Price)

    6 FROM ORDERS JOIN ORDERITEMS USING (ORDER#)

    7 JOIN Products USING (ITEM_ID)

    8 WHERE ORDERTYPE = 'DIGI'),3) AS "% of Revenue"

    9 FROM ORDERS JOIN ORDERITEMS USING (ORDER#)

    10 JOIN Products USING (ITEM_ID)

    11 WHERE ORDERTYPE = 'DIGI'

    12 GROUP by Order#, Item_ID, Orderdate, Price;

    View created.


Implementation13

IMPLEMENTATION

  • Query 4: Display all the orders that were placed during the summer. Please include the title of the album, orderdate, and the full name of the customer.

    COLUMN Full_Name FORMAT A15

    SQL> COLUMN title FORMAT A25 HEADING 'Album Title'

    SQL> COLUMN Orderdate FORMAT A15

    SQL> SELECT title, orderdate, Firstname ||' '|| Lastname "Full_Name"

    2 FROM Customers JOIN Orders USING (Customer#)

    3 JOIN Orderitems USING (Order#)

    4 JOIN Products USING (ITEM_ID)

    5 WHERE orderdate BETWEEN '21-JUN-12'and'21-SEP-12';


Implementation14

IMPLEMENTATION

Album Title ORDERDATE Full_Name

------------------------- --------------- ---------------

This Is Real Life 01-AUG-12 Collin Parker

Levels 25-AUG-12 Ronald Ojeda

Escape 22-JUL-12 Brandi Salyer

Whole Lotta Love 25-AUG-12 Ronald Ojeda

Apple Sauce 22-JUL-12 Brandi Salyer


Implementation15

IMPLEMENTATION

  • Query 5: How much revenue has come from Customer# 1006?

    SQL> COLUMN SUM(Price) FORMAT $99.99 HEADING 'Revenue'

    SQL> (SELECT SUM(Price)

    2 FROM ARTISTS a JOIN PRODUCTS p USING (Artist_ID)

    3 JOIN Orderitems USING (Item_ID)

    4 JOIN Orders USING (Order#)

    5 JOIN Customers USING (Customer#)

    6 WHERE Customer# = '1006');

    Revenue

    -------

    $37.97


Maintenance

Maintenance

  • Routine Checks

  • Proactive in maintenance

  • Efforts to predict future problems


Growth and change

Growth and change

  • May need to create a larger database in the future

  • May expand into other markets (audiobooks, movies, etc.)

  • Other unforeseeable concerns


Conclusion

Conclusion

  • How the project went

  • What we learned

  • Issues we had


  • Login