Question

Dan Tragg insists on using only the highest quality blanks in the manufacturing of his surfboards. Tragg’s uses a list of approved vendors with whom they have established a good relationship over the years. The blanks are purchased when a production supervisor recognizes a need for inventory. The supervisor goes to his or her terminal and completes a Purchase Order Form online. This is done by selecting the vendor, the inventory item, and the amounts to be ordered. The order can be for one type of blank or for several types. The Purchase Order Form is reviewed by a Purchasing Agent. If it is approved, a Purchase Order Report is prepared, printed, and mailed to the selected vendor. The goods are received and counted by the Receiving Clerk. The Receiving Clerk enters the date, the PO number, and the Vendor’s Invoice Number in the top portion of the form. In addition, the Clerk must enter the Inventory ID number and the quantity received in the form. If the order is in agreement with the Purchase Order, it is scheduled for payment. Tragg’s sometimes pays for more than one invoice (receipt of inventory) with each check (cash disbursement). In addition, on occasion they have placed very large orders and have paid for those orders in installments. You will apply the REA Model and use Microsoft Access software to complete the case analysis of the expenditure cycle described above.

Requirements 1. Identify the resources, events and agents

2. Draw E-R diagrams that illustrate the relationships between these items. You for sure need additional assumptions - please make it clear in writing.

3. Identify tables that you would need to create a working database. For each table, please include the following information: table name, primary key(s), foreign key(s), and other attributes. Your tables should be consistent with the E-R diagram you created in part 2.

4. Using Access, create at least three records for each of the tables you identified in part 3. Document by printing the window from the Access. 5. Create relationships for your various tables. Document by printing the relationships window – make sure primary key icons appear on the relationships. Note that your relationships should be consistent with the E-R diagram you created in part 2.

Answer 1

1) Identify the resources, events and agents:-

As we know that according to real obejects of REA model

⦁ goods, services or money, i.e., resources

⦁ business transactions or agreements that affect resources, i.e., events

⦁ people or other human agencies (other companies, etc.), i.e., agents

So, as per the given case here Blank is related to goods , purchasing and selling is part of events and approved vendor are agents.

2) Draw E-R diagrams that illustrate the relationships between these items. In case you need additional assumptions, please make it clear in writing.

Here is the E-R diagram as per the given requirements, please go through it thoroughly:

3) Identify tables that you would need to create a working database. For each table, please include the following information: table name, primary key(s), foreign key(s), and other attributes.

Here is shown below the tables with required information, please have a look:

4) Here we create table as per the given requirements:--

CREATE Table Employee(

Emp_ID int(10) NOT NULL,

Emp_Name varchar(50)NOT NULL,

Emp_Add varchar(50)NOT NULL,

Emp_PhoneNo varchar(10)NOT NULL,

DOB DATE,

CONSTRAINT PK_Employee PRIMARY KEY(Emp_ID)

);

CREATE Table orders(

Item_ID int(12) NOT NULL,

Purchase_Date DATE,

Quantity int(4)NOT NULL,

Amount Number(6,2),

CONSTRAINT FK_orders REFERENCES PARENT_Employee(Emp_ID)

);

CREATE Table Vendors(

Ven_ID int(10) NOT NULL,

Ven_Name varchar(50)NOT NULL,

Ven_Add varchar(50)NOT NULL,

Ven_PhoneNo varchar(10)NOT NULL,

Item_Qty int(4)NOT NULL,

CONSTRAINT PK_Vendors PRIMARY KEY(Ven_ID)

);

CREATE Table Payments(

Invoice_No int(12) NOT NULL,

Po_Number int(6) NOT NULL,

Payment_Type varchar(20)NOT NULL,

Item_Qty int(3)NOT NULL,

Amount Number(6,2),

CONSTRAINT PK_Payments PRIMARY KEY(Invoice_No)

CONSTRAINT PK_Payments PRIMARY KEY(Po_Number)

);

------------------------------------------

Now here we showing how we enter the input into databse:

INSERT INTO Employee

VALUES

('1', 'Sara Jhones', 'NeyYork', '4002540246', '19/9/1987'),

('2', 'Sara Gomes','America', '4875558755', '27/9/1990'),

('3', 'Dim Thomes', 'London', '4875411554', '3/3/2001');

INSERT INTO Vendors

VALUES

('1', 'Sara Gomes', 'NeyYork', '4542540246', '52'),

('2', 'Tina Gomes','France', '4848558755', '65'),

('3', 'Kim Thomes', 'London', '4845871554', '45');

If you don't like the answer please don't dislike, let me know in the comments. If you like the answer please hit like.