DDI GROUP PROJECT ASSIGNMENT 2017
Your group (A technology solutions company) has been asked to produce a proposal for a database solution for an online radio service. You will be using http://www.accuradio.com for the purpose of this project but your solution is a generic online radio system based on the features of this site.
The database design is used to manage the member’s scheme and website for the company, in which listeners can sign up to the radio station. Note the database doesn’t store the music files, just the data about it.
This work should be completed in your group. Group progress should be recorded through minutes of all meetings. Each member of a group will receive the same mark for the group project. (note: If group minutes record an unacceptable level of non-participation by a group member this position will be reviewed).
Customer relationship management is crucial to the success of the online radio service and so the company would like you to focus on creating a relational database solution to create maximum value for the company and for the customer.
You should consider the following users:
· Online users
· Customer management
· Marketing and advertising
· Music development
You will have to use your business knowledge and database design knowledge to analysis the website to gather requirements and business rules. You will need to decide on some of some assumptions yourselves.
The design should be flexible enough to cope with the business expanding its operation and the proposal does not need to be costed.
The proposal will include a written report of 3000 words (maximum) and a working prototype database using Microsoft Access which you should populate with a sample data of a maximum of 100 website members who joined over the last year. You will the Database System Development Lifecycle Process (DSDLC). The Design Document will be incorporated into description of the DSDLC.
To limit some complexity and make the sample data manageable there will be some informational limitations placed on the design:
1. Only record user information for members
2. You do not have to record each members ‘song history’
3. Limit the number of channels per Genre to 5 and the number of tracks per channel to 5.
In the written proposal you will include:
1. Your database system development lifecycle process (DSDLC) including the Database Design Document.
2. A set of SQL queries and results (see below)
3. You will also suggest TWO business intelligence methods which can create business value from the data and demonstrate it by giving appropriate calculations and visualisations of the results.
4. You will evaluate the benefits of your proposed database solution and any limitations involved.
5. You will provide an evaluation of any system management issues involved in your proposal and describe strategies to overcome them.
You are not required to design any user views in the proposal or show how data is added to the database. The Access prototype database will be handed in with the report on a suitable storage device to the faculty office.
In addition, in order to demonstrate your prototype the report will include a series of SQL queries and results tables that address the following requests:
1. A list of members, their favourite music genre and top five tracks?
2. Which music genres do different types of users like?
3. What is the pattern (if any) of membership over the last year?
4. Are there any trends in the track ratings over the last year?
Marking scheme is as follows:
· DSDLC 15%
· Design document and implementation 30%
· SQL queries 15%
· BI 15%
· Benefits and system management issues 15%
· Overall presentation and minutes 10%
Submission deadline: 28 April 5pm
Submit one copy of all paper work, and one storage device with the Access database.
Submit one electronic copy of the coursework through Moodle. This does not include the Access database. Please see coursework submission guidelines.
Notes:
1. Design Document
This is not straightforward and you will probably work through several design iterations. Do not include early designs in your documentation, but you may if you wish put these in a clearly labelled appendix. Use the top down approach. Fully document your design process in a manner you would use if you were a professional database administrator – marks will be taken off for the inclusion of extraneous information and unclear designs. Please note that I cannot ‘check’ designs for errors prior to ‘hand in’ as this is assessed work.
2. DSDLC
You will document this in the manner taught in lectures and in the text book. It is important that the stages of the design process align with each other.
3. Implementation
a. Write SQL statements to implement your design and print these out.
b. Implement your design in MS Access.
4. Print out SQL statements and results tables. A sample of the tables may suffice here if the table is very large but only if it demonstrates the result fully.
5. Business Intelligence
The online radio business is keen to use the data they are collecting to improve their business. Your task is to come up with TWO ideas for how the data could be used. You might want to consider using some of the data mining techniques we covered in class. Write a report explaining your ideas. For each, write a brief explanation/rationale for it and then detail its implementation and results.
Guidance:
• The benefits and issues section should clearly set out the case for the database design proposed. This is not as essay format and does not require references to support your arguments. Spend time on getting the design right. An incorrect design will have implications on your queries.
• You have not been given a usable list of entities and will have to generate your own. Spend time thinking about your entities. Get this bit right, and the rest will be easier.
• You get to make up at least some of your own enterprise rules (although base them in reality and include any assumptions). In a way therefore, you get to control to an extent how complex your database is. If your database doesn’t match your enterprise rules, this is a MAJOR problem. However, if you ‘simplify’ the specification by keeping complications out of the enterprise rules, and
then your database matches these, that’s fine, although not necessarily a top grade project.
• Your database doesn’t have to do any automatic calculations or automatic data input – the application programmer will take care of this sort of thing. You only have to store data. Likewise, you don’t have to build a website – the website developers does this.
• Follow a design process and demonstrate that you have followed a process in your write up. Don’t ‘pad out’ your design with too much commentary on what you are doing. Your diagrams and your data dictionary should be understandable on their own, if you follow the notation and procedure we went through in class.
• Be careful you don’t duplicate data or introduce unnecessary attributes/entities just to make the SQL or BI easier.
• Your SQL queries should be as efficient as possible
DEdwards DDI 2017
Page 4