CORP707 – 2 - Database and SQL.pdf

Database & SQL Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Data vs Database Data can be facts related to any object in consideration. For example, your name, age, height, image, file, PDF A database is a systematic collection of data. They support electronic storage...

Database & SQL Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Data vs Database Data can be facts related to any object in consideration. For example, your name, age, height, image, file, PDF A database is a systematic collection of data. They support electronic storage and manipulation of data. Databases make data management easy. Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Types of Databases Distributed Graph Relational OLTP Object Oriented Document/JSON Centralized Hierarchical Open-Source Network Cloud MultiModal NoSQL Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Database Management System Database Management System (DBMS) is a collection of programs that enable its users to access databases, manipulate data, report, and represent data. It also helps to control access to the database. Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Advantages vs Disadvantages DBMS offers a variety of techniques to store & retrieve data. DBMS serves as an efficient handler to balance the needs of multiple applications using the same data. Cost of Hardware and Software of a DBMS is quite high which increases the budget of your organization. DBMS can’t perform sophisticated calculations. Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. SQL (Structured Query Language) SQL is the standard language for dealing with Relational Databases. SQL can be used to insert, search, update, and delete database records. SQL can do lots of other operations, including optimizing and maintenance of databases. Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Types of SQL Statements Data Definition Language (DDL) Data Manipulation Language (DML) Data Control Language (DCL) Transaction Control Language (TCL) Data Query Language (DQL) Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. SQL Command CREATE – defines the database structure schema INSERT – inserts data into the row of a table UPDATE – updates data in a database DELETE – removes one or more rows from a table SELECT – selects the attribute based on the condition described by the WHERE clause DROP – removes tables and databases Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. SQL Process SQL Query Engine Optimization Engines Query Dispatcher Classic Query Engine Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Database Development Life Cycle Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Database Normalization Normalization is a database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update and Deletion Anomalies. Normalization rules divides larger tables into smaller tables and links them using relationships. Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Database Normal Forms 1NF (First Normal Form) 2NF (Second Normal Form) 3NF (Third Normal Form) BCNF (Boyce-Codd Normal Form) 4NF (Fourth Normal Form) 5NF (Fifth Normal Form) 6NF (Sixth Normal Form) Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Keys Primary Key Foreign Key Composite Key Index Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. 1NF (First Normal Form) Rules Each table cell should contain a single value. Each record needs to be unique. Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. 2NF (Second Normal Form) Rules Rule 1- Be in 1NF Rule 2- Single Column Primary Key that does not functionally dependant on any subset of candidate key relation Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. 3NF (Second Normal Form) Rules Rule 1- Be in 2NF Rule 2- Has no transitive functional dependencies Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. BCNF (Boyce-Codd Normal Form) Even when a database is in 3rd Normal Form, still there would be anomalies resulted if it has more than one Candidate Key. Sometimes is BCNF is also referred as 3.5 Normal Form. Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Rest Forms 4NF (Second Normal Form) Rules 5NF (Second Normal Form) Rules 6NF (Second Normal Form) Rules Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Entity Relationship Model Entity Relationship Model (ER Modeling) is a graphical approach to database design. It is a high-level data model that defines data elements and their relationship for a specified software system. An ER model is used to represent real-world objects. Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Conceptual Data Model A conceptual data model is the most abstract view of a data model. It offers a complete overview of a business area, including all significant business entities, and describing their relationships. It omits further levels of detail, including data types and interface definitions, making it suitable as a means of presenting an initial business idea to a wide range of stakeholders. Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Logical Data Model Using this model a higher level of detail is established, with data entities describes as master, operational or transactional, and the relationships between them more precisely defined. This is the testing phase of a data model, where functionality can be observed independently of physical specifications. Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Physical Data Model Physical constraints of the database area. Important factors to consider are database performance, physical storage space, and indexing strategy. Diagram notations will fully define all tables and columns, indexes, constrain definitions, and any linking or partitioned tables. Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Example 1 Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Example 2 Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Create Table CREATE TABLE cars ( CREATE TABLE weather ( brand VARCHAR(255), city varchar(80), model VARCHAR(255), temp_lo int, year INT temp_hi int, ); prcp real, date date ); Copyright © 2023-2025 Conestoga College ITAL. All rights reserved.  Insert Into INSERT INTO cars (brand, model, year) INSERT INTO weather VALUES VALUES ('Ford', 'Mustang', 1964); ('San Francisco', 46, 50, 0.25, '1994-11-27’); INSERT INTO weather (date, city, temp_hi, temp_lo) VALUES ('1994-11-29', 'Hayward', 54, 37); Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Select Data SELECT brand, year FROM cars; SELECT * FROM weather JOIN cities ON city = name; SELECT * FROM cars; SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather JOIN cities ON city = name; Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Add Column ALTER TABLE cars ALTER TABLE weather ADD color VARCHAR(255); ADD country VARCHAR(100); Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Update Column UPDATE cars UPDATE weather SET color = 'red' SET temp_hi = temp_hi - 2, WHERE brand = 'Volvo'; temp_lo = temp_lo - 2 WHERE date > '1994-11-28'; Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Drop Column ALTER TABLE cars ALTER TABLE weather DROP COLUMN color; DROP COLUMN temp_lo ; Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Delete Data DELETE FROM cars DELETE FROM weather WHERE brand = 'Volvo'; WHERE city = ‘Hayward’ And temp_hi = 7; Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Drop Table DROP TABLE cars; DROP TABLE weather; Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. Rename Column ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name; Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. PostgreSQL Clauses Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. WHERE SELECT column1, column2,..... columnN FROM table_name WHERE [search_condition] ORDER BY sort_expression Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. WHERE Copyright © 2023-2025 Conestoga College ITAL. All rights reserved. WHERE AND IN OR LIKE = BETWEEN > IS NULL < NOT NULL >= OR !=