Note
There are a lot of technical details presented with this specification. This is done to provide clarity and a starting point for design discussion. However, it is not done so to specify specific class or method naming, project layout, or order of work. You are free to make many technical adjustments to the ideas presented.Design 2 sets of database schema that account for the following information. Although we will deploy the 2 database schemas to the same database for the project, they should be designed to be independently deployed to separate databases. eSales and eBidbot are two independent applications. This will primarily affect your attempt re-use tables or to make primary key assumptions between the two.
This table stores singleton account information that will not be shared with external users
USER_IDThe user login for the account must be maintained by the database so that we can associate the account with a specific user after they login. This information is unique and cannot be changed, so it can also be used as a primary key field.
FIRST_NAMEtext field to hold a person's first name
MIDDLE_NAMEtext field to hold a person's middle name
LAST_NAMEtext field to hold a person's last name
START_DATEdate when user started with eSales
END_DATEdate when user closed account with eSales
since we want to protect personal information, the public contact information can be placed in a separate table so it can be more freely shared. This has a 1:1 relationship with ESALES_ACCT and could be done as a primary key join.
USER_ID(1:1 relationship with ESALES_ACCT)
EMAILvalid e-mail address to contact user. This field should be unique.
users can provide various types of addresses for use at the site. Each will have a name that defines its purpose. Since billing is not a part of the initial project, the only address we are concerned with is a shipping address. Items are also shipped to addresses. Note that an address for a shipped item may need a way to keep a user's change-of-address from modifying the historical fact of where it was shipped. This may mean that address fields cannot be changed, changes once referenced, or copied for use by the shipping side of the application.
IDthere is no single natural field within address that forms a primary key, so a generate value can be used.
NAMEidentifies the type of address (e.g., "SHIPPING")
TOidentifies the recipient at the address.
STREETCITYSTATEZIP
There is a 1:N relationship between accounts and addresses. If we want addresses to stay independent of the private account information, we can form a foreign key relationship through a secondary link table. Since this is a 1:N relationship, the foreign key pair can be constrained to be unique by the database. Otherwise, we can simply put a foreign key in the address table.
USER_ID(N:1 relation to ESALES_ACCT)
ADDRESS_ID(1:1 relation to ESALES_ADDRESS)
This will anchor the information associated with an auction. Auctions will progress from bidding to sale. Once the bidding is over, the winning bid information can be retained within this table and the incremental bid history purged. Be sure to design in an easy way to determine if an auction item is expired or officially over; even if it did not sell or ship yet. Note that end-of-auction processing will be looking for auctions that have expired, but not officially closed. Buyers and bidders will be looking for auctions that have not closed. You may have to adjust the schema suggested below to accommodate.
IDthere is no natural field for forming a primary key value within this table.
TITLECATEGORYjust a text name for now. There will be no defined set of categories.
DESCRIPTIONthis will be a straight text narrative.
START_TIMEEND_TIMEthis is a date in the future when the auction is taking place.
ASKING_PRICEthis forms a minimum bid value.
PURCHASE_PRICEthis comes from the AMOUNT of the winning ESALES_BID before it is purged.
BUYER_ID(N:1 relation to ESALES_USER) this comes from the BIDDER_ID of the winning ESALES_BID before it is purged.
SELLER_ID(N:1 relation to ESALES_USER)
SHIPTO_ID(1:1 or N:1 relation to ADDRESS) We should probably copy the buyer's shipping address since a later update of the user's shipping address should not modify a historical record of where this item was shipped to. No bid deal during this overall project.
bids for an active auction should be retained for viewing. Once the bidding is over, however, the loosing bid information is no longer needed. The winning bid can be kept around or copied into the auction as part of a sale record.
IDthere is no natural field that represents a primary key
BIDTIMEAMOUNTITEM_ID(N:1 relation with ESALES_AUCTIONITEM)
BIDDER_ID(N:1 relation with ESALES_USER)
design a table to store eBidbot user account information. No need for names here, we just want a userId of some sort and their eSales credentials (they trust us). For simplicity, make the eBidbot SALES_ACCOUNT column have an equal value to the USER_ID column and rely on the fact that their userIds will be the same. eBidbot will be a trusted user of eSales, but we have no way (within the scope of the project) to verify the eSales account ID provided by the user unless it is the same as the eBidbot login.
USER_IDthis user's login will be used as a primary key.
SALES_ACCOUNTbidder's login name for eSales
design a table to hold eBidbot information associated with an order. This application will use the information to know the specific auction, bid parameters, and final results (cached from eSales result).
IDAUCTION_IDlogically, this is a N:1 relationship to ESALES_AUCTIONTIME. However, there should be no physical database references between the two applications. All integration will come at the higher application levels.
START_BIDwhether we jump in with this bid or wait until it reaches this value is up to you.
MAX_BIDdon't bid any more that this amount.
COMPLETEknow if auction is complete for order.
RESULTknow if we won or not.
FINAL_BIDknow purchase price for winner. The buyer will owe this amount if the result says he won.
USER_ID(N:1 relation to EBIDBOT_BIDACCT)
design a class that encapsulates the singleton private information for a user. This class will reference other objects in the system, but will not be referenced by others.
userId:Stringuse login name. This could be optionally dropped in favor of using the identical property within the referenced POC object.
firstName:StringmiddleName:StringlastName:StringstartDate:DateendDate:Dateaddresses:Map<String, Address>addresses can be keyed by their name.
poc:POC
design a class that holds addressing information. This will primarily be used as a shipping address.
id:longname:Stringindicates type of address. This should be unique per account.
to:Stringthe recipient at the address
street:Stringcity:Stringstate:Stringzip:String
design a way for public point of contact information to be exposed for a user.
userId:Stringemail:Stringauctions:Collection<AuctionItem>provides direct navigation to auctions the user was a seller. This property could be dropped in favor of a uni-directional mapping from the AuctionItem side.
bids:Collection<Bid>provides direct navigation to bids the user has placed on open auctions. This property could be dropped in favor of a uni-directional mapping from Bid.
purchases:Collection<AuctionItem>provides direct navigation to auctions the user has won. This property could be dropped in favor of a uni-directional mapping from AuctionItem.
design a way to record bid information while an auction is still open.
id:longamount:doubletimestamp:Datebidder:POCauction:AuctionItem
design a class to encapsulate the information for an auction. AuctionItems will need to know if they are still open, expired but not yet completed, or closed. When searching for an auction to bid on, you should only see items that are still officially open.
id:longtitle:Stringcategory:Enumthe full list of categories is still TBD, but a candidate set will be provided with the test data.
description:StringstartTime:DateendTime:DateaskingPrice:doubleno bid should be allowed below this value.
purchasePrice:doublefilled in from winning bid when auction is complete.
bids:List<Collection>bids are retained as long as the auction is active. They can be maintained in an ordered list sorted by bid amount to make locating the highest bid easier to find.
buyer:POCfilled in when auction is complete from from winning bid when auction is complete.
shipTo:Addressaddress item is to be shipped to. This is filled in with the shipping address of the user when the auction is complete.
seller:POCimages:List<Image>auctions can have zero or more images associated with them.
design a class that encapsulates the bidder information for automated bidding.
userId:Stringaccount login for eBidbot
salesAccount:Stringaccount login for eSales
salesPassword:Stringaccount password for eSales
orders:Collection<Order>
design a class that defines the parameters for making a bid against a specific auction and its results.
auctionId:longeSales auction Id
startBid:doublestarting bid for user. You can determine when they should start with this value; right away or wait.
maxBid:doubleno bids should be made above this value.
completecache the active/complete state of the auction to help limit activity for application and cache results.
resultprovide information that indicates whether the bidder won or not.
finalBidcache the winning bid amount from eSales.
Add validation API declarations to your business objects as appropriate. This need not be extensive or exhaustive. Just do enough to show use of declarative validation as a part of your application.
Determine the validation groups you wish to use in your application (suggest business logic interface and persistence tier).
Add validation annotations to your business objects and assign them to your designed groups as appropriate.
Manually invoke validation from the your junit test and integrate the validation into your persistence unit.
Design and implement a mechanism to ingest a starting state for eSales based on a provided data file and parser. You will implement two primary sets of classes to support this requirement; the DAO(s) and an Ingestor.
Design and implement a set of DAOs that can be used to ingest eSales business data into the database using the database schema you designed as a part of a separate requirement. These DAOs can optionally be tuned for ingest or simply reused from your CRUD-style requirements.
Design and implement an Ingestor that will use an externally provided parser to obtain business data for eSales and use the Ingest DAO to populate the database.
Note
Please ignore references in the diagrams that call out use of JDBC. All DAOs can be implemented exclusively with JPA for this assignment.
Design and implement a DAO layer that will map the business objects between the object model and the database using the Java Persistence API (JPA). These DAOs will support all standard CRUD operations and can optionally implement the same interface as other potential DAO implementations. The implementation can make liberal use of JPA @Annotations, descriptor files, or a combination of both. Your interface should encapsulate the fact that an EntityManager is being used and the same EntityManager should be shared among other DAOs in the same Thread. Your DAOs should not attempt to control the transaction or they will NOT be portable to the EJB tier.
AccountDAO/JPAAccountDAOencapsulates the use of JPA when mapping account and closely associated business objects to/from the database.
AuctionDAO/JPAAuctionDAOencapsulates the use of JPA when mapping auction and closely associated business objects to/from the database.
BidAccountDAO/JPABidAccountDAOencapsulates the use of JPA when mapping BidAccount and closely associated business objects to/from the database.
Add tuning to your database schema by augmenting the DDL files with indexes for foreign keys, joins, and where clauses. This need not be extensive or exhaustive. Just do enough to show that proper data model and database tuning is part of the overall enterprise development process.
Design an initial business interface and business logic for the applications. The core O/R mapping work will be done by the DAOs. However, it is the ultimate responsibility of these business logic implementations that either it or the business objects enforce the business rules of the application. The DAOs only perform O/R mapping and do not enforce such things as a minimum bid. The business logic is assumed to work within the context of a single, externally controlled transaction. Do not attempt to control the transaction of the EntityManager within these objects or you will NOT be portable to the EJB tier. You need only implement the behavior required to implement the end-to-end use case listed in the testing section. Some of the anticipated methods are listed below.
AccountMgmt/AccountMgmtImplencapsulates the actions required to create, get, update and close an account.
createaccount - you will need to at least create a seller and two or more buyer accounts.
SellerMgmt/SellerMgmtImplencapsulates the actions required to create and get auctions for a seller.
createAuction - creates a new auction for a seller. All dates and properties of the auction need to be of consistent and legal values.
getUserAuctions - returns a collection of auctions associated with a seller. This can be used to determine if the auction has been added.
getAuction - returns a specific auction by ID. This can be used to track the state of a specific auction for a seller.
BuyerMgmt/BuyerMgmtImplencapsulates the actions required to get and bid on auctions.
getOpenAuctions - returns a collection of auctions that have not ended. This can be used to pick a specific auction to bid on.
placeBid - creates a bid for a specific user and auction. The auction must be open, the user must exist, and the bid amount must be greater than any pre-existing bid.
getAuction - returns a specific auction by ID. This can be used to track the state of a specific auction for a buyer.
TestSupport/TestSupporta useful tool during testing that encapsulates how to get the application back into a known state prior to running a test or to inspect values not normally exposed through the normal business interfaces.
getAccounts - get all accounts in the system.
removeAccount - sanely remove an account from the system. This may require removing the account from any current bids, etc. in order to satisfy referential integrity.
getAuctions - get all auctions in the system.
removeAuction - sanely remove an auction from the system. This may require removing bids, images, and other objects that may have references to this object.
clearAll - sanely take the state of the system down to a coldstart.
Ingestorthe Ingestor written as a part of a separate requirement is also logically considered part of this tier.
ingest - point an externally provided parser at a set of test data and use the DAOs to populate the system to a known state.
OrderMgmt/OrderMgmtImplcreateOrder - create a record within bidbot that indicates the sale and maximum bid. This may require some stubbing in project 1.
placeBid - place a bid that is higher than the current bid for an open auction but less than the order maximum. This will require some stubbing for project1.
endOrder - complete order processing once auction has closed and note if won. This will require some stubbing in project1.
getOrderStatus - did user win or not.
BidbotTestUtil/BidbotTestUtilImplreset - reset the bidbot database to an initial starting state.