Transcript Gelinas-Dull_8e_Chapter_06_Revised_September_2009

Chapter 6 – Relational Databases
and SQL
Accounting Information Systems 8e
Ulric J. Gelinas and Richard Dull
© 2010 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated,
in whole or in part, except for use as permitted in a license distributed with a certain product
or service or otherwise on a password-protected website for classroom use
Learning Objectives
• Understand techniques used to model complex accounting
phenomena in an entity-relationship (E-R) diagram.
• Develop E-R diagrams that model effective accounting
database structures using the Resources- Events-Agents
(REA) approach.
• Recognize the components of relational tables and the keys to
effective relational database design.
• Understand use of SQL commands to create relational tables
during implementation of the model.
• Manipulate relational tables to extract the necessary data
during decision making.
2
REA Modeling - Entities
REA helps database designers define a complete set of
entities and attributes.
Entity: anything in which we are interested that exists
independently:
– Resources - inventory, equipment, cash
– Events - orders, sales, purchases
– Agents - customers, employees, vendors
An instance of an entity is one specific thing of the type
defined by the entity.
– The agent entity EMPLOYEE in a company with three
employees might have instances of Marge Evans,
Roberto Garcia, and Arte Singh.
– In a relational database, the entity is represented as a
table and the three instances of the entity are
represented as rows in the table.
3
REA Modeling – Attributes
Attribute - item of data that characterizes an entity
or relationship
– To fully describe a CLIENT we need to record
several attributes such as:
• Name, Address, Contact_Person, and
Phone_Number.
– Sometimes, attributes are a combination of parts
that have unique meanings of their own.
– Attributes that consist of multiple sub-attributes are
referred to as composite attributes.
4
Attribute Hierarchy for the Entity
CLIENT
Attributes describe an entity – a client has a name,
address, contact_person and phone-number.
5
Key Attributes
• A unique attribute/value is needed to
locate the desired record in the
database.
– An attribute with a unique value is known
as a key attribute.
– In implementing the database, the key
attribute becomes the primary key.
6
Symbols used in E-R and REA
Diagrams
7
Relationships
•
Relationships are associations between entities.
•
Entities must be logically linked to show relationships.
•
Relationships map and define how data can be extracted from
the database.
•
Mapping is the development of the E-R diagram.
•
A three-step strategy is generally most effective in identifying
relationships that should be included in a model.
1. Identify users’ information requirements to determine if
relationships in the data model can fulfill them.
2. Evaluate each of the entities in pairs to determine if one
entity in the pair provides a better description of an attribute
contained in the other entity in the pair.
3. Evaluate each entity to determine if there is a need for two
occurrences of the same entity type to be linked.
8
REA Approach
• Figure 6.3 (Slide 10) shows three entities and their attributes.
• One event and two agents have been identified for the business
process of billing for professional services.
– The WORK_COMPLETED entity is an event.
– The CLIENT and EMPLOYEE entities are agents.
• CLIENT is an entity but not an attribute of WORK_ COMPLETED.
– CLIENT improves the description of an attribute for the work
completed—the client for whom the work was performed.
– This descriptive value suggests that a relationship exists between
the CLIENT entity and the entity capturing the completed work
– The need for defining relationships can be identified by examining
the prescribed entities as pairs to identify logical linkages that
would improve the description of an entity’s attributes.
9
Relationship Types in the REA Model
of the Client Billing Business Process
10
Recursive Relationship
• A relationship between two different instances of an entity.
• When one employee supervises other employees, this relationship
should be shown in the database.
• Employees and supervisors can by shown as separate entities in the
model.
– This separate entity approach yields data redundancies when the
supervisor is supervised by a third employee.
– It is easier and logically correct to use a recursive relationship to the
entity, EMPLOYEE.
• In a recursive relationship, a link is created between the employee and
his/her supervisor.
• As shown in part (b) of Figure 6.3 (Slide 10) the diamond represents the
recursive relationship, Supervises, just as it is used to show any
relationship – i.e. the Works_For relationship in part (a).
11
Constraints in the E-R Diagram
• Cardinality is the most common
constraint specified in E-R diagrams.
• The other meaningful constraint that
may be specified is participation.
– The participation constraint specifies the
degree of minimum participation of one
entity in the relationship with the other
entity.
12
Constraints in the E-R Diagram
• The participation (minimum) constraint
provide more information, but is used
less frequently than the cardinality
(maximum) constraint.
• Both types of constraints and notation
are used because, members of the
development team, auditors, and
users, need to communicate using the
methods selected by the organization
where they are working.
13
Constraints in the E-R Diagram
• The “many” cardinality in part (a) of Figure 6.4 (Slide 15) specifies the
maximum participation in the relationship.
• The participation constraints appear in Figure 6.4, part (b).
– In the Works relationship, not all employees are billable.
• Some employees are new
• Others might be involved with training or new business
development.
– The minimum participation in the relationship can be zero or one.
– The notation (0,N) reflects the range of zero to many occurrences
of work being completed on client projects, where the numbers
reflect (minimum, maximum).
– The notation (1,1) illustrates that for any given occurrence of work
completed for a client, the maximum of one employee providing the
specific service still holds. The (1,1) relationship reflects that there
is a required participation of one, and only one, employee.
14
Relationship Constraints in the
Client Billing Business Process
15
Relational Database Concepts
• A relation is a collection of data representing multiple
occurrences of a resource, event, or agent.
– These relations correspond to the entities in the E-R
model and the REA model.
• A tuple is a set of data that describes a single instance
of the entity represented by a relation.
– One employee is an instance of the EMPLOYEE
relation.
• Attributes (or fields), as in an E-R model, represent an
item of data that characterizes an object, event, or agent.
16
Example of a Relation
17
Developing an REA Model
• Objective is to integrate the data in a
way that allows managers and other
users access to the information they
need to perform effectively.
• Figure 6.5 (Slide 19) presents the
integrated REA data model for the client
billing and human resources business
processes.
18
An
Integrated
REA
Model for
the Client
Billing and
Human
Resources
Processes
19
Relationship with Cardinalities
20
Relationship without
Cardinalities
21
Steps in Mapping an REA Model
to a Relational DBMS
1.
Create separate relational table for each entity.
2.
Determine the primary key for each relation. The
primary key must uniquely identify any row within
the table.
3.
Determine the attributes for each of the entities.
4.
Implement the relationships among the entities by
ensuring that the primary key in one table also
exists as an attribute in every table for which there
is a relationship specified in the REA diagram.
5.
Determine attributes, if any, for relationship tables.
22
Mapping an REA Model to a
Relational DBMS
1.
Create separate relational table for each entity.
– First specify the database schema before expanding the
relations to account for specific tuples.
– Notice that each of the entities in Figure 6.5 (Slide 19)
has become a relation in Figure 6.10 (Slide 23).
– To complete the schema, however, steps 2 and 3 also
must be completed.
2.
Determine the primary key for each relation. The
primary key must uniquely identify any row within
table.
23
Mapping an REA Model to a
Relational DBMS
3.
Determine attributes for each of the entities
– In Figure 6.5, a complete REA model includes all the
attributes, including the key attribute.
– The key attribute specified in the REA model is
matched to the corresponding attribute in the relation.
• Employee_Number in the EMPLOYEE agent entity.
– To create a composite primary key, break the key
down into its component subattributes.
•
In the implementation of the WORK_COMPLETED
event relation, Employee_No, Date, and Client_No
are three distinct attributes in the relation, but also
combine to form the composite primary key.
24
Schema for the Client Billing
and Human Resources Portion
of the Data
25
Mapping an REA Model to a
Relational DBMS
4.
Implement relationships among the entities by ensuring the
primary key in one table exists as an attribute in every table
for which there is a relationship specified in the REA diagram.
–
With the availability of the full REA model, the mapping of
the relationships in the model to the relationships in the
relational schema is straightforward.
–
References to the key attributes of one entity are captured
by including a corresponding attribute in the other entity
that participates in the relationship.
–
All of the relationships in Figure 6.5 (Slide 19) are 1:N
relationships, which simplifies the process.
26
Mapping an REA Model to a
Relational DBMS - Step 4 cont’d.
One-to-many (1:N or N:1) relationships are
implemented by including the primary key of the table
on the one side of the relationship as an attribute in
the table on the many side of the relationship (Figure
6.5 – Slide 19).
– The recursive relationship with EMPLOYEE uses
Supervisor_No to identify the correct EMPLOYEE
as the supervisor.
– The linking between these relationships in the
schema are drawn in Figure 6.11 (Slide 26).
For one-to-one (1:1) relationships, follow the same
steps used for 1:N relationships, starting with either
table.
27
Referential Constraints for the
Relational Schema
28
Mapping an REA Model to a
Relational DBMS - Step 4 cont’d.
Many-to-many (M:N) relationships are implemented by creating
a new relation whose primary key is a composite of the primary
keys of the relations to be linked.
– There are no M:N relationships in the current REA model.
A relationship between the EMPLOYEE and CLIENT entities
would be an M:N relationship.
This creates problems because these tables (that have been
normalized) cannot store multiple client numbers in a single
EMPLOYEE tuple. A single CLIENT tuple cannot store multiple
employee numbers.
Figure 6.12 (Slide 28) shows a M:N relation linking the
EMPLOYEE and CLIENT.
29
Linking Two Relations in a Manyto-Many Relationship
30
Mapping an REA Model to a
Relational DBMS
5. Determine attributes, if any, for
relationship tables.
– In the extended version of the REA
model, the attributes map directly
to the relations.
– The implementation of the schema is
shown in Figure 6.13 (Slide 30).
31
Implemented
Relational
Schema
32
SQL : Relational DB Query
Language
• Used to
– define database systems.
– query DB for information.
– generate reports from DB.
– access DB from within programs.
• De facto standard DB language
33
Constructing DBs using SQL
• Assign name to relation.
• Assign names to each attribute.
• Specify data type for each attribute.
• Specify constraints, when appropriate, on
the attributes.
34
SQL Commands
• CREATE: Use to generate the structure of the relation.
• INSERT: Use to enter current data into the structure (add single
tuple to an existing relation).
• DELETE: Use to remove tuple from a relation.
– Requires specification of the table name and inclusion of a
WHERE condition, which is used to identify the unique tuple
or tuples for deletion.
• UPDATE: Use to change one or more attribute values for one or more
tuples in a table.
– Identify the tuple using the “WHERE” condition; change the
existing values by using a “SET” command to set the new
values for the database.
•
SELECT/WHERE: Use to query the database
− See Figure 6.16 (Slide 34) for a single query example for billing
information .
− See Figure 6.17 (Slide 35) for a double query example for billing
information .
35
SQL Commands for Creating Database Relations
36
SQL Commands to Add Data to the Database
37
Single Query Billing Information
38
Double Query Client Billing
Information
39
Generating Reports
• Ad-hoc Reports
– Users can easily manipulate the base level tables to
generate information on an ad hoc (as needed) basis.
– The most effective way to provide data availability to
users when information needs constantly change.
• Regular Reports
– Users can create alternative ways to view the data that
already exists in the database.
– Data are not copied to a second physical location in the
database.
– A view creates the appearance of a different set of tables
in the format a user wants.
• See Figure 6.18 (Slide 37) for the schema for the Client
Billing and Human Resources Portion of the Database.
• See Figure 6.19 (Slide 38) for creating a view of the Client
Billing Detail with SQL.
40
Schema for the Client Billing
and Human Resources Portion
of the Database
41
Creating a View of the Client
Billing Detail with SQL
42
Query to Extract Client Billing
Data for Hasbro, Inc.
43