Finals Handouts PDF

CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Mana...

CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Week 13 – Handout 1 Declarative Knowledge: Union Clause Group by Clause Having Clause Functional Knowledge: Explain the Union, Group by and Having Clauses. Create database and use the syntax of Union, Group by and Having Clauses. Intended Learning Outcomes (ILO): Apply and use MySQL syntax of Union, Group by and Having Clauses. INTRODUCTION Last topic, we discussed the Limit Clause, Order By as well as Like Operator that can limit and filter the results. Another way to filter is the use of Having Clause. If you want to sort the results or group it, MySQL also provides way to do it as well as if you like to combine two table. On this topic, we will discuss the Group by, Having Clause & Union Operator. MySQL Order By, Group By & Having Clause Group By Clause This clause will aggregate or combine data rows together that have the same values. It will collect all the values of data rows with similar type and display it by showing different types that collected. It is like answering the question “how many employees in each branch”. Group by clause were used together with aggregate function. Syntax: SELECT column1, column2,... FROM tableName WHERE condition GROUP BY columnName; Sample: SELECT COUNT(customerName), membershipStatus FROM tblSales Page | 1 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba GROUP BY membershipStatus; In this command, it will display the total number of customer in table tblSales that was group by its membershipStatus. Having Clause This clause is used to filter result data rows on a Group By Clause. Like WHERE Clause, it is for filtering results but since it cannot filter the aggregated data rows hence the Having Clause were added in MySQL. Syntax: SELECT column1, column2,... FROM tableName WHERE condition GROUP BY columnName HAVING condition; HAVING Clause on COUNT() Sample: SELECT COUNT(customerId) AS totalCustomer FROM tblSales GROUP BY datePurchase HAVING COUNT(CustomerID) > 10; In this command, it will display the total number of customer in table tblSales that was group by its datePurchase with 11 or more total number of customer ONLY. HAVING Clause on SUM() Sample: SELECT SUM(price) AS totalSales FROM tblSales GROUP BY datePurchase Page | 2 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba HAVING SUM(price) > 10000; In this command, it will display the sum of all price in table tblSales that was group by its datePurchase and ONLY greater than 10000 of the totalSales per date Purchase will be displayed. MySQL Union Operator This operator will unite the query result of more than one SELECT statements and will remove duplicate result (see DISTINCT()) provided that: 1. All SELECT statement must have the same selected column number; 2. Column must have same data types; 3. Column order must be the same. Syntax: SELECT column1, column2,... FROM tableName1 UNION SELECT column1, column2,... FROM tableName2 Sample: SELECT itemName FROM tblSales UNION SELECT itemName FROM tblDispose; In this command, it will display the all the unique value of itemName in table tblSales and tblDispose. References: w3schools, “MySQL HAVING Clause”, [Online]. Available: https://www.w3schools.com/mysql/mysql_having.asp. [Accessed: 3-February-2023]. Page | 3 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Week 14 – Handout 1 Declarative Knowledge: Database Normalization 1st Normal Form 2nd Normal Form 3rd Normal Form Functional Knowledge: Explain the Database Normalization. Differentiate the 3 Basic Database Normalization. Intended Learning Outcomes (ILO): Create Tables displaying different Database Normalization. INTRODUCTION Data Normalization is used to eliminates and reduce data redundancy like update, insertion and deletion of data and ensure that all the data has been stored logically. This technique helps to organize data in your database and become more flexible. If data that already exists in multiple locations needs to be modified, it must be changed in exactly the same way everywhere. Database normal forms, rules and specification in SQL: a. First Normal Form b. Second Normal Form c. Third Normal Form FIRST NORMAL FORM(1NF) First Normal Form sets the fundamental rules for database normalization and relates to a single table within a relational database system. Normalization follows three basic steps, each building on the last. The first of these is the first normal form. Rule of the First Normal Form: 1. In every column in the table must be unique that means there is no duplicate rows 2. In every cell must contain only a single value not a lists 3. In every value it should be non-visible that cannot be split down. Example Problem 1: All rows must be uniquely identifiable: Page | 4 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Student Name Ordered Books Jasper Santos Math, English, Science Jessica Lat P.E, Physics Dustin Vinos The Codes Jasper Santos Math, English, Science Note: The table has an identical row and that is the name “Jasper Santos” that have the same ordered books also. Solution 1 Add an order ID as a primary key: Order ID Student Name Ordered Books 1 Jasper Santos Math, English, Science 2 Jessica Lat P.E, Physics 3 Dustin Vinos The Codes 4 Jasper Santos Math, English, Science Note: The only way to solve the problem is to Add an order ID as a primary key to the table to make the values unique and it is no longer an identical row. Example Problem 2: Each cell must contain only a single value: Order ID Student Name Ordered Books 1 Jasper Santos Math, English, Science 2 Jessica Lat P.E, Physics 3 Dustin Vinos The Codes 4 Jasper Santos Math, English, Science Note: The table has multiple values under the order ID number 1 and 4 that belongs to “Jasper Santos” Page | 5 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Solution 2 Create a separate table with Order Items: Order ID Student Name 1 Jasper Santos 2 Jessica Lat 3 Dustin Vinos 4 Jasper Santos Order ID Item 1 Math 1 English 1 Science 2 P.E. 2 Physics 3 The Codes 4 Math 4 English 4 Science Note: Since the table has multiple values you will create a separate table with order items to make it unique. Example Problem 3: All data must be atomic or non-divisible: Order ID Student Name 1 Jasper Santos 2 Jessica Lat 3 Dustin Vinos 4 Jasper Santos Note: The table has a Student Name with a complete name including First and the Last name that is why it makes the data becomes be atomic or non-divisible. Page | 6 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Solution 3 Create a separate column for the First Name and the Last name: Order ID First Name Last Name 1 Jasper Santos 2 Jessica Lat 3 Dustin Vinos 4 Jasper Santos Note: You need to insert and create a column for the first name and the last name to make the table acceptable as data that is atomic or non-divisible. SECOND NORMAL FORM(2NF) The Second Normal Form is said to be a relation in 2NF when it exists in 1NF, while the relation’s every non-prime attribute depends on every candidate key as a whole. That means all non-key attributes are fully functional dependent on the primary key itself. Rule of the Second Normal Form: 1. Database must be in the First Normal Form 2. Non-partial dependencies that mean all attribute must be functional and dependent on the candidate key. Example Problem 1: No Partial Dependency: Student ID Course ID Course Fee Composite Key 1 1 1,000 1 2 200 2 4 300 2 6 5,000 3 5 200 3 6 5,000 Note: This table is not Second Normal Form because the course fee is dependent on the primary key to determine the course fee. Page | 7 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Solution 1 Create two tables for student courses and course fee: Student Courses: Student ID Course ID Composite Key 1 1 1 2 2 4 2 6 3 5 3 6 Courses Fees: Course ID Course Fee 1 1,000 2 200 4 300 6 5,000 5 200 6 5,000 Note: This table is now a Second Normal Form because the single column Primary Key are not functionally dependent on any subset of candidate key relation. Third Normal Form(3NF) The Third Normal Form(3NF) is a relation where it is in 2NF but has no transitive partial dependency. A given relation is said to be in its third normal form when it’s in 2NF but has no transitive dependencies. That means the relation can be stated to be in 3NF when there is no transitive dependency for the non-prime characteristics. Rule of the Third Normal Form: 1. Database must be in the First and Second Normal Form Page | 8 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba 2. All fields must only be determinable by the primary or composite key and not by the other keys or no transitive dependency. Example Problem 1: No transitive Dependency: Composite Key Singing Competition Year Winner Winner’s Date of Birth Tawag Ng Tanghalan 2023 JM Yosores March 16, 1991 The Clash 2022 Mariane Osabel July 7, 1995 Idol Ph 2022 JM Yosores March 16, 1991 The Voice 2022 Darren Espanto April 5, 1998 Note: The problem on this table is that you can determine one of these keys by looking at the other keys and that is Winner’s DOB. Solution 1 Create two tables for Singing Competition Winners and Winners DOBs: Singing Competition Winners Table Singing Competition Year Winner Tawag Ng Tanghalan 2023 JM Yosores The Clash 2022 Mariane Osabel Idol Ph 2022 JM Yosores The Voice 2022 Darren Espanto Winners DOBs Table Winner Winner’s Date of Birth JM Yosores March 16, 1991 Mariane Osabel July 7, 1995 Darren Espanto April 5, 1998 Note: This means that in the Singing Competition Winners Table, the Winner is dependent in the name of the singing competition yearly and cannot workout by just the singing Page | 9 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba competition itself and there is multiple singing competition every year which means you cannot determine the winner buy just looking at the year. References: Javapoint, “Database Normalization”, [Online]. Available: https://www.javatpoint.com/dbms-first-normal-form. [Accessed: 30-January-2023]. learn.microsoft, “Database Normalization Basic”, [Online]. Available: https://learn.microsoft.com/en-us/office/troubleshoot/access/database-normalization- description. [Accessed: 30-January-2023]. youtube, “Learn Learn Scratch Tutorials”, [Online]. Available: https://www.youtube.com/watch?v=J-drts33N8g Page | 10 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Week 15 – Handout 1 Declarative Knowledge: MySQL Joins Left Join Right Join Inner Join Functional Knowledge: Explain the MySQL Joins. Create database and use the syntax of MySQL Joins. Intended Learning Outcomes (ILO): Apply and use syntax of MySQL Joins. INTRODUCTION SQL Join makes it easier to merge many tables to carry out different operations. Data normalization cannot exist without SQL Server Joins since we must keep all data in a single table. Based on a shared column between two or more tables, a JOIN clause is used to merge rows from those tables. SQL Join Example: Consider the following selection from the "Orders" table: Then, consider also the following selection from the "Customers" table: Note: Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column. Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables: Page | 11 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba SQL JOIN Program Code: SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID; Sample output of SQL JOIN: The following are the different types of SQL Join use in databases: 1. (INNER) JOIN: This join will return records that have matching values in both tables. 2. LEFT (OUTER) JOIN: This join will return all records from the left table, and the matched records from the right table 3. RIGHT (OUTER) JOIN: This join will return all records from the right table, and the matched records from the left table SQL INNER JOIN If there is at least one match between the columns, the SQL Inner Join returns the records (or rows) found in both tables. The SQL Server Inner is the default join, so it is optional to use the INNER Keyword. SQL Inner Join Syntax CODE: SELECT Table1.Column(s), Table2.Column(s), FROM Table1 INNER JOIN Page | 12 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Table2 ON Table1.Common_Column = Table2.Common_Column --OR We can Simply Write it as: SELECT Table1. Column(s), Table2. Column(s),f FROM Table1 JOIN Table2 ON Table1.Common_Column = Table2.Common_Column Example 1 SQL Inner Join Select All Columns: The following query will display all the columns present in the Employees and Department tables. SELECT * FROM [Employee] INNER JOIN [Department] ON [Employee].[DepartID] = [Department].[id] Sample Output: Page | 13 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba To avoid the INNER Keyword, SELECT * FROM [Employee] JOIN [Department] ON [Employee].[DepartID] = [Department].[id] Sample Output: Note: If you observe the above screenshot, although we had 15 records in the Employee, and Inner join is displaying 11 records. It is because Department Id for the remaining four records (i., ID number 10, 11, 14, and 15) in the Employee table are NULLS. The [Department ID] column is repeated twice, which is annoying to the user. By selecting individual column names, we can avoid unwanted cols. So, please avoid the SELECT * Statement. Example 2 SQL Inner Join Select Few Columns: This example will place the required ones after the SELECT Statement to avoid unwanted columns in Inner Join. SELECT [FirstName] ,[LastName] ,[DepartmentName] FROM [Employee] INNER JOIN [Department] ON Page | 14 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba [Employee].[DepartID] = [Department].[id] Sample Output: Note: The above Inner Join query will perfectly work as long as the column names from both the tables are different like above. Example 3 SQL Inner Join WHERE Clause: This example will allow us to use the Where Clause to limit the number of rows delivered by it. In this example, we will use that WHERE Clause along with the Inner Join. SELECT Emp.[FirstName] AS [First Name] ,Emp.[LastName] AS [Last Name] ,Dept.[DepartmentName] AS [Department Name] FROM [Employee] AS Emp INNER JOIN [Department] AS Dept ON Emp.[DepartID] = Dept.[id] WHERE Dept.[DepartmentName] = 'Software Developer' OR Dept.[DepartmentName] = 'Sr. Software Developer' Page | 15 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Sample Output: Example 4 SQL Server Inner Join Order By Clause: This example will us to use Order By Clause to rearrange the order of the records. SELECT Emp.[FirstName] AS [First Name] ,Emp.[LastName] AS [Last Name] ,Dept.[DepartmentName] AS [Department Name] FROM [Employee] AS Emp INNER JOIN [Department] AS Dept ON Emp.[DepartID] = Dept.[id] ORDER BY [FirstName] ASC Page | 16 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Sample Output: SQL LEFT JOIN When data from the right table are matched, the SQL Server Left Join returns all of the rows or records that are present in the left table. The illustration of this is shown below. The figure above shows all of the records from Table 1 plus any records from Table 2 that match those in Table 1. The right table's unmatched rows will all have NULL values in them. SQL LEFT Join Syntax CODE: SELECT Table1.Column(s), Table2.Column(s), FROM Table1 LEFT OUTER JOIN Table2 ON Table1.Common_Column = Table2.Common_Column --OR We can Simply Write it as: Page | 17 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba SELECT Table1. Column(s), Table2. Column(s), FROM Table1 LEFT JOIN Table2 ON Table1.Common_Column = Table2.Common_Column Note: The Left join in SQL Server can also be called a Left outer join. So, it is optional to use the Outer Keyword. Example 1 SQL Server Left Join Select All Columns: This example will display all the query that is present in the Employees columns and matching records from Department tables. SELECT * FROM [Employee] LEFT OUTER JOIN [Department] ON [Employee].[DepartID] = [Department].[id] Sample Output: Page | 18 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba To avoid the OUTER Keyword, This is to remove the Outer keyword from the SQL LEFT JOIN, which is optional, and it works well. As you can see, it returns rows from both the tables in the result set. SELECT * FROM [Employee] LEFT JOIN [Department] ON [Employee].[DepartID] = [Department].[id] Sample Output: Note: There are 15 records in the Employee table. And the SQL Left Join is displaying 15 records, but for [DepartID], id, [Department Name], it displays NULL Values for ID numbers 10, 11, 14, and 15. It is because the Department Id for them in the Employee table is NULLS, so there are no matching records in the right table. The [Department ID] column is repeated twice, which is annoying to the user. By selecting individual column names, we can avoid unwanted ones. So, please avoid SELECT * Statements. Page | 19 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Example 2 SQL Server Left Join Select Few Columns: This is to place the required columns after the SELECT Statement to avoid unwanted Server columns in Joins. -- Select Few Columns Example SELECT [FirstName] ,[LastName] ,[DepartmentName] FROM [Employee] LEFT JOIN [Department] ON [Employee].[DepartID] = [Department].[id] Sample Output: Note: This query will work as long as the column names from both tables are different like above. If we have identical Column names in both the tables, it will throw an error. For instance, we are using the above query. But we added id from the department table as an additional column. Page | 20 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba SELECT [FirstName] ,[LastName] ,id ,[DepartmentName] FROM [Employee] LEFT OUTER JOIN [Department] ON [Employee].[DepartID] = [Department].[id] The above code will throw an error: Ambiguous column name id. It is because the id is present in both tables. And the server doesn’t understand which column you are requesting it to retrieve. To resolve the error, forever use the table name before the column name. The following query uses the ALIAS table name before the column names. By this method, we can inform you that we are looking for an id related to the department. We can rewrite the above query as: SELECT Emp.[FirstName] AS [First Name] ,Emp.[LastName] AS [Last Name] ,Dept.id ,Dept.[DepartmentName] AS [Department Name] FROM [Employee] AS Emp LEFT JOIN [Department] AS Dept ON Emp.[DepartID] = Dept.[id] Page | 21 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Sample Output: Note: The SQL Server Left outer Join also permits us to use the Where Clause to limit the number of rows returned by it. Here, we use the WHERE Clause along with this. SELECT Emp.[FirstName] AS [First Name] ,Emp.[LastName] AS [Last Name] ,Dept.[DepartmentName] AS [Department Name] FROM [Employee] AS Emp LEFT JOIN [Department] AS Dept ON Emp.[DepartID] = Dept.[id] WHERE Dept.[DepartmentName] IS NOT NULL Page | 22 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Sample Output: Try this Order By Clause to rearrange the order of the records, SELECT Emp.[FirstName] AS [First Name] ,Emp.[LastName] AS [Last Name] ,Dept.[DepartmentName] AS [Department Name] FROM [Employee] AS Emp LEFT JOIN [Department] AS Dept ON Emp.[DepartID] = Dept.[id] ORDER BY [DepartmentName] ASC Page | 23 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Sample Output: SQL RIGHT JOIN All of the entries that are present in the Right table are returned by the SQL Server Right Join, which also matches rows from the Left table. The right outer join function in SQL Server is also available. Therefore, using the outer keyword is not required. Keep in mind that all of the left table's unmatched rows will contain NULL values. SQL RIGHT Join Syntax CODE: SELECT Table1.Column(s), Table2.Column(s), FROM Table1 RIGHT OUTER JOIN Table2 ON Table1.Common_Column = Table2.Common_Column --OR We can Simply Write it as SELECT Table1. Column(s), Table2. Column(s), FROM Table1 RIGHT JOIN Page | 24 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Table2 ON Table1.Common_Column = Table2.Common_Column Example 1 SQL Right Join Select All Columns: This is an example of a Right Join Query that will display all the columns present in the Department table and matching records from the Employees table. SELECT * FROM [Employee] RIGHT OUTER JOIN [Department] ON [Employee].[DepartID] = [Department].[id] Sample Output: Example 2 SQL Right Join Without Outer keyword: This example allows us an option to use an Outer keyword. To remove the Outer keyword, and work will SQL server RIGHT JOIN. Page | 25 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba SELECT * FROM [Employee] RIGHT OUTER JOIN [Department] ON [Employee].[DepartID] = [Department].[id] Sample Output: Although the Employee table has 15 records, Right join displays 13 records. It is because Department Id for 14th and 15th records in the Employee table are NULLS, so there are no matching records in the right table. If you observe the 8th and 9th records, they display NULL values. Because in the Employee table, there are no matching records for Department Id 3, 4 (Module Lead and Team Lead) in Department table. So they are replaced by NULLS. Note: The [Department ID] column repeats twice, which annoys the user. By selecting individual column names, we can avoid unwanted columns. So, please avoid SELECT * Statements. Page | 26 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Example 3 SQL Right Join Select Few Columns: This example allows us to place the required columns after the SELECT Statement to avoid unwanted columns in Joins. SELECT [FirstName] ,[LastName] ,[DepartmentName] FROM [Employee] RIGHT JOIN [Department] ON [Employee].[DepartID] = [Department].[id] Sample Output: Note: The above Right Join query will excellently work as long as the column names from both Employee and Department tables are different like above. If they have the same Column names, you get an error. To solve the issue used the above right outer join query. However, we added id from the department table as an additional column. SELECT [FirstName] ,[LastName] Page | 27 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba ,id ,[DepartmentName] FROM [Employee] RIGHT OUTER JOIN [Department] ON [Employee].[DepartID] = [Department].[id] Note: The right join throws an error: Ambiguous column name id. It is because the id column is available in both Employee and department tables. And it doesn’t recognize which column you are claiming Sample Output: Note: To resolve the error, practice the table name before the column name. For example, the following right outer join query uses the ALIAS table name before the column names. This will notify the Server that we are looking for the id column belonging to the department table. We can rewrite the earlier SQL Server right outer join query as: SELECT Emp.[FirstName] AS [First Name] ,Emp.[LastName] AS [Last Name] ,Dept.id ,Dept.[DepartmentName] AS [Department Name] FROM [Employee] AS Emp RIGHT JOIN [Department] AS Dept ON Emp.[DepartID] = Dept.[id] Page | 28 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Sample Output: Example 4 SQL Right Join WHERE Clause: This example will allow us to use the Where Clause to restrict the records returned. In this example, we use the WHERE Clause along with the Right Outer Join. SELECT Emp.[FirstName] AS [First Name] ,Emp.[LastName] AS [Last Name] ,Dept.[DepartmentName] AS [Department Name] FROM [Employee] AS Emp RIGHT JOIN [Department] AS Dept ON Emp.[DepartID] = Dept.[id] WHERE Emp.[FirstName] IS NOT NULL Page | 29 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Sample Output: Example 5 SQL Right Join Order By Clause: This example will allow us to use Order By Clause to rearrange the order of the records. SELECT Emp.[FirstName] AS [First Name] ,Emp.[LastName] AS [Last Name] ,Dept.[DepartmentName] AS [Department Name] FROM [Employee] AS Emp RIGHT JOIN [Department] AS Dept ON Emp.[DepartID] = Dept.[id] ORDER BY [FirstName] ASC Page | 30 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Sample Output: References: w3schools, “MySQL SQL JOINS”, [Online]. Available: https://www.w3schools.com/sql/sql_join.asp. [Accessed: 30-January-2023] w3schools, “SQL INNER JOIN”, [Online]. Available: https://www.w3schools.com/sql/sql_join_inner.asp [Accessed: 30-January-2023] edtechbooks, “The Join Clause”, [Online]. Available: https://edtechbooks.org/learning_mysql/the_join_clause [Accessed: 30-January-2023]. tutorialgateway, “MySQL RIGHT JOIN”, [Online]. Available: https://www.tutorialgateway.org/mysql-right-join/ [Accessed: 30-January-2023]. Page | 31 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Week 16 – Handout 1 Declarative Knowledge: MySQL Joins ERD Components ERD using Chen Notation Functional Knowledge: Explain the Entity Relationship Diagram. Explain the Chen Notation in making ERD. Intended Learning Outcomes (ILO): Create ERD sample using Chen Notation. INTRODUCTION Entity Relationship Diagram (ERD) is a diagram that shows the relationship of all entity created in database and explains its logical structure. There are three basic concepts for ERD which uses different symbols: entities which uses rectangles, attributes which uses oval and relationships which uses diamond. ER Diagram Main Components using Chen Notation Symbols Peter Chen is the creator of Chen’s Notation invented in 1976 a detailed way of notation. This notation is one of the first in designing software and still popular in making ERD these days. Entity Entity can be a person, place, object, event or concept that stored in database. Example: Person: Employee Place: Store Object: Machine Event: Sale Concept: Department Page | 32 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Attributes Attributes is a property that describes the entity Example: Entity Attributes Person: Employee Name Place: Store Address Object: Machine Type Event: Hiring Date Concept: Department Work Scope Page | 33 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Page | 34 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Relationship Relationship used to label the association of entities. It is usually used verbs or verb phrases to identify the relation. Relationship uses diamond or rhombus symbol. Example: Student Attends the Course Student Joined the Competition Page | 35 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Optionality of Relationship 1. Mandatory Relationship Student Attends Course 2. Optional Relationship Student Joined Competition Cardinality of Relationship defines the degree or number of attributes of the relationship connecting two entities. It is characterized by “1”, “N” or “M” which located at the end of the relationships. 1. One-to-One (1:1). Each Department can be managed by one employee only and one employee can manage only one department. Department 1 Managed 1 Employee 2. One-to-Many (1:N). Customer can buy many orders, but each order can be bought by one customer only. Customer 1 Bought N Orders 3. Many-to-One (N:1). Many students may enroll to one Program such as BSIT, but one particular student can enroll one Program only. Student N Enrolled 1 Program Page | 36 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba 4. Many-to-Many (M:N). One student may join many student organizations, and one student organization can have more than one student. Student M Joined N Organization Benefits of ERD Deliver a quick look of all your tables connection, and what columns are needed on each table. The ERD can be converted into relational tables which gives the database developer to build the databases faster. database designers can use the created ERD as a blueprint for implementing data in particular software applications. The database designer gets a quick understanding of the information to be included in the database. ERD permits you to interconnect with the database logical structure to users Steps in Creating ERD 1. Identify Entity Student Course Professor 2. Identify Relationship Student Course Professor Assigned Delivers 3. Identify Cardinality Student M N Course Professor Assigned M Delivers N Page | 37 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba 4. Identify Attributes Student_ID Course_ID Employee_ID Student M N Course Professor Assigne M Delivers N d StudentName CourseName ProfName 5. Create the Modern ERD Student M Assigned N Course Delivers Professor Student_ID Course_ID M N Employee_ID StudentName CourseName ProfName References: Peterson, R(2023), “Entity Relationship (ER) Diagram Model with DBMS Example”, [Online]. Available: https://www.guru99.com/er-diagram-tutorial-dbms.html. [Accessed: 5-May- 2023]. javatpoint, “ER (Entity Relationship) Diagram in DBMS”, [Online]. Available: https://www.javatpoint.com/dbms-er-model-concept. [Accessed: 5-May-2023] geeksforgeeks, “Introduction of ER Model”, [Online]. Available: https://www.geeksforgeeks.org/introduction-of-er-model/. [Accessed: 5-May-2023] Dybka, P(2014), “DESIGN FUNDAMENTALS: Chen Notation”, [Online]. Available: https://vertabelo.com/blog/chen-erd-notation/. [Accessed: 5-May-2023]. prepinsta, “Weak Entity and Strong Entity in DBMS”, [Online]. Available: https://prepinsta.com/dbms/weak-entity-and-strong-entity/. [Accessed: 5-May-2023]. Page | 38 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph