Database

Database

1. Client/Server Database Environment

   • Database Server Architecture
     • 2-tiered approach  Client is responsible for : lI/O processing logic + Some business rules logic Server performs all data storage and access processing = DBMS is only on server Thinner Client      
     • stored procedure
       • module of code that implements application logic (business rules)
       • stored on the server where it runs when called
       • Advantages
         • Network traffic decreases
           • processing is moved from the client to the server
         • Security improves
           • stored procedure is accessed rather than the data, and code is moved to the server, away from direct end-user access
         • Data integrity improves
           • multiple applications access the same stored procedure
         • Performance improves for compiled SQL statements
     • Advantages
       • Reduced network traffic
         • database is stored on the server so data is not sent across the network as much which reduces the communication load
       • Reduced power required for each client
         • only the database server requires processing power adequate to handle the database, and the database is stored on the server, not the clients.
       • Centralise
         • Centralise user authorisation, integrity checking, data dictionary maintenance, and query and update processing on the database server
     • Disadvantages
       • takes more time
         • Writing stored procedures takes more time than using tools to create an application.
       • difficult to change the DBMS
         • Stored procedures’ proprietary nature reduces their portability and may make it difficult to change the DBMS without having to re-write the procedures.
       • upgrades
         • Each client must be loaded with the application that will be used at that location – upgrades to an application will require each client to be upgraded separately.
       • bottle neck
         • The server can be a bottle neck, the server can be important for critical parts of the organisation, so if it goes down there is a serious problem.
   • Common solution for hardware and software organisation that implements the idea of distributed computing
     • many Client/Server environment use a network to support a group of personal computer
     • Advantages
       • Functionality can be delivered in stages to the end users
         • arrives more quickly as the first pieces of the project are deployed
       • GUI interfaces
         • common in client/server environments encourage users to utilise the applications functionality.
       • flexibility and scalability of client/server solutions
         • facilitate business process reengineering
       • improving response times and reducing network traffic
         • more processes are performed close to the source of data being processed.
       • Client/server architectures allow the development of web-enabled applications
         • Internally
           • facilitating the ability of organisations to communicate effectively
         • Externally
           • conduct external business over the internet
     • VS mainframe computing
       • Both are ways of centralising data storage
         • allowing multiple users access to shared data
       • implement distributed approaches to computing and open systems development
       • mainframe systems are a centralised approach to computing
   • middleware
     • Software that allows an application to interoperate with other software without requiring the user to understand and code the low level operations necessary to achieve interoperability.
     • Asynchronous remote procedure call (ARPC)
       • Client requests a service but does not wait for a response,  will typically establish a point to point connection with the server and  perform other processing until the response arrives
     • Message oriented Middleware (MOM)
       • Asynchronous, sends messages and continues other processing until the response is received
     • Object Request Broker
       • Makes it possible for applications to send objects and request services in an object oriented system
     • SQL oriented data access
       • ability to translate generic sql statements into the database specific SQL needed to complete the query
   • Parallel Computer Architectures
     • symmetric multi-processing (SMP)
       • Multiple CPUs Shared RAM     
     • share nothing architecture (MPP)
       • (massive parallel processing) enables each CPU to be allocated its own memory.
   • File Server Architecture
     • All processing is done at the PC that requested the data      Entire files are transferred from the server to the client for processing. Fat Client     
     • file server
       • device that manages file operations
       • shared by each of the client PCs attached to the network
     • Limitation
       • large amount of network traffic that using a file server generates
       • each client workstation must contain a full version of the database management system
   • Thin Client
     • not do very much in the way of processing as the application server will handle the application logic, and the database server will handle the database logic.
   • Fat Client
     • do lots of processing and the server will act in a similar manner to an external hard drive
   • three-tier architecture
     • Three layers: 1. Client  - Browser 2. Application server - Web Server 3. Database server - DBMS Thinnest Client
     • Advantages
       • Scalability
         • more scalable than two tier one - middle tier cam be used to reduce the load on a database server  by using a transaction processing monitor to reduce the number of connections to a server.
       • Technological flexibility
         • easier to change DBMS engines with three tier architecture and the DBMS is on its own tier
       • Lower Long term costs:
         • Use of off-the-shelf components or services in the middle tier --> reduce costs -->  substitution of modules within an application rather than an entire application
       • Better match of systems to business needs
         • New modules can be built to support specific business needs rather than building more general, complete applications.
     • Disadvantages
       • High short-term costs
         • presentation component be split from the process component   – needs more programming.
       • Tools and training
         • requires specialised knowledge and experience
       • Incompatible standards
         • Several competing standards have been  developed but a universal one has yet to be prevalent

2. Physical Database Design

   • Information Required Critical Decisions Data volume and usage analysis Designing fields (attributes) Denormalization
   • Replication
     • Purposely storing the same data in multiple locations of the database
   • Physical Files
     • Denormalisation
       • Process of transforming normalised relations into unnormalised physical record specifications.
       • inefficiency of normalisation
         • Often all the attributes that appear together within a relation are not used together and data from different relations are needed together to answer a query or produce a report.  --> Thus, although normalised relations solve data maintenance anomalies, normalised relations, if implemented one for one as physical records, may not yield efficient data processing.
       • situations
         • one-to-one relationship with each other
         • associative entity between two parent entities with non-key attributes.
         • one-to-many relationship with reference data
           • between two entities where the one is considered reference data and is not in a relationship with other entities
     • RAID
       • RAID stands for Redundant Array of Inexpensive Disks and is a set or array of physical disks drives that appear to the database user (and programs) as if they form one large logical storage unit
     • partitioning
       • techniques that are used to divide a table into groups of data that are used together.
       • Horizontal
         • distributes the rows of a table into two or more separate files  depending on the value of a specific field.
       • Vertical
         • distributes the columns of a table into two or more separate files depending on  the use of specific columns by different processes.
   • File Organization
     • how the files are arranged on the disk.
     • sequential
       • The storage of records in a file in sequence according to a primary key value.  To locate a particular record, a program must normally scan the file from the beginning.  Adding new records requires rewriting the file and deleting records can require reorganization.
     • indexed
       • records are stored either sequentially or non-sequentially with an index that allows software to locate individual records.
     • Selecting Criteria
       • Data retrieval speed
       • transaction processing throughput rate
         • Data input and maintenance transaction processing throughput rate.
       • Storage efficiency.
       • Failure or data loss protection level.
       • Frequency of data reorganisation required.
       • Ability to accommodate growth.
       • Security protection provided.
     • Primary Key
       • unique identifier for the record
     • Secondary Key
       • a field or combination of fields for which more than one record may have the same combination of values.
   • Using and selecting indexes
     • Index =  either a separate table that contains organization of records for quick retrieval or  a table or other data structure used to determine the location of rows in a file that satisfy some condition
     • Rules
       • Use on larger tables
       • databases volatilety
         • Use indexes heavily for non-volatile databases; limit the use of indexes for volatile databases
       • primary key
         • Index the primary key of each table (automatic in Oracle)
       • search fields
         • Index search fields (fields frequently in WHERE clause)
       • SQL ORDER BY and GROUP BY commands
         • Fields in SQL ORDER BY and GROUP BY commands
       • limit
         • DBMS may have limit on number of indexes per table and number of bytes per indexed field(s)
       • Null values - not referenced
         • Null values will not be referenced from an index
     • hash-index
       • A file organisation that uses hashing to map a key into a location in an index. where there is a pointer to the actual data record matching the hash key.
   • major inputs
     • Normalized relations
       • including volume estimates
     • Definitions of each attribute.
       • Data dictionary Name, Description, Data type & size, Domain (all ranges are inclusive) + example or format, Primary Key, FK, Required, Default  Value, Index
       • field specification
         • Decisions
           • data type
             • Define the data type used to represent values of the field.
           • data integrity controls
             • Establish data integrity controls for the field, including default values, ranges, null value controls, and referential integrity.
           • missing values
             • Determine how missing values for the field will be handled.
           • Other
             • such as display format, must be made as part of the total specification of the information system, but those specifications are typically handled by the programs rather than the DBMS.
     • Descriptions (including frequencies) of where and when data are used
       • in entry, retrieval, deletion, and updating.
     • Expectations or requirements
       • for response time and data security, backup, recovery, retention and integrity
     • Descriptions of the technologies used for implementing the database.
   • key decisions
     • data type used
       • Choosing the storage format (data type) for each attribute from the logical data model.
     • Grouping attributes from the logical data model into physical records.
       • Grouping attributes from the logical data model into physical records.
     • Arranging similarly structured records in secondary storage
       • Arranging similarly structured records in secondary storage so that individual and groups of records (file organizations) can be stored, updated and retrieved rapidly.
     • Selecting structures
       • Selecting structures (indexes and database architectures) for storing and connecting files to make retrieving related data more efficient.
     • Preparing query strategies
       • Preparing strategies for handling queries against the database that will optimize performance.

3. Enhanced ERD

   • Process
     • Generalization
       • defining a more general entity type from a set of more specialized entity types -  known as a BOTTOM-UP approach.  An example is starting with cat, fly, and fish putting all common characteristics into the super-type animal. 
     • specialization
       • defining one or more subtypes of the super-type - forming super-type/subtype relationships -  TOP-DOWN approach   an example is starting with the super-type aircraft and the defining subtypes such as airplane and helicopter
     • super-type
       • generic entity type that has a relationship with one or more subtypes.
     • subtype
       • sub grouping of the entities in an entity type which has attributes that are distinct from those in other sub groupings.
       • subtype discriminator
         • an attribute of the super-type which the values determine the target subtype(s)
         • disjoint rule
           • simple attribute with alternative values to indicate the possible subtypes. e.g. ‘H’ and ‘M’
         • overlap rule
           • composite attribute whose subparts pertain to different subtypes.  Each subpart contains a Boolean value to indicate whether or not the instance belongs to the associated subtype.
     • inheritance
       • all attributes and relationships that belong to a specific super-type also belong to any subtypes of that super-type.
   • completeness constraint
     • type of constraint that addresses the question of whether an instance of a superclass must also be a member of at least one subtype.  It consists of two rules: total specialisation and partial specialisation.
     • total specialisation rule
       • rule specifies that each entity instance of the super-type must be a member of some subtype in the relationship. It is represented by a double line.
     • partial specialisation rule
       • specifies that an entity instance is allowed not to belong to any subtype. It is represented by a single line.
   • disjointness constraint
     • addresses the question of whether an instance of a super-type may simultaneously be a member of two (or more) subtypes. It consists of two rules: overlap and disjoint.
     • overlap rule
       • states that an instance of the super-type could be more than one of the subtypes. It is represented by O
     • disjoint rule
       • an instance of the super-type can be only ONE of the subtypes. It cannot simultaneously be a member of any other subtype. It is represented by d.
   • Classification of business rules
     • Derivation
     • Structural assertion
       • structural business rule = rule expressing static structure e.g. A student can make zero or many enrolments. A student’s ID, name and address are to be stored.
     • Action assertion
       • rule expressing constraints/control of organizational actions A customer may only place an order if that customer’s credit status is good. A student cam only enrol on a course if that student has passed the prerequisite course.
       • Objects
         • used when defining an action assertion using the enhanced ERD notation.
         • Anchor
           • the object on which the action may be limited
         • corresponding
           • object influencing the ability to perform the action on the anchor object.

4. Database Integrity

   • Business Rules (Operational Constraints)
     • Handled by triggers within DBMS Business rules stored in database Adds complexity to database Hidden rules and database activity VS referential integrity Business Rules = Action Assertion = external factors that influence database design. These rules define the operation of the business and restrict how the database should function. Business rules are derived from the regulations and directives of the organisation the database is being developed for.
     • triggers
       • A trigger is a procedure that is stored in the database and implicitly executed ("fired") when a table is modified. Triggers can cascade and cause other triggers to fire.
       • Parts
         • Once the event occurs, if the condition stated within the trigger is met, then a prescribed action is taken.
         • Event
         • Condition
         • Action
   • Data entry/modification
     • Table definitions, constraints
     • Rules
       • entity integrity rule
         • no primary key attribute (or component of a primary key) can be null because primary key must be unique (cannot be null).
       • Referential integrity constrain
         • states either each foreign key value must match a primary key value in another  relation; or the foreign key value must be null. Therefore a foreign key value does not have to be unique and can also be null VS Business Rules: Referential integrity ensures that the relationship between tables is error-free by guaranteeing that a foreign key in one table refers to an existing primary key in another table. Referential integrity is a law that is applied to all relational databases.
         • FK set Null
           • relationship between the child and parent (from the child’s (dependent’s) point of view) is optional, the foreign key value may be null
     • Missing Data
       • required field
         • To avoid missing data, a field can be defined as required field; any such field must have a valid value in it when the record is inserted.
         • Enforce
           • NOT NULL
             • To define a field as NOT NULL when creating the table.
     • Managing changes to PK
       • restricted delete
         • performs deletion of the parent record if the primary key value is not referenced by a foreign key in child table
       • cascade delete
         • performs deletion of records in the child table as well as the record in the parent table
       • delete set null
         • The foreign key in the dependent side is set to null if deleting a record from the parent side. --> So data in the dependent side is not lost.  For example, when we delete a customer from the customer table (the parent table) who has an id of 1 and there are 5 records corresponding with the customer in the order table (the child or dependent table), the 5 records can remain in the order table with a foreign key (customer id) value of null.
   • Transaction support
     • A transaction is a sequence of database operations and logical unit of work. It makes possible to return to previous correct state.
     • Commit
       • Definition: ends the transaction with making changes permanent
     • Rollback
       • Definition:  ends the transaction with undoing all changes in the current transaction.

5. Concurrency & Transaction Control

   • concurrency control
     • process of managing simultaneous operations against a database so that data integrity is maintained and the operations do not interfere with each other in a multiuser environment.
     • optimistic concurrency control
       • takes the approach that most of the time other users do not want the same record, or if they do, they only want to read (but not update) the same record. advantage of optimistic concurrency control over pessimistic concurrency control is performance improvement because read-only transactions can run concurrently with updating transactions without loss of database consistency.
       • versioning
         • Versioning takes the approach that most of the time other users do not want the same record, or  if they do, they only want to read (but not update) the same record.  With versioning there is no form of locking making transactions easier to write for the application developer.
     • pessimistic concurrency control
       • Locking
         • Locking data means that access to that data is denied to other users until the transaction is either committed or rolled back.
         • Deadlock
           • A deadlock is an impasse that results when two or more transactions have locked a common resource and each waits for the other to unlock that resource.
           • deadlock prevention
             • ways of managing concurrency involves application programs locking all records they require at the beginning of a transaction and unlocking all record once the transaction is completed.
           • deadlock resolution
             • ways of managing concurrency allows deadlocks to occur but builds mechanisms into the database management system for detecting and breaking the deadlocks.
         • shared locks
           • user locking the data and other users can read the data but none of the users can change the data. Both shared and exclusive locks deny other users the opportunity to change data that is locked and can be applied at record and table levels.
         • exclusive locks
           • user locking the data can read and update the data and all other users are denied all access (including read) to the data. Both shared and exclusive locks deny other users the opportunity to change data that is locked and can be applied at record and table levels.

6. Database Development

   • database application
     • application program (or set of related programs) that is used to perform a series of database activities (create, read, update, and delete) on behalf of database users.
     • Metadata
       • data that describes the properties or characteristics of end-user data, and the context of that data.
     • data dependence
       • is where file descriptions are stored within each database application program that accesses a given file. Any changes to a file description requires changes to the file descriptions for all programs that access the file.
     • Data independence
       • Data independence is the separation of data descriptions from the application programs that use the data.
     • three-schema database architecture
       • External schema
         • view or views of managers and other employees who are the database users.
       • Conceptual schema
         • This view combines the different external views into a single coherent definition of the enterprise’s data.
       • Internal schema
         • logical view
           • representation of the data for a type of database architecture (e.g. relational)
         • Physical View
           • the representation of the data for a particular DBMS (e.g. ORACLE)
   • Benefits
     • database approach compared to conventional file systems.
     • Program-data independence
       • data descriptions (metadata) are stored in a central repository thus allowing changes to the structure (within limits) to be made without effecting changes in the application program
       • Reduced program maintenance
         • in a database environment, the data are more independent of the application programs that use them; within limits changes to and data or the application programs can be made without  necessitating a change in the other.
     • Planned data redundancy
       • The database approach does not eliminate redundancy entirely but it enables the designer to carefully control the type and amount of redundancy.
       • Improved data consistency
         • By eliminating or controlling data redundancy the opportunities for inconsistency are reduced
     • Improved data sharing
       • A database is designed as a shared resource; authorised internal and external users can be granted permission to use the database and each user is provided one or more user views to facilitate this use.
     • Increased productivity of application development
       • The data approach can greatly reduce the cost and time for developing new business applications.
     • Enforcement of standards
       • The database repository provides database administrators with a powerful set of tools for developing and enforcing data quality standards.
     • Improved data quality
       • Database designers can specify integrity constraints that are enforced by the database management system; a constraint is a rule that cannot be violated by the database users
     • Improved data accessibility and responsiveness
       • With a relational database, end users without programming experience can often retrieve and display data even when it crosses traditional departmental boundaries.
     • Improved decision support
       • some databases are designed expressly for decision support applications; for example a database can be developed to support financial analysis.
   • Information Systems Architecture
     • conceptual blueprint or plan that expresses the desired future structure for the information system in an organisation.
     • key corporate planning object
       • Organizational units
         • various departments of the organization.
       • Organizational location
       • Business functions
         • Related groups of business processes that support the mission of the organization.
       • Entity types
         • Major categories of data about the people, places, and things managed by the organization.
       • Information systems
         • The application software and supporting procedures for handling sets of data.
   • systems development life cycle
     • traditional methodology used to develop, maintain, and replace information systems. It consists of a series of steps that are followed usually in a linear approach. The users are usually consulted only at the beginning and end of the cycle.
   • prototyping
     • iterative process of systems development in which requirements are converted to a working system that is continually revised through close work between analysts and users.