Customer Relationship Management Questions
Uploaded by
elliotalderson2611Customer Relationship Management Questions
Uploaded by
elliotalderson2611Common questions
Powered by AIA Database Administrator (DBA) is primarily responsible for ensuring database integrity, security, and availability. Key functions include database design, implementation, management of users and security, monitoring performance, backup and recovery planning, and fine-tuning the database functions. They are also responsible for setting up and testing new database management software before it goes live, and continuously updating the system to meet the needs of the evolving environment .
Aggregation in an E-R model is a higher-level abstraction used to express relationships among relationships, essentially treating a relationship between entities as an entity itself. This is useful in scenarios involving multi-level hierarchies. For example, in modeling a university database, aggregation lets us model a 'research project' involving 'professors' and 'students' by considering the 'project' relationship entity as a standalone entity participating in another relationship, like 'funding' from external organizations. Thus, it simplifies complex database designs and accommodates the representation of inter-relationship constraints .
Functional dependencies form the basis for formalizing relationships among attributes in normalization—the process of organizing database tables to reduce redundancy and improve integrity. A functional dependency between attributes indicates that the value of one attribute is determined by another. In normalization, decomposing relations reduces anomalies. For instance, a relation with attributes {Student ID, Course Code, Instructor} might be prone to update anomalies if instructor names are stored in multiple rows and then changed. By decomposing into two separate tables, one for {Student ID, Course Code} and another for {Course Code, Instructor}, we maintain data integrity and ensure modifications do not lead to anomalies .
Poor relational database design can lead to pitfalls such as redundancy, update anomalies, insertion anomalies, and deletion anomalies. Redundancy leads to unnecessary duplication across tables, consuming space and potentially leading to inconsistencies. An update anomaly occurs when changes in data require modifications to multiple rows, risking inconsistency if not updated synchronously. Insertion anomalies prevent new data insertion due to lack of a complete primary key. Deletion anomalies result in unintended data loss when deleting records. These issues are mitigated through normalization, which organizes attributes across tables to maintain functional dependencies and reduce redundancy. For example, decomposing a single table with many attributes into a well-designed schema can resolve these anomalies .
A SQL trigger is a procedural code that is automatically executed in response to certain events on a particular table or view. Triggers are important for tasks such as enforcing business rules, validating input data, maintaining audit trails, and synchronizing tables. For example, a trigger can be used to automatically log updates to a sensitive field or delete associated records in another table when a record is deleted. In a banking database, a trigger might ensure that transactions remain consistent by checking any modifications to account balances, such as preventing an overdraft before executing a withdrawal .
Query optimization is the process of choosing the most efficient execution strategy for SQL statements. It is crucial because it directly impacts the performance of database operations by minimizing resource usage and speeding up query response time. For large databases, optimized queries reduce the load on system resources. Techniques for optimization may include rewriting queries to leverage efficient access paths, using indexes, and selecting appropriate algorithms for join operations. Additionally, optimization ensures that databases handle complex and large workloads efficiently, improving overall system performance .
Data independence refers to the ability to change the schema at one level of a database system without having to change the schema at the next higher level. There are two types: physical and logical data independence. Physical data independence is concerned with the capacity to change the internal schema without having to change the conceptual schema, thus affecting the physical storage of data only. In contrast, logical data independence involves modifications to the conceptual schema, such as adding new fields or changing data types, without altering existing external schemas or application programs .
In an E-R model, a 'weak entity set' is an entity that cannot be uniquely identified by its own attributes alone and relies on a 'strong entity set' for identification. For example, a 'dependent' entity might be considered weak if it requires the 'employee' entity to which it relates for unique identification . On the other hand, a 'derived attribute' is an attribute that does not exist in the physical database but can be derived or calculated from other stored attributes. For instance, the 'age' of a person can be derived from the 'date of birth' attribute .
The ACID properties refer to Atomicity, Consistency, Isolation, and Durability, which are essential for ensuring reliable transaction processing in database systems. Atomicity ensures that all parts of a transaction are completed; otherwise, it is aborted. Consistency maintains the database state by ensuring that transactions lead the database from one valid state to another. Isolation ensures that transactions operate independently without interference, maintaining correctness notwithstanding concurrent execution. Finally, Durability guarantees that once a transaction is completed, its changes are permanent, even in the event of a system failure. Together, these properties maintain transaction integrity and safeguard the correctness and reliability of databases .
A Database Management System (DBMS) differs from a file-processing system primarily in four areas: data redundancy and inconsistency, data isolation, concurrent access anomalies, and security issues. DBMS reduces redundancy since it integrates various separate files into a single database, addressing redundancy and inconsistency issues. In contrast, a file-processing system isolates data, leading to inconsistencies due to duplicate data . Additionally, DBMS allows multiple users to access the database concurrently without conflicts, whereas file-processing systems may face concurrent access anomalies. Further, DBMS provides a way to enforce data integrity and security, which is typically more challenging in file-processing systems .