chapter 15 - creating more complex database applications 1 chapter 15 creating more complex database...

Chapter 15 - Creating More Complex Database Applications

1

Chapter 15

Creating More Complex Database Applications


2

One to One Relationships 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

15


3

Understanding Tables in CustomerAndBoatDatabase

1. Create Chapter15Example1 folder2. Locate CustomerAndBoatDatabase.mdb on the

CD3. Open CustomerAndBoatDatabase using

Microsoft Access4. View the contents of the BoatTable

The primary key of BoatTable is StateRegistrationNo

5. View the contents of the CustomerTable The primary key of CustomerTable is PhoneNo

15


4


15


5


15


6


15


7



8


15


9

Using SQL to Join Table in Database

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 owner:

SELECT StateRegistration, Manufacturer, CustomerName, PhoneNoFROM BoatTable, CustomerTableWHERE CustomerPhoneNo = PhoneNo;

15


10

Using SQL to Join Table in Database The SQL statement displays the state

registration number, name, and address of the owner of the boat with state registration number MO98765:

SELECT StateRegistration, Manufacturer, CustomerName, PhoneNoFROM BoatTable, CustomerTableWHERE CustomerPhoneNo = PhoneNoAND StateRegistrationNo = ‘MO98765’;

15


11

Establishing a Common Connection

Need a new class named CustomerAndBoatDatabaseConnect

It is responsibility of this class is to manage the connection to the database

The initialize method establishes a connection of the database


12

Modifying 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


13

The BoatDA Class

Adds find, add, delete, and update methods

Defines a boat reference variable and a Vector of boat reference variables

15


14

The BoatDA Class – Initialize and Terminate

Initialize uses the database connection established by CustomerAndBoatDatabaseConnect to create a Statement

Terminate closes the Statement instance

15


15

The BoatDA Class – find and getAll Methods

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

15


16

The 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


17

The 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


18

Modifying the Boat Class to Work with BoatDA

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

15


19

Modifying the CustomerDA Class Change the CustomerDA class to

support joining information from the BoatTable and CustomerTable

Now includes a reference to a Boat object

15


20

CustomerDA 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


21

CustomerDA Class – find and getAll Methods


22

CustomerDA 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


23

CustomerDA Class – delete and update Methods

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

15


24

Testing the CustomerAndBoatDatabase Application1. Declare necessary reference variables2. Invoke the initialization method3. Attempt to retrieve customer and boat information for

customer whose phone number is 123-45674. Use the printDetails method to display the results5. Try to retrieve a record that doesn’t exist6. Call the getAll method7. Create a new Customer and Boat instance8. Call the addNew method9. Verify records have been added10. Test the delete method11. Test the update method12. Call the terminate method

15


25

Implementing a One-To-Many Relationship

The relationship between Dock and Slip is a one-to-many relationship

A dock contains many slips A slip belongs to one dock

15


26

Understanding the Tables in DockAndSlipDatabase

1. Create a Chapter15Example2 folder2. Copy the DockAndSlipDatabase from the

CD3. Open the database in Microsoft Access4. View the DockTable

DockID is the primary key

5. View the SlipTable SlipNo and DockID form the concatenated

primary key DockID is a foreign key for DockTable


27



28



29

Common Connection to DockAndSlipDatabase

Need a separate DA class to establish a connection

Same as CustomerAndBoatDatabaseConnect class except: Different data source is used

15


30

Modifying the Dock and Slip Classes

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

Don’t need to modify the Slip class nor create a SlipDA class -- WHY?

See Figure 15-25 on pp. 557-558

15


31

Introducing the DockDA Class

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

See pp. 559-563

15


32

DockDA 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


33

DockDA Class – find Method15


34

DockDA 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

See Figure 15-26 on pp. 563


35

DockDA Class – getAll Method 15


36

Testing the DockAndSlipDatabase Application

1. Define necessary variable and establish the connection to the database

2. Attempt to find Dock 13. printDetails method displays information

about the dock and slips4. Attempt to find Dock 2 and slips5. Invoke the getAll method


37

Testing the DockAndSlipDatabase Application


38

Implementing an Association Class The Lease class has associated

AnnualLease and DailyLease subclasses

Implement the Lease association class in a relational database application


39

Understanding Tables in CustomerLeaseSlipDatabase

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

15


40



41


15


42

Modifying the Customer Class 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 See Figure 15-31 on pp. 569-570

15


43

Modifying the Lease and AnnualLease Class

Must associate the lease with a customer’s slip

Need to add a slip reference and customer reference

Include getter and setter references to manage these references

See Figures 15-32 and 15-33

15


44

Modifying the Slip Class

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

15


45

The SlipDA Class

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

15


46

The 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


47

AnnualLeaseDA 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


48

AnnualLeaseDA 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


49

Testing CustomerLeaseSlipDatabase Application

1. Declare instances of AnnualLease, Customer, and Slip

2. Add a new record to LeaseTable3. Locate customer 123-45674. Find Slip 1 on Dock 15. Create a new annual lease instance6. Retrieve newly added information7. Close the database connection


50

Testing CustomerLeaseSlipDatabase Application

15

chapter 15 - creating more complex database applications 1 chapter 15 creating more complex database...

Documents