Answer to question 2009

Answer to question 2009

Q2(i) View: A view is a logical table based on a table or another view. Views do not contain any data. Tables contain data. Views just displayed data from tables. The view is stored as a SELECT statement in the data dictionary.

**Why Use Views?

• To restrict data access

• To make complex queries easy

• To provide data independence

• To present different views of the same data

(ii) Data Models: System designers develop models to explore ideas and improve the understanding of the database design. Models help communicate the concepts in people’s minds.

Purpose of Models:

Models help communicate the concepts in people’s minds. They can be used to do the following:

• Communicate

• Categorize

• Describe

•Specify

•Investigate

•Evolve

•Analyze

• Imitate

(iii) Foreign Key: The FOREIGN KEY designates a column or combination of columns as a foreign key and establishes a relationship between a primary key or a unique key in the same table or a different table. A foreign key value must match an existing value in the parent table or be NULL.

Foreign keys are based on data values and are purely logical, not physical, pointers.

(iv) Cartesian Product:

When a join condition is invalid or omitted completely, the result is a Cartesian product, in which all combinations of rows are displayed. All rows in the first table are joined to all rows in the second table.

(v) Single row Function: Single-row functions are used to manipulate data items. They accept one or more arguments and return one value for each row returned by the query.

(vi) SDLC:

(vii) DCL: The database administrator uses data control language (DCL) statements to grant privileges to users. DCL statements control access to the database and database objects.

(viii) Normalization:

Normalization is a process of putting things normal or making those right.

1NF                        Eliminate Repeating Groups

2NF                        Eliminate Redundant Data

3NF                        Eliminate Columns Not Dependent On Key

3.a. Define DBMS?

A database management system (DBMS) is a program that stores, retrieves, and modifies data in the database on request.

3b. Describe twelve rules of Dr. E. F. Codd.

Dr. E.F. Codd proposed the relational model for database systems in 1970. It is the basis for the relational database management system (RDBMS).

Twelve rules of Dr. E. F. Codd are below:

1. The Information rule
2. Guaranteed access rule
3. Systematic treatment of null values
4. Database Description /  Active online catalog based on the relational model
5. The comprehensive data sublanguage rule
6. The view updating rule
7. High-level insert, update, and delete
8. Physical data independence
9. Logical data independence
10. Integrity independence
11. Distribution independence
12. The non-subversion rule

4a. What is ER Modeling?

Entity-relationship model is a top-down object based logical data model. This is a widely used logical database design. The basic concept used by the entity-relationship model are therefore those of Entity, attribute and relationship.

4b. Describe the benefits of ER Modeling.

Benefits of ER Modeling-

• Documents information for the organization in a clear, precise format

• Provides a clear picture of the scope of the information requirement

• Provides an easily understood pictorial map for the database design

• Offers an effective framework for integrating multiple applications

4c. Describe the Key Components of ER Modeling.

Key Components

• Entity: An entity is a thing (object or concept) which the enterprise recognizes as being capable of an independence existence, and which can be uniquely identified. Examples are departments, employees.

• Attribute: An entity is represented by a set of attributes. So an attribute may be called as a property of an entity. For example, for the employee entity, the attributes would be the employee number, name, job title, hire date, department number, and so on.

• Relationship: A relationship is an association of two (or more) entities.

4d. Describe the rules of conversion of ERD to Tables.

Rule 1: Degree of the relationship is 1:1 and both side obligatory- then one table is necessary and the identifier of one entity will be posted to another

Rule 2: Degree of the relationship is 1:1 and one side obligatory and one side non-obligatory-

then two tables are necessary, one for each entity. The identifier of non-obligatory entity will be posted to obligatory entity.

Rule 3: Degree of the relationship is 1:1 and both side non-obligatory-

then three tables are necessary, two for two entities and one for relationship. Identifiers of both entities will be posted to relationship. Relationship may or may not have descriptive attributes.

Rule 4: Degree of the relationship is 1 : many and membership of many side obligatory,

and the other side may be obligatory or non-obligatory, then two tables are necessary, one for each entity. The identifier of one side will be posted to many side.

Rule 5: Degree of the relationship is 1:many and membership of many side non-obligatory,

and the other side may be obligatory or non-obligatory then three tables are necessary, two for two entities and the other for relationship. The identifier of both the entities will be the attributes of the relationship.

Rule 6: Degree of the relationship is many : many,

If the relationship between two entities is many : many regardless of membership class, define three  tables, one for each entity and one for the other for relationship.

Q7.a. What is an INDEX? When to create an INDEX?

 ** How are INDEXS created? When do not create an INDEX?

An Oracle server index is a schema object that can speed up the retrieval of rows by using a pointer. Indexes can be created explicitly or automatically.

An index provides direct and fast access to rows in a table. The index is used and maintained automatically by the Oracle server.

When to Create an Index

We should create indexes only if:

• The column contains a wide range of values

• The column contains a large number of null values

• One or more columns are frequently used together in a WHERE clause or join condition

• The table is large and most queries are expected to retrieve less than 2–4% of the rows

How Are Indexes Created?

• Automatically: A unique index is created automatically when we define a PRIMARY KEY or UNIQUE constraint in a table definition.

• Manually: Users can create nonunique indexes on columns to speed up access to the rows.

When Not to Create an Index

We should not create an index if:

• The table is small

• The columns are not often used as a condition in the query

• Most queries are expected to retrieve more than 2 to 4 percent of the rows in the table

• The table is updated frequently

• The indexed columns are referenced as part of an expression

  

Q7b. What is a SEQUENCE?

A sequence is a user created database object that can be shared by multiple users to generate unique integers.

The sequence is generated and incremented (or decremented) by an internal Oracle routine. This can be a time-saving object.

Q 8a. Which types of DML statement are used in Oracle?

INSERT- Adds a new row to the table

UPDATE- Modifies existing rows in the table

DELETE- Removes existing rows from the table

MERGE- Conditionally inserts or updates data in a table

Q 8c. List any six data types used in oracle.

VARCHAR2, CHAR, NUMBER, DATE, LONG, CLOB, RAW and LONG RAW, BLOB, BFILE, ROWID.

Q 9a. Why constraints are used in Database? List the CONSTRAINT types used in ORACLE.

The Oracle Server uses constraints to prevent invalid data entry into tables.

We can use constraints to do the following:

• Enforce rules on the data in a table whenever a row is inserted, updated, or deleted from that table. The constraint must be satisfied for the operation to succeed.

• Prevent the deletion of a table if there are dependencies from other tables

• Provide rules for Oracle tools, such as Oracle Developer

The following constraint types are valid in oracle:

• NOT NULL

• UNIQUE

• PRIMARY KEY

• FOREIGN KEY

• CHECK

True False solution 2009 2010

Hello friend
I just add the real fact of True false..............
If any one has any query please write a comment  below.............

2009

True False solution

1. A composite PRIMARY KEY is created by using the table-level definition.
2. A foreign key value must match an existing value in the parent table or be NULL.
3. A single column can have multiple CHECK constraints which refer to the column in its definition. There is no limit to the number of CHECK constraints which you can define on a column. CHECK constraints can be defined at the column level or table level.
4. The GROUP BY column does not have to be in the SELECT clause. You can use the group function in the ORDER BY clause.
5. You can remove privileges granted to other users by using the REVOKE statement.
6. Null values are displayed last for ascending sequences and first for descending sequences.
7. ….
8. You can present logical subsets or combinations of data by creating views of tables. A logical table based on a table or another view. A view contains no data of its own but is through which data from tables can be viewed or changed. The tables on which a view called base tables. The view is stored as a SELECT statement in the data dictionary.
9. A PRIMARY KEY constraint creates a primary key for the table. Only one primary key can be created for each table. The PRIMARY KEY constraint is a column or set of columns that uniquely identifies each row in a table. This constraint enforces uniqueness of the column or column combination and ensures that no column that is part of the primary key can contain a null value.
10. NVL Conversions for Various Data Types

Data Type	Conversion Example
NUMBER	NVL(number_column,9)
DATE	NVL(date_column, ’01-JAN-95’)
CHAR or VARCHAR2	NVL(character_column, ’Unavailable’)

11. ….
12. The default display of queries is all rows, including duplicate rows.
13. You can remove existing rows from a table by using the DELETE statement.
14. The NOT NULL constraint can be specified only at the column level, not at the table level.
15. The WHERE clause follows the FROM clause.
16. UNIQUE constraints allow the input of nulls unless you also define NOT NULL constraints for the same columns.
17. …..
18. VIEW Can be dropped without removing the underlying data
19. ….
20. Performing DML Operations on a View. You can perform DML operations on data through a view if those operations follow certain rules. You can remove a row from a view unless it contains any of the following:
• • Group functions
• • A GROUP BY clause
• • The DISTINCT keyword
• • The pseudocolumn ROWNUM keyword
21. …….
22. Use the HAVING clause to restrict groups:
• Rows are grouped.
• The group function is applied.
• Groups matching the HAVING clause are
• displayed.
23. You can sort by a column that is not in the SELECT list.
24. ……………….

Default Ordering of Data

The default sort order is ascending:

• Numeric values are displayed with the lowest values first—for example, 1–999.

• Date values are displayed with the earliest value first—for example, 01-JAN-92 before

01-JAN-95.

• Character values are displayed in alphabetical order—for example, A first and Z last.

• Null values are displayed last for ascending sequences and first for descending sequences.

Reversing the Default Order

To reverse the order in which rows are displayed, specify the DESC keyword after the column name

the ORDER BY clause. The slide example sorts the result by the most recently hired employee.

2010

True False solution

1. A DML statement is executed when you:

–       Add new rows to a table

–       Modify existing rows in a table

–       Remove existing rows from a table

2. ……………………
3. A foreign key value must match an existing value in the parent table or be NULL.

Foreign keys are based on data values and are purely logical, not physical, pointers.

4. A privilege granted on a synonym is converted to a privilege on the base table referenced by the synonym.
5. ….
6. When Does a Transaction Start and End?

A transaction begins when the first DML statement is encountered and ends when one of the

following occurs:

• • A COMMIT or ROLLBACK statement is issued
• • A DDL statement, such as CREATE, is issued
• • A DCL statement is issued
• • The user exits iSQL*Plus
• • A machine fails or the system crashes

After one transaction ends, the next executable SQL statement automatically starts the next

transaction.

A DDL statement or a DCL statement is automatically committed and therefore implicitly ends a

transaction.

7. All columns in the SELECT list that are not group functions are included in the GROUP BY clause.
8. ……
9. Any column or expression in the SELECT list that is not an aggregate function must be in the GROUP BY clause.
10. Columns without the NOT NULL constraint can contain null values by default.
11. COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT statement, including duplicate rows and rows containing null values in any of the columns.
12. What is an Index?

An index:

• • Is a schema object
• • Is used by the Oracle server to speed up the
• retrieval of rows by using a pointer
• • Can reduce disk I/O by using a rapid path access
• method to locate data quickly
• • Is independent of the table it indexes
• • Is used and maintained automatically by the
• Oracle server
12. You can decrease the width of a column only if the column contains only null values or if the table has no rows.
13. Only one primary key can be created for each table.
14. To drop an index, you must be the owner of the index or have the DROP ANY INDEX privilege.
15. Single-row functions can be nested to any depth.
16. Table names and column names must begin with a letter and be 1–30 characters long.
17. …………….
18. NVL Function

Converts a null to an actual value.

• • Data types that can be used are date, character,
• and number.
• • Data types must match:

–       NVL(commission_pct,0)

–       NVL(hire_date,’01-JAN-97’)

–       NVL(job_id,’No Job Yet’)

20. Values specified with the BETWEEN condition are inclusive. You must specify the lower limit first.
21. A view is a logical table based on a table or another view. A view contains no data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called base tables. The view is stored as a SELECT statement in the data dictionary.
22. Performing DML Operations on a View You can perform DML operations on data through a view if those operations follow certain rules. You can remove a row from a view unless it contains any of the following:
• • Group functions
• • A GROUP BY clause
• • The DISTINCT keyword
• • The pseudocolumn ROWNUM keyword
23. You can use the group function in the ORDER BY clause.
24. When you drop an integrity constraint, that constraint is no longer enforced by the Oracle server and is no longer available in the data dictionary.

DBMS Q solve 2010

2010-3.a

select last_name,
TO_CHAR(hire_date, 'ddth "of" month YYYY fmHH:MI:SS AM')
 AS "JOINING DATE"

from employees;


2010-3.B


select emp.last_name,dept.department_name,job.job_title,emp.hire_date

from employees emp,Departments dept, jobs job
where emp.department_id=dept.department_id AND emp.job_id= job.job_id AND (emp.hire_date>='1 jun 1987' AND emp.hire_date<='28 jun 1987');



2010 4.C

insert into jobs values(M_ENGR,'Maintenance Engineer',35000,55000);


2010. 3.c.

select emp.last_name, dept.department_name, job.job_title, emp.hire_date
 from employees emp, departments dept, jobs job
where emp.department_id=dept.department_id
AND emp.job_id=job.job_id
AND dept.department_name= 'IT';

SQL Solution 2009 & 2010 Q

Solution 2009

--6.a.

SELECT Employee_name as NAME,

TO_CHAR(hire_date,

’fmDdspth "of" Month YYYY fmHH:MI:SS AM’)

Joining Date

FROM employees;

Solution 2010

--3.a.

SELECT Employee_name as NAME,

TO_CHAR(hire_date,

’ddth "of" Month YYYY fmHH:MI:SS AM’)

Joining Date

FROM employees;

---3.b.

select emp.employee_name,d.Department_name,j.Job_title,emp.hire_date

from Employees emp,Departments d,Jobs j

where emp.department_id=d.department_id AND

emp.job_id=j.job_id AND

and (emp.hire_date='June 2008' );

---3.c.

SELECT

emp.employee_name,

jobs.job_title,

dept.location_id

FROM Employees emp,Departments dept,Jobs

where emp.department_id=d.department_id AND

emp.job_id=j.job_id AND

department.department_name =  'ICT';

(incomplate)

--4.c.

insert into jobs (job_id,job_title,min_salary,max_salary)

values (M_ENGR, 'Maintenance Engineer', 35000, 55000);

--5.c.

CREATE VIEW DEPT_MKT

AS SELECT emp.employee_id, emp.employee_name, emp.hire_date, emp.Job_id, emp.salary, emp.Manager_id, dept.Department_id, dept.department_name

FROM employees emp, departments dept

WHERE emp.department_id=d.department_id AND

department.department_name =  'Marketing';