ICOM 5016 Introduction to Database System

1
ICOM 5016 Introduction to Database System

• Project 1
• Dr. Manuel Rodriguez-Martinez
• Department of Electrical and
• Computer Engineering
• University of Puerto Rico, Mayagüez

2
Term Project

• Goal
• Build an application to manage the inventory of
  an on-line CD store
• Enable administrators to keep track of
• Customers
• Songs
• Albums
• Artists
• Record Labels (e.g. Sony, BMG)
• Sales
• Returns
• Profits

3
Term Project Goals

• Enable administrators to
• Order more CD
• Create reports about sales, loses, revenues, etc.
• Send invoice to Credit Card Companies
• Enable customers to
• Search for songs, artists, albums
• Buy CDs
• Returns CDs
• See their shopping cart
• See the status of an order

4
Technical Requirements

• Application must follow a three-tier
  (middle-tier) organization
• GUI Web browser
• You have freedom to design your pages, as long as
  they ask for the required information
• Application Logic Various Servlets running on
  Tomcat
• Accounts will be given in ADASEL
• Database System Oracle
• Accounts will be given in ADASEL

5
Information to be represented

• Customers customer name, account number,
  mailing address, billing address, credit card
  information
• Album album serial number, album title, artist,
  record label, release date, list of songs,
  musical category (salsa, rock, etc.)
• Songs song name, duration, singer (could be
  more than one), author, album in which it appears
  (can be more than one)

6
Information to be represented

• Record Label record label name, label id,
  postal address, name of sales representative,
  phone number for sales rep.,
• Customer Shopping cart albums currently being
  considered for shopping by a given customer,
  amount per item.
• Orders placed albums sold, quantity per item,
  card charged, order number, billing address, mail
  address, customer
• Items sold CD purchased, customer, amount sold
  per item, date of purchase, order number

7
Items to be represented

• Items returned CD to return, customer, amount
  to return per item, date of purchase, date of
  return, order number
• Re-Supply orders CD to re-order, record label,
  quantity, sales representative, re-supply order.

8
Tasks

• You need to desing an E-R that identifies
• Entities
• Relationship
• Previous list is quite ambiguous. You need to
  identify each one and justify your
  classification.
• You are free to add more information as needed.

9
Customer Operations to be supported

• Search songs by
• Song Name, Artists, album name,
• Returns list of CDs with that song
• Search album
• Artists, album name, serial number, record label
• Returns list of CDs with given search value
• Search artists
• Artist name, album, song, record label
• Returns list of CDs for a given artists
• Hit parade Top 10 albums for current week
• Can ask to classify by category

10
Customer Operations to be supported

• Search Record label
• Label name, name of artists, name of album
• Returns list of CDs made by given label
• Add CD to shopping cart
• Delete CD from shopping cart
• Login and View Shopping Cart
• Login and Place order
• Login and Return CD
• Login and Track order status (in progress,
  shipped, cancelled)

11
Manager Operations to be supported

• Report indicating total sales by day,week and
  month (all)
• Report indicating returns by day, week and month
  (all)
• Report indicating total revenue by day, week and
  month (all)
• Report indicating total sales grouped by artists
• Ask for one of day, week or month
• Report indicating total revenue grouped by
  artists
• Ask for one of day, week, or month

12
Manager Operations to be supported

• Report indicating total sales for a particular
  artist
• Ask for one of day, week or month
• Report indicating total revenue for a particular
  artist
• Ask for one of day, week, or month
• Report indicating total sales for a particular
  album
• Ask for one of day, week or month
• Report indicating total revenue for a particular
  album
• Ask for one of day, week, or month

13
Manager Operations to be supported

• Report indicating total sales for a particular
  record label
• Ask for one of day, week or month
• Report indicating total revenue for a particular
  record label
• Ask for one of day, week, or month
• Invoice to buy different albums from a given
  record label
• Specify album and quantity

14
Manager Operations to be supported

• Invoice to charge a given credit card company for
  a set of albums purchased by a group customer14
• Specify album and quantity

15
Deliverables

• Friday October 10, 2003
• ER Diagram
• Task Descriptions
• Demo of GUI interacting with Servlets
• Actions just returns dummy values
• Friday, October 31, 2003
• Refinements and Corrections to ER Diagram
• Refinements and Corrections to Task Description
• Refinements of GUI
• Relational Schema for application implemented
• Demo all Queries running from the command prompt

16
Deliverables

• Friday, December 5, 2003
• Final ER Diagram
• Final Tasks Descriptions
• User Guide for the system
• Final GUI
• Final Relational Schema for application
  implemented
• Integration of GUI with queries
• Request are posed from GUI and sent to servlet
• Servlet maps request to one or more queries
• Queries are sent to Oracle via JDBC
• Results are acquired, formatted and sent back to
  browser.
• Demo of Application that is fully operational