Get ahead with a $300 test prep scholarship
| Enter to win by Tuesday 9/24
Accounting Information Systems Chapter 4 - Relational Databases: Free Response Questions
Terms in this set (16)
Describe a major advantage of database systems over file-oriented transaction processing systems.
Database systems separate logical and physical views. This separation is referred to as program-data independence. Such separation facilitates developing new applications because programmers can concentrate on coding the application logic (what the program will do) and do not need to focus on how and where the various data items are stored or accessed. In the file-oriented transaction systems, programmers need to know physical location and layout of records which adds another layer of complexity to programming.
Explain the difference between file-oriented transaction processing systems and rational processing systems.
In file-oriented approach, many files managed by many users. This results in a significant increase in number of master files stored by an organization.
The disadvantages of file-oriented approach include:
- Data Redundancy
- Data Inconsistency
- Data-Program dependency
- Lack of Data Integration
- Lack of Compatibility
- Lack of Data Sharing
In the database approach, data is an organizational resource that is used by and managed for the entire organization. The program that manages and controls the data and the interfaces between data and application programs is called the database management system (DBMS).
The advantages include:
- Data Integration
- Data Sharing
- Minimal Data Redundancy and Data Inconsistencies
- Data Independence
- Cross-Functional Analysis
Describe what you think will be the main impact of database technology in your career.
Database systems have the potential to alter external reporting significantly. Considerable time and effort are currently invested in defining how companies should summarize and report accounting information to external users. Relational databases have the potential to increase the use and value of accounting information. Accountants must understand database systems so they can help design and use the AISs of the future. Such participation is important for ensuring that adequate controls are included in those systems to safeguard the data and ensure the reliability of the information produced.
What is the difference in logical view and physical view?
The logical view is how the user or programmer conceptually organizes and understands the data, such as data organized in a table. The physical view, on the other hand, refers to how and where the data are physically arranged and stored in the computer system.
Describe the different schemas involved in a database structure. What is the role of accountants in development of schemas?
A schema describes the logical structure of a database. There are three levels of schemas. First, the conceptual-level schema is an organization-wide view of the entire database listing all data elements and relationships between them. Second, an external-level schema is a set of individual user views of portions of the database, each of which is referred to as a subschema. Finally, an internal-level schema provides a low-level view of the database, includes descriptions about pointers, indexes, record lengths, etc. Accountants are primarily involved in the development of conceptual-and external-level schemas; however, database knowledgeable accountants may participate in developing an internal-level schema.
Describe a data dictionary.
The data dictionary contains information about the structure of the database. For each data element, there is a corresponding record in the data dictionary describing that element. Information provided for each element includes a description or explanation of the element; the records in which it is contained; its source, the length and type of the field in which it is stored, the programs in which it is used, the outputs in which it is contained, the authorized users of the element, and other names for the element.
Discuss the ways in which a well-designed DBMS will facilitate the three basic functions of creating, changing, and querying data.
A DBMS will use data definition, and data query languages in order to perform the three basic, essential data functions. Data definition is achieved using data definition language (DDL); data manipulation is achieved using data manipulation language (DML) which includes operations such as updating, inserting, and deleting portions of the database. Data query language (DQL) is used to retrieve, sort, order, and present subsets of data in response to user queries. A DBMS will probably also include a report writer, which is a language that simplifies report creation.
List the four DBMS "languages" and describe who uses each and for what purpose.
DDL is the data definition language used by the database administrator (DBA) to create, initialize, describe logical views, and specify security limits. The DML is the data manipulation used by application programmers who embed these action commands into application to access data in the database. The DQL is the data query language used by IT professionals and users to interrogate the database by retrieving and presenting data in novel ways often on an ad hoc basis. The report writer is a language used by IT professionals and users that simplifies report creation so reports can be created according to user-specified format.
Describe the information that is contained in the data dictionary.
The data dictionary is an overall storehouse of information about the system, and serves as a central clearinghouse for all documentation. An analyst uses the data dictionary to collect, document, and organize specific facts about the system, including the contents of data flows, data stores, entities, and processes.
Inputs to the dictionary include records of new or deleted data elements and changes in name, descriptions, or uses of existing elements. Outputs include reports that are useful to programmers, database designers, and IS users in designing and implementing the system, documenting the system, and creating an audit trail.
Explain the types of attributes that tables possess in a relational database.
Primary Key: An attribute or combination of attributes that can be used to uniquely identify a specific row (record) in a table.
Foreign Key: An attribute in one table that is a primary key in another table. Used to link the two tables.
Non-Key Attributes: Store important information about the entity.
Explain two advantages semantic data modeling has over normalization when designing a relational database.
Semantic data modeling takes advantage of a system designer's knowledge about the business policies and practices of an organization. This is of great benefit in the design of transaction processing databases. Also, since the database model is created around the policies and practices of an organization, communications with the future database users is facilitated. The result is that the system will more closely meet the needs of the intended user.
Discuss redundancy as it applies to database design.
Redundancy has been called an enemy of relational databases. There are several problems that may occur when redundant data is stored in a database. First, the database becomes larger than it needs to be, since duplicate facts are being stored within it. Second, a situation may occur where only one instance of redundant data is updated or purged. The result is that the accuracy and integrity of the database suffers, since users may be relying on such inaccurate or incorrect redundant data. Redundancy can also make file maintenance unnecessarily time consuming and error-prone when human intervention is required. The problems discussed above have been identified as anomalies of a relational database. There are three specific anomalies connected with redundancy: the update anomaly, the insert anomaly, and the delete anomaly. A well-designed relational database will attempt to reduce or eliminate the number of instances of redundant data. The best way to achieve such a goal is proper design of the database for the needs of a specific organization.
Enterprise resource planning (ERP) systems overcome these problems as they integrate all aspects of a company's operations with a traditional AIS. Most large and many medium-sized organizations use ERP systems to coordinate and manage their data, business processes, and resources. The ERP system collects, processes, and stores data and provides the information managers and external parties need to assess the company.
Explain the relational database data model.
A data model is an abstract representation of the contents of a database. The majority of new DMBS use what is called the relational data model. Using this model, everything in the database is stored in the form of tables, known as relations. Keep in mind that this is the conceptual- and external-level schemas (which describes the logical structure of a database), not the actual physical structure of the database itself. In the concept of relations, both rows and columns comprise the database tables. Each row in a relation (table) is called a tuple. Tuples contain data about a specific occurrence of the type of entity represented by that table. For example, in a sales table, each tuple may contain all of the information about a specific customer. Each column in a relation contains information about one specific attribute of that entity. Using the sales example again, the columns in such a table may represent specific characteristics about each sales transaction.
What are the basic requirements when logically designing a relational database model?
1. Every column must be single valued.
2. Primary keys must contain data (not null).
3. Foreign keys must contain the same data as the primary key in another table.
4. All other attributes must identify a characteristic of the table identified by the primary key.
Seacrest Bedding is changing from a file-oriented system to a relational database system. Identify and design at least three tables that would be needed to capture data for a sales transaction. Each table should include a primary key, three non-key attributes, and foreign keys as necessary. Make up data values for two records within each table.
Customer table; ID (primary key), name, street address, city, state, zip code, phone number, credit limit, salesperson ID (foreign key)
Salesperson table; ID (primary key), name, street address, city, state, zip code, phone number, hire date, commission rate, base salary
Sales table; sales invoice number (primary key), date, amount, sales tax, shipping charge, shipping method, customer ID (foreign key), salesperson ID (foreign key)
Inventory table; item ID (primary key), description, size, color, warehouse location, list price, quantity on hand
Sales-Inventory table; sales invoice number and item ID (concatenated primary key), quantity sold, sales price, extended amount.
6 Advantages of Database Systems
1. Data Integration: Master files are combined into large "pools" of data that many application programs access. An example is an employee database that consolidates payroll, personnel, and job skills master files.
2. Data Sharing: Integrated data are more easily shared with authorized users. Databases are easily browsed to research a problem or obtain detailed information underlying a report.
3. Minimal data redundancy and data inconsistencies: Because data items are usually stored only once, data redundancy and data inconsistencies are minimized.
4. Data independence: Because data and the programs that use them are independent of each other, each can be changed without changing the other. This facilitates programing and simplifies data management.
6. Cross-Functional Analysis: In a database system, relationships, such as the association between selling costs and promotional campaigns, can be explicitly defined and used in the preparation of management reports.