Get Latest Exam Updates, Free Study materials and Tips
The fact and figures that can be recorded in the systemand that have special meaning assign to it is called data.
Database is a collection of data atom school in one place and havingsomecommon background between them.
Database management system is a collection of inter related data andset of process to access that data.
Characteristic of database management system are:
File system | Data base management system |
---|---|
i. It consist of collection of individual file accessed by application programs. | i. Computerized records keepingsystemm is used in the Databasemanagement system |
ii. It is designed to allow pre- determined access to data. | ii. It allows flexible access to data. |
iii. It co-ordinates only with the physical access to the data. | iii.It co-ordinates only with the physicaland logical access to the data. |
iv. Unauthorized access cannot be restricted in the file system. | iv.Unauthorized access can be restrictedin the file system. |
v. Data redundancy cannot be controlled in this system. | v. Data redundancy can be controlledinthis system. |
Advantages of database management system:
a. Data redundancy can be reduced.
b. Inconsistent data can be avoided.
c. Data can be share between multiple user.
d. User can have centralised control of the data.
e. Data standard can be enforced.
f. Security and restriction can be applied to the data present in the database.
g. Integrity of the database can be maintained.
h. Data independency can be provided among the users. i. New application maybe develop using the existing database.
Data independence is defined as the capacity to change one Level Schemawithout changing any schema at the next higher level.
A Database Administrator (DBA) is individual or person responsibleforcontrolling, maintenance, coordinating, and operation of database management system.
Roles of DBA are;
a. Creating and maintaining database standards and policies.
b. Supporting database design, creation, and testing activities.
c. Managing the database availability and performance, including incident andproblem management.
d. Administering database objects to achieve optimum utilization.
e. Defining and implementing event triggers that will alert on potential databaseperformance or integrity issues.
f. Performing database housekeeping, such as tuning, indexing, etc.
g. Monitoring usage, transaction volumes, response times, concurrency levels, etc.
Database systems comprise complex data-structures. In order to makethesystem efficient in terms of retrieval of data, and reduce complexity in termsofusability of users, developers use abstraction
In One-Tier Architecture the database is directly available to the user, theuser can directly sit on the DBMS and use it i.e.; the client, server and the Database all present on the same machine.
The application on the client-side establishes a connection with the server-side in order to communicate with the DBMS.
3-tier architecture divides the complete system into three inter-relatedbut independent modules as shown below:
ER diagram is the first step of the database design to specify the desiredcomponent of the database system and the relationships among those components
Components of ER model are entity, attributes and relationship
Entity is anything in Real world object which may have physical or logical existence.
Entity set is a collection of same type of data.
Each entity has its own property which describes that entity these suchproperties are called as attributes.
Relation ship is an association among two more than two entities.
There are two type entities namely,
Key is an attribute of an entity which must have a unique value by whichanyrow can be uniquely identify.
Relationship set is define as the collection of all relationship of same type.
Degree of relationship is the number of participating entity types in a particular relation.
Based on the mapping constraits relationship are of four types:
A diagrammatic technique for displaying the concept of specialisationgeneralization and aggregation when they arises in EER Schema the resultingSchema diagram is called as EER diagram
Relation are a logical structure which is a collection of tables consistinghorizontal rows also called tuples and vertical columns also called as attributes. Tables are know as relations.
a. A table composed of rows and columns.
b. Each table in a database has its unique table name.
c. Each table rows represents a single entity occurrence within the entity set.
d. All values in a same column must conform to the same format of data.
e. Each table must have a single attribute or set of attributes that uniquelyidentifieseach row
Relation has its own properties which describes that relatiion such propertiesare know as attributes.
A single row in relational table which contains all the information about asingle entity is called as tuple.
The intersection column and row in a relational table which represents dataofentity is called as domain.
Relational database schema consists of a number of relation schema associated with that database. It describes the structure of relational database
The intersection column and row in a relational table which represents dataofentity is called as domain.
Constraint make sure that only authorized user will make modifications todatabase and changes should not lead to loss of data consistency and correctness.
Types of relational constraint are as follows:
a. Required data constraint/Nullness constraint : This constraint ensure that the attributes in the database are not allowed to contain null values or blanks.
b. Check constraint : The check constraint is used to ensure that attributevalue satisfies specific condition as specified by data requirements or user.
c. Default keyword : Default keyword is used to add a default specific value, if attribute value is not provided by user.
Relational Algebra is procedural query language, which takes Relation as input and generate relation as output.
Unary relational operations:
Software design deals with transforming the customer requirements, as SQL (Structure Query Language) is the computer language into store, manipulate and retrive data store in the relational data bases.
DDL (Data Definition Language) statements are used to build and modify the structure of the table and the other objects in the database.
The DDL commands are as follows:
i. CREATE: Used to create database object.
ii. ALTER: Used to modified database object.
iii. DROP: Used to drop the database object.
iv. RENAME: Used to renamed the database object.
v. TRUNCATE: Used to delete all the records from the table.
DML (Data Manipulation Language) used to modify or manage data inthedatabase.
The DML commands are as follows:
i. INSERT: Used to add new records in the table.
ii. UPDATE: Used to modify the existing data in the table.
iii. DELETE: Used to delete some or all records from the table.
Data control language (DCL) is used to control various user actions or privileges in database.
DCL commands are as follows:
i. GRANT: Give some privileges to user for performing task on database.
ii. REVOKE: Take back permission given from user
Transaction Control Language (TCL) is a set of special commands that deal with the transactions within the database.
TCL commands are as follows:
i. COMMIT: It is used to permanently save all the modifications done (all thetransactions) in the database.
ii. ROLLBACK: It is used to undo all the transactions / modifications that have done to the database.
A SQL Join statement is used to combine data or rows from two or more tables based on a common field between them
There are four types of, namely
i. INNER JOIN : The join operation that do not preserve the non-matching tuples in the result.
ii. LEFT OUTER JOIN : The join operation that preserve the tuples onlyfromthe table that is present to the left of the left outer join operator or named before left outer join operator.
iii. RIGHT OUTER JOIN : The join operation that preserve the tuples only from the table that is present to the right of the right outer join operator or named after right outer join operator.
iv. FULL OUTER JOIN : The FULL OUTER JOIN combines the results of both left and right outer joins and returns all matched or unmatched rows from the tables on both sides of the full outer join operator.
A triggers is a procedure that is automatically invoked by the database management system in response to specific changes to the database or a table in database.
Aggregate functions are the functions that can summarize the data of thetable.
Some aggregate functions in sql are as follows:
i. AVG() : This function is used to calculate average of all the values from all thetuples of the column.
ii. COUNT() : This function is used to calculate count of all the tuples of the column.
iii. SUM() : This function is used to calculate sum of all the values fromall the tuples of the column.
iv. MIN() : This function is used to give the minimum value in the column.
v. MAX() : This function is used to give the maximum value in the column.
i. A GROUP BY clause creates a set of data, containing several sets of records grouped together based on some conditions.
ii. HAVING is conditional clause which checks data for specific search condition. A HAVING clause is like a WHERE clause, but applicable only to groups as awhole, wheras the WHERE clause applies to individual rows.
iii. The WHERE clause is used to filter records. It is used to extract only those tuples from the table that fulfill a specified condition.
Integrity constraint are a set of rules that ensures that the data insertion, updating and other processes have to be perform in such a way that it does not thedata integrity.
a. Domain constraint: It ensure that the data value entered for that particular column matches with the data type defined for that column.
b. Check constraint: It is used to ensure that attribute value satisfies specificuserdefined condition.
c. Referential integrity: It is a value appearing in a one relation for a given set of attributes also appears for another set of attributes in another relation. Referential integrity constraint is defined between two tables to maintain the consistency among tuples in the two relations.
d. Key constraints: In dbms these are set of rules that are defined for primary key or for foregin key in dbms.
e. Entity integrity constraint: It ensures that the primary key attribute in a relation, should not accept a null value
A view is defined as a database object that allows us to create a virtual table in the database whose contents are defined by a query or taken from one or more tables.
Pitfalls of relational database management system suggests the effects when database design is incorrect or invalid.
The process of breaking up or dividing a single relation into two or more subrelations is called as decomposition of a relation.
Functional dependency is a relationship that exists between two relations when one attribute uniquely determines another attribute.
a. Full functional dependency b. Partial functional dependency c. Transitive functional dependency d. Trival functional dependency e. Multivalued functional dependency
The properties of functional dependency are:
Normalisation is the process of step by step decomposition of complex records into simple records.
A relation in 1NF states that all attributes in the relation must have atomic(single) values and all attributes in a tuple(row) must have a single value.
Conditions for a relation to be 1NF are as follows:
a. Every value of each attributes must be atomic.
b. No composite values will be there.
c. All entities in any column must be of same kind.
d. All the values in the column must unique.
e. No two rows are identical.
A relation is said to be in 2NF if it is in 1NF and any non-prime attributes does not have any partial dependency on any prime attributes i.e. all non-prime attributes are have full functional dependency on prime attributes.
A relation is said to be in 3NF if it is in 2NF and any non-prime attributes does not have any transitive dependency on any prime attributes i.e. all non-prime attributes are have full functional dependency on prime attributes.
A relation is said to be in BC NF if it is in 3NF and there should not be any overlapping among non-prime attributes.
A relation is said to be in 4NF if it is in BC NF and there should not be any multivalued dependencies among attributes.
The goals of the decomposition are:
A transaction is a sequence of one or more SQL statements that combined together to form a single logical unit of work
There are five states of transactions namely, Active state, Partially committedstate, Failed state, Committed state and Aborted State.
i. Active state: As soon as transaction execution starts it enters in the activestateand remain in this state till transaction finishes.
ii. Partially committed state: As soon as last operation in transaction is executedtransaction goes to partially committed state. At this condition the transactionhascompleted its execution and ready to commit on database server.
iii. Failed state: A transaction enters the failed state after the systemdeterminesthat the transaction can no longer proceed with its normal execution.
iv. Aborted State: Fail transaction enters in aborted state where it has beenrollback restoring into prior state and gets terminated.
v. Committed state: After successful completion of transaction it enters inthecommitted state and gets terminated
In a database, a deadlock is an unwanted situation in which two or more transactions are waiting indefinitely for one another to give up locks.
A deadlock can we detected by using wait for graft method in which a graphis drawn based on the transaction and their lock on the resources if the graph is a close loop or a cycle then there is a deadlock otherwise not
Deadlock can be prevented by using two methods wait-die Schema andwound wait schema.
i. Wait-die schema: In this method if a transaction for a resources that is held by other transaction then database management system checks the time stamp of both the transaction and allows the older transaction to wait until the resources is
available for execution.
ii. Wound-Wait Schema: In this method if an older transaction request for a resources hold by a younger transaction then an older transaction forces a younger transaction to kill the younger transaction.
In order to maintain consistency in a database before and after transaction certain properties are followed. These properties are called ACID properties.
They are as follws:
i. Atomicity: This property states that either the entire transaction takes placeat
once or doen’t happen at all.
ii. Consistency: It states that integrity constraits must be maintained so that the database is consistent before and after the transaction.
iii. Isolation: This property ensures that multiple transaction can occur concurrently without leading to the in consistency of database state.
iv. Durability: This property ensure that once the transaction has completed it’s execution, then update and modifications are stored in the database and written to disk and they persist even if a system failure occurs.
Serializability is the concept in a transaction that helps to identify which non-serial schedule is correct and will maintain the database consistency.
There are two type of serializability as follows:
i. Conflict serializability: A schedule is called conflict serializable if it can be transformed into a serial schedule by swapping non-conflicting operations.
ii. View serializability: A schedule is said to be View-Serializable if it is view equivalent to a Serial Schedule (where no interleaving of transactions is
possible).
Timestamp base protocol is a general method to achieve serializability order of transaction for execution in which execution can be decided in advance using its time at which transaction entered in system.
Lock is a mechanism which is important in a concurrent control. It controls concurrent access to a data item. It assures that one process should not retrieve or update a record which another process is updating. If the locking is not done properly, then it will display the inconsistent and corrupt data. It manages theorder between the conflicting pairs among transactions at the time of execution.
In log based protocol, any transaction cannot read or write data until it acquires an appropriate lock on it.
There are two type of locks, namely Shared lock and exclusive lock.
i. Shared lock: This type of locking is used by the DBMS when a transaction wants to only read data without performing modification to it fromthe database.
ii. Exclusive lock: This type of locking is used by the DBMS when a transaction wants to read or write (i.e. performing modification) data in the database
Lock is granted only when no other conflicting type of lock on it, is held by other transaction.
Lock is rejected when other conflicting type of lock is held by other transaction
Thomas write rule is a concurrency control mechanism that is implemented to maintain and manage the concurrent transactions and processes in the database management system.
Not a member yet? Register now
Are you a member? Login now