SQL PCS

What do you understand by cardinality and why it is used?

- Cardinality is important and used to arrange the data inside the database. - It is related to the design part and need to be properly used in database. - It is used in E-R diagrams and used to show the relationship between entities/tables. - It has many forms like the basic is one to one, which associate one entity with another. - Second is one to many: which relates one entity with many entities in a table. - Third is many to many M: N that allows many entities to be related to many more. - Last is many to one that allows the many entities to be associated with one entity.

What is the difference between DBMS and RDBMS?

- DBMS is persistent and accessible when the data is created or exists, but RDBMS tells about the relation between the table and other tables. - RDBS supports a tabular structure for data and relationship between them in the system whereas DBMS supports only the tabular structure. - DBMS provide uniform methods for application that has to be independently accessed, but RDBMS doesn't provide methods like DBMS but provide relationship which link one entity with another

What are the two principles of relational database model? What is the difference between them?

- Entity integrity tells that in a database every entity should have a unique key; on the other hand referential integrity tells that in the database every table values for all foreign keys will remain valid. - Referential integrity is based on entity integrity but it is not the other way around.

entity

- Entity is referred to the store data about any particular thing.

How de-normalization is different from normalization?

- It is used to extract the data that are ancient and accumulated over long period of time. For this purpose de-normalization occurs that provide smart business applications. - Dimensional tables in star schema are good example of de-normalized data. - The de-normalized form must be controlled while extracting, transforming, loading and processing. - There should be constraint that user should not be allowed to view the state till it is consistent. - It is used to increase the performance on many systems without RDBMS platform.

What is Index tuning?

- Query performance as well as speed improvement of a database can be done using Indexes.- The process of enhancing the selection of indexes is called Index Tuning.

How view is related to data independence?

- View is a virtual table that doesn't really exist, but it remains present so that user can view their data. - It is derived from the base table. The view is stored in the data dictionary and represents the file directly. - The base table updation or reconstruction is not being reflected in views. - It is related to the logical data independence as it is at the logical level and not at the physical level.

Define "correlated subqueries".

A 'correlated subquery' is a sort of sub query but correlated subquery is reliant on another query for a value that is returned. In case of execution, the sub query is executed first and then the correlated query.

Why a database is called as relational database model?

A database model represents the relationship between one or more databases. The relationship is known as the relational database model. It is an extension of the normal databases without relations. It provides flexibility and allows one database to be in relation with another database.

cursor

A database object which helps in manipulating data row by row representing a result set is called cursor.

What do you mean by Entity type?

A set of entries having similar attributes are entity types.

What is true about FLOOR(n) function?

A) Returns smallest integer greater than or equal to n B) Returns largest integer less than or equal to n C) It is used with numeric data D) It operates on character data

Pseudo-column LEVEL can be used in ____statement where ______ is used.

ANSWER: b) Select , connect by

Transaction Phases

Analysis Undo Redo

Denormalization

Boosting up database performance, adding of redundant data which in turn helps rid of complex data is called denormalization.

______ condition can't contain sub queries or sequence.

Check

Define Entity Set

Compilation of all entries of any particular type of entry in the database is called Entity Set.

Entity type extension

Compilation of similar entity types into one particular type which is grouped together as an entity set.

What are constraints in database?

Constraints are kind of restrictions that are applied to the database or on the domain of an attribute. For example an integer attribute is restricted from 1-10 and not more than that.

Which of the following query will correctly create the view of Employee table having fields fname,lname,dept?

Create vew1 view AS Select fname,lname,dept from Employee;

Database system

DBMS + database

Which of the following column is not a part of USER_CONSTRAINTS?

DB_DOMAIN

Define DDL Interpreter.

DDL statements are interpreted and recorded in tables called metadata.

What do you understand by Data Independence?

Data independence tells about the independence of the data inside the application. It usually deals with the storage structure and represents the ability to modify the schema definition. It doesn't affect the schema definition which is being written on the higher level. There are two types of data independence: 1. Physical data independence: It allows the modification to be done in physical level and doesn't affect the logical level. 2. Logical data independence: It allow the modification to be done at logical level and affects the view level. NOTE: Logical Data Independence is more difficult to achieve.

Database partitioning

Division of logical database into independent complete units for improving its management, availability and performance is called Database partitioning.

Why domain is of high importance?

Domain describes possible values grouped together that can be given for an attribute. It is considered the same way as a constraint on the value of attribute.

SQL was initially developed by

Donald D. Chamberlin and Raymond F. Boyce

Enlist the cursor types.

Dynamic: it reflects changes while scrolling. Static: doesn't reflect changes while scrolling and works on recording of snapshot. Keyset: data modification without reflection of new data is seen.

EXTRACT() function returns ___________.

EXTRACT() function returns ___________.

SQL ______performs a JOIN against equality or matching column(s) values of the associated tables.

Equi JOIN

What do you mean by Fill Factor concept with respect to indexes?

Fill Factor can be defined as being that value which defines the percentage of left space on every leaf-level page that is to be packed with data. 100 is the default value of Fill Factor.

Fragmentation

Fragmentation can be defined as a database feature of server that promotes control on data which is stored at table level by the user.

30) How does Index hunting help in improving query performance?

Index hunting helps in improving the speed as well as the query performance of database. The followed measures are achieved to do that: The query optimizer is used to coordinate the study of queries with the workload and the best use of queries suggested based on this. Index, query distribution along with their performance is observed to check the effect. Tuning databases to a small collection of problem queries is also recommended.

Atomicity

It's an all or none concept which enables the user to be assured of incomplete transactions to be taken care of. The actions involving incomplete transactions are left undone in DBMS.

Differentiate Table Scan from Index Scan.

Iterating over all the table rows is called Table Scan while iterating over all the index items is defined as Index Scan.

Pseudo-column ________ returns a level of row in a tree-structured query.

LEVEL

disadvantages of query.

No indexes Stored procedures are excessively compiled.

What is the type of de-normalization?

Non-first normal form (NFA) - It describes the definition of the database design which is different from the first normal form. - It keeps the values in structured and specialized types with their own domain specific languages. - The query language used in this is extended to incorporate more support for relational domain values by adding more operators.

______ constraint can only applied at column level.

Not null

What restrictions can you apply when you are creating views?

Only the current database can have views. You are not liable to change any computed value in any particular view. Integrity constants decide the functionality of INSERT and DELETE. Full-text index definitions cannot be applied. Temporary views cannot be created. Temporary tables cannot contain views. No association with DEFAULT definitions. Triggers such as INSTEAD OF is associated with views.

Query Evaluation Engine

Query Evaluation Engine executes the low-level instructions that are generated by the compiler.

The____ data types are used to store binary data.

RAW

____ column displays the location of row in a database.

ROWID

What do you understand by relation in relational database model?

Relation in the relational database model is defined as the set of tuples that have the same attributes.Relation is described as a table and is organized in rows and columns. They an be modified using commands such as insert and delete.

Executive Plan

SQL Server caches collected procedure or the plan of query execution and used thereafter by subsequent calls. An important feature in relation to performance enhancement. Data execution plan can be viewed textually or graphically.

Scalar functions.

Scalar function is depended on the argument given and returns sole value.

Dynamic vs Static SQL

Staitic SQL statements in an application do not change at runtime and, therefore, can be hard-coded into the application Dynamic SQL: SQL statements that are constructed at runtime; for example, the application may allow users to enter their own queries. Dynamic SQL is a programming technique that enables you to buildSQL statements dynamically at runtime. You can create more general purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation.

Define data warehousing

Storage and access of data from the central location in order to take some strategic decision is called Data Warehousing. Enterprise management is used for managing the information whose framework is known as Data Warehousing.

What are its two major subsystems of System R?

System R is being developed by IBM. Its purpose is to demonstrate the possible solution to build a relational database system. The relational database system has to be such that which can interact with the real life environment to sole real life scenarios. The two subsystems that are included in it are: 1. Research storage: This includes the research information of the database. 2. System relational system: This includes the relational data that a system has to produce and keep everything in relation.

Data files are logically grouped together into an oracle logical structure called a

TableSpace

Aggregation

The collected entities and their relationship are aggregated in this model. It is mainly used in expressing relationships within relationships.

What is the difference between extension and intension?

The major difference between extension and intension is that: - Extension is time dependent, whereas intension includes a constant value. - Extension tells about the number of tuples presented in a table at any instance, whereas intension gives the name, structure and constraint of the table.

Define Object-oriented model.

The object itself comprises bodies of object for its operation which are called methods. Objects containing same kind of variables and methods are called classes.

How many levels of data abstraction are available?

There are three levels of data abstraction available in database model and these are as follows: 1. Physical level: It is the lowest level that describes how data is stored inside the database. 2. Logical level: It is the next higher level in the hierarchy that provides the abstraction. It describes what data are stored and the relationship between them. 3. View level: It is the highest level in hierarchy that describes part of the entire database. It allows user to view the database and do the query.

Enlist the types of cursor.

They types of cursor are: Implicit cursor: Declared automatically as soon as the execution of SQL takes place without the awareness of the user. Explicit cursor: Defined by PL/ SQL which handles query in more than one row.

importance of partitioning

To improve query performance in situations dramatically when mostly rows which are heavily accessed are in one partition. Accessing large parts of a single partition Slower and cheaper storage media can be used for data which is seldom used.

Enlist ways to efficiently code transactions.

User input should not be allowed while transactions. While browsing, transactions must not be opened of data. Transactions must be kept as small as possible. Lower transaction segregation levels. Least information of data must be accessed while transacting.

char vs varchar

Var is longer, can hold more characters,slower, uses dynamic memory allocation while char uses static memory allocation, char length is fixed while varchar is variable

To use the result of certain query repeatedly which clause will you use?

WITH

________ allows grantee to in turn grant object privileges to other users.

With grant option

Suppose your assistant, named Jene has not been previously authorized to add data about new customers into the database, which of the following statement can be used to give her that permission

a) Grant Insert

What are the prerequisite for a View to be updateable?

a) Views defined from a single table. If user wants to Insert records with the help of a view, then the primary key column and all the Not Null columns must be included in the view b) The user can Update, Delete records with the help of a view even if the primary key column and Not Null column(s) are executed from the view definition

Restrictions on Dropping Tablespace

a) You cannot drop the SYSTEM tablespace. b) You cannot drop a tablespace that contains a domain index or any objects created by a domain index. c) You cannot drop an undo tablespace if it is being used by any instance or if it contains any undo data needed to roll back uncommitted transactions.

VSIZE( ) function returns ___________.

a) number of bytes in the internal representation of an expression.

Why stored procedures are called as executable code?

includes the executable code that usually collects and customizes the operations like insert, encapsulation, etc. These stored procedures are used as APIs for simplicity and security purposes. The implementation of it allows the developers to have procedural extensions to the standard SQL syntax.

What are the relational operations that can be performed on the database?

operators are as follows: 1. Union operator that combines the rows of two relations and doesn't include any duplicate. It also removes the duplicates from the result. 2. Intersection operator provides a set of rows that two relations have in common. 3. Difference operator provide the output by taking two relations and producing the difference of rows from first that don't exist in second.

The ___ operator is used to calculate aggregates and super aggregates for expressions within a ________.

rollup, groupby

Which of the following query is correctly give the user name of the currently logged in user?

select user from dual

A____ level constraint must be applied if data constraint spans across multiple columns in a table.z

table