RDBMS.ppt What is RDBMS RDBMS stands for Relational Database Management System.

What is RDBMS RDBMS stands for Relational Database Management System. RDBMS stands for Relational Database Management System.

All modern database management systems like SQL, MS SQL Server, IBM DB2, ORACLE, My-SQL, and Microsoft Access are based on RDBMS.

It is called Relational Database Management System (RDBMS) because it is based on the relational model introduced by E.F. Codd.Brief History of RDBMS
From 1970 to 1972, E.F. Codd published a paper to propose using a relational database model.

RDBMS is originally based on E.F. Codd's relational model invention.A relational database is the most commonly used database. It contains several tables, and each table has its primary key.

Due to a collection of an organized set of tables, data can be accessed easily in RDBMS.Everything in a relational database is stored in the form of relations. The RDBMS database uses tables to store data. A table is a collection of related data entries and contains rows and columns to store data. Each table represents some real-world objects such as person, place, or event about which information is collected. The organized collection of data into a relational table is known as the logical view of the database.
A row of a table is also called a record or tuple. It contains the specific information of each entry in the table. It is a horizontal entity in the table. For example, The above table contains 5 records.

Properties of a row:

No two tuples are identical to each other in all their entries.
All tuples of the relation have the same format and the same number of entries.
The order of the tuple is irrelevant. They are identified by their content, not by their position.The domain refers to the possible values each attribute can contain. It can be specified using standard data types such as integers, floating numbers, etc. For example, An attribute entitled Marital_Status may be limited to married or unmarried values.

NULL Values
The NULL value of the table specifies that the field has been left blank during record creation. It is different from the value filled with zero or a field that contains space.

Data Integrity
There are the following categories of data integrity exist with each RDBMS:Entity integrity: It specifies that there should be no duplicate rows in a table.

Domain integrity: It enforces valid entries for a given column by restricting the type, the format, or the range of values.

Referential integrity specifies that rows cannot be deleted, which are used by other records.

User-defined integrity: It enforces some specific business rules defined by users. These rules are different from the entity, domain, or referential integrity.Generalization is like a bottom-up approach in which two or more entities of lower level combine to form a higher level entity if they have some attributes in common.
In generalization, an entity of a higher level can also combine with the entities of the lower level to form a further higher level entity.
Generalization

1. Copyright © 2009, Oracle. All rights reserved.
Introduction

2. Copyright © 2009, Oracle. All rights reserved.I-2
Requirements of an Application:

3. Copyright © 2009, Oracle. All rights reserved.I-3
Information is a critical factor for any business to be successful.
Information is extracted from data stored in databases.
Database is an integral part of an organization. Aspiring database
developers should be able to efficiently design and implement
databases.
Knowledge of these will enable the developers to build robust
database solutions.
This module will help students to understand the concepts related
to relational databases.

4. Copyright © 2009, Oracle. All rights reserved.I-4
What is a
Database?

5. Copyright © 2009, Oracle. All rights reserved.I-5
A database is a collection of logically related data.
Data means known facts, which are meaningful and can be
recorded.
For example:
Hard Disk
Address
Book

6. Copyright © 2009, Oracle. All rights reserved.I-6
Database Management is the task of maintaining databases so that
the information is easily available.
Database Management Systems (DBMS) is the software required
to perform the task of maintaining databases.
Management of data involves:
Defining structures for data storage.
Providing methods for data manipulation, such as adding, editing,
and deleting data.
Providing data security against unauthorized access.
Database management systems are now available on a wide range
of computers, from desktops to mainframes. The size and power of
the computer determines the system facilities, such as security and
storage.

7. Copyright © 2009, Oracle. All rights reserved.I-7
The main objectives of any DBMS are to:
Provide an efficient and easy way to store, update, and retrieve data
from a database.
Manage information about users who interact with the DBMS, and the
tasks that these users can perform on the data.
Today, relational databases are the most popular form of storing
data. Besides storing data and extracting information, a database has
to be managed effectively. The software that is used to manage and
retrieve information in a relational database is called a Relational
Database Management System (RDBMS).
A Relational Database Management System (RDBMS) is an
advanced version of DBMS that also defines the relationship between
various data values.

8. Copyright © 2009, Oracle. All rights reserved.I-8
Some typical applications of an RDBMS are:
Airline and railway reservations
Banking applications
Manufacturing industry
Order processing
Hospital management systems
Library management systems
Hotel industry

9. Copyright © 2009, Oracle. All rights reserved.I-9
Several RDBMS products are available today. Some popular products are:
Oracle
Microsoft SQL Server
Sybase
Ingres
DB2

10. Copyright © 2009, Oracle. All rights reserved.I-10
Now, let us understand the
working of DBMS.

11. Copyright © 2009, Oracle. All rights reserved.I-11
Database
DBMS
Request for
data
Retrieved data
returned as a result
User system to extract
information from the
database

12. Copyright © 2009, Oracle. All rights reserved.I-12
Relational data structure:
Each table in a database has a unique table name.
The row (or record) in the table is called a tuple.
The column (or field) is called an attribute.
Every column in a table must have a unique name.
The number of tuples is called the cardinality of the table.
The number of attributes is called the degree of the table.
Rows are unordered in a relation.
A column or combination of columns that uniquely identifies each row in
the table is called the primary key of the table.
A table where every row is different from all other rows is called a relation.
A domain is a collection of values from which one or more attributes
(columns) draw their actual values.

13. Copyright © 2009, Oracle. All rights reserved.I-13
The following diagram describes the data structure of an RDBMS.
T_CODE NAME AGE SEMESTER
I1001 Nancy Mathews 27 I
I1002 Catherine 38 II
I1003 Mac Thams 50 IV
I1004 Joe Nelson 35 III
Tuple

14. Copyright © 2009, Oracle. All rights reserved.I-14
T_CODE NAME AGE SEMESTER
I1001 Nancy Mathews 27 I
I1002 Catherine 38 II
I1003 Mac Thams 50 IV
I1004 Joe Nelson 35 III
Attributes
The following diagram describes the data structure of an RDBMS.

15. Copyright © 2009, Oracle. All rights reserved.I-15
T_CODE NAME AGE SEMESTER
I1001 Nancy Mathews 27 I
I1002 Catherine 38 II
I1003 Mac Thams 50 IV
I1004 Joe Nelson 35 III
Primary Key
Relation
The following diagram describes the data structure of an RDBMS.

16. Copyright © 2009, Oracle. All rights reserved.I-16
T_CODE NAME AGE SEMESTER
I1001 Nancy Mathews 27 I
I1002 Catherine 38 II
I1003 Mac Thams 50 IV
I1004 Joe Nelson 35 III
DOMAIN
T_CODE
DOMAIN
NAME
DOMAIN
AGE
DOMAIN
SEMESTER
The following diagram describes the data structure of an RDBMS.

17. Copyright © 2009, Oracle. All rights reserved.I-17
Representing missing information:
In RDBMS, missing or unknown information is represented as a NULL
value in a table.
NULL is not the same as space or zero.
Representing relationships in an RDBMS:
A column in one table whose value matches the primary key in some
other table is called a foreign key.
A primary key and a foreign key create a parent-child relationship
between the tables that connect them.

18. Copyright © 2009, Oracle. All rights reserved.I-18
The ER (Entity-Relationship) model:
Views the real world as a collection of objects or entities and the relationship
among them.
It also has a corresponding diagramming technique.
STUDENT COURSE
LEARN
Sno Sname CrsID Crsname
Entities
Relationship
Attributes Attributes

19. Copyright © 2009, Oracle. All rights reserved.I-19
An entity:
Is any object, place, person, or activity about which the data is recorded.
Can be categorized as entity type and entity instance.
In the ER model diagramming technique, entities are named and represented
inside a box.
For example:
TEACHER

20. Copyright © 2009, Oracle. All rights reserved.I-20
Entity can be of the following types:
Dependent entity:
Depends on another entity for existence.
Is also called as a weak entity.
Independent entity:
Does not depend on any other entity for existence.
Is also called as a regular entity.
Independent
Entity
Dependent
Entity

21. Copyright © 2009, Oracle. All rights reserved.I-21
Relationship among entities:
A relationship is depicted as a diamond with the name of the
relationship type.
Relationship
For example:

22. Copyright © 2009, Oracle. All rights reserved.I-22
The following diagram represents the association of an entity with itself.
STUDENT
SEMINAR
An entity associating with
itself can be a student giving
a seminar presentation to
other students.

23. Copyright © 2009, Oracle. All rights reserved.I-23
The following statement has been extracted from a case presented by a
manufacturer regarding the maintenance of their data: “A supplier ships
certain parts”. Identify the entities mentioned in this statement, and their
relationship. Draw a diagram depicting the relationship.
Solution:
Entities: SUPPLIER, PARTS
Relationship: SHIP or SUPPLIES

24. Copyright © 2009, Oracle. All rights reserved.I-24
There are three types of relationships:
One-to-one
One-to-many (or Many-to-one)
Many-to-many

25. Copyright © 2009, Oracle. All rights reserved.I-25
The following diagram represents the one-to-one relationship between
DEPARTMENT and DEPARTMENT HEAD.
For a particular DEPARTMENT there can be only one DEPARTMENT HEAD.

26. Copyright © 2009, Oracle. All rights reserved.I-26
The following diagram represents the many-to-one relationship between
STUDENT and MAJOR.
A STUDENT can MAJOR in only one course, but many STUDENTs can register for a
given MAJOR course.

27. Copyright © 2009, Oracle. All rights reserved.I-27
The following diagram represents the many-to-many relationship between
STUDENT and COURSE.
A STUDENT can take many COURSEs and many STUDENTs can register for a given COURSE.

28. Copyright © 2009, Oracle. All rights reserved.I-28
What do the following ER diagrams represent?
Solution:
Many students can work on many projects.
Many employees belong to only one department.

29. Copyright © 2009, Oracle. All rights reserved.I-29
Consider the following statement of a manufacturing company:
“A supplier supplies certain parts. A particular part is not necessarily supplied
by only one supplier. No supplier supplies only a single part.” What type of
relationship is this? Draw a diagram to depict the relationship.
Solution:
Many-to-many relationship

30. Copyright © 2009, Oracle. All rights reserved.I-30
An attribute:
Is a property of a given entity.
Is depicted as ellipses, labeled with the name of the property.
The following diagram shows the various attributes of the entity, STUDENT.

31. Copyright © 2009, Oracle. All rights reserved.I-31
A manufacturer needs to maintain the following details about the supplier:
Name
Address
Credit status
Assigned code number
Draw a diagram to depict this information.
Solution:

32. Copyright © 2009, Oracle. All rights reserved.I-32
A subtype:
Is a subset of another entity.
Is always dependent on the supertype for its existence.
The attributes of a supertype apply to all of its subtypes.

33. Copyright © 2009, Oracle. All rights reserved.I-33
The following diagram shows the relationship between supertype
(PATIENT) and subtypes (INPATIENT and OUTPATIENT).
PATIENT
ID
Name
Addr
OUTPATIENTINPATIENT
VstDateRoomNo BedNo

34. Copyright © 2009, Oracle. All rights reserved.I-34
Map an ER diagram to a table
The following components play an important part while mapping an
ER diagram to table:
oRegular entities
oAttributes
oRelationships
oWeak entities
oSubtypes and supertypes

35. Copyright © 2009, Oracle. All rights reserved.I-35
Regular entities:
They can exist alone, independent of any other entity.
They are the “building blocks” of the database.
Each regular entity maps to a table.
For example, STUDENT and BOOKS are two separate entities in the
following ER diagram.
STUDENT BOOKSISSUES

36. Copyright © 2009, Oracle. All rights reserved.I-36
Attributes:
Each property or attribute shown in the ER diagram maps to an attribute
in the appropriate table.
In the following ER diagram, STUDENT and BOOKS individually has
different attributes.
Entity becomes the table and the
attributes of the entity become
columns of the table.

37. Copyright © 2009, Oracle. All rights reserved.I-37
Relationships:
There are the following types of relationships:
One-to-One
One-to-Many
Many-to-Many

38. Copyright © 2009, Oracle. All rights reserved.I-38
One-to-one relationship:
One instance of an entity can relate to only one instance of the related entity.
If frequent queries require data from
the two tables, then it is better to
merge the two tables to improve
query performance.
For example:

39. Copyright © 2009, Oracle. All rights reserved.I-39
One-to-many relationship:
One instance of an entity can relate to more than one instance of the related entity.
Mapped to the
DEPARTMENT
table
Mapped to the
EMPLOYEE
table
For example:

40. Copyright © 2009, Oracle. All rights reserved.I-40
Many-to-many relationship:
Many instances of an entity can relate to more than one instance of the related
entity.
The primary keys, ROLL_NO and CODE of
the STUDENT and BOOKS table will act as
the foreign keys in the ISSUE table.
Mapped to the
STUDENT
table
Mapped to
the BOOKS
table
Mapped to
the ISSUES
table
For example:

41. Copyright © 2009, Oracle. All rights reserved.I-41
For example, a user wants to know which book was issued to a particular
student and on which date. You can use the foreign keys in the ISSUE table to
make a join between all the three tables.
ROLL_NO NAME CODE DESC ISSUE_DATE RETURN_DATE
What is the primary key of the ISSUE table? There are two possibilities. One
possibility is to take a combination of the two foreign keys (ROLL_NO and CODE)
The resulting key is known as Composite Key.
Another possibility is to create a new attribute for the primary key, for example
ISSUENUM.
ISSUENUM ROLL_NO CODE ISSUE_DATE RETURN_DATE

42. Copyright © 2009, Oracle. All rights reserved.I-42
Weak entity:
It is an entity whose existence depends on some other entity.
The EMPLOYEEADDRESS
entity can be mapped to a
separate table.
ADDRESSID EMPNO CITY
For example:

43. Copyright © 2009, Oracle. All rights reserved.I-43
Subtypes and Supertypes:
A subtype is a subset of another entity.
A subtype is always dependent on supertype for its existence.
The primary key of the supertype creates a link between the
supertype and subtypes.

44. Copyright © 2009, Oracle. All rights reserved.I-44
For example:
Each subtype and
supertype maps to a
separate table.
Supertype
Subtypes

45. Copyright © 2009, Oracle. All rights reserved.I-45
An RDBMS identifies and locates rows by value.
Relational systems require keys that can uniquely identify the rows of a table.
The various types of keys used in an RDBMS are:
oPrimary
oForeign
oCandidate
oAlternate
oComposite
Keys

46. Copyright © 2009, Oracle. All rights reserved.I-46
Any attribute (or set of attributes) that uniquely identifies a row in a
table is a candidate for the primary key. Such an attribute is called a
candidate key.
One of the candidate keys is chosen to be the primary key, based on
the familiarity and greater usage.
Any attribute that is a candidate for the primary key but is not the
primary key is called the alternate key.
When the key that uniquely identifies the rows of the table is made up
of more than one attribute, it is called a composite key.
Keys (Contd)

47. Copyright © 2009, Oracle. All rights reserved.I-47
Consider the STUDENT table, as shown in the following diagram.
STUDENT# and REGN# are
both individually unique in every
row.
If STUDENT# is chosen as the
primary key, then REGN# is the
alternate key.
Candidate keys
Primary keyAlternate key

48. Copyright © 2009, Oracle. All rights reserved.I-48
Keys can be simple or composite. A Simple key is composed of a
singe attribute.
A composite key, on the other hand, comprises two or more attributes.
ACCNUM TRANSDATE TRANSTIME AMOUNT DESCRIPTION
A1101 01/02/200112:30 P.M1000.00Deposit
A1102 01/02/200112:30 P.M2000.00Deposit
A1101 02/02/200110:30 P.M3000.00Deposit
In the preceding table, assume that only one transaction can take
place for a given account number at a time.
We can select ACCNUM, TRANSDATE, and TRANSTIME as the
composite key.

49. Copyright © 2009, Oracle. All rights reserved.I-49
Consider the table, WORKING HOURS of an employee, as shown in the
following diagram.
EMPLID PROJECTID HOURS
E309 P675 18
E453 P908 5
E334 P876 20
Composite key
Each row can be uniquely identified by a
composite key composed of EMPLID and
PROJECTID.