UNIT-2
1 Introduction to the Relational Model
Relational Model represents how data is stored in Relational Databases. A relational database stores data in the form
of relations (tables). Consider a relation STUDENT with attributes ROLL_NO, NAME, ADDRESS, PHONE and
AGE shown in Table.
STUDENT
ROLL_NO NAME ADDRESS PHONE AGE
1 RAM DELHI 9455123451 18
2 RAMESH GURGAON 9652431543 18
3 SUJIT ROHTAK 9156253131 20
4 SURESH DELHI 18
1.1 Important Terminologies
Attribute: Attributes are the properties that define a relation. e.g.; ROLL_NO, NAME
Relation Schema: A relation schema represents name of the relation with its attributes. e.g.; STUDENT
(ROLL_NO, NAME, ADDRESS, PHONE and AGE) is relation schema for STUDENT. If a schema has more
than 1 relation, it is called Relational Schema.
Tuple: Each row in the relation is known as tuple. The above relation contains 4 tuples, one of which is shown
as:
1 RAM DELHI 9455123451 18
Relation Instance: The set of tuples of a relation at a particular instance of time is called as relation instance.
Table 1 shows the relation instance of STUDENT at a particular time. It can change whenever there is insertion,
deletion or updation in the database.
Degree: The number of attributes in the relation is known as degree of the relation. The STUDENT relation
defined above has degree 5.
Cardinality: The number of tuples in a relation is known as cardinality. The STUDENT relation defined above
has cardinality 4.
Column: Column represents the set of values for a particular attribute. The column ROLL_NO is extracted from
relation STUDENT.
ROLL_NO
1
2
3
4
NULL Values: The value which is not known or unavailable is called NULL value. It is represented by blank
space. e.g.; PHONE of STUDENT having ROLL_NO 4 is NULL.
1.2 Constraints in Relational Model (Follow heading 10)
2 Advanced SQL programming
https://learnsql.com/blog/what-is-advanced-sql/
https://advanced-sql-programming.blogspot.com/2010/01/advanced-sql-programming.html
Follow these links as it is a programming language and there is no limit so prepared accordingly
3 Advanced Transaction Processing
https://ssyu.im.ncnu.edu.tw/course/CSDB/chap20.pdf
https://www.slideshare.net/koolkampus/ch24
Follow these links
4 Relational model Integrity
, Every relation has some conditions that must hold for it to be a valid relation. These conditions are called Relational
Integrity Constraints. There are three main integrity constraints – (Follow heading 10)
5 Data Replication
Data Replication is the process of storing data in more than one site or node. It is useful in improving the availability
of data. It is simply copying data from a database from one server to another server so that all the users can share the
same data without any inconsistency. The result is a distributed database in which users can access data relevant to
their tasks without interfering with the work of others. Data replication encompasses duplication of transactions on
an ongoing basis, so that the replicate is in a consistently updated state and synchronized with the source. However
in data replication data is available at different locations, but a particular relation has to reside at only one location.
There can be full replication, in which the whole database is stored at every site. There can also be partial replication,
in which some frequently used fragment of the database are replicated and others are not replicated.
Types of Data Replication –
5.1 Transactional Replication – In Transactional replication users receive full initial copies of the database and then
receive updates as data changes. Data is copied in real time from the publisher to the receiving database (subscriber)
in the same order as they occur with the publisher therefore in this type of replication, transactional consistency is
guaranteed. Transactional replication is typically used in server-to-server environments. It does not simply copy the
data changes, but rather consistently and accurately replicates each change.
5.2 Snapshot Replication – Snapshot replication distributes data exactly as it appears at a specific moment in time
does not monitor for updates to the data. The entire snapshot is generated and sent to Users. Snapshot replication is
generally used when data changes are infrequent. It is bit slower than transactional because on each attempt it moves
multiple records from one end to the other end. Snapshot replication is a good way to perform initial synchronization
between the publisher and the subscriber.
5.3 Merge Replication – Data from two or more databases is combined into a single database. Merge replication is
the most complex type of replication because it allows both publisher and subscriber to independently make changes
to the database. Merge replication is typically used in server-to-client environments. It allows changes to be sent from
one publisher to multiple subscribers.
6 Security considerations
Database security refers to the use of the DBMS features and other related measures to comply with the security
requirements of the organization. From the DBA’s point of view, security measures should be implemented to protect
the DBMS against service degradation and the database against loss, corruption, or mishandling.
To protect the DBMS against service degradation there are certain minimum recommended security safeguards. For
example:
Change default system passwords.
Change default installation paths.
Apply the latest patches.
Secure installation folders with proper access rights.
Make sure only required services are running.
Set up auditing logs.
Set up session logging.
Require session encryption.
Protecting the data in the database is a function of authorization management. Authorization management defines
procedures to protect and guarantee database security and integrity. Those procedures include, but are not limited to,
user access management, view definition, DBMS access control, and DBMS usage monitoring.
Define each user to the database: This is achieved at the operating system level and at the DBMS level. At the
operating system level, the DBA can request the creation of a logon user ID that allows the end user to log on to the
computer system. At the DBMS level, the DBA can either create a different user ID or employ the same user ID to
authorize the end user to access the DBMS.
Assign passwords to each user: This, too, can be done at both operating system and DBMS levels. The database
passwords can be assigned with predetermined expiration dates. The use of expiration dates enables the DBA to
1 Introduction to the Relational Model
Relational Model represents how data is stored in Relational Databases. A relational database stores data in the form
of relations (tables). Consider a relation STUDENT with attributes ROLL_NO, NAME, ADDRESS, PHONE and
AGE shown in Table.
STUDENT
ROLL_NO NAME ADDRESS PHONE AGE
1 RAM DELHI 9455123451 18
2 RAMESH GURGAON 9652431543 18
3 SUJIT ROHTAK 9156253131 20
4 SURESH DELHI 18
1.1 Important Terminologies
Attribute: Attributes are the properties that define a relation. e.g.; ROLL_NO, NAME
Relation Schema: A relation schema represents name of the relation with its attributes. e.g.; STUDENT
(ROLL_NO, NAME, ADDRESS, PHONE and AGE) is relation schema for STUDENT. If a schema has more
than 1 relation, it is called Relational Schema.
Tuple: Each row in the relation is known as tuple. The above relation contains 4 tuples, one of which is shown
as:
1 RAM DELHI 9455123451 18
Relation Instance: The set of tuples of a relation at a particular instance of time is called as relation instance.
Table 1 shows the relation instance of STUDENT at a particular time. It can change whenever there is insertion,
deletion or updation in the database.
Degree: The number of attributes in the relation is known as degree of the relation. The STUDENT relation
defined above has degree 5.
Cardinality: The number of tuples in a relation is known as cardinality. The STUDENT relation defined above
has cardinality 4.
Column: Column represents the set of values for a particular attribute. The column ROLL_NO is extracted from
relation STUDENT.
ROLL_NO
1
2
3
4
NULL Values: The value which is not known or unavailable is called NULL value. It is represented by blank
space. e.g.; PHONE of STUDENT having ROLL_NO 4 is NULL.
1.2 Constraints in Relational Model (Follow heading 10)
2 Advanced SQL programming
https://learnsql.com/blog/what-is-advanced-sql/
https://advanced-sql-programming.blogspot.com/2010/01/advanced-sql-programming.html
Follow these links as it is a programming language and there is no limit so prepared accordingly
3 Advanced Transaction Processing
https://ssyu.im.ncnu.edu.tw/course/CSDB/chap20.pdf
https://www.slideshare.net/koolkampus/ch24
Follow these links
4 Relational model Integrity
, Every relation has some conditions that must hold for it to be a valid relation. These conditions are called Relational
Integrity Constraints. There are three main integrity constraints – (Follow heading 10)
5 Data Replication
Data Replication is the process of storing data in more than one site or node. It is useful in improving the availability
of data. It is simply copying data from a database from one server to another server so that all the users can share the
same data without any inconsistency. The result is a distributed database in which users can access data relevant to
their tasks without interfering with the work of others. Data replication encompasses duplication of transactions on
an ongoing basis, so that the replicate is in a consistently updated state and synchronized with the source. However
in data replication data is available at different locations, but a particular relation has to reside at only one location.
There can be full replication, in which the whole database is stored at every site. There can also be partial replication,
in which some frequently used fragment of the database are replicated and others are not replicated.
Types of Data Replication –
5.1 Transactional Replication – In Transactional replication users receive full initial copies of the database and then
receive updates as data changes. Data is copied in real time from the publisher to the receiving database (subscriber)
in the same order as they occur with the publisher therefore in this type of replication, transactional consistency is
guaranteed. Transactional replication is typically used in server-to-server environments. It does not simply copy the
data changes, but rather consistently and accurately replicates each change.
5.2 Snapshot Replication – Snapshot replication distributes data exactly as it appears at a specific moment in time
does not monitor for updates to the data. The entire snapshot is generated and sent to Users. Snapshot replication is
generally used when data changes are infrequent. It is bit slower than transactional because on each attempt it moves
multiple records from one end to the other end. Snapshot replication is a good way to perform initial synchronization
between the publisher and the subscriber.
5.3 Merge Replication – Data from two or more databases is combined into a single database. Merge replication is
the most complex type of replication because it allows both publisher and subscriber to independently make changes
to the database. Merge replication is typically used in server-to-client environments. It allows changes to be sent from
one publisher to multiple subscribers.
6 Security considerations
Database security refers to the use of the DBMS features and other related measures to comply with the security
requirements of the organization. From the DBA’s point of view, security measures should be implemented to protect
the DBMS against service degradation and the database against loss, corruption, or mishandling.
To protect the DBMS against service degradation there are certain minimum recommended security safeguards. For
example:
Change default system passwords.
Change default installation paths.
Apply the latest patches.
Secure installation folders with proper access rights.
Make sure only required services are running.
Set up auditing logs.
Set up session logging.
Require session encryption.
Protecting the data in the database is a function of authorization management. Authorization management defines
procedures to protect and guarantee database security and integrity. Those procedures include, but are not limited to,
user access management, view definition, DBMS access control, and DBMS usage monitoring.
Define each user to the database: This is achieved at the operating system level and at the DBMS level. At the
operating system level, the DBA can request the creation of a logon user ID that allows the end user to log on to the
computer system. At the DBMS level, the DBA can either create a different user ID or employ the same user ID to
authorize the end user to access the DBMS.
Assign passwords to each user: This, too, can be done at both operating system and DBMS levels. The database
passwords can be assigned with predetermined expiration dates. The use of expiration dates enables the DBA to
