Question

Typically a library assesses a fine the user must pay if they keep a book out past the due date. As well the library needs to track the amount, if any, the member has paid. In this exercise we add two fields to the Loan table so we can keep track of fines that are assessed and the amount the member has paid. 1\. Open the Loan table in design view and add two new fields named fineAssessed and finePaid. These fields must have a datatype of Currency. 2\. Save the Loan table and then view the rows of the table. There are no amounts for these fields. 3\. Choose some row(s) in the Loan table and enter values for the fineAssessed and finePaid fields. Note the values you enter will appear as dollars and cents.

Short answer

In the Loan table, two new fields named 'fineAssessed' and 'finePaid' of the 'Currency' datatype were added. The table was saved and values in the form of dollars and cents were added into these new fields.

Step-by-step solution

Add New Fields

Open the Loan table in design view. This is generally done through right-clicking the table and selecting the design view option. Once in design view, create two new fields named fineAssessed and finePaid. Set the datatype of these fields to Currency, which accommodates monetary values in the database.

Save the Table

After adding the new fields to the Loan table and setting the correct datatypes, save the changes. This is generally done by clicking the save icon or through the File menu. Once saved, close the design view.

Enter Values into the Table

Return to the normal table view by selecting the Loan table. Enter some values for the fineAssessed and finePaid fields in some of the rows. When entering data into the new fields, it should be in the form of dollars and cents, as determined by the Currency datatype.

Key concepts

Understanding Relational Databases

Relational databases are a type of database where data is organized into tables. Each table holds data in rows and columns, much like a spreadsheet. However, what distinguishes relational databases is their ability to relate data between tables through keys. For instance, in a library system, you might have tables for Books, Members, and Loans, where a loan would connect to both a book and a member.
To ensure data integrity, relational databases use primary and foreign keys. A primary key is a unique identifier for each record in a table, while a foreign key in another table can refer to it, establishing a relationship between the tables. This relationship is crucial as it allows for efficient data retrieval and management without redundancy.

• Primary Keys: Unique value for each row.
• Foreign Keys: Enable connection between different tables.
• Data Integrity: Relational databases prevent data duplication and ensure data accuracy.

The Basics of Database Design

Database design involves structuring a database in a way that optimizes its functionality and efficiency. A well-designed database ensures that data is stored logically, minimizing duplication and maintaining accuracy. In the library scenario, database design would include tables like Books, Members, and Loans, each serving a specific purpose.
One key consideration in database design is normalization. This process involves organizing data into tables and columns, ensuring that data dependencies make sense and redundancy is minimized. For instance, information about a book should only be stored in one place – the Books table.

• Normalization: Avoid redundant data and ensure logical dependencies.
• Entities and Attributes: Identify what data needs tables and what details those tables should hold.
• Scalability: A good design should allow a database to grow with increasing data volumes.

Understanding the Currency Datatype

The Currency datatype in Microsoft Access is used for fields that need to hold monetary values. This datatype is particularly useful in financial applications because it provides accuracy up to four decimal places, which is important for precise calculations.
When using the currency datatype, fields are formatted to display numbers as local currency, such as dollars and cents. This promotes a clear and professional presentation. In the context of the library database, fields like fineAssessed and finePaid use the currency datatype to track financial transactions related to book loans. This choice helps reflect accurate amounts responsibly.

• Precision: Allows for four decimal places, crucial for accuracy.
• Display: Automatically formats numbers as currency, improving readability.
• Application: Ideal for any scenario where detailed financial tracking is needed.