ADS.docx

**SQL:** Used to manage and manipulate relational databases **MYSQL:** A platform uses SQL to manage data stored in db **Queries:  **a query is a command used to interact with a database **What are SQL statements?** - **Data Manipulation Language (DML):** Commands used to manipulate data w...

**SQL:** Used to manage and manipulate relational databases **MYSQL:** A platform uses SQL to manage data stored in db **Queries:  **a query is a command used to interact with a database **What are SQL statements?** - **Data Manipulation Language (DML):** Commands used to manipulate data within a database. - **INSERT INTO** : Adding new data records. - **UPDATE**: Modifying existing data records. - **DELETE FROM**: Removing unwanted or obsolete data. - **Select**: Retrieving specific data records. - **Sorting**: Arranging data records - **Filtering**: Extracting data records - **Joining**: Combining data from multiple tables - **Aggregation**: Calculating summary statistics - **Subqueries**: Embed queries within other queries. - **Stored** **Procedures**: Create reusable blocks of code - **Data Definition Language (DDL):** Defines and modifies the structure of db Objects - **Create:** Create objects - **Alter:** Modify existing object - **Drop:** delete existing object - **Truncate:** Removes all records from a table, but keep structures - **Data Control Language (DCL):** Control access to data within db - **Grant:** Give a user access - **Revoke:** Remove access from a user - **Transaction Control Language (TCL):** Manages Transaction within the db to ensure data integrity - **Commit:** Saves all changes made during transaction - **Rollback:** Undoes all changes made during the current transaction - **Save point:** Set a save point within a transaction **What is Database?** - **Database:** collection of stored data that can be retrieved as needed - *[**Database Management System:** Used to create, maintain, and access databases]* - *[**Database engine:** part of program that stores and retrieves data]* - **Database consist of:** - **Tables:** collection of related records - [**Fields (columns)**: single category of data to be stored] - *[**Records(rows):** collection of related fields in a database]* - *[**Primary key:** field that has unique identifier in a record and used to relate tables to others]* - **Individuals in DBMS:** - **DB designer:** design the DB - **DB dev:** Create the DB - *[**DB programmer:** write programs to access the DB or connect to other programs]* - *[**DB administrator:** manage DB within an org.]* - *[**Users:** Individuals who enter, update, and retrieve data]* **Advantages and Disadvantages of the DBMS Approach** - ***[Advantages:]*** - ***[Low level of redundancy:]*** - Faster response time. - Lower storage requirements. - Easier to secure. - Increased data accuracy. - ***[Disadvantages:]*** - *[Increased vulnerability (making backups essential).]* ***[Disadvantage of using File Management System:]*** - *[Inability to relate data across table]* **Database Classifications** - **Single-User Database System**: - Located on a **single computer**. - Designed to be used by **one user**. - Suitable for **personal applications** and **small businesses**. - **Multiuser Database System**: - Designed for access by **multiple users** (used in most business databases today). - **Client-Server Database Systems**: - Involves both **clients** (front end) and at least one **database server** (back end). - **N-Tier Database System**: - Has **more than two tiers**. - Additional tiers usually function as **middleware**. - Middleware provides a standardized interface for applications to interact with one or more databases and ensures **seamless access** and **management of data** across different systems. - Allows program code to be **separate** from the database. - Code can be divided into **many logical components**. - *[**Centralized Database System**s: ]* - *[The database is located on a **single computer**, such as a **server or mainframe**. ]* - Easier to manage but can create a single point of failure. - *[**Distributed Database Systems**: ]* - *[Data is **physically** **divided** among several computers connected by a network. ]* - Appears as a single database logically. - Enhances reliability and scalability but increases complexity. - **Disk-Based Database Systems**: - Data is stored on **physical storage** devices like hard drives. - Traditionally more common due to cost-effectiveness and practicality. - **In-Memory Database Systems (IMDBs):** - Data is stored directly in the **system's main memory** (RAM). - Significantly faster than disk-based databases. - Essential for high-performance needs and small-footprint, embedded applications. - Requires good backup procedures. **Module 1: Database Design and Implementation** - *[**Relational Database**: A database consisting of **tables** that contain data items linked through defined **relationships**.]* - *[**Entity**: Real-world object or concept represented in a diagram.]* - **Types**: - *[**Strong Entity**:]* - *[Independent and always has a **primary key**.]* - Represented by a **single rectangle**. - Relationship with other strong entities is shown using a **single diamond**. - *[**Weak Entity**:]* - *[Dependent on a **strong entity** for existence.]* - Lacks a primary key but uses a **partial discriminator key**. - Represented by a **double rectangle**. - Relationship with a strong entity is shown using a **double diamond**. - This relationship is known as an **identifying relationship**. - *[**Attributes:** a column in a table that holds specific data values for each instance of an entity. Also, represent **specific characteristics** or properties of the data in a table.]* - **Composite Attribute**: Composed of multiple simple attributes (e.g., address can be broken down into street, city, postal\_code). - **Single-Valued Attribute**: Holds a single value for each entity instance (e.g., date\_of\_birth). - **Multi-Valued Attribute**: Can hold multiple values for each entity instance (e.g., phone\_numbers). - **Derived Attribute**: Calculated from other attributes (e.g., age can be derived from date\_of\_birth). **Entity-Relationship Diagram (ERD) Symbols**  **Relationships** - **Link between entities** - **Types:** - **One-to-One Relationship**: Each entity in one table is linked to a single entity in another. - **Example:** A person and their passport. Each person has one passport, and each passport is assigned to one person. - **One-to-Many Relationship**: One entity in a table is linked to multiple entities in another table. - **Example:** A teacher and their students. One teacher can have many students, but each student has only one teacher. - **Many-to-Many Relationship**: Entities in both tables can have multiple relationships with each other. - **Example:** Students and courses. A student can enroll in multiple courses, and each course can have multiple students. This is typically implemented using a junction table. **Database Models** **1. Relational Model** - **Foundation**: Based on **set theory** and **relational algebra**. - **Structure**: Data is organized into **tables** (relations), with rows representing records and columns representing attributes. - **Relationships**: Defined using **foreign keys** to establish links between tables. - **Examples**: **MySQL**, **PostgreSQL**, **Oracle**, **SQL Server**. - **Strengths**: - **Strong data integrity** and **data consistency**. - **Flexibility** in structuring data across multiple tables. - **Powerful query capabilities** using SQL. - **Weaknesses**: - Can become **complex** for handling **large-scale** or **non-relational data** structures. **2. Hierarchical Model** - **Structure**: Organizes data in a **tree-like structure**, with each record having a **parent-child relationship**. - **Limitations**: - Supports only **one-to-many** relationships (each child has only one parent). - **Less flexible** compared to the relational model, especially for complex data relationships. - **Use Cases**: - **Legacy systems**. - Specific domains like **genealogy** or **document management**. **3. Network Model** - **Structure**: Similar to the hierarchical model but allows **many-to-many relationships**, making it more versatile. - **Complexity**: - **More complex** to design and manage than both the hierarchical and relational models. - Requires precise design for maintaining the network of relationships. - **Use Cases**: - **Specialized applications** where **complex relationships** between data entities are critical, such as **telecommunications** and **transportation systems**. **4. Object-Oriented Model** - **Structure**: Data is represented as **objects**, with associated **properties** (attributes) and **methods** (functions). - **Advantages**: - **Natural mapping** to object-oriented programming languages (e.g., Java, C++). - **Better handling** of **complex data types** like images, multimedia, and user-defined data structures. - **Examples**: - **Object-oriented databases (OODBMS)**. - Some **NoSQL databases** with object-oriented features, such as **MongoDB** and **Couchbase**. **5. NoSQL (Not Only SQL)** - **Diverse Models**: Includes various database types like: - **Document**: Stores data as **documents** (e.g., JSON, BSON). - **Key-value**: Data stored as **key-value pairs**. - **Graph**: Represents data as nodes and edges to manage **graph-based relationships**. - **Wide-column**: Data is stored in **columns** rather than rows, allowing for flexible schema. - **Characteristics**: - High **scalability** and **performance**, ideal for handling **large datasets**. - **Flexibility** in managing **unstructured** or **semi-structured data**. - **Examples**: - **Document**: **MongoDB**. - **Key-value**: **Redis**. - **Graph**: **Neo4j**. - **Wide-column**: **Cassandra**. - **Use Cases**: - **Big data** applications, **real-time analytics**, **content management**, and **social networks**.