Consider a fully normalized relational database schema in Second Normal Form (2NF) with two tables:
- STUDENTS(StudentID, StudentName, ClassID)
- CLASSES(ClassID, CourseTitle, Venue)
Under database maintenance guidelines, you are requested to perform a 'De-normalization' step to convert these tables back into a single, flat First Normal Form (1NF) relation. What is the correct flat schema representation and what specific anomalies will be reintroduced into the design?
💻 sql Snippet
-- De-normalized Flat 1NF Schema:
CREATE TABLE STD_CLASS (
StudentID INT,
StudentName VARCHAR(50),
ClassID CHAR(5),
CourseTitle VARCHAR(100),
Venue VARCHAR(50),
PRIMARY KEY (StudentID)
);
A.Flat Schema: STD_CLASS(StudentID, StudentName, ClassID, CourseTitle, Venue). Reintroduces redundant data storage, insertion anomalies (cannot add class venue until a student registers), deletion anomalies (losing student details deletes class details), and update anomalies (modifying a venue requires updates across multiple rows).
B.Flat Schema: STD_CLASS(StudentID, StudentName, CourseTitle, Venue). Eliminates all primary keys, making physical row addressing impossible and introducing infinite loop query scenarios.
C.Flat Schema: STD_CLASS(ClassID, CourseTitle, Venue). Discards student details completely, causing loss of referential integrity constraints and database compilation failures.
D.Flat Schema: STUDENTS(StudentID, ClassID, Venue). Maintains 2NF properties but eliminates foreign key constraints to speed up analytical query rendering times.
Consider a fully normalized relational database schema in Second Normal Form (2NF) consisting of two tables:
- STUDENTS (StudentID, StudentName, ClassID)
- CLASSES (ClassID, CourseTitle, Venue)
You are asked to perform a 'De-normalization' step to convert these tables back into a single flat First Normal Form (1NF) table named 'STD_CLASS' under 5 strict business rules...
💻 sql Snippet
CREATE TABLE STD_CLASS (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50),
ClassID CHAR(5),
CourseTitle VARCHAR(100),
Venue VARCHAR(50)
);
During physical database design, which of the following partitioning techniques reduces the chances of creating unbalanced partitions (data skewing) across physical storage media?
💻 text Snippet
-- Conceptual Hash Partitioning mapping:
-- Partition_ID = Hash_Function(Key_Value) % Total_Partitions
A.Vertical partitioning
B.List partitioning
C.Hash partitioning
D.Range partitioning
Which of the following types of horizontal partitioning reduces the chances of unbalanced partitions (data skewing)?
A.Vertical
B.List
C.Hash
D.Range
Which of the following is NOT true with respect to the database design technique of De-normalization?
A.A denormalized data model is not the same as a data model that has not been normalized.
B.Denormalization takes place before the normalization process.
C.It is an attempt to optimize the performance of database.
D.Denormalization process cannot be initiated before the Database design is established.
During physical database partition design, which of the following horizontal partitioning techniques reduces the chances of creating unbalanced partitions?
A.Vertical partitioning
B.List partitioning
C.Hash partitioning
D.Range partitioning
To analyze normal form transformations, a database administrator wants to deliberately 'denormalize' a fully normalized Second Normal Form (2NF) database schema back into a single flat First Normal Form (1NF) table. Suppose we have two 2NF tables:
- STUDENT(StudentID, StudentName, ProgramCode)
- PROGRAM(ProgramCode, ProgramName, ProgramDuration)
What is the correct structural process to merge and denormalize these relations back into a single flat 1NF table, and what is the primary side-effect of this transformation?
💻 sql Snippet
-- Fully Normalized 2NF Tables:
-- STUDENT(StudentID, StudentName, ProgramCode)
-- PROGRAM(ProgramCode, ProgramName, ProgramDuration)
A.Merge them into a single table: 'STUDENT_PROGRAM_FLAT(StudentID, StudentName, ProgramCode, ProgramName, ProgramDuration)'. The primary side-effect is that it re-introduces transitive dependencies, which can cause update, insertion, and deletion anomalies.
B.Decompose the primary keys of both tables into single-attribute keys. The primary side-effect is that it prevents any transactions from executing due to concurrent locks.
C.Convert all primary keys into unique composite indexes. The primary side-effect is that it deletes all previous records from the physical database container.
D.Create a virtual database view that hides the 'ProgramCode' column. The primary side-effect is that it converts the physical tables into non-writable read-only schemas.
You are given a fully normalized Second Normal Form (2NF) schema containing two tables:
- STUDENTS(StudentID, StudentName, MajorCode)
- MAJORS(MajorCode, MajorTitle, TotalCredits)
Under database performance optimization guidelines, write the SQL code to perform a 'De-normalization' step to merge these two relations back into a single flat First Normal Form (1NF) relation. What is the resulting flat table structure, and what specific operational anomalies will be reintroduced?
💻 sql Snippet
CREATE TABLE STUDENT_FLAT (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50),
MajorCode CHAR(5),
MajorTitle VARCHAR(100),
TotalCredits INT
);
A.CREATE TABLE STUDENT_FLAT(StudentID, StudentName, MajorCode, MajorTitle, TotalCredits). Reintroduces redundant data, insertion anomalies (cannot add a major without a student), deletion anomalies (deleting a student deletes major details), and update anomalies.
B.CREATE TABLE STUDENT_FLAT(StudentID, MajorCode, MajorTitle). Eliminates student name, introducing referential dependency violations across all database structures.
C.CREATE TABLE STUDENT_FLAT(StudentID, StudentName). Removes the major information entirely, avoiding logical duplicates but losing academic major historical tracking data.
D.CREATE TABLE STUDENT_FLAT(MajorCode, MajorTitle). Converts the primary key of the students table to a foreign key to optimize search indexing speeds.
A database administrator (DBA) is designing a database that will store millions of student enrollment records. Many courses have extremely long names (e.g., 'Introduction to Relational Database Management Systems and SQL Server'), which would cause massive storage overhead if repeated in every row. What physical database design technique should the DBA use to compress course names and save storage space?
💻 text Snippet
-- Redundant and wasteful design:
-- ENROLLMENT(StudentID, StudentName, CourseTitle, DateCompleted) -- repeating long course names
A.Define a separate 'Course' lookup table with a short, fixed-length code as the primary key (e.g., 'CS403'), and reference this code as a foreign key in the 'Enrollment' table.
B.De-normalize the table by repeating the full course name in multiple text columns in both tables to improve performance.
C.Convert the table schema into First Normal Form (1NF) by splitting the course name into multiple single-character rows.
D.Store all data in volatile memory cache buffers and disable physical hard disk logging completely.
Which of the following statements is NOT true about De-normalization?
A.It is the process of attempting to optimize the performance of a database.
B.De-normalization is a technique to move from lower to higher normal forms of database modeling.
C.In de-normalization it is required to add redundant data.
D.It enhances the overall performance of the database.
