IT 403 SEU Fundamentals of Databases & EER Diagram Questions

College of Computing and Informatics
Assignment 2
Deadline: Tuesday 30/05/2023 @ 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.
Question One
Pg. 01
Learning
Outcome(s):
Question One
1.75 Marks
In a train stations database, an employee MUST be a train driver, ticket issuer or
train attendant, with following constraint: Same employee cannot occupy more
than one job type.
Create EntityRelationship
model, Relational
model, and write
SQL queries.
Draw EER diagram to represent specialization of employees in the train station
database.
Answer:
Question Two
Pg. 02
Learning
Outcome(s):
Question Two
2.25 Marks
Draw the schema mapping (logical database design) for the following part of ER
diagram for train station database. When appropriate use the foreign key option.
Design a
database starting
from the
Answer
conceptual design
to the
implementation of
database
schemas.
Answer:
Question Three
Pg. 03
Learning
Outcome(s):
Question Three
2.5 Marks
Relationship
Suppose you are creating a database for a library management system. Explain
how you would create a table for the “books” entity and insert some sample data,
and then alter the table to add a new column for the “authorID” attribute and
update “authorID” with some data. Finally, write a SQL query to retrieve all the
books that were published after the year 2000.
Note that this book schema has several columns, including “book_id” as the
model, Relational
primary key, “title”, “authorN”, “publisher”, “publication_year”, “isbn”,
model, and write
“language”, and “num_pages”. It also includes “available” column that is set to
SQL queries.
“true” by default and can be used to track the availability of the book.
Create Entity-
Answer:
Question Four
Pg. 04
Learning
Outcome(s):
Create EntityRelationship
model, Relational
Question Four
Write a SQL query that retrieves the names and email addresses of all students
who are enrolled in at least one course in the “Computer Science” department
and have a graduation year of 2022 or later. Include the course name and
instructor name for each enrollment in the output. Schemas of the tables are
below.
model, and write
SQL queries.
1.5 Marks
“students” table:
student_id: unique identifier for the student
name: name of the student
email: email address of the student
major: field of study for the student
graduation_year: expected year of graduation for the student
“courses” table:
course_id: unique identifier for the course
course_name: name of the course
instructor_name: name of the instructor teaching the course.
department: department offering the course.
course_description: description of the course
“enrollments” table:
enrollment_id: unique identifier for the enrollment
student_id: identifier for the student enrolled in the course.
course_id: identifier for the course the student is enrolled in
enrollment_date: date the student enrolled in the course
grade: grade the student received in the course
Question Four
Pg. 05
Answer: