Introduction to Database
moe1990
IT-244Assignment2Questions.docx|
Pg. 09 |
|
Question Five |
|
|
|
|
Assignment 2
Deadline: Saturday 08/12/2018 @ 23:59
[Total Mark for this Assignment is 6]
Introduction to Database
IT244
College of Computing and Informatics
|
|
|
|
|
|
|
|
Question One
Marks: 1
Learning Outcome(s):
LO3: Design a database starting from the conceptual design to the implementation of database schemas. (3.2)
Compute the closure (F+) of the following set F of functional dependencies for relation schema R(A, B, C, D, E).
A → BC
CD → E
E → A
List the candidate keys for R.
Ans.
Question Two
Marks: 1
Learning Outcome(s):
LO2: Create entity-relationship model, relational model, and write SQL queries. (2.1)
Write the following queries in Relational Algebra, for the following bank schema:
account (ano, bname, balance)
branch (bname, bcity, assest)
customer (Cname, cstreet, ccity)
loan (lno, bname, amount)
deposit (cname, ano)
borrower (cname, ino)
a) Select all loan tuples where branch name is Abha. [0.5 marks]
Ans:
b) Select all loan tuples where branch name is Abha and loan amount less than 200000. [0.5 marks]
Ans:
Question Three
Marks: 1
Learning Outcome(s):
LO2: Create entity-relationship model, relational model, and write SQL queries. (2.1)
Using the above bank schema, find the Tuple Relational Calculus queries, for the following:
a) Find the loan with amount more than 150,000. [0.5 marks]
Ans:
b) Find loan number for each loan of amount greater than 150,000 [0.5 marks]
Ans:
Question Four
Marks: 2
Learning Outcome(s):
LO2: Create entity-relationship model, relational model, and write SQL queries. (2.1)
Given the three tables below, write the following queries.
Customer
|
custNo |
custName |
custSt |
custCity |
age |
|
1 |
C1 |
Olaya St |
Jeddah |
20 |
|
2 |
C2 |
Mains St |
Riyadh |
30 |
|
3 |
C3 |
Mains Rd |
Riyadh |
25 |
|
4 |
C4 |
Mains Rd |
Dammam |
|
|
5 |
C5 |
Mains Rd |
Riyadh |
|
Product
|
prodNo |
prodName |
prodDes |
price |
|
100 |
P0 |
Food |
100 |
|
101 |
P1 |
healthy Food |
100 |
|
102 |
P2 |
|
200 |
|
103 |
P3 |
self_raising flour,80%wheat |
300 |
|
104 |
P4 |
network 80x |
300 |
Order
|
ordNo |
ordDate |
custNo |
prodNo |
quantity |
|
R03010101 |
01-jan-2003 |
C01 |
100 |
2 |
|
R03010203 |
02-jan-2003 |
C01 |
101 |
1 |
|
R03010102 |
01-jan-2003 |
C2 |
102 |
1 |
|
R03010103 |
01-jan-2003 |
C343 |
100 |
2 |
|
R03010301 |
03-jan-2003 |
C01 |
101 |
1 |
|
R03030601 |
06-mar-2003 |
C2 |
100 |
10 |
1. A DDL command to build order table, including all integrity constraints. Make sure that ordDate value exists and quantity value is always positive.
[0.5 marks]
Ans:
2. A DML command to answer: How many orders were made by customer C01 and how many products pieces did he order? [0.5 marks]
Ans:
3. A DML command to list customers who have ordered the product 100. (Hint: use join) [0.5 marks]
Ans:
4. A DML command to find products with price more than average (Hint: use nested queries) [0.5 marks]
Ans:
Question Five
Marks: 1
Learning Outcome(s):
LO4: Apply principles and concepts of information integrity, security and confidentiality; (4.1)
Describe Server-side Scripting and Client-side Scripting in detail. Also, discuss the key difference between Server-side Scripting and Client-side Scripting.
Ans.
