6Relational Chapter Databases and SQL
TRUE/FALSE
1. An entity in an accounting system can be classified as a resource, event or agent about which data are
collected.
ANS: T
2. Attributes are often called fields.
ANS: T
3. A model is a complex representation of a simple entity or phenomenon.
ANS: F
4. A primary attribute is the attribute whose value is unique for every entity that will appear in the database.
ANS: F
5. The customer street address is often used as the key attribute in an REA diagram.
ANS: F
6. In an REA diagram, the rectangle is used to represent the composite attribute.
ANS: F
7. The first step in REA is evaluating the attributes.
ANS: F
8. Relationships map and define the way in which data can be extracted from a database.
ANS: T
9. A recursive relationship occurs between two different instances of an entity.
ANS: T
10. Representing supervisors and their employees as separate entities in the model can lead to data redundancy.
ANS: T
11. A recursive relationship is created when there is an employee being supervised, and another employee
is the supervisor.
ANS: T
12. The cardinality constraint is used less frequently than the participation constraint.
ANS: F
13. The cardinality constraint is used to specify both the minimum and maximum participation of one entity in the relationship with the other entity.
ANS: F
14. The notation 1,N is used to specify the relationship type of one to many.
ANS: T
15. The notation of (1,1) is used to specify the relationship type of one to many.
ANS: F
16. REA stands for resources, entities and agents
ANS: F
17. The fundamental requirement for moving toward an event driven model is the complete integration of
data related to an organizations business events.
ANS: T
18. A legacy system is one that has existed in an organization for less than one year.
ANS: F
19. A collection of data representing multiple occurrences of a resource, event, or agent is known as a relation.
ANS: T
20. A set of data that describes an instance of the entity represented by a relation is known as a tuple.
ANS: T
21. A missing value is called a non-null.
ANS: F
22. The secondary key is specified to uniquely identity each tuple in the relation.
ANS: F
23. SQL is a powerful database language that can be used to define database systems, query the database
for information, generate reports from the database, and access databases from within programs using
embedded commands.
ANS: T
24. Relational integrity specifies that for every attribute in one relation that has been specified to allow reference to another relation, the tuple being referenced must remain intact.
ANS: F
25. A relation in an ER model is the same as an entity in the REA model.
ANS: F
26. Most DBMS available today that include SQL also include mechanisms within the DBMS itself for establishing and enforcing referential integrity.
ANS: T
27. The INSERT command is used to add a single tuple to an existing relation.
ANS: T
28. The UPDATE command demonstrates one of the weaknesses in most forms of SQL.
ANS: F
29. The DELETE command is used when we want to change one or more attribute values.
ANS: F
30. Queries of a database are driven by SELECT commands.
ANS: T
MULTIPLE CHOICE
1. An entity can be classified as all of the following except
a. resource
b. event
c. tuple
d. agent
ANS: C
2. A(n) ____ of an entity is on specific thing of the type defined by the entity.
a. agent
b. composite attribute
c. attribute
d. instance
ANS: D
3. A(n) ____ is an item of data that characterizes an entity or relationship
a. agent
b. composite attribute
c. attribute
d. instance
ANS: C
4. Which of the following is the entity or agent?
a. client
b. client number
c. client name
d. client street address
ANS: A
5. Which of the following is the key attribute?
a. client
b. client number
c. client name
d. client street address
ANS: B
6. Which of the following is an attribute but least likely to be the key attribute?
a. client
b. client number
c. client name
d. client street address
ANS: D
7. Which of the following is a composite attribute?
a. client
b. client number
c. client name
d. client street address
ANS: D
8. A data field in a traditional file is similar to a relational:
a. table
b. attribute
c. tuple
d. row
ANS: B
9. For the following entity and attribute model, what would be the key attribute of the Students entity?
a. social security number
b. name
c. address
d. street
ANS: A
10. A key attribute is the attribute whose value is:
a. undeterminable
b. unique
c. large
d. the same for all attributes
ANS: B
11. The mapping of the relationships between entities would be used in the development of a(n):
a. context diagram
b. logical DFD
c. physical DFD
d. REA Model
ANS: D
12. The sequence of steps in identifying relationships in a model is:
a. evaluate entities as pairs, identify user existing and desired information, evaluate each entity to determine if there is any need for two occurrences of the same entity type to be
linked
b. identify user existing and desired information, evaluate entities as pairs, evaluate each entity to determine if there is any need for two occurrences of the same entity type to be
linked
c. evaluate entities as pairs, evaluate each entity to determine if there is any need for two occurrences of the same entity type to be linked, identify user existing and desired information
d. evaluate each entity to determine if there is any need for two occurrences of the same entity type to be linked, evaluate entities as pairs, identify user existing and desired information
ANS: B
13. Which of the following statements about the participation constraint is false?
a. It specifies the degree of minimum participation of one entity in the relationship with the
other entity.
b. The constraint is either 1 or 0.
c. It is the most common constraint in E-R diagrams.
d. It provides more information than the cardinality constraint.
ANS: C
14. Which of the following is not a relationship type?
a. one-to-many
b. one-to-one
c. one-to-few
d. many-to-many
ANS: C
15. Which relationship type does (1, N) mean?
a. one-to-many
b. one-to-one
c. one-to-few
d. many-to-many
ANS: A
16. The cardinality constraint:
a. specifies the degree of minimum participation of one entity in the relationship with the
other entity.
b. provides more information than the participation constraint.
c. is the most common constraint specified in E-R diagrams.
d. all of the above.
ANS: C
17. Customers of Hanson Company, a home furnishings wholesaler, place orders by contacting a sales
representative via a toll-free number. Identify the constraints of the following data model relationship.
a. 1:1
b. 1:N
c. N:M
d. N:1
ANS: B
18. The constraint that is specifies the degree of minimum participation of one entity in the relationship
with the other entity is called:
a. cardinality constraint
b. participation constraint
c. economic constraint
d. logical constraint
ANS: B
19. Students at Macom University use an web-based course registration system. Identify the participation
constraint of the following data model relationship. The university maintains student records until they
graduate or do not enroll in a course for a 3-year period.
a. N:M
b. 1:N
c. (1,1):(0,N)
d. (1,1):(1,N)
ANS: D
20. Enterprise systems are integrated software packages designed to provide complete integration of:
a. an organization's accounting information system
b. an organization's business data
c. an organization's business information processing systems and all related data
d. an organization's management
ANS: C
21. A set of data that describes an instance of the entity represented by a relation is known as a(n) ____.
a. tuple
b. resource
c. agent
d. event
ANS: A
22. Legacy systems do not usually have which of the following characteristics?
a. developed on previous hardware (and/or software) platforms
b. are easily adaptable to enterprise systems
c. have been used by an organization for a long period of time
d. all of the above are typical characteristics of legacy systems
ANS: B
23. In a relational database, a relation is defined as a collection of data representing multiple occurrences
of the following except:
a. a resource
b. an event
c. an activity
d. an agent
ANS: C
24. ____ are defined as a set of data that describes an instance of the entity represented by a relation.
a. Attributes
b. Tuples
c. Primary keys
d. Candidate keys
ANS: B
25. ____ specifies that for every attribute value in one relation that has been specified in order to allow
reference to another relation,the tuple being referenced must remain intact.
a. Cardinality constraint
b. Participation constraint
c. Referential integrity
d. Economic constraint
ANS: C
26. Which of following is not a step in the process for specifying relations based on REA diagrams?
a. Determine the attributes for each of the entities.
b. Determine the number of tables.
c. Create a separate relational table for each entity.
d. Implement the relationships among the entities.
ANS: B
27. A situation in which a particular supplier provides more than one type of inventory item to a firm and a
particular type of inventory item is acquired from more than a single supplier is called a(n):
a. one-to-one relationship
b. one-to-many relationship
c. many-to-many relationship
d. entity relationship
ANS: C
28. The following E-R diagram shows a M:N relationship between two relations. To map this E-R diagram into a logical database model:
a. divide the content of the Course relation to establish two relations having 1:N relation-
ships
b. add the primary key of the Student relation as an attribute in the Course relation
c. create a new relation whose primary key is a composite of the primary keys of two relations
d. add the primary key of the Course as relation an attribute in the Student relation
ANS: C
29. Which of the following is not a proper SQL command for creating database relations?
a. Create table employee
b. Create table training_completed
c. Create client
d. Alter table release_time
ANS: C
30. To generate a standard report of a relational database, which of the following SQL commands would
be used?
a. Select report
b. Select view
c. Create report
d. Create view
ANS: D
31. Which of the following relational commands would a manager use to combine the data from two related relations into a third relation?
a. select
b. project
c. join
d. display
ANS: A
32. In a relational database, the entity is represented as a(n):
a. table
b. network
c. field
d. attribute
ANS: A
33. Which is not a step in creating a relation using SQL?
a. assign the relation a name
b. assign each attribute a name
c. specify the constraints on the attributes
d. specify the attributes of the constraints
ANS: D
34. Which is not an SQL command in which data can be changed in the database?
a. INSERT
b. DELETE
c. UPDATE
d. CREATE
ANS: D
35. Which of the following is a valid SQL command?
a. MODIFY
b. INSERT
c. ADD
d. APPEND
ANS: B
36. The SELECT statement has all but one of the following parts
a. a list of attributes that we wish to SELECT from the database
b. a WHEN clause
c. a list of tables where these attributes can be found
d. a WHERE clause
ANS: B
The following table is a view of BILLING_HOURS from the various data elements in High-Tech
Corp.'s relational database.
BILLING_HOURS
Employee_No
B122
A632
B122
Client_No
F26768
F11555
F26768
Date
20010823
20010823
20010824
Hours
8
7
8
Billing_Rate
150
100
150
A632
F11555
20010824
6
100
B122
A632
F26768
F11555
20010825
20010825
9
10
150
100
37. Which of the following SQL statements would extract the employee number and date for every instance that an employee worked more than 8 hours any particular day?
a. SELECT
Employee_No, Date
FROM
BILLING_HOURS
WHERE
Hours>8
b. QUERY
Employee_No, Date
FROM
BILLING_HOURS
WHERE
Hours>8
c. SELECT
WHERE
Employee_No, Date
Hours>8
d. QUERY
SELECT
WHERE
BILLING_HOURS
Employee_No, Date
Hours>8
ANS: A
38. Which of the following SQL statements would extract the employee number and date for every instance that had a billing rate of $150?
a. SELECT
Employee_No, Date
FROM
BILLING_HOURS
WHERE
Rate=150
b. QUERY
FROM
WHERE
Employee_No, Date
BILLING_HOURS
Rate=150
c. SELECT
WHERE
Employee_No, Date
Rate=150
d. QUERY
SELECT
WHERE
BILLING_HOURS
Employee_No, Date
Hours>8
ANS: A
39. Which of the following SQL statements would extract the client number and date for every instance
that had a billing rate of $150?
a. QUERY
BILLING_HOURS
SELECT
Client_No, Date
WHERE
Rate=150
b. QUERY
FROM
WHERE
client_No, Date
BILLING_HOURS
Rate=150
c. SELECT
WHERE
Client_No, Date
Rate=150
d. SELECT
FROM
WHERE
Client_No, Date
BILLING_HOURS
Rate=150
ANS: D
40. Which of the following SQL statements would extract the client number and date for every instance
that an employee worked less than 8 hours any particular day?
a. QUERY
BILLING_HOURS
SELECT
Client_No, Date
WHERE
Hours<8
b. QUERY
FROM
WHERE
client_No, Date
BILLING_HOURS
Hours>8
c. SELECT
WHERE
Client_No, Date
Hours<8
d. SELECT
FROM
WHERE
Client_No, Date
BILLING_HOURS
Hours<8
ANS: D
COMPLETION
1. A(n) ____________________ in an accounting system can be classified as a resource, event, or agent
about which data is collected.
ANS: entity
2. A(n) ____________________ is an item of data that characterizes an entity or relationship.
ANS: attribute
3. Attributes that consist of multiple subattributes are referred to as _________________________.
ANS: composite attributes
4. A(n) _________________________ is the attribute whose value is unique for every entity that will
ever appear in the database.
ANS: key attribute
5. ____________________ map and define the way in which data can be extracted from a database.
ANS: Relationships
6. A(n) ______________________________ relationship occurs between two different instances of an
entity.
ANS: recursive
7. The relationship that relates to the specification of how many occurrences of an entity can participate
in the given relationship with any one occurrence of the other entity in the relationship is referred to as
______________________________.
ANS: cardinality constraint
8. The ______________________________ specifies the degree of minimum participation of one entity
in the relationship with the other entity.
ANS: participation constraint
9. The notation 1,N is used to specify the relationship type of _________________________.
ANS: one to many
10. REA stands for ____________________, ____________________, and ____________________.
ANS: resources, events, agents
11. ___________ values waste memory space and may cause problems when running queries.
ANS: Null
12. A collection of data representing multiple occurrences of a resource, event, or agent is known as a(n)
____________________.
ANS: relation
13. A set of data that describes a single instance of the entity represented by a relation is known as a(n)
____________________.
ANS: tuple
14. The _________________________ is specified to uniquely identity each tuple in the relation.
ANS: primary key
15. ___________________________________ specifies that for every attribute value in one relation that
has been specified to allow reference to another relation, the tuple being referenced must be intact.
ANS: Referential integrity
16. ____________________ is a powerful database language that can be used to define database systems,
query the database for information, generate reports from the database, and access databases from
within programs using embedded commands.
ANS: SQL
17. The ____________________ command is used to add new tuples to a relation.
ANS: INSERT
18. The ____________________ command demonstrates one of the weaknesses in most forms of SQL.
ANS: DELETE
19. The ____________________ command is used when we want to change one or more attribute values
for one or more tuples in a table.
ANS: UPDATE
20. Queries of a database are driven by ____________________ commands.
ANS: SELECT
PROBLEMS
1. The following table is a view of BILLING_HOURS from the various data elements in ABC's relational database:
BILLING_HOURS
Employee_No
B122
A632
B122
Client_No
F11555
F11555
F11555
Date
990823
990823
990824
Hours
8
8
8
Billing_Rate
150
100
150
A632
F11555
990824
8
100
B122
F11555
990825
8
150
A632
F11555
990825
8
100
B122
H12456
990826
8
150
A632
H12456
990826
8
100
A356
F11555
990826
8
57
A432
H12456
990826
8
57
A491
H12456
990826
8
57
B122
F11555
990827
8
150
A632
H12456
990827
8
100
A356
F11555
990827
8
57
B432
H12456
990827
8
57
B491
H12456
990827
8
57
Required:
a.
Using proper SQL commands, develop a query to extract billing data for Fleet Services (client number F11555), showing the employee number, date, hours, and
billing rate.
b.
Design a table showing the output that would be generated based upon your query
created above.
ANS:
a. SELECT
FROM
WHERE
Employee_No, Date, Hours, Billing_Rate
BILLING_HOURS
Client_No='F11555'
b.
Employee_No
B122
Date
990823
Hours
8
A632
990823
8
Billing_Rate
150.00
100.00
B122
990824
8
150.00
A632
990824
8
100.00
B122
990825
8
150.00
A632
990825
8
100.00
A356
990826
8
57.00
B122
990827
8
150.00
A356
990827
8
57.00
2. The following table is a view of TRAINING_COMPLETED from the various data elements in HighTech Corp.'s relational database:
TRAINING_COMPLETED
Employee_No
11111
11152
Date
990823
990823
Hours
8
8
Train_Code
15
15
11163
990823
8
45
11111
990824
8
25
11152
990824
8
25
11163
990824
8
15
11111
990825
8
45
11152
990825
8
55
11175
990825
8
45
11111
990826
8
55
11175
990826
8
55
11111
990827
8
65
11152
990827
8
45
11163
990827
8
65
11175
990827
8
65
Required:
a.
Using proper SQL commands, develop a query to extract training data for employee
Missy Pitman (employee number 11111), showing the date, hours, and training
code.
b.
Design a table showing the output that would be generated based upon your query
created above.
ANS:
a. SELECT
FROM
WHERE
Date, Hours, Train_Code
TRAINING_COMPLETED
Employee_No='11111'
b.
Date
990823
Hours
8
Train_Code
15
990824
8
25
990825
8
45
990826
8
55
990827
8
65
3. Based on the following abbreviated description of Avery University's course scheduling and student
advising procedures, prepare an Entity-Relationship (E-R) diagram that reflects the key entities and relationships.
Course Scheduling:
In consultation with the vice-president of Academic Affairs and the Registrar's Office, each academic
department chair decides which courses will be offered each semester and how many sections of each
course will be scheduled. Courses are scheduled in sections meeting at different times over the course
of the week. Some courses are scheduled in multiple sections. The department chair also assigns a faculty member who teaches for his/her department to teach each course-section. Students enroll in
courses-sections by submitting their schedule selections to the Registrar's Office. In assigning students
to sections, the Registrar gives preference to students based on their class standing (i.e., Senior, Junior,
Sophomore, and Freshman).
Student Advising:
Student academic advising is done according to each student's major. Each student chooses a faculty
advisor from his/her major department. While each student has only one "official" academic advisor,
each faculty person advises several students, up to a maximum of 15 advisees.
ANS:
See Figure TB-6.1