This SQL statement will extract record number 2 from the Customer table. Far more complicated queries can be written that can extract data from many tables at once.
Better security. By splitting data into tables, certain tables can be made confidential. When a person logs on with their username and password, the system can then limit access only to those tables whose records they are authorized to view. For example, a receptionist would be able to view employee location and contact details but not their salary.
A salesman may see his team's sales performance but not competing teams. Cater for future requirements. By having data held in separate tables, it is simple to add records that are not yet needed but may be in the future.
For example, the city table could be expanded to include every city and town in the country, even though no other records are using them all as yet. A flat file database cannot do this. Ease of use: The revision of any information as tables consisting of rows and columns is much easier to understand.
Flexibility: Different tables from which information has to be linked and extracted can be easily manipulated by operators such as project and join to give information in the form in which it is desired. Precision: The usage of relational algebra and relational calculus in the manipulation of he relations between the tables ensures that there is no ambiguity, which may otherwise arise in establishing the linkages in a complicated network type database. Disadvantages of the Relational Database.
The main problem when using a relational database is the complexity that arises when it is first created. It is absolutely vital that the defined relationships between the tables are correct and that each set of information is linked to its pair.
Although less information has to be entered in total than with other databases, making sure every point is set up correctly is a slow process. Furthermore, the relationships can become extremely intricate when a relational database contains more than just two tables. Performance: A major constraint and therefore disadvantage in the use of relational database system is machine performance.
If the number of tables between which relationships to be established are large and the tables themselves effect the performance in responding to the SQL queries. Physical Storage Consumption: With an interactive system, for example an operation like join would depend upon the physical storage also. It is, therefore common in relational databases to tune the databases and in such a case the physical data layout would be chosen so as to give good performance in the most frequently run operations.
Data Security. Data Integrity means that data is accurate and consistent in the database. A database can have only one table. Explanation:Fields are the column of the relation or tables. Keys are the constraints in a relation. Explanation:Column has only one set of values. Keys are constraints. Entry is just a piece of data.
Explanation:Tuple is one entry of the relation with several attributes. Explanation:Attribute is a specific domain in the relation which has. For each attribute of a relation, there is a set of permitted. Explanation:The values of the attribute should be present in the. Domain is a set of values permitted. Explanation:Instance is a instance of time and schema is a. Explanation:The relation course has a set of attributes.
Department dept name, building, budget and Employee. Here using common attributes in relation schema is one. Explanation:Here the relations are connected by the common. A domain is atomic if elements of the domain are. Explanation:The values only count. The order of the tuples does not. Which one of the following is a set of one or more. Explanation:Super key is the superset of all the keys in a relation.
Which one of. Explanation:Here the id is the only attribute which can be taken as a. Other attributes are not uniquely identified. The subset of super key is a candidate key under what. Explanation:The subset of a set cannot be the same set. Candidate key. Explanation:Key is the constraint which specifies uniqueness. Which one of the following attribute can be taken as a. Explanation:The attributes name , street and department can repeat.
But the id attribute has to be unique. So it forms a. Which one of the following cannot be taken as a primary.
Explanation:Street is the only attribute which can occur more than. Explanation:The primary key has to be referred in the other relation. The relation with the attribute which is the primary key. The relation which has. Explanation:A relation, say r1, may include among its attributes the primary key of another relation, say r2. This attribute is called a foreign key from r1, referencing r2.
The relation r1 is also called the referencing relation of the foreign key dependency, and r2 is called the referenced relation of the foreign key. Using which language can a user request information. Explanation:Query language is a method through which the database. In this query which attribute form the primary key? Which one of the following is a procedural language? Explanation:Domain and Tuple relational calculus are non-procedural.
Query language is a method through which the database. Explanation:Join finds the common tuple in the relations and. The result which operation contains all pairs of tuples.
Explanation:Cartesian product is the multiplication of all the values in. Explanation:Union just combines all the values of relations of same. The most commonly used operation in relational. Explanation:Select is used to view the tuples of the relation with or. Explanation:The union operator gives the result which is the union of. What is the purpose of index in SQL? To enhance the query performance.
To provide an index to a record. To perform fast searches. All of the above. What is the full form of SQL? Structured Query Language. Structured Query List. Simple Query Language. Which operator performs pattern matching? What operator tests column for the absence of data? A command that lets you change one or more fields in a record is. Which of the SQL statement is correct? Which SQL keyword is used to retrieve only unique values? Which SQL keyword is used to retrieve a maximum value? What is a view?
A view is a special stored procedure executed when certain event occurs. A view is a virtual table which results of executing a pre-compiled query. A view is a database diagram. Which of the following SQL command is used to retrieve data? Which of the following is a SQL aggregate function? Which SQL statement is used to update data in a database? Which SQL keyword is used to sort the Data? Which of the following group functions ignore NULL values? Which of the following command makes the updates performed by the transaction permanent in the database?
Which command undo all the updates performed by the SQL in the transaction? Find all the cities whose Temperature is 90 from the Table weather. Find the temperature in increasing order of all cities from the Table weather. Feature begins with two 0's. Feature ends with two 0's. Feature has more than two 0's.
Feature has two 0's in it, at any position. Find temperature is in the range of 65 to 76 from the table weather. Which of the following SQL command can be used to add data to a database table? Which of the following join is also called as an 'inner-join'? Outer join. What is an SQL virtual table that is constructed from other tables?
A relation. Change the table data. Change the table structure. Delete rows from the table. Add rows to the table. What SQL command can be used to add columns to a table? Limits the row data are returned. Limits the column data that are returned. Both A and B are correct. Neither A nor B are correct. Which of the following is the original purpose of SQL? To define the data structures. To specify the syntax and semantics of SQL Data definition language. To specify the syntax and semantics of SQL Data manipulation language.
The command to eliminate a table from a database is:. NOT IN only. LIKE only. IN only. Brackets [ Hash All columns of the table are to be returned. All records meeting the full criteria are to be returned. All records with even partial criteria met are to be returned.
None of the above is correct. Which of the following do you need to consider when you make a table in SQL? Data types. Primary keys.
Default values. SQL can be used to:. Create database structures only. Query database data only. Modify database data only. All of the above can be done by SQL. To limit the columns displayed. For ranges. As a wildcard. None of these is correct. Select all data from student table the name starting with 'r'?
Select the right statement to insert values to the student table. Which joins two or more tables based on a specified column value not equaling a specified column value in another. Count function in SQL returns the number of. Distinct values.
NULL is. The same as 0 for integer. The same as blank for character. The same as 0 for integer and blank for character. Not a value. Which subprogram is used to compute a value? Which is a procedural extension of Oracle? Advanced SQL.
Which of the following combines the data manipulating power of SQL with the data processing power of Procedural. Which keyword and parameter used for declaring an explicit cursor? Which keyword is used instead of the assignment operator to initialize variables? Which statements execute a sequence of statements multiple times? Which statements are used to control a cursor variable?
All mentioned above. The variables or expressions passed from the calling subprogram are -. Actual Parameters. Formal Parameters. Which attribute returns NULL? Which type of cursor is automatically declared by Oracle every time an SQL statement is executed?
An Implicit. An Explicit. Which structure executes a sequence of statements repeatedly as long as a condition holds true?
Selection structure. Iteration structure. Sequence structure. Which of the following returns the current value in a specified sequence. Which operators combine the results of two queries into one result? Set operator. Row Operator. Select invalid variable Data type. List the correct sequence of commands to process a set of records when using explicit cursors.
Which of the following is not a grouping function. If you don't specify a mode for a parameter, what is the default mode? What are the three parameter modes for procedures? Read, Write, Append. Which of the following function checks whether the expression is a valid date or not? Which of the following are TCL commands? Which is not a category of SQL command. Which function returns leading and trailing characters of a string? The function returns a string with first letter of each word in uppercase.
The function returns current date SQL is. The function returns the next day of week following a given date is. ERAGE of the salary? Avg salary. Sum salary. Count salary. Which one of the following is used to define the structure of the relation ,deleting relations and relating.
Explanation: Data Definition language is the language which performs. Which one of the following provides the ability to query information from the database and to insert tuples into,. Explanation: DML performs change in the values of the relation. Create table employee name varchar ,id integer. What type of statement is this? Explanation:Data Definition language is the language which performs.
Explanation: Select operation just shows the required fields of the. So it forms a DML. Explanation: Varchar changes its length accordingly whereas char has. An attribute A of datatype varchar 20 has the value. The attribute B of datatype char 20 has value. To remove a relation from an SQL database, we use the.
Explanation: Drop table deletes the whole structure of the relation. Delete from r; r — relation. This command performs which of the following action? Explanation: Delete command removes the entries in the table.
Explanation: The values are manipulated. So it is a DML. Explanation:Integrity constraint has to be maintained in the entries of. Which keyword must be used here to rename the field name?
Explanation:As keyword is used to rename. In the SQL given above there is an error. Identify the error. Select name. Explanation:Specification of descending order is essential but it not. To display the salary from greater to smaller and name in. This query can be replaced by which of the following? Explanation: SQL includes a between comparison operator to simplify.
Select instructor. This query does which of the following operation? In SQL the spaces at the end of the string are removed. Explanation: The syntax of trim is Trim s ; where s-string. Explanation: is the concatenation operator. The union operation is represented by.
Explanation:Union operator combines the relations. Explanation:Intersection operator ignores unique tuples and takes. If we want to retain all duplicates, we must write. Explanation:Union all will combine all the tuples including duplicates. Select course id from section. Transitive Functional When an indirect relationship causes functional dependency it is called Transitive Dependency. Which of the following is a fundamental operation in relational algebra? Set intersection. Natural Join.
Question 72 Explanation:. The primary key is selected from the:. Composite keys. Candidate keys. Foreign keys. Question 73 Explanation:. Candidate Key is a super key with no repeated attributes Each Table have one or more candidate keys Primary key should be selected from the candidate keys. Every table must have at least a single candidate key. Each table can have multiple candidate keys but only a single primary key. In functional dependency between two sets of attribute A and B then set of attributes A of database is classified as:.
Top right side. Down left side. Left hand side. Right hand side. Question 74 Explanation:. Determinant It is displayed on the left side of the functional dependency diagram. Which type of statement can execute parameterized queries? Parameterized Statement. ParameterizedStatement and CallableStatement.
All kinds of Statements. Question 75 Explanation:. Prepared Statement PreparedStatement interface extends the Statement interface. It represents a precompiled SQL statement which can be executed multiple times. This accepts parameterized SQL quires and you can pass 0 or more parameters to this query.
Source : Tutorialspoint. Question 76 Explanation:. Following are the fundamental operations in relation algebra Select Project Union Set difference Rename Cartesian product. Lossless,dependency preserving decomposition into BCNF is always possible. Lossless,dependency preserving decomposition into 3NF is always possible. Any relation with two attributes is BCNF.
Question 77 Explanation:. Dependency preserving. Not dependency preserving. Need be dependency preserving. Question 78 Explanation:. Note : Redundancies are sometimes still present in a BCNF relation as it is not always possible to eliminate them completely. Question 79 Explanation:. A primary key, if combined with a foreign key creates. Parent child relationship between the tables that connect them. Many-to-many relationship between the tables that connect them.
Network model between the tables connect them. Question 80 Explanation:. Using binary relationship sets is preferable in this case. Not in 2 NF. In both 2NF and 3 NF. Question 81 Explanation:. Candidate keys are S,T,U and V. Question 82 Explanation:. Consider the following tables relations : Primary keys in the tables are shown using Underline. Name, Sum P. Name The number of rows returned by the above query is. Question 83 Explanation:.
A process to upgrade the quality of data after it is moved into a data warehouse. A process to upgrade the quality of data before it is moved into a data warehouse. A process to lead the data in the warehouse and to create the necessary indexes.
A process to reject data from the data warehouse and to create necessary indexes. Question 84 Explanation:. Data scrubbing is also called as data cleansing it is the process of amending or removing data in a database that is incorrect, incomplete, improperly formatted, or duplicated. By using data scrubbing tool can save a database administrator a significant amount of time and can be less costly than fixing errors manually.
AE and BE. Question 85 Explanation:. Find the closure set of all the options given. If any closure includes all attributes of a table then it becomes the candidate key. Algorithm to find Closure Set Step1: Equate an attribute or attributes to X for which closure needs to be identified. Step2: Take each FD functional dependency one by one and check whether the left side of FD is available in X, if yes then add the right side attributes to X if it is not available.
Step3 : Repeat step 2 as many times as possible to cover all FD's. Step4: After no more attributes can be added to X declare it as the closure set.
Closure of AEF, i. We can apply redo and undo operation in arbitrary order because they are idempotent. We must redo log record vi to set A to 16, We must undo log record vi to set A to 16, and then redo log record ii and iii. We need not redo records ii and iii because transaction T1 has committed. Question 86 Explanation:. Answer should be option c Here we are not using any checkpoints. So, We must undo log record vi to set A to 16, and then redo log records ii and iii because system fail before commit operation.
So we need to undone active transactions T2 and redo committed transactions T1 Checkpoint : Checkpoint is a mechanism where all the previous logs are removed from the system and stored permanently in a storage disk. Checkpoint declares a point before which the DBMS was in consistent state, and all the transactions were committed.
Recovery : When a system with concurrent transactions crashes and recovers, it behaves in the following manner? All the transactions in the undo-list are then undone and their logs are removed. All the transactions in the redo-list and their previous logs are removed and then redone before saving their logs.
The clustering index is defined on the fields which are of type. Question 87 Explanation:. Update table. Remove table. Delete table. Drop table. Question 88 Explanation:. Database relations have a large number of records. Database relations are sorted on the primary key. Data transfer from disks is in blocks. Question 89 Explanation:.
Consider the following four schedules due to three transactions indicated by the subscript using read and write on a data item x, denoted by r x and w x respectively. Which one of them is conflict serializable. Question 90 Explanation:. First we need to Draw precedence graph for all the options and for conflict serializability graph must not contain cycle.
In a database system, the domain integrity is not defined by:. The data type and the length. The NULL value rejection. The allowable values, through techniques like constraints or rules.
Default value. Question 91 Explanation:. Domain Level Integrity A domain defines the possible values of an attribute. Domain Integrity rules govern these values. In a database system, the domain integrity is defined by: The datatype and the length The NULL value acceptance The allowable values, through techniques like constraints or rules The default value.
To maintain transactional integrity and database consistency DBMS will use:. Question 92 Explanation:. Locks are used In-order to maintain transactional integrity and database consistency.
What is the minimum and maximum number of keys in such a tree? Question 93 Explanation:. Therefore, Minimum number of keys that a node can have becomes 2 i.
The primary key of any table is selected from the:. Alternate keys. Question 94 Explanation:. A table can have multiple candidate keys but only a single primary key. A lock which required is not acquired by database engine is:. Row lock. Page lock. Table lock. Attribute lock. Question 95 Explanation:. The name of parser generator that is used for SQL query parsing is:.
Lexer parser generator. Syntactic parser generator. Tokenizer parser generator. Question 96 Explanation:.
When parsing a query the processor generates fields based on the fields defined in the SQL query and specifies the CRUD operation, table, and schema information in record header attributes. Bounded minimization is a technique for. Proving whether a promotivate recursive function is turing computable or not. Proving whether a primitive recursive function is a total function or not. Generating primitive recursive functions. Generating partial recursive functions. Question 97 Explanation:.
Global locks. Synchronize access to local resources. Synchronize access to global resources. Are used to avoid local locks. Prevent access to global resources. Question 98 Explanation:. In a relational schema, each tuple is divided into fields called. Question 99 Explanation:.
In a relational schema, each tuple is divided into fields called Domains. In a relational model, each attribute carries a domain of its own. A domain refers to the set of unique values used to define that attribute and will act as a "model" set of values. These values, being unique to that attribute are referred to as "atomic values".
A domain is a set of acceptable values that a column is allowed to store The rule for determining the domain boundary may be as simple as a data type with an enumerated list of values. The employee salary should not be greater than Rs.
This is. Integrity constraint. Referential constraint. Over-defined constraint. Feasible constraint. Question Explanation:. In Given relational algebra, Tuple t should have 2 attributes A and B such that t. If any closure covers all the attributes of the relation R then that is the key. A program P reads and processes consecutive records from a sequential file F stored on device D without using any file system facilities. What is the elapsed time of P if records of F are organized using a blocking factor of 2 i.
In this case accesses are sufficient to transfer records. The data manipulation language DML. Refers to data using physical addresses. Cannot interface with high-level programming language. Is used to define the physical characteristics of each record.
A tuple in relation DBMS is a equivalent to. A B-tree of order 4 is built from scratch by 10 successive insertions. What is the maximum number of node splitting operations that may take place? Data Scrubbing is. Process of analyzing relation schemas to achieve minimal redundancy and insertion or update anomalies is classified as:.
Normalized of data. Denomination of data. Isolation of data. Denormalized of data. Rule which states that addition of same attributes to right side and left side will result in other valid dependency is classified as:.
Referential rule. Inferential rule. Augmentation rule. Reflexive rule. If every functional dependency in set E is also in closure of F then this is classified as:. FD is covered by E. E is covered by F. F is covered by E. F plus is covered by E. Considering relational database, functional dependency between two attributes A and B is denoted by:.
The left side of FD is known as a determinant, the right side of the production is known as a dependent. If there is more than one key for relation schema in DBMS then each key in relation schema is classified as:. Primary key. Super key. Candidate key. A candidate key is a column, or set of columns, in a table that can uniquely identify any database record without referring to any other data. Each table can have multiple candidate keys but only a single primary key Each table may have one or more candidate keys, but one candidate key is unique, and it is called the primary key.
A candidate key is the minimal subset of a super key. Candidate key of having "no redundant attributes" and being a "minimal representation of a tuple" in a relational database table.
P1 V p2. If R is a relation in relational data Model and A1,A2,.. An are the attributes of relation R, what is the cardinality of R expressed in terms of domain of attributes? The term cardinality refers to the uniqueness of data values contained in a particular column attribute of a database table. The lower the cardinality, the more duplicated elements in a column.
In the context of databases, cardinality refers to the uniqueness of data values contained in a column. Cardinality refers to a number. It gives the number of unique values that appear in the table for a particular column. Gender column can have values either 'Male' or 'Female". Then the cardinality of Gender column is 2, since there are only two unique values that could possibly appear in that column — Male and Female. The order of occurrence determines the order of evaluation. We can use parentheses to override the rules of precedence.
Selection operation. Rename operation. Join Operation. Projection Operation. An alias only exists for the duration of the query. Related fields in a database are grouped to form a. Data file. Data record. Relation sometime called as Table Fields sometime called as columns or Attributes Rows sometime called as Records or Tuples In a database, Related fields are grouped within a table to form a record.
A table joined with itself is called. Self join. Outer join. Equi join. A table joined with itself is called self join Table must contain a column call it X that acts as the primary key and a different column call it Y that stores values that can be matched up with the values in Column X. Consider the join of a relation R with relation S. If R has m tuples and S has n tuples, then the maximum size of join is. Person NID, Name. Create one table for each entity while converting an ER diagram into relation model So we got 2 tables i.
T1 start 2. T1 commit 5. T2 start 6. T2 commit Suppose the database system crashes just before log record 7 is written. When the system is restarted, which one statement is true of the recovery procedure? We must redo log record 6 to set B to We must undo log record 6 to set B to and then redo log records 2 and 3. We need not redo log records 2 and 3 because transaction T1 has committed.
We can apply redo and undo operations in arbitrary order because they are idempotent. Answer should be option B Here we are not using any checkpoints. So, We must undo log record 6 to set b to 10, and then redo log records 2 and 3 because system fail before commit operation. Given a block can hold either 3 records or 10 key pointers. A database contains n records, then how many blocks do we need to hold the data file and the dense index.
Which of the following is a key of R? Consider the following relational schema: Suppliers sid:integer , sname:string, city:string, street:string Parts pid:integer , pname:string, color:string Catalog sid:integer, pid:integer , cost:real Consider the following relational query on the above database: SELECT S.
Which one of the following is the correct interpretation of the above query? Find the names of all suppliers who have supplied a non-blue part. Find the names of all suppliers who have not supplied a non-blue part. Find the names of all suppliers who have supplied only non blue parts. Find the names of all suppliers who have not supplied only non-blue parts.
So, None of the options are matching. Option B : wrong because it returning other than any blue part. Average salary of male employee is the average salary of the organization. Average salary of male employee is less than the average salary of the organization. Average salary of male employee is equal to the average salary of the organization. Average salary of male employee is more than the average salary of the organization. So, if it has "K" child pointers then it must have "K-1" keys.
If D 1 , D 2 …D n are domains in a relational model, then the relation is a table, which is a subset of. A relation or table is a subset of the Cartesian product of a list of domains characterized by a name Below are the steps for Logic between a relation and its domains. The maximum length of an attribute of type text is. It is variable. Which of the following relation schemas is definitely in BCNF? R1 A,B. R3 A,B,C,D.
R2 A,B,C. Attributes from a table relation. Tables from a database. Tuples from a table relation. Referential integrity constraints works on the concept of:.
Secondary key. Foreign key. Primary Index : Primary index is defined on an ordered data file. The data file is ordered on a key field.
The key field is generally the primary key of the relation. Secondary Index : Secondary index may be generated from a field which is a candidate key and has a unique value in every record, or a non-key with duplicate values.
Clustering Index : Clustering index is defined on an ordered data file. The data file is ordered on a non-key field. Which of the following desired features are beyond the capability of relational algebra? Aggregate Computation. Finding transitive closure. Aggregate is a function and to collection of values to generate a single result. Transitive closure is an operation on directed graphs where the output is a graph with direct connections between nodes only when there is a path between those nodes in the input graph.
This question dicuss about definition of Wait-die scheme. Assume transaction A holds a shared lock R. If transaction B also requests for a shared lock on R. It will. Result in deadlock situation. Immediately be granted. Immediately be rejected. Be granted as soon as it is released by A. Shared locks exist when two transactions are granted read access. One transaction gets the shared lock on data and when the second transaction requests the same data it is also given a shared lock.
Both transactions are in a read-only mode, updating the data is not allowed until the shared lock is released. There is no conflict with the shared lock because nothing is being updated. Shared locks last as long as they need to last; it depends on the level of the transaction that holds the lock Transaction hold shared lock on data item A. So any number of transaction can read that the data from the requesting item using shared locking So transaction B request will immediately granted Source : Wiki.
R has no duplicates and S is non empty. S has no duplicates and R is non empty. R and S has the same number of tuples. Which of the following statements are not correct? S1: 3NF decomposition is always lossless join and dependency preserving S2: 3NF decomposition is always lossless join but may or may not be dependency preserving S3: BCNF decomposition is always lossless join and dependency preserving S4: BCNF decomposition is always lossless join but may or may not be dependency preserving.
Only S1. Only S4. Boh S1 and S4. Both S2 and S3. In conservative two phase locking protocol, a transaction. Should release all the locks only at beginning of transaction. Should release exclusive locks only after the commit operation.
Should acquire all the exclusive locks at beginning of transaction. Should acquire all the locks at beginning of transaction. In Conservative 2-PL we need to lock all the data items which the transaction required before the transaction begins execution by predeclaring its read-set and write-set. And for its conservative nature, this protocol is deadlock free but starvation can occur. Which of the following is TRUE? A relation R is in 3NF if every non prime attributes of R is fully functionally dependent on every key of R.
Maximum cardinality should be one. Minimum cardinality should be one. Minimum cardinality should be zero. None of the options. Natural join, outer join.
Outer join, natural join. Left outer join, right outer join. Left outer join, natural join. The outer join operation preserves a few tuples that are otherwise lost in the join operation. The outer join operation preserves the tuples to the right of the operation. No, Dept. Name, Dept.
Manager, SSn Which of the following is are invalid operation s? They clearly specified in the question that SSn is the key So, Inserting an employee without SSn is invalid operation. Data aggregation. Extraction of data. Compression of data. Cleaning of data. The process of removing the deficiencies and loopholes in the data is called as cleaning up of data. Data cleansing or data cleaning is the process of detecting and correcting or removing corrupt or inaccurate records from a record set, table, or database and refers to identifying incomplete, incorrect, inaccurate or irrelevant parts of the data and then replacing, modifying, or deleting the dirty or coarse data.
Data cleansing may be performed interactively with data wrangling tools, or as batch processing through scripting. Which of the following is correct? Candidate, primary. Referential integrity ensures that the values for a set of attributes in one relation must also appear the same for the particular set attributes in another relation. This is because the primary key attribute value uniquely defines an entity in a relation.
0コメント