Translate all this to English

i) Data Validation

a) What does validation mean in this context?

Validation is the process of checking user-entered data to ensure it is accurate, complete, and conforms to predefined rules or constraints before being stored in the database. This helps maintain data integrity and quality.

b) Describe a rule for each type of data and an alternative rule.

• Name:
  • Rule: The name must contain only alphabetical characters (letters) and spaces.
  • Alternative rule: The name must not be empty and its length must be between 2 and 50 characters.
• Gender:
  • Rule: The gender must be selected from a predefined list of values (e.g., 'Male', 'Female', 'Other').
  • Alternative rule: The gender must be a single character 'M' or 'F'.
• Password:
  • Rule: The password must contain at least 8 characters, including at least one uppercase letter, one lowercase letter, one digit, and one special character.
  • Alternative rule: The password must be different from the username and must not contain simple numeric or alphabetical sequences (e.g., "123456" or "abcdef").
• Email Address:
  • Rule: The email address must follow a standard format (e.g., user@domain.com) and contain an '@' and a '.'.
  • Alternative rule: The email address must not already exist in the database (uniqueness check).

ii) Relational Database Schema

a) Explain what the term referential integrity means.

Referential integrity is a database rule that ensures that relationships between tables remain valid. It requires that any foreign key value in one table corresponds to an existing primary key value in the referenced table, or that it is NULL. This prevents the creation of links to non-existent data.

b) Present one example row for the Students table and two example rows for the Subjects and Results tables.

• Students Table (1 row):

  StudentID	StudentName	DateOfBirth
  101	Alice Dupont	2000-01-15

• Subjects Table (2 rows):

  SubjectCode	SubjectTitle
  CS101	Computer Science
  MA201	Calculus I

• Results Table (2 rows):

  StudentID	SubjectCode	Grade
  101	CS101	A
  101	MA201	B

c) Draw an entity-relationship (E-R) diagram showing the degrees of the relationships.

Here is the E-R diagram, without attributes as requested:

+-----------+       1           M       +---------+       M           1       +-----------+
|  Students |---------------------------|  Results  |---------------------------|  Subjects |
+-----------+                           +---------+                           +-----------+

Explanation of the relationships:

• An entity Students can have multiple records in the Results table (1 to M).
• An entity Subjects can have multiple records in the Results table (1 to M).
• The Results table represents a many-to-many (M:N) relationship between Students and Subjects.

d) Write an SQL command using Inner Join to retrieve the IDs and names of students who have a grade of 'A' in the subject "Computer Science".

SELECT
    S.StudentID,
    S.StudentName
FROM
    Students AS S
INNER JOIN
    Results AS R ON S.StudentID = R.StudentID
INNER JOIN
    Subjects AS Sub ON R.SubjectCode = Sub.SubjectCode
WHERE
    R.Grade = 'A' AND Sub.SubjectTitle = 'Computer Science';