Database Reference
In-Depth Information
heather Sweeney Designs Case Questions
Heather Sweeney is an interior designer who specializes in home kitchen design. She offers a
variety of seminars at home shows, kitchen and appliance stores, and other public locations.
The seminars are free; she offers them as a way of building her customer base. She earns reve-
nue by selling topics and videos that instruct people on kitchen design. She also offers custom-
design consulting services.
After someone attends a seminar, Heather wants to leave no stone unturned in attempting
to sell that person one of her products or services. She would therefore like to develop a data-
base to keep track of customers, the seminars they have attended, the contacts she has made
with them, and the purchases they have made. She wants to use this database to continue to
contact her customers and offer them products and services.
For reference, the SQL statements shown here are built from the HSD database design
Figure 7-38, the sample database column specifications (shown for the SEMINAR table only)
in Figure 7-39, and the referential integrity constraint specifications detailed in Figure 7-40.
The SQL statements to create the Heather Sweeney Designs (HSD) database are shown in
Figure 7-41 in Microsoft SQL Server syntax. The SQL statements to populate the HSD database
are shown in Figure 7-42, again in Microsoft SQL Server syntax.
Write SQL statements and answer questions for this database as follows:
A.
Create a database named HSD in your DBMS.
B.
Create a folder in your
My Documents
folder to save and store *.sql scripts containing the
SQL statements that you are asked to create in the remaining questions in this section.
●
For the SQL Server Management Studio, create a folder named
HSD-Database
in the
Projects
folder structure in your
My Documents
folder.
●
In the Oracle SQL Developer folder structure in your
My Documents
folder, create a
folder named
HSD-Database
.
●
For the SQL Workbench, create a folder named
HSD-Database
in the
Schemas
folder
in your
MyDocuments
folder.
Figure 7-38
Database Design for the
Heather Sweeney Designs
Database
SEMINAR
SeminarID
SeminarDate
SeminarTime
Location
SeminarTitle
SEMINAR_CUSTOMER
SeminarID (FK)
EmailAddress (FK)
CUSTOMER
EmailAddress
LastName
FirstName
Phone
StreetAddress
City
State
ZIP
C
O
NTACT
EmailAddress (FK)
ContactDate
ContactNumber
ContactTy pe
SeminarID (FK)
PRODUCT
ProductNumber
Description
UnitPrice
QuantityOnHand
INVOICE
InvoiceNumber
InvoiceDate
PaymentType
Subtotal
Shipping
Ta x
Total
EmailAddress (FK)
L
I
NE_ITEM
InvoiceNumber (FK)
LineNumber
Quantity
UnitPrice
Total
ProductNumber (FK)
Search WWH ::
Custom Search