Module3: Relational Model & Relational Algebra Relational Model ‘A relational database is collectively ct ombination of data structures, operations and integrity constraints. storage and retrieval The relational model represents how data Is stored in Relational Oat Ustabare stores data in the form of relations (tables). Relational model can represent as a table with c¢ srumns and rows. Each row is known as a tuple: Each table of the columa has a name or attribute. labases. A relational Relation seit elations are a logical structure which is a collection of tables co) horizontal Rows and vertical columns. eee eee entaining rows and columns represents entity in relational model (= called az Relation. Characteristics of Relation sisting J A table composed of rows and columns. 2 gach table in a database has its unique table name. 2 eae obs cow tuple) represents 9 single entity occurrence within the entity a All values in a same column mus 5. Each table must have single atti identifies each row. Example of relation: 2 conform to the same format of data. ‘bute or set of attributes that uniquely [Stuc name | Age Branch 2. Attributes Relation has its own properties which de: sscribes that relation(table) such properties Gre known ae attribute. 3. Tuple Characteri tics of Relational Model‘+ This model is called as Relational Model by Dr. Codd because the data is stored in the tables which are having relationships in between them, ‘The whole data of the system is represented as systematic arrangement of data into rows and columns called as relation or table, A table is also form in two-dimensional structure. + At any given row/column position means in every cell in the relation there is one and only one value whiel known as scalar value, © Column represents attribute, and each column has a distinct name + Allvalues entered in the columns are of the same data format. + implements the concept of closure means all operations are performed on an entire relation and result is an entite relation, ‘+ Itsupeorts the operations like data definition, data manipulation and transaction management Advantages of Relational Model 1. Simple Model A Relational Database system is the simplest model, as it does not require complex structuring or querying processes. As the structure is simple, itis sufficient to be handled with simple SQL queries and does not require complex queries to be designed. 2. Data Accuracy In the relational database system, multiple tables can be related to one another using a primary key and foreign key concepts. This makes the data to be non-repetitive. There is no chance for duplication of data. Hence, the relational database's data accuracy is higher than in any other database system. 3. Easy Access to Data In the Relational Database System, there is no pattern or pathway for accessing the data, as other types of databases can be accessed only by navigating through a tree or a hierarchical model. Anyone who accesses the data can query any table in the relational database. One can combine all or any number of related tables to fetch the required data using join queries and conditional statements. The user can effortlessly recover the relevant data by modifying the resulting data based on the values from any column or number of columns. One can select the desired columns to incorporate into the outcome, allowing for displaying only appropriate data, 4, Data Integrity Data integrity is a crucial characteristic of the Relational Database system. Data integrity aids in making sure of the relational database's other significant characteristics like Ease of use, precision, and data stability. 5. Flexibility The flexible structure of a relational database system allows it to accommodate constantly shifting requirements, enabling it todevel up anc-expand for bigger lengths. This facilitates theincreasing incoming amount of data and the update and deletes wherever required. A Data Analyst can insert, update or delete tables, columns, or individual data in the given database system promptly and efficiently, to meet the business needs. There is supposedly no boundary on the number of rows, columns, or tables a relational database can hold. 6. Normalization ‘A normalization process provides a set of regulations, characteristics, and purposes for the database structure and evaluation of a relational database model. Normalization aims at illustrating multiple levels of breaking down the data. One should accomplish any level of normalization on the same level before advancing to the subsequent levels. To ensure the normalization of a relational database model, it should meet the requirements of the third normalization form. Normalization offers reassurance that the database plan is extra strong and reliable. 7. High Security Users can designate tables as confidential or non-confidential in a relational database system. When a data analyst tries to log in with a username and password, the database can set boundaries for their level of access by providing admission only to the tables they are allowed to work on, depending on their access level. Properties of a Relational Model The relational databases consist of the following properties: «Every row is unique + Allof the values present in a column hold the same data type + Values are atomic «The columns sequence is not significant © The rows sequence is not significant + The name of every column is unique Relation Schema in DBMS Relation schema defines the design and structure of the relation like it consists of the relation name, set of attributes/field names/column names. every attribute would have an associated domain.professors: [>| professor id= tirst_name last_name students enrolis_in "studentiid-fe—{ student_ia first_name course_id con name last_name aie description house_name § required_textbook class_year required_equipment Fig, 3.3.1: Schema of School Management System Keys Refer the theory given in Module 2 Notes. Relational Algebra Relational algebra is a procedural query language. It gives a step by step process to obtain the result of the query. It uses operators to perform queries. 1. Select Operation: © The select operation selects tuples that satisfy a given predicate. © It is denoted by sigma (0). 2 Syntax: ¢ p(t) Where: ois used for selection prediction ris used for relation pis used as a propositional logic formula which may use connectors like: AND OR and NOT. These relational can use as relational operators like =, 4, 2, <. >,For example: LOAN Relation LOAN_NO AMOUNT ee Downtown a7 1000 Redwood La 2000 Perryride L15 1500 Downtown “Lv 1500 Mianus OS La a 500 : Roundhill are ie 900 Perryride L16 1300 Input: 6 BRANCH_NAME="pertyride” (LOAN) Output: BRANCH_NAME : LOAN.NO ce AMOUNT. ae | Pemide a oa : 1500 Perryride “16 1300 2. Project Operation: © This operation shows the list of those attributes that we wish to appear in the result. Rest of the attributes are eliminated from the table.© itis denoted by []. © Syntax: [] A1, A2, An (1) Where A1, A2, A3 is used as an attribute name of relation r. Example: CUSTOMER RELATION | Hays | Main | Harrison pene pe te of » Curry | North Rye \ | | hea - | johnson Alma | Brooklyn ee | Brooks ioe Sees ce Ee Input: [NAME, CITY (CUSTOMER) Output:Hays Harrison Curry : Rye : Johnson " Brooklyn Brooks _ Brooklyn 3. Union Operation: Suppose there are two tuples R and S. The union operation contains all the tuples that are either in R or S or both in R & S. It eliminates the duplicate tuples. It is denoted by u. Synrax: RUS. Aunion operation must hold the following condition: © Rand $ must have the attribute of the same number. ‘© Duplicate tuples are eliminated automatically. Example: DEPOSITOR RELATION Johnson A101 ‘Smith A-121 Mayes | A321 Turner | AT76|. [] CUSTOMER_NAME (BORROW) U [] CUSTOMER_NAME (DEPOSITOR) Output:“smith Hayes Turner | Jones Lindsay : Jackson Cuny Williams Mayes 4. Set Intersection: © Suppose there are two tuples R and S. The set intersection operation contains all tuples that are in both R&S. © tis denoted by intersection 0. o Syntax: RAS Example: Using the above DEPOSITOR table and BORROW table Input: [CUSTOMER _NAME (BORROW) n [] CUSTOMER_NAME (DEPOSITOR) Output:5. Set Difference: «o- Suppos there are two tp thatarein but notin. les Ran. Th etnesetin operation otis al Es «tis denoted by intersection minus (). fo SytaR-S Example: Using the above DDEPOSITOR table and BORROW table Input: [ICUSTOMER_NAME (BORROW) [] CUSTOMER NAME (DEPOSITOR) Output: Jackson “Hayes Wilians cuny 6, Cartesian product «The Cartesian producti used to com! table Its also known a a ross product. ine each rowin one table with each rowin the other -ttis denoted by X Syntax EXDExample: EMPLOYEE Input: EMPLOYEE X DEPARTMENT Output: | Marketing | "Sales1 Smith 2 Harry 2 : Harry 2 Harry 3 John 3 John 3 John 7. Rename Operatio Legal Marketing Sales Legal Marketing Sales Legal The rename operation is used to rename the output relation. It is denoted by rho (0). Example: We can use the rename operator to rename STUDENT relation to STUDENT1. p(STUDENT1, STUDENT) 8. Join Operation A Join operation combines related tuples from different relations, if and only if a given join condition is satisfied. It is denoted by >. inner join Inner Join is a join operation in DBMS that combines two or more table based on related columns and return only rows that have matching values among tables. + It is the most common join. It is based on matched data as per the equality condition. + The equi join uses the comparison operator (=).Syntax: SELECT column_name(s) FROM tablez INNER JOIN table2 ON tablet. coLumn_name table2.coLumn_name; Example: CUSTOMER RELATION. CUSTOMER om PRODUCT oR SELECT * FROM EMPLOYEE INNER JOIN FACT_WORKERS;+ Inner join of two types. © EquiJoin ‘© Natural Join 1. Equi Join Equi Join is a type of Inner join in which we use euivalence(’=’) condition in join condition Example: Table A Column A Column B a a a b Table B Column A Column B A >a A.Column B = B.Column B (8) Result: Column A Column B 2. Natural Join Natural join is a type of inner join in which we not need of any comparison operators. In natural join columns should have the same name and domain. There should be at least one common attribute between two tables. Eample: Table A Number SquareNumber Square 3 9 Table B Number Cube 2 8 3) 7 ApaB Number Square Cube 2 4 8 3 9 27 Outer Join + Outer join is 2 type of join that retrieve matching as well as non-maching records from related tables. + There three types of outer join © Left outer join cc Right outer join 2 Full outer join 1. Left Outer Join {tis also called left join. This type of outer join retrieve all records from left table and retrive maching record from right table. Example: Table A Number SquareNumber Square 3 9 4 16 Table B Number Cube 2 8 3 7 5 75 ADB Result: Number Square Cube 2 4 8 3 9 a 4 16 - 2. Right Outer Join itis also called right join. This typ ‘trive maching record from right table. ¢ of outer join retrieve al records from right table and re Example: Table A and Table B are same as in left outer join AraBNumber Square Cube 2 4 8 3 9 27 5 os 5 3. Full Outer Join In full outer join all the rows from both table are inserted in result table Eaxmple: Table A and Table B are same as in left outer join ADB Result: Number Square Cube 2 4 8 3 9 27 4 16 - 5 - 75 SQL Aggregate Functions 9 SQL aggregation function is used to perform the calculations on multiple rows of a single column of a table. It returns a single value. © It is also used to summarize the data.Types of SQL Aggregation Function SQL. Aggregs Function 4. COUNT FUNCTION = COUNT function is used to Count the number of rows in a database tab! le. It can work on both numeric and non-numeric data types. 5 COUNT function uses the COUNT(*) that returns the count of all the rows in a specified table. COUNT(*) considers duplicate and Null. Syntax COUNT(*) or COUNT( (ALL|DISTINCT] expression } Sample table: PRODUCT_MASTExample: COUNTO SELECT COUNT(*) FROM PRODUCT_MAS?: Example: COUNT with WHERE SELECT COUNT(*) FROM PRODUCT_MAST; 7s 150 30Example: COUNT() with DISTINCT ‘SELECT COUNT(DISTINCT COMPANY) FROM PRODUCT_MAST; Output: Example: COUNT() with GROUP BY SELECT COMPANY, COUNT(*) FROM PRODUCT_MAST GROUP BY COMPANY; Output: cont Example: COUNT() with HAVING SELECT COMPANY, COUNT(*) FROM PRODUCT_MAST GROUP BY COMPANY HAVING COUNT(*)>2; Output: com 5 com 3 2. SUM Function ‘Sum function is used to calculate the sum of all selected columns. It works on numeric fields only. syntax SUMOor SUM( (ALLIDISTINCT] expression ) Example: SUM() SELECT SUM(COsT) FROM PRODUCT_MAST; Output: 670 Example: SUM() with WHERE SELECT SUM(COST) FROM PRODUCT_MAST WHERE QTY>3; Output: 320 Example: SUM() with GROUP BY SELECT SUM(COST) FROM PRODUCT_MAST WHERE QTY>3 GROUP BY COMPANY; Output: Coml 150 Com2 170 Example: SUM() with HAVING SELECT COMPANY, SUM(COST) FROM PRODUCT_MAST GROUP BY COMPANY HAVING SUM(COST)>=170; Output:3. AVG function ‘The AVG function is used to calculate the average value of th returns the average of all non-Null values. e numeric type. AVG function Syntax AVG AVG| [ALLIDISTINCT] expression ) Exampl SELECT AVG(COST) FROM PRODUCT_MAST; Output: 4. MAX Function MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column, Syntax MAXO MAX( ALLIDISTINCT] expression ) Example: SELECT MAX(RATE) FROM PRODUCT_MAST; Output: 305. MIN Function : n MIN function is used to find the minimum value of a certain column, This functio determines the smallest value of all selected values of a column. Syntax MINO or MIN( [ALL|DISTINCT) expression ) Example: SELECT MIN(RATE) FROM PRODUCT MAST; Output: