assf

Q1. What is a foreign key? What is referential integrity?
Foreign keys are identifiers that enable a dependent relation (on the many side of a relationship) to refer to its parent relation (on the one side of the relationship).
Referential Integrity is a rule stating that any foreign key value (on the relation of the many side) MUST match a primary key value in the relation of the one side. (Or the foreign key can be null)

Q2. How do you map a many-to-many relationship into relations?
A many-to-many relationship is represented by creating a separate relation. The primary key of this relation is a composite key, consisting of the primary key of each of the entities that participate in the relationship.
Q3. What is the purpose of data normalization?
The purpose of normalization is to derive well-structured relations that are free of anomalies that would otherwise result when the relations are updated or modified. Following are some of the main goals of normalization:
1. Minimize data redundancy, thereby avoiding anomalies and conserving storage space
2. Simplify the enforcement of referential integrity constraints
3. Make it easier to maintain data (insert, update, and delete)
4. Provide a better design that is an improved representation of the real world and a stronger basis for future growth are removed.
The purpose of normalization is to derive well-structured relations that are free of anomalies that would otherwise result when the relations are updated or modified.
Q4. What is transitive dependency? Which normal form doesn't have any transitive dependencies?
A transitive dependency in a relation is a functional dependency between the primary key and one or more nonkey attributes that are dependent on the primary key via another nonkey attribute.

Transitive dependency is when two 3NF relations are merged to form a single relation, transitive dependencies may result. Relations in third normal form (3NF) contain no transitive dependency



Explain the difference between conceptual, logical, and physical database design. 
A conceptual data model identifies the highest-level relationships between the different entities without using a particular data model or DBMS. It is a permanent description of the database requirements and has a clear method to convert from high-level model to relational model.
Logical data design is the process of transforming the conceptual data model into a logical data model and describes the data in as much detail as possible, without regard to how they will be physically implemented in the database. The resulting databases must meet user needs for data sharing, flexibility, and ease of access.
Physical data model translates the logical description of data into the technical specifications for storing and retrieving data. It represents how the model will be built in the database by creating a design for storing data that will provide adequate performance and ensure database integrity.

What is denormalization? Discuss the benefits and costs of denormalization.
Denormalization is the process of transforming normalized relations into non-normalized physical record specifications. The benefit of denormalization is that it can improve performance by reducing a number necessary join queries and tables.
However, there are costs associated with denormalization due to data duplication, in terms of wasted storage space resulting in data/integrity and consistency threats.
Explain indexed file organization. When would you use indices?
In an indexed file organization, the records are stored either sequentially or nonsequentially, and an index is created that allows the application software to locate individual records. Each index entry matches a key value with one or more records. An index can point to unique records or to potentially more than one record.
Indexes should be used generously for databases intended primarily to support data retrievals, such as for decision support and data warehouse applications. Indexes should be used sensibly for databases that support transaction processing and other applications with heavy updating requirements, because the indexes impose additional overhead.
What is clustering? Discuss the advantages of clustering. 
Clustering is related records from different tables stored together in the same disk area. The advantages of clustering is improving the performance of join operations to provide higher availability, reliability, and scalability than can be obtained by using a single system. Moreover, it reduces the time to access related records compared to the normal allocation of different files to different areas of a disk.