Part A (10 marks)

A University records the subject registration information of their students in a database named University.mdb, which contains three tables, STUDENT, SUBJECT and SUBJECT_REG. A student may enroll in the same subject as many times as they like. However, they can only enroll each subject at most once in each semester. The design of the three tables are shown below:

			Table Subject_REG
	Table STUDENT

Field Name	Data Type	Note
SID	Short Text	primary key
FirstName	Short Text
LastName	Short Text
Contact	Short Text
Gender	Short Text	M or F

Field Name	Data Type	Note
StudentID	Short Text
SubjectCode	Short Text
Year	Number
Semester	Number
Score	Number

Table SUBJECT

Field Name	Data Type	Note
SubjectCode	Short Text	primary key
SubjectName	Short Text

Download the database file University.mdb from Moodle. Rename it as University_Lxx_syyyyyy.mdb (where Lxx is your class and syyyyyy is your student number, e.g. University_L01_s200001.mdb / University_L01_s200001.accdb) and complete the following tasks.

1. Create a primary key of the table SUBJECT_REG. (1 mark)

(b) Establish the relationships between the tables STUDENT, SUBJECT and SUBJECT_REG. (1 mark)

(c) Create queries to perform the following tasks:

1. Display the student IDs, student names (including both first name and last name), as well as the code and name of subjects that each student had enrolled in the second semester of 2020. Arrange the records in ascending order of the student IDs. Save the query as Query1. Note: You must NOT show the semester and the year in the output. (2 marks)
2. Display the total number of students whose last name is zeng in each gender. Save the query as Query2.

Note: You must NOT show the last name in the output. (2 marks)

3. Produce a list of student IDs, student names (including both first name and last name), and the total number of enrollment records of subjects having more than 5 enrollments. Arrange the records in ascending order of the total number of enrollment records of each subject. Save the query as Query3.

Note: You must NOT show the number of enrollment records. (2 marks)

4. Produce a list of subject codes, subject names, and student names (including both first name and last name) of enrolled students for all subjects with subject codes beginning with COM being registered in the first semester of 2020. Arrange the records in ascending order of subject codes. Save the query as Query4. Note: You must NOT show the semester and the year in the output. (2 marks)