1 / 31

Practical Databases

Practical Databases. A look at DBMS in practice: A Trading and Inventory System. Introductory Guff. Your speaker is Dr Richard Keeble; Today’s talk - a look at database systems in a real-life environment ... A case study of real system s in a real company;

rhoda
Download Presentation

Practical Databases

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Practical Databases A look at DBMS in practice: A Trading and Inventory System

  2. Introductory Guff • Your speaker is Dr Richard Keeble; • Today’s talk - a look at database systems in a real-life environment ... • A case study of real systems in a real company; • A look at the bigger issues surrounding database systems in financial environments.

  3. What I’ll Talk About • Business background and rationale: • What systems did we look after and why? • How did they support the business goals? • A look at the systems themselves: • The various components and their relationships; • How the different elements of the business interact and are supported by the technology.

  4. What You’ll Get Out Of This • An idea of the scale and complexity of ‘enterprise’ database systems; • An insight into how business systems fit together and inter-operate; • A look at some of the problems in the development of business database applications; • Experience from others’ cautionary tales ... !

  5. The Business: What • Goldman Sachs: • General Activities: • Investment Banking; • Proprietary Trading. • My responsibilities: • Front-line support; • Routine maintenance and development; • Flow testing; • New development, test and commission.

  6. The Business: What & Why • What’s a ‘Repurchase Agreement’ (‘Repo’)? • A short-term collateralised loan. • What’s it for? • Short-term low-risk borrowing and lending; • Also allows GS to spend the same money several times a day … !

  7. Repo Trades • Legal definition: • ‘an agreement to sell and a simultaneous forward agreement to re-purchase’. • Contrast to a ‘normal’ trade: • A repo has a start and an end, as opposed to a share purchase which is a single event.

  8. An Example Repo Trade • A one-week repo at 5% for EUR 10MM vs. 10MM BUNDs at par: • Start cash = 10MM EUR; • End cash = 10MM EUR + 9,491.48 EUR interest; • One Trade, but two Business Transactions: SELL 2003-01-30 RE-PURCHASE 2003-02-06 RP 10MM EUR vs 10MM BUND NEWPIECE FULLRETURN

  9. Trading Volumes • Usually around 1300-1400 trades done per day; • Somewhere around 15,000 trades active at any point; • Approximately 40,000 different securities may be traded; • So how do we know what’s going on?

  10. Security Projections • Sister to the NDR trading systems; • Shows traders and operations the Firm’s commitments summed over time, sorted: • By bond or bond group (called ‘book’); • By currency; • By legal entity. • Critical for keeping all the plates spinning!

  11. Roles in the System • Front Office: • Traders, salespeople, trading assistants. • Middle/BackOffice: • Daily trading operations staff, controllers,clearance support staff and operations. • IT Personnel: • Looking after all of the above.

  12. The Bigger Picture • Repo acts as both a source and a sink for information streams from many places: • As a sink for: currency rate information, inter-company trading data; • As a source for: other firm groups (cash ledgers, stock control, trading position management systems), legal, regulatory and clearing houses. • Projections: only a sink (easier to look after!)

  13. Trade is Booked REPO(repo_db) PROJECTIONS(secproj_db) Traders Trade Entry Security Projections Window Traders & Operations OUTSIDE

  14. Postings Generated REPO(repo_db) PROJECTIONS(secproj_db) Traders Trade Entry genesis2 Security Projections Window Traders & Operations OUTSIDE

  15. Trade Hits Projections REPO(repo_db) PROJECTIONS(secproj_db) Traders Trade Entry genesis2 trades Security Projections Window Traders & Operations OUTSIDE

  16. Trade Posts Internally REPO(repo_db) PROJECTIONS(secproj_db) Traders Trade Entry genesis2 trades Security Projections Window Traders & Operations OUTSIDE Cash Bonds

  17. Clearance Instructed REPO(repo_db) PROJECTIONS(secproj_db) Traders Trade Entry genesis2 trades Security Projections Window Traders & Operations OUTSIDE Cash Bonds Banks, Clearing Houses

  18. Confirm Received REPO(repo_db) PROJECTIONS(secproj_db) Traders Trade Entry genesis2 trades Security Projections Window settlements Traders & Operations OUTSIDE Cash Bonds Banks, Clearing Houses

  19. Trade Settled – Done! REPO(repo_db) PROJECTIONS(secproj_db) Traders Trade Entry genesis2 Exception Window trades Operations Security Projections Window settlements Traders & Operations OUTSIDE Cash Bonds Banks, Clearing Houses

  20. No Settlement? REPO(repo_db) PROJECTIONS(secproj_db) Traders Trade Entry genesis2 Exception Window trades Operations Security Projections Window Traders & Operations OUTSIDE Banks, Clearing Houses

  21. Call The Counterparty! REPO(repo_db) PROJECTIONS(secproj_db) Traders Trade Entry genesis2 Exception Window trades Operations Security Projections Window Traders & Operations OUTSIDE Banks, Clearing Houses

  22. Confirm Received (At Last) REPO(repo_db) PROJECTIONS(secproj_db) Traders Trade Entry genesis2 Exception Window trades Operations Security Projections Window settlements Traders & Operations OUTSIDE Banks, Clearing Houses

  23. Trade Settled – Done! REPO(repo_db) PROJECTIONS(secproj_db) Traders Trade Entry genesis2 Exception Window trades Operations Security Projections Window settlements Traders & Operations OUTSIDE Banks, Clearing Houses

  24. Transactions: 1 • Absolutely essential in sizeable, production systems: • To ensure integrity of data during protracted operations on the database; • To regulate concurrent accesses to the database. • By making it appear to whatever application or process that they are the only database user.

  25. When It’s All Gone Pear-Shaped • Sometimes, there is no alternative to manipulating the database directly: • The dreaded ‘Manual Update’! • These are dangerous things to do: • In ‘Production’, you’ve no room for error; • How can you make adjustments and verify they’re correct?

  26. Transactions: 2 • Also extremely important in supporting a real-time system where occasional manual updates are required: • Used correctly, you can do no wrong! • Just remember to always be in a transaction, or bad things may happen ... • And don’t forget the transaction afterwards ...

  27. Environmental Separation • Using separate databases for different activities: • Development and Testing; • QA: Quality Assurance; • Production and Archival. • Much safer to use ... • ... as long as you remember where you’re looking at!

  28. Multi-Tier Applications • The ‘n-tier’ application development idea: • 2-tier: user interface and database; • 3-tier: UI, business logic, DB; • 4-tier: UI, middleware, BL, DB. • and so on ... • A lot depends upon the application’s particular requirements: • Requirements can change over time ...

  29. Example: Trade Entry • A collection of windows used by traders to book trades. • ‘2.5-tier’ architecture (apps, SPs+DB): • In the first place, a good thing: TE was the only source of trades and adjustments; • As time passed, a bad thing: business logic intertwined with UI software, plus additional sources of trading information.

  30. Problems • The rules for ‘genesis2’: • Flexible rules, based on trade types (and other criteria); • Easily configurable without changing the program; • But is there a price to pay? • What if the rules are wrong?

  31. So, are there ... • Any questions?

More Related