Weekly Assessment Spring 2020 Management Information Systems
Weekly Assessment 3spring 2 2020management Information Systems Mis30
Consider the following two relational database tables to answer the questions below: Employee Table and Payment Table. Identify the primary keys and foreign keys for each table. Create the data dictionary for the Payment table using appropriate data types. Determine if certain records can be added to the Employee and Payment tables, providing explanations. The Employee Table includes Employee ID, Employee Name, Address, and Phone Number; the Payment Table includes Payment ID, Employee ID, Overtime Payment, and Payment Date. Clarify whether specific entries comply with database constraints and integrity rules.
Paper For Above instruction
The relational database structure presented comprises two primary tables: Employee and Payment, each with distinctive key constraints that maintain data integrity and facilitate relational mapping.
In the Employee table, the primary key (PK) is Employee ID, which uniquely identifies each employee, ensuring that no two employees share the same ID. The table includes Employee Name, Address, and Phone Number, all of which are attributes describing the employee. For normalization and consistency, Employee Name and Address should have data types of VARCHAR with suitable character limits, such as VARCHAR(50) for Employee Name and VARCHAR(100) for Address. The Phone Number attribute should be VARCHAR(15), accommodating various international formats.
The Payment table registers employee financial transactions, with Payment ID serving as the primary key. The Employee ID is a foreign key (FK) linking to Employee Table, enforcing referential integrity, so that each payment record is associated with a valid employee. The Overtime Payment can be represented as DECIMAL(8,2) to accurately store monetary values with two decimal places. Payment Date should be stored as DATE, capturing the specific date of transaction.
The data dictionary for the Payment table includes:
- Payment ID: VARCHAR(10), PRIMARY KEY
- Employee ID: VARCHAR(10), FOREIGN KEY references Employee(Employee ID)
- Overtime Payment: DECIMAL(8,2)
- Payment Date: DATE
Regarding the addition of specific records, the record with Employee ID 106 Khalid can be added only if Employee ID 106 already exists in the Employee table; otherwise, it violates referential integrity constraints. As for the Payment table, inserting a record without a valid Employee ID present in the Employee table compromises referential integrity, leading to an error or data inconsistency.
This structure and data validation ensure data consistency, support relational integrity, and facilitate reliable data retrieval for organizational purposes.