Gce Model Questions Vol1 2023 Computer Studies Question 9

Practice essay / theory question 9 from the 2023 Gce Model Questions Vol1 Computer Studies examination. This question covers Database Design, Normalization, SQL Queries, Transaction Management .

Gce Model Questions Vol1 2023 Computer Studies Essay / Theory
Topics: Database Design Normalization SQL Queries Transaction Management
Question 9 GCE_MODEL_QUESTIONS_VOL1 • 2023 • COMPUTER_STUDIES • essay

A small university maintains a database to record information about students, courses, lecturers and enrolments. The following entities and attributes are identified: - Student(SID, SName, DOB, Email) - Lecturer(LID, LName, Department) - Course(CID, CTitle, Credits, LID) - Enrolment(SID, CID, EnrolDate, Grade) The relationships are: - Each course is taught by one lecturer (LID in Course). - A student may enrol in many courses and a course may have many students (Enrolment). - The university wishes to enforce that a student cannot enrol in the same course more than once and that a lecturer can teach at most three courses in a semester. Using this scenario, answer the following questions:

Question Parts

( a )
Identify all functional dependencies (FDs) that hold in the initial relation set and state the highest normal form (1NF, 2NF, 3NF, BCNF) each relation currently satisfies.
( b )
Transform the design to achieve Third Normal Form (3NF). List the resulting relations with their primary keys and any foreign keys required.
( c )
Write an SQL query that returns the SID and SName of every student who is enrolled in at least two different courses that are taught by the same lecturer.
( d )
Explain how the choice of transaction isolation level (Read Uncommitted, Read Committed, Repeatable Read, Serializable) would affect concurrent updates to the Enrolment table when multiple registration clerks are inserting new enrolments for the same student.

Detailed Solution & Marking Scheme

Get the step-by-step solution, key points required by examiners and detailed marking guidance through the Edcrib AI Tutor.

Reveal Answer

About This Question

This is Gce Model Questions Vol1 2023 Computer Studies Question 9. It is one of the essay questions from the 2023 Gce Model Questions Vol1 Computer Studies examination.

The question covers Database Design, Normalization, SQL Queries, Transaction Management .

View More Gce Model Questions Vol1 2023 Computer Studies Questions More Gce Model Questions Vol1 Practice Questions Back to Questions

Master the Exam!

You've seen a preview, but there are thousands more questions plus AI tutor to break down complex solutions.

Unlock Full Access Available for Android & Windows
Chat with Support