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:
- The Information rule
- Guaranteed access rule
- Systematic treatment of null values
- Database Description / Active online catalog based on the relational model
- The comprehensive data sublanguage rule
- The view updating rule
- High-level insert, update, and delete
- Physical data independence
- Logical data independence
- Integrity independence
- Distribution independence
- 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
No comments:
Post a Comment