Define database terms (entity, attribute, data model, data integrity) and explain dependencies in 2NF and 3NF.

Here are the answers to Question 5:

a) Briefly define the following database related terms:

• i) Entity: An entity refers to a person, place, object, event, or concept about which data is stored. For example, in a school database, "Student" or "Course" would be entities.
• ii) Attribute: An attribute is a characteristic or property of an entity. It describes a specific piece of information about an entity. For example, for a "Student" entity, "Name" and "Student ID" are attributes.
• iii) Data model: A data model is an abstract model that organizes elements of data and standardizes how they relate to one another and to the properties of real-world entities. It defines the structure of a database.
• iv) Data integrity: Data integrity refers to the accuracy, consistency, and reliability of data stored in a database. It ensures that data remains valid and uncorrupted over its entire lifecycle.

b) State and explain the type of data dependencies addressed by the second (2NF) and the third normal form (3NF) rules.

• Second Normal Form (2NF): Addresses partial dependency. This occurs when a non-key attribute is dependent on only a part of the primary key, not the entire primary key. 2NF requires that all non-key attributes be fully functionally dependent on the entire primary key.
• Third Normal Form (3NF): Addresses transitive dependency. This occurs when a non-key attribute is dependent on another non-key attribute, which in turn is dependent on the primary key. 3NF requires that all non-key attributes be directly dependent on the primary key and not on other non-key attributes.

c) Consider the database schema illustrated below:
Employee(empID, empName, empAddress, deptID)
Department(deptID, deptName, deptLocation)

• i) Explain the term referential integrity using the database schema above. The database is to be modified such that an employee can work in more than one department, and a department may have several employees.

  • Referential integrity is a database concept that ensures that relationships between tables remain consistent. It means that if a foreign key in one table refers to a primary key in another table, then every value of the foreign key must have a matching primary key value in the referenced table.
  • Using the given schema, deptID in the Employee table is a foreign key referencing deptID (primary key) in the Department table. Referential integrity ensures that every deptID value in the Employee table must exist as a deptID in the Department table. This prevents employees from being assigned to non-existent departments.

• ii) State the type of relationship that exists between the employees and the departments entities.

  • The original schema implies a one-to-many relationship (one department can have many employees, but one employee belongs to only one department).
  • However, the modification described ("an employee can work in more than one department, and a department may have several employees") changes this to a many-to-many relationship.

d) Briefly explain how this type of relationship could be implemented in a database.

A many-to-many relationship is implemented in a database by creating an associative table (also known as a junction table or bridge table). This new table contains the primary keys of both participating entities as foreign keys, forming a composite primary key. Each record in the associative table represents one instance of the relationship.

e) Write an SQL DDL query to create an associative table for this database called empDept including its key fields.

$$
\begin{align*}
&\text{CREATE TABLE empDept (} \\
&\quad \text{empID INT NOT NULL,} \\
&\quad \text{deptID INT NOT NULL,} \\
&\quad \text{PRIMARY KEY (empID, deptID),} \\
&\quad \text{FOREIGN KEY (empID) REFERENCES Employee(empID),} \\
&\quad \text{FOREIGN KEY (deptID) REFERENCES Department(deptID)} \\
&\text{);}
\end{align*}
$$