NSC Information Technology SQL statements: 4.1 Explain what a relational da

Question

4.1 Explain what a relational database is.

A relational database is a database consisting of different tables which are linked with relationships.

4.1.2 Explain why record locking is necessary when different staff members are working on the same table in the database.

Record locking is required to prevent changes made by one of the staff members from being lost. If the record is locked, only one user may be allowed to make changes to the content of the record. Accessing the same record at the same time can corrupt data, and anomalies may occur, ensuring that the data remains consistent.

4.1.3 Besides planning and creating a database, name TWO other tasks that a database administrator has to perform as part of his/her duties.

Two responsibilities of a database administrator can include setting up user accounts and user rights and monitoring the workings of the database.

4.2 The database of the library has two tables: tblStudents and tblBooksBorrowed. The table tblStudents keeps record of the students who use the services of the library. The table tblBooksBorrowed keeps record of the books borrowed by a student.

The relationship between the tables is given below.

4.2.1 The student number in the tblStudents table is the primary key.
Explain what a primary key is in this context.

A primary key refers to a field that makes it possible that the record can be uniquely identified.

4.2.2 What is the StudentNumber field in the tblBooksBorrowed table called in terms of the relationship between the tables?

It is referred to as a foreign key.

4.2.3 A composite primary key is used in the tblBooksBorrowed table.
What is meant by a composite primary key?

It means that the primary key can consist of more than one field.

4.2.4 Identify and motivate the specific relationship that exists between the tblStudents and tblBooksBorrowed tables.

The relationship is one to many; one student can borrow many books.

4.2.5 Referential integrity was enforced when the relationship between the tables was created.
Why was this done?

A record on the one (tblStudents) side of the relationship cannot be deleted if it is linked to records on the many (tblBooksBorrowed) side of the relationship. A new record cannot be entered on the many (tblBooksBorrowed) side of a table without a corresponding record on the one (tblStudents) side of the relationship.

4.2.6 The extract of data below shows some of the records in the two tables.

tblBooksBorrowed
ISBN | CopyNr | DateBorrowed | StudentNumber
-------------------------------------------------
9781368422493 | 01 | 2018/04/12 | 20120028312
9781468425591 | 03 | 2018/04/12 | 20150020192
9781468425591 | 01 | 2018/04/13 | 20120028312
9781608229403 | 01 | 2018/04/12 | 20160022122
9783469219403 | 02 | 2018/04/12 | 20160019213

(a) Write an SQL statement to display the name, surname and funds available of all the students with R30 or less in their account.

SELECT StudentName, StudentSurname, FundsAvailable
FROM tblStudents
WHERE FundsAvailable <= 30;

(b) Write an SQL statement to display the student number and number of books borrowed of all the students who have more than two books on their account. The number of books borrowed must be shown in a field called NumberBorrowed.

SELECT tblStudents.StudentNumber,
Count(ISBN) AS NumberBorrowed
FROM tblStudents, tblBooksBorrowed
WHERE tblStudents.StudentNumber = tblBooksBorrowed.StudentNumber
GROUP BY tblStudents.StudentNumber HAVING (NumberBorrowed > 2);

4.1 Explain what a relational database is - NSC Information Technology - Question 4 - 2018 - Paper 2

Worked Solution & Example Answer:4.1 Explain what a relational database is - NSC Information Technology - Question 4 - 2018 - Paper 2

4.1 Explain what a relational database is.

4.1.2 Explain why record locking is necessary when different staff members are working on the same table in the database.

4.1.3 Besides planning and creating a database, name TWO other tasks that a database administrator has to perform as part of his/her duties.

4.2.1 Explain what a primary key is in this context.

4.2.2 What is the StudentNumber field in the tblBooksBorrowed table called in terms of the relationship between the tables?

4.2.3 What is meant by a composite primary key?

4.2.4 Identify and motivate the specific relationship that exists between the tblStudents and tblBooksBorrowed tables.

4.2.5 Why was referential integrity enforced when the relationship between the tables was created?

4.2.6 (a) Write an SQL statement to display the name, surname and funds available of all the students with R30 or less in their account.

4.2.6 (b) Write an SQL statement to display the student number and number of books borrowed of all the students who have more than two books on their account.

Join the NSC students using SimpleStudy...