Accounting Information Systems - Test 1 Flashcards
What is a normalised (or error free data base) X
No update anomalies, no insert anomalies, no delete anomalies.
Anomalies occur when you don’t design a DB correctly.
Update Anomaly (Data Redundancy) X
When you want to update data but can’t, because data is located in various places all over the database. Non key data is repeated, and update is difficult or impossible. When there are NO update anomalies - when a data value is changed, all users see that change. Relates to redundancy of data.
Insert Anomaly X
When you want to insert data but can’t i.e. can’t fit the info into a cell, or you have to know another piece of information in order to insert a record.
When there are NO insert anomalies, you can add new info to master files
eg current version only stores info about students with parking tickets. If student doesn’t have a parking ticket, their registered car will not have a record in this file.
Delete Anomaly X
Occurs when more data is deleted than is desired by the database user. i.e. if when you delete a row in a table, it removes all data in the database about some entity.
When you discard one item you lose something that you still need. e.g delete records after students have paid parking ticket - lose all car rego information
Where there are no delete anomalies, deleting a row in a transaction table does not remove all data about some entity.
How to design a Database Correctly X
- Draw an ER diagram
- Draw cardinalities on diagram
- Create tables for each entity on diagram (data dictionary)
- follow 6 basic rules: - Primary Keys must be unique and cannot be NULL (blank) - entity integrity rule.
- Foreign Keys can be NULL. When there IS a foreign key, it will be the primary key of the related table - referential integrity.
- Every column (other than foreign keys) must describe a fact about primary key
- Every column must be single valued (no sets allowed) - cannot have a list of item numbers in a single column. Could be audio file, book text, video - but only of one book, one video etc.
- Data type must be constant for a column. Consistent
- Neither Row nor column order is significant
What is a primary key? X
Primary key is the attribute that uniquely identifies a specific row in a relation (tuple) eg customer number, item number.
Must be unique and cannot be NULL (blank) - entity integrity rule.
What is a foreign key? X
Foreign key is an attribute in one table that is the primary key of another table. They are used to link tables (eg customer number in sales table).
The relationship between the primary key and the foreign key enables the DB software to link the two tables together.
In 1:1 relationship, foreign key can be in either table. If events are in sequence, usually be in second.
In 1:N relationship, you take the foreign key from the one side, to the many side.
What do columns represent? X
Columns = attributes. Every column (other than foreign keys) must describe a fact about the primary key. eg columns/attributes in customer table - name, address, balance, credit limit, are facts about customer X; you don't store info about products in the customer table. Every column must be single-valued. Data type must be constant for a column eg text, number, currency.
Basic rules for table creation X
Create a table for each entity of the REA diagramme
Give each table a unique name
Every row in each table is unique (RECORD)
Every column in each table has a unique name (ATTRIBUTE)
Order of columns/rows irrelevant
Each relation has a set of identifiers called keys (primary, foreign).
What is Normalisation? X
A set of rules for taking relations and putting them in their simplest form. Normalisation simplifies the database structure and eliminates anomalies (insert, update, delete)
A methodology for ensuring that attributes are stored in the most appropriate tables and that the design of the DB promotes accurate and non-redundant storage of data.
Process of normalisation can result in creation of new tables.
Steps in Normalisation X
1) First Normal Form - all non-key attributes are singular with respect to the key.
2) Second Normal Form - Must be in first normal form. All non-key attributes relate to the entire key (NB: only important when there are multi-attribute keys).
3) Third Normal Form - must be in second normal form. All non-key attributes must be independent.
First Normal Form X
All non-key attributes are singular with respect to the key. Each attribute has only one value. i.e. list of parking tickets per vehicle, or children names per employee. Make parking ticket # the primary key of another table.
(Employee#, Last Name, Department, Salary History)
All columns/attributes in a table that are NOT the primary key, should be singular. One answer given the key. eg one last name, one department. Salary history is not singular - could be many salaries the employee has had. Breaks rules. Can’t put 15 salaries in one cell.
(Employee#, Last Name, Department)
I still want the salary history. How do I get it? How do I make it singular? What makes salary singular? Assume increases happen annually, any given year, one position and one salary.
(Employee#, year, position, salary)
101 2020, manager, $105 000
102 2020, staff, $55 000
101 2021, ceo, $150 000
Standard way to “write” tables” X
Table (aka relation)
Parenthesis = table ( )
Attributes/Columns separated by commas ,
Rows are records (aka tuple)
Primary Key is underlined (can have 2 part, 3 part etc)
Foreign Key is in italics or squiggly line
Solution to problems = MAKE MORE TABLES
Second Normal Form X
Have to do this in sequence. Are they in first normal form? Yes - second rule.
Must be in first normal form
All non-key attributes must relate to the ENTIRE key (only important when the primary key includes two or more parts (multi-attribute keys).
(Supplier#, Part#, Supplier Name, City, Quantity)
Quantity is amount we have in inventory
Supplier name only relates to the supplier part of the multi attribute Supplier#,Part# key. I don’t need to know both parts of the key to know supplier name. Likewise for city. Quantity relates only to part#, not supplier.
Eg if I buy 5000 different parts from this supplier - name and city 5000 times repeated.
If I buy a part from 7 different suppliers, quantity on hand will be stored 7 different times. Repeated information means can’t correct/update it. UPDATE ANOMALY
What if I want to add a new supplier I’ve never bought from, I can’t because I don’t have a part # yet. INSERT ANOMALY.
If I delete a supplier, and that’s the only supplier I have for that part, I delete the quantity on hand that I have for that part and I won’t know I have it. DELETE ANOMALY.
TO FIX: Pull out the items that only relate to the one item of the 2 or more part primary key, and create separate tables.
(Supplier #, Supplier Name, City)
(Part#, Quantity)
505, 14230 - for Part 505 (Blue pens) i have a total of 14230.
If I wanted to know who I got certain parts from:
(Supplier#, Part#) - which part do I get from each supplier.
OR if I wanted to know how many I had of each part from each supplier.
(Supplier #, Part #, quantity from each supplier)
101, 505, 450 - from Supplier 101 for Part 505 Blue pens I have 450 of them.
No repetition. Nothing I can’t insert. Nothing I lose if I delete.
Third Normal Form X
Do in order
Must be in second normal form
All non-key attributes must be independent. Nothing should act like a primary key (unless the primary key); i.e. can’t be identified by another attribute in the row.
No transitive dependencies. The same record does not contain any data fields where data field A determines data field B.
(Customer#, Last Name, Address, City, Postal Code, Account Balance).
Postal Code uniquely ID’s a city. It’s not independent of city. Once you know the code, you know your city. City is not independent of postal code. It is uniquely ID’d by postal code. Which means postal code should be a primary key of another table.
(Customer #, Last Name, Address, Postal Code, Account Balance)
(Postal Code, City)
What are the basic requirements of Accounting Information Systems? X
- systematic recording of data
- logical, convenient and useful organisation of data
- ability to create useful reports from the data
- easy access to required information
Most accounting systems involve complex combinations of data stored in databases, processing software and hardware, that interact with one another to support specific storage and retrieval tasks.
Most AIS DB’s are relational (groups of related, 2D tables)
AIS’s must gather pertinent data & store the info in formats that enable managers to obtain timely answers to important questions.
What is a database? X
Large collection of organised data that can be accessed by multiple users and used by many different computer applications.
DB’s are used to store the data that comprise nearly all accounting systems. eg inventory, general ledger, production scheduling.
Not every collection of data is a DB - eg time card data in excel - too simple. Most commercial DB’s - large & complex collections of proprietary data that developers carefully design and protect and that form core of acc info systems.
What is a database management system (DBMS)? X
Specialised software packages that manipulate data in databases
What is a relational database? X
Groups of related two dimensional tables
Why are databases signifiant to AIS? X
Cannot overstate importance.
ORGANISE, ACCESS, PROCESS INFO EFFICIENTLY.
Extensive use of DB’s in nearly every acc system that influences financial reports.
eg. acc rec apps need vast info re customer’s acc, acc pay apps need vast info re suppliers etc
Databases that support AIS are relied upon to make key business decisions and conduct day to day operations.
Issues databases can raise for accounting profs? X
Critical information Volume Distribution Privacy Irreplaceable Data Need for Accuracy Internet Uses Big Data
Issues w DB’s: Critical information X
Sometimes the most important and valuable asset of a business
Issues w DB’s: Volume X
Enormous amounts of data (require substantial resources to design, use and maintain)
Issues w DB’s: Distribution X
Some DB centralised (single location). Others distributed (duplicated in local or regional computers as processing needs dictate). Distribution of data can make it difficult to ensure data accuracy, consistency, completeness; and to secure the info from unauthorised access.
Issues w DB’s: Privacy X
Often contain sensitive info. Needs to be protected from unauthorised access. Internal control procedures that protect DB - most critical.
Issues w DB’s: Irreplaceable Data X
Info contained in DB unique to the organisation that created it, typically priceless (makes security critical).
Issues w DB’s: Need for Accuracy X
Data stored in DB’s must be complete, comprehensive and accurate. Consequences of inaccurate data can be substantial. eg left v right limb in surgery.
Issues w DB’s: Internet Uses X
DB’s are critical components of both internal and external corporate web systems. DB’s store info re product info for online catalog sales, emails, product reg data, employment opps, stock prices. Internet apps often store customer entered data - online product orders. CC numbers, subscription info, registration data etc
Issues w DB’s: Big Data X
Big Data: Data so big, of such great volume cannot be captured, stored and analysed by traditional DB’s and existing hardware.
Create new opportunities for accountants and auditors b/c big data can reveal meaningful patterns, and produce info from data that were previously considered BG noise.
Big data - structured (DB’s) and unstructured data (CEO conf calls, press releases, blogs, social networking).
Historically unstructured data diff or impossible to analyse. Big declines in storage and processing costs have made big data analysis possible. Can use info to inform business decisions. Need to combine structured and unstructured info to maintain competitive advantage.
What makes the data in a DB useful? X
When it is stored efficiently and organised systematically.
Need to understand data hierarchy, record structures, database keys.
What is data hierarchy? X
Have to organise the data stored into a logical structure. Ascending order: Data Field (attribute/column) Record (tuple) File (table/relation) Database
What is a data field? X
aka attribute, column, field
information that describes a person, event, or thing in a DB. eg payroll file, data fields would be employee names, ID numbers, pay rates.
What is a record? X
aka a tuple, row
When fields combine to form a complete record.
A record stores all of the info about one entity.
eg information about one inventory item in an inventory file, one employee in a payroll file, one customer in a customer file.
What is a file? X
aka table
a set of common records eg set of customer records or inventory records.
Master files - permanent info eg part numbers and descriptions, customer names and addresses for the individual records.
Transaction files - transient info.
What is a Database? X
At the highest level, several files (or tables) form a complete database (i.e. a collection of tables that contain all the information needed for an accounting application).
eg Inventory application - part number master table, supplier table, price table, order transaction table etc.
Whatever is necessary to help end users organise, access or process inventory info efficiently.
