RELATIONAL MODEL RELATIONAL MODEL Relational database is comprised of a collection of tables and each table is assigned a unique name Tables that contain data are sometimes called as relations in a relational database model The column heads for a table are described as relation schema A Table can consist several rows and columns An individual column is called as an attribute and the rows are technically referred to as tuples
USES OF RDBMS Relational data model produces the primary data model One of the highly used data models Reduces complexity Simplifies the task of programmers
Sample relationship Model: Student table with 3 columns and four records. Stu_Id
Stu_Name
Stu_Age
111
Ashish
23
123
Saurav
22
169
Lester
24
234
Lou
26
Database Schema and Database Instance Design of a database is called the schema. Schema is of three types: Physical schema, logical schema and view schema. MAHESH V, BCA Dept., JU
Page 1
RELATIONAL MODEL
Keys
Alternate Key : Out of all candidate keys, only one gets selected as primary key,
remaining keys are known as alternate or secondary keys. A super key is a set or one of more columns (attributes) to uniquely identify rows in a table. How candidate key is different from super key? Answer is simple – Candidate keys are selected from the set of super keys, the only thing we take care while selecting candidate key is: It should not have any redundant attribute. That’s the reason they are also termed as minimal super key. Let’s take an example to understand this: Employee table Emp_SSN
Emp_Number
Emp_Name
123456789
226
Steve
999999321
227
Ajeet
888997212
228
Chaitanya
777778888
229
Robert
Candidate Keys:
As I stated above, they are the minimal super keys with no redundant attributes. {Emp_SSN} {Emp_Number} Only these two sets are candidate keys as all other sets are having redundant attributes that are not necessary for unique identification. MAHESH V, BCA Dept., JU
Page 2
RELATIONAL MODEL Super keys:
{Emp_SSN, Emp_Number} {Emp_SSN, Emp_Name} {Emp_SSN, Emp_Number, Emp_Name} {Emp_Number, Emp_Name}
All of the above sets are able to uniquely identify rows of the employee table. Primary key: Primary key is being selected from the sets of candidate keys by database designer. So Either {Emp_SSN} or {Emp_Number} can be the primary key.
Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference between tables. Example: In the below example the Stu_Id column in Course_enrollment table is a foreign key as it points to the primary key of the Student table. Course_enrollment table: Course_Id
Stu_Id
C01
101
C02
102
C03
101
C05
102
C06
103
C07
102
Student table: Stu_Id
Stu_Name
Stu_Age
101
Chaitanya
22
102
Arya
26
103
Bran
25
Out of all candidate keys, only one gets selected as primary key, remaining keys are known as alternative or secondary keys. MAHESH V, BCA Dept., JU
Page 3
RELATIONAL MODEL For example: Consider the below table Emp_Id
Emp_Number
Emp_Name
E01
2264
Steve
E22
2278
Ajeet
E23
2288
Chaitanya
E45
2290
Robert
There are two candidate keys in above table: {Emp_Id} {Emp_Number} Since we have selected Emp_Id as primary key, the remaining key Emp_Number would be called alternative or secondary key.
Datatypes and Data Constraints
Character Datatypes : Character datatype is a datatype which stores alphanumeric or character data in strings, with byte values equivalent to the character encoding scheme, usually called a code page or a character setCHAR is fixed character datatype
VARCHAR2 is variable character datatypes NCHAR and NVARCHAR2 datatypes are same as CHAR and VARCHAR except that they are used to store Unicode characters LONG datatypes is a bigger version of VARCHAR2
Numeric Datatypes : Numeric datatypes stores zero, positive, negative, fixed & floating point numbers, infinity and NAN (values that are the undefined results of an operation or ‘not a number’). MAHESH V, BCA Dept., JU
Page 4
RELATIONAL MODEL It includes:
NUMBER datatype
DATE datatypes : Oracle database has its own internal format to store date that is DDMM-YYYY format. Ex: ’01-mar-1995’. LOB Datatypes : LOB (Large Objects) datatype allows you to store and manipulate large blocks of unstructured data or information in binary or character format. The unstructured data includes graphic images, text, audio and video clips. It includes:
BLOB(Binary Large Object) datatype CLOB(Character Large Object) and NCLOB(National Character Large Object) datatypes BFILE datatype(Used to store link to an external binary file)
RAW and LONG RAW Datatypes: RAW and LONGRAW datatypes are the datatypes used for data or information that is not to be interpreted by Oracle database. LONG RAW is used to store documents, sound, graphics or arrays of binary data. ROWID and UROWID Datatypes : ROWID datatypes is used to store the address of each and every row in a database. ROWIDs are classified into physical rowid and logical rowid. UROWID or the Universal Rowid is a single datatype that supports both physical and logical rowids along with the rowids of foreign tables which are not Ora cle based.
MAHESH V, BCA Dept., JU
Page 5
RELATIONAL MODEL Data Constraints Constraints are the rules enforced on the data columns of a table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. Constraints could be either on a column level or a table level. The column level constraints are applied only to one column, whereas the table level constraints are applied to the whole table. Types: NOT NULL Constraint : Ensures that a column cannot have NULL value. By default, a column can hold NULL values. If you do not want a column to have a NULL value, then you need to define such a constraint on this column specifying that NULL is now not allowed for that column. Example : For example, the following SQL query creates a new table called CUSTOMERS and adds five columns, three of which, are ID NAME and AGE, In this we specify not to accept NULLs : s If CUSTOMERS table has already been created, then to add a NOT NULL constraint to the SALARY column in Oracle and MySQL, you would write a query like the one that is shown in the following code block. ALTER TABLE CUSTOMERS MODIFY SALARY DECIMAL (18, 2) NOT NULL; DEFAULT Constraint− Provides a default value for a column when none is specified. The DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value. Example : For example, the following SQL creates a new table called CUSTOMERS and adds five columns. Here, the SALARY column is set to 5000.00 by default, so in case the IN SERT INTO statement does not provide a value for this column, then by default this column would be set to 5000.00. CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2) DEFAULT 5000.00, PRIMARY KEY (ID) );
MAHESH V, BCA Dept., JU
Page 6
RELATIONAL MODEL
UNIQUE Constraint : Ensures that all values in a column are different.
The UNIQUE Constraint prevents two records from having identical values in a column. In the CUSTOMERS table, for example, you might want to prevent two or more people from having an identical age. Example : For example, the following SQL query creates a new table called CUSTOMERS and adds five columns. Here, the AGE column is set to UNIQUE, so that you cannot have two records with the same age. CREATE TABLE CUSTOMERS( ID number(10) NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL UNIQUE, ADDRESS CHAR (25) , SALARY number (18, 2), PRIMARY KEY (ID) ); PRIMARY Key : Uniquely identifies each row/record in a database table. A primary key is a field in a table which uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values. To create a PRIMARY KEY constraint on the "ID" column when the CUSTOMERS table already exists, use the following SQL syntax − ALTER TABLE CUSTOMER ADD PRIMARY KEY (ID); FOREIGN Key : Uniquely identifies a row/record in any of the given database table. A foreign key is a key used to link two tables together. This is sometimes also called as a referencing key. Example: book table create table book ( name varchar2(20), id number(2) primary key ); author table create table author ( section varchar2(20), id number(2), constraint mykey foreign key(id) references author(id) );
CHECK Constraint : The CHECK constraint ensures that all the values in a column satisfies certain conditions. The CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and isn't entered into the table.
MAHESH V, BCA Dept., JU
Page 7
RELATIONAL MODEL Example : For example, the following program creates a new table called CUSTOMERS and adds five columns. Here, we add a CHECK with AGE column, so that you cannot have any CUSTOMER who is below 18 years. CREATE TABLE CUSTOMERS( ID number NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL CHECK (AGE >= 18), ADDRESS varchar2 (25) , SALARY NUMBER(18, 2), PRIMARY KEY (ID) );
Fundamental Operations
Book_Id
Title
Author
Price
Year
Article_Id
MAHESH V, BCA Dept., JU
ATitle
Author
AYear
APrice
Page 8
RELATIONAL MODEL Unary Fundamental Operations 1. Select Operation ( ) The select operation selects tuples from a relation, based on the given condition. It is represented by a lowercase Greek sigma (σ) symbol, with the predicate shown as a subscript. The notation for select operation is pr Where, σ is a selection predicate r is a relation , p is selection condition which may use connecters or Boolean conditions like AND (∧), OR (∨) and NOT (¬) and allow comparisons using the relational operators like =, ≠, ≥, <, >, ≤ in the selection predicate. Notation: pr Example: Title=”Database”(Books) 2. Project Operation ( ) The project operation projects a column or attributes that satisfy a given predicate. It is denoted by capital Greek letter pi (π) in which the attributes to be copied appear as subscripts. The notation for select operation is A1, A2, .., An (r) Where A1, A2, …An are attribute names of relation r. Duplicate rows are eliminated automatically since relation is a set. Notation A1, A2, .., An (r)Example: Title, author(Books) 3. Rename operation ( ) The rename operation allows user to rename the output relations that are obtained as the results of relational algebra and which do not have a name. It is denoted by a small Greek letter rho (ρ). The notation is ρx (E) Where the result of the expression E is saved with the name x Example : ρNew1(
E=πBookId,
Author(Books)
Binary Fundamental Operations
1. Union operation (U)- executes binary union between two compatible relations. Notation: r ∪ s Example: author (Books) ∪ author (Articles) 2. Set Difference operation ( )- finds the tuples that are present in one relation but not in the other relation. Notation: r – s Example: author (Books) author(Articles) 3. Cartesian Product operation ( ) - combines the information from two different relations into one. MAHESH V, BCA Dept., JU
Page 9
RELATIONAL MODEL Notation: r × s Example: author = ‘iNurture’ (Books Articles)
Logical Operators
Operator
Description
Examples
and
Called the logical AND operator. If both the operands are true then condition becomes true.
(A and B) is false.
or
Called the logical OR Operator. If any of the two operands is true then condition becomes true.
(A or B) is true.
not
Called the logical NOT Operator. Used to reverse the logical state of its operand. If a condition is true then Logical NOT operator will make it false.
not (A and B) is true.
Oracle Functions:
1. Single row functions a) Numeric Functions: Numeric functions are used to perform operations on numbers. They accept numeric values as input and return numeric values as output. Few of the Numeric functions are: Function Return Value Name ABS (x) Absolute value of the number 'x' CEIL (x) Integer value that is Greater than or equal to the number 'x' FLOOR (x) Integer value that is Less than or equal to the number 'x' TRUNC (x, y) Truncates value of number 'x' up to 'y' decimal places ROUND (x, y) Rounded off value of the number 'x' up to the number 'y' decimal places
MAHESH V, BCA Dept., JU
Page 10
RELATIONAL MODEL b) Character or Text Functions: Character or text functions are used to manipulate text strings. They accept strings or characters as input and can return both character and number values as output. Few of the character or text functions are as given below: Function Name Return Value LOWER (string_value) All the letters in 'string_value' is converted to lowercase. UPPER (string_value) All the letters in 'string_value' is converted to uppercase. INITCAP (string_value) All the letters in 'string_value' is converted to mixed case. LTRIM (string_value, All occurrences of 'trim_text' is removed from the left trim_text) of 'string_value'. RTRIM (string_value, All occurrences of 'trim_text' is removed from the right trim_text) of 'string_value' . TRIM (trim_text FROM All occurrences of 'trim_text' from the left and right string_value) of 'string_value' , 'trim_text' can also be only one character long . SUBSTR (string_value, Returns 'n' number of characters from 'string_value' starting from m, n) the 'm' position. LENGTH (string_value) Number of characters in 'string_value' in returned. c) Date Functions: These are functions that take values that are of datatype DATE as input and return values of datatypes DATE, except for the MONTHS_BETWEEN function, which returns a number as output. Few date functions are as given below. Function Name Return Value ADD_MONTHS (date, n) Returns a date value after adding 'n' months to the date 'x'. MONTHS_BETWEEN Returns the number of months between dates x1 and x2. (x1, x2) Returns the date 'x' rounded off to the nearest century, year, ROUND (x, month, date, hour, minute, or second as specified by date_format) the 'date_format'. Returns the date 'x' lesser than or equal to the nearest century, TRUNC (x, date_format) year, month, date, hour, minute, or second as specified by the 'date_format'. NEXT_DAY (x, Returns the next date of the 'week_day' on or after the week_day) date 'x' occurs. It is used to determine the number of days remaining in a month LAST_DAY (x) from the date 'x' specified. Ex: 1. Select MONTHS_BETWEEN ('16-Sep-81', '16-Dec-81') from dual; Select last_day ('1-jun-2009') from dual;
MAHESH V, BCA Dept., JU
Page 11
RELATIONAL MODEL 2. Group Functions
Group functions are built-in SQL functions that operate on groups of rows and return one value for the entire group. These functions are: COUNT, MAX, MIN, AVG, SUM, DISTINCT SQL COUNT (): This function returns the number of rows in the table that satisfies the condition specified in the WHERE condition. If the WHERE condition is not specified, then the query returns the total number of rows in the table. For Example: If you want the number of employees in a particular department, the query would be: SELECT COUNT (*) FROM employee WHERE dept = 'Electronics'; If you want the total number of employees in all the department, the query would take the form: SELECT COUNT (*) FROM employee; SQL DISTINCT(): This function is used to select the distinct rows. For Example: If you want to select all distinct department names from employee table, the query would be: SELECT DISTINCT dept FROM employee; To get the count of employees with unique name, the query would be, SELECT COUNT (DISTINCT name) FROM employee; SQL MAX(): This function is used to get the maximum value from a column. To get the maximum salary drawn by an employee, the query would be, SELECT MAX (salary) FROM employee; SQL MIN(): This function is used to get the minimum value from a column. To get the minimum salary drawn by an employee, he query would be, SELECT MIN (salary) FROM employee; SQL AVG(): This function is used to get the average value of a numeric column. To get the average salary, the query would be, SELECT AVG (salary) FROM employee; SQL SUM(): This function is used to get the sum of a numeric column To get the total salary given out to the employees, SELECT SUM (salary) FROM employee;
MAHESH V, BCA Dept., JU
Page 12
RELATIONAL MODEL Grouping data from tables in SQL or Clauses used in Oracle Database:
CONSTRAINT
UPDATE FROM WHERE ORDER BY
GROUP BY HAVING ORDER BY CLAUSE: It is used to sort the data in ascending or descending order, based on one or more columns. Some databases sort the query results in an ascending order by default. Syntax: SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC]; You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort that column should be in the column-list. SQL> SELECT * FROM CUSTOMERS ORDER BY NAME, SALARY; GROUP BY CLAUSE : is used in collaboration with the SELECT statement to arrange identical data into groups. This GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause. Syntax: SELECT column1, column2 FROM table_name WHERE [ conditions ] GROUP BY column1, column2 : Ex:1. select ename, count(COMM) from emp group by ENAME: From the above query we will come to know who are all the employees getting commission from the emp table of scott schema. HAVING Clause: it enables you to specify conditions that filter which group results appear in the results.
MAHESH V, BCA Dept., JU
Page 13
RELATIONAL MODEL The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause. Syntax: ELECT column1, column2 FROM table1, table2 WHERE [ conditions ] GROUP BY column1, column2 HAVING [ conditions ] ORDER BY column1, column2 Ex: 1. select ename, max(sal) from emp group by ename having max(sal)>960; 2. SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS GROUP BY age HAVING COUNT(age) >= 2; Basic Structure of SQL Expression:
Basic structure of an SQL expression consists of select, from and where clauses. select clause lists attributes to be copied - corresponds to relational algebra project. from clause corresponds to Cartesian product - lists relations to be used. where clause corresponds to selection predicate in relational algebra. Typical query has the form: select from where P where each
represents an attribute, each
MAHESH V, BCA Dept., JU
a relation, and P is a predicate.
Page 14
