1. What components should an implementation-ready data model contain?

An entity is a person, place, thing, concept, or event about which data will be collected and stored •
An attribute is a characteristic of an entity • A relationship describes an association among entities •
The following are three different types of relationships: • One-to-many (1:M or 1..*) relationship •
Many-to-many (M:N or *..*) relationship • One-to-one (1:1 or 1..1) relationship. • A constraint is a
restriction placed on the data • Constraints help ensure data integrity •Constraints are normally
expressed in the form of rules.
2. What do business rules require to be effective?
A business rule is a brief, precise, and unambiguous description of a policy, procedure, or principle
within a specific organization • They apply to any organization that stores and uses data to generate
information • Business rules are used to define entities, attributes, relationships, and constraints •
They must be easy to understand and widely disseminated.
3. What are the sources of business rules, and what is the database designer's role with regard to
business rules?
The main sources of business rules are company managers, policy makers, department managers,
and written documentation such as company procedures. Business rules are essential to database
design due to the following reasons: • It helps to standardize the company’s view of data • It can be
a communication tool between users and designers • It allows the designer to understand the
nature, role, and scope of the data • It allows the designer to understand business processes • It
allows the designer to develop appropriate relationship participation rules and constraints and to
create an accurate data model
4. Describe the three parts involved in any SQL-based relational database application.
- User Interface – Allows users to interact with data, often by generating SQL automatically; it can be
vendor-designed or customized. Each interface is a product of the software vendor’s idea of
meaningful interaction with the data. - Tables – Data is organized into independent tables, related
through shared attributes, and presented in an understandable format. The tables simply “present”
the data to the end user in a way that is easy to understand. Each table is independent. Rows in
different tables are related by common values in common attributes. - SQL Engine – Part of the
DBMS, it executes all SQL commands behind the scenes, enabling data management without
requiring users to know how the operations are performed. It processes all user requests largely
behind the scenes and without the end user’s knowledge, and is said to be a declarative language
that tells what must be done but not how.
5. Describe the three basic characteristics of Big Data databases.
-Volume – Refers to the massive amount of data being generated and stored, especially due to the
rise of the Internet and social media, reaching petabyte scales.
-Velocity – Describes both the rapid growth of data and the need to process it quickly to produce
timely insights, driven by fast-moving data sources like social media, sensors, and e-commerce. A
company's business response time is reflective of the velocity of their Big Data database storage and
processing.
-Variety – Highlights the diverse formats of data collected (e.g., text, images, video, sensor data),
much of which isn’t easily handled by traditional relational databases. Variety refers to the fact that
data is collected in multiple data formats
This framework reflects the growing scale and complexity of data businesses manage today, and
illustrates what companies now know, that the amount of data being collected in their databases has
been growing exponentially in size and complexity.
6. Describe what metadata are and what value they provide to the database system
Metadata, or data about data, through which the end-user data is integrated and managed.
Describes the data characteristics and the set of relationships that links the data found within the
database. The metadata provide information that complements and expands the value and use of
the data. In short, metadata present a more complete picture of the data in the database. It is
described as a collection of self-describing data.
7. What are the advantages of having the DBMS between the end user's applications and the
database? The DBMS presents the end user with a single, integrated view of the data in the
database. Advantages: • Improved data sharing • Improved data security • Better data integration •
Minimized data inconsistency • Improved data access • Improved decision making • Increased end-
user productivity
8. Discuss some considerations when designing a database.
Database design refers to the activities that focus on the design of the database structure that will
be used to store and manage end-user data • Designing appropriate data repositories of integrated
information using the two-dimensional table structures found in most databases is a process of
decomposition • The integrated data must be decomposed properly into its constituent parts • A
well-designed database facilitates data management and generates accurate and valuable
information • A poorly designed database causes difficult-to-trace errors that may lead to poor
decision making.
9. What are the problems associated with file systems? How do they challenge the types of
information that can be created from the data as well as the accuracy of the information?
The problems associated with file systems can be created from the data as well as the accuracy of
the information:
1.Lengthy Development Time – Even simple tasks require extensive programming, slowing
development. With the older file systems, programmers had to specify what must be done and how
to do it. 2.Slow Responses – Ad hoc queries aren't possible, so users must wait days or weeks for
custom reports.
3.Complex Administration – Managing many separate files and their individual programs becomes
increasingly difficult and inefficient. The problem is compounded by the fact that each department in
the organization “owns” its data by creating its own files.
4.Poor Security & Limited Sharing – File systems lack strong security and make safe data sharing
challenging, especially across departments or locations. In terms of spreadsheet data, they are not
always used, and even when they are, they are insufficient for robust data sharing among users. In
terms of creating data management and reporting programs, security and data-sharing features are
difficult to program and consequently are often omitted from a file system environment.
5. Extensive programming- Making changes to an existing file structure can be difficult in file system
environment.
10. Discuss any three functions performed by the DBMS that guarantee the integrity and consistency
of the data in the database.
-Data Dictionary Management – Stores metadata (data about data), allowing programs to access
data without hardcoding relationships. The DBMS stores definitions of data elements and their
relationships in a data dictionary.
-Data Storage Management – Manages complex data storage needs, including data, forms, reports,
and multimedia, and supports performance tuning through efficient use of multiple storage devices.
-Data Transformation and Presentation – Converts data between physical and logical formats,
presenting it in a user-friendly way.
-Security Management – Controls user access, ensuring data privacy and restricting operations (read,
write, delete, modify) based on permissions—especially critical in multiuser environments.
11. What is a key and how is it important in a relational model?
• A key consists of one or more attributes that determine other attributes • Keys are important
because they are used to ensure that each row in a table is uniquely identifiable • They are also used
to establish relationships among tables and to ensure the integrity of the data. The role of a key is
based on the concept of determination, which is the state in which knowing the value of one
attribute helps to determine the value of another
12. Define entity integrity. What are the two requirements to ensure entity integrity?
Entity integrity is the condition in which each row in the table has its own known, unique identity.
Requirement: All primary key entries are unique, and no part of a primary key may be null. Purpose:
Each row will have a known, unique identity, and foreign key values can properly reference primary
key values. Example: No invoice can have a duplicate number, nor can it be null; in short, all invoices
are uniquely identified by their invoice number.
13. Describe the use of null values in a database.
• A null is the absence of any data value, and it is never allowed in any part of a primary key. A null
could represent any of the following: • An unknown attribute value • A known, but missing, attribute
value • A “not applicable” condition.
14. Describe the use of the INTERSECT operator.
INTERSECT is an operator used to yield only the rows that are common to two union compatible
tables. As with UNION, the tables must be union-compatible to yield valid results. For example, you
cannot use INTERSECT if one of the attributes is numeric and one is character-based. For the rows to
be considered the same in both tables and appear in the result of the INTERSECT, the entire rows
must be exact duplicates.
15. Define an index. Explain the role of indexes in a relational database.
An index is an orderly arrangement to logically access rows in a table • The index key is the index’s
reference point that leads to data location identified by the key • In a unique index, the index key
can have only one pointer value associated with it • A table can have many indexes, but each index is
associated with only one table • The index key can have multiple attributes
16. Explain multivalued attributes with the help of examples. How are multivalued attributes
indicated in the Chen Entity Relationship model?
Multivalued attributes are attributes that have many values • Implementing multivalued attributes •
Create several new attributes, one for each component of the original multivalued attribute • Create
a new entity composed of the original multivalued attribute’s components. A Multivalued Attribute
in An Entity ex. A car’s color may be subdivided into many colors for the roof, body, and trim.
17. What is a weak relationship? Provide an example
Weak (Non-identifying) Relationships • A weak relationship exists if the primary key of the related
entity does not contain a primary key component of the parent entity. By default, relationships are
established by having the primary key of the parent entity appear as a foreign key (FK) on the related
entity (also known as the child entity. suppose the 1:M relationship between COURSE and CLASS is
defined as: COURSE (CRS_CODE, DEPT_CODE, CRS_DESCRIPTION, CRS_CREDIT) CLASS (CLASS_CODE,
CRS_CODE, CLASS_SECTION, CLASS_TIME, ROOM_CODE, PROF_NUM). In this example, the CLASS
primary key did not inherit a primary key component from the COURSE entity. In this case, a weak
relationship exists between COURSE and CLASS because CRS_CODE (the primary key of the parent
entity) is only a foreign key in the CLASS entity.
18. Explain mandatory participation in an entity relationship.
Participation in an entity relationship is either optional or mandatory. Recall that relationships are
bidirectional; that is, they operate in both directions. Mandatory participation means that one entity
occurrence requires a corresponding entity occurrence in a particular relationship. If no optionality
symbol is depicted with the entity, the entity is assumed to exist in a mandatory relationship with
the related entity. The existence of a mandatory relationship indicates that the minimum cardinality
is at least 1 for the mandatory entity.
19. What is a ternary relationship? Provide some business rules examples that specify the need for a
ternary or higher-order relationship.
A ternary relationship implies an association among three different entities. • A DOCTOR writes one
or more PRESCRIPTIONs. • A PATIENT may receive one or more PRESCRIPTIONs. • A DRUG may
appear in one or more PRESCRIPTIONs. (To simplify this example, assume that the business rule
states that each prescription contains only one drug. In short, if a doctor prescribes more than one
drug, a separate prescription must be written for each drug.)
20. Explain recursive relationships with the help of an example
A recursive relationship is a relationship within a single entity type. • A recursive relationship can
exist between occurrences of the same entity set • Naturally, such a condition is found within a
unary relationship • One common pitfall when working with unary relationships is to confuse
participation with referential integrity • Similar because they are both implemented through
constraints on the same set of attributes. An entity (or record) in a table has a relationship with
another entity in the same table. Imagine a company where:
a. Each employee has a manager.
b. But the manager is also an employee.
21. Explain normalization and its different forms
Normalization is a process for evaluating and correcting table structures to minimize data
redundancies • It reduces the likelihood of data anomalies • Assigns attributes to tables based on
determination • Normalization works through a series of stages called normal forms and the first
three are described as follows: 1. First normal form (1NF) 2. Second normal form (2NF) 3. Third
normal form (3NF).
The term 1NF describes the tabular format in which the following occur: • All key attributes are
defined • There are no repeating groups in the table • All attributes are dependent on the primary
key • All relational tables satisfy 1NF requirements
Conversion to 2NF occurs only when the 1NF has a composite primary key • If the 1NF has a single-
attribute primary key, then the table is automatically in 2NF • The 1NF-to-2NF conversion is simple,
you take the following steps: • Step 1: Make new tables to eliminate partial dependencies • Step 2:
Reassign corresponding dependent attributes
• A table is in 2NF under the following circumstances : • When it is in 1NF • When it includes no
partial dependencies • The data anomalies created by the database organization are easily
eliminated by completing the following two steps: • Step 1: Make new tables to eliminate transitive
dependencies • Step 2: Reassign corresponding dependent attributes • A table is in 3NF under the
following circumstances: • When it is in 2NF • When it contains no transitive dependencies
22. What steps are involved in the conversion to the third normal form?
• Step 1: Make new tables to eliminate transitive dependencies. For every transitive dependency,
write a copy of its determinant as a primary key for a new table. A determinant is any attribute
whose value determines other values within a row. If you have three different transitive
dependencies, you will have three different determinants. As with the conversion to 2NF, it is
important that the determinant remain in the original table to serve as a foreign key.
• Step 2: Reassign corresponding dependent attributes. identify the attributes that are dependent on
each determinant identified in Step 1. Place the dependent attributes in the new tables with their
determinants and remove them from their original tables.
23. What is transaction isolation and why it is important?
Isolation means that the data used during the execution of a transaction cannot be used by a second
transaction until the first one is completed. In other words, if transaction T1 is being executed and is
using the data item X, that data item cannot be accessed by any other transaction until T1 ends. This
property is particularly useful in multiuser database environments because several users can access
and update the database at the same time. Different isolation levels (e.g., read uncommitted, read
committed, repeatable read, serializable) control how much interaction is allowed between
transactions, balancing performance and consistency. It is important because it maintains data
consistency by preventing issues dirty reads, non-repeatable reads, and phantom reads, helping
avoid data corruption and unreliable query results.
24. How does a shared/exclusive lock schema increase the lock manager's overhead?
An exclusive lock exists when access is reserved specifically for the transaction that locked the
object. The exclusive lock must be used when the potential for conflict exists. A shared lock exists
when concurrent transactions are granted read access on the basis of a common lock. A shared lock
produces no conflict as long as all the concurrent transactions are read-only. A shared lock is issued
when a transaction wants to read data from the database and no exclusive lock is held on that data
item. An exclusive lock is issued when a transaction wants to update (write) a data item and no locks
are currently held on that data item by any other transaction. Using the shared/exclusive locking
concept, a lock can have three states: unlocked, shared (read), and exclusive (write).
25. What are the three basic techniques to control deadlocks?
A deadlock occurs when two transactions wait indefinitely for each other to unlock data
-Deadlock Prevention: Ensure deadlocks cannot occur by designing the system to avoid the
necessary conditions (e.g., requiring transactions to acquire all locks at once or using a strict
ordering of resource acquisition).
-Deadlock Detection: Allow deadlocks to occur, but periodically check for them (e.g., using a wait-for
graph) and resolve them by aborting one or more transactions to break the cycle.
-Deadlock Avoidance: Use runtime strategies to prevent deadlocks by analyzing transaction resource
needs in advance (e.g., using schemes like wait-die or wound-wait to manage lock requests based on
transaction timestamps).
The choice of which deadlock control method to use depends on the database environment
26. What are database checkpoints?
Database checkpoints are operations in which the DBMS writes all of its updated buffers in memory
to disk. While this is happening, the DBMS does not execute any other requests. A checkpoint
operation is also registered in the transaction log. As a result, the physical database and the
transaction log will be in sync. This synchronization is required because update operations update
the copy of the data in the buffers and not in the physical database. Checkpoints are automatically
and periodically executed by the DBMS according to certain operational parameters but can also be
executed explicitly (as part of a database transaction statement) or implicitly (as part of a database
backup operation).
27. How do transaction recovery procedures use the deferred-write and write-through techniques to
recover transactions?
Transaction recovery procedures generally make use of the following:
• Deferred-write technique or deferred update • Transaction operations do not immediately update
the physical database • Only transaction log is updated. Changes are saved in memory and written to
disk only after a transaction commits. Uncommitted transactions require no undo during recovery;
committed ones are redone using the log.
• Write-through technique or immediate update • The database is immediately updated by
transaction operations during transaction’s execution. Changes are written to disk immediately. If a
transaction fails, changes are undone using the log. Committed changes don’t need to be redone.
Both methods use transaction logs to maintain database consistency after failures.
28. What are the modes that an optimizer can create and describe query optimization?
The query optimizer can operate in one of two modes:
• A rule-based optimizer uses rules and points to determine the best approach to execute a query •
The rules assign a “fixed cost” to each SQL operation
• A cost-based optimizer uses algorithms based on statistics about the objects being accessed to determine the
best approach to execute a query
•Hybrid Mode: Combines both approaches, using rules when stats are missing.
Query optimization involves analyzing a query, evaluating multiple execution plans, and choosing the
one with the lowest resource cost (CPU, I/O, memory). It's essential for efficient execution, especially
in large or complex databases.
29. Your management team wants to know why they need to optimize a DBMS with SQL performance
tuning, even though they automatically optimize SQL queries.
SQL performance tuning is evaluated from the client perspective. • Most current-generation
relational DBMSs perform automatic query optimization at the server end. • Most SQL performance
optimization techniques are DBMS-specific and, therefore, are rarely portable, even across different
versions of the same DBMS. Part of the reason for this behavior is the constant advancement in
database technologies.
The DBMS uses general optimization techniques rather than focusing on specific techniques dictated
by the special circumstances of the query execution.) A poorly written SQL query can, and usually
will, bring the database system to its knees from a performance point of view. The majority of
current database performance problems are related to poorly written SQL code. Therefore, although
a DBMS provides general optimizing services, a carefully written query almost always outperforms a
poorly written one.