Transcript
Database Design and Development: A Visual Approach
Relational Theory
TRUE / FALSE QUESTIONS
Information systems include people, policies, computers, and application software.
Answer: True
Relational database is not a part of an information system.
Answer: False
End users typically have a direct access to a database.
Answer: False
In relational databases tables are called tuples.
Answer: False
Relational database management system (RDBMS) is the software that runs on the Web server.
Answer: False
Structured query language (SQL) is the command language used to communicate with the RDBMS.
Answer: True
DBA stands for database authorization.
Answer: False
Database design is a model of the data items and their interrelationships.
Answer: True
A conceptual database model is a description of data that depends on the type of database software product.
Answer: False
Database design is visually presented in the form of an entity-relationship diagram.
Answer: True
Entities are adjectives (name, address, phone number) that describe an attribute such as a customer.
Answer: False
Attributes are nouns (people, places, things) such as orders and customers.
Answer: False
Relationships are verbs (possessions) such as customer places an order.
Answer: True
- 17
Entities in today’s ER diagrams are modeled as diamonds.
Answer: False
Relationships in today’s ER diagrams are modeled as lines.
Answer: True
A crow’s foot at the single end of a line represents a one-to-many relationship.
Answer: True
Crow’s feet at the both ends of a line represent a many-to-many relationship.
Answer: False
Entity and table are typically used interchangeably.
Answer: True
Attribute and record are typically used interchangeably.
Answer: False
A primary key uniquely identifies each field in a table.
Answer: False
Ref: p 18
Two fields, longitude and latitude, together form a primary key for a location table.
Answer: True
Ref: p 18
Email address satisfies all of the desired primary key properties.
Answer: False
Difficulty: Hard
Ref: p 19
Foreign keys link the related records between parent and child tables.
Answer: True
Foreign key in the child table is defined over the same set of values as the primary key in the parent table.
Answer: True
Network database has physical pointers to connect related tables.
Answer: True
Object-oriented databases are easier to use than relational databases.
Answer: False
MULTIPLE-CHOICE QUESTIONS
Relational database is a part of a(n)
relational system.
information system.
physical system.
storage system.
Answer: b
Which of the following describes the relationship between an end user and a database?
End user has a direct access to a database.
End user can never access a database.
End user interacts with a database front-end program.
End user can only interact with the database through a database administrator.
Answer: c
Forms in Web pages, such as order forms, are used for
data input.
result output.
data formatting.
data storage.
Answer: a
Reports, such as order summary, are used for
data input.
result output.
data formatting.
data storage.
Answer: b
In a relational database, tables are called
tuples.
fields.
attributes.
relations.
Answer: d
In relational database, columns in a table are called
tuples.
fields.
records.
relations.
Answer: b
In relational database, rows in a table are called
tuples.
fields.
attributes.
relations.
Answer: a
RDBMS can be best defined using which of the following?
A software that runs on the Web server
A software that runs on the mail server
A software that runs on the database server
A hardware on which the database resides
Answer: c
RDBMS stands for which of the following?
Rational database management software
Relational database management system
Rapid database maintenance system
Relational database management structure
Answer: b
Ref: p16
RDBMS can be described as doing which of the following?
Provides end user with unrestricted access to data and procedures
Prevents authorized end users from accessing data and procedures
Provides authorized end users with restricted access to data and procedures
Provides unauthorized users with restricted access to data and procedures
Answer: c
SQL stands for which of the following?
Structured query language
Simple query language
Structured queuing language
Simple query list
Answer: a
SQL would be best described as doing which of the following?
Allowing the end user to communicate with RDBMS
Enabling the database to communicate with RDBMS
Allowing RDBMS to communicate with the database administrator.
Allowing the RDBMS to communicate with database server hardware.
Answer: b
DBA is the person who
runs the RDBMS.
grants database access to end users.
maintains the database using RDBMS.
All of the above.
Answer: d
Databases are designed as a group of related
entities.
attributes.
relationships.
fields.
Answer: a
Entities (persons, places, things) are like
nouns.
verbs.
adjectives.
sentences.
Answer: a
Relationships (possessions) are like
nouns.
verbs.
adjectives.
sentences.
Answer: b
Attributes (names, phone numbers, addresses) are like
nouns.
verbs.
adjectives.
sentences.
Answer: c
ER diagrams use which of the following as a symbol for an entity?
Diamond
Triangle
Box
Line
Answer: c
ER diagrams use which of the following as a symbol for a relationship?
Diamond
Triangle
Box
Line
Answer: d
A crow’s foot at the single end of the line represents which of the following?
One-to-one relationship
One-to-many relationship
Many-to-many relationship
No relationship
Answer: b
Crow’s feet at both ends of the line represent which of the following?
One-to-one relationship
One-to-many relationship
Many-to-many relationship
Not a proper relationship representation
Answer: d
In a one-to-many relationship, the crow’s foot always points to the
child table.
parent table.
sibling table.
grandparents table.
Answer: a
Customer/order relationship is an example of
one-to-one relationship.
one-to-many relationship.
many-to-many relationship.
difficult relationship.
Answer: b
Many-to-many relationships are represented by a
parent table.
child table.
sibling table.
third table.
Answer: d
Customer/product relationship is an example of
one-to-one relationship.
one-to-many relationship.
many-to-many relationship.
difficult relationship.
Answer: c
In a doctor/patient relationship, a visit represents a
parent table.
child table.
sibling table.
third table.
Answer: b
Primary keys can be formed
from an existing single field.
from several existing fields.
as computer-generated field.
using all of the above.
Answer: d
Ref: p 19
Customer’s phone number does not satisfy which of the following primary key desired properties?
Unique
Minimal
Non-null
Nonupdateable
Answer: d
Ref: p 18
In an order table, foreign key for a customer table would be named as which of the following?
Customer$name
Order$date
Customer$email
OrderID
Answer: c
Fourth-Generation Languages like SQL
are procedural and record oriented.
require significant amount of looping.
are nonprocedural and table oriented.
are oriented towards processing binary code.
Answer: c
Difficulty: Hard
Ref: p 24
FILL-IN QUESTIONS
Information systems include people, policies, computers, and _application software_.
End users typically have an _indirect_ access to a database.
Businesses have _programs_ that allow end user to interact with a database.
Forms are used to _input_ data into a database.
In a relational database, rows in a table are called _tuples_.
In a relational database, tables are called _relations_.
RDBMS is a software that prevents unauthorized access to a _database_.
SQL is a command language that allows _RDBMS_ to communicate with a database.
DBA stands for _database administrator_.
The most common way of designing a relational database is through the creation of a(n) _entity-relationship diagram_.
ER diagrams make it much easier to understand the _relationships_ among entities in the database.
Entities are like _nouns_; they are persons, places, or things.
Relationships are like _verbs_; they are acts of possession.
Attributes are like _adjectives_; they describe properties of entities.
In an ER diagram, a line between two entities with a crow’s foot at a single end of the line represents a _one-to-many_ relationship.
In a parent/child relationship, the crow’s foot always points toward the _child_ table.
OrderID is an example of a _computer-generated_ primary key.
A primary key _uniquely_ identifies each record in a table.
Ref: p 18
Email address as a primary key violates _nonupdateable_ property every primary key should support.
Ref: p 18
Entity_ integrity requires that the designer specify a primary key at the time the table is created.
Difficulty: Hard
Ref: p 21
Referential_ integrity requires that foreign key values match existing primary key values in the table to which they refer.
Difficulty: Hard
Ref: p 21
A table is said to be in first normal form when each field in that table contains single values only_.
Ref: p 22
Relational databases use _logical_ links between related tables, whereas network databases use _physical_ links between related tables.
Relational databases achieve a high degree of data independence by deriving views_ from base table in order to protect those tables from the end user.
Ref: p 24
An _associative_ table is a child of two parent tables that are in a many-to-many relationship.
Difficulty: Hard
Ref: p 26
ESSAY QUESTIONS
Plymouth Car Rental started with two compact cars and has expanded its fleet of vehicles with several sedans and trucks. When a customer books a rental, his/her age has to be at least 18 for compact cars and sedans, which are rented by the day and must be returned the following morning by 11 AM. Trucks are rented for a maximum of six hours. The owner requires a deposit on the reservation to be paid within seven days of booking. Vehicle records consist of the makes and types (Honda sedan), color, seating capacity, required deposit, rental rate, and rental limit (in hours). Customer data consists of customer’s names, addresses, phone numbers, and birth dates. Bookings identify the customer, vehicle, as well as the time rented and returned. There can be several payments up until the reservation date. Payments must reflect the payment status for each booking, including deposit, when the deposit was made, then each of the payments made, and when the entire payment was completed.
For questions 82 - 86, please refer to the preceding paragraph.
List all the entities for Plymouth Car Rental, and describe their relationships with other entities.
Entities: Customer, Vehicle, Booking, Payment. Relationship between Customer and Booking tables is one-to-many. One customer can have many bookings during a period of time. Relationship between Vehicle and Booking tables is one-to-many. One vehicle can appear in many bookings (not at the same time, of course). Relationship between Booking and Payment tables is also one-to-many. Each booking can have many payments.
Draw the proper relationship between the customer and booking tables, as well as between booking and payment tables.
Customer
-6858021653500
Booking
Booking
-6540532893000
Payment
List all the attributes in vehicle table.
Vehicle
VIN
Make
Type
Color
Seating Capacity
Required Deposit
Rental Rate
Rental Limit
List and/or construct primary keys for each of the tables.
Customer table: CustomerID
Vehicle table: VIN
Booking table: BookingID
Payment table: PaymentID
List all foreign keys in appropriate tables.
CustomerID is a foreign key in Booking table that establishes connection with Customer table. VIN is a foreign key in Booking table that establishes connection with Vehicle table. BookingID is a foreign key in Payment table that establishes connection with Booking table.
Jerry, the owner of Exotic Flower, Inc., built a small greenhouse to store several types of exotic flowers that he purchases from wholesale suppliers around the world. Each exotic flower Jerry buys and resells falls into one of several flower groups that differ with respect to their storage needs, duration, time in bloom, price, etc. Each of the orders placed by customers specifies the type of exotic flower, the supplier it came from, the date of order, expected delivery date, flower condition on delivery, as well as the quantity ordered. Customer’s data contain all the standard information needed to collect payment and deliver the flowers.
For questions 87 - 91, please refer to the preceding paragraph.
List all the entities for Exotic Flower, and describe their relationships with other entities.
Entities: Customer, Flower, Order, Supplier. Relationship between Customer and Order tables is one-to-many. One customer can have many orders during a period of time. Relationship between Flower and Order tables is one-to-many. One flower can appear in many orders. Relationship between Supplier and Order tables is also one-to-many. Each supplier can be present in many orders. Relationship between Customer and Flower is many-to-many. Many customers can have orders for many flowers.
Draw the proper relationship between the customer and order tables, as well as between customer and flower tables.
Customer
-6858026543000
Order
Customer
-6540526543000
Order
-6540527178000
Flower
List all the attributes in flower table.
Flower
FlowerID
Flower Group
Storage Needs
Duration
Time in Bloom
Price
List and/or construct primary keys for each of the tables.
Customer table: CustomerID
Order table: OrderID
Flower table: FlowerID
Supplier table: SupplierID
List all foreign keys in appropriate tables.
CustomerID is a foreign key in Order table that establishes connection with Customer table. FlowerID is a foreign key in Order table that establishes connection with Flower table. SupplierID is a foreign key in Order table that establishes connection with Supplier table.
Exposures is a specialty retailer of fine gifts, picture frames, albums, scrapbooks, as well as occasional gift ideas. Visit their Web site at www.exposureonline.com, and using the registration, billing, catalog, and shopping cart screens below, go through each of the five ER design steps.
List all the entities in Exposures database and describe their relationships with other entities.
Entities: Customer, Payment, Order, and Shopping Cart. Customer table has one-to-many relationship with Payment table. One customer can have many credit cards (and other payment options). Customer table has one-to-many relationship with Order table. One customer can have many orders. Order table has one-to-one relationship with Shopping Cart table. Each order should be associated with only one shopping cart. Shopping Cart table has one-to-many relationship with Shopping Cart Item table. One shopping cart can have many items. Also, Customer table might have one-to-many relationship with Shopping Cart table. Each customer can have many shopping carts (presumably at different times).
List all the attributes in shopping cart table.
CartID
CustomerID
OrderID
ItemID
TimeCheckedOut
OrderPlaced?
List and/or construct primary keys for each of the tables.
Each of the tables probably has computer-generated IDs like CustomerID, CartID, OrderID, ItemID, and PaymentID.
List all foreign keys in appropriate tables.
CustomerID is a foreign key in Payment table, establishing the relationship with Customer table. CustomerID is a foreign key in Shopping Cart table, establishing the relationship with Customer table. OrderID is a foreign key in Shopping Cart table, establishing the relationship with Order table. ItemID is a foreign key in Shopping Cart table, establishing the relationship with Shopping Cart Item table. CustomerID is a foreign key in Order table, establishing the relationship with Customer table.