Prelim Handouts.pdf

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

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 1 – Handout 1 Declarative Knowledge: Introduction to Database Database Management System Major Components of DBMS Advantages of DBMS Functional Knowledge: Explain the vision, mission, goals and objectives of institution and classroom policies. Describe Major Components of DBMS. Discuss the Advantages of DBMS. Intended Learning Outcomes (ILO): Understand the Database Management System INTRODUCTION In today's world, all applications whether it is from transactions from cashier or registrar, your previous game highlights, and scores, and even the items from your cart or the delivered parcels are all data stored in a database. This module will introduce you the database. It provides information about the database systems and relational database. Database System A database is structured gathered data which was stored to allow to be accessed and updated in the future. It is used by many establishments or applications to manage and manipulate data of all transactions. A database is not always necessarily data from websites and applications. It can be data from stored files and folders inside the cabinet which is called File System. They use it to track their data. Database Management System Commonly these days, when people use database, they are referring to an information from a computer application which interact to its database. This is more known as database management system or DBMS which can be accessed on a local server or in remote. 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 Major Components of DBMS The five major components of a database are: 1. Hardware – refers to the physical parts of a computer which includes storage drive where data is stored and some input and output devices such as keyboard, mouse and monitor. 2. Software – is a collection of programs that provides user friendly interface which has instructions that were used to manage and manipulate the database. 3. Data – are organized stored information. It can be end-user data which are information from the end-user or metadata which is a data about the data that holds information about the data types, relationship, constraints, etc. 4. Procedures – refer to the instructions used in a database management system to help users manage and operate the database system. It also includes operation to install the DBMS, backup and restore database and produce reports. 5. Database Access Language – is a program language used to write commands to create, read, update and delete (CRUD) data stored in a database. Advantages of Database Management System Reduction of Data Redundancy numerous files were kept in various locations inside a system, or even across numerous systems, in file-based data management systems. As a result, there were occasionally several copies of the same file, which resulted in redundant data. Because there is just one database and any changes are instantly reflected, this is prohibited in databases. There is no possibility of running into duplicate data as a result. Data Integrity When information is accurate and consistent in a database, it is said to have data integrity. A database management system (DBMS) has several databases, making data integrity crucial. These databases are all filled with information that is accessible to numerous individuals. For this reason, it is essential to guarantee that the data is accurate and consistent across all databases and users. Data Security In a database, data security is an essential topic. A username and password should be used to validate the identity of any authorized user before granting them access to the database. Under no circumstances should unauthorized people be permitted access to the database as this would breach integrity limitations. Data Sharing 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 Users of a database have the ability to share data amongst themselves. Since there are many degrees of authorization for accessing the data, sharing the data depends on the proper authorization processes being followed. Additionally, a large number of distant users can share data among themselves by concurrently accessing the database. Backup and Restore Data Backup and recovery are handled automatically by the database management system. Because the DBMS handles data backups, users don't need to do so on a regular basis. In addition, it returns the database to its initial state following a crash or other system malfunction. References: Dixon Kimani, “Introduction to Databases”, [Online]. Available: https://www.techopedia.com/6/28832/enterprise/databases/introduction-to- databases#the-relational-database. [Accessed: 8-March-2024]. Mark Drake, “An Introduction to Databases”, [Online]. Available: https://www.digitalocean.com/community/conceptual-articles/an-introduction-to- databases. [Accessed: 8-March-2024]. Claudio Buttice, "Oracle Database (Oracle DB)”, [Online]. Available: https://www.techopedia.com/definition/8711/oracle-database. [Accessed: 8-March- 2024]. tutorialspoint, " Advantages of Database Management System”, [Online]. Available: https://www.tutorialspoint.com/Advantages-of-Database-Management-System. [Accessed: 8-March-2024]. interviewbit, "What are the Components of DBMS (Database Management System)?”, [Online]. Available: https://www.interviewbit.com/blog/components-of-dbms/. [Accessed: 168-March-2024]. 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 2 – Handout 1 Declarative Knowledge: Overview of Database Models Types of Database Models Types of Database Languages Functional Knowledge: Explain Data Models and their types. Explain Databased Languages and their types. Intended Learning Outcomes (ILO): Create simple drawing diagram of different database models, Identify the types of Databased Languages. OVERVIEW To create a building, an architect needs a building plan in order to construct a strong and acceptable building. In database, a database programmer also needs a building plan and that is the database model. A database model describes as the logical structure of a database, which comprises relationships and constraints that define the storage of data and how it can be accessed. Each model is constructed based on the rules and concepts of a data model the database programmer implemented. Types of Database Models Four common types of database model: 1. Hierarchical databases 2. Network databases 3. Relational databases 4. Object-oriented databases Hierarchical databases Developed by IBM for information Management System. It is one of the first database model created and it is rarely used today. Hierarchical databases organized into a tree-like structure. Its structure is like a tree with nodes indicating records and branches representing fields. Best example is the list of officers of an 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 organization that usually posted on a bulletin board where there is a President and below of it were managers and below of the managers are supervisors with team leaders employees below them. President Manager Manager Supervisor Supervisor Supervisor Supervisor Each node in a tree has exactly one (1) parent except for the root node, which has no parent Nodes that have the same parent are siblings A node that has no child nodes is a leaf node or external node. Nodes that have children are known as internal node. A tree within a tree is considered a subtree. The level of a node is a measure of its distance from the root. The depth of the tree is its highest level The degree is the number of child nodes in a subtree C F S P O J L B R U Parts Value(s) Root node C 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 Child nodes F, S, P, O, J, L, B, R, U Parent nodes C, F, S, P, O Siblings F-S, P-O, J-L, B-R Leaf nodes J, L, B, R, U One-level subtrees C-F/S F-P/O S-J/L P-B/R O-U Nodes per level Level 0 – C Level 1 – F, S Level 2 – P, O, J, L Level 3 – B, R, U Depth 3 Degree of each one-level Subtree C – 2 subtree Subtree F – 2 Subtree S – 2 Subtree P – 2 Subtree O – 1 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 Network databases Network databases known as modified hierarchical database as it was structured similar to a graph but can have more than 2 parent node. This database model is a adaptable on representing objects and their relationships. A B1 B2 C C C 1 2 3 The network model can have one to many and many to many relationships which will benefit in modelling the real-life situations. Relational Database Relational Database was invented by Dr. E. F. Codd, a British Scientist who worked for IBM. Its’ fundamental elements are relations (also called as tables) which has tuples (also called as rows) and attributes (also called as columns). Table that has relations to other table were connected thru a certain column that reference to other table. Table: STUDENT COLUMNS Students_ID Student_Name Address Primary 10001 Jay Dela Cruz Calamba Key 10002 John Santos Cabuyao ROWS RELATIONS Table: SECTION Section_ID Description Students_ID Foreign Primary Key 201 IT 10001 Key 202 IT 10002 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 Primary Key is a column that uniquely identify the rows of a table. Foreign Key is a Primary Key of one’s table inserted to another table to relate the two tables. Object-oriented databases The object-oriented data model is centered on the object-oriented- programming concepts unite with relational database principles. It has OBJECTS where the type is either predefine or user-define, CLASSES which defines the behavior and METHODS that define the behavior of a class. It also has pointers that form relations between objects. Object-Oriented Relational Database Concepts Principles Object-Oriented Polymorphis Integrity m Database Inheritance Concurrency d Encapsulatio d Query n Processing Unlike relational databases, all information comes in object package instead of multiple tables. Object-Oriented Relational Database Languages We employ particular languages in our daily interactions to express ourselves and our ideas to others. It's a crucial component of our life since it makes it easier for other people to comprehend what we're trying to say. Similar to this, we require certain programming languages in the realm of data so that DBMS software can comprehend our requirements and manage the data contained in the databases appropriately. These programming languages are also referred to as query or database languages. 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 A database management system's ability to operate properly is aided by the usage of database languages for a number of crucial activities. These jobs could be specific actions like reading, updating, inserting, searching, or deleting data from the database. Types of Database Languages Database Management System (DBMS) are providing different set of operations to store and manipulate data such as creating, reading, updating and deleting (CRUD). These operations are the database languages. The database languages are categorized into four types which includes Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL) and Transaction Control Language (TCL). Database Languages DDL DML DCL TCL Create Select Revoke Rollback Drop Insert Grant Commit Truncate Delete Rename Update Data Definition Language (DDL) A collection of unique commands called Data Definition Language (DDL) enables us to specify and modify the metadata and database structure. The database structures, such as the schema, tables, indexes, and so on, can be created, modified, and deleted using these commands. DDL commands are often not used by an end-user (someone who is accessing the database via an application), as they can modify the structure of the entire database and any change done by a DDL command is auto-committed (the change is saved permanently in the database). 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 Data Manipulation Language (DML) We can access and modify the data held in pre-existing schema objects by using the Data Manipulation Language (DML), which is a set of unique instructions. The insertion, deletion, updating, and retrieval of data from the database are just a few of the actions carried out by these commands. These instructions handle user requests since they are in charge of all data modification. Data Query language refers to the DML instructions that deal with data retrieval. NOTE: The changes and updates made using DML commands can be reversed because they are not automatically committed. Data Control Language (DCL) A collection of unique instructions known as Data Control Language (DCL) is used to manage user access in a database system. The user has access to ALL, CREATE, SELECT, INSERT, UPDATE, DELETE, and EXECUTE, among other things. For each action or query to be executed in the database system, we need data access permissions. The DCL statements are used to regulate this user's access. Access to data or the database can be granted or revoked using these statements. DCL instructions have rollback arguments since they are transactional. Transaction Control Language (TCL) A collection of unique commands known as Transaction Control Language (TCL) is used to manage transactions within databases. A transaction is a group of connected tasks that the DBMS software treats as a single execution unit. Therefore, transactions are in charge of carrying out various functions within a database. TCL commands are used to execute or rollback changes made with the aid of DML commands. These commands are employed to monitor the effects of other commands on the database. References: David Taylor, “What is Data Modelling?”, [Online]. Available: https://www.guru99.com/data-modelling-conceptual-logical.html. [Accessed: 8-March- 2024]. Lucidchart, “What is a Database Model”, [Online]. Available: https://www.lucidchart.com/pages/database-diagram/database-models#:~:text= A%20database%20model%20shows%20the,data%20model%20the%20designers%20adopt.. [Accessed: 8-March-2024] 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 learntek, “Types of Databases”, [Online]. Available: https://www.learntek.org/blog/types- of-databases/. [Accessed: 8-March-2024]. Milica Dancuk, "What Is an Object-Oriented Database?”, [Online]. Available: https://phoenixnap.com/kb/object-oriented-database. [Accessed: 8-March-2024]. Vaibhav Gulati, " Database Languages”, [Online]. Available: https://www.scaler.com/topics/databse-languages-in-dbms/. [Accessed: 8-March-2024]. techopedia, “Data Definition Language (DDL)”, [Online]. Available: https://www.techopedia.com/definition/1175/data-definition-language-ddl. [Accessed: 8-March-2024]. Bhanu Priya, “What are different types of DBMS languages?”, [Online]. Available: https://www.tutorialspoint.com/what-are-different-types-of-dbms-languages. [Accessed: 8-March-2024] 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 Week 3 – Handout 1 Declarative Knowledge: Getting Ready to MySQL MySQL Workbench Naming Conventions for MySQL MySQL Data Types Functional Knowledge: Explain the working interface of MySQL Workbench. Explain the naming conventions and data types of MySQL. Intended Learning Outcomes (ILO): Install and familiarize the MySQL Workbench. Create sample of MySQL naming conventions and data types. MySQL Workbench General Information MySQL Workbench is a graphical tool for working with MySQL servers and databases. MySQL Workbench fully supports MySQL server version 5.7 and higher. Deprecated versions of MySQL Server (prior to version 5.7) are incompatible with MySQL Workbench and should be upgraded before you attempt to make a connection. MySQL Workbench functionality covers five main topics: SQL Development: Enables you to create and manage connections to database servers. Along with enabling you to configure connection parameters, MySQL Workbench provides the capability to execute SQL queries on the database connections using the built-in SQL Editor. Data Modeling (Design): Enables you to create models of your database schema graphically, reverse and forward engineer between a schema and a live database, and edit all aspects of your database using the comprehensive Table Editor. The Table Editor provides easy-to-use facilities for editing Tables, Columns, Indexes, Triggers, Partitioning, Options, Inserts and Privileges, Routines and Views. Server Administration: Enables you to administer MySQL server instances by administering users, performing backup and recovery, inspecting audit data, viewing database health, and monitoring the MySQL server performance. Data Migration: Allows you to migrate from Microsoft SQL Server, Microsoft Access, Sybase ASE, SQLite, SQL Anywhere, PostreSQL, and other RDBMS tables, objects and data to MySQL. Migration also supports migrating from earlier versions of MySQL to the latest releases. MySQL Enterprise Support: Support for Enterprise products such as MySQL Enterprise Backup, MySQL Firewall, and MySQL Audit. 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 MySQL Workbench is available in two editions: the Community Edition and the Commercial Edition. The Community Edition is available free of charge. The Commercial Edition provides additional Enterprise features, such as access to MySQL Enterprise Backup, MySQL Firewall, and MySQL Audit. Installation Requirements for Windows The following prerequisites are available at the Microsoft Download Center: Microsoft.NET Framework 4.5.2 Microsoft Visual C++ 2015-2022 Redistributable Microsoft Windows 11 or Windows Server 2022 Creating A New MySQL Connection (Tutorial) This tutorial adds a new connection that can be either an initial connection or an additional connection. An instance of MySQL server must be installed, started, and accessible to MySQL Workbench before you begin. To create a new connection, follow these steps: 1. Launch MySQL Workbench to open the home screen. Existing connections are shown when you click the MySQL Connections view from the sidebar. No connections exist for first-time users. Getting Started Tutorial - Home Screen 2. From the MySQL Workbench home screen shown in the previous figure, click the [+] icon near the MySQL Connections label to open the Setup New Connection wizard. 3. Define the Connection Name value, such as MyFirstConnection as the next figure shows. 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 Getting Started Tutorial - Setup New Connection: MyFirstConnection The default connection values are for a typical local setup, so check them and enter the appropriate values. If you are unsure, click the Test Connection button to check the connection parameters. Do not press OK. Next, optionally click Configure Server Management... , which opens up the Configure Local Management wizard: 4. Read the Configure Local Management introduction (shown in the next figure), and press Next to begin defining the new connection parameters. Getting Started Tutorial - Configure Local Management Introduction 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 5. The connection will now be tested. You should see that the connection was successful. If not, click Back and check that you have entered the information correctly. The following figure shows a database connection that tested successfully. Getting Started Tutorial - Test Database Connection Toggle the Show Logs to view additional details about the tested connection, then click Next. 6. Optionally, you may configure a method for remote management if a Remote Host was specified. Setting these options enables MySQL Workbench to determine the location of configuration files, and the correct start and stop commands to use for the connection. SSH login based management and Native Windows remote management types are available. The Operating System and MySQL Installation Type are configured for the SSH login variant. This step creates a local MySQL connection, so you can skip the Management and OS and SSH Configuration options, which are used for configuring a remote MySQL connection. 7. On Microsoft Windows, select the appropriate MySQL service for the MySQL connection, as shown in the figure that follows. Getting Started Tutorial - Windows Management 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 8. The wizard will now check its ability to access the start and stop commands and then check access to the MySQL Server configuration file as the next figure shows. Getting Started Tutorial - Test Settings 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 9. You now have a chance to review the configuration settings. The information displayed varies slightly depending on platform, connection method, and installation type. At the Review Settings prompt, choose I'd like to review the settings again to review the settings as shown in the next figure. Choosing Continue closes the Configure Server Management dialog. Getting Started Tutorial - Review Settings Check the Change Parameters if you want to check or edit information about the MySQL configuration file. For this example, select the check box and click Next to continue. 10. Review the MySQL configuration file information shown in the next figure. Click Check Path and Check Name to perform the described checks, or optionally change the configuration file path. Getting Started Tutorial - MySQL Configuration File 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 11. Optionally, enter your own commands for starting, stopping, and checking the MySQL connection. To apply the default values, leave these optional values blank as the following figure shows. Getting Started Tutorial - Specify Commands Click Finish to close the Configure Server Management dialog, which returns to the original Setup New Connection step. 12. After reviewing the Setup New Connection information (see the figure that follows), click Test Connection again to make sure it still functions and then click OK to create the new MySQL connection. Getting Started Tutorial - Setup New Connection 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 Your new MyFirstConnection MySQL connection is now listed on the home screen. 13. From the home screen, click the new MySQL connection to open the SQL editor for this connection. The SQL editor is the default page. Click Server Status from the Navigator area of the sidebar to display the current status of the connected MySQL server instance (see the figure that follows). Getting Started Tutorial - Server Status 14. Test the other Navigator area options that relate to your new MySQL connection. Check its status, MySQL logs, and measure its performance statistics from the Dashboard. Notice the Administration and Schemas tabs in the Navigator area. The Schemas view displays the schemas that are associated with your new MySQL connection. Alternatively, you can merge the content of the tabs by either clicking 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 merge ( ) or by enabling the Show Management Tools and Schema Tree in a single tab SQL editor preference. Configuration User Accessibility Options MySQL Workbench includes options to improve user accessibility that you can select from the Workbench Preferences dialog. To open the dialog, click Edit and then Preferences from the menu. Fonts Modeling fonts are adjustable from the Appearance section of the Modeling list. The following figure shows the color presets and fonts. Appearance Preferences Choose the character set from the Configure Fonts For list (or leave the default setting) and then adjust the model fonts to fit your requirements. The font types and sizes for other screen elements are set from the Fonts & Colors preferences. The next figure shows the default fonts for the SQL Editor, Resultset Grid, Scripting Shell, and Script Editor. Fonts & Color Preferences 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 Note Font changes require a refresh or restart before they take effect. The following figure shows an example of the SQL Editor after changing the Editor font size from 10 to 30. SQL Editor with Font size 30 Color Presets 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 Color presets in the Appearance preferences enable you to define the colors used in EER diagrams for the tables, views, layers, and notes. You can edit or add additional color choices by entering their ASCII values. Theming On Windows, the Fonts & Colors preference tab also includes a "Color Scheme" configuration section. From here, you can enable the High Contrast color theme (see the figure that follows). This theme preference affects the MySQL Workbench GUI. High Contrast Preference Visual SQL Editor The visual SQL editor consists of a set of specialized editors (query, schema, table, and so on) and three panels: sidebar, secondary sidebar, and output area. Each editor opens in a separate secondary tab within an active MySQL connection tab. Each panel can be hidden or shown. Together the editors and panels enable you to: Build, edit, and run queries Create and edit data View and export results Perform basic RDBMS administrative tasks Color syntax highlighting, context help, and code completion assist you in writing and debugging SQL statements. The integrated EXPLAIN plans provide data to help optimize the your queries. The following figure shows the main elements of the visual editor. 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 Visual SQL Editor Description of the Visual SQL Editor Elements 1. Home screen tab. The Home screen tab provides quick access to connections, models, and the MySQL Migration wizard. Unlike the other main tabs, the Home screen tab does not close. 2. Connection tab. Each connection made to the MySQL server is represented by a separate connection tab. A server can be active or inactive when the connection tab for it is opened. 3. SQL query tab. The SQL query tab is a secondary tab that opens by default when you make a connection to a MySQL server. Each query tab is uniquely identified by an incrementing number: query 1, query 2, and so on. To close an open tab, click the x on the tab. All SQL query tabs provide an area to edit queries. You can open other specialized editors within tabs in this same central area. For example, you can edit schemas, tables, columns, and so on. Administration tabs also open in this area. 4. Main menu bar. The menu bar has the following menus: File, Edit, View, Query, Database, Server, Tools, Scripting, and Help. The actions available to you depend on which tab is selected when you click a menu. 5. Main toolbar. The quick actions in this toolbar are (ordered from left to right): Create a new SQL tab for executing queries Open an SQL script file in a new query tab Open Inspector for the selected object Create a new schema in the connected server Create a new table in the active schema in connected server Create a new view in the active schema in the connected server 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 Create a new stored procedure in the active schema in the connected server Create a new function in the active schema in the connected server Search table data for text in objects selected in the sidebar schema tree Reconnect to DBMS 6. Shortcut actions. Provides the following shortcuts (ordered from left to right): Hide or show the sidebar panel Hide or show the output area panel Hide or show the secondary sidebar panel 7. Sidebar panel. The sidebar has two main labels: Navigator and Information. The labels are omitted on some hosts. The Navigator has two subtabs: Administration (previously named Management) and Schemas. You can merge (or separate) the content of the two tabs into a single list by clicking merge ( ). The Information area provides the Object Info and Session subtabs, which include read-only information about a selected object and about the active connection. 8. Secondary sidebar panel (SQL Additions). The SQL Additions area provides the following subtabs: Context Help Snippets 9. Output area panel. The output panel can display a summary of the executed queries in the following forms: Action Output, Text Output, or History Output. Naming Conventions for MySQL MySQL database identifiers that you can name include databases, tables, and columns. They follow these naming conventions. Aliases must be from 1 to 255 characters long. All other identifier names must be from 1 to 64 characters long. Database names can use any character that is allowed in a directory name except for a period, a backward slash (\), or a forward slash (/). By default, MySQL encloses column names and table names in quotation marks. Table names can use any character that is allowed in a file name except for a period or a forward slash. Table names must be 32 characters or less because SAS does not truncate a longer name. If you already have a table name that is greater than 32 characters, it is recommended that you create a table view. Column names and alias names allow all characters. Embedded spaces and other special characters are not permitted unless you enclose the name in quotation marks. Embedded quotation marks are not permitted. Case sensitivity is specified when a server is installed. By default, the names of database objects are case sensitive on UNIX and not case sensitive on Windows. For example, the names CUSTOMER and Customer are different on a case-sensitive server. A name cannot be a reserved word in MySQL unless you enclose the name in quotation marks. See the MySQL documentation for more information about reserved words. 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 Database names must be unique. For each user within a database, names of database objects must be unique across all users. For example, if a database contains a department table that User A created, no other user can create a department table in the same database. MySQL does not recognize the notion of schema, so tables are automatically visible to all users with the appropriate privileges. Column names and index names must be unique within a table. MySQL Data Types The data type of a column defines what value the column can hold: integer, character, money, date and time, binary, and so on. Each column in a database table is required to have a name and a data type. An SQL developer must decide what type of data that will be stored inside each column when creating a table. The data type is a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data. In MySQL there are three main data types: string, numeric, and date and time. String Data Types Data type Description CHAR(size) A FIXED length string (can contain letters, numbers, and special characters). The size parameter specifies the column length in characters - can be from 0 to 255. Default is 1 VARCHAR(size) A VARIABLE length string (can contain letters, numbers, and special characters). The size parameter specifies the maximum column length in characters - can be from 0 to 65535 BINARY(size) Equal to CHAR(), but stores binary byte strings. The size parameter specifies the column length in bytes. Default is 1 VARBINARY(size) Equal to VARCHAR(), but stores binary byte strings. The size parameter specifies the maximum column length in bytes. TINYBLOB For BLOBs (Binary Large OBjects). Max length: 255 bytes TINYTEXT Holds a string with a maximum length of 255 characters TEXT(size) Holds a string with a maximum length of 65,535 bytes BLOB(size) For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data MEDIUMTEXT Holds a string with a maximum length of 16,777,215 characters MEDIUMBLOB For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data LONGTEXT Holds a string with a maximum length of 4,294,967,295 characters LONGBLOB For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data ENUM(val1, val2, A string object that can have only one value, chosen from a list of val3,...) possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted. The values are sorted in the order you enter them 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 SET(val1, val2, A string object that can have 0 or more values, chosen from a list of val3,...) possible values. You can list up to 64 values in a SET list Numeric Data Types Data type Description BIT(size) A bit-value type. The number of bits per value is specified in size. The size parameter can hold a value from 1 to 64. The default value for size is 1. TINYINT(size) A very small integer. Signed range is from -128 to 127. Unsigned range is from 0 to 255. The size parameter specifies the maximum display width (which is 255) BOOL Zero is considered as false, nonzero values are considered as true. BOOLEAN Equal to BOOL SMALLINT(size) A small integer. Signed range is from -32768 to 32767. Unsigned range is from 0 to 65535. The size parameter specifies the maximum display width (which is 255) MEDIUMINT(size) A medium integer. Signed range is from -8388608 to 8388607. Unsigned range is from 0 to 16777215. The size parameter specifies the maximum display width (which is 255) INT(size) A medium integer. Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255) INTEGER(size) Equal to INT(size) BIGINT(size) A large integer. Signed range is from -9223372036854775808 to 9223372036854775807. Unsigned range is from 0 to 18446744073709551615. The size parameter specifies the maximum display width (which is 255) FLOAT(size, d) A floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. This syntax is deprecated in MySQL 8.0.17, and it will be removed in future MySQL versions FLOAT(p) A floating point number. MySQL uses the p value to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT(). If p is from 25 to 53, the data type becomes DOUBLE() DOUBLE(size, d) A normal-size floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter DOUBLE PRECISION(size, d) DECIMAL(size, An exact fixed-point number. The total number of digits is specified d) in size. The number of digits after the decimal point is specified in the d parameter. The maximum number for size is 65. The maximum number for d is 30. The default value for size is 10. The default value for d is 0. DEC(size, d) Equal to DECIMAL(size,d) 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 Note: All the numeric data types may have an extra option: UNSIGNED or ZEROFILL. If you add the UNSIGNED option, MySQL disallows negative values for the column. If you add the ZEROFILL option, MySQL automatically also adds the UNSIGNED attribute to the column. Date and Time Data Types Data type Description DATE A date. Format: YYYY-MM-DD. The supported range is from '1000-01- 01' to '9999-12-31' DATETIME(fsp) A date and time combination. Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Adding DEFAULT and ON UPDATE in the column definition to get automatic initialization and updating to the current date and time TIMESTAMP(fsp) A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP in the column definition TIME(fsp) A time. Format: hh:mm:ss. The supported range is from '-838:59:59' to '838:59:59' YEAR A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and 0000. MySQL 8.0 does not support year in two-digit format. References: dev.mysql, " MySQL Workbench”, [Online]. Available: https://dev.mysql.com/doc/workbench/en/. [Accessed: 8-March-2024]. sas, " MySQL Workbench”, [Online]. Available: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n0rfg6x1shw0ppn1 cwhco6yn09f7.htm#:~:text=Column%20names%20and%20alias%20names,when%20a%20 server%20is%20installed.. [Accessed: 8-March-2024]. w3schools, " MySQL Data Types”, [Online]. Available: https://www.w3schools.com/mysql/mysql_datatypes.asp. [Accessed: 8-March-2024]. 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 Week 4 – Handout 1 Declarative Knowledge: SQL Data Definition Language create and drop database create, drop and alter tables different data constraints Functional Knowledge: Explain the Data Definition Language. Create database and use the syntax of Data Definition Language.. Intended Learning Outcomes (ILO): 1. Apply and use MySQL syntax of Data Definition Language. Data Definition Language (DDL) Data Definition Language (DDL) were used to create and modify database objects such as schema, tables, indexes, views, etc. Create and Drop Database Syntax to create the database: CREATE DATABASE database_name; Syntax to drop (delete) the database: DROP DATABASE database_name Create, Drop and Alter Table To create a new table : CREATE TABLE table_name ( column_1 DATATYPE, column_2 DATATYPE, column_n DATATYPE ); 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: CREATE TABLE tblItems ( itemId integer primary key auto_increment, itemName varchar(100) not null, description varchar(100)); Syntax to drop (delete) an existing table : DROP TABLE table_name; Syntax to modify the structure of the table To add new column(s) in a table : ALTER TABLE table_name ADD column_name datatype constraints; Sample: ALTER TABLE tblItems ADD price int not null; To change the datatype of a column in a table : ALTER TABLE table_name MODIFY column_name DATATYPE; Sample: ALTER TABLE tblItems MODIFY price double; To remove a column from a table : ALTER TABLE table_name DROP COLUMN column_name; 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 Constraints To limit the type of data that will be inserted into the table, SQL Constraints should be followed otherwise, the command will not proceed. NOT NULL Data insertion will not proceed if inserted data in the column is NULL or no value Sample: CREATE TABLE tblItems ( itemId integer primary key auto_increment, itemName varchar(100) NOT NULL, description varchar(100)); PRIMARY KEY Unique identifier of a table. Column with primary key constraints will not accept NULL and not UNIQUE values. Sample: CREATE TABLE tblItems ( itemId integer PRIMARY KEY auto_increment, itemName varchar(100) not null, description varchar(100)); FOREIGN KEY is a Primary Key of one’s table inserted to another table to relate the two tables. Values inserted as Foreign Key but not existing as Primary Key to related table will not proceed. Sample: Assuming a table named tblItems were created with a primary key column named itemId added. CREATE TABLE tblReceipt ( receiptId int primary key auto_increment, itemId int not null, 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 dateOrdered date, FOREIGN KEY (itemId) REFERENCES tblItems(itemId) ); AUTO_INCREMENT Data in this column will generate automatic increment each record inserted into the table. Column with primary key constraints is always suggested to add auto increment constraints so that it will ensures data inserted is unique. Sample: CREATE TABLE tblItems ( itemId integer primary key AUTO_INCREMENT, itemName varchar(100) not null, description varchar(100)); References: techopedia, “Data Definition Language (DDL)”, [Online]. Available: https://www.techopedia.com/definition/1175/data-definition-language-ddl. [Accessed: 8-March-2024]. w3schools, “SQL Constraints”, [Online]. Available: https://www.w3schools.com/sql/sql_constraints.asp. [Accessed: 8-March-2024]. 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 5 – Handout 1 Declarative Knowledge: SQL Data Manipulation Language Insert Update Delete Select Functional Knowledge: Explain the Data Manipulation Language. Create database and use the syntax of Data Manipulation Language. Intended Learning Outcomes (ILO): Apply and use MySQL syntax of Data Manipulation Language. INTRODUCTION Last topic, we discussed one of the four types of database languages which is the Data Definition Language (DDL). This time, we will learn another database language, the Data Manipulation Language (DML). Data Manipulation Language or DML are user request operations that are used to retrieve data and manipulate it in a database. Commands used in DML are Select, Insert, Update and Delete. Insert, Update and Delete Statement Insert, Update and Delete are commands used to manipulate existing data in the database. Insert Statement – used to add data in table. Syntax to insert row that corresponds to the table structure : INSERT INTO table_name VALUES (column_value1, column_value2, column_value3); 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 Sample: INSERT INTO tblItems VALUES (default, ‘palmolive’, ‘green’); Syntax to insert row based on the specific columns to be inserted: INSERT INTO table_name (column1, column2, column3) VALUES (column_value1, column_value2, column_value3); Sample: INSERT INTO tblItems (itemId, itemName) VALUES (default, ‘palmolive’); Note: The data types of each column must match with the values to be inserted as well as must follow the constraints required, otherwise errors will prompt and command will not proceed. Syntax to insert multiple rows that corresponds to the table structure : INSERT INTO table_name VALUES (column_value1, column_value2, column_value3), (column_value1, column_value2, column_value3), (column_value1, column_value2, column_value3),...; Sample: INSERT INTO tblItems VALUES (default, ‘palmolive’, ‘green’), (default, ‘dove’, ‘white’), (default, ‘tender care’, ‘mild’); 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 Update Statement – used to modify value/s of existing column/s within a table. Syntax to update the columns of a table: UPDATE table_name SET column_1 = value1, column_2 = value2, column_3 = value3, [WHERE condition]; Sample: UPDATE tblItems SET description = ‘Shampoo’ WHERE itemName = ‘Safe guard’; In this command, all the itemName with ‘Safe Guard’ value will replace all the inserted value of column description with value ‘Shampoo’. Note: the SET statement is used to modify values on the specific column stated and the WHERE clause is used as Condition to modify the values when specific column met the column’s values stated. Delete Statement – used to remove existing rows of a table. Syntax to delete the rows of a table: DELETE FROM table_name [WHERE condition]; Sample: DELETE FROM tblItems WHERE itemName = ‘Safe guard’; In this command, all rows with values of ‘Safe guard’ in column itemName will be deleted. 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 Select Statement – used to retrieve data from a database. TO DISPLAY ALL ROWS INSERTED FROM THE TABLE Syntax to retrieve all rows inserted from the table: SELECT * FROM table_name; Sample: SELECT * FROM tblItems; On this command, a table will display on the Database Management System used displaying all the rows inserted in tblItems. TO DISPLAY SPECIFIC ROWS INSERTED FROM THE TABLE Syntax to retrieve specific rows inserted from the table: SELECT * FROM table_name [WHERE condition]; Sample: SELECT * FROM tblItems WHERE itemId > 1003; On this command, only rows with values greater than 1003 of column itemId will display. TO DISPLAY ALL ROWS WITH SPECIFIC COLUMNS INSERTED FROM THE TABLE Syntax to retrieve all rows with specific columns inserted from the table: SELECT column_1, column_2, column_3 FROM table_name; Sample: SELECT itemName, description 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 FROM tblItems; On this command, all rows showing columns itemName and description only will display. TO DISPLAY SPECIFIC ROWS WITH SPECIFIC COLUMNS INSERTED FROM THE TABLE Syntax to retrieve specific rows with specific columns inserted from the table: SELECT column_1, column_2, column_3 FROM table_name [WHERE condition]; Sample: SELECT itemName, description FROM tblItems WHERE itemId > 1003; On this command, only rows with values greater than 1003 of column itemId that shows columns itemName and description only will display. References: javatpoint, “Database Languages in DBMS”, [Online]. Available: https://www.javatpoint.com/dbms-language. [Accessed: 8-March-2024]. w3schools, “MySQL UPDATE Statement?”, [Online]. Available: https://www.w3schools.com/mysql/mysql_update.asp. [Accessed: 8-March-2024] 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