Chapter 15 Creating More Complex Database Applications - PowerPoint PPT Presentation

1 / 56
About This Presentation
Title:

Chapter 15 Creating More Complex Database Applications

Description:

... boats in BoatTable together with the name and phone number of each boats owner: ... and boat information for customer whose phone number is 123-4567 ... – PowerPoint PPT presentation

Number of Views:27
Avg rating:3.0/5.0
Slides: 57
Provided by: johnf191
Category:

less

Transcript and Presenter's Notes

Title: Chapter 15 Creating More Complex Database Applications


1
Chapter 15Creating More Complex Database
Applications
2
One 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

3
Understanding 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

4
Understanding Tables in CustomerAndBoatDatabase
15
5
Understanding Tables in CustomerAndBoatDatabase
15
6
Understanding Tables in CustomerAndBoatDatabase
15
7
Understanding Tables in CustomerAndBoatDatabase
8
Understanding Tables in CustomerAndBoatDatabase
15
9
Using 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

10
Using 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

11
Establishing 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

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
15
  • Adds find, add, delete, and update methods
  • Defines a boat reference variable and a Vector of
    boat reference variables

14
The BoatDA Class Initialize and Terminate
15
  • Initialize uses the database connection
    established by CustomerAndBoatDatabaseConnect to
    create a Statement
  • Terminate closes the Statement instance

15
The 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

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

19
Modifying 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

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

24
Testing 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

25
Testing the CustomerAndBoatDatabase Application
  • Test the delete method
  • Test the update method
  • Call the terminate method

26
Testing the CustomerAndBoatDatabase Application
15
27
Testing the CustomerAndBoatDatabase Application
15
28
Testing the CustomerAndBoatDatabase Application
29
Testing the CustomerAndBoatDatabase Application
15
30
Implementing 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

31
Understanding 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

32
Understanding the Tables in DockAndSlipDatabase
33
Understanding the Tables in DockAndSlipDatabase
34
Understanding the Tables in DockAndSlipDatabase
35
Common Connection to DockAndSlipDatabase
15
  • Need a separate DA class to establish a
    connection
  • Same as CustomerAndBoatDatabaseConnect class
    except
  • Different data source is used

36
Modifying 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

37
Introducing 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

38
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

39
DockDA Class find Method
15
40
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

41
DockDA Class getAll Method
15
42
Testing 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

43
Testing the DockAndSlipDatabase Application
44
Implementing an Association Class
  • The Lease class has associated AnnualLease and
    DailyLease subclasses
  • Implement the Lease association class in a
    relational database application

45
Understanding 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

46
Understanding Tables in CustomerLeaseSlipDatabase
47
Understanding Tables in CustomerLeaseSlipDatabase
15
48
Modifying 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

49
Modifying 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

50
Modifying 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

51
The 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

52
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

53
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

54
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

55
Testing 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

56
Testing CustomerLeaseSlipDatabase Application
15
Write a Comment
User Comments (0)
About PowerShow.com