1 Replies - 289 Views - Last Post: 22 April 2013 - 02:29 PM Rate Topic: -----

#1 alex71385  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 74
  • Joined: 27-October 09

SQL assignment, not sure if I did it right

Posted 21 April 2013 - 06:38 PM

The assignment calls to examine the DTO's and the BankDA to determine data and relationships needed. This is the DA file:
public interface BankDA extends GenericDA {
    Customer getCustomer(CustomerId id) throws NotFoundDAException, DAException;
    
    // if no id, generate a unique id, otherwise updating existing
    Customer saveCustomer(Customer customer) 
        throws NotFoundDAException, DAException;
    
    CustomerSearchResponse findCustomers(CustomerSearchRequest req) 
        throws DAException;
    
    Account getAccount(AccountId id) throws NotFoundDAException, DAException;

    // if no id, generate a unique id, otherwise updating existing
    Account saveAccount(Account account) 
        throws NotFoundDAException, DAException;

    // zero-length array is none, NotFoundException is for missing customerId
    Account[] getAccounts(CustomerId id) 
        throws NotFoundDAException, DAException; 
    
    AccountTransferResponse transfer(BigDecimal amount, 
                                     AccountId sourceId, 
                                     AccountId destinationId) 
        throws InsufficientFundsException, NotFoundDAException, DAException;
    

    // return all account types sorted by state code 
    State[] getSortedStates() throws DAException;
    
    // if no id, generate a unique id, otherwise updating existing
    State saveState(State state) throws NotFoundDAException, DAException;
    
    // return all account types sorted by type code 
    AccountType[] getSortedAccountTypes() throws DAException;
    
    // if no id, generate a unique id, otherwise updating existing
    AccountType saveAccountType(AccountType type) 
        throws NotFoundDAException, DAException;


The DTO's are:
account
accidentTransferResponse(has only sourse and destination listed and didn't think that should be a table but transation)
address
customer
customerSearchRequest(thought this is a transaction also)
customerSearchResponse(thought this is a transaction also)
state

The requirements are:
file needs to run over and over so tables and indices must be dropped
create tables need to support data
use ref integrity, primary keys, unique constraints, null/not null const
create some indices as need to help improve data retrieval
do not need to insert inital data.

With that said, this is what I have:
DROP TABLE account;
DROP TABLE address;
DROP TABLE customer;
DROP TABLE state;
DROP INDEX cust;
DROP INDEX bal;

CREATE TABLE account (
	id INTEGER PRIMARY KEY;
    type VARCHAR(30);
    balance INTEGER;
    owners INTEGER,
	FOREIGN KEY (owners) REFERENCES customer(id);
);

CREATE TABLE address (
	id INTEGER PRIMARY KEY,
	street VARCHAR(30),
	city VARCHAR(30),
	state VARCHAR(2),
	zip VARCHAR(5)
);

CREATE TABLE customer (
	id INTEGER PRIMARY KEY,
	lastName VARCHAR(30),
	firstName VARCHAR(25),
	mailingAddress INTEGER,
	FOREIGN KEY (mailingAddress) REFERENCES address(id)
);

CREATE TABLE state (
	id INTEGER PRIMARY KEY,
	code VARCHAR(2),
	name VARCHAR(25)
);

SELECT *
FROM account;

SELECT *
FROM address;

SELECT *
FROM customer;

CREATE INDEX cust
ON customer (id);

CREATE INDEX bal
ON account (balance);



It feels like doing that was too easy and that I'm missing the requirements and can someone who knows this to take a look at make suggestions/corrections/advice please? Thank you.

This post has been edited by alex71385: 21 April 2013 - 06:45 PM


Is This A Good Question/Topic? 0
  • +

Replies To: SQL assignment, not sure if I did it right

#2 alex71385  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 74
  • Joined: 27-October 09

Re: SQL assignment, not sure if I did it right

Posted 22 April 2013 - 02:29 PM

Anyone? Please?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1