DBMS
From Wikibooks, the open-content textbooks collection
[edit] Introduction
- Data
- raw facts.
- Data Processing
- performing operations on the input data to generate output.
- Database
- collection of related information about a particular enterprise.
- Database Management System
- collection of interrelated data, set of programs to access the data conveniently and efficiently.
[edit] File System
Typically data is stored in files.
[edit] Need for Database Management Systems
Data access through file systems suffers from the following problems.
- minimal data redundancy
- inconsistency
- concurrent access
- data isolation
- integrity
- atomicity
- security
A good database management system solves all the above mentioned problems which a user generally faces in a file system.
[edit] Database Users
Following are the types of database users:
- Database Administrator - a database administrator performs the following activities:
- Monitoring performance
- Granting user authority to access the database
- Application programmers
- Data analysts
- Naive users
[edit] Enterprise Application Architecture
- three tier architecture
- Modern enterprise application architecture consists of the following layers
- application client
- application server
- database system
Present trend in the enterprise architecture is moving towards n-tier architecture in which each of the above three layers is further distributed among multiple systems or layers
Definition
Database management system
developed by yogesh kale
[edit] Database Tools
- query tools
- administration tools
[edit] Database Architecture
Following are the different database architecture types:
- Centralized
- Client-server
- Parallel (multi-processor)
- Distributed
[edit] Physical Storage
-
- data
- statistical data
- meta data/dictionary
- integrity constraints
- domain constraints
- referential integrity
- assertions
- authorization
- data storage
- integrity constraints
- indices
[edit] Database abstraction
- physical abstraction
- logical abstraction
- view abstraction
[edit] Database Schema
Definition : Overall design of data base. Schema contains 'No of records + Type of data + No of attributes'
- View schema
- logical schema
- physical schema
[edit] Database Instance
Definition: The term instance is typically used to describe a complete database environment, including the RDBMS software, table structure, stored procedures and other functionality. It is most commonly used when administrators describe multiple instances of the same database.
Also Known As: environment
Examples: An organization with an employees database might have three different instances: production (used to contain live data), pre-production (used to test new functionality prior to release into production) and development (used by database developers to create new functionality).
[edit] Data Model
What is data model:it consists of some concepts to describe the structure of database i.e. data type,ralations, and constraints that should hold on the data. Eg. ER model
- xml data Model - semi structured
- relational data model
- object oriented
- object relational
- network
- hierarchy
[edit] Database Languages
- procedural - what data is required and how to get this data
- declarative - what data is required without describing how to get this data
- data manipulation language (DML)
- data definition language (DDL)
- data storage language
[edit] Relational Database
A database based on relational algebra or relation model is called relation database
[edit] Relational data model
- relation
- a table in a relational database is called relation in the mathematical language of relational algebra. relations are unordered.
- attribute
- column of a table in database table is called attributes. columns or attributes have names.
- domain
- set of permissible values for an attribute ( or column) is called domain.
- tuple
- a row in the database table is called tuple in the mathematical language of relational algebra. order of tuples in a relation has no significance.
- database
- a database is a collection of multiple relations.
- schema
- a database design is called schema, alternatively, a schema can refer to namespace within a database.
- cardinality of a relation
- number of attributes in a relation is called cardinality of te relation.
Normalization theory deals with design of relational database schema.
[edit] Keys
- key
- any subset of a relation is called key.
- super key
- a key is called super key if it is sufficient to identify a unique tuple of a relation.
- candidate key
- a minimal super key is called candidate key i.e no proper subset of a candidate key is super key.
- primary key
- a candidate key chosen as a principal to identify a unique tuple of a relation.
- foreign key
- a key of a relation which is a primary key of some other relation in the relational schema.
[edit] Database Design
- entity
- e.g., employee
- entity set
- relationship
- relationship set
- domain
- attribute
- e.g., gender
- composite attribute
- degree of relationship
- cardinality of relationship
- e.g., one to one, one to many, many to many
- weak entity
- identifying relationship
- non-identifying relationship
- discriminator
[edit] ER Diagram
- entity relationship data model
- Introduction to Data Modeling at University of Texas, Austin
Modelling concepts
- Specialisation
- Generalisation
- Categorisation
- Aggregation
[edit] Normalization
Normalization is the formal process for deciding which attributes should be grouped together in a relation.The process of normalization was first developed by E.F.CODD.
[edit] First Normal Form
A relational schema is in first normal form if the domains of all attributes are atomic. First Normal form is characterized by the property of atomicity.
[edit] Second Normal Form
Second Normal form is characterized by the property of functional dependency. a relation is in second normal form if it is in 1st normal form and every non key attribute is fully and functionally dependent on the primary key.
[edit] Third Normal Form
transitive dependency : A relation is in third normal form , if it is in second normal form and no transitive dependencies exist.
[edit] Boyce Codd Normal Form (BCNF)
BCNF is based on functional dependencies that take into account all candidate keys in a relation. For a relation with only one candidate key, 3rd normal form and BCNF are equivalent. A relation is in BCNF if and only if every determinant is a candidate key. '''' now what is a determinant? consider the following functional dependency: A→B where A and B are attributes in relation R. it says that B is functionally dependent on A. here A is referred to as determinant and B is the dependent.
BCNF is slightly stronger than 3nf.
[edit] Fourth Normal Form
- multivalued dependencies are removed
[edit] Fifth Normal Form
Any remaining anomalies are removed.in this normal form we isolate semantically related multiple relationships.
Anomalies can be :
Insertion Anomaly,Deletion Anomaly or Modification/update Anomaly.
Any remaining anomalies are removed.in this normal form we isolate semantically related multiple relationships.
[edit] Query Languages
- query
- the retrieval of tuples from the relations of a relational schema
- query language
- a language used to retrieve information (tuples) from the relations of a relational schema.
types of query language
- procedural language
- non-procedural language
Mathematical query languages
- Relational algebra
- Tuple relational calculus
- Domain relational calculus
[edit] Relational algebra
relational algebra is a procedural query language
-
- operations
- selection (r σ s) -
- projection (r ∏ s) -
- union: (r ∪ s)
- set difference: (r – s)
- Cartesian product: (r X s)
- rename: ( ρr )
- intersection:
- natural join:
- division:
- operations
[edit] Tuple relational calculus
[edit] Domain relational calculus
[edit] SQL
SQL stands for Structured Query Language. SQL is a non procedural language.
References
relational algebra
[edit] XML
[edit] Application Design
[edit] JDBC
[edit] Storage Manager
Storage manager module of the database provides the interface between the following component:
* data stored in the database * the application programs * queries submitted to the system
[edit] Components of a Storage Manager
[edit] file manager
[edit] buffer manager
[edit] data integrity manager
[edit] transaction manager
[edit] Storage access
[edit] File organization
[edit] Indexing and hashing
[edit] Parsing and translation
[edit] Optimization
[edit] Evaluation
[edit] Data Storage
[edit] File Structure
[edit] Data Retrieval or Querying
[edit] Indexing
[edit] Hashing
[edit] Query Processing
- Query Processor
- Query parsing
- Query optimizer
- Query evaluation engine
[edit] Query Optimization
[edit] Transaction
- transaction
- collection logically related operations in a database application.
- Transaction-management
- ensures that the database remains in a consistent (correct) state despite system failures (e.g., power failures and operating system crashes) and transaction failures.
- authorization engine
[edit] Concurrency control
- Concurrency-control
- ensuring the consistency of the database inspite of interaction among the concurrent transactions,
Á
[edit] Recovery
[edit] Object Relational Database Management System
[edit] Distributed Database Management System
[edit] Data Mining and Analysis
[edit] Parallel Databases
[edit] References
Online interactive SQL tutorial
Some topics of DBMS by Lecturer Manik Chand Patnaik
