Midterm Detailed Review.docx

**Concept Explanations and Answers** **1. Examples of Databases (Vendors)** - **Examples**: Oracle, MySQL, Microsoft SQL Server, PostgreSQL. - **Explanation**: These are platforms used to create, manage, and manipulate databases. For example, **MySQL** is commonly used for web applica...

**Concept Explanations and Answers** **1. Examples of Databases (Vendors)** - **Examples**: Oracle, MySQL, Microsoft SQL Server, PostgreSQL. - **Explanation**: These are platforms used to create, manage, and manipulate databases. For example, **MySQL** is commonly used for web applications, while **Oracle** is more robust and used in large enterprises for mission-critical applications. **2. Difference Between Structured and Unstructured Data** - **Structured Data**: Highly organized in a fixed format, typically in rows and columns (like in a relational database). Example: a customer's name, phone number, and address stored in a database. - **Unstructured Data**: Does not have a predefined structure or format, making it harder to manage. Example: emails, videos, and social media posts. **3. What is Metadata?** - **Metadata**: Describes other data, providing information about the structure and attributes of the data. For instance, for a document, metadata might include its file size, creation date, and author. This helps users and systems understand the characteristics of the data without viewing its actual content. **4. Longest Phase of DB Development Process** - **Phase**: The maintenance and operation phase is typically the longest since databases require regular updates, monitoring, backups, and tuning to ensure they continue to meet business needs over time. It also involves resolving data integrity issues and performance optimization. **5. Bottom-Up vs. Top-Down Database Development** - **Bottom-Up**: In this approach, you start by focusing on small details, such as defining attributes for individual entities and relationships, and later organizing them into a broader structure. This is more suitable for projects where details are well-known at the start. - **Top-Down**: This starts with the overall structure and major components of the database, such as identifying major entities and relationships, and later adds specific details (e.g., attributes and keys). This is often used when designing large systems with unclear details at the beginning. **6. Five Key Words in SQL and Their Functions** - **SELECT**: Retrieves data from a table (e.g., SELECT \* FROM Customers gets all customer records). - **INSERT**: Adds new data to a table (e.g., INSERT INTO Orders (OrderID, CustomerID) VALUES (101, 1)). - **UPDATE**: Modifies existing data (e.g., UPDATE Employees SET Salary = 50000 WHERE EmployeeID = 1). - **DELETE**: Removes data from a table (e.g., DELETE FROM Orders WHERE OrderID = 101). - **CREATE**: Defines new database objects, like tables (e.g., CREATE TABLE Employees (EmployeeID INT, Name VARCHAR(50))). **7. DBMS, SQL, QBE, DDL, DML, ERD Definitions** - **DBMS**: A system used to store, retrieve, and manage data in databases (e.g., MySQL, SQL Server). - **SQL**: A language used to interact with a DBMS (e.g., for querying or updating data). - **QBE (Query by Example)**: A visual interface in databases where users build queries by providing examples of what they want to retrieve without writing SQL code. - **DDL (Data Definition Language)**: Defines the structure of the database (e.g., creating, altering, or dropping tables). - **DML (Data Manipulation Language)**: Manipulates data within the database (e.g., SELECT, INSERT, UPDATE, DELETE). - **ERD (Entity-Relationship Diagram)**: A diagram that visually represents the entities in a database and the relationships between them. **8. Three Levels of the ANSI-SPARC Schema** - **External Level**: Represents individual user views or specific application perspectives on the data. For example, an HR system might view employee data differently than a payroll system. - **Conceptual Level**: The logical structure of the entire database, including entities, attributes, and relationships. This is the overall organization of the database. - **Internal Level**: The physical storage of the database, including how data is actually stored on disk. **9. Instance of an Entity** - **Instance**: A specific example of an entity. For example, if \"Employee\" is an entity, a single employee record (like \"John Doe\") is an instance of that entity. Instances represent real-world objects. **10. Problems Redundancy Creates** - **Issues**: Data redundancy means that the same piece of data is stored in multiple places, which can lead to: - **Inconsistent Data**: If one copy of the data is updated, but others are not, it can lead to inconsistencies. - **Increased Storage Costs**: Storing the same data multiple times wastes space. - **Complex Data Management**: Redundant data complicates updating, deleting, or inserting new data. **1. Exclusive Subtypes (Disjoint Subtypes)** **Definition**: Exclusive subtypes, also known as disjoint subtypes, are subtypes in which each instance of the supertype can belong to **only one** of the possible subtypes. **Explanation**: - In a specialization/generalization hierarchy, a supertype entity can be divided into multiple subtypes based on certain distinguishing characteristics. - With exclusive subtypes, an instance of the supertype **cannot** be a member of more than one subtype. - This enforces strict categorization where each entity instance fits into one and only one category. **Example**: - **Employee** is a supertype entity with subtypes **FullTimeEmployee** and **PartTimeEmployee**. - An individual employee can be either a full-time employee or a part-time employee, **but not both**. - This exclusivity ensures clear classification of employees based on their employment status. **2. Inclusive Subtypes (Overlapping Subtypes)** **Definition**: Inclusive subtypes, also known as overlapping subtypes, are subtypes where an instance of the supertype can belong to **one or more** subtypes simultaneously. **Explanation**: - In this scenario, subtypes are not mutually exclusive; an instance can satisfy the criteria for multiple subtypes. - This allows for more flexible classification where entities can have multiple characteristics. **Example**: - **Vehicle** is a supertype with subtypes **ElectricVehicle** and **HybridVehicle**. - A vehicle can be both electric and hybrid, meaning it can belong to both subtypes. - This overlapping enables representation of vehicles that share multiple features. **3. Specialization** **Definition**: Specialization is the process of defining subtypes of a supertype and forming supertype/subtype relationships. **Explanation**: - It involves identifying distinguishing characteristics that separate entities into more specific categories. - Specialization allows for inheritance of attributes from the supertype to subtypes, reducing redundancy. **Example**: - From the **Person** supertype, we can specialize into **Student** and **Instructor** subtypes. - **Student** might have attributes like Major and GPA. - **Instructor** might have attributes like Department and Salary. - Both subtypes inherit common attributes from **Person** such as Name and DateOfBirth. **4. Total Specialization** **Definition**: Total specialization indicates that every instance of the supertype **must** be a member of at least one subtype. **Explanation**: - There are no instances of the supertype that do not belong to a subtype. - Represented in ER diagrams by a double line from the supertype to the circle connecting the subtypes. **Example**: - In an **Account** entity with subtypes **SavingsAccount** and **CheckingAccount**: - Every account **must** be either a savings account, a checking account, or both (if overlapping). - This ensures that all accounts are classified appropriately. **5. Partial Specialization** **Definition**: Partial specialization means that an instance of the supertype may or may not belong to any subtype. **Explanation**: - Some instances exist only at the supertype level without being part of any subtype. - Represented in ER diagrams by a single line from the supertype to the circle connecting the subtypes. **Example**: - **Employee** entity with subtypes **Manager** and **Engineer**: - Some employees might not be managers or engineers (e.g., **HR staff**). - This allows for entities that don\'t fit into any of the defined subtypes. **6. Entity** **Definition**: An entity is an object or concept about which data is stored. **Explanation**: - Represents a real-world object or concept with an independent existence in the database. - Each entity has attributes that describe its properties. **Example**: - **Book** as an entity in a library database. - Attributes: ISBN, Title, Author, Publisher, YearPublished. **7. Strong Entity** **Definition**: A strong entity is an entity that can exist independently of other entities and has a unique identifier (primary key). **Explanation**: - It does not rely on any other entity for its existence. - Its primary key is not derived from any other entity. **Example**: - **Customer** entity with a unique CustomerID. - Each customer exists independently in the system. **8. Weak Entity** **Definition**: A weak entity depends on a strong entity for its existence and identification. **Explanation**: - Cannot be uniquely identified by its own attributes alone. - Its primary key is partially or fully derived from the parent entity. - Typically represented with a double rectangle in ER diagrams. **Example**: - **Dependent** entity in an insurance database: - Depends on the **Employee** entity. - Attributes might include DependentName, but it needs EmployeeID to uniquely identify each dependent. **9. Associative Entity** **Definition**: An associative entity is a bridge between two entities in a many-to-many (M ) relationship. **Explanation**: - Converts an M relationship into two one-to-many (1 ) relationships. - May have attributes of its own. **Example**: - **Enrollment** entity linking **Student** and **Course** entities. - Attributes: EnrollmentDate, Grade. - Allows tracking of which students are enrolled in which courses. **10. Relationship** **Definition**: A relationship is an association among entities. **Explanation**: - Defines how entities are connected or interact with each other. - Can have attributes that describe the nature of the association. **Example**: - **Orders** relationship between **Customer** and **Product** entities. - Attributes of the relationship might include OrderDate, Quantity. **11. Degree** **Definition**: Degree refers to the number of entities involved in a relationship. **Explanation**: - **Unary (Degree 1)**: Involves one entity (recursive relationship). - **Binary (Degree 2)**: Involves two entities. - **Ternary (Degree 3)**: Involves three entities. **Example**: - **Binary Relationship**: **Employee** works in **Department**. - **Ternary Relationship**: **Supplier** supplies **Product** to a **Warehouse**. **12. Cardinality** **Definition**: Cardinality specifies the number of instances of one entity that can be associated with instances of another entity. **Explanation**: - Defines the minimum and maximum number of times an instance of one entity can be associated with instances of another entity. - Common cardinalities: - **One-to-One (1:1)** - **One-to-Many (1** **)** - **Many-to-Many (M** **)** **Example**: - **One-to-Many (1** **)**: A **Teacher** can teach many **Classes**, but each class is taught by only one teacher. **13. Recursive Relationship** **Definition**: A recursive relationship is a relationship between instances of the same entity. **Explanation**: - Models situations where entities are related to other entities of the same type. - Useful for hierarchies or networks within an entity. **Example**: - **Employee** entity with a recursive relationship **Manages**. - An employee can manage other employees. - Attributes might include ManagerID referencing EmployeeID. **14. Binary Relationship** **Definition**: A binary relationship involves two entities. **Explanation**: - The most common type of relationship in databases. - Can be any cardinality (1:1, 1 , M ). **Example**: - **Customer** places **Order**. - Each order is placed by one customer, and a customer can place many orders. **15. Ternary Relationship** **Definition**: A ternary relationship involves three entities. **Explanation**: - Represents a complex relationship where all three entities interact together. - Cannot be decomposed into binary relationships without losing some information. **Example**: - **Project**, **Employee**, and **Role** in an **Assignment** relationship. - An employee is assigned to a project with a specific role. **16. Keys** **Primary Key** **Definition**: An attribute or a set of attributes that uniquely identifies each instance of an entity. **Explanation**: - Ensures that each record can be uniquely identified. - Cannot be null. **Example**: - **StudentID** in a **Student** entity. **Foreign Key** **Definition**: An attribute in one entity that is a primary key in another entity, used to establish a relationship between the two entities. **Explanation**: - Enforces referential integrity between tables. - Allows navigation from one entity to related entities. **Example**: - **CustomerID** in an **Order** entity referencing **CustomerID** in **Customer** entity. **Candidate Key** **Definition**: An attribute or a set of attributes that could serve as a primary key. **Explanation**: - Must be unique and not null. - There can be multiple candidate keys in an entity. **Example**: - In an **Employee** entity: - **EmployeeID** and **SSN** (Social Security Number) could both be candidate keys. **Partial Key** **Definition**: An attribute in a weak entity that, when combined with the primary key of its related strong entity, uniquely identifies instances of the weak entity. **Explanation**: - Cannot uniquely identify a record on its own. - Used in weak entities dependent on strong entities. **Example**: - **PaymentNumber** in a **Payment** entity that is dependent on **Order**. - Combined with **OrderID**, it uniquely identifies each payment. **17. Attribute** **Definition**: An attribute is a property or characteristic of an entity or a relationship. **Explanation**: - Describes details about entities or relationships. - Attributes have data types and can have constraints. **Multivalued Attribute** **Definition**: An attribute that can have multiple values for a single entity instance. **Explanation**: - Represented in ER diagrams with double ovals. - Requires special handling in relational databases. **Example**: - **Skills** attribute in an **Employee** entity. - An employee can have multiple skills (e.g., programming, project management). **Composite Attribute** **Definition**: An attribute that can be subdivided into other attributes. **Explanation**: - The components of a composite attribute can be useful individually. - In ER diagrams, the composite attribute is connected to its components. **Example**: - **Address** attribute subdivided into: - Street, City, State, ZipCode. **Derived Attribute** **Definition**: An attribute whose value is calculated from other attributes. **Explanation**: - Does not need to be stored physically in the database. - Represented in ER diagrams with a dashed oval. **Example**: - **Age** derived from DateOfBirth: - Age = CurrentDate - DateOfBirth. **18. Properties of Relations** **1. Each Intersection of Row and Column Is Atomic** **Explanation**: - Each cell contains only a single value. - No repeating groups or arrays. **Example**: - In a **Customer** table, the PhoneNumber field should have only one number per record. - If a customer has multiple phone numbers, use separate rows or a related table. **2. Order of Rows Is Insignificant** **Explanation**: - The sequence of rows does not impact the meaning of the data. - Rows can be retrieved in any order. **Example**: - A query without an ORDER BY clause may return rows in any sequence. **3. Order of Columns Is Insignificant** **Explanation**: - Columns can be arranged in any order without affecting the data\'s meaning. - Queries can select columns in any sequence. **Example**: - Selecting Name, Address is equivalent in meaning to selecting Address, Name. **4. Each Relation Has a Unique Name** **Explanation**: - Table names must be distinct within a database. - Prevents confusion and ensures clarity in queries. **Example**: - Cannot have two tables named **Employee**; one might be **Employee** and the other **EmployeeArchive**. **5. Each Row Is Unique** **Explanation**: - No two rows in a table are identical in all columns. - Ensures data integrity and proper identification of records. **Example**: - In an **Order** table, each OrderID must be unique. **6. Each Attribute Has a Unique Name** **Explanation**: - Column names must be unique within a table. - Avoids ambiguity when referencing columns. **Example**: - A table cannot have two columns both named Date. - One could be OrderDate and the other ShipDate. **Crow's foot notation**  **Binary Relationships with Crow's Foot Notation** **Crow\'s Foot Notation** is used to visually represent relationships between entities in a database. Here are a couple of examples: **Example 1: Customer and Order** - **Entities**: - **Customer**: (CustomerID, Name) - **Order**: (OrderID, OrderDate, CustomerID) - **Relationship**: One-to-Many (1 ) - **Cardinality**: One customer can place many orders, but each order is placed by only one customer. **How to Read Crow's Foot Notation**: - The **crow\'s foot symbol** at the end of the line near the \"Order\" entity indicates that **many** orders are related to one customer. - The **single line** at the \"Customer\" side indicates that **one** customer can place many orders. **Example 2: Employee and Department** - **Entities**: - **Employee**: (EmployeeID, Name, DeptID) - **Department**: (DeptID, DeptName) - **Relationship**: Many-to-One (N:1) - **Cardinality**: Many employees can work in one department, but each employee works in only one department. **How to Read Crow's Foot Notation**: - The **crow's foot symbol** on the \"Employee\" side shows that **many employees** can work in **one department**. - The **single line** at the \"Department\" side shows that each department can have **one or more employees**. **Binary Relationships with Chen's Notation** In **Chen's Notation**, the relationships between entities are represented with diamonds, and the entities themselves are represented as rectangles. The cardinalities are shown with numbers or symbols at the ends of the connecting lines. **Example 1: Customer and Order** - **Entities**: - **Customer**: (CustomerID, Name) - **Order**: (OrderID, OrderDate, CustomerID) - **Relationship**: One-to-Many (1 ) - **Cardinality**: A customer can place many orders, but each order is placed by one customer. **How to Read Chen's Notation**: - In the relationship diamond between **Customer** and **Order**, you'll see a \"1\" near Customer and an \"N\" near Order, showing that one customer can place many orders. - The **rectangles** represent entities, and the **diamond** represents the \"places\" relationship. **Example 2: Employee and Department** - **Entities**: - **Employee**: (EmployeeID, Name, DeptID) - **Department**: (DeptID, DeptName) - **Relationship**: Many-to-One (N:1) - **Cardinality**: Many employees work in one department, but each employee works in only one department. **How to Read Chen's Notation**: - The diamond between **Employee** and **Department** has \"N\" on the Employee side and \"1\" on the Department side, indicating that many employees are assigned to one department. **Summary of Crow's Foot vs. Chen's Notation:** - **Crow's Foot Notation**: Uses the crow's foot symbol to represent \"many\" and a single line for \"one.\" It's popular because it visually simplifies complex relationships. - **Chen's Notation**: Uses numbers or symbols (1, N, etc.) to show cardinality directly on the lines connecting entities and the relationship diamond. Both notations are useful, but **Crow's Foot** is more commonly used in modern ER diagrams due to its clearer visual representation of relationships and cardinality. https://www.gleek.io/blog/uml-vs-erd