Posts

Showing posts from January, 2018

What is basic SELECT statement?

Image
SELECT Statement
A SELECT statement retrieves data from the database. With SELECT statement PROJECTION, SELECTION and JOIN can be performed on database tables.
As data in relational database is stored in TABLES. TABLE has ROW and COLUMN structure. With SELECT statement all rows and all columns can be retrieved, this is known as PROJECTION. But, if rows and columns are retrieved on the basis of some condition, it is known as SELECTION. And suppose if some information is needed and that is stored in two or more different tables but are related, use JOINS to retrieve it from two or more tables.
In SQL, basic SELECT statements are as follows:
SELECT * FROM [table_name];
SELECT[column1][column2][column3],..... FROM  [table_name];
SELECT and FROM are known as clauses. 
SELECT clause allows to specify columns to be selected from the database table. 
FROM clause allows to specify table name that has those column to be selected.
Semicolon at the end of SELECT statement specify the end of SQL statemen…

What is E-R model? What are the various symbols used to draw E-R diagram?

Image
E-R Model (Entity-Relationship Model) E-R model is a high-level data model. It is used in conceptual design phase of a database designing process for defining a conceptual schema. E-R model comprises of entities, relationships and attributes.
E-R Diagram: A graphical or diagrammatic presentation of all the concepts of E-R model. 
Entity: A real world object or thing is known as entity. For example EMPLOYEE, STUDENT, TEACHER, SUBJECT, COMPANY, PROJECT etc. are entities in real world. It is designated as small letters  for EMPLOYEE,  for STUDENT etc..
Attribute: A property that describes an entity in a real world is known as attribute. For example an EMPLOYEE entity have attributes like Employee Number, First Name, Last Name, Date of Birth, Address, Gender, Mobile Number etc. An entity have particular values for each attribute.
Entity Type: A set of entities having same attributes. It describes the schema or intension of entities that share the same structure. It is designated as capital…

What do you mean by data model? What are different types of data models?

Image
Data Model: A data model is defined as a set of concepts for describing the structure of the database. Database structure comprises of data types, relationships, constraints applied to the data and basic operations for accessing the data.
Data models are classified as follows: Conceptual Data Model: It is an abstract-level or summary-level data model. It is used in strategic data projects. It uses high-level key concepts that are easy to understand by end user and very close to the way of how end user see data in an enterprise or an organization. 
Following high-level concepts are used:
Entities: A real-world object, such as an student, course, teacher etc.  Attributes: Property of an entity, such as student name, date of birth, gender, etc. Conceptual level defines extremely limited number of attributes in a model. Relationships: Association between two or more entities. For example an student is enrolled in a course. ER Model: The most popular and widely used model in database designing. I…

With the help of block diagram explain the architecture of a DBMS.

Image
Database System Architecture [REF: Fundamentals of Database Systems, Elmasri, Navathe, 6e]


Following block diagram explains the simplified DBMS architecture.

Architecture is divided into two major blocks. First block represents the different types of end users and their respective interfaces for interacting with the database system. Second block shows the internal components responsible for the storage management of data and processing of transactions.

DBA Staff: Database administrative staff, defines database structure.
DDL Statements and Privileged Commands:DBA staff use DDL (Data Definition Language) statements and Privileged Commands (Access control statements allocated as per assigned roles) to define database structure and make changes in definitions as per the requirements on timely basis.
DDL Compiler:It compiles DDL statements and record database definitions in system catalog or data dictionary. 
Data Dictionary/Storage Catalog: Data dictionary store meta data (data about data). Met…

How database approach is different from traditional file systems?

Database SystemsFile Systems
Database systems defines the database, storage structure, relation between data and constraints. It is defined as interrelated data and programs to access that data.
File management system defines the management of storage, retrieval and modification of set of files. This can be done by different application programs. Database systems control redundancy and avoid inconsistency because of central storage mechanism. No chance of creating multiple copies of same data at multiple locations. File processing systems do not offer central control. Not able to reduce redundancy. Increases data inconsistency. There are high chances of creating multiple copies of same data at multiple files stored at different locations.
Highly secured as it provides user profile management, roles and privileges for access control. Not secured and does not provide any inbuilt user profile management. Also, does not have any role and access control mechanism.
These …

What are the main advantages of DBMS?

Handle Redundancy: Storing same data at multiple locations is known as data  redundancy. Database system provide central storage mechanism. Copies of data are not created. It saves storage space as well as effort of accessing the data. 
Avoid Inconsistency: Redundancy brings inconsistency. Suppose, multiple copies of same data are available at different locations and data is updated only in few locations but not in all locations. This is inconsistency, different values of same data at different locations and it create confusion. Database system's centralized storage avoid this problem.
Data Sharing: Data stored for one application can be used for another application. Database system provides fast and efficient data access over digital network.
Enforcing Standards: Database system has centralized environment and this makes easy for DBA to create and enforce standards between users. 
Data Isolation: Database systems provide centralized data storage in a single format instead of storing …

What are different database languages?

Database system allows end user to read, create, update, delete and to perform other functions on stored data. To make this possible database system provide interfaces and languages for interacting with the system. Database languages are categorized as follows:
1) DDL (Data Definition Language)
Database administrators (DBAs) and designers use DDL to create physical and logical schema for database systems. Also, a language called storage definition language (SDL) is used to create the physical schema, where it defines the storage structure and access mechanism.

Database systems like Oracle Database, MS SQL Server, IBM DB2, MySQL etc. are equipped with an ANSI standard language, SQL(Structured Query Language). End user can write executable statements using SQL for creating database structures, accessing and manipulating data. SQL provides DDL implementation. CREATE (create schema), ALTER (alter schema), DROP (delete schema) and RENAME (rename schema) are DDL statements in SQL.

2) DML (Data …

What is data independence?

Image
To understand the concept of data independence, first we need to understand the three level (also known as three layered or three schema) architecture of database management systems. The goal of defining this architecture is to reduce the complexity involved in storing data in databases. So, database experts categorize three hierarchical levels (or layers or schema), which are internally mapped with each other and hides all the complexities from end user.

Physical Level: It is the lowest level in three level architecture. Also known as internal level. The schema defined at this level is known as physical schema. It describes all the details of the physical storage structure of the database and how they are stored.



Logical Level: It is the level above the physical level. Also known as conceptual level. It hides all the complexity of physical level from end user. The schema defined at this level is known as logical schema or conceptual schema. It describes the entities, thei…

What is database schema and database instance?

Image
Database SchemaThe description of the database is known as database schema. It is specified at the time of database designing. The data in a database changes frequently but database schema changes infrequently.

Following are the examples of schema:


Database InstanceThe data stored in the database at a particular moment is known as database instance. It is also known as a database state or snapshot. Whenever data is inserted or updated in a database, database state changes.
Now, suppose at some moment of time an employee has following state or instance:



If because of annual increments in this employee's company, he get a raise of 20% in his salary. Then the new instance of this employee looks like below:

What are main functions of a database administrator (DBA) ?

Create database schema by using Data Definition Language (DDL).Provide physical organization for storing data in a database.Modify database schema as per the requirements.Grant and authorize database access privileges to ensure data security.Take timely backup for preventing data loss because of any unforeseen reasons.Manage data storage memory.Implement access optimization schemes.Monitor automatic batch jobs and load maintenance.

What are the different types of database users?

Following are the types of database users:


1) Application Programmer: A technical person who writes database application programs. They are also known as Software Developer or Software Engineer. They write programs by using computer programming languages and development tools.
Languages: Java, COBOL, PASAL, PL/SQL etc are used. Development tools: Eclipse, JDeveloper, SQL Developer etc are used.
Application programs that interact with database are developed as per the user requirements and include functionalities of creating, reading, updating and deleting data from the database. These are commonly known as CRUD operations.  
2) Naive Users: Naive users use application programs written by application programmer for interacting with  the database. These users don't have much knowledge about database but thy use predefined applications to access required data from the database. 
Clerk working at the LIC office to maintain customer policy data is a naive user. Student filling information for o…