Databases Reference
In-Depth Information
YOU TRY IT
Good Reading Bookstores
You are preparing to implement a database to support
Good Reading Bookstores. The current logical table
design in shown in Figure 5-14.
Figure 5-14
PUBLISHER
Publisher
Name
Year
Founded
1.
For which tables and columns should you store
data as date/time type data?
City
Country
Telephone
2.
Customer numbers are a 5-digit alphanumeric
string, two letters and three numbers. How can
you enforce this?
AUTHOR
3.
You want to minimize disk space requirements.
Which columns in the CUSTOMER table should
be fixed length string types and which should
be variable length string types? Explain your
answers.
Author
Number
Author
Name
Year
Born
Year
Died
BOOK
4.
How can you determine whether character data
should be stored as ASCII or Unicode?
Book
Number
Book
Name
Publication
Year
Publisher
Name
5.
How can you determine whether or not a trigger
should be used to enforce data integrity?
Pages
Implementation Requirements
You are designing a database to support a Customer
Service company. You have already designed part of
the table requirements and have some idea of data
storage needs. The EMPLOYEE table will have approx-
imately 200 rows. Managers are identified through a
one-to-many unary relationship. The CUSTOMER table
will have approximately 2000 rows initially but could
grow to over a million rows. There is no set relationship
between customers and employees. Any employee can
take an initial call from any customer.
The CALL COMMENT table will need to track the
customer, employee, date and time of the call, and a
written summary. You include Customer Number and
Employee Number as foreign key constraints referenc-
ing CUSTOMER and EMPLOYEE. You must be able to
call up these summaries by employee and customer as
quickly as possible. You want to provide as much
space as possible for the summary.
CUSTOMER
Customer
Number
Customer
Name
Street
City
State
Country
WRITING
Book
Number
Author
Number
SALE
Book
Number
Customer
Number
Date
Price
Quantity
Good Reading Bookstores tables.
182
