Phần 2: Thực hành (trả lời câu hỏi ở phần dưới)

Assignment: Database Normalization and Querying Case Study

1. Scenario Description You are a database consultant hired by a university. They are
currently using a single, unnormalized table (or spreadsheet) to manage all course
registrations. This design has led to significant data redundancy and integrity issues
(anomalies). Your task is to analyze their current table, normalize its structure into a set of
well-formed relations, and then demonstrate how to retrieve data from your new, normalized
design using relational algebra.

2. Provided Materials Unnormalized Table:

CourseRegistration(StudentID, StudentName, StudentEmail, StudentMajorID,

StudentMajorName, CourseID, SectionNo, Semester, Year, CourseTitle, CourseCredits, ProfID,
ProfName, ProfOffice, ProfDeptID, ProfDeptName, Grade)

Functional Dependencies (FDs):

FD1: StudentID \to StudentName, StudentEmail, StudentMajorID

FD2: StudentMajorID \to StudentMajorName

FD3: CourseID \to CourseTitle, CourseCredits

FD4: ProfID \to ProfName, ProfOffice, ProfDeptID

FD5: ProfDeptID \to ProfDeptName

FD6: {CourseID, SectionNo, Semester, Year} \to ProfID

FD7: {StudentID, CourseID, SectionNo, Semester, Year} \to Grade (Primary Key)

3. Required Tasks (10 Questions) Answer the following questions based on the scenario and
provided materials.

Part A: Analysis of the Unnormalized

Table 1. Identify Anomalies: Based on the unnormalized CourseRegistration table, provide

one specific example for each of the following:

a) An Insertion Anomaly.

b) An Update Anomaly.

c) A Deletion Anomaly.

2. Determine 2NF Violation: Identify one FD from the list that violates Second Normal Form
(2NF). Explain why it violates 2NF (i.e., what is the definition of a partial dependency, and
how does this FD fit that definition?).

3. Determine 3NF Violation: Identify one FD from the list that creates a transitive
dependency and therefore violates Third Normal Form (3NF). Explain the "chain" of
dependencies that causes this violation. Part B: Database Decomposition (Normalization)

4. Decompose to 3NF/BCNF: Decompose the CourseRegistration table into a set of smaller

relations that are in at least Third Normal Form (3NF).
5. Define Normalized Schemas: For each new relation you created in Task 4, you must:

a) Provide a clear name (e.g., Student, Course, Professor).

b) List all of its attributes.

c) Clearly underline the Primary Key (PK).

d) Clearly identify all Foreign Keys (FKs) and state which table and attribute they reference.

Part C: Relational Algebra Queries (on Normalized Schema) Use your normalized tables from
Part B to answer the following. Use plain text syntax (e.g., PI, SIGMA, JOIN).

6. Simple Query (Select, Project, Join): Write the relational algebra expression to retrieve the
names (StudentName) of all students who are majoring in the department named 'Computer
Science'.

7. Multi-Join Query 1: Write the relational algebra expression to retrieve the titles
(CourseTitle) of all courses taught by the professor named 'Dr. Smith'.

8. Multi-Join Query 2: Write the relational algebra expression to retrieve the names
(StudentName) of all students who received a 'B' grade or higher (assume 'A' or 'B') in the
course titled 'Database Systems'.

9. Set Operator (INTERSECTION): Write the relational algebra expression to retrieve the
StudentIDs of students who are enrolled in both 'CS101' AND 'MATH101'.

10. Set Operator (DIFFERENCE): Write the relational algebra expression to retrieve the
StudentIDs of students who are enrolled in 'CS101' but are not enrolled in 'MATH101'.hãy trả
lời theo bài cơ sở dữ liệu

What is the term for a column in a relation (table)? 1 point

Tuple Attribute Domain Relation

What is the term for a row in a relation (table)? 1 point

Tuple Attribute Domain Schema

What does the 'degree' of a relation refer to? 1 point

The number of rows (tuples)

The number of columns (attributes)

The number of primary keys

The number of tables in the database

What does the 'cardinality' of a relation refer to? 1 point

The number of rows (tuples)

The number of columns (attributes) The number of foreign keys The type of data in a column
What is the definition of a 'domain' in the relational model? 1 point The name of a table A set
of permissible, atomic values for an attribute A row in a table The entire database structure
What is a 'superkey'? 1 point A set of one or more attributes that uniquely identifies a tuple
A minimal set of attributes that uniquely identifies a tuple A key used to link two tables The
first attribute in a table What is a 'candidate key'? 1 point A set of one or more attributes
that uniquely identifies a tuple A minimal superkey A key used to link two tables Any
attribute that contains unique values What is a 'primary key'? 1 point The only key in the
table A key used to link to another table A candidate key chosen by the database designer
to be the main identifier A composite key What is a 'foreign key'? 1 point A key that is not in
its "home" table A minimal superkey An attribute (or set) that refers to the primary key of
another table A key that contains non-unique values The Entity Integrity constraint states
that: 1 point No foreign key value can be NULL No primary key attribute can be NULL All
attributes must be atomic The table must be in 3NF The Referential Integrity constraint
states that: 1 point All attributes must be atomic The primary key cannot be NULL A foreign
key value must match an existing primary key value or be NULL All data must be numeric A
key that consists of two or more attributes joined together is called a: 1 point Foreign Key
Superkey Composite Key Domain Key What does it mean for a value to be 'atomic'? 1 point It
is the primary key It is indivisible (e.g., not a list or a set in a single cell) It is a foreign key It
is a large text value The logical design or "blueprint" of a relation is called its: 1 point
Schema Instance Cardinality Domain The actual data in a relation at a specific point in time
is called its: 1 point Schema Instance Degree Domain Which operator selects a subset of
columns (attributes) from a relation? 1 point SIGMA (Select) PI (Project) JOIN (Join) UNION
(Union) Which operator selects a subset of rows (tuples) from a relation based on a
condition? 1 point SIGMA (Select) PI (Project) JOIN (Join) INTERSECTION (Intersection) What is
the result of a PI (Project) operation? 1 point A new relation with all original rows, but only
the specified columns A new relation with all original columns, but only the specified rows A
single value The number of rows in the table Which operator combines two relations by
pairing every tuple from the first relation with every tuple from the second? 1 point UNION
(Union) JOIN (Join) DIFFERENCE (Set Difference) X (Cartesian Product) What is a key
requirement for the UNION, INTERSECTION, and DIFFERENCE operators? 1 point The
relations must have the same primary key The relations must be 'union-compatible' (same
number and type of attributes) The relations must have the same name One relation must
be a subset of the other Which operator combines tuples from two relations, only keeping
those that appear in both relations? (Assume union-compatibility) 1 point UNION (Union)
DIFFERENCE (Set Difference) INTERSECTION (Intersection) X (Cartesian Product) Which
operator keeps all tuples from the first relation that are not in the second relation? (Assume
union-compatibility) 1 point UNION (Union) DIFFERENCE (Set Difference) INTERSECTION
(Intersection) JOIN (Join) Which operator combines two relations based on a common
attribute and a matching condition? 1 point RHO (Rename) PI (Project) JOIN (e.g., Natural
Join, Theta Join) X (Cartesian Product) A NATURAL JOIN between R and S is equivalent to: 1
point A Cartesian Product of R and S Selecting common columns, joining on equality, and
projecting away the duplicates The UNION of R and S The INTERSECTION of R and S What
does the RHO (Rename) operator do? 1 point It renames the database It renames a relation
and/or its attributes It renames a tuple It renames the primary key If relation R has 5 tuples
and relation S has 4 tuples, how many tuples will their Cartesian Product (R X S) have? 1
point 9 5 4 20 Relational algebra is a ____ query language. 1 point Procedural (you specify
how to get the result) Non-Procedural (you specify what result you want) Object-Oriented
Visual The expression PI A, C (SIGMA B='value' (R)) will: 1 point First select columns A and C,
then select rows where B='value' First select rows where B='value', then select columns A
and C from those rows Select all rows where A='C' OR B='value' This expression is invalid A
'Theta Join' (R JOIN[condition] S) is equivalent to: 1 point SIGMA condition (R X S) PI condition
(R X S) R UNION S R - S Which operator is not one of the 5 fundamental operators (Select,
Project, Union, Difference, Cartesian Product)? 1 point PI (Project) UNION INTERSECTION
SIGMA (Select) What is the primary goal of database normalization? 1 point To make the
database run faster To reduce data redundancy and avoid anomalies To store all data in a
single table To use less disk space What is an 'insertion anomaly'? 1 point The inability to
add new data due to missing (unrelated) information The inability to insert a duplicate
primary key The inability to update a row The inability to delete a row What is an 'update
anomaly'? 1 point When updating one piece of data requires updating multiple rows, leading
to inconsistency When an update fails due to a NULL value When an update automatically
deletes a row When an update changes the primary key What is a 'deletion anomaly'? 1
point When deleting a row fails When deleting a row unintentionally removes other,
unrelated facts from the database When a foreign key constraint prevents deletion When a
row is deleted by mistake A relation is in 1NF (First Normal Form) if: 1 point It has no
transitive dependencies It has no partial dependencies All attributes contain only atomic
(indivisible) values It has a single primary key A 'functional dependency' (FD) X → Y means: 1
point If two tuples have the same value for X, they might have the same value for Y If two
tuples have the same value for Y, they must have the same value for X If two tuples have
the same value for X, they must have the same value for Y X and Y are mathematically
related In an FD X → Y, what is X called? 1 point The determinant The dependent The
attribute The key Given R(A, B, C) with key {A, B}. The FD A → C is an example of a: 1 point
Full Functional Dependency Partial Dependency Transitive Dependency Multi-valued
Dependency A relation is in 2NF (Second Normal Form) if it is in 1NF and... 1 point It has no
transitive dependencies All attributes are atomic It has no partial dependencies Every
determinant is a superkey 2NF is only a concern for relations that have: 1 point A simple
(single-attribute) primary key A composite (multi-attribute) primary key No primary key
Many foreign keys Given R(A, B, C) with key A. The FDs are A → B and B → C. This is an
example of a: 1 point Partial Dependency Full Functional Dependency Transitive Dependency
Multi-valued Dependency A relation is in 3NF (Third Normal Form) if it is in 2NF and... 1 point
It has no transitive dependencies It has no partial dependencies It has no multi-valued
dependencies All attributes are numeric An FD X → Y violates 3NF if X is not a superkey and
Y is a: 1 point Prime attribute (part of a candidate key) Non-prime attribute (not part of any
candidate key) Foreign key The primary key What is a 'lossless-join' decomposition? 1 point
A decomposition where all tables are in BCNF A decomposition where all functional
dependencies are preserved A decomposition where joining the tables back together
produces the original, correct data A decomposition where no data is lost (no attributes are
removed) A relation is in BCNF (Boyce-Codd Normal Form) if for every non-trivial FD X → Y: 1
point Y is a prime attribute X is a non-prime attribute X is a superkey X is a foreign key How
does BCNF differ from 3NF? 1 point BCNF is stricter; it does not allow a non-prime attribute
to determine a prime attribute. 3NF is stricter than BCNF. BCNF deals with partial
dependencies; 3NF deals with transitive ones. They are identical. What is 'denormalization'?
1 point The process of normalizing to BCNF The process of creating a database with no rules
The process of combining tables to improve query performance, at the cost of redundancy
The process of finding all functional dependencies A table with a multi-valued dependency
(e.g., a ProfID determines a set of Courses and a set of Committees, independently) violates:
1 point 2NF 3NF BCNF 4NF (Fourth Normal Form) Normalization typically ____ the number of
tables in a database. 1 point Increases Decreases Does not change Duplicates The process
of breaking a large table into smaller, well-structured tables is called: 1 point Aggregation
Decomposition Concatenation Denormalization