Relational model introduction .pptx

• Relational model
• Key concepts – Tables, Tuple, Attribute, Relation Schema ,Degree,
Cardinality, Attribute domain .
Relational Integrity constraints :
1. Domain constraints - (NULL, CHECK, UNIQUE)
2. Key constraints – (Entity integrity)
3. Referential integrity constraints
• Relational Algebra:
Selection – return rows that meet some condition
Projection – return column values
Union
Intersection
Difference
Cross product

• Relational model is an example of implementation model or
representational model.
• The relational model represents database as a collection of relations.
• A relation is a table of values, and each row in the table represents a
collection of related data values.
• In the relational model terminology a row is called a tuple, a column
header is called an attribute.
• The data type describing the types of values that can appear in each
column is called a domain.

1. Tables – In the Relational model the, relations are saved in the table
format. It is stored along with its entities. A table has two properties
rows and columns. Rows represent records and columns represent
attributes.
2. Tuple – It is nothing but a single row of a table, which contains a single
record.
3. Attribute: Each column in a Table. Attributes are the properties which
define a relation. e.g., Student_Rollno, NAME,etc.
4. Relation Schema: A relation schema represents the name of the
relation with its attributes.

5. Degree: The total number of attributes which in the relation is called
the degree of the relation.
6. Cardinality: Total number of rows present in the Table.
7. Attribute domain – Every attribute has some pre-defined value and
scope which is known as attribute domain.

Relational Integrity constraints
• Relational Integrity constraints is referred to conditions which must be
present for a valid relation.
• These integrity constraints are derived from the rules in the mini-
world that the database represents.
• There are many types of integrity constraints.
• Constraints on the Relational database management system is mostly
divided into three main categories are:
1. Domain constraints - (NULL, CHECK, UNIQUE)
2. Key constraints – (Entity integrity)
3. Referential integrity constraints

1. Domain Constraints - NULL
• Domain constraints can be violated if an attribute value is not appearing in
the corresponding domain or it is not of the appropriate data type.
• Domain constraints specify that within each tuple, and the value of each
attribute must be unique.
• This is specified as data types which include standard data types integers,
real numbers, characters, Booleans, variable length strings, etc.
• Eg:
Create table Students
(Student_id NUMBER not null,
Student_name varchar(30),
marks NUMBER
)

2. Domain Constraint - Check:
• Usedto check the values of attributes.
• Eg: Let us say we have a results of class of students. Now, the school
decides that only the students with marks greater than 35% will be
declared qualified in the current class.only those students results are
published.
• So, for this kind of constraint application, the Domain Constraint –
Check is used.
Create table Students
(Student_id NUMBER not null,
Student_name varchar(30),
marks NUMBER check(marks > 35))

• 3.Domain Constraints -UNIQUE

• Entity Integrity Constraint states that , every table must have primary
key and the Primary key must not be null.
• Primary Key Constraint:
• A primary key is a column or a set of columns that uniquely identifies
each row in a table.
• The primary key constraint ensures that the values in the specified
columns are unique and not NULL.
• There can be only one primary key in a table.

• A foreign key is a column or a set of columns in a table that refers to
the primary key of another table.
• It establishes a relationship between the two tables, enforcing
referential integrity.
• The foreign key constraint ensures that values in the foreign key
column(s) match values in the referenced primary key column(s).

Relational algebra
• Union
• Intersection
• Difference

• Selection – return rows that meet some condition
• Projection – return column values
• Union
• Cross product
• Difference
• Other operators can be defined in terms of basic operators

Projection
Project name and gpa of all students in S1:
name, gpa(S1)
S1
Sid name gpa
50000 Dave 3.3
53666 Jones 3.4
53688 Smith 3.2
53650 Smith 3.8
53831 Madayan 1.8
53832 Guldu 2.0
name gpa
Dave 3.3
Jones 3.4
Smith 3.2
Smith 3.8
Madayan 1.8
Guldu 2.0

Selection
Select students with gpa higher than 3.3 from S1:
σgpa>3.3(S1)
S1
sid name gpa
50000 Dave 3.3
53666 Jones 3.4
53688 Smith 3.2
53650 Smith 3.8
53831 Madayan 1.8
53832 Guldu 2.0
sid name gpa
53666 Jones 3.4
53650 Smith 3.8

Combine Selection and Projection
• Project name and gpa of students in S1 with gpa
higher than 3.3:
name,gpa(σgpa>3.3(S1))
Sid name gpa
50000 Dave 3.3
53666 Jones 3.4
53688 Smith 3.2
53650 Smith 3.8
53831 Madayan 1.8
53832 Guldu 2.0
name gpa
Jones 3.4
Smith 3.8

Example: Intersection
sid name gpa
50000 Dave 3.3
53666 Jones 3.4
53688 Smith 3.2
53650 Smith 3.8
53831 Madayan 1.8
53832 Guldu 2.0
sid name gpa
53666 Jones 3.4
53688 Smith 3.2
53700 Tom 3.5
53777 Jerry 2.8
53832 Guldu 2.0
S1 S2
S1  S2 =
sid name gpa
53666 Jones 3.4
53688 Smith 3.2
53832 Guldu 2.0

Joins
• Combine information from two or more tables
• Example: students enrolled in courses:
S1 S1.sid=E.studidE
Sid name gpa
50000 Dave 3.3
53666 Jones 3.4
53688 Smith 3.2
53650 Smith 3.8
53831 Madayan 1.8
53832 Guldu 2.0
cid grade studid
Carnatic101 C 53831
Reggae203 B 53832
Topology112 A 53650
History 105 B 53666
S1
E

Joins
sid name gpa cid grade studid
53666 Jones 3.4 History105 B 53666
53650 Smith 3.8 Topology112 A 53650
53831 Madayan 1.8 Carnatic101 C 53831
53832 Guldu 2.0 Reggae203 B 53832
Sid name gpa
50000 Dave 3.3
53666 Jones 3.4
53688 Smith 3.2
53650 Smith 3.8
53831 Madayan 1.8
53832 Guldu 2.0
cid grade studid
Carnatic101 C 53831
Reggae203 B 53832
Topology112 A 53650
History 105 B 53666
S1
E

Relational model introduction .pptx

More Related Content

Similar to Relational model introduction .pptx

More from sivamathi12

Recently uploaded

Relational model introduction .pptx