Database Management System (CSM-2202): Unit II
By:
Prof. Mohammad Ubaidullah Bokhari
Department of Computer Science Aligarh Muslim University, Aligarh
Relational Database Management Systems(RDBMS)
• A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd.
• RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
RDBMS: What is Table ?
• In Relational database model, a table is a collection of data elements organised in terms of rows and columns.
• A table is also considered as a convenient representation of relations.
• Table is the most simplest form of data storage.
• Below is an example of an Employee table.
ID Name Age Salary
1 Adam 34 13000
2 Alex 28 15000
3 Stuart 20 18000
4 Ross 42 19020
RDBMS: What is a Tuple?
• A single entry in a table is called a Tuple or Record or Row. A tuple in a table represents a set of related data. For example, the above Employee table has 4 tuples/records/rows.
• Following is an example of single record or tuple.
1 Adam 34 13000
RDBMS: What is an Attribute?
• A table consists of several records(row), each record can be broken down into several smaller parts of data known as Attributes.
• The above Employee table consist of four attributes, ID, Name, Age and Salary.
• When an attribute is defined in a relation(table), it is defined to hold only a certain type of values, which is known as Attribute Domain.
• A relation schema describes the structure of the relation, with the name of the relation(name of table), its attributes and their names and type.
• A relation key is an attribute which can uniquely identify a particular tuple(row) in a relation(table).
Relational Integrity Constraints
• Every relation in a relational database model should abide by or follow a few constraints to be a valid relation, these constraints are called as Relational Integrity Constraints.
• The three main Integrity Constraints are:
➢Key Constraints
➢Domain Constraints
➢Referential integrity Constraints
Introduction to Conventional Data Model & Systems
• Data models define how the logical structure of a database is modeled.
• Data Models are fundamental entities to introduce abstraction in a DBMS.
• Data models define how data is connected to each other and how they are processed and stored inside the system.
Network Data Model
• In this model data is organised more like a graph, and are allowed to have more than one parent node.
• In this database model data is more related as more relationships are established in this database model.
• Also, as the data is more related, hence accessing the data is also easier and fast. This database model was used to map many-to-many data relationships.
IDMS
(Integrated Database Management System)
• IDMS (Integrated Database Management System) is primarily a network model (CODASYL) database management system for mainframes.
• It was first developed at B.F. Goodrich and later marketed by Cullinane Database Systems
(renamed Cullinet in 1983).
• Since 1989 the product has been owned by
Computer Associates (now CA Technologies), who renamed it Advantage CA-IDMS and later simply to CA IDMS.
• One of the sophisticated features of IDMS was its built-in Integrated Data Dictionary (IDD). The IDD was primarily developed to maintain database definitions. It was itself an IDMS database.
• DBAs (database administrators) and other users interfaced with the IDD using a language called Data Dictionary Definition Language (DDDL).
• IDD was also used to store definitions and code for other products in the IDMS family such as ADS/Online and IDMS-DC.
• IDD's power was that it was extensible and could be used to create definitions of just about anything. Some companies used it to develop in-house documentation.
• IDMS organizes its databases as a series of files. These files are mapped and pre-formatted into so-
called areas. The areas are subdivided into pages which correspond to physical blocks on the disk. The database records are stored within these blocks.
• The DBA allocates a fixed number of pages in a file for each area. The DBA then defines which records are to be stored in each area, and details of how they are to be stored.
Hierarchical data model
• A hierarchical database model is a data model in which the data is organized into a tree-like structure.
• The data is stored as records which are connected to one another through links. A record is a collection of fields, with each field containing only one value.
• The entity type of a record defines which fields the record contains.
• A record in the hierarchical database model corresponds to a row (or tuple) in the relational database model and an entity type corresponds to a table (or relation).
• In hierarchical model, data is organised into tree-like structure with one one-to-many relationship between two different types of data, for example, one
department can have many courses, many professors and of-course many students.
IMS
• IMS stands for Information Management System.
IMS was developed by IBM with Rockwell and Caterpillar in year 1966 for the Apollo program to send a man to the moon.
• It started the database management system revolution and still continues to evolve to meet data processing requirements. IMS provides an easy-to-use, reliable, and standard environment for executing high-performance transactions.
• IMS database is a hierarchical database where data is stored at different levels and each entity is dependent on higher level entities.
• The physical elements on an application system that use IMS are shown in the following figure.
IMS: Database Management
• A Database Management system is a set of
application programs used for storing, accessing, and managing data in the database.
• IMS database management system maintains integrity and allows fast recovery of data by organizing it in such a way that it is easy to retrieve.
• IMS maintains a large amount of world's
corporate data with the help of its database management system.
IMS: Transaction Manager
• The function of transaction manager is to
provide a communication platform between the database and the application programs.
IMS acts as a transaction manager.
• A transaction manager deals with the end- user to store and retrieve data from the database.
• IMS can use IMS DB or DB2 as its back-end database to store the data.
IMS : DL/I – Data Language Interface
• DL/I comprises of application programs that grant access to the data stored in the database.
• IMS DB uses DL/I which serves as the interface language that programmers use for accessing the database in an application program.
Characteristics of IMS
• IMS supports applications from different languages such as Java and XML.
• IMS applications and data can be accessed over any platform.
• IMS DB processing is very fast as compared to DB2.
Limitations of IMS
• Implementation of IMS DB is very complex.
• IMS predefined tree structure reduces flexibility.
• IMS DB is difficult to manage.
Object-oriented database
management systems (OODBMS)
• An object database is a database management system in which information is represented in the form of objects as used in object-oriented programming.
• Object databases are different from relational databases which are table-oriented.
• Object-relational databases are a hybrid of both approaches.
• OODBMSs allow object-oriented programmers to develop the product, store them as objects, and replicate or modify existing objects to make new objects within the OODBMS.
• Because the database is integrated with the programming language, the programmer can maintain consistency within one environment, in that both the OODBMS and the programming language will use the same model of representation.
• Relational DBMS projects, by way of contrast, maintain a clearer division between the database model and the application.
Objects and Identity
• In an object-oriented database, each real-
world entity is represented by an object. This object has a state and a behaviour.
• The combination of the current values of an object's attributes define the object's state.
• A set of methods, acting on an object's state, define the object's behaviour.
• When it comes to identity, each object in the database is defined by a unique object
identifier.
• There are certain differences between keys and object identifiers.
• A key is an attribute value, or a set of attribute values. An example would be an employee
number.
• When using object identifiers, we have to distinguish two different kinds of object equality.
• The first one is identity equality. Identity equality is given, if two objects have the same object identifier.
• The second one is value equality. Value equality is given, if all the attributes' values of two objects are identical. Identity equal objects are always value equal. The reverse is not true.
Encapsulation
• Encapsulation is a basic concept for all object- oriented technologies.
• It was created for making a clear distinction between the specification and the
implementation of an operation and in this way for enabling modularity.
• Software that exceeds a certain size needs some sort of modular architecture for enabling design, implementation and maintenance to be executed by a larger group of programmers.
Classes and Instantiation
• When looking on the concept of classes in object-oriented databases, you have to
distinguish the terms class and type.
• A type is used to describe a set of objects that share the same behaviour.
• In this sense, an object's type depends on which operations can be invoked on the object.
• A class is a set of objects that have the exactly same internal structure. In that way, a class defines the implementation of an object and a type describes the way the object can be used.
• The term instantiation aims at the fact that a class definition can be used to generate a set of objects that share the same structure and behaviour.
• A class specifies a structure (a set of attributes), a set of operations and a set of methods, which implement the operations.
Inheritance
• Inheritance makes it possible to define a class as a subclass of an already existing one (superclass). The subclass inherits all attributes and methods from the superclass and can additionally define its own attributes and methods.
• This concept is an important mechanism for supporting reusability. Identical parts of the structure of two different classes may be defined only once in a common superclass. Is this way, less code has to be written.
• There are some systems that allow a class to be subclass of more than one superclass. This feature is
SQL - Object-Relational Extensions
• Postgresql and many other databases actually have many extensions that go well beyond the relational data model.
• As these extensions violate relational data
model, think about what you are giving up and use them sparingly!
– Simplicity of data model and queries
– Optimizations may not be as easy to perform
ODMG
• The Object Data Management Group (ODMG) was conceived in the summer of 1991 at a
breakfast with object database vendors that was organized by Rick Cattell of Sun Microsystems.
• In 1998, the ODMG changed its name from the Object Database Management Group to reflect the expansion of its efforts to include
specifications for both object database and object-relational mapping products.
•
Major components :ODMG 3.0 specification
• Object Model: This was based on the Object Management Group's Object Model. The OMG core model was designed to be a common denominator for object request brokers, object database systems, object programming languages, etc. The ODMG designed a profile by adding components to the OMG core object model.
• Object Specification Languages: The ODMG Object Definition Language (ODL) was used to define the object types that conform to the ODMG Object Model. The ODMG Object Interchange Format (OIF) was used to dump and load the current state to or from a file or set of files.
• Object Query Language (OQL): The ODMG OQL was a declarative (nonprocedural) language for query and
• C++ Language Binding: This defined a C++ binding of the ODMG ODL and a C++ Object Manipulation Language (OML). The C++ ODL was expressed as a library that provides classes and functions to implement the concepts defined in the ODMG Object Model. The C++ OML syntax and semantics are those of standard C++ in the context of the standard class library. The C++ binding also provided a mechanism to invoke OQL.
• Smalltalk Language Binding:This defined the mapping between the ODMG ODL and Smalltalk, which was based on the OMG Smalltalk binding for the OMG Interface Definition Language (IDL). The
Smalltalk binding also provided a mechanism to invoke OQL.
• Java Language Binding: This defined the binding between the
ODMG ODL and the Java programming language as defined by the Java 2 Platform. The Java binding also provided a mechanism to invoke OQL.
Object Definition Language (ODL)
• Object Definition Language (ODL) is the specification language defining the interface to object types conforming to the ODMG Object Model.
• Often abbreviated by the acronym ODL.
• This language's purpose is to define the
structure of an Entity-relationship diagram.
Example of ODL commands:
• Class declarations
Interface < name > { elements = attributes, relationships, methods }
• Element Declarations
attributes ( < type > : < name > );
relationships ( < rangetype > : < name > );
• Example
Type Date Tuple (year, day, month) Type year, day, month integer
interface Manager { keys id; attribute String name; attribute String phone;
relationShip Set<Employee> employees inverse Employee::manager}
interface Employee { keys id; attribute String name; attribute Date Start_D ate; relationShip Manager manager inverse Manager::employees }
Object Query Language (OQL)
• Object Query Language (OQL) is a query language standard for object-oriented databases modeled after SQL. OQL was developed by the Object Data Management Group (ODMG).
• Because of its overall complexity nobody has ever fully implemented the complete OQL.
• OQL has influenced the design of some of the newer query languages like JDOQL and EJB QL, but they can't be considered as different flavors of OQL.
The following rules apply to OQL statements:
• All complete statements must be terminated by a semi-colon.
• A list of entries in OQL is usually separated by commas but not terminated by a comma(,).
• Strings of text are enclosed by matching quotation marks.
OQL Queries
• SELECT, FROM, WHERE SELECT <list of values>
FROM <list of collections and variable assignments>
WHERE <condition>
• Example Query
Give the names of people who are older than 26 years old:
• SELECT SName: p.name FROM p in People
WHERE p.age > 26 (hit Ctrl-D)
Dot Notation & Path Expressions
• We use the dot notation and path expressions to access components of complex values.
Let variables t and ta range over objects in extents (persistent names) of Tutors and TAs (i.e., range over objects in sets Tutors and TAs).
ta.salary -> real
t.students -> set of tuples of type tuple(name: string, fee: real) representing students
t.salary -> real
• Cascade of dots can be used if all names represent objects and not a collection.
Set Operations and Aggregation
• The standard O2C operators for sets
are + (union), * (intersection), and - (difference).
In OQL, the operators are written as UNION, INTERSECT and EXCEPT , respectively.
• Example Query
Give the names of TAs with the highest salary:
SELECT t.name FROM t in TAs
WHERE t.salary = max ( select ta.salary from ta in TAs )< /FONT >
Subqueries in FROM Clause
• Example Query
Give the names of the Tutors which have a salary greater than $300 and have a student paying more than $30:
SELECT t.name
FROM ( SELECT t FROM Tutors t WHERE t.salary > 300 ) r, r.students s
WHERE s.fee > 30
Subqueries in WHERE Clause
• Example Query
Give the names of people who aren't TAs:
• SELECT p.name
FROM p in People
WHERE not ( p.name in SELECT t.name FROM t in TAs )
Embedded OQL
• Instead of using query mode, you can incorporate these queries in your O2 programs using the "o2query" command:
run body {
o2 real total_salaries;
o2query( total_salaries, "sum ( SELECT ta->get_salary \ FROM ta in TAs )" );
printf("TAs combined salary: %.2f\n", total_salaries);
};
• The first argument for o2query is the variable in which you want to store the query results. The second argument is a string that
contains the query to be performed. If your query string takes up several lines, be sure to backslash (\) the carriage returns.
E-mail: mubokhari@gmail.com
