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**: Adding new data records. - **Update**: Modifying existing data records. - **Delete**: 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 - **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 Query Language (DQL):** retrieves data from db - **Select: fetch data from one or more table** - **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 - **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). **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. - **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.