SOLUTION

Union is the binary operation and others are unary operations.

SOLUTION

There are three data models available : Relational, Hierarchical and Network data model.

SOLUTION

The main disadvantage of this model is data dependency. If we delete the parent node, child node automatically gets deleted.

SOLUTION

The main disadvantage of relational model is machine performance. If the number of tables increases, the response time gets increased.

SOLUTION

A set of entities of same type, i.e., which share common properties constitutes an entity set.

SOLUTION

A relationship is an association among several entities. For example, a teacher teaches a student. Here, teacher and student are entities and 'teaches' is the relationship between them.

SOLUTION

A foreign key is a field (or fields) that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data.

SOLUTION

A foreign key is used to represent the relationship between two tables. The table in which this non-key attribute i.e., the foreign key attribute exists, is called a Foreign table or Detail table and the table that defines the Primary-key, which the foreign-key of detail-table refers to, is called Primary table or Master table.

SOLUTION

Number of rows (tuples) in a relation is called cardinality of the relation.

SOLUTION

In the context of databases, a tuple is one record (one row). A tuple consists of different fields which relate to a single unit of information.

SOLUTION

A subschema lets the user have access to different areas of applications in which the user designed. The areas that are included in an application are set, types, record types, data items and data aggregates.

SOLUTION

In computer science, data that has several parts can be divided into different fields. For example : to represent today's date we can use three distinct fields, i.e., the day, the month and the year.

SOLUTION

In file management system, it is not possible to share the data. Due to it, redundancy of data is possible.

SOLUTION

In tree, one node (parent node) is connected to many other nodes (child nodes). It forms one-to-many relationship i.e. one parent node may have more than one child nodes.

SOLUTION

Hierarchical data model is in the form of a tree. In tree, one node (parent node) is connected to many other nodes (child nodes), thus forming one-to-many relationship.

SOLUTION

The row of a table is called as tuple, which is equivalent to a record.

SOLUTION

The relational model for database management is a database model based on first-order predicate logic, first formulated and proposed in 1969 by E.F. Codd.The relational data model uses the concept of tables.

SOLUTION

An ordered set of fields that represents a complete set of information is known as record.

SOLUTION

We have to display the columns PRODCODE and PRICE so project operation is used for this. The condition is that supplier should be QWIK and Price should be greater than 100.

SOLUTION

The columns to be displayed are product name(NAME) and product code(PRODCODE) where suppliers are VXL.

SOLUTION

It is a simple select statement to display product name(NAME) and code(PRODCODE) of all the rows.

SOLUTION

All the columns from the table are to be selected where price is greater than 100.

SOLUTION

In hierarchical data model, the data is organised in the form of trees from top to bottom.

SOLUTION

In network data model, the records are connected by links to form a network.

SOLUTION

In relational algebra, the data is organised in the form of tables where columns are attributes and rows contain the data for these attributes.

SOLUTION

In relational algebra,while performing union operation between two relations their cardinality should be same.

SOLUTION

The project operation helps to remove data redundancy by removing the duplicate columns.

SOLUTION

When the value for any column is not defined by the user or it is unknown, then in that case, NULL values are used.

SOLUTION

The query is to select the columns name and price from the relation Books where author=A.Roy.

SOLUTION

Select operation is signified by . The table name is booklist and the predicate or condition is author =A.ROY.

SOLUTION

When cartesian product of two tables is found then the number of tuples in the resultant relation is the product of the number of tuples in both the relations.

SOLUTION

Union operation between two tables is only possible when they are having similar number of attributes.

SOLUTION

The select operation of relational database is represented by the greek letter i.e.   (sigma) in relational algebra.

SOLUTION

When the select operation is being performed then the conditions being applied should be applicable to the relation.

SOLUTION

A primary key is used to uniquely identify an entity so its values must be unique. No two entities can have the same value of primary key.

SOLUTION

The union operation combines two relations and projects the tuples of both the relations.

SOLUTION

Cartesian product of two relations is the total number of elements in a given relation.
It is given as M X N.

SOLUTION

The projection operation in database selects the columns that fulfill a given condition.

SOLUTION

The select statement in database helps to find out the rows that fulfill a given condition.

SOLUTION

Candidate keys are a set of keys that taken together can distinguish one row from other.

SOLUTION

Alternate keys are the candidate keys which can be used in place of primary key in case it is not available.

SOLUTION

A foreign key is an attribute in one table which acts as a primary key in other relation. So, its values in both the tables should match.

SOLUTION

A primary key is an attribute through which an object can be distinguished from other objects. Therefore, it should always have unique values and can never be NULL.

SOLUTION

Cardinality is the number of rows or instances of a given data item.

SOLUTION

Degree is the number of attributes of an entity. The attributes in a relational database are represented in the form of columns.

SOLUTION

Domain is defined as the set of values which an attribute can take.

SOLUTION

In relational database, the columns denote the attributes or properties of a given data.

SOLUTION

In relational database, a table is known as a Relation because it expresses the relation between the different data items in the form of rows and columns.

SOLUTION

In relational database, the data is expressed in the form of rows and columns. The rows contain the different values of the attributes. The rows are called tuples.

SOLUTION

To represent data and the relationship between data, three data models that have been specified are :- 1. Relational Model. 2. Network Model. 3. Hierarchial Model.

SOLUTION

Data independance means that changes at one level can be made without affecting the other levels of data.

SOLUTION

At external level, the data is managed according to the need of the user. Only the essential features are available to the user.

SOLUTION

At the conceptual or logical level, the relationship among the different data entities is defined.

SOLUTION

Unlike ordinary tables (base tables) in a relational database, a view does not form part of the physical schema, it is a dynamic virtual table computed or collated from data in the database.

SOLUTION

SQL provides several types of built in functions that return different kinds of information from the database. These are lower, upper, replicate, substr, getdate()etc. SELECT, UPDATE, ALTER, DROP are commands in SQl.

SOLUTION

A view is a virtual table with no data, but can be operated like any other table.

SOLUTION

ORDER BY is used for sorting the result set. By default it will sort in ascending order but if we want the result set in descending order, then we use ORDER BY DESC.

SOLUTION

The truncate command does not write data to the user segment.

SOLUTION

A column name cannot start with a digit or hash. A column is a reserved word, it is possible only if it would have been inside quotes like “column”.

SOLUTION

Table access by Rowid is the fastest access method.

SOLUTION

The sole purpose of indexes in any relational database management system is to reduce the access time for SQL statements. If, indexes did not exist in a relational database, then the Oracle database SQL optimizer would have no choice except to read every single row.

SOLUTION

The degree of normalization in the database design is critical to SQL performance. The ideal candidates for the introduction of redundancy are small data items that are updated very infrequently.

SOLUTION

SQL coder need not be concerned with the proper work of joining the tables together.

SOLUTION

Prior to the invention of relational databases, it was very difficult to establish relationships with other database objects and systems relied on pointers in order to establish data relationships. With the advent of relational database management, it became possible to establish ad-hoc relationships using SQL joins.

SOLUTION

Aggregate functions operate on a aggregate of tuples. These are max, min, avg, sum, stddev, count, variance etc. The result of aggregate functions is a single value.

SOLUTION

Data is selected from the table using SELECT command.

SOLUTION

Primary key constraints declare the column as the primary key of the table and cannot have null values. It can be applied to one or more columns.

SOLUTION

It ensures that the value in a column is unique. It can be applied to one or more columns.

SOLUTION

NOT NULL constraint ensures that we cannot leave the column blank or empty and must provide a value to it, whether zero or greater than it.

SOLUTION

A constraint is a condition or check applicable on a field or set of fields. Two basic types of constraints are column constraints and table constraints.

SOLUTION

UPDATE is used to change some values in an existing row command. It specifies the rows to be changed using the where clause.

SOLUTION

The SQL TRUNCATE TABLE clause deletes all rows from a database table. We use truncate command only when we want to delete all rows in a table.

SOLUTION

INSERT command is used to insert values in the database.

SOLUTION

A candidate key that is not a primary key is called an Alternate Key.

For Example - In Suppliers table if there are two candidate keys - SuppID and Supp_Name. Here SuppID is the Primary Key then Supp_Name is the Alternate Key. 

SOLUTION

Primary Key – A column (or columns)  in a table that uniquely identifies each row. A primary key value is unique and cannot be null. There is only one primary key for a table.

Foreign key - A column (or a set of columns) that refers to the primary key in another table i.e. it is used as a link to a matching column in another table.

SOLUTION

The advantages provided by a DBMS are –

SOLUTION

The cartesian product is a binary operation denoted by a cross(x) sign. The cartesian product of two relations r1 and r2 is written as r1 x r2. The resulting relation has a degree(number of attributes) equal to the sum of the degrees of the two relations operated upon. The number of tuples(cardinality) of the new relation is the product of the number of tuples of the two relations operated on.
 

Table ITEM below has 3 attributes and 5 tuples.

Table SUPPLIER below has 2 attributes and 3 tuples.

If we write,

ITEM x SUPPLIER in the resulting table, then it will have 5 attributes (3 + 2) and 15 tuples (5 * 3)

SOLUTION

In the Hierarchical Data Model, data is represented by collections of records and the relationships among data are represented by links. Somehow it is similar to Network Data Model.

In Hierarchical Model records are organized as trees. It represents relationship among it records through parent-child relationships that can be easily represented through tree like structures.

The figure below shows hierarchical representation of a database.

SOLUTION

In the Relational Data Model, data is organized into tables with rows and columns. Each column has a unique name and is called an attribute. A row of the table represents a relationship among a set of values. As the table is a collection of such rows, it has a close relationship with the mathematical concept of relation, from where this model takes its name.

SOLUTION

As we want to count the entries including repeating entries for the city, we have to use keyword ALL. The * is the only argument that includes null when it is used only with COUNT.

SOLUTION

We will simply replace the VALUES clause with the sql query selecting the rows from the employee table.

SOLUTION

In an INSERT statement, only those columns can be omitted that have either default value defined or they allow null values.

SOLUTION

To insert values either of these commands can be used. INSERT INTO students VALUES (‘aditi’, ‘20’,‘f’,’e1’, 10,000); or INSERT INTO students (name, roll no, sex, grade, salary) VALUES (‘aditi’, ‘20’,‘f’,’e1’, 10,000);. The data values order should match with the table columns when we create table.

SOLUTION

The last two statements can not be mapped on to base table employee as the value of the primary code ecode is missing and also the sex column does not have any default value and it cannot have null at the same time. Thus, this view is not updatable.

SOLUTION

A view only stores its definition. Its data is derived from base table.

SOLUTION

We can create view using CREATE VIEW, the name of the view to be created, the word AS, and then the query.

SOLUTION

To update multiple columns, multiple column assignments can be specified with SET clause, separated by commas.

SOLUTION

UPDATE command is used when we want to change the values of some specified columns and it is used with WHERE clause.

SOLUTION

DISTINCT removes duplicate values.

SOLUTION

The declarative nature of SQL makes it possible to write an individual query in many different forms, each with identical results. For example, a query can be written with a standard join, a non-correlated sub-query or a correlated sub-query, each producing identical results but with widely varying internal execution plans.

SOLUTION

A navigational data language requires knowledge of the internal tables and index structures.

SOLUTION

BETWEEN operator includes the name “Aditi” and “Mukta” also. The > and < operators do not include these two names. The BETWEEN operator defines a range of values that the column values must fall in to make the condition true. This range includes both lower value and the upper value.

SOLUTION

The AND operator displays a record when all the conditions are met where as OR operator displays the record if any of the condition is met.

SOLUTION

The physical level is the lowest level which stores how the data is stored and how it is organised.

SOLUTION

The data abstraction is a three level process. The lowest level is the internal level which includes the details of data and is hidden from the user. The second level is the conceptual or the logical level which deals with the organisation of data, even this one is hidden from the end user. The last level is the external level with essential features of data and is visible to the end user.

SOLUTION

Data redudancy refers to the existence of multiple copies of the same data. When change in one is made, then it has to be made in others also, to avoid errors.

SOLUTION

Data inconsistency is a consequence of data redudancy. When change in any one copy of data is made then the other copies of data become inconsistent.

SOLUTION

Data abstraction means making the user aware of just the essential features of any object and hiding the details about that object.

SOLUTION

Data Models describe how data structures may be defined and data relationships or constraints maintained. The three most widely accepted data models are the Relational Data Model, Network Data Model and Hierarchical Data Model.

SOLUTION

DBMS stands for Data Base Management System. A database is an organized collection of related data.  A DBMS is a software that allows access to data contained in databases.It provides an effective and convenient method of storing, retrieving and updating information.