Assignment Help

[kimmeh]

I need to produce an entity relationship (ER) diagram normalised to third normal form

Secondly, I need to provide a detailed specification (data dictionary) for each table required in the database, including table name, field name, data type, length, format, validation and any additional comments I think would be helpful, e.g. whether the field is a primary key or not, or any assumptions I am making.

Then I need to explain the data handling procedures the database will undertake.

Lastly, I need to provide a strategy for testing and evaluating the database solution.

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

I have no idea on what to do to be honest, any help would be appreciated.

Here is the scenerio we got given.

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

eRadio MXFM is a web-based music radio station. It wants to set up its own discussion forum on the web. Listeners will be encouraged to register for the forum by filling in an on-line form. The details of people who register will be stored in a database. If their registration is accepted, they will be contacted by e-mail and provided with a username and password to log onto the forum.

The Marketing Department at eRadio MXFM is keen to use this registration as a means of gathering useful information from its listeners. One of the aims is to provide third-party companies with the email addresses of those listeners who want to be contacted about products and services that meet their musical interests. In particular, the department wants to put some specialist music magazines in touch with potential listeners. The music magazines have agreed to pay eRadio for the purchase of listener mailing lists.

In addition, the department wants to use the information to build up a profile of its listener base. The aim is to use this analysis to gain more advertising revenue and increase listening figures by focusing on the right kind of music.

eRadio MXFM has outsourced the hosting of its website to an external Internet Service Provider (ISP). The ISP will forward data captured from the on-line registration on a regular basis in the form of a comma separated variable (csv) file.

You have been asked by eRadio MXFM to produce a suitable database solution.

Additional Information

- The database is to be stored on eRadio’s main server and will not be linked to the internet in any way. Only specified employees of eRadio will have access to the database.
- The text file containing all the details of listeners, who want to register, will be imported into the database. If an application is valid, an email will be generated to the listener confirming acceptance.
- Registration is only accepted if an email address is provided and is valid. A valid email address is one that contains an @ symbol and does not have any invalid symbols for an email address.
- The imported text file will contain a number of fields.

 

[KenHigg]

First off, welcome to the forum. Second, a forum like this is not really a place where one can / should expect to get the level of help you've asked for. For that most here feel you really should go back to the instructor or other classmates. This forum is however invaluable for getting help on short, specific problems. The best questions posted here are ones that can be answered in a post or two. To help you with all you've listed would take a couple hundred posts...

 

[jzwp22]

Welcome to the forum!

As Ken points out in his post what you describe is quite general and hard to answer in a few replies, but I jotted down some ideas on how I would approach your assignment below.

Based on the information you provided, it sounds like the database is intended not to capture the forum discussions but only to capture the listeners' basic registration information and music preferences and to link them to potential third party companies. With that in mind, you obviously need a table to hold the listeners' information including the e-mail address. What other fields might be captured is really not indicated, but I would have to make assumptions such as first name, last name and probably address & contact information. You will also probably have a list of music types that the listener can choose from to indicate their preferences, so that would be another table. Since a listener can have many music preferences, a junction table that joins the users and the types of music they like will be necessary. Since the description also mentions that only certain users will have access to this database, you need some security around the database that will require users to log in. This will require a user table with associated passwords. You can technically put all people (users and listeners) into 1 table and add a field that distinguishes users from listeners.

Since you will be getting information via a csv file, I must assume that the csv file will have to be imported into the database as a temp table, but of course, most csv file information will not be normalized so you will have to do some work on that end. I assume that you would want the manipulation of the csv file data to be automated which will require some coding. Within that code you can check the e-mail address to insure validity. If valid, you would then append the listener to your listener table and assign them a password. You would also append their music preferences to the appropriate table. You can then create e-mail notifications for those newly appended listeners to inform them of their passwords. You could have a yes/no field in the listener table that would indicate if an e-mail was sent. You would not append listeners to your table if they did not provide a valid e-mail address.

To handle the third party companies, you will need another table to hold the company names and another to join them with the music types that fit their market. This would require another junction table similar to the one used to join listeners with their music types. You could probably use a query to match the 3rd party companies with the appropriate listeners based on the type of music. You would probably need to track which listeners information was sent to which 3rd party companies, so that you do not repeat the information to the 3rd party companies over time. I think a junction table could be used to capture this.

Hopefully this will give you some information to get started.