ADSUpdated.pdf

Advance Database System Advanced Database System This course focuses on Advanced Database Management. This includes manipulating databases using foreign keys, composite keys and filtering data using SQL. Programming Language Minimum Requirements SQL Low-End C...

Advance Database System Advanced Database System This course focuses on Advanced Database Management. This includes manipulating databases using foreign keys, composite keys and filtering data using SQL. Programming Language Minimum Requirements SQL Low-End Computers Mode of Learning Laboratory and Lecture General Topics: Introduction to SQL DDL and DML Statements Filtering Data Aggregate Functions Sub Queries Joins Window Functions View Lesson 1 Introduction to SQL What is SQL? SQL stands for Structured Query Language SQL lets you access and manipulate databases SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987 Introduction to SQL What Can SQL do? SQL can execute queries against a database SQL can retrieve data from a database SQL can insert records in a database SQL can update records in a database SQL can delete records from a database SQL can create new databases SQL can create new tables in a database SQL can create stored procedures in a database SQL can create views in a database SQL can set permissions on tables, procedures, and views RDBMS RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows. Tables - Student_table Every table is broken up into smaller entities called fields. The fields in the Student_table consist of StudentID, StudentName, Contact, Address, City, Postal Code and Country. A field is a column in a table that is designed to maintain specific information about every record in the table. A record, also called a row, each individual entry that exists in a table. (For example, there are 91 records in the above Student_table. ) A record is a horizontal entity in a table. A column is a vertical entity in a table that contains all information associated with a specific field in a table. Database Table - Attribute / Column In SQL, an attribute refers to a column or field within a database table. It represents a specific characteristic or property of the data stored in that table. Attributes define the types of information that can be stored in a table. Database_table - Rows / Tuples In a relational database, a row, a.k.a. Record or Tuple, represents a single, implicitly structured data item in a table.  Activity #1 Instructions: 1. Introduction: ○ Begin with a brief explanation of the importance of tables in databases for organizing and managing information. ○ Explain that each table consists of columns (attributes) and rows (records). What is DDL in SQL ? DDL stands for Data Definition Language. It is a subset of SQL that is used to define the structure of a database. This includes creating, modifying, and deleting tables, columns, indexes, and other database objects. examples of DDL statements: CREATE TABLE: Creates a new table in the database. ALTER TABLE: Modifies an existing table by adding, dropping, or modifying columns. DROP TABLE: Deletes an existing table from the database. CREATE INDEX: Creates an index on a table column to improve query performance. DROP INDEX: Deletes an existing index from a table. What is DDL in SQL ? ➔ DDL statements are typically executed before any data is inserted into the database. They ensure that the database is properly structured to accommodate the data that will be stored in it. What is DML in SQL ? ➔ DML stands for Data Manipulation Language. It is a subset of SQL that is used to manipulate data within a database. This includes inserting, updating, and deleting data from tables. examples of DML statements: INSERT INTO: Inserts new rows into a table. UPDATE: Modifies existing rows in a table. DELETE FROM: Deletes existing rows from a table. What is DML in SQL ? ➔ DML statements are typically executed after the database structure has been defined using DDL statements. They are used to populate the database with data and to make changes to the data over time. DDL vs DML : A comparison ➔ DDL (Data Definition Language) and DML (Data Manipulation Language) are two fundamental subsets of SQL (Structured Query Language) used to interact with databases. ➔ While they both serve essential purposes, they differ significantly in their functions. DDL vs DML : A comparison DDL (Data Definition Language) ➔ DML (Data Manipulation Language) ➔ Purpose: Manipulates the data within a ➔ Purpose: Defines the structure of a database. database. ➔ Operations: Inserts, updates, and deletes ➔ Operations: Creates, modifies, and data from existing tables. deletes database objects like tables, ➔ Examples of statements: INSERT INTO, indexes, views, and constraints. UPDATE, DELETE FROM ➔ Examples of statements: CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, DROP INDEX. Key Differences: DDL vs DML Commands Core SQL Statements Data Definition Language (DDL): ➔ CREATE: Used to create database objects like tables, indexes, and views. Data Definition Language (DDL): ➔ ALTER: Used to modify existing database objects. Data Definition Language (DDL): ➔ DROP: Used to delete database objects. Data Manipulation Language (DML): ➔ INSERT: Used to insert new rows into a table. Data Manipulation Language (DML): ➔ UPDATE: Used to modify existing rows in a table. Data Manipulation Language (DML): ➔ DELETE: Used to delete rows from a table. Data Manipulation Language (DML): ➔ SELECT: Used to retrieve data from tables. Other SQL Statements: ➔ GRANT: Used to grant privileges to users. ➔ REVOKE: Used to revoke privileges from users. ➔ COMMIT: Used to save changes made in a transaction. ➔ ROLLBACK: Used to undo changes made in a transaction. Additional SQL Features ➔ Joins: Used to combine data from multiple tables. ➔ Subqueries: Used to embed queries within other queries. ➔ Aggregates: Used to perform calculations on groups of data. ➔ Views: Used to create virtual tables based on existing tables. ➔ Stored Procedures: Used to create reusable blocks of code. Example of a more complex query: This query joins the customers and orders tables to retrieve the first name, last name, and order date for all customers who placed orders between January 1st and December 31st, 2023. Note: The specific syntax and capabilities of SQL can vary slightly depending on the database system being used (e.g., MySQL, PostgreSQL, Oracle, SQL Server). Act#2 / Prelim Project / Data Product Canvas Lesson 2 Database Middleware Database middleware provides a standardized way for applications to interact with one or more databases. It abstracts database-specific details, enabling seamless access and management of data across different database systems. This type is important for applications requiring high-level data integration and consistency. Module 1: Database Design and Implementation UNIT 1: Overview of Relational Database Design What is Relational Database? Module 1: Database Design and Implementation Relational Database A relational database is a database that has a collection of tables of data items and links them, based on defined relationships. Relational Database Entities Entities: These are the real-world objects or concepts that the diagram represents. Entities can be tangible (like a person or a product) or intangible (like an event or an idea). Entities in an ER diagram are categorized into two types: strong entities and weak entities. Strong entities and Weak entities What is a Strong Entity? A strong entity is not dependent on any other entity in the schema. A strong entity will always have a primary key. Strong entities are represented by a single rectangle. The relationship of two strong entities is represented by a single diamond. Various strong entities, when combined together, create a strong entity set. Strong entities and Weak entities What is a Weak Entity? A weak entity is dependent on a strong entity to ensure its existence. Unlike a strong entity, a weak entity does not have any primary key. It instead has a partial discriminator key. A weak entity is represented by a double rectangle. The relation between one strong and one weak entity is represented by a double diamond. This relationship is also known as an identifying relationship. Strong entities and Weak entities Types of Attributes Definition of Derived Attributes Derived attributes are a class of attributes in ER diagrams that aren't stored directly within the entity; instead, they are computed or derived from other base attributes. These derived attributes do not contain their own data; rather, their values are calculated from existing data within the database. For instance, consider an entity 'Student' with a base attribute 'Date of Birth.' A derived attribute for this entity could be 'Age,' which isn't stored directly but calculated based on the current date and the employee's date of birth. Entity Relationship Diagram (ERD) Symbols Relationships The link between entities is known as “Relationship” Relationships on relational database enable users to retrieve and combine data from one or more tables. One to One Relationship One to Many Relationships Many to Many Relationships Generalization Example Specialization Example Aggregation Example Aggregation Aggregation An ER diagram is not capable of representing the relationship between an entity and a relationship which may be required in some scenarios. In those cases, a relationship with its corresponding entities is aggregated into a higher-level entity. Aggregation is an abstraction through which we can represent relationships as higher-level entity sets. For Example, an Employee working on a project may require some machinery. So, REQUIRE relationship is needed between the relationship WORKS_FOR and entity MACHINERY. Using aggregation, WORKS_FOR relationship with its entities EMPLOYEE and PROJECT is aggregated into a single entity and relationship REQUIRE is created between the aggregated entity and MACHINERY. ERD for the Project Management System Project: Machinery: ProjectID (Primary Key) MachineryID (Primary Key) ProjectName MachineryType Description... (Other machinery attributes)... (Other project attributes) Project_Machinery: Employee: ProjectID (Foreign Key referencing Project) EmployeeID (Primary Key) MachineryID (Foreign Key referencing EmployeeName Machinery) Department Quantity... (Other employee attributes) Relationships Many-to-Many: A project can require multiple machines, and a machine can be used in multiple projects. This relationship is represented by the Project_Machinery junction table. Many-to-Many (implied): An employee can work on multiple projects, and a project can have multiple employees assigned to it. This relationship is implied by the Project_Machinery table. 3 Different ways of modeling data ER Diagrams ER Diagram ERD Notation ERD Notation Lesson 3 Database models Relational Model: Foundation: Based on set theory and relational algebra. Structure: Data is organized into tables, where each table represents a relation. Relationships: Relationships between tables are defined using foreign keys. Examples: MySQL, PostgreSQL, Oracle, SQL Server Strengths: Strong data integrity, flexibility, and powerful query capabilities. Weaknesses: Can be complex for large-scale, non-relational data. Hierarchical Model: Structure: Data is organized in a tree-like structure, with parent-child relationships. Limitations: Less flexible than relational model, limited to one-to-many relationships. Use cases: Legacy systems, specific domains like genealogy or document management. Network Model: Structure: Similar to hierarchical model but allows more complex relationships (many-to-many). Complexity: More complex to design and manage compared to relational model. Use cases: Specialized applications where complex relationships are essential. Object-Oriented Model: Structure: Data is represented as objects, with properties and methods. Advantages: Natural mapping to object-oriented programming languages, better handling of complex data types. Examples: Object-oriented databases (OODBMS), NoSQL databases with object-oriented features. NoSQL (Not Only SQL): Diverse: Includes various models like document, key-value, graph, and wide-column. Characteristics: Scalability, flexibility, and performance for large datasets and unstructured data. Examples: MongoDB, Cassandra, Redis, Neo4j Use cases: Big data, real-time analytics, content management, and more. Lesson 4