materi SIA chapter 4
CHAPTER 4
• Relational Databases
• INTRODUCTION
• Questions to be addressed in this chapter:
– How are databases different than file-based legacy systems?
– Why are databases important and what is their advantage?
– What is the difference between logical and physical views of a database?
– What are the fundamental concepts of database systems such as DBMS, schemas, the data dictionary, and DBMS languages?
– What is a relational database, and how does it organize data?
– How are tables structured to properly store data in a relational database?
• INTRODUCTION
• Relational databases underlie most modern integrated AISs.
– They are the most popular type of database used for transaction processing.
– In this chapter, we’ll define the concept of a database.
• FILE VS. DATABASES
• Let’s examine some basic principles about how data are stored in computer systems.
– An entity is anything about which the organization wishes to store data. At your college or university, one entity would be the student.
• FILE VS. DATABASES
– Information about the attributes of an entity (e.g., the student’s ID number and birth date) are stored in fields.
• FILE VS. DATABASES
– All the fields containing data about one entity (e.g., one student) form a record.
– The example below shows the record for Artie Moore.
• FILE VS. DATABASES
– A set of all related records forms a file (e.g., the student file).
– If this university only had three students and five fields for each student, then the entire file would be depicted below.
• FILE VS. DATABASES
– A set of interrelated, centrally coordinated files forms a database.
• FILE VS. DATABASES
• Database systems were developed to address the problems associated with the proliferation of master files.
– For years, each time a new information need arose, companies created new files and programs.
– The result: a significant increase in the number of master files.
• FILE VS. DATABASES
• This proliferation of master files created problems:
– Often the same information was stored in multiple master files.
– Made it more difficult to effectively integrate data and obtain an organization-wide view of the data.
– Also, the same information may not have been consistent between files.
• If a student changed his phone number, it may have been updated in one master file but not another.
• FILE VS. DATABASES
• A database is a set of inter-related, centrally coordinated files.
• FILE VS. DATABASES
• The database approach treats data as an organizational resource that should be used by and managed for the entire organization, not just a particular department.
• A database management system (DBMS) serves as the interface between the database and the various application programs.
• FILE VS. DATABASES
• The combination of the database, the DBMS, and the application programs that access the database is referred to as the database system.
• FILE VS. DATABASES
• The person responsible for the database is the database administrator.
• As technology improves, many large companies are developing very large databases called data warehouses.
• IMPORTANCE AND ADVANTAGES OF DATABASE SYSTEMS
• Database technology is everywhere.
– Most new AISs implement a database approach.
– Virtually all mainframe computer sites use database technology.
– Use of databases with PCs is growing also.
• IMPORTANCE AND ADVANTAGES OF DATABASE SYSTEMS
• As accountants, you are likely to audit or work for companies that use database technology to store, process, and report accounting transactions.
– Many accountants work directly with databases and will enter, process, and query databases.
– Some will develop and evaluate internal controls necessary to ensure database integrity.
– Others will be involved in the design and management of databases.
• IMPORTANCE AND ADVANTAGES OF DATABASE SYSTEMS
• Database technology provides the following benefits to organizations:
– Data integration
• IMPORTANCE AND ADVANTAGES OF DATABASE SYSTEMS
• Database technology provides the following benefits to organizations:
– Data integration
– Data sharing
• IMPORTANCE AND ADVANTAGES OF DATABASE SYSTEMS
• Database technology provides the following benefits to organizations:
– Data integration
– Data sharing
– Reporting flexibility
• IMPORTANCE AND ADVANTAGES OF DATABASE SYSTEMS
• Database technology provides the following benefits to organizations:
– Data integration
– Data sharing
– Reporting flexibility
– Minimal data redundancy and inconsistencies
• IMPORTANCE AND ADVANTAGES OF DATABASE SYSTEMS
• Database technology provides the following benefits to organizations:
– Data integration
– Data sharing
– Reporting flexibility
– Minimal data redundancy and inconsistencies
– Data independence
• IMPORTANCE AND ADVANTAGES OF DATABASE SYSTEMS
• Database technology provides the following benefits to organizations:
– Data integration
– Data sharing
– Reporting flexibility
– Minimal data redundancy and inconsistencies
– Data independence
– Central management of data
• IMPORTANCE AND ADVANTAGES OF DATABASE SYSTEMS
• Database technology provides the following benefits to organizations:
– Data integration
– Data sharing
– Reporting flexibility
– Minimal data redundancy and inconsistencies
– Data independence
– Central management of data
– Cross-functional analysis
• DATABASE SYSTEMS
• Logical and Physical Views of Data
– In file-oriented systems, programmers must know the physical location and layout of records used by a program.
• They must reference the location, length, and format of every field they utilize.
• When data is used from several files, this process becomes more complex.
• DATABASE SYSTEMS
• Database systems overcome this problem by separating the storage and use of data elements.
– Two separate views of the data are provided:
• Logical view
• DATABASE SYSTEMS
• Database systems overcome this problem by separating the storage and use of data elements.
– Two separate views of the data are provided:
• Logical view
• Physical view
• DATABASE SYSTEMS
• Database systems overcome this problem by separating the storage and use of data elements.
– Two separate views of the data are provided:
• Logical view
• Physical view
– Separating these views facilitates application development, because programmers can focus on coding the logic and not be concerned with storage details.
• DATABASE SYSTEMS
• The DBMS handles the link between the physical and logical views of the data.
– Allows the user to access, query, and update data without reference to how or where it is physically stored.
– The user only needs to define the logical data requirements.
• DATABASE SYSTEMS
• Separating the logical and physical views of data also means users can change their conceptualizations of the data relationships without making changes in the physical storage.
• The database administrator can also change the physical storage of the data without affecting users or application programs.
• DATABASE SYSTEMS
• Schemas
– A schema describes the logical structure of a database.
– There are three levels of schema.
• Conceptual level
• DATABASE SYSTEMS
• Schemas
– A schema describes the logical structure of a database.
– There are three levels of schema.
• Conceptual level
• External level
• DATABASE SYSTEMS
• Schemas
– A schema describes the logical structure of a database.
– There are three levels of schema.
• Conceptual level
• External level
• Internal level
• DATABASE SYSTEMS
• The DBMS uses the mappings to translate a request by a user or program for data (expressed in logical names and relationships) into the indexes and addresses needed to physically access the data.
• DATABASE SYSTEMS
• Accountants are frequently involved in developing conceptual- and external-level schema.
• An employee’s access to data should be limited to the subschema of data that is relevant to the performance of his job.
• DATABASE SYSTEMS
• The Data Dictionary
– A key component of a DBMS is 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.
• DATABASE SYSTEMS
• 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.
– Other names for the element.
• DATABASE SYSTEMS
- Accountants should participate in the development of the data dictionary because they have a good understanding of the data elements in a business organization, as well as where those elements originate and how they are used.
• DATABASE SYSTEMS
• The DBMS usually maintains the data dictionary.
– It is often one of the first applications of a newly implemented database system.
– Inputs to the dictionary include:
• Records of new or deleted data elements.
• Changes in names, 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.
– Creating an audit trail.
• DATABASE SYSTEMS
• DBMS Languages
– Every DBMS must provide a means of performing the three basic functions of:
• Creating a database
• Changing a database
• Querying a database
• DATABASE SYSTEMS
• DBMS Languages
– Every DBMS must provide a means of performing the three basic functions of:
• Creating a database
• Changing a database
• Querying a database
• DATABASE SYSTEMS
• Creating a database:
– The set of commands used to create the database is known as data definition language (DDL). DDL is used to:
• Build the data dictionary
• Initialize or create the database
• Describe the logical views for each individual user or programmer
• Specify any limitations or constraints on security imposed on database records or fields
• DATABASE SYSTEMS
• DBMS Languages
– Every DBMS must provide a means of performing the three basic functions of:
• Creating a database
• Changing a database
• Querying a database
• DATABASE SYSTEMS
• Changing a database
– The set of commands used to change the database is known as data manipulation language (DML). DML is used for maintaining the data including:
• Updating data
• Inserting data
• Deleting portions of the database
• DATABASE SYSTEMS
• DBMS Languages
– Every DBMS must provide a means of performing the three basic functions of:
• Creating a database
• Changing a database
• Querying a database
• DATABASE SYSTEMS
• Querying a database:
– The set of commands used to query the database is known as data query language (DQL). DQL is used to interrogate the database, including:
• Retrieving records
• Sorting records
• Ordering records
• Presenting subsets of the database
– The DQL usually contains easy-to-use, powerful commands that enable users to satisfy their own information needs.
• DATABASE SYSTEMS
• Report Writer
– Many DBMS packages also include a report writer, a language that simplifies the creation of reports.
– Users typically specify:
• What elements they want printed
• How the report should be formatted
– The report writer then:
• Searches the database
• Extracts specified data
• Prints them out according to specified format
• DATABASE SYSTEMS
• Users typically have access to both DQL and report writer.
• Access to DQL and DML are typically restricted to employees with administrative and programming responsibilities.
• RELATIONAL DATABASES
• A DBMS is characterized by the type of logical data model on which it is based.
– A data model is an abstract representation of the contents of a database.
– Most new DBMSs are called relational databases because they use the relational model developed by E.F. Codd in 1970.
• RELATIONAL DATABASES
• The relational data model represents everything in the database as being stored in the forms of tables (aka, relations).
• RELATIONAL DATABASES
• This model only describes how the data appear in the conceptual- and external-level schemas.
• The data are physically stored according to the description in the internal-level schema.
• RELATIONAL DATABASES
• Alternatives for Storing Data
– One possible alternate approach would be to store all data in one uniform table.
– For example, instead of separate tables for students and classes, we could store all data in one table and have a separate line for each student x class combination.
• RELATIONAL DATABASES
• Alternatives for Storing Data
– Another possible approach would be to store each student in one row of the table and create multiple columns to accommodate each class that he is taking.
• RELATIONAL DATABASES
• Basic Requirements of a Relational Database
– Every column in a row must be single valued.
• In other words, every cell can have one and only one value.
• In the student table, you couldn’t have an attribute named “Phone Number” if a student could have multiple phone numbers.
• There might be an attribute named “local phone number” and an attribute named “permanent phone number.”
• You could not have an attribute named “Class” in the student table, because a student could take multiple classes.
• RELATIONAL DATABASES
• Basic Requirements of a Relational Database
– The primary key cannot be null.
• The primary key uniquely identifies a specific row in the table, so it cannot be null, and it must be unique for every record.
• This rule is referred to as the entity integrity rule.
• RELATIONAL DATABASES
• Basic Requirements of a Relational Database
– A foreign key must either be null or correspond to the value of a primary key in another table.
• This rule is referred to as the referential integrity rule.
• The rule is necessary because foreign keys are used to link rows in one table to rows in another table.
• RELATIONAL DATABASES
• Basic Requirements of a Relational Database
– All non-key attributes in a table should describe a characteristic of the object identified by the primary key.
• Could nationality be a non-key attribute in the student table?
• Could advisor’s nationality be a non-key attribute in the student table?
• RELATIONAL DATABASES
• The preceding four constraints produce a well-structured (normalized) database in which:
– Data are consistent.
– Redundancy is minimized and controlled.
• In a normalized database, attributes appear multiple times only when they function as foreign keys.
• The referential integrity rule ensures there will be no update anomaly problem with foreign keys.
• RELATIONAL DATABASES
• An important feature is that data about various things of interest (entities) are stored in separate tables.
– Makes it easier to add new data to the system.
• You add a new student by adding a row to the student table.
• You add a new course by adding a row to the course table.
• Means you can add a student even if he hasn’t signed up for any courses.
• And you can add a class even if no students are yet enrolled in it.
– Makes it easy to avoid the insert anomaly.
• Space is also used more efficiently than in the other schemes. There should be no blank rows or attributes.
• RELATIONAL DATABASES
• Deletion of a class for a student would cause the elimination of one record in the student x class table.
– The student still exists in the student table.
– The class still exists in the class table.
– Avoids the delete anomaly.
• RELATIONAL DATABASES
• There are two basic ways to design well-structured relational databases.
– Normalization
– Semantic data modeling
• RELATIONAL DATABASES
• There are two basic ways to design well-structured relational databases.
– Normalization
– Semantic data modeling
• RELATIONAL DATABASES
• Normalization
– Starts with the assumption that everything is initially stored in one large table.
– A set of rules is followed to decompose that initial table into a set of normalized tables.
– Objective is to produce a set of tables in third-normal form (3NF) because such tables are free of update, insert, and delete anomalies.
– Approach is beyond the scope of this book but can be found in any database textbook.
• RELATIONAL DATABASES
• There are two basic ways to design well-structured relational databases.
– Normalization
– Semantic data modeling
• RELATIONAL DATABASES
• Semantic data modeling (covered in detail in Chapter 15)
– Database designer uses knowledge about how business processes typically work and the information needs associated with transaction processing to draw a graphical picture of what should be included in the database.
– The resulting graphic is used to create a set of relational tables that are in 3NF.
• RELATIONAL DATABASES
• Advantages over simply following normalization rules:
– Semantic data modeling uses the designer’s knowledge about business processes and practices; it therefore facilitates efficient design of transaction processing databases.
– The resulting graphical model explicitly represents information about the organization’s business processes and policies and facilitates communication with intended users.
• RELATIONAL DATABASES
• Creating Relational Database Queries
– Databases store data for people and organizations.
– To retrieve the data, you query the database and its tables.
– Chapter 4 of your textbooks provides some samples of database queries in Microsoft Access.
– Try these on your own and/or with your instructor in class.
• DATABASE SYSTEMS AND THE FUTURE OF ACCOUNTING
• Database systems may profoundly affect the fundamental nature of accounting:
– May lead to abandonment of double-entry accounting, because the redundancy of the double entry is not necessary in computer data processing.
– May also alter the nature of external reporting.
• EXAMPLE: External users could have access to the company’s database and manipulate the data to meet their own reporting needs.
• DATABASE SYSTEMS AND THE FUTURE OF ACCOUNTING
• The use of accounting information in decision making will be enhanced by:
– Powerful querying capabilities that accompany database packages.
– The ability to accommodate multiple views of the same underlying phenomenon.
– The ability to integrate financial and operational data.
• DATABASE SYSTEMS AND THE FUTURE OF ACCOUNTING
• Accountants must become knowledgeable about databases so they can participate in developing the AIS of the future.
• They must help ensure that adequate controls are included to safeguard the data and assure its reliability.
• SUMMARY
• You’ve learned how databases differ from file-based legacy systems.
• You’ve learned why databases are important and what advantages they offer.
• You’ve learned how the logical and physical views of a database differ.
• You’ve learned about fundamental concepts of database systems such as DBMS, schemas, the data dictionary, and DBMS languages.
• You’ve learned what a relational database is and how it organizes data.
• You’ve learned how tables are structured to properly store data in a relational database.
Artikel Terkait
