Database Architecture and Design PDF

# Database Architecture ## Three Levels of Abstraction - **Schema:** Structure of database - **Users**: Interact with the database through different schema levels. - **External Schema:** View schema/sub schema, Logical schema/logical level/middle level, Internal schema/physical level. - *...

# Database Architecture ## Three Levels of Abstraction - **Schema:** Structure of database - **Users**: Interact with the database through different schema levels. - **External Schema:** View schema/sub schema, Logical schema/logical level/middle level, Internal schema/physical level. - **Conceptual Schema:** Represents the overall logical structure of the database, independent of how it is physically implemented. - **Physical Schema:** Defines how the data is physically stored and accessed. - **Harddisk Database:** Contains the actual data. - **Structure of Database (DB) Blueprint:** - **Designing:** ER diagram, Entity Relationship, Entities, Attributes, Relationships, Data types, Constraints, etc. - **Mapping:** Conceptual-Logical Mapping, Logical-Physical Mapping. - **Logical Level Data:** Independent of the physical schema. - **Physical Level Data:** Independent of the logical schema. ## Conceptual Design with ER Model - **Entity vs Attribute:** An entity is a real-world object that can be identified and distinguished from others, while attributes are properties or characteristics of these entities - **Entity vs Relationship:** An entity is an object that can be identified, while a relationship is a link between two or more entities. - **Binary vs Ternary Relationship:** A binary relationship involves two entities, while a ternary relationship involves three entities. - **Aggregation vs Ternary Relationship:** Aggregation is a special type of relationship where a relationship is treated as an entity, while a ternary relationship involves three separate entities. ### Example 1: Employees and Departments - **Binary Relationship:** An employee works in a department. - **Ternary Relationship:** An employee works in a department from a specific date. ### Example 2: Customers, Loans, and Branches - **Binary Relationship:** A customer borrows a loan from a branch. - **Ternary Relationship:** A customer borrows a loan with a specific amount. ## ER Model Additional Attributes - **High level Data model:** - **Entity:** Student, - **Relationship:** Student attends a school. ## ER Model - **Entity:** An object class, person, or rectangle - **Attribute:** Property of an entity - **Key Attribute:** One to one - **Composite Attribute:** One to many - **Multi-valued Attribute:** Many to one - **Derived Attribute:** Many to many - **Weak Entity:** Depends on a strong entity - **Strong Entity:** Not dependent - **PK (Primary Key):** Single rectangle - **No PK: ** Partial discriminant key (Double rectangle) - **Example:** Customers borrow a loan from a branch. ## Key Attribute vs Composite Attribute - **Key Attribute:** Unique identifier of an entity, such as ID. - **Composite Attribute:** Combining multiple attributes, such as a name (first + middle + last). ## Multivalued Attribute - **Example:** Student can have multiple phone numbers (A phone number is stored multiple times). - **Derived Attribute:** Can be derived from other attributes, such as age from birth date (it can be calculated, not an attribute). ## Relation - **Teacher** can **teach** **Students**: One to many - **Male** can **marry** **female**: One to one - **Scientist** can **invent** **Inventions**: Many to many - **Student** can **involve** into **Cause**: Many to many - **Employee** can be **assigned** to a **Project**: Many to many ## Database Application 1. Business 2. Healthcare 3. Financial 4. Education 5. Government 6. Social Media and Online Communities 7. Scientific and Engineering ## Data Models in DBMS - To design a database - **One to many relationships:** - Hierarchical data model - Network data model - **Many to many relationships:** - Entity Relationship model - Relational model - **Object-based data model:** - Semi-structured data model ### Example: Students and Courses - **Student** entity has attributes like ID, name, rollNo, etc. - **Course** entity has attributes like ID, name, etc. - **Enroll** relationship links students and courses, with attributes like courseID, courseName, etc. ### Example: Tables - **Student** table has three tuples, each with columns for ID, name, and marks. ### Example: Objects - **Student** object has attributes like ID, name, and marks. - **Course** object has attributes like ID, name, etc. ## Entity / Additional Attributes - **XML technologies:** Extensible Markup Language (XML) is a markup language designed to store and transport data. ## Centralized Architecture of DBMS - **Constraints in Relational Models:** Applied to data in a database to ensure data integrity and consistency. - **Primary Key:** Uniquely identifies each record in a table. - **Foreign Key:** Ensures that data in one table matches data in another table. - **Unique Constraint:** Ensures that all values in a column are unique. - **Not Null Constraint:** Ensures that a column cannot contain null values. - **Check Constraint:** Ensures that data in a column meets specific criteria. - **Default Constraint:** Specifies a default value for a column. ### Example: Employees - **Primary Key:** EID (Employee ID) - **Foreign Key:** CustomerID (must match a customer record) - **Unique Constraint:** sid_live_email (must be unique) - **Not Null Constraint:** Name - **Check Constraint:** Age (must be greater than or equal to 18) - **Default Constraint:** Age (default value of 25) ## Importance of Null Values 1. **Handling Null Values:** Handling instances where a value might not be available or is unknown. 2. **Avoiding Data Inconsistency:** Avoiding inconsistencies by ensuring that data is accurate and consistent across the database 3. **Improving Data Accuracy:** Ensuring that data is accurate by checking if data is not NULL. ## Relational Model - **The foundation of a relational database:** It's based on the idea of organizing data into tables with each table having rows and columns. - **Key components:** - **Tables (relations):** Data is stored in tables. - **Rows (tuples):** Each row represents a single record. - **Columns (attributes):** Each column represents a data field. - **Primary Key:** Uniquely identifies each row in a table. - **Foreign Key:** Links entities (relationships) - **ER Diagrams:** Graphical representations of relationships - **Normalization:** Optimizing a database design to reduce redundancy and improve data integrity. - **De-normalization:** Adding redundancy, sometimes for performance reasons. ## Constraints - **Rules:** Applied to data in a database to ensure data integrity and consistency. - **Types:** - **Primary Key Constraint (PKC):** Uniquely identifies each record in a table. - **Foreign Key Constraint (FKC):** Ensures that data in one table matches data in another table. - **Unique Constraint (Unique C):** Ensures that all values in a column are unique. - **Not Null Constraint (Not Null):** Ensures that a column cannot contain null values. - **Check Constraint (Check C):** Ensures that data in a column meets specific criteria. ## Relational Algebra and Relational Calculus - Two formal languages used to manipulate and query relational databases. ### Relational Algebra - **Procedural language:** Specifies the steps involved in retrieving the data. - **Operators** (actions): Select, Project, Union, Intersection, Difference, Cartesian Product, Join - **Results:** New relations (sets of tuples) ### Relational Calculus - **Declarative language:** Specifies the desired data but not how to retrieve it. - **Uses tuple variables:** To represent tuples in a relation. - **Types:** - **Tuple Relational Calculus (TRC):** Specifies the desired data using tuples. - **Domain Relational Calculus (DRC):** Specifies the desired data using domains. ## Schema - **The overall structure of a database:** Defines its organization, including the relationships between tables, indexes, views, and other database objects. ## DML Commands (Data Manipulation Language) - **Create:** Creates new tables. - **Insert:** Inserts data into tables. - **Select:** Retrieves data from tables. - **Update:** Modifies existing data in tables. - **Delete:** Removes data from tables.