Short Question and answers

1) What is QBE?

A) Query by example is a query language used in relational databases that allows users to search for information in tables and fields by providing a simple user interface where the user will be able to input an example of the data that he or she wants to access. The principle of QBE is that it is merely an abstraction between the user and the real query that the database system will receive. In the background, the user's query is transformed into a database manipulation language form such as SQL, and it is this SQL statement that will be executed in the background

2) What is Data dictionary and System Catalog?

A) Data Dictionary : The Data Dictionary contains meta data – data about data. The Data Dictionary provides a detailed description of all tables found within the user (or designer) created database. It contains at least all of attribute’s names and characteristics for each table in the system. It is sometimes described as “the database designer’s database” because it records the design decisions about tables and their structures.

System Catalog : The System catalog is actually a system created database whose tables store the user (or designer) created database characteristics and contents. Like the Data Dictionary, the System Catalog contains metadata. It is described as the detailed system data dictionary that describes all objects within the database, including data about the table names, the table’s creator and creation date, the number of columns in each table, the data type corresponding to each column, index filenames, index creators, authorized users, and access privileges.

3) What is the difference between a database and a table?

Database : The database is a structure that contains one or more tables and metadata. A database is a place where we store required data. A database has tables of data, views, indexes and programs. A database can hold thousands of tables. The database is managed using tools called Database Management Systems(like Oracle, Informix, Sybase, DB2, SQL Server, MYSQL etc.,), you can create, view, modify, and delete databases. Different types of Databases are -Relational -Object Oriented -Object Relational.

Table : A table, a logical structure that represents an entity set, is only one of the components of a database. A table is perceived as two-dimensional structure composed of rows and columns. It exists inside a database. Relational database stores data in tables(called relations). These tables are related to each other. It is an object inside a database. What are the characteristics of Relational Table.

4) What is Entity Integrity and Referential Integrity.

A) Entity integrity is important, because it means that a proper search for an existing tuple (row) will always be successful. And the failure to find a match on a row search will always mean that the row for which the search is conducted does not exist in that table. Referential integrity is important, because its existence ensures that it will be impossible to assign a non-existing foreign key value to a table.

5) Write a short note on Keys

A) In relational model keys are very important because they are used to ensure that each row in a table is uniquely identifiable. They are also used to establish relationships among tables and to ensure the integrity of the data. Therefore, a proper understanding of the concept and use of keys in the relational model is very important. A key consists of one or more attributes that determine (find out) other attributes. Different type of keys are Super Key, Candidate Key, Primary Key, Secondary Key and Foreign Key.

6) Write about Relational Algebra.

A) Relational algebra is a widely used procedural query language. It collects instances of relations as input and gives occurrences of relations as output. It uses various operation to perform this action

7) Define the terms Super Key, Candidate Key, Primary Key, Composite, Primary-Key, Alternate Key, Secondary Key, Non-key attributes and Foreign Key

A) Super Key : It is an attribute (or combination of attributes) that uniquely identifies each row in a table. In short, the super key functionally determines all of a row’s attributes.

Ex: Consider STUDENT table consists of attributes namely 

STU_NUM,STU_LNAME, STU_FNAME, STU_INT, STU_PHONE, STU_DOB, 

STU_CLASS, STU_EMAIL. In this table Super Key could be any of the following.

STU_NUM (or)

STU_NUM,STU_LNAME (or)

STU_NUM,STU_LNAME,STU_FNAME (or)

STU_NUM,STU_LNAME,STU_FNAME,STU_INT.

In fact, STU_NUM, with or without additional attributes, can be a Super Key even 

when the additional attributes are surplus.

Candidate Key : A candidate key can be described as a super key without unnecessary attributes, that is, a minimal (irreducible) super key.

Ex : In the above example STU_NUM is considered as a candidate key since it is a minimal super key.

Primary Key : A candidate key selected to uniquely identify all other attribute values in any given row and cannot contain null entries. (or) A primary key is a set of one or more attributes that can uniquely identify tuples within the relation.

Ex : In the above example STU_NUM is considered as primary key.

Composite-Primary-Key : A group of attributes(more than one) is considered as a Composite-Primary-Key, if it serves the purpose of a primary key, that is, group of attributes provides a unique value which is used to uniquely identify rows in a table.

Ex : Let us consider a table CLASS with attributes CRS_CODE, CLASS_SECTION, CLASS_TIME, PROF_CODE). Here no attribute alone can be taken as primary key 

so the combination of CRS_CODE and CLASS_SECTION is treated as composite primary-key.

Candidate Key : All attribute combinations inside a relation that can serve as primary key are candidate keys as they are candidates for the primary key positions.

Ex : Let us consider a table SUPPLIER with attributes SUP_NUM, SUP_NAME, STATUS, CITY. Here SUP_NUM and SUP_NAME are eligible for candidate keys,because SUP_NUM and SUP_NAME alone can serve the purpose of a primary key.

Note : In case of two or more candidate keys, the database designer decides one of them as the primary key for the relation according to requirement.

Alternate Key : A candidate key that is not the primary key is called as an Alternate key.

Ex : In Supplier example SUP_NAME is treated as alternate key if SUP_NUM is considered as primary key and vice-versa

Secondary Key : An attribute (or combination of attributes) used strictly for data retrieval purposes.

Ex : Let us consider a table CUSTOMER with attributes CUST_NUM, 

CUST_LNAME, CUST_FNAME, CUST_PHONE, CUST_ZIP. Consider 

CUST_NUM is primary key. While searching the data, Instead of using CUST_NUM as key we may use CUST_LNAME and CUST_PHONE combination as key which is considered as secondary key.

Non-Key Attributes : The non-primary-key attributes of a table are referred to as non-key attributes.

Ex : In the above CUSTOMER table except CUST_NUM all attributes are treated as non-key attributes.

Foreign Key : An attribute (or combination of attributes) in one table whose values must either math the primary key in another table or be null. It is used to represent the relationship between two tables. ( or) A non-key attribute, whose values are derived from the primary key of some other table (or be null), is known as Foreign-Key in its current table.

Ex : Consider two tables COURSE with attributes CRS_CODE, DEPT_CODE, CRS_DESC and CLASS with attributes CRS_CODE, CLASS_SEC, CLASS_TIME. Here CRS_CODE is primary key in COURSE table and foreign-key in CLASS table. Here foreign-key contains either matching values or nulls.