IT 403 SEU BANK ER Diagram and SQL DDL Statements Questions

College of Computing and InformaticsAssignment 2
Deadline 07/4/2022 @ 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):
CLO4
Design a
database starting
from the
conceptual design
to the
implementation of
database
schemas.
Question One
2 Marks
The following figure present BANK ER diagram. Map it into a full relational
schema. Specify all primary keys and foreign keys.
Question Two
Pg. 02
Learning
Outcome(s):
CLO2
Create EntityRelationship
model, Relational
model, and write
SQL queries.
Question Two
2 Marks
Write appropriate SQL DDL statements to define the database schema you
made in the previous question. Create all the appropriate Referential Integrity
constrains.
Question Three
Pg. 03
Learning
Outcome(s):
CLO2
Create EntityRelationship
model, Relational
model, and write
SQL queries.
Question Three
2 Mark
Using DML SQL, write the appropriate SQL query for each of the following:
a) Insert A new customer with the following information:
(Name: Ali, SSN: 1111, Address: Riyadh, Phone: 05555)
b) Delete all the loans for customer with SSN: 2222
c) Select all customer who got loans more than 250000 SAR, print the
customers SSN, name
d) Print all the Accounts in Bank: ALBILAD and in branch no: 5
Question Four
Pg. 04
Learning
Outcome(s):
CLO2
Create Entity-
Question Four
Draw the ER-Diagram for the following database schema. Specify the types of
relationships in details on the diagram.
Relationship
model, Relational
model, and write
SQL queries.
2 Mark
Customers {Customer_ID, C_Name}
Employees {Employee_ID, E_Name}
Orders
{ Order_No, Customer_ID , Employee_ID, }
Order_Details {Order_No, Product_No}
Products {Product_NO, Product_Name}