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