Databases Reference
In-Depth Information
YOU TRY IT
Using SELECT Statements
You design a database for a video rental company. The
database is normalized to 3NF. Whenever a customer
rents a title, a rental invoice is generated. This is stored
in two tables, RHead and RTail. RHead contains informa-
tion on the invoice as a whole, including the CustNum
to identify the customer, and RTail contains information
for each detailed item in the invoice.
Three tables are used to track customer informa-
tion. CustInfo contains general information about the
customer. CustSec contains more senstive information,
such as telephone number and address. CustRentals
has information about current and past rentals. The
table has two rental columns, one for VHS and one for
DVD. The CustNum column is used to establish the
relationship between the tables.
There are two tables for videos. One contains
titles on VHS tape and the other DVDs. You are prepar-
ing to add a third table for games, which you plan to
call Games. Each title has a unique tracking number
that is generated internally. The data type for the track-
ing number is the same in both tables, NCHAR(10).
Each value is unique, so that none of the numbers are
duplicated between the VHS table and the DVD table.
(a)
What tables would be needed?
(b)
What values would you use to retrieve the
information you need?
(c)
How would this solution need to be modified
if you start renting games?
Using Batches and Scripts
You have several activities that must be completed at
the end of each month and at the end of each quarter.
These include reporting and data manipulation opera-
tions. You need to automate as many of these opera-
tions as possible. You also want to simplify them so
that you can delegate some of the periodic require-
ments to someone else.
There are two databases involved, both of which
are hosted on the SQL Server 2005 database server
OURDATA. The databases are named SalesData and
OpsData. End-of-period activities include both data-
bases, but all of the activities run separately on each.
There are no requirements for combining data from the
databases or transfering data between the databases.
1.
Assuming that the end-of-quarter activites can
run sequentally, first for one database and then
for the other, what is the minimum number of
scripts required? Explain your answer.
1.
Describe how you would generate a list of in-
voices that include the Customer's name and
address? Include tables and specific columns
required by the solution.
2.
When you create your scripts, what extension
should you use and why?
3.
Some operations must run as a set because
they are dependent on each other. Each set of
operations is independent of other sets. You
want to minimize the potential impact of errors
on script execution. How should you do this?
How does your solution help?
2.
You need a fast way of checking to make sure
that none of the numbers in VHS are dupli-
cated in DVD. How can you do this? (Minimize
the size of the result set needed by your
solution and the result that you would expect
to see.)
4.
There are several either/or decisions that must
be made while the scripts are running. How
can this functionality be implemented? What is
the possible restriction on the decision logic
used?
3.
You want to be able to run a monthly rental his-
tory report by customer. The report would include
the Customer number and name, but not include
any senstive information about the customer.
List videos by both tracking number and title.
262
