Database Reference
In-Depth Information
provide this service, she has developed an initial database with several tables. Three of those
tables are the following:
CUSTOMER (
CustomerID
, FirstName, LastName, Phone, Email)
INVOICE (
InvoiceNumber
,
CustomerNumber
, DateIn, DateOut, TotalAmount)
INVOICE_ITEM (
InvoiceNumber
,
ItemNumber
, Item, Quantity, UnitPrice)
In the database schema above, the primary keys are underlined and the foreign keys are shown
in italics. The database that Marcia has created is named MDC, and the three tables in the
MDC database schema are shown in Figure 2-34.
The column characteristics for the tables are shown in Figures 2-35, 2-36, and 2-37. The
relationship between CUSTOMER and INVOICE should enforce referential integrity, but not
cascade updates nor deletions, while the relationship between INVOICE and INVOICE_ITEM
should enforce referential integrity and cascade both updates and deletions. The data for these
tables are shown in Figures 2-38, 2-39, and 2-40.
We recommend that you create a Microsoft Access 2013 database named
MDC-CH02.
accdb
using the database schema, column characteristics, and data shown above and then use
this database to test your solutions to the questions in this section. Alternatively, SQL scripts
for creating the
MDC-CH02
database in Microsoft SQL Server, Oracle Database, and MySQL are
available on our Web site at
www.pearsonhighered.com/kroenke
.
Write SQL statements and show the results based on the MDC data for each of the following:
a.
Show all data in each of the tables.
Figure 2-34
The MDC Database
B.
List the LastName, FirstName, and Phone of all customers.
The CUSTOMER
table
The INVOICE
table
The INVOICE_ITEM
table
Figure 2-35
Column Characteristics
for the MDC Database
CUSTOMER Table
CUSTOMER
Column Name
Type
Key
Required
Remarks
CustomerID
AutoNumber
Primary Key
Yes
Surrogate Key
FirstName
Text (25)
No
Yes
LastName
Text (25)
No
Yes
Phone
Text (12)
No
No
Email
Text (100)
No
No
Search WWH ::
Custom Search