Subject: RDBMS Using Oracle
Q.1 : Explain the basic concept of ER- Model? Write its basic characteristics.
Ans:
Entity relationship model defines the conceptual view of database. It works around real world entity and association
among them. At view level, ER model is considered well for designing databases.
Entity
A real-world thing either animate or inanimate that can be easily identifiable and distinguishable. For example, in a
school database, student, teachers, class and course
Entity
A real-world thing either animate or inanimate that can be easily identifiable and distinguishable. For example, in a
school database, student, teachers, class and course offered can be considered as entities. All entities have some
attributes or properties that give them their identity.
TYPES OF ATTRIBUTES:
1. Simple attribute:
Simple attributes are atomic values, which cannot be divided further. For example, student’s phone-number is an
atomic value of 10 digits.
2.Composite attribute:
Composite attributes are made of more than one simple attribute. For example, a student’s complete name may have
first_name and last_name.
3. Derived attribute:
Derived attributes are attributes, which do not exist physical in the database, but there values are derived from other
attributes presented in the database. For example, average_salary in a department should be saved in database
instead it can be derived. For another example, age can be derived from data_of_birth.
4. Single-valued attribute:
Single valued attributes contain on single value. For example: Social_Security_Number.
5. Multi-value attribute:
Multi-value attribute may contain more than one values.
Q.2 Explain the various set operations in Oracle.
Set operators in the Oracle database are used to join the results of two or more SELECT statements from single or multiple tables.
The set operators in the Oracle database are also called vertical Joins. Oracle has the following set operators:-
Oracle Set Operators
UNION Set Operator:– UNION set operator returns the combined result in the sorted order (ascending by default), and it excludes the duplicate results. Only one result is displayed for the duplicate results. For example:- f(x) = {a,b,x} and g(x) = {a,a,b,c,c,d} then the UNION Set for them will be {a,b,d,x}
UNION ALL Set Operator:– UNION ALL set operator is similar to UNION set operator but it includes the duplicate results in the final result. The UNION ALL set operator gives better performance as compared to the UNION set operator. Because resources are not used in filtering duplicates and sorting the result set. For example:- f(x) = {a,b,x} and g(x) = {a,a,b,c,c,d} then the UNION ALL Set for them will be {a,b,x,a,a,b,c,c,d}
INTERSECT Set Operator:– INTERSECT set operator in Oracle returns the common values in sorted order (ascending by default). It produces an unduplicated result. For example:- f(x) = {a,b,x} and g(x) = {a,a,b,c,c,d} then the INTERSECT Set for them will be {a,b}
Q.3 Write a note on database integrity
ANS::
Data integrity is the overall completeness, accuracy and consistency of data. This can be indicated by the absence of alteration between two instances or between two updates of a data record, meaning data is intact and unchanged.
Data integrity is usually imposed during the database design phase through the use of standard procedures and rules. It is maintained through the use of various error-checking methods and validation procedures.
Q.4 Differentiate between multimedia database & Conventional DBMS.
Conventional database
More specifically, a database is an electronic system that allows data to be easily accessed, manipulated, and updated. In other words, a database is used by an organization as a method of storing, managing and retrieving information. Modern databases are managed using a database management system (DBMS).
multimedia databse
Multimedia database is the collection of interrelated multimedia data that includes text, graphics (sketches, drawings), images, animations, video, audio etc and have vast amounts of multisource multimedia data. The framework that manages different types of multimedia data which can be stored, delivered and utilized in different ways is known as multimedia database management system. There are three classes of the multimedia database which includes static media, dynamic media and dimensional media.
Q.5 Compare all the three models of database.
A database model is a type of data model that determines the logical structure of adatabase and fundamentally determines in which manner data can be stored, organized, and manipulated. The most popular example of a database model is the relational model, which uses a table-based format.
HIERARCHICAL MODEL
The hierarchical model serves data to the user in hierarchy of data elements. This model only provides for one-to-one and one-to-many relationships, not many-to-many. Thus, the model can be said to be shaped like a logical tree, with data elements as nodes. This model’s main drawback is its inability to support many-to-many relationships (relational/object, object-oriented, semi-structured, associative, and entity attribute value).
NETWORK MODEL
The network model is similar to the hierarchical model, except that it provides for many-to-many relationships. Visually, this model can be said to be shaped like several logical trees sharing common branches. This model’s main drawback is its difficulty to implement.
RELATIONAL MODEL
The relational model is commonly used today. This model was designed to overcome the complexity and inflexibility of the network and hierarchical models. The relational model is composed of unique tables, which in turn are composed of records (or rows, conceptually) and fields (or columns, conceptually speaking). One field in each table may be designated as the primary key, or the unique value that will be used to distinguish that record from the other records within the current table. Additionally, you may designate one or more fields as foreign keys which can be used to uniquely identify a record in a different table (hence, relational). Popular relational database management systems include Oracle, MySQL, and Microsoft SQL Server.