Description
IT403
Assignment 2
Deadline: Monday 10/11/2025 @ 23:59
[Total Mark for this Assignment is 8]
Student Details:
Name: ###
ID: ###
CRN: ###
Instructions:
• You must submit two separate copies (one Word file and one PDF file) using the Assignment Template on
Blackboard via the allocated folder. These files must not be in compressed format.
• It is your responsibility to check and make sure that you have uploaded both the correct files.
• Zero mark will be given if you try to bypass the SafeAssign (e.g. misspell words, remove spaces between
words, hide characters, use different character sets, convert text into image or languages other than English
or any kind of manipulation).
• Email submission will not be accepted.
• You are advised to make your work clear and well-presented. This includes filling your information on the cover
page.
• You must use this template, failing which will result in zero mark.
• You MUST show all your work, and text must not be converted into an image, unless specified otherwise by
the question.
• Late submission will result in ZERO mark.
• The work should be your own, copying from students or other resources will result in ZERO mark.
• Use Times New Roman font for all your answers.
Restricted – مقيد
Question One
Pg. 01
Learning
Outcome(s):
Question One
CLO3
In the Saudi Electronic University (SEU) database, the entity STAFF represents
all employees.
Instructors:
Create EntityRelationship
model, Relational
model, and write
SQL queries.
2 Marks
Draw an EER diagram showing the specialization of STAFF into the subclasses
ACADEMIC_STAFF and ADMIN_STAFF.
Indicate whether the specialization is disjoint or overlapping, and whether it is
total or partial, and justify your choice briefly.
Question Two
Pg. 02
Learning
Outcome(s):
CLO3
Instructors:
Create EntityRelationship
model, Relational
model, and write
SQL queries.
Question Two
2 Marks
At the Saudi Electronic University (SEU), an entity STAFF is specialized into two
subclasses:
ACADEMIC_STAFF and ADMIN_STAFF.
Each has unique attributes—ACADEMIC_STAFF(Faculty_Rank)
ADMIN_STAFF(Position).
Task:
Show how this specialization can be mapped into relational tables using:
Option A (Multiple relations – Superclass + Subclasses)
Option B (Multiple relations – Subclass relations only)
Write the resulting table schemas for both options.
and
Question Three
Pg. 03
Question Three
Learning
Outcome(s):
CLO3
Instructors:
Create EntityRelationship
model, Relational
model, and write
SQL queries.
2 Marks
Library Management System contains two related tables named Books and
Borrowers.
The Books table should include 4 attributes : BookID, Title, Author,
PublicationYear.
The Borrowers table should include 3 attributes: BorrowerID,
BorrowerName, BookID
Requirements:
• Use SQL statements to create Books and Borrowers tables.
• Clearly identify the Primary Key and Foreign Key in your SQL script.
• Use the ON DELETE CASCADE option in the foreign key definition.
• Include a CHECK constraint on the PublicationYear column to ensure
valid data entry. Year must be greater than 1900.
• After creating both tables, explain briefly what happens when a book
record is deleted.
Question Four
Pg. 04
Learning
Outcome(s):
CLO3
Instructors:
Create EntityRelationship
model, Relational
model, and write
SQL queries.
Question Four
2 Marks
Write two SQL queries based on the Books and Borrowers tables you created
in previous question.
1. Retrieve the titles and authors of all books that were borrowed by any
borrower named “Sara”.
Use a JOIN between Books and Borrowers tables.
Display the output columns as: Book_Title and Author_Name.
Order the results by Book_Title in ascending order.
2. Update the author’s name for a specific book. Assume the book titled
“Database Systems” was written by a new author named “Dr. Ali
Mohammed”.
Purchase answer to see full
attachment
