Assignment: Create 3 Related Tables In Access And Link Them ✓ Solved

Assignment: Create 3 related tables in Access, link them now

Assignment: Create 3 related tables in Access, link them now. This assignment requires designing a small database consisting of three related tables: Employees, Salary, and Department. Build a form, a query, and an activity diagram that shows how the tables interact. Deliverables include print screens and a PowerPoint style activity diagram pasted into Word.

Step 1 (prepare Table 1): Table 1 will contain information about employee identification. Go to the sheet named "Database" and show employees with postal code containing the letter "N" and with the phone ending by 7. Replace "student name" with your own name. Record as Table 1 named "Employees". Ensure the column widths show all information. Table 1 must be linked to Table 2.

Step 2 (prepare Table 2): Table 2 will contain information about employee salary: 1st column = paycheck number; 2nd column = rate per hour; 3rd column = annual salary; 4th column = link with Table 1 (define the linking column). Create data to fill each column. There must be data for all of the employees shown on Table 1.

Step 3 (prepare Table 3): Table 3 will contain information about employee department: 1st column = work order number; 2nd column = department number; 3rd column = supervisor name; 4th column = link with Table 2 (define the linking column). Create data to fill each column. There must be data for all of the employees shown on Table 1.

Step 4 (prepare a Form): Prepare a Form using Table 2 only. Make sure you modify the layout so we can see all of the fields on the screen.

Step 5 (connect the Tables): Connect the 3 Tables (into Database tool).

Step 6 (prepare a Query): Prepare the following query: Employee name - Rate/hour - Supervisor name - Phone - Cell - Paycheck number. Must be only for Montreal employees.

Step 7 (prepare Activity Diagram): Build the Activity diagram on PowerPoint and copy the result into the Word document. Table 1-2-3 must be shown on the same diagram. There must be 1 decision node shown per Table (total of 3 decision nodes).

Paper For Above Instructions

The solution presented here outlines a rigorous approach to modeling three related entities in a relational database, implementing them in Microsoft Access, and producing the accompanying artifacts (form, query, and activity diagram). The emphasis is on data integrity, clear relationships, and the ability to generate meaningful insights through a Montreal-focused query. This paper discusses the rationale, design decisions, and implementation steps aligned with literature on database design, relational theory, and practical Access usage.

Relational data modeling begins with identifying core entities and their attributes. For this assignment, the primary entities are Employees, Salary, and Department. The standard practice is to assign a primary key to each table that uniquely identifies each row and to introduce foreign keys to enforce referential integrity between related tables (Elmasri & Navathe, 2016). The Employee table (Table 1) should capture essential identification data such as employee_id (PK), name, postal_code, city, and phone. The requirement to filter postal codes containing the letter "N" and phone numbers ending with "7" serves as a practical constraint that guides data population and testing of relationships (Silberschatz, Korth, & Sudarshan, 2019). Replacing a placeholder name with the student’s actual name aligns the dataset with real-world usage and demonstrates familiarity with data entry processes (Coronel & Morris, 2019).

Table 2 (Salary) introduces a paycheck record linked to the corresponding employee. The first column is the paycheck_number (PK), followed by rate_per_hour and annual_salary. The fourth column acts as the foreign key linking back to Table 1, typically via employee_id. Designing the linking column explicitly helps enforce a one-to-one or one-to-many relationship depending on how salary history is modeled. Creating data for all employees listed in Table 1 ensures that the query in Step 6 can operate across the entire dataset, facilitating meaningful Montreal-only filtering (Date, 2012).

Table 3 (Department) captures work-order information and departmental supervision. The first column is work_order_number (PK), the second column is department_number, the third column is supervisor_name, and the fourth column is a link to Table 2 (salary). This structure enables an end-to-end chain: Employees -> Salary -> Department, with each link defined by a foreign key to the preceding table. Reasonable data for all employees ensures complete coverage in the related forms and diagrams and supports realistic reporting (Hernandez, 2013).

The Form in Step 4 should be based exclusively on Table 2, presenting all fields in a single screen layout. This design choice demonstrates how salary data can be reviewed and edited while preserving the integrity of the related employee record. A well-constructed form improves usability and reduces the likelihood of data entry errors (Alexander & Kusleika, 2019).

Step 5 involves physically connecting the tables within the database tool, thereby establishing the referential constraints that enforce correct data relationships. Proper relationship definitions include enforcing cascade updates and deletes where appropriate and ensuring that join types reflect the intended data retrieval semantics (Elmasri & Navathe, 2016).

Step 6 requires a query that returns Employee name, Rate/hour, Supervisor name, Phone, Cell, and Paycheck number, restricted to Montreal employees. This constraint necessitates a WHERE clause filtering by city (Montreal) and potentially province/region attributes. The query demonstrates how multiple related tables can be joined to produce a consolidated view, a central capability of relational databases (Silberschatz, Korth, & Sudarshan, 2019).

Step 7 requests an Activity Diagram that displays Tables 1-3 on a single diagram, with one decision node per table (three total). The diagram should be created in PowerPoint and pasted into the Word document. Activity diagrams convey workflow and decision points, illustrating how data flows through the tables and how operations such as lookups, joins, and validations occur in practice (Fowler, 2003; OMG, 2015).

In terms of data quality and reproducibility, a key practice is to normalize data to reduce redundancy and anomalies. Normalization generally proceeds to at least the third normal form in educational exercises, ensuring that non-key attributes depend only on the key, the whole key, and nothing but the key (Date, 2012). The use of primary keys and foreign keys, along with carefully chosen data types, constraints, and validation rules, supports data integrity across the three related tables (Hernandez, 2013).

From a practical perspective, documenting the schema, including field names, data types, and relationship cardinalities, is essential for maintenance and future enhancement. The deliverables—DESIGN VIEW screenshots, DATASHEET VIEW screenshots, a Form screenshot, a Relationships screenshot, a Query DESIGN screenshot, and the Activity Diagram—collectively demonstrate mastery of both conceptual design and practical implementation (Alexander & Kusleika, 2019).

Finally, the instruction to replace placeholder names with a real name and to tailor the Montreal filter to actual data emphasizes the importance of realistic data scenarios when teaching database design. This aligns with best practices in information systems education, where authentic-looking data fosters better understanding of relational constraints and user interfaces (Coronel & Morris, 2019).

References

• Elmasri, R., & Navathe, S. Fundamentals of Database Systems. Pearson, 7th ed., 2016.
• Silberschatz, A., Korth, H. F., & Sudarshan, S. Database System Concepts. Wiley, 7th ed., 2019.
• Coronel, C., & Morris, S. Database Systems: Design, Implementation, & Management. Cengage, 12th ed., 2019.
• Date, C. J. Database Design and Relational Theory. O'Reilly Media, 2012.
• Hernandez, M. J. Database Design for Mere Mortals. Addison-Wesley, 3rd ed., 2013.
• Alexander, M., & Kusleika, R. Microsoft Access 2019 Bible. Wiley, 2019.
• Fowler, M. UML Distilled: A Short Guide to the Language of UML. Addison-Wesley, 2003.
• OMG. UML 2.5 Specification. Object Management Group, 2015. https://www.omg.org/spec/UML/
• Microsoft Docs. Create relationships between tables in Access. https://support.microsoft.com/en-us/office/create-relationships-between-tables-1f8c2df5-9d1b-4e0f-8f6a-29f0a7f3d2ea
• Kroenke, D. & Rob, P. Database Concepts. Pearson, 6th ed., 2016.