Group Project - Part III

Kim's Flower Inc. Purchasing (Expenditure) Cycle

Requirements:

The final part of the project requires your team to implement the logical data model in MS Access. Please read carefully the instructions below before you start working on your project. You should submit your database file and a separate word document with answers/explanations as requested below.

1. Create tables in the MS Access file by using the provided solution of "Project - Part II" (i.e., the normalized tables).

• In each table, define the primary key.
• For each attribute, define the data type, provide a field description, and define the field properties.
• For each table, there should be at least one validation rule and a corresponding validation text and explain why these validation rules are implemented.
• References
• Chapter 9 (Appendix) of the "Accounting Information Systems: A Database Approach" (Murthy and S. Michael) book
• Chapter 3 of the "Modeling and Designing Accounting Systems: Using Access to Build a Database" (Chang and Ingraham) book

1. Establish relationships between all the tables you created.

• Make sure that you enforce the referential integrity in all your relationships.
• Reference
• Chapter 9 (Appendix) of the "Accounting Information Systems: A Database Approach" (Murthy and S. Michael)

1. Create at least three forms.

• One of which should be a form with a subform.
• Describe the function of each form, who would use it, and its purpose.
• Reference
• Chapter 4 and 5 of the "Modeling and Designing Accounting Systems: Using Access to Build a Database" (Chang and Ingraham) book, explaining how to create a form with a subform.

1. Populate your tables with fictitious data entries.

• You should sufficiently populate your tables so that you can demonstrate your design (about 20 rows for events and 10 for agents and resources).

1. Create queries and explain why these queries are important for managers/decision makers.

• You should have at least four multiple table queries in your design, and three other queries.
• At least one of your queries should perform calculation. You can create a query using either SQL (i.e., SELECT statement) or Expression Builder in Query Design.
• References
• SQL: SQL materials posted on the course website (Course Materials - Assignment 1 and Team Exercise #1)
• Expression Builder: Chapter 4 and 5 of the "Modeling and Designing Accounting Systems: Using Access to Build a Database" (Chang and Ingraham) book, explaining how to use the Expression Builder in Query Design.

1. Create at least two reports for management/decision makers.

• Describe what benefits these reports provide for decision makers, and how often they will be used.
• Reference
• Chapter 4 and 5 of the "Modeling and Designing Accounting Systems: Using Access to Build a Database" (Chang and Ingraham) book, explaining how to create a simple report based on a query.

1. Document the existing internal control structure based on your current design.

• Control examples are provided in the "Sample Controls" tables in Chapters 12 and 13, and in Chapter 10.

Deliverables:

Create the tables in 'Group Project - Part III.accdb.'

Complete the document (i.e., Group Project - Part III - Documentation.docx).

Submit three files. Be sure to change the file name using your team number. For example, my MS Access file would be "Team10-Part3.accdb' and my MS Word file would be "Team10-Part3.docx."

Normalized Tables

Table Name

Primary Key

Foreign Key

Other Attributes

PurchaseOrder

PONo

PreparedEmployeeNo, AuthorizedEmployeeNo,

VendorNo

PODate

ReceiveOrder

ReceiptNo

EmployeeNo, VendorNo

ReceiptDate

MakePayment

PaymentNo

PreparedEmployeeNo, AuthorizedEmployeeNo, VendorNo, AccountNo

PaymentDate, PaymentAmount,

FlowerInventory

ItemNo

Description, QtyOnHand, ListPrice

Cash

AccountNo

AccountType, Bank, AccountBalance

Vendor

VendorNo

VendorName, VendorAddress, Balance

Employee

EmployeeNo

EmployeeName, EmployeeAddress, DateStarted, JobTitle, Phone

FI_PO

PONo, ItemNo

QtyOrdered, OrderPrice

FI_RO

ItemNo, ReceiptNo

ItemCondition

PO_RO

PONo, ReceiptNo

QtyReceived

RO_MP

ReceiptNo, PaymentNo

ERA Diagram

Kim's Flower

VALIDATION RULES AND VALIDATION TEXTS (No. 1)

Table Name

Validation rule

Corresponding Validation Text

Why the role is implement

QUERIES (No. 4)

Query Name

Table Used

Why the query is important for managers/decision makers.

FORMS (No. 5)

Form Name

Function

Who would use

Purpose

REPORTS (No. 6)

Report Name

What benefits the report provide

How often the report will be used.

CONTROLS (No. 7)

Control

Description

RBS Veterinary

VALIDATION RULES AND VALIDATION TEXTS (No. 1)

Table Name

Validation rule

Corresponding Validation Text

Why the role is implement

QUERIES (No. 4)

Query Name

Table Used

Why the query is important for managers/decision makers.

FORMS (No. 5)

Form Name

Function

Who would use

Purpose

REPORTS (No. 6)

Report Name

What benefits the report provide

How often the report will be used.

CONTROLS (No. 7)

Control

Description

Group Project - Part II

Kim's Flower Inc. Purchasing (Expenditure) Cycle

Requirements:

The second stage of the project requires students to perform step 8 in the event-oriented modeling (as described in chapters 8 and 9).

• Examine the standard partial solution for Kim's Flower Inc. Use the provided partial solution as a starting point for this stage.
• Convert the REA diagram into tables. Make sure that you follow the procedure described in chapter 9.
• Implement the rules in chapter 9 for determining whether a table conforms to the first, second, or third normal form. Normalize tables that are not in the 3rd normal form.
• Make sure that all your tables are normalized (each table should be in the 3rd normal form).
• You should complete the attached table describing the list of all your tables, the attributes in each table, and primary and foreign keys for each table.
• In addition, you must summarize all the steps that you sued in the normalization process.

Example:

When the diagram was converted into a table form this already made all of the tables into the second normal form. At this point then the only thing that needs to occur to make all of the tables into third normal form is to remove all transitive dependencies that may be still in the tables......

Deliverable:

Complete the document (i.e., Group Project - Part II - Normalized Tables.docx).

Submit one file. Be sure to change the file name using your team number. For example, my MS file would be "Team10-Part2.docx."

ERA Diagram

Partial List of Attributes for Each Entity

Entity

Attributes

Flower Inventory

ItemNo, Description, QtyOnHand, ListPrice

Vendor

VendorNo, VendorName, VendorAddress, Balance

Employee

EmployeeNo, EmployeeName, EmployeeAddress, DateStarted, JobTitle, Phone

Cash

AccountNo, AccountType, Bank, AccountBalance

Purchase Order

PONo, PODate

Receive Order

ReceiptNo, ReceiptDate

Make Payment

PaymentNo, PaymentDate, PaymentAmount

Flower Inventory - Purchase Order

QtyOrdered, OrderPrice

Flower Inventory - Receive Order

ItemCondition

Purchase Order - Receive Order

QtyReceived

Note: The list does not include attributes (i.e., foreign keys) which are characteristic of relationships with other entities.

Normalized Tables

Table Name

Primary Key

Foreign Key

Other Attributes

Flower Inventory

ProductId

(productidLocation)

Product Description, QtyonHand, ListPrice,

Stype,Color,Price,

Quantity

Vendor

VendorID

VendorTaxID

Vendor Name,

VendorAddress

Vendor Number

Vendor email

Purchase order invoice number

Account Balance

Employee

EmployeeID

Department

First name

Last name

Date of Birth

Sexo

SSN

Address

Telephone #

Email

Hired date

Position

Salary

Bank account number

Cash

AccountNo

VendorID

AccountNo, AccountType, Bank, AccountBalance

Purchase Order

OrderNo

VendorID

PODate,Purchase order form (invoice)

number

Date ordered

Item number

Item description

Quantity ordered

Order price

Employee number

Receive Order

PONumber

VendorID

Receipt number

Receipt data

Quantity received

Item condiction

Purchase order number

Item cost

Received reporter

Check number

Employee number

Update inventory records

Make Payment

PaymentNo

VendorID

PaymentDate, PaymentAmount

Invoices number

Purchase ordered

Received Report

Amount changed

Vendor Number

Vendor address

Vendor email

Flower Inventory - Purchase Order

PurchaseOrderNo

ProductID

QtyOrdered, OrderPrice,

Flower Inventory - Receive Order

PurchaseOrderNo

ProductCondiction

Acceptproduct, Rejectproduct

Purchase Order - Receive Order

PurchaseOrderNo

ProductID

QtyReceived, QtyReject, QtyReturnVendor

Step 8:

Relationships

Counts

1:1

0

1:M

10

M:M

3

Mandatory Relationships

Collapse 1:1 relationship into a single table

For 1:M relationships, post the primary key of the table on the "one" side to the table on the "many" side

For M:M relationships, create a new composite key table posting the primary key of each participating table the new table

Optional Relationships

Treat the "optional" side of a relationship as a mandatory "many" relationship for the purpose of conversion.

Apply the same conversion rules for mandatory

Group Project - Part II

RBS Veterinary Hospital Revenue Cycle

Requirements:

The second part of the project requires your team to perform step 8 in the event-oriented modeling (as described in chapters 8 and 9).

• Examine the standard partial solution for the RBS Veterinary Hospital. Use the provided partial solution as a starting point for this stage.
• Convert the REA diagram into tables. Make sure that you follow the procedure described in chapter 9.
• Implement the rules in chapter 9 for determining whether a table conforms to the first, second, or third normal form. Normalize tables that are not in the 3rd normal form.
• Make sure that all your tables are normalized (each table should be in the 3rd normal form).
• You should complete the attached table describing the list of all your tables, the attributes in each table, and primary and foreign keys for each table.
• In addition, you must summarize all the steps that you sued in the normalization process.

Example:

When the diagram was converted into a table form this already made all of the tables into the second normal form. At this point then the only thing that needs to occur to make all of the tables into third normal form is to remove all transitive dependencies that may be still in the tables......

Deliverable:

Complete the document (i.e., TeamNumber-Part2.docx).

Submit one file. Be sure to change the file name using your team number. For example, my MS file would be "Team10-Part2.docx."

REA Diagram

Partial List of Attributes for Each Entity

Entity

Attributes

Employee

EmployeeID, EmployeeName, EmployeeAddress, DateStarted, JobTitle, Phone, EmployeeEmail

Service

ServiceNo, ServiceName, ServiceCharge, ServiceHour

Medication

MedicationNo, DrugName, UnitCost, QtyOnHand,

Cash

AccountNo, AccountType, BankName, AccountBalance

Pet Parent

ParentID, ParentName, ParentAddress, DayPhone, NightPhone, ParentEmail

Pet

PetID, PetName, Breed, Color, DateOfBirth

Appointment

AppointmentNo, PetID, AppointmentDate

Examination

ExamNo, Weight, Height, Heartbeat, Temperature, Symptom

CashReceipt

ReceiptNo, ReceiptDate, AmountReceived

Appointment_Service

SpecialNeeds

Examination_Service

ActualServiceHours, ExaminationResults

Examination_Medication

QtyOfMedication

Note: The list does not include all the attributes (i.e., primary and foreign keys) which are characteristic of relationships with other entities.

Normalized Tables

Table Name

Primary Key

Foreign Key

Other Attributes

Employee

Employee ID

EmployeeNo

Date of Birth

SSN

First Name

Middle Name

Last Name

Home Address

Personal Email

Work Email

Sex

Position

Salary

Bank Routing Number

Hired Date

Service

ServiceID

Examination_Service

ExamNo

MedicationNo

Surgery

Check-up

Blood Work

Xray

MRI

CAT Scan

Medication

MedicationNo

DrugID

Drug Type

Pharmaceutical Company

Function

Cash

AccountNo

BankID

Routing Number

Pet Parent

ParentID

PetID

Parent Name

Home Address

Day Phone

Night Phone

Parent Email

Pet

PetID

ParentID

Pet Name

Date of Birth

Home Address

Phone Number

Breed

Color

Appointment

AppointmentNo

AppointmentID

AppointmentNo

PetID

Appointment Date

Examination

ExamNo

PetID

Pet Name

Breed

Color

Date of birht

Cash Receipt

ReceiptNo

EmployeeID

CustomerID

AccountNo

Date ofReceipt

Time of Receipt

Quantity of Receipt

Appointment- Service

Appointment_Sevice

AppointmentID

Special Needs

Examination- Medication

Examination_Medication

PetID

QtyOfMedication

Medication name

Examination- Service

Examination_Service

ExamNo

Actual Service Hours

Examination Results

Step 8 Hospital:

Relationships

Counts

1:1

1

1:M

9

M:M

3

.