| Course | AC442 Data Management and Analysis |
|---|---|
| Unit | Unit 4 |
| Paper type | Table design exercise |
| Length | About 560 words, 5 pages double-spaced |
| Format | APA 7 student paper |
| School | Purdue University Global |
| Program | BS in Accounting |
| Updated | October 2026 |
Free sample paper for AC442 Unit 4
4,906 Rows Into Seven Tables: A Unit 4 Table Design Exercise for a Winery's Distributor Register
[Student Name]
Purdue University Global
AC442: Data Management and Analysis
Unit 4 Table Design Exercise
[Instructor Name]
[Date]
Cobble Hill Estate Winery, its distributors and every figure are composite; the article and textbooks cited are real.
Evidence: Six Rows of the Flat Register
The sample rows show four faults. One distributor appears as "Bayview Wine Co." and "Bayview Wine Company." A license number, 0047821, lost its leading zero and became 47821 when the file was saved. One row's check column reads "10442/10457," two payments in one cell. And a non-vintage blend carries the text NV in the vintage column, which forces the whole column to text.
Functional Groups in the Nineteen Columns
1. Distributor: distributor ID determines name, license, state and terms.
2. Sales representative: rep ID determines name and region.
3. Wine: SKU determines varietal, vintage, bottle size and list price.
4. Invoice: invoice number determines date, distributor and representative.
5. Line: invoice number plus line number determine SKU, quantity and price.
6. Payment: check number determines date and amount; which invoices it pays is a separate fact.
Grouping columns by what determines them is the step that decides the tables, because each group's determinant becomes a primary key (Codd, 1970).
Table Specifications, Parents First
1. DISTRIBUTOR: distributor_id INT, PK; name VARCHAR(80), not null; license VARCHAR(20), not null; state CHAR(2); terms_days SMALLINT.
2. SALES_REP: rep_id INT, PK; name VARCHAR(60); region VARCHAR(20).
3. WINE: sku VARCHAR(16), PK; varietal VARCHAR(40); vintage SMALLINT, null allowed; bottle_ml SMALLINT; list_price DECIMAL(12,2).
4. INVOICE: invoice_no INT, PK; invoice_date DATE; distributor_id INT, FK; rep_id INT, FK; doc_type CHAR(2), invoice or credit memo.
5. INVOICE_LINE: invoice_no INT, FK; line_no SMALLINT; composite PK on both; sku VARCHAR(16), FK; quantity INT; unit_price DECIMAL(12,2).
6. CASH_RECEIPT: check_no VARCHAR(20), PK; receipt_date DATE; distributor_id INT, FK; amount DECIMAL(12,2).
7. CASH_APPLICATION: check_no and invoice_no, composite PK, both FK; amount_applied DECIMAL(12,2).
The license is stored as text, not a number, because it is an identifier that only looks numeric, and saving it as a number is exactly what turned 0047821 into a different license in the old file.
Money is DECIMAL(12,2) throughout, since floating-point types round cents. Vintage allows null, so a non-vintage blend stores an honest blank instead of NV, and every sort and range filter on year keeps working.
The split check from row three now has a home: check 10442 and check 10457 each become one CASH_RECEIPT row, and CASH_APPLICATION records how much of each went to the invoice they paid together.
The Invoice Total Is Not Stored
The register carried a stored total on every row, and nine invoices disagreed with their own lines. The new design derives the total by summing INVOICE_LINE, so it cannot drift. At this volume the computation costs almost nothing (Hoffer et al., 2019).
Load Summary and Proof
Rows loaded: DISTRIBUTOR 46; SALES_REP 6; WINE 38; INVOICE 1,322, including 38 credit memos; INVOICE_LINE 4,906; CASH_RECEIPT 702; CASH_APPLICATION 1,131.
INVOICE_LINE has exactly as many rows as the register, and the sum of quantity times unit price across all lines is $1,284,660.00, matching the general ledger's distributor sales balance to the cent. Normalization should lose no information, and reconciling a rebuilt total to the ledger is the accountant's proof of that (Romney & Steinbart, 2018).
References
Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377-387. https://doi.org/10.1145/362384.362685
Hoffer, J. A., Ramesh, V., & Topi, H. (2019). Modern database management (13th ed.). Pearson.
Romney, M. B., & Steinbart, P. J. (2018). Accounting information systems (14th ed.). Pearson.
What the AC442 Unit 4 assignment asks for
Unit 4 of AC442 asks you to normalize a flat file into related tables. Start with evidence: show sample rows and name the faults they contain, such as repeated text, inconsistent spellings, lost leading zeros and several values in one cell. Group the columns by what each depends on, and identify the determinant of each group, which becomes a primary key. Specify each table in dependency order, parents before children, with column names, data types, null rules and keys. Argue data types where accounting meaning is at stake: currency, identifiers that look numeric and fields that may legitimately be empty. Decide whether totals should be stored or derived, and explain why. Finish with a load summary giving row counts per table, and prove the design lost nothing by rebuilding a total and matching it to the ledger. Cite the relational model and a database text in APA 7.
How the AC 442 Unit 4 example is put together
The sample opens with four faults in six sample rows: a distributor spelled two ways, license 0047821 saved as 47821, two checks in one cell and NV in the vintage column. Six functional groups follow, each with its determinant, citing Codd. Seven tables are specified parents first, with money as DECIMAL(12,2), the license as VARCHAR(20) and a nullable vintage. The highlighted sentence argues that the license must be text because it only looks numeric. A note shows where the split check now lives. A section explains why invoice totals are derived from lines, since nine stored totals disagreed with their own lines. The load summary lists row counts from 46 distributors to 4,906 lines, and the rebuilt $1,284,660.00 matches the ledger, citing Romney and Steinbart.
Reading the AC442 Unit 4 grading rubric
AC442 table designs earn their marks for correct normalization, sound data types and proof that nothing was lost. Each table should hold facts about one thing, identified by its key, and graders check for repeating groups and values that depend on only part of a composite key. Every foreign key must point to a table already defined. Data types should be justified where they matter: money in exact decimal types, identifiers as text, optional values allowed to be null. Deciding whether to store or derive totals, with a reason, shows design judgment. The load summary and a reconciliation to the ledger turn the design into something verifiable, which this course weights heavily. Starting from evidence in the original file makes every decision traceable. Clear specifications and APA 7 citations close out the Purdue Global grade for this unit.
Common AC442 Unit 4 mistakes, and how to avoid them
Many Unit 4 designs split the file into tables by guesswork rather than by what each column depends on. Composite keys are missing from line tables, so two lines of one invoice collide. Money is stored as floating point and loses cents. License numbers and ZIP codes are stored as integers and lose leading zeros. NV and similar placeholders force year columns to text. Stored totals are kept without any check against their lines. No reconciliation is offered, so nobody knows whether rows were lost. If your flat file is a payroll register, a vendor list or a patient billing export, our writers can design the tables and prove the split against your control totals.
Get an AC442 Unit 4 example written to your instructions
Attach the spreadsheet your Unit 4 prompt hands out, exactly as issued, plus the rubric and the database product your section uses if it names one, since data types differ between products. Tables, specifications and the rebuilt control total arrive within 24-48h, and the first custom sample is free. The paper above is an original model document written by our desk, not a submitted student paper and not an official Purdue University Global document.
More AC442 papers and BS in Accounting samples
- AC442 Unit 1: Accounting Data Map Discussion Post
- AC442 Unit 2: Business Process Description
- AC442 Unit 3: REA Entity Relationship Diagram
- AC442 Unit 5: Query Writing Problem
- AC442 Unit 6: Multi-table Join Exercise
- AC442 Unit 7: Data Quality Worksheet
- AC442 Unit 8: Seminar Reflection
- AC442 Unit 9: Summary Query Set
- AC442 Unit 10: Analysis Brief
- AC430 Unit 4: Earnings and Profits Schedule
- AC420 Unit 4: FIFO Process Costing Worksheet
- AC410 Unit 4: Internal Control Walkthrough
- AC314 Unit 4: Revenue Cycle Analysis
AC442 Unit 4 questions, answered
Why not store the invoice total for speed?
Because a stored total can drift from its lines, and the register already proved it: nine invoices disagreed with themselves. Recomputing the total from lines costs almost nothing at this volume, and it guarantees agreement. Some sections accept a stored total if a constraint or a scheduled check compares it with the lines, and the sample notes that alternative in a sentence.
What happens to non-vintage wines?
Their vintage is stored as a blank rather than as the letters NV, which would force the column to text and break every sort and range filter on year. A null says honestly that no vintage applies. The specification records the rule, and the load summary counts how many SKUs carry a blank so a reader can check it.
How is the rebuilt total proven?
By summing line amounts in the new INVOICE_LINE table and placing the result beside the general ledger's distributor sales balance. Both read 1,284,660.00 in the sample. Row counts per table sit alongside, so a grader can see that 4,906 lines went in and 4,906 came out, with nothing duplicated or dropped along the way.
Where can I find a free AC442 Unit 4 sample paper?
This page holds a complete AC442 Unit 4 table design exercise normalizing a composite winery's distributor register into seven tables, with data types, keys, a load summary, a ledger reconciliation and APA 7 references.
Why is the license number stored as text in AC442 Unit 4?
It is an identifier that only looks like a number. Stored as a number, license 0047821 became 47821, a different license, so the design keeps it as VARCHAR(20).