Description
IT403 Fundamentals of Database
Assignment 2
Deadline: 27/11/2024 @ 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
CLO 3
Consider the following EMPLOYEE relation from a company database. Write
Create EntityRelationship
model, Relational
model, and write
SQL queries
2 Marks
SQL queries to answer the following questions:
EmpID
Name
Salary
Gender
Address
123
Sarah
50000
Female
Jeddah
234
Mohamed
45000
Male
Riyadh
376
Ahmed
70000
Male
Jeddah
498
Maryam
10000
Female
Jeddah
555
Muna
30000
Female
Riyadh
1- Retrieve the count of Female Employees.
2- Retrieve the average Salary of all Employees.
3- Delete the record of the Employee named “Muna”.
4- Insert a new employee into the table.
Restricted – مقيد
Question Two
Pg. 02
Learning
Outcome(s):
Question Two
2 Marks
Answer the following questions by referring to the tables below:
CLO 3: Create
EntityRelationship
model, Relational
model, and write
SQL queries.
COURSE
Course_name
Course_number
Credit_hours
Department
Intro to Computer Science
CS310
4
CS
System Analysis and
Design
IT353
4
IT
Discrete Mathematics
MATH410
3
MATH
IT403
3
IT
Fundamentals of
Database
SECTION
Section_ID
Course_no
Semester
Year
Instructor
80
MATH410
Fall
23
Sara
95
CS310
Fall
23
Ali
103
IT353
Fall
24
Khaled
115
MATH410
Fall
24
Ahmed
120
CS310
Spring
24
Ali
140
IT403
Fall
24
Eman
a) Create a virtual table called (COURSE_ON_SEC) that summarize courses
information along with its section and instructor, listed alphabetically by
course name.
b) Retrieve the section ID, course name, course number, department, and
instructor of the courses taught by instructor “Ali” from
COURSE_ON_SEC table.
Restricted – مقيد
Question Three
Pg. 03
Learning
Outcome(s):
CLO4 – Design a
database starting
Question Three
2 Marks
You are given a table with unnormalized design. You are required to transform the
provided table into First Normal Form (1NF), and Second Normal Form (2NF). For
each step, provide the new table(s) and explain the changes made.
from the
conceptual design
Order_ID
Supplier_Name
Products_Info
Order_Date
001
Amazon
P1, product101,
product102
01-11-2024
002
Noon
P2, product201,
product202,
product203
04-11-2024
003
Alim
P3, product301
10-11-2024
to the
implementation of
database
schemas
Restricted – مقيد
Question Four
Pg. 04
Learning
Outcome(s):
CLO3:
Question Four
2 Marks
Using join:
–
write an SQL query to retrieve the names and addresses of all employees who work in
Research departments.
Create EntityRelationship
model, Relational
model, and write
SQL queries.
Restricted – مقيد
–
Write an SQL query to retrieve the names of all employees who have a higher salary
than their manager. Use a self-join to compare the salaries.
Purchase answer to see full
attachment
