TOPICS (Click to Navigate)
Wednesday, 10 September 2014
Database management system (DBMS) is a collection of interrelated data and a set of programs to access those data.
d) Credit card transactions
e) Tele communication
i) Human resources
The advantages of using a DBMS are
a) Controlling redundancy
b) Restricting unauthorized access
c) Providing multiple user interfaces
d) Enforcing integrity constraints.
e) Providing back up and recovery
a) Physical level – shows “how the data are stored?”
b) Logical level – shows “What data are stored?”
c) View level – shows “various views of the entire database”
Physical schema: The physical schema describes the database design at the physical level, which is the lowest level of abstraction describing how the data are actually stored. It is about files, indices etc.
Logical schema: The logical schema describes the database design at the logical level, which describes what data are stored in the database and what relationship exists among the data. This schema is used by application programmers to develop applications.
The logical schema is sometimes called as Conceptual schema. The Conceptual schema defines the logical structure using a data model. In the relational model, the Conceptual schema presents data as a set of tables.
Ex. Courses(cid: string, cname:string, credits:integer)
A data model is a collection of conceptual tools for describing data, data relationships, data semantics and consistency constraints.
A storage manager is a program module that provides the interface between the low level data stored in a database and the application programs and queries submitted to the system.
The storage manager components include
a) Authorization and integrity manager
b) Transaction manager
c) File manager
d) Buffer manager
The storage manager is responsible for the following
a) Interaction with the file manager
b) Translation of DML commands into low level file system commands
c) Storing, retrieving and updating data in the database
The storage manager implements the following data structure
a) Data files
b) Data dictionary
A data dictionary is a data structure which stores meta data about the structure of the database ie. the schema of the database. A Data Dictionary contains the information like list of tables, list of attributes of tables, number of records stored in a table, indices of tables etc. of any database.
It is a centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format
The entity relationship model is a collection of basic objects called entities and relationship among those objects. An entity is a thing or object in the real world that is distinguishable from other objects.
An entity is represented by a set of attributes. Attributes are descriptive properties possessed by each member of an entity set.
Ex: possible attributes of customer entity are customer name, customer id, customer street, customer city.
A relationship is an association among several entity sets.
Ex: A depositor relationship associates a customer with each account that he/she has.
Entity set: The set of all entities of the same type is termed as an entity set. [In relational model, a table (a relation) is an entity set].
Relationship set: The set of all relationships of the same type is termed as a relationship set. [In relational model, relationship set is a table which connects two or more tables].
Single valued attributes: attributes with a single value for a particular entity are called single valued attributes. For example, Emp_Name attribute can have only one employee name for every record.
Multi-valued attributes: Attributes with a set of value for a particular entity are called multi-valued attributes. For example, some companies demands one to few phone numbers from every customer. For such case, a record of a customer may have one or more phone numbers.
Stored attributes: The attributes stored in a data base are called stored attributes.
Derived attributes: The attributes that are derived from the stored attributes are called derived attributes. Ex. The value for Age attribute can be derived from the stored attribute DOB.
Composite attribute is a type of attribute which represents a value with its subparts. They can be divided in to meaningful sub parts. For example, Address can be a composite attribute with subparts Street, City and Pincode.
In some cases a particular entity may not have an applicable value for an attribute or if we do not know the value of an attribute for a particular entity. In these cases null value is used.
Physical Data Independence: The power of changing the physical schema without affecting the logical schema is called Physical Data Independence. For example, we need not change the logical or conceptual structure of a table before changing or upgrading the way the data stored.
Example, the different indexes used or created on various attributes may be changed without changing the structure of a table.
Logical Data Independence: It is about changing the logical schema without affecting the next higher level ‘View schema’. We can alter the table structure by adding new fields which would not affect all the application programs or views.
The degree of relationship type is the number of participating entity types.
Weak entity set: entity set that do not have key attribute of their own are called weak entity sets.
Strong entity set: Entity set that has a primary key is termed a strong entity set.
Mapping cardinalities or cardinality ratios express the number of entities of an entity set to which another entity set can be associated. Mapping cardinalities must be one of the following:
For example, a passenger can book many tickets in a railway reservation system, a customer may have one or more accounts in a bank system, etc.
Total: The participation of an entity set E in a relationship set R is said to be total if every entity in E participates in at least one relationship in R.
Partial: if only some entities in E participate in relationships in R, the participation of entity set E in relationship R is said to be partial.
Example: Let us assume the tables Customer, Account and Loan. The Customer table is connected to Account through a relationship set and connected to Loan through another relationship set. Irrespective of having account or loan, all the customers’ personal information is stored in Customer table. Here, all the records in Account table are connected to one or more Customer records. But, not all the records of Customer are connected to one or more Account records. That is, Loan customers are connected differently. Hence, the relationship between Account to Customer is Total and Customer to Account is Partial.
DDL: Data base schema is specified by a set of definitions expressed by a special language called a data definition language. [Data definition means the creation, deletion, and modification of table structures]
DML: A data manipulation language is a language that enables users to access or manipulate data as organized by the appropriate data model. [Manipulation means Insertion, Deletion, and Updation/Modification of records and values]
The relational model uses a collection of tables to represent both data and the relationships among those data. The relational model is an example of a record based model.
Relation is a subset of a Cartesian product of list of domains.
For each attribute there is a set of permitted values called the domain of that attribute. For example, an attribute Phone permits set of Phone numbers as values, not anything other than phone numbers.
Minimal super keys are called candidate keys. A key for which no proper subset of it is a key is called a candidate key.
Primary key is chosen by the database designer as the principal means of identifying an entity in an entity set. Primary key is one of the identified Candidate keys.
A super key is a set of one or more attributes that collectively allows us to identify uniquely an entity in an entity set. A super key can contain a candidate key as a part of it.
The relational algebra is a procedural query language. It consists of a set of operations that take one or two relation as input and produce a new relation as output.
A relation schema r1 derived from an ER schema may include among its attributes the primary key of another relation schema r2. This attribute is called a foreign key from r1 referencing r2. In other words, it is an attribute that refers the set of permitted values from other tables to which it is connected.
Schema definition – Creating database
Storage structure and access method definition – Taking care of the way data stored and accessed.
Schema and physical-organization modification – Altering database design, and organizing database to improve performance.
Granting of authorization for data access – Permits different users with different permissions.
Routine maintenance – Backup of database, checking the performance, adding and removing users etc.
Naïve users / New users – They are new to DBMS or even computers and access any database through any applications by form filling. For example, those users who have email accounts need not do anything with the DBMSs.
Application Programmers – They are the programmers who develop the applications which are accessed by other users, say naïve users.
Sophisticated users – They are interacting with the database using query languages like SQL.
Specialized users – They are interacting with the database through non-conventional systems.
Procedural DML is a Data Manipulation Language used to manipulate data in the database. For manipulating the database, it needs the user to specify the following;
· What data are needed?
· How to get those needed data?
Overall, it follows a procedure.
Example: Relational Algebra
Declarative DML is Non-Procedural DML. It requires the user to specify what data are needed. You need not provide on how to get those data.
SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...
Advanced concepts in DBMS Advanced Database Topics (Click on the links to navigate) Advanced Concepts in D...
Set of solved exercises in Normalization / Normalization Solved Examples / How to find candidate keys, and primary keys in database? /...
Query Processing in DBMS / Steps involved in Query Processing in DBMS / How is a query gets processed in a Database Management System? / Q...