The R elational Model

1. The Relational Model

2. The Relational Model • Introduction • Introduced by Ted Coddat IBM Research in 1970 • The relational model represents data in the form of table. • Main concept of this model is mathematical relation ie the mapping between sets • Relation is some kind of association or link between sets

3. The Relational Model • Introduction • Relational database is collection of relations • Relation represents a Table. • Contains different columns • Each column with a Name • Several different rows • Each row corresponds one particular record • Each row is independent of other rows

4. The Relational Model • Introduction • Eg : first row :- details of Adithya • second row :- details of Aninth Kumar .. • Each row in the table represented the collection of related data values called (instances of the relation) • Relation in the case is table with 3 different column rollno,name & date of regisrtation • Instance of this relation is one of these rows • Each row in a relation is called Tuple • Each column is called attribute

5. The Relational Model • A relational database schema defines: • R(A1,A2,A3,..An) • RName of the relation • A1,A2,..An Each Ai is Attribute • Degree of relation no of attribute in a relation • R(A1,A2,A3,..An) is a set of tuples[t1,t2,t3…tm]

6. The Relational Model • A relational database schema defines: • 1) Names of tables in the database, • 2) The columns of each table, i.e., the column name and the data types of the column entries, • 3) Integrity constraints, i.e., conditions that data entered into the tables is required to satisfy.

7. The Relational Model • Example • DEPT: information about departments. • The table with three columns • DEPTNO ,DNAME, LOC

8. The Relational Model • Example • DEPTNOhas data type NUMERIC(2), i.e., the column can hold two-digit integer entries −99 . . . 99. • An integrity constraint can be used to exclude negative department numbers. • DNAMEhas type VARCHAR(14), i.e., the entries are character strings of variable length of up to 14 characters. • LOC has type VARCHAR(13).

9. The Relational Model • A relational database state defines for each table with a set of rows (tuples). • In the current state, table DEPT has four rows. • The relational model does not define any particular order of the rows (e.g., first row, second row). • Each row specifies values for each column of the table.

10. The Relational Model

11. The Relational Model • A relation schema specifies the domain of each column in the relation. • These domain constraints in the schema specify an important condition that the instance of the relation to satisfy: • The domain of a field is essentially the type of that field,and restricts the values that can appear in the field.

12. The Relational Model • The degree, of a relation is the number of fields. • The cardinality of a relation is the number of tuplesin the table. • In the above example DEPT degree of the relation is 3 • And • cardinality is 4

13. The Relational Model Creating and Modifying Relations Using SQL

14. The Relational Model • Creating and Modifying Relations Using SQL • The SQL language standard uses to creation, deletion, and modification of tables is called the Data Definition Language (DDL).

15. The Relational Model • Creating and Modifying Relations Using SQL • The CREATE TABLE statement is used to define a new table. • To create the Students relation, can use the following statement: • CREATE TABLE Students ( sid char(20),name char(30), login char(20), age int, gpa number(10,2);

16. The Relational Model • Creating and Modifying Relations Using SQL • Tuples are inserted ,using the INSERT command. • We can insert a single tuple into the Students table as follows: • INSERT INTO Students (sid, name, login, age, gpa) VALUES (53688, 'Smith', 'smith@ee', 18, 3.2); • We can optionally omit the list of column names in the INTO clause and list the values in the appropriate order

17. The Relational Model • Creating and Modifying Relations Using SQL • We can delete tuples using the DELETEcommand • DELETE FROM WHERE Students name = 'Smith‘; • We can modify the column values in an existing row using the UPDATE command. • UPDATE Students SET age = age + 1, gpa = gpa – 1 WHERE S.sid = 53688;

18. The Relational Model INTEGRITY CONSTRAINTS OVER RELATIONS

19. The Relational Model • INTEGRITY CONSTRAINTS OVER RELATIONS • DBMS must prevent the entry of incorrect information. • An integrity constraint(Ie) is a condition specified on a database schema and restricts the data that can be stored in an instance of the database. • A DBMS enforces integrity constraints, in that it permits only legal instances to be stored in the database.

20. The Relational Model • INTEGRITY CONSTRAINTS OVER RELATIONS • Key Constrains • A relation is defined as a set of tuples • All the tuples in a relation must be distinct. • No two tuples can be same combination of values for all their attributes • Key is the way to identify a tuple in a relation

21. The Relational Model • INTEGRITY CONSTRAINTS OVER RELATIONS • Key Constrains • Candidate key • Primary Key • Alternate key • Super key • Foreign key

22. The Relational Model • INTEGRITY CONSTRAINTS OVER RELATIONS • Key Constrains • Candidate key • Set of attributes that uniquely identifies a row • In the student table • Candidate key :- AddmNo can uniquely identify row • There may be more than one candidate keys • It can be a composite key • IeYear,Rollno can be also be another candidate key

23. The Relational Model • INTEGRITY CONSTRAINTS OVER RELATIONS • Key Constrains • Primary Key • One of the candidate key • Candidate keys are considered as candidates for primary key position. • Candidate key that contains least number of attributes is used as primary key • In the example, AdmNo is used as primary key, • Primary key does not have duplicate values

24. The Relational Model • INTEGRITY CONSTRAINTS OVER RELATIONS • Key Constrains • Alternate key • Part of candidate key, which is not a primary key • Eg: EMPLOYEE(Name, Issurance_No, Emp_id) • Combination of {Issurance_No, Emp_id} is candidate key • If choosing Issurance_No as primary key, then Emp_id is treated as Alternate key

25. The Relational Model • INTEGRITY CONSTRAINTS OVER RELATIONS • Key Constrains • Super key • Combination of fields, used to identify each record in a table • Eg: table EMPLOYEE (emp_id,Name,Job, dept_id) • Using these fields, possible super keys are • 1) {emp_id ,Name} • 2) {emp_id ,Name, Job} • 3) {emp_id ,Name, Job, dept_id}

26. The Relational Model • INTEGRITY CONSTRAINTS OVER RELATIONS • Key Constrains • Foreign key • Used to accomplish link between two tables • Foreign key used to point primary key in other table • Eg: two tables PERSON(P_ID,NAME,..) & ORDER(ORDER_ID, P_ID..) • P_ID is primary key in PERSON table • Same P_ID in the ORDER table as foreign key , this P_ID is pointing to the primary key of the PERSON table

27. The Relational Model • INTEGRITY CONSTRAINTS OVER RELATIONS • Specifying Key Constraints in SQL • NOT NULL Constraint: Ensures that a column cannot have NULL value. • DEFAULT Constraint: Provides a default value for a column when none is specified. • UNIQUE Constraint: Ensures that all values in a column are different. • PRIMARY Key: Uniquely identified each rows/records in a database table. • FOREIGN Key: Uniquely identified a rows/records in any another table. • CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.

28. The Relational Model • Specifying Key Constraints in SQL • NOT NULL Constraint: Ensures that a column cannot have NULL value. • Example: • The following SQL creates a new table called TAB1 and adds 3 columns, 2 of which, ID and NAME, specify not to accept NULLs: • CREATE TABLE TAB1( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT); • If the table has already been created, then to add a NOT NULL constraint to AGE column • ALTER TABLE TAB1 MODIFY AGE INT NOT NULL;

29. The Relational Model • Specifying Key Constraints in SQL • DEFAULT Constraint: Provides a default value for a column when none is specified. • Example: • following SQL creates a new table called TAB1 and adds 3 columns. • SALARY column is set to 5000.00 by default, so in case INSERT INTO statement does not provide a value for this column, then by default this column would be set to 5000.00. • CREATE TABLE TAB1( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, SALARY DECIMAL(10,2) DEFAULT 5000.00);

30. The Relational Model • Specifying Key Constraints in SQL • UNIQUE Constraint: Ensures that all values in a column are different. • Example: • following SQL creates a new table called TAB1 and adds 3 columns. • AGE column is set to UNIQUE, so that can not have two records with same age: • CREATE TABLE TAB1( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL UNIQUE);

31. The Relational Model • Specifying Key Constraints in SQL • PRIMARY Key: Uniquely identified each rows/records in a database table. • Primary keys must contain unique values. • A primary key column cannot have NULL values. • A table can have only one primary key, which may consist of single or multiple fields. • When multiple fields are used as a primary key, they are called a composite key.

32. The Relational Model • Specifying Key Constraints in SQL • Here is the syntax to define ID attribute as a primary key in a TAB1 table. • CREATE TABLE TAB1( ID INT NOT NULL, NAME VARCHAR(20)NOT NULL, AGE INT NOT NULL,PRIMARY KEY (ID));

33. The Relational Model • Specifying Key Constraints in SQL • FOREIGN Key: Uniquely identified a rows/records in any another table. • A foreign key is a key used to link two tables together. • This is sometimes called a referencing key. • The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.

34. The Relational Model • Specifying Key Constraints in SQL • Example: • Consider the structure of the two tables as follows: • TAB1 table: • CREATE TABLE TAB1( ID INT NOT NULL, NAME VARCHAR(20)NOT NULL, AGE INT NOT NULL,PRIMARY KEY (ID)); • TAB2 table: • CREATE TABLE TAB2( ID INT NOT NULL, SID INT REFERENCES TAB1(ID), SALARY DECIMAL(10,2)NOT NULL,PRIMARY KEY (ID));

35. The Relational Model • Specifying Key Constraints in SQL • CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions. • Example: • following SQL creates a new table called TAB1 and adds 3 columns. • CHECK with AGE column, so that you can not have any VALUE below 10 years: • CREATE TABLE TAB1( ID INT NOT NULL, NAME CHAR(20) NOT NULL, AGE INT NOT NULL CHECK(AGE>10),PRIMARY KEY (ID));

36. The Relational Model ENFORCING INTEGRITY CONSTRAINTS (ICs)

37. The Relational Model ENFORCING INTEGRITY CONSTRAINTS (ICs) • ICs are specified when a relation is created and enforced when a relation is modified. • If an insert, delete, or update command causes a violation, it is rejected. • violation is generally checked at the end of each SQL statement execution • The following insertion violates the primary key constraint because there is already a tuple with • the s'id 53688, and it will be rejected by the DBMS: • INSERT INTO Students (sid, name, login, age, gpa) VALUES (53688, 'Mike', 'mike@ee', 17,3.4)

38. The Relational Model ENFORCING INTEGRITY CONSTRAINTS (ICs) • The following insertion violates the constraint that the primary key cannot contain null: • INSERT INTO Students (sid, name, login, age, gpa) VALUES (null, 'Mike', 'mike@ee', 17,3.4) • Deletion does not cause a violation of primary key or unique constraints. • update can cause violations, sirnilar to an insertion: • UPDATE Students SET sid = 50000 WHERE sid = 53688 • This update violates the primary key constraint because there is already a tuple with sid 50000.

39. The Relational Model ENFORCING INTEGRITY CONSTRAINTS (ICs) • foreign key constraints is more complex because SQL sometimes tries to rectify a foreign key constraint violation instead of simply rejecting the change. • We discuss the referential integrity enforcement steps taken by the DBMS in terms of our Enrolled and Students tables, with the foreign key constraint that Enrolled. • sid is a reference to (the primary key of) Students. • Deletions of Enrolled tuples do not violate referential integrity, but insertions of Enrolled tuples could. The following insertion is illegal because there is no Students tuple with sid 51111: • INSERT INTO Enrolled (cid, grade, studid) VALUES ('Hindi101', 'B', 51111)

40. The Relational Model ENFORCING INTEGRITY CONSTRAINTS (ICs) • On the other hand, insertions of Students tuples do not violate referential • integrity, and deletions of Students tuples could cause violations. Further, • updates on either Enrolled or Students that change the studid (respectively, • sid) value could potentially violate referential integrity.

41. The Relational Model QUERYING RELATIONAL DATA SQL is the most popular commercial query language for a relational DBMS. • We can retrieve rows corresponding to students who are younger than 18 with the following SQL query: • SELECT * FROM Students S WHERE S.age < 18 • The symbol ‘*’ means that we retain all fields of selected tuples in the result. • The condition S. age < 18 in the WHERE clause specifies that we want to select only tuples in which the age field has a value less than 18.

42. The Relational Model QUERYING RELATIONAL DATA • In addition to selecting a subset of tuples, a query can extract a subset of the fields of each selected tuple. • We can compute the names and logins of students who are younger than 18 with the following query: • SELECT S.name, S.login FROM Students S WHERE S.age < 18 • it is obtained by applying the selection to the instance 81 of followed by removing unwanted fields.

43. The Relational Model QUERYING RELATIONAL DATA