Title: Chapter 15 Creating More Complex Database Applications
1Chapter 15Creating More Complex Database
Applications
2One to One Relationships
15
- There is a one-to-one relationship between
Customer and Boat - A customer always owns exactly one boat and a
boat always belongs to just one customer - Use a database to implement the one-to-one
association using a relational database
3Understanding Tables in CustomerAndBoatDatabase
15
- Create Chapter15Example1 folder
- Locate CustomerAndBoatDatabase.mdb on the CD
- Open CustomerAndBoatDatabase using Microsoft
Access - View the contents of the BoatTable
- The primary key of BoatTable is
StateRegistrationNo - View the contents of the CustomerTable
- The primary key of CustomerTable is PhoneNo
4Understanding Tables in CustomerAndBoatDatabase
15
5Understanding Tables in CustomerAndBoatDatabase
15
6Understanding Tables in CustomerAndBoatDatabase
15
7Understanding Tables in CustomerAndBoatDatabase
8Understanding Tables in CustomerAndBoatDatabase
15
9Using SQL to Join Table in Database
15
- The SQL statement to find and display the state
registration number and manufacturer of all boats
in BoatTable together with the name and phone
number of each boats ownerSELECT
StateRegistration, Manufacturer, CustomerName,
PhoneNoFROM BoatTable, CustomerTableWHERE
CustomerPhoneNo PhoneNo
10Using SQL to Join Table in Database
15
- The SQL statement displays the state registration
number, name, and address of the owner of the
boat with state registration number
MO98765SELECT StateRegistration, Manufacturer,
CustomerName, PhoneNoFROM BoatTable,
CustomerTableWHERE CustomerPhoneNo PhoneNoAND
StateRegistrationNo MO98765
11Establishing a Common Connection
- Need a new class named CustomerAndBoatDatabaseConn
ect - It is responsibility of this class is to manage
the connection to the database - The initialize method establishes a connection of
the database
12Modifying the Customer PD
- Include a boat reference attribute
- Initialize boat reference to null in constructor
- Include setter and getter methods to set and
retrieve boat reference - Use the common database connection established by
CustomerAndBoatDatabaseConnect
13The BoatDA Class
15
- Adds find, add, delete, and update methods
- Defines a boat reference variable and a Vector of
boat reference variables
14The BoatDA Class Initialize and Terminate
15
- Initialize uses the database connection
established by CustomerAndBoatDatabaseConnect to
create a Statement - Terminate closes the Statement instance
15The BoatDA Class find and getAll Methods
15
- The find method defines a SELECT statement that
retrieves a particular record from BoatTable - Find throws a NotFoundException if the record is
not found - The getAll method returns a Vector of boat
references
16The BoatDA Class addNew Method
- Similar to the addNew method of the Customer
class - Extracts boat attributes from the boat instance
received - Create a SQL INSERT statement
- Before executing INSERT need to confirm that the
database does not already contain a duplicate
using the find method - If a duplicate is found a DuplicateException is
thrown
17The BoatDA Class update and delete Methods
- The update and delete methods extract attributes
from the boat instance received in the argument
list, then define the appropriate SQL statements - Before executing UPDATE and DELETE need to
confirm that the database already contains the
record using the find method - If a record is not found a NotFoundException is
thrown
18Modifying the Boat Class to Work with BoatDA
15
- Can extend the functionality of the Boat PD to
use the BoatDA class - The Boat class needs four static methods
- Initialize
- Find
- getAll
- Terminate
- Three instance methods
- addNew
- Update
- delete
19Modifying the CustomerDA Class
15
- Change the CustomerDA class to support joining
information from the BoatTable and CustomerTable - Now includes a reference to a Boat object
20CustomerDA Class find and getAll Methods
- Change the SQL statement WHERE clause to specify
both the join condition and the primary key for
the customer of interest
21CustomerDA Class find and getAll Methods
22CustomerDA Class addNew Method
- The is a mandatory one-to-one relationship
between Customer and Boat - Must be a customer record for every boat record
the addNew method must enforce this requirement - After inserting a customer record into
CustomerTable, a record must be inserted into the
BoatTable - Preserves the integrity of the database
23CustomerDA Class delete and update Methods
15
- The delete method must also preserve the
integrity of the database - When a customer record is deleted, the
corresponding boat record must be deleted as well - The update method is unchanged
24Testing the CustomerAndBoatDatabase Application
15
- Declare necessary reference variables
- Invoke the initialization method
- Attempt to retrieve customer and boat information
for customer whose phone number is 123-4567 - Use the printDetails method to display the
results - Try to retrieve a record that doesnt exist
- Call the getAll method
- Create a new Customer and Boat instance
- Call the addNew method
- Verify records have been added
25Testing the CustomerAndBoatDatabase Application
- Test the delete method
- Test the update method
- Call the terminate method
26Testing the CustomerAndBoatDatabase Application
15
27Testing the CustomerAndBoatDatabase Application
15
28Testing the CustomerAndBoatDatabase Application
29Testing the CustomerAndBoatDatabase Application
15
30Implementing a One-To-Many Relationship
15
- The relationship between Dock and Slip is a
one-to-many relationship - A dock contains many slips
- A slip belongs to one dock
31Understanding the Tables in DockAndSlipDatabase
- Create a Chapter15Example2 folder
- Copy the DockAndSlipDatabase from the CD
- Open the database in Microsoft Access
- View the DockTable
- DockID is the primary key
- View the SlipTable
- SlipNo and DockID form the concatenated primary
key - DockID is a foreign key for DockTable
32Understanding the Tables in DockAndSlipDatabase
33Understanding the Tables in DockAndSlipDatabase
34Understanding the Tables in DockAndSlipDatabase
35Common Connection to DockAndSlipDatabase
15
- Need a separate DA class to establish a
connection - Same as CustomerAndBoatDatabaseConnect class
except - Different data source is used
36Modifying the Dock and Slip Classes
15
- Dock class must be modified to support object
persistence in a relational database - Implement the initialize, terminate, find, and
getAll methods - Implement a tellAboutSelf method
- Dont need to modify the Slip class nor create a
SlipDA class
37Introducing the DockDA Class
15
- Similar to other DA classes, except it does not
require insert, update, or delete methods - Methods used to terminate and initialize are
similar to other DA classes
38DockDA Class find Method
- Needs SQL statement to extract dock and slip
information from the database - WHERE clause specifies both the join condition
and the primary key for the dock of interest - ORDER BY clause specifies the information
returned by the query is sorted by slip number
39DockDA Class find Method
15
40DockDA Class getAll Method
- Similar to find method
- Returns dock and slip information for all slips
in the marina, sorted by dock and then slip - Dock information is repeated for each slip
- Creates only one dock instance however
- Control-break logic is used to manage this
41DockDA Class getAll Method
15
42Testing the DockAndSlipDatabase Application
- Define necessary variable and establish the
connection to the database - Attempt to find Dock 1
- printDetails method displays information about
the dock and slips - Attempt to find Dock 2 and slips
- Invoke the getAll method
43Testing the DockAndSlipDatabase Application
44Implementing an Association Class
- The Lease class has associated AnnualLease and
DailyLease subclasses - Implement the Lease association class in a
relational database application
45Understanding Tables in CustomerLeaseSlipDatabase
15
- Create a Chapter15Example3 folder
- Open the CustomerLeaseSlipDatabase
- View the CustomerTable, LeaseTable, and SlipTable
- Customer phone number is primary key for
LeaseTable - Slip number and dock ID are foreign keys in
LeaseTable
46Understanding Tables in CustomerLeaseSlipDatabase
47Understanding Tables in CustomerLeaseSlipDatabase
15
48Modifying the Customer Class
15
- Need to associate a customer instance with a
lease instance - Need to add a lease reference to the CustomerPD
class - Include getter and setter methods for the lease
reference
49Modifying the Lease and AnnualLease Class
15
- Must associate the lease with a customers slip
- Need to add a slip reference and customer
reference - Include getter and setter references to manage
these references
50Modifying the Slip Class
15
- Must associate a slip with its corresponding
lease and customer - Need to add an annual lease reference to the
attribute list and set its value to null - Three DA methods are needed
- Initialize
- Terminate
- Find requires two parameters slip number and
dock ID
51The SlipDA Class
15
- You must find a particular slip so it can be
leased to a customer - Need a SlipDA class
- Initialize and Terminate methods are identical to
other DA classes - Find method defines a SELECT statement that
returns information from the SlipTable for a
particular slip
52The AnnualLeaseDA Class
- Must find and insert information about annual
leases in the database - Define variables for annual lease attributes
- Uses standard initialize and terminate methods
53AnnualLeaseDA Class - find
- Query retrieves information from three tables
CustomerTable, LeaseTable, and SlipTable - The WHERE clauses uses the relationships between
these tables to create the join conditions - The setCustomer method establishes a link with
the annual lease instance
54AnnualLeaseDA Class - addNew
- Receives a reference to the annual lease instance
that will be added - Lease amount, balance, payment, and start date
are extracted - Before executing SQL statement that inserts into
LeaseTable checks to see if primary key already
exists in the database
55Testing CustomerLeaseSlipDatabase Application
- Declare instances of AnnualLease, Customer, and
Slip - Add a new record to LeaseTable
- Locate customer 123-4567
- Find Slip 1 on Dock 1
- Create a new annual lease instance
- Retrieve newly added information
- Close the database connection
56Testing CustomerLeaseSlipDatabase Application
15