Summary INF3703 STUDY NOTES.

INF3703 STUDY NOTES. An informaton system is composed of people, hardware, software, databases, applicatons and procedures  Systems analysis is the process that establishes the need for and the scope of an informaton system  Systems development – is the process of creatng an informaton system  The performance of an information system depends on DB design and implementaton; app design and implementation; and administrative procedures  DB Design objectves – create complete, normalised, non-redundant and fully integrated conceptual, physical and logical DB models SDLC Phases (PADIM)  Planning (inital assessment and feasibility study)  Analysis (user requirements, existng system evaluaton and logical system design) o Thorough audit of user requirements o Development of data models  Detailed systems design( detailed system spec)  Implementaton (coding, testng and debugging, installaton, fne-tuning)  Maintenance (Evaluaton, Maintenance and Enhancement) o Correctve changes – in response to errors o Adaptve changes – changing business environment o Perfective maintenance –system enhancement Database Lifecycle (IDITOM)  Database Inital Study o Analyse the company situaton (company objectves, operations and structure) o Defne problems and constraints o Defne objectves o Defne scope and boundaries  Design (most critcal phase) o 2 views of data – business view and designer’s view Database Design Flow a) Conceptual Design (DBMS-independent) - create an abstract DB structure that represents real-world objects o DB analysis and requirements (user views, output and transactionprocessing requirements) o ER modelling and normalisation (entties, attributes, relatons, ER diagrams, table normalisaton) o Data model verification – identfy main processes, validate reports, queries, views, integrity, sharing and security o Distributed DB design – defne locaton of tables, access requirements and fragmentaton strategy b) DBMS Software selection (costs, features, underlying model, portability and hardware requirements) c) Logical design (DBMS-dependent) – Translate conceptual model into defnitions for tables, views, etc. Objective – to map the conceptual model into a logical model which can be implemented on an RDBMS. o Create logical data model o Validate logical data model using normalisation o Assign and validate constraints o Merge various local models o Review logical model with user d) Physical Design (Hardware-dependent) – defne storage structure and access paths o Analyse data and usage o Translated logical model relatons into tables o Defne indexes o Defne user views o Estmate data storage requirements o Determine DB security for users  Implementaton and Loading (create DB, Create views, relations, assign rights, load DB) o Also includes security, performance, integrity, standards; and backup and recovery Oracle encryption feature – Transparent Data Encryption – can decrypt DB columns. Circuit-level gateway blocks all incoming connectons to any host but itself  Testng and Evaluaton (testng and fne-tuning, modify physical design, modify logical design, upgrade DBMS)  Operaton  Maintenance and Evoluton o Preventatve, correctve, adaptve, access permission assignment, DB stats, security audits and periodic summaries and reports DB Design Strategies (infuenced by scope and size of system, company operations, mgmt. style and structure) Y Top-Down – Identfy entties/data sets and the elements within the entites – ER Models Y Bottom-Up – Identfy the elements and group then into entites/data sets – Normalisaton Centralised vs. Decentralised DB Design Ce n t r a li se d Y Normally suited for smaller companies or small databases where 1 DBA designer can defne the problem, create the conceptual model, verify conceptual model, etc. Y Illustraton – (Conceptual model – (conceptual model verifcaton (user views, system processes, data constraints)), data dictonary) D e c e n t ra l i s e d Y When the data component of a system requires many enttes with complex relations on which complex operations are performed. Y Teams of designers independently create the conceptual model for a partcular component and each conceptual model is merged to form one overall conceptual model Y Illustraton – (Data Component – (conceptual models, verifcaton, aggregation, conceptual model, data dictionary) D BA R o le s Y DA (Informaton Resource Manager) has higher authority than DBA DBA Managerial Role (Controlling and Planning) Y E nd - U s e r Suppo r t – gather user reqs, build end-use confdence, resolve conficts and problems, fnd solutons to informaton needs, ensure quality and integrity of applicatons and data; and manage the training and support of end users Y Data backup and recovery Y Data distributon and use – Ensure that data is distributed to the right people at the right tme Y Data security, privacy and integrity Y Y U se r A c ce s s M a n a g e m e n t o Defne users, groups, roles o Manage user access privileges o Assign passwords o Control physical access o Defne views o DBMS usage monitoring, access control DBA Managerial Roles include: Y End-User Support Y Policies, Procedures and Standards Y Data security, Privacy and Integrity Y Data Backup and Recovery Y Data Distribution and Use Technical DBA Roles Y Evaluatng, Selectng and Installing the DBMS and utlities Y Designing and Implementaton of databases and applicatons Y Testng and Evaluation of Databases and Applicatons Y Operate the DBMS, Utlites and Applicatons Y Training and Supportng Users Y Maintenance of the DBMS, Utlites and Applications Critcal success criteria for an Informaton Systems Architecture: Y Management commitment Y End-use involvement Y Thorough company situation analysis Y Defned standards Y Training Y Small pilot project C h a p t e r 1 1 DB Design stages – Conceptual, Logical and physical design Y Module cohesivity – Must be high. High cohesivity indicates a strong relationship amongst enttes Y Module coupling - must be low. Indicates how independent modules are from each other CODD’S relational DB rules Y Logical independence – if the logical structure of the database changes, the user should not be afected in any away Y Physical independence – If the physical structure of the database changes, the user should not be afected in any way C on c e p t u a l D e s i gn S t e p s Y Data analysis and requirements (info needs, users, sources, consttution) o Sources of information (developing and gathering use data views, observing current system and interface with the systems design team) o Business rules must be easy to understand and widely disseminated o Description of operations – a doc that provides precise, detailed, up-to-date and thoroughly reviewed descripton of business activities o Business Rules have the following benefits: • Communicaton tool between designers and users • They allow the designer to understand the nature of, role and scope of user data • They allow the designer to understand business processes • They allow the designer to develop appropriate relationship partcipation rules and foreign key constraints Y Entity relationship modelling and normalisation (standard t be used for documentaton must be communicated) o Identfy, analyse and refne business rules o Identfy main entites o Defne relationships amongst entties o Defne attributes, primary keys and foreign keys for each entty o Normalise the entites o Complete the inital ER diagram o Verify the model with end-users o Modify the ER diagram based on user feedback Y Data Model Verification o Identfy the model’s central entty o Identfy each module and it’s components o Identfy each module’s transacton requirements o Verify all processes against the ER model o Make changes as suggested o Repeat steps 2 to 5 for all modules Y Distributed Database Design (spreading the DB across multple distributed DBs) L o gi c a l D a t a b a s e D e s i gn Y Create the logical model o Create relations for strong enttes o Create relations for weak enttes o Map multvalued attributes o Map binary relations o Map ternary relations o Map supertype andsubtype relationships Y Validate the model using normalisation o Normalise all entties to @least 3NF Y Assign and validate integrity constraints (domain, entity and referential integrity) Y Merge local models constructed for diferent parts together Y Review logical model with users P h y s ic a l D a t a b a s e D e s i gn Y Analyse data volume and database usage (shown in composite usage or transacton usage map) o Identfy most frequent and critcal transactions o Determine which entties will be accessed by critcal transactions Y Translate logical relations into tables Y Determine suitable fle organisaton o Heap Files – contain randomly ordered records. Used only when a large quantty of data needs to be added to a database for the frst time. Slow searches and impractcal. o Sequential file organisations – Records are usually sequenced based on primary key. Insertons are costly, deletions lead to unused space. Searches are very slow o Indexed file organisations – fles that are sorted based on one more fields. Index is created to quickly locate records. Faster searches, access, aggregaton. Indexes are logically and physically independent of the data in the associated table o Hashed file organisation – Uses a hashing algo to map primary key value to a specifc record. Disadvantage – the uniqueness of the hash cannot be guaranteed o Types of indexes • Primary index – placed on unique felds/primary key. One primary index per fle. • Secondary index – can be placed on any field in the fle that is unordered • Mult-level index – used when the index becomes large and is split into separate indexes Indexes can be sparse or dense. Dense indexes are faster. • Balanced trees (B-Trees) – more efficient in storing ordered data. Lef node parent node, parent node right node • Bitmap indexes – usually applied to attributes which are sparse in their given domain. 2 dimensional arrays. Used in the following instances • Column has low cardinality • The table is not used for data manipulaton • Specifc queries reference a number of low cardinality values • Join Indexes (Bitmap Join Indexes) – used in data warehousing and applies to columns from 2 or more tables whose value come from the same domain. Y Creatng indexes o Primary Index - CREATE UNIQUE INDEX INDEX_NAME ON TABLE(CLOUMN_NAME) o Secondary Index – CREATE INDEX INDEX_NAME ON TABLE(COLUMN_NAME) Y Defne User Views Y Estmate Data Storage Requirements o Estmate size of each row by adding the length of each data type o Estmate number of rows taking into consideraton growth o Multply the size by the estimated number of rows Y Determine database security for users o Two sets of privileges (system and object) o Systems allows the executing of DDL commands like CREATE TABLE o Object allows the executng of DML commands (insert, update) o Roles (CREATE ROLE Role_Name, GRANT SELECT ON CUSTOMERS TO Role Name, GRANT Role_Name to user_to_be_added_to_role C h a p t e r 1 2 Y Concurrency control – managing concurrent transactions Y Transaction – logical unit of work that must be entirely completed or entrely aborted Y Consistent database state – a state in which all data integrity constraints are satisfed Y Transactions must satsfy (ACIDS) – Atomicity, Consistency, Isolation, Durability and Serialisaton o Atomicity – All transactons must be completed, otherwise the transaction will be aborted o Consistency – the permanence of the database’s consistent state o Isolation – Data used during the executng of a transacton cannot be used by a second transaction untl the frst one is completed o Durability – Once transactions are committed, they cannot be undone o Serialisability – Ensures that the concurrent execution of several transactons yields consistent results Y Scheduler – special DBMS program that establishes the order in which transactions are executed. Satsfies isolation and serialisation Y A lock guarantees exclusive use of a data item to a current transacton. Controlled by a lock manager. Y Lock levels – database, table, page or field(attribute) Y Database lock – suitable for batch-processing but not for mult-user databases Y Table lock – locks all rows in a table. Causes bottlenecks. Not suitable for mult-user databases Y Page-level lock – an entre diskpage is locked. Most frequent locking opton. Y Row-level lock – Less restrictve. Improves availability of data. Adds management overhead. Y Field-level lock – Allows concurrent transactons to access the same row as long as they require access to different felds in the row. Most fexible but rarely implemented due to overhead Y Lock Types o Binary – either locked or unlocked. Too restrictve o Exclusive lock – Access is reserved for the transaction using the data. No other access (read or write) o Shared locks – concurrent transactons are granted read access as long as the transaction is read-only Y 2-Phase locking – defne how transactons acquire and relinquish locks. Ensures serialisation but cannot prevent deadlocks. o Growing phase – transaction acquires all required locks without unlocking any data. Transaction is locked once all locks have been acquired o Shrinking phase – Transaction releases all locks and cannot obtain a new lock Y Deadlock – When 2 transactons wait indefnitely for each one to release a lock – deadly embrace Deadlock Control techniques: Y Deadlock preventon – a transacton requitng a lock is aborted when there is a possibility for a deadlock Y Deadlock detecton – DBMS periodically tests the database for deadlocks, if found, one transaction is aborted Y Deadlock avoidance – A transacton must obtain all the locks it requires before executng D a t a b a s e R e c o v e r y Y Write-ahead logging – transacton logs are written before executon Y Redundant transaction logs – redundant copies of logs are kept W a it / D ie o r Wound /Wait S c h e m e s Wait/Die Y Older transacton requesting lock waits untl newer transactons releases the lock Y Younger transaction requestng lock dies and rescheduled Wound/Die Y Older transacton requesting lock wounds the newer transaction. Newer transacton is rescheduled Y Newer transaction requesting lock waits untl the older transaction releases the lock Database Recovery using the deferred write/update process: Y Identfy the last checkpoint in the DB (when the transactons were last saved to a physical disk) Y Transactions saved before the checkpoint need not be recovered Y For transactons committed afer the checkpoint, the DBMS uses transaction logs to redo the transactions in ascending order (oldest to newest) Y Transactions that were rolled back or not committed afer the checkpoint need not be restored Database Recovery using the write through/immediate update process: Y Identfy the last checkpoint Y Transactions saved before the checkpoint need not be recovered Y For transactons committed afer the checkpoint, the DBMS uses transaction logs to redo the transactions in ascending order (oldest to newest) Y Transactions that had a rollback or not committed after the last checkpoint are rolled back startng with the newest transactons