10 Database and file concepts (3)
Resources |
Revision Questions |
Information Technology IT
Login to see all questions
Click on a question to view the answer
1.
Explain how the choice of data type for a date field (e.g., DateOfRegistration) can impact the accuracy of calculations and comparisons. Provide examples of potential issues that could arise if an inappropriate data type is used and suggest the correct data type to avoid these issues.
The choice of data type for a date field significantly impacts the accuracy of date calculations and comparisons. If an inappropriate data type is used, it can lead to errors and inconsistencies.
Potential Issues with Inappropriate Data Types:
- Storing Dates as Text: If a date is stored as text (e.g., '2023-10-27'), it cannot be directly used for date calculations. For example, calculating the difference between two dates or finding all registrations within a specific date range becomes difficult and error-prone. String comparisons are lexicographical (alphabetical), not chronological.
- Storing Dates as Integers: Storing dates as integers (e.g., the number of days since a reference date) can be problematic. It requires knowing the reference date and can be difficult to interpret. It also doesn't inherently handle leap years or varying month lengths.
- Lack of Date-Specific Functions: If a field isn't a dedicated date type, the database system may not provide built-in functions for date manipulation (e.g., adding days, extracting the month, comparing dates).
Correct Data Type: The correct data type for a date field is Date or DateTime (depending on whether time information needs to be stored). The Date data type stores only the date, while DateTime stores both the date and time. Using a dedicated date type ensures that date calculations and comparisons are performed accurately and efficiently. It also allows the use of date-specific functions provided by the database system.
2.
Question 2
You are designing a database for a library. The books table stores information about books with columns book_id (integer, primary key), title (text), author (text), and publication_year (integer). The members table stores information about library members with columns member_id (integer, primary key), member_name (text), and borrowed_books (text). Write SQL queries to:
- Create the books table.
- Delete a book from the books table based on its book_id.
- Update the author of a book.
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.
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 student_id IN (SELECT student_id FROM students WHERE student_id NOT IN (SELECT student_id FROM courses));
```
Question 2 Answer:
1. Create the books table:
CREATE TABLE books (
book_id INT PRIMARY KEY,
title VARCHAR(255),
author VARCHAR(255),
publication_year INT
);
2. Delete a book from the books table:
DELETE FROM books
WHERE book_id = 123;
3. Update the author of a book:
UPDATE books
SET author = 'Jane Doe'
WHERE book_id = 123;
3.
Consider a database for a university. Students can enroll in multiple courses, and each course can have multiple students enrolled. Describe the database relationship that should be used to represent this situation. Explain how you would implement this relationship in the database schema, including the roles of primary and foreign keys.
The database relationship to represent this situation is a many-to-many relationship. Students can enroll in many courses, and each course can have many students enrolled. A one-to-many or many-to-many relationship would not accurately represent this scenario.
To implement this relationship, a junction table (also known as an associative table or linking table) is required. This table will bridge the relationship between the Students and Courses tables.
The junction table would have the following structure:
| StudentID (Foreign Key referencing Students.StudentID) | CourseID (Foreign Key referencing Courses.CourseID) | EnrollmentDate |
Students Table: (StudentID (Primary Key), StudentName, StudentID)
Courses Table: (CourseID (Primary Key), CourseName, CourseCode)
The StudentID and CourseID in the junction table form a composite primary key, ensuring that each enrollment record is unique. The foreign keys link the junction table to the Students and Courses tables, maintaining referential integrity.