Due: 12 November 2024 (Tuesday) 18:00

Late submission is liable to a penalty of 10% of the available marks for each day late; Saturdays, Sundays and holidays are counted. Submission after 16 November 2024 at 18:00 will not be accepted.

Hand in ONLY softcopy in WORD format

Filename format: class_studentid.docx, e.g., 101_23334455A.docx

Attention:

Type all answers on the answer sheet provided. NO handwriting is accepted.

All personal particulars (including student name, student number) must be typed clearly at the top of the first page of each document, if NOT, 10% of marks will be deducted.

When submitting your assignment via Blackboard, the submission time will be recorded. There will be a sharp cut-off time at Blackboard, so late assignments will be recorded at Blackboard. Softcopies submitted via email or other means will NOT be accepted unless the Blackboard is not available. As many students will submit their assignments to Blackboard at around the deadline time, it normally takes longer for uploading your assignment, so it is strongly suggested that you start submitting earlier, say at least 45 minutes before the deadline. Marks will be deducted for late submission.

Plagiarism will be penalized severely. Marks will be deducted for assignments that are plagiarized in whole or in part, regardless of the sources.2

A college offers several courses to its students. Each course has one single offering, so that there is no distinction between courses and classes. The data are organized into the tables below.

Some sample records:

You do NOT need to submit the database file, but you need to include in your submission the screen shots of the four tables in datasheet view. Make sure the screen shots are clearly readable (reasonable font size and no blurring of the images).

(b) Write a SQL statement in the Access database to create a table called ENROLLMENT2, which has the same structure as ENROLLMENT table. Provide a screen shot of the SQL statement.

(c) Write a SQL statement in the Access database to retrieve the ID and name of students who have taken both SEHH2240 and SEHH2239. Provide a screen shot of the SQL statement and a screen shot of the query result.

(d) Write a SQL statement in the Access database to return the ID, name, and GPA of each student. The statement should retrieve the marks, convert the marks to grade points, and calculate the GPAs by averaging the grade points of each student. Exclude the missing marks from the calculation of the GPAs. Provide a screen shot of the SQL statement and a screen shot of the query result.3

Create a fully developed Crow’s Foot notation ERD to support the following business operations. Use Lucidchart to produce the ERD. Copy-and-paste the ERD directly from Lucidchart onto WORD to produce a clear picture (refrain from screen capturing). The font size set in Lucidchart should be large enough to make the text clearly readable after pasting the ERD onto WORD.

The Crow’s Foot ERD should be fully attributed, including all entities, attributes, relationships (connectivities), and constraints (primary keys, foreign keys). Design all appropriate attributes, relationships, and constraints in the ERD. The diagram should include exactly the following entities:

Question 3 (30 marks)