Question

Question 3

A university is creating a database to manage student records. The students table contains columns for student_id (integer, primary key), student_name (text), course_id (integer, foreign key referencing the courses table), and grade (text). The courses table contains columns for course_id (integer, primary key) and course_name (text). Write SQL queries to:

Create the students table.
Update the course a student is enrolled in.
Delete a student from the students table if they are no longer enrolled in any courses.

```

Answer 1

Question 3 Answer:

1. Create the students table:


CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(255),
course_id INT,
grade VARCHAR(2)
);

2. Update the course a student is enrolled in:


UPDATE students
SET course_id = 456
WHERE student_id = 789;

3. Delete a student from the students table if they are no longer enrolled in any courses:

This requires a more complex query involving a subquery to identify students not in the courses table. Here's an example:


DELETE FROM students
WHERE studentid IN (SELECT studentid FROM students WHERE studentid NOT IN (SELECT studentid FROM courses));

Answer 2

Violation of 3NF: The table violates 3NF because the attribute Publisher is dependent on the ISBN. The ISBN uniquely identifies a book, and therefore, the publisher is a non-key attribute dependent on a part of the primary key (ISBN).

Normalization to 3NF: The table should be decomposed into two tables:

Books (BookID, Title, Author, ISBN): ISBN is the primary key.

Publishers (PublisherID, PublisherName): PublisherID is the primary key.

BookPublishers (BookID, PublisherID): This table acts as a linking table to represent the many-to-many relationship between books and publishers. BookID and PublisherID together form the composite primary key.

Reasoning: By separating the publisher information into its own table, we eliminate the transitive dependency. The BookPublishers table resolves the many-to-many relationship, ensuring that each non-key attribute is dependent only on the primary key of its own table.

Answer 3

The appropriate database relationship to model this scenario is a one-to-many relationship. The one side of the relationship is the Author, and the many side is the Book. This is because one author can write multiple books, but each book is written by only one author.

This relationship type is suitable because it accurately reflects the real-world scenario. It ensures data integrity by preventing orphaned books (books without an associated author) and orphaned authors (authors with no books listed). The relationship also improves data efficiency. When querying for all books by a specific author, the database can efficiently retrieve all related books using the foreign key relationship established between the tables. This avoids redundant storage of the author's name with each book record.

Here's a conceptual representation:

Author Table: (AuthorID (Primary Key), AuthorName, AuthorBiography)

Book Table: (BookID (Primary Key), Title, ISBN, AuthorID (Foreign Key referencing Author.AuthorID), PublicationYear)

10 Database and file concepts (3)

Question 3

Question 2