CD Lending Library



CD Lending Library

Problem description:

You have built up quite a collection of music CDs, and word has gotten out to your friends (and their friends). While you like sharing your CDs with others—you get satisfaction out of exposing others to your music tastes—you don’t like the hassle of tracking down a long-ago borrowed CD or recommending a CD only to find it missing.

A friend suggests you develop a relational database in Microsoft Access to keep track of your collection and to help you manage the lending process. You hope that this system will save you from the hassles of administering the lending you already do—you don’t have much time. You also hope the system would add new value by allowing you to search for CDs by artist, composer, or song title. Currently, you file CDs by Artist, but you own many compilations and CDs with cover material, which makes finding a particular song hard sometimes.

Needs assessment process:

The first step in developing a database is to make sure you have a good understanding of the goals of the system, the current and desired processes involved, and the constraints that the organization faces. If you do not have a thorough understanding of these areas, you should ask questions that would help you understand. It is sometimes helpful to walk through the current process, step by step, investigating what works and what doesn’t throughout.

Once the goals, process, and constraints are understood, it is time to consider what data need to be stored. It is often helpful to think first of the output (information) that your database should produce. It is easy to get distracted by potential inputs (data) that your system might store. For example, what reports, graphs, values, and the like would help the user achieve goals? From that, you can determine what data need to be stored to produce that information.

Developing the database:

Often, the greatest challenge of developing a relational database system is deciding how to store the data to avoid redundancy (which maximizes efficiency and prevents many future data inconsistencies) while still providing all the ingredients for the information you might need.

In a relational database, data are stored in two-dimensional (rows and columns) tables. For example in this database, we will need a table (called CDs) to store information about CDs. Each row in the table is called a record and has information about one particular item of the type stored in that table, in this case one particular CD. Each piece of information that you want to store about CDs would be stored in a column, called a field in database terminology. For example, the record label might be in one column and the issue data might be in another. It is important to be able to identify each record in the table uniquely. Often an id number is used for this because many different items might share the same name (e.g., “Greatest Hits”). Sometimes multiple fields, taken together, can uniquely identify each record. The field (or fields) that uniquely identifies each record is known as the primary key. You should only store information in a table that can change from one record to the next and cannot be calculated with existing data. And, each field should have information in the smallest detail needed—you should never need to retrieve just one part of the data stored in a field.

When information repeats for a certain record in a table, the best solution is usually to create another table to store that repeating information (instead of creating multiple fields of the same type). For example, to store information about the various songs on a CD, there should not be multiple columns for track 1, track 2, … Instead, there should be a separate table (called Tracks) that has information about individual tracks. Each record (row) would have information for one individual track. And there would need to be a field that indicated which CD the track was on. How will we uniquely identify each record in this Tracks table (i.e., what is the primary key)? These two tables, CDs and Tracks, are related to each other, and are the start of our relational database. What other information (fields) do we need in the Tracks table? …in the CDs table? How are these two tables related? What other tables do we need?

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download