Sample data base design and Data management

Sample Data base design:

The introduction described an extremely simple database and a simple transaction that uses it. We discuss how that database could be structured as well as how the transaction would access it.

The database comprises of the records:

ACCOUNT    (ACCOUNT_NUMBER,CUSTOMER_NUMBER, ACCOUNT_BALANCE, HISTORY)
CUSTOMER (CUSTOMER_NUMBER, CUSTOMER_NAME, ADDRESS,.....)
HISTORY (TIME, TELLER, CODE, ACCOUNT_NUMBER, CHANGE, PREV_HISTORY)
CASH_DRAWER (TELLER_NUMBER, BALANCE)
BRANCH_BALANCE (BRANCH, BALANCE)
TELLER (TELLER_NUMBER, TELLER_NAME,......)

This is an extremely cryptic description that says that a customer record has fields giving the customer name, customer number, address and other attributes.

The CASH_DRAWER, BRANCH_BALANCE as well as TELLER files (sets) are rather small (less than 100000 bytes). The ACCOUNT as well as CUSTOMER files are large (about 1,000, 000,000 bytes). The history file is very large. If there are fifteen transactions against every account per month and if each history record is fifty bytes then the history file grows 7500000000 bytes per month. Traffic on BRANCH_BALANCE as well as CASH_DRAWER is high and access is by BRANCH_NUMBER and TELLER_NUMBER respectively. Consequently these two sets are kept in high-speed storage and are accessed via a hash on these attributes. Traffic on the ACCOUNT file is high however random. Most accesses are via ACCOUNT_NUMBER however some are via CUSTOMER_NUMBER. Consequently the file is hashed on ACCOUNT_NUMBER (partitioned set). A key-sequenced index NAMES is sustained on these records that give a sequential and associative access path to the records ascending by customer name. CUSTOMER is treated similarly (having a hash on customer number and an index on customer name.) The TELLER file is ordered as a sequential set. The HISTORY file is the most interesting. These records are written once and after that are only read. Almost each transaction generates such a record and for legal reasons the file must be maintained forever. This reason it to be kept as an entry sequenced set. Fresh records are inserted at the end of the set. To permit all recent history records for a specific account to be quickly located a parent child set is defined to link each ACCOUNT record (parent) to its HISTORY records (children). Every ACCOUNT record points to its most recent HISTORY record. Every HISTORY record points to the previous history record for that ACCOUNT.

Given this structure we are able to discuss the execution of the DEBIT_CREDIT transaction outlined in the introduction. We will presume that the locking is done at the granularity of a page and that recovery is achieved by keeping a log (on transaction management.)

At beginning the data manager allocates the cursors for the transaction on the ACCOUNTS, HISTORY, BRANCH and CASH_DRAWER sets. In every instance it gets a lock on the set to insure that the set is available for update. Locking at a better granularity will be done during transaction execution (see locking section). The first call the data manager observes is a request to find the ACCOUNT record with a given account number. This is complete by hashing the account number thereby computing an anchor for the hash chain. Buffer administrator is called to bring that page of the file into fast storage. Buffer administrator looks in the buffer pool to see if the page is there. If the page is present buffer administrator returns it immediately. Or else it finds a free buffer page slot reads the page into that buffer and returns the filled buffer slot. Data administrator then locks the page in share mode therefore that no one else modifies it. This lock will be supposed to the end of the transaction. The record module searches the page for a record with that account number. If the record is established its value is returned to the caller and the cursor is left pointing at the record.

The next demand updates account balance of the record addressed by the cursor. These needs converting the share mode lock acquired by the previous call to a lock on the page in exclusive mode therefore that no one else sees the new account balance until the transaction successfully completes. As well the record component must write a log record that allows it to undo or redo this update in case of a transaction or system abort (see section on recovery). Additionally the transaction must note that the page depends on a certain log record therefore that buffer manager is able to observe the write forward log protocol (see recovery section.)Finally the record component does the update to the balance of the record.

Next the transaction fabricates the history record as well as inserts it in the history file as a child of the fetched account. The record component calls buffer administrator to get the last page of the history file (ever since it is an entry sequence set the record goes on the last page.) For the reason that there is a lot of insert activity on the HISTORY file the page is likely to be in the buffer pool. Therefore buffer manager returns it the record component locks it and updates it. Subsequent the record component updates the parent-child set therefore that the new history record is a child of the parent account record. the entire of these updates are recorded in the system log in case of error.

The subsequent call updates the teller cash drawer. This necessitates locking the appropriate CASH_DRAWER record in exclusive mode (it is located by hash). An undo-redo log record is written as well as the update is made.

A alike scenario is performed for the BRANCH_BALANCE file. When the transaction ends data administrator releases all its locks and puts the transaction's pages in the buffer manager's chain of pages eligible for write to disk.

If data manager or else any other component detects an error at any point it issues an ABORT_TRANSACTION command which initiates transaction undo (see recovery section.)This causes data administrator to undo all its updates to records on behalf of this user and then to release all its locks and buffer pages.

The recoveries as well as locking aspects of data manager are elaborated in later sections. I suggest the reader design as well as evaluate the performance of the MONTHLY_STATATEMENT transaction described in the introduction as well as a transaction which given two dates as well as an account number will display the history of that account for that time interval.

Latest technology based Operating System Online Tutoring Assistance

Tutors, at the www.tutorsglobe.com, take pledge to provide full satisfaction and assurance in Operating System help via online tutoring. Students are getting 100% satisfaction by online tutors across the globe. Here you can get homework help for Operating System, project ideas and tutorials. We provide email based Operating System help. You can join us to ask queries 24x7 with live, experienced and qualified online tutors specialized in Operating System. Through Online Tutoring, you would be able to complete your homework or assignments at your home. Tutors at the TutorsGlobe are committed to provide the best quality online tutoring assistance for Operating System Homework help and assignment help services. They use their experience, as they have solved thousands of the Operating System assignments, which may help you to solve your complex issues of Operating System. TutorsGlobe assure for the best quality compliance to your homework. Compromise with quality is not in our dictionary. If we feel that we are not able to provide the homework help as per the deadline or given instruction by the student, we refund the money of the student without any delay.