Understanding SQL Server Architecture, Constraints, and Relationships

Posted on May 23, 2024 in Computers

SQL Server Architecture and Data Management

Q: What is the role of the architecture of SQL Server in maintaining metadata and data values?

A: SQL Server uses a special type of database called the system database to maintain metadata. Metadata includes information about the structure of other databases, the data types they contain, and the constraints applied to them. SQL Server stores data values in user databases created for specific applications. The architecture separates system databases from user databases to manage and access metadata efficiently.

Q: Describe the process SQL Server undergoes when a DDL (Data Definition Language) statement is submitted.

A: When a DDL statement is submitted, SQL Server performs the following steps:

  1. Parses the DDL statement to check syntax.
  2. Checks permissions to ensure the user has the right to perform the action.
  3. Validates the statement against the current database schema to ensure the action is possible.
  4. Updates the system catalogs (metadata) to reflect the changes made by the DDL statement, such as creating a new table or altering a table’s structure.

Database Constraints and Normalization

Q: What are the main types of constraints in databases, and what do they do?

  • Primary Key Constraint (PK): Uniquely identifies each record in a table.
  • Entity Constraint (Not Null Constraint): Ensures that a column cannot have NULL values.
  • Foreign Key Constraint (FK): Ensures the referential integrity of the data in one table to match values in another table.

Two Properties to Choose Correct PK attributes: Uniqueness and stability (the value should not change over time).

Q: What are the correct transformation rules for multi-valued and composite attributes?

A:

  • For multi-valued attributes: Create a separate table to store the attribute values, ensuring each value is associated with a single record in the original table.
  • For composite attributes: Break down the attribute into its components and store each component as a separate column in the same table.

Q: What is the correct normal form transformation rule for multi-valued, composite attributes?

A: The rule is to ensure that each table represents a single theme or concept.

  • For multi-valued attributes: Separate them into their own table with a foreign key linking back to the original table.
  • For composite attributes: Decompose them into simpler attributes that represent only one piece of data each.

Q: Is the data file Department described in the ER diagram valid as a relational table? If not, why?

A: No, the data file Department as described is not valid as a relational table in the context of normalization because it contains repeating groups or multi-valued attributes (DLocation), which violates the first normal form (1NF). To satisfy 1NF, each column must contain atomic (indivisible) values and there can be no repeating groups or arrays.

Q: What are the two important database rules (constraints) to satisfy the Third Normal Form that every table is required to satisfy for the valid state of a database?

A:

  1. The table must be in Second Normal Form (2NF).
  2. All non-primary-key attributes must be directly dependent on the primary key and not on any other non-primary-key attributes (transitive dependency). In other words, the table should have no transitive functional dependencies.

Database Relationships and Joins

Q: Can you list and describe 5 different relationship types?

  • One-to-One (1-1): Each entity in one table is related to at most one entity in another table.
  • One-to-Many (1-N): A single entity in one table can be related to one or more entities in another table.
  • Many-to-Many (N-M): Entities in one table can have relationships with multiple entities in another table, and vice versa.
  • Weak Relationship: A relationship type where the existence of one entity depends on the existence of another.
  • Unary One-to-Many (1-N): An entity is related to multiple instances of itself.

Q: Describe the purpose and usage of SQL Joins, Self Join, and Union Query.

  • SQL Joins: Used to combine rows from two or more tables, based on a related column between them.
  • Self Join: A join of a table to itself to compare rows within the same table.
  • Union Query: Combines the result sets of two or more SELECT statements into a single result set, removing duplicates.

Referential Integrity Constraint in RDBMS

Referential integrity constraint in RDBMS is a set of rules that ensures the validity and consistency of data within the relationships of the database. It maintains that if a table has a foreign key, every value of that key must either be null or match values in the primary key column of the referenced table. This ensures that relationships between tables remain consistent. When a foreign key value is inserted or updated, the following must hold true:

  • The value must exist as a primary key value in the referenced table unless the foreign key is null.
  • If the foreign key is not allowed to be null, then it must always point to an existing primary key value in the related table.

When the RDBMS server processes the DDL statement that includes a foreign key definition, it will perform checks to enforce referential integrity. For the foreign key Dno that references Department (Dnumber), the server will:

  • Verify that each Dno value in the Person table corresponds to a valid Dnumber in the Department table.
  • Not allow insertion of a Person record with a Dno that does not exist in the Department table’s Dnumber column unless Dno is allowed to be null.
  • Not allow deletion of a Department record that is referenced by a Person record without also deleting the Person record or updating the Dno to a different valid Dnumber or to null, depending on the deletion rule specified in the foreign key constraint.

CREATE TABLE Department ( Dname VARCHAR(255), Dnumber INT PRIMARY KEY, Dmgr_ssn CHAR(9) );CREATE TABLE DepartmentLocation ( Dnumber INT, DLocation VARCHAR(255), PRIMARY KEY (Dnumber, DLocation), FOREIGN KEY (Dnumber) REFERENCES Department(Dnumber) );Enforce the constraint: ALTER TABLE Employee ADD CONSTRAINT FK_Department FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID);