Relational model introduction .pptx

.

1. 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

2. 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 .

3. 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. Tuple – It is nothing but a single row of a table, which contains a single record. Attribute : Each column in a Table. Attributes are the properties which define a relation. e.g., Student_Rollno , NAME,etc . Relation Schema : A relation schema represents the name of the relation with its attributes.

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

5. 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

6. 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 )

7. 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))

8. 3. Domain Constraints - UNIQUE  

9. 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.

10. 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).

11. Relational algebra Union Intersection Difference

12. 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

13. 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

14. 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

15. 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

16. 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

17. Joins Combine information from two or more tables Example: students enrolled in courses: S1 S1.sid=E.studid E 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

18. 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