database technology session ii 10:15 - 11:00 dr s sudarshan & dr d b phatak iit bombay
TRANSCRIPT
-
Database TechnologySession II 10:15 - 11:00
Dr S Sudarshan & Dr D B PhatakIIT Bombay
Data Base Technologies
-
OVERVIEWStorage devicesFiles and Index Structures Legacy Systems and CobolRelational Databases and SQLTransactions and ACID propertiesSystem ArchitecturesSecurity and Audit
Data Base Technologies
-
Storage DevicesMain memoryvolatile, lost on power failureexpensive and relatively smallHard disknon-volatile, reasonably fast accessrelatively cheap, and largemain storage system for databasesMean time to Failure: ~5 years
Data Base Technologies
-
RAID SystemsGoal: improve storage reliabilityData stored on multiple disks if one disk fails, data still available on othersEssential for safety of dataHardware RAIDexpensive, very high availabilityfor 24x7 applications (24 hrs X 7 days/wk)Software RAIDcheaper, use if some downtime is allowable
Data Base Technologies
-
Storing Data on DisksByte: unit of information one characterFile: sequence of bytesFile system:stores multiple files organizes files into directories/folders
Data Base Technologies
-
File StructureInformation stored within large filesSequential filessorted on a key (e.g., account number)Index needed for efficient accesse.g. find information of account 2345similar to library card catalogs
Data Base Technologies
-
Traditional File ProcessingCOBOL: Common Business Oriented languageFiles contain sequence of records, e.g. Record per accountComplex program for each taske.g. withdrawal, deposit, average balance, File structures often very complexmotivated by efficiency, but become hard to understand
Data Base Technologies
-
SEGMENT OF A SAMPLE COBOL PROGRAM Open Input SALARYTABLE-FILE.Perform Varying I from 1 to 11.Move zero to GROUPTOT [I].END-PERFORM.PROCESS-NEXT.Read EMPTAB-FILE At End Go To End-job.- - - - -Add salary to Group tot [K].Go to Process-Next.END-JOB. - - - -
Data Base Technologies
-
PROGRAMMING PARADIGMS4 GL: What to doSet processing3 GL: How to do (Algorithm)Record by Record Processing2 GL: Algorithm at the lowest levelDetails of Individual Operations
Data Base Technologies
-
Relational DatabasesMotivation: simplify storage structureseasy to use language for queries/updatesefficiency is job of systemautomatic optimizationLegacy systemsSystems built using COBOL and older data modelsStill in wide use, but declining usage
Data Base Technologies
-
Relational Databases Provide: Tabular Data model: simple, yet powerfulA Standard Query Language: SQLMature Products with Reliable, Fault-Tolerant Operations availableGood Performance High number of transactions per secondParallel operation for scalability (handle growth)Distributed and Replicated Data BasesInteroperation, High availability
Data Base Technologies
-
Relational Model: Tabular DataAccountTransactions
Data Base Technologies
Account Number
Name
Address
Balance
23456
D. B.Phatak
A-12, IIT, Powai
40,000,000
23457
Bill Gates
Microsoft Corp
9999,999,999
23458
S.Sudarshan
C-148, IIT Powai
1,000
Date
Acct Num
Debit
Credit
Balance
1/4/99
23458
2000
3000
1/4/99
23456
2000
3000
4/4/99
23456
9000
12000
4/5/99
23456
200000
212000
4/5/99
23458
4000
7000
-
Querying the Database using SQLselect name, balance from account where name = D. B. Phatak insert into transactions values (3/5/99, cash, 5000, -, 14000)select acct-num, avg(balance) from transactions where date between 1/4/99 and 30/4/99 groupby acct-num
Data Base Technologies
-
Transactions: ACID PropertiesTransactions: e.g. Debit/credit Problems:Failures (e.g., power, disk storage)Concurrent transactionsSolution:Support for ACID properties
Data Base Technologies
-
ACID PropertiesAtomicity: Transaction appears to either run completely or not at all -- no partial stateConsistency: Integrity checks (e.g., balance >= 0)Isolation: Locks on data so that transactions do not step on each others toesDurability: Data/updates are never lost
Data Base Technologies
-
Database Application ClassesOLTP: Online Transaction Processingsupports many small transactionsDecision SupportSummaries/aggregatesOLAP: Online Analytical Processing
Data Base Technologies
-
Performance IssuesImportant for high volume systemse.g., internet bankingSizing / Performance tuningdeciding on CPUs, memory size, disk size, number of disks, etctuning transaction code to reduce disk I/Oto reduce lock conflicts between concurrent transactionsScalability via parallelismSmoothly handling more traffic as the business grows
Data Base Technologies
-
Access SecurityAuthenticationIdentifying who a person isPasswordswidely used, but quite insecureSmart cards, biometrics, etcMore on this laterAccess privilegeswho is allowed to do whatAudit trailTrace back what happened
Data Base Technologies
-
Authorization MechanismsPrivilegese.g., read table, update table, insert row in table, delete row from table, privilege to grant privilegesEach user given specific set of privileges he/she needs Rolesprivileges given to roles (e.g., teller, manager) users authorized to play roles
Data Base Technologies
-
Audit TrailsDatabase keeps track of all transactions in an audit tablewhat the transaction did, and who ran itMechanisms for tracing back what transactions affected a particular entity (such as an account)
Data Base Technologies
-
MORE ON SECURITYEncryption, Session KeyData Encryption Standard (DES-3)Shared Private keyRSA, DH AlgorithmsPublic/Private key
Data Base Technologies
-
SQLSQL is Intergalactic DataspeakStrong Data Definition Language (DDL)Domain DefinitionsIntegrity ConstraintsSecurity & Access Control ProvidedViews, PermissionsInteractive Queries
Data Base Technologies
-
SQLEmbedded SQL:Use of SQL commands from within 3GL programs (programs in C/COBOL/)Open Data Base Connectivity (ODBC): Standard for client server interconnectivity, using C languageJDBC: Like ODBC, for Java languageSQL Standards: 86, 89, 92, SQL-3 Draft
Data Base Technologies
-
SQLNo Standards for User Interface Screens (Forms & Menus)ReportsSpecial Tools AvailableNative to ProductIndependent Vendors 24
Data Base Technologies
-
DATABASE SERVERSMajor playersOracle, IBM DB2, Microsoft SQL Server, Informix, Sybase, IngressWide range of performance, features, and price
Data Base Technologies
-
Database ArchitecturesCentralizedDumb terminals connected to single serverClient ServerSmarter client machines connect to serverMain work still done at serverParallel ServersWork divided between multiple CPUsDistributedMultiple independent databases in cooperation
Data Base Technologies
-
EVOLUTION OF CLIENT SERVER COMPUTINGMultiple Computers to Independently Handle Component Tasks of an ApplicationNeed to Partition Tasks Judiciously
Data Base Technologies
-
TWO TIER PARTITIONING
Data Base Technologies
Distributed Remote Distributed Remote Data Distributed Data
Presentation Presentation Application Logic Management Management
Data
Management
Application
Logic
Presentation
Logic
Presentation
Logic
Data
Management
Application
Logic
Presentation
Logic
Data
Management
Application
Logic
Application
Logic
Presentation
Logic
Data
Management
Application
Logic
Presentation
Logic
Data
Management
Data
Management
Application
Logic
Presentation
Logic
-
CLIENT - SERVER TERMINOLOGYService: Provided by the ServerEach Client Is a ConsumerShared Resources : Managed by ServerClient : Initiator of a Request
Data Base Technologies
-
SERVERSFile Servers : NovellDatabase Servers : SQLTransaction Servers : OLTPTP lite (Stored Procedures)TP heavy (TP monitors)
Data Base Technologies
-
SERVER FUNCTIONSWait for RequestsHandle Concurrent TransactionsTake care of VIP requestsAssign prioritiesAuthentication, Authorisation Audit trails
Data Base Technologies
-
CLIENT FUNCTIONSTypically Processes Running on Front-end Machine (PC)Provide User InterfaceSupport Graphics, Multimedia
Data Base Technologies
-
THREE TIER C/STier One : ClientTier Two : Application ServerTier Three: Database Server
Data Base Technologies
-
MORE C/S TERMINOLOGYThin or Fat Client Fat Servern-Tier ArchitectureOOUI
Data Base Technologies
Data Base TechnologiesDr. Sudarshan & PhatakData Base TechnologiesDr. Sudarshan & PhatakData Base TechnologiesDr. Sudarshan & PhatakData Base Technologies55Dr. Sudarshan & PhatakData Base Technologies56Dr. Sudarshan & PhatakData Base Technologies57Dr. Sudarshan & PhatakData Base Technologies54Dr. Sudarshan & PhatakData Base TechnologiesDr. Sudarshan & PhatakData Base TechnologiesDr. Sudarshan & PhatakData Base Technologies65Dr. Sudarshan & PhatakData Base Technologies67Dr. Sudarshan & PhatakData Base TechnologiesDr. Sudarshan & PhatakData Base Technologies69Dr. Sudarshan & PhatakData Base Technologies73Dr. Sudarshan & PhatakData Base Technologies74Dr. Sudarshan & Phatak