dbms ii mca-ch4-relational model-2013

Aruna Devi C (DSCASC)

1

Data Base Management System[DBMS]

Chapter 4Unit III

Relational Model


Relational Data Model

The first commercial implementations of the relational model became available in the early 1980s, such as the SQL/DS system on the MVS operating system by IBM and the Oracle DBMS.

Since then, the model has been implemented in a large number of commercial systems.

Current popular relational DBMSs (RDBMSs) include DB2 and Informix Dynamic Server (from IBM), Oracle and Rdb (from Oracle), Sybase DBMS (from Sybase) and SQLServer and Access (from Microsoft).

In addition, several open source systems, such as MySQL and PostgreSQL, are available.


3

The relational model represents the database as a collection of relations.

The relational data model represents data in the form of the table.

Relational data consist of three components as follows: Data structure: Data are organized in the form of tables with

rows and columns.

Data Manipulation: Powerful operations (using SQL) are used to

manipulate data, store data in the relation.

Data integrity: Facilities are included to specify business rules that maintain

the integrity of data when they are manipulated.

Relational Model Concepts


4

Relation/Table


5

Attribute is a column represent the attributes of a real-time object.

When a relation is thought of a table of values, Each row represent a collection of related data values.

Tuple is a row, which represents a collection of related data values.

Domains: A domain D is a set of atomic values.

The set of allowed values for each attribute is called the domain of the Attribute.

The data type describing the types of values that can appear in each column is called a domain.

i.e., Domain represent set of all permissible values for that column.

Example: Ph-no. – Mobile or landline Name Emp Age – Value between 15 to 80

Tuples, Attribute, Domain, Relations

Relational Model Concepts


Relation:

A relation schema R, denoted by R(A1, A2, ...,An), is made up of a relation name R and a list of attributes, A1, A2, ..., An.

Each attribute Ai is the name of a role played by some domain D in the relation schema R.

D is called the domain of Ai and is denoted by dom(Ai).

A relation schema is used to describe a relation; R is called the name of this relation.

Example:

STUDENT(Name: string, Ssn: string, Home_phone: string, Address: string,

Office_phone: string, Age: integer, Gpa: real)

Tuples, Attribute, Domain, Relations (Cont.,)


7

A1, A2, …, An are attributes

R = (A1, A2, …, An ) is a relation schema

Example:

Customer_schema = (customer_name, customer_ street, customer_ city)

r(R) is a relation on the relation schema R

Example: customer (Customer_schema)

An element t of r is a tuple, represented by a row in a table

Relation Schema

JonesSmithCurryLindsay

customer_name

MainNorthNorthPark

customer_street

HarrisonRyeRyePittsfield

customer_city

customer

tuples(or rows)

attributes(or columns)


DEFINITION SUMMARY

Informal Terms Formal Terms

Table Relation

Column Attribute/Domain

Row Tuple

Values in a column Domain

Table Definition Schema of a Relation

Populated Table Extension


Relational Model Constraintsand Relational Database Schemas

Constraints on databases can generally be divided into three main categories:

1. Constraints that are inherent in the data model. We call these inherent model-based constraints or implicit constraints. For example, the constraint that a relation cannot have duplicate tuples is

an inherent constraint.

2. Constraints that can be directly expressed in schemas of the data model, typically by specifying them in the DDL (data definition language).

We call these schema-based constraints or explicit constraints.

3. Constraints that cannot be directly expressed in the schemas of the data model, and hence must be expressed and enforced by the application programs.

We call these application-based or semantic constraints or business rules.


Domain Constraint Key/Entity Constraint Referential Integrity Constraint Operational Constraint

11

Types of Constraints:

Relational Model Constraintsand Relational Database Schemas (Cont.,)


Domain Constraint:

All the values that appear in a column of a relation must be taken from the same domain. Domain means data type, size and the allowable range.

The data types include standard numeric data types for integers and real numbers.

There are also characters, Booleans, fixed-length strings and variable length strings, date, time, timestamp and so on.

12

Relational Model Constraintsand Relational Database Schemas (Cont.,)


Key / Entity Constraint: Key is a field that uniquely identifies the records, tables or data.

Different types of keys: Primary Key Foreign Key Unique key Super key Candidate key

Primary Key:A primary key is one or more columns in a table .

Every relation has a primary key, and the primary key values are “unique and not null”.

Eg: Reg.NoUnique key :

Unique key is one or more columns in a table. It can contain Null value.

Foreign Key: Foreign key represent relationship between tables.

13

Relational Model Constraints and Relational Database Schemas (Cont.,)


Super key : A super key is a set of one or more attributes that, taken

collectively.Eg: {Reg. no} or {Reg.No, Name}

Candidate key : A relation schema has more than one key, each of the keys is

called a candidate key. All the key which satisfies the condition of primary key can be a

candidate key. One of the candidate keys can be chosen to be the primary key.

Ex: {cname, SSN} is a super key,

but the attribute SSN is a candidate key.

{Reg.No} and {ph.No} are two candidate key in student table.

14



Operational Constraint:

It is designed to enforce the business rules namely operational constraints to the database.

Example: Min balance of Bank account is Rs. 1000/- ….etc.

Referential Integrity Constraint:

It is specified between two relations and is used to maintain the consistency among tuples in the two relations.

Ensures that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation.

15



Referential Integrity:

Ensures that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation.

Example: If “Perryridge” is a branch name appearing in one of the tuples in the account relation, then there exists a tuple in the branch relation for branch “Perryridge”.

Primary and candidate keys and foreign keys can be specified as part of the SQL create table statement:

The primary key clause lists attributes that comprise the primary key.

The foreign key clause lists the attributes that comprise the foreign key and the name of the relation referenced by the foreign key. By default, a foreign key references the primary key attributes of the referenced table.

16



Constraints on a Single Relation

Not Null Primary key Unique Check (P ), where P is a predicate

18

Not Null Constraint A constraint specifies whether null values are or are not

permitted. The constraint is specified as NOT NULL.

Declaring branch_name for branch table as not null

branch_name char(15) not null

Declaring the domain salary to be not null salary numeric(12,2) not null



One possible database state for the COMPANY relational database schema.


Database Schema for Company


Other Types of Constraints:The preceding integrity constraints are included in the data

definition language (DDL).

Semantic Integrity Constraints:

semantic integrity constraints, which may have to be specified and enforced on a relational database.

Examples: 1. The salary of an employee should not exceed the salary of the

employee’s supervisor. 2. “The max. no. of hours per employee for all projects he or she

works on is 56 hrs per week”.

- A constraint specification language may have to be used to express these

SQL allows triggers and ASSERTIONS to allow for some of these.


-----------------------------


Update Operations, Transactions,and Dealing with Constraint

Violations

The operations of the relational model can be categorized into retrievals and updates.

A relational algebra expression forms a new relation after applying a number of algebraic operators to an existing set of relations; its main use is for querying a database to retrieve information.

There are three basic operations that can change the states of relations in the database:

Insert, Delete, and Update (or Modify)


The Insert Operation:

The Insert operation provides a list of attribute values for a new tuple t that is to be inserted into a relation R.

Insert can violate any of the four types of constraints:

Domain constraints can be violated if an attribute value is given that does not appear in the corresponding domain.

Key constraints can be violated if a key value in the new tuple t already exists in another tuple in the relation r(R).

Entity integrity can be violated if any part of the primary key of the new tuple t is NULL.

Referential integrity can be violated if the value of any foreign key in t refers to a tuple that does not exist in the referenced relation.

Update Operations, Transactions,and Dealing with Constraint Violations (Cont.,)


The Insert Operation: Example

Insert <‘Cecilia’, ‘F’, ‘Kolonsky’, NULL, ‘1960-04-05’, ‘6357 Windy Lane, Katy, TX’, F, 28000, NULL, 4> into EMPLOYEE.

Result: This insertion violates the entity integrity constraint (NULL for the

primary key Ssn), so it is rejected.

Insert <‘Alicia’, ‘J’, ‘Zelaya’, ‘999887777’, ‘1960-04-05’, ‘6357 Windy Lane,

Katy, TX’, F, 28000, ‘987654321’, 4> into EMPLOYEE.

Result: This insertion violates the key constraint because another tuple with

the same Ssn value already exists in the EMPLOYEE relation, and so it is rejected.

Example


Example

The Insert Operation: Example (cont.,)

Insert <‘Cecilia’, ‘F’, ‘Kolonsky’, ‘677678989’, ‘1960-04-05’, ‘6357 Windswept, Katy, TX’, F, 28000, ‘987654321’, 7> into EMPLOYEE

Result: This insertion violates the referential integrity constraint specified on Dno in EMPLOYEE because no corresponding referenced tuple exists in DEPARTMENT with Dnumber = 7.

Insert <‘Cecilia’, ‘F’, ‘Kolonsky’, ‘677678989’, ‘1960-04-05’, ‘6357 Windy Lane, Katy, TX’, F, 28000, NULL, 4> into EMPLOYEE.

Result: This insertion satisfies all constraints, so it is acceptable.


The Delete Operation: The Delete operation can violate only referential integrity. This occurs if the tuple being deleted is referenced by foreign keys from

other tuples in the database.

Example: Delete the WORKS_ON tuple with Essn = ‘999887777’ and Pno = 10

Result: This deletion is acceptable and deletes exactly one tuple.

Delete the EMPLOYEE tuple with Ssn = ‘999887777’

Result: This deletion is not acceptable, because there are tuples in WORKS_ON that refer to this tuple. Hence, if the tuple in EMPLOYEE is deleted, referential integrity violations will result.


Company Database


The Update Operation:

The Update (or Modify) operation is used to change the values of one or more attributes in a tuple (or tuples) of some relation R.

Example:

Update the salary of the EMPLOYEE tuple with Ssn = ‘999887777’ to 28000

Result: Acceptable.

Update the Dno of the EMPLOYEE tuple with Ssn = ‘999887777’ to 1

Result: Acceptable.

Update the Dno of the EMPLOYEE tuple with Ssn = ‘999887777’ to 7

Result: Unacceptable, because it violates referential integrity.


Company Database


The Transaction Concept

A transaction is an executing program that includes some database operations, such as reading from the database, or applying insertions, deletions, or updates to the database.

For example, a transaction to apply a bank withdrawal will typically read the user account record, check if there is a sufficient balance, and then update the record by the withdrawal amount.


EXAMPLES


Aruna Devi.C (DSCASC) 31

branch (branch_name, branch_city, assets)

customer (customer_name, customer_street, customer_city)

loan (loan_number, branch_name, amount)

borrower (customer_name, loan_number)

account (account_number, branch_name, balance)

depositor (customer_name, account_number)

Database Schema for Bank


Aruna Devi.C (DSCASC) 32

Schema Diagram for a Banking Enterprise


Banking Enterprise

dbms ii mca-ch4-relational model-2013

Software

customer customer

relation r

relation schema example

data integrity

integrity of data

relation schema r example

data manipulation

data type