The Relational Model The Relational Model Introduction Introduced

The Relational Model

The Relational Model • Introduction • Introduced by Ted Codd at 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

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

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

The Relational Model • A relational database schema defines: • • R(A 1, A 2, A 3, . . An) R Name of the relation A 1, A 2, . . An Each Ai is Attribute Degree of relation no of attribute in a relation • R(A 1, A 2, A 3, . . An) is a set of tuples [t 1, t 2, t 3…tm]

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.

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

The Relational Model • Example • DEPTNO has 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. • DNAME has type VARCHAR(14), i. e. , the entries are character strings of variable length of up to 14 characters. • LOC has type VARCHAR(13).

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.

The Relational Model

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.

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

The Relational Model Creating and Modifying Relations Using SQL

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

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

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

The Relational Model • Creating and Modifying Relations Using SQL • We can delete tuples using the DELETE command • 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;

The Relational Model INTEGRITY CONSTRAINTS OVER RELATIONS

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.

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 • • •

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

The Relational Model • INTEGRITY CONSTRAINTS OVER RELATIONS Adm No Rollno Year Name • Key Constrains • • 1001 1002 1003 1004 1005 1 2 2 3 1 2001 2003 2001 2002 Aji Saji Raji Biju Saju Dist Kollam TVM PTA Candidate key Set of attributes that uniquely identifies a row In the student table Candidate key : - Addm. No can uniquely identify row There may be more than one candidate keys It can be a composite key Ie Year, Rollno can be also be another candidate key

The Relational Model • INTEGRITY CONSTRAINTS OVER RELATIONS • Key Constrains Adm No Rollno Year Name 1001 1002 1003 1004 1005 1 2 2 3 1 2001 2003 2001 2002 Aji Saji Raji Biju Saju Dist Kollam TVM PTA • 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, Adm. No is used as primary key, • Primary key does not have duplicate values

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 • • •

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}

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 • • •

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.

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 TAB 1 and adds 3 columns, 2 of which, ID and NAME, specify not to accept NULLs: • CREATE TABLE TAB 1( 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 TAB 1 MODIFY AGE INT NOT NULL;

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 TAB 1 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. • CREATE TABLE TAB 1( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, SALARY DECIMAL(10, 2) DEFAULT 5000. 00);

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 TAB 1 and adds 3 columns. • AGE column is set to UNIQUE, so that can not have two records with same age: • CREATE TABLE TAB 1( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL UNIQUE);

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.

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

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.

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

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 TAB 1 and adds 3 columns. • CHECK with AGE column, so that you can not have any VALUE below 10 years: • CREATE TABLE TAB 1( ID INT NOT NULL, NAME CHAR(20) NOT NULL, AGE INT NOT NULL CHECK(AGE>10), PRIMARY KEY (ID));

The Relational Model ENFORCING INTEGRITY CONSTRAINTS (ICs)

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)

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.

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 ('Hindi 101', 'B', 51111)

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.

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.

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.

The Relational Model QUERYING RELATIONAL DATA