ACCT-361: Database Project Step Two (20 Points) Due Date ✓ Solved

ACCT-361: Database Project Step Two (20 points) Due date

Instructions: 1. If you are given feedback to change things in Step 1, make sure you follow it. If you are not given any feedback, it means everything is good as is. The feedback is on Blackboard, under Project Step 1. In most cases you can increase your Step 1 grade if you reupload. You may upload corrected versions of Step 1 until Step 2 due date. One thing to note is that I will regrade your Step 1 only once. So, make sure you understand the feedback you are given. I will regrade Step 1 when grading Step 2. Also, you can upload multiple versions of your project steps. I will always grade the latest one at the time of grading. Therefore, you do not need to ask me to grade the updated version.

2. Begin to build your system project prototype using the two chosen events (needs to be connected, as per Step 1 instructions) and related resources and agents from your Step 1.

3. Create the tables with primary key, foreign keys, and non-keys based on rules we have used for implementation in this course. Grading of this assignment is based heavily on proper implementation. Selection of non-key attributes based on your chosen industry is also critical. If I recognize your effort in doing so, you will be rewarded.

4. Create the relationships between tables and enforce referential integrity. (Remember for Step 2 and your project that only prototype (implement in Access) 2 events selected in Step 1 along with related master files and linking tables, not the entire process.) Enforcing referential integrity is quite simple – when you double-click on the relationship line, you should see a checkbox about it. Relationships can be implemented and reviewed by clicking Database Tools/Relationships in Access.

5. Make sure to choose the correct data type when building fields in tables. This step is a good time to consider using unique industry attributes and auto number data type. Review of these topics can be found in the Tables lab videos on Blackboard, under Course Resources, Microsoft Access Help Resources, Tables Lab and Videos.

6. Do not populate tables with data at this point.

7. Keep your graded step 1 & 2 as you will need that for the final project.

8. Save your database as: YourLastName_FirstName_Spring20_FirmName

What to submit: 1. PAPER: A print screen of your relationships created in the database – with referential integrity enforced. Expand print screen size, if too small.

2. UPLOAD TO BLACKBOARD: Access Database File.

3. UPLOAD TO BLACKBOARD: Data dictionary for all tables. This can be accessed in MS Access under Tools/Documenter. See next page for data dictionary instructions. Steps to save data dictionary as .pdf for all tables for Project Step 2: 1. Select Database Tools tab. Click Database Documenter icon.

2. Documenter window pops up. Select all table names. Click Options button.

3. Print Table Definition window pops up. There are three sections: Tables, Fields, Indexes:

• For Tables – Include Properties and Relationships.
• For Fields – Select Names, Data Types and Sizes only.
• For Indexes- Select Nothing.

4. Click OK. Click PDF or XPS. 5. Save in PDF format and upload to blackboard.

Sample Paper For Above instruction

Introduction

The project centers on developing a relational database model for a payroll service provider company, highlighting key processes involving system installation and user training. The goal is to design a functional prototype that accurately models the events, resources, and agents involved, ensuring proper implementation of database principles and referential integrity.

Understanding the REA Model in Payroll Services

The REA (Resources, Events, Agents) model provides a framework for representing economic exchanges within an organization. In this context, the two principal events are 'Installing the Payroll System' and 'Training the Payroll Clerk.' These events are interconnected within the company's operational workflow, with resources such as the payroll software and training materials, and agents including the company staff and clients.

Create the Database Tables

The initial step involves creating tables for the involved entities. Each table must have a primary key, relevant foreign keys to establish relationships, and appropriate non-key attributes. For instance, the 'Clients' table might include client ID, name, and contact information, while the 'Contracts' table includes contract ID, client ID (foreign key), date, and contract details.

The 'PayrollSystemInstallation' and 'TrainingSession' tables are central to modeling the two events. The 'PayrollSystemInstallation' table tracks system deployment details, linked to the client and responsible agent. The 'TrainingSession' table records training specifics, which must connect to the system installation event and the trainee, the payroll clerk.

Defining Relationships and Enforcing Referential Integrity

Once tables are created, relationships are established by linking primary keys to foreign keys, ensuring data consistency. For example, the 'Contracts' table's client ID links to the 'Clients' table. Similarly, 'TrainingSession' links to 'PayrollSystemInstallation' via installation ID, enforcing referential integrity. In Access, these relationships are set via the Relationships view, with the referential integrity option checked to prevent orphan records.

Choosing Data Types

Proper data types enhance data integrity. AutoNumber fields are suitable for primary keys, while Text data types are used for names and descriptions. Dates are stored with Date/Time data types. Industry-specific attributes, such as license numbers or system versions, are assigned suitable data types to match their nature.

Data Population and Final Preparations

At this stage, data entry is deferred. The focus is on creating structural components. The database, relationships, and data dictionary files (exported via Access Documenter) are saved and prepared for submission.

Conclusion

This project demonstrates the application of database design principles within a payroll service context, emphasizing proper table creation, relationship building, referential integrity enforcement, and documentation. Such a design ensures robust data management supporting the company operations and provides a foundation for future expansion and automation.

References

• Allen, E., & Shea, R. (2017). Database systems: A practical approach to design, implementation, and management. Pearson.
• Harrington, J. L. (2016). Relational database design and implementation. Morgan Kaufmann.
• Rob, P., & Coronel, C. (2018). Database systems: Design, implementation, & management. Cengage Learning.
• Kroenke, D. M., & Kroenke, V. (2018). Database concepts. Pearson.
• Hoffer, J. A., Venkataraman, R., & Topi, H. (2016). Modern database management. Pearson.