Kingdom of Saudi Arabia Ministry of Higher Education
University of Hail
College of Computer Science and Engineering Department of Computer Science and Software
Engineering
ةيدوعسلا ةيبرعلا ةكلمملا يلاعلا ميلعتلا ةرازو
لئاح ةعماج
يللآا بساحلا ةسدنهو مولع ةيلك تايجمربلا ةسدنهو بساحلا مولع مسق
Final Exam: Database Systems /ICS 334 First Semester 2013/2014
(Including cover page this exam booklet contains Nine pages)
Exam Date: 7/01/2014 Exam Duration: 150 minutes
Student Name: _________________________________ Coordinator : Amani Mahagoub Omwer
Student ID:_______________
Find your section from the table below:
Instructor Name Day/Time Section Check
Sana’a Shedafat/Peer Fatima UTR 1100 -1150AM 101
Amani Mhajoub Omer MW 800 -920AM 102
Saima Abdullah MW 930 -1050AM 103
Saima Abdullah UTR 900 -950AM 104
Amani Mhajoub Omer UTR 100 -150AM 105
Noor Azaarier UTR 900 -950AM 106
Instructions:
1. Including this cover page, this exam booklet contains 9 pages. Check if you have missing pages.
2. Any form of cheating on the examination will result in a zero grade.
3. Please write your solutions in the spaces provided on the exam. You may use the blank areas and backs of the exam pages for scratch work.
The following is forbidden during the exam: يلي ام ناحتملاا ءانثأ عنمي:
Mobile use. ةلاقنلا فتاوهلا مادختسا
Questions after the first quarter of the exam time. ناحتملأا ةدم نم لولأا عبرلا دعب ةلئسلأا
Leaving the exam room for any invalid reason. ببس يلأ ةعاقلا ةرداغم
Borrowing tools from other students نيرخآ بلاط نم تاودلأا ةراعتسا
Max Score
Student
Score
Part I
Question 1
8
Question 2
5
Question 3
7
Part II
Question 4
6
Question 5
3
Part III
Question 6
9
Question 7
7
Total
45
Best wishes
PART I:
(20 Marks)
Q1] Choose the correct answer.
(8 marks)
1) The data model that show how data stored in the storage in detail : A. High level data model
B. Low level data model C. Representational data model
2) Describe some property of the real object: A. Entity
B. Attribute C. Relationship
3) Part of SQL language that allow users to update the data: A. DDL
B. VDL C. DML
4) In relational algebra natural join of two relation R and S result is : D. New relation contain all attribute of both relation R and S
E. New relation contain attribute of relation R with related record to S F. New relation contain attribute of relation S with related record to R
5) The functional dependency X Y means: A. Value of Y determine unique value of X B. Value of X determine unique value of Y C. No relation between value of X and Value of Y
6) The database that used to store image ,audio, clips is called : A. Traditional database
B. Multimedia database
C. Geographic Information System
7) Collection of programs that enable us to create and maintain a database is : A. Data base
B. DBMS C. Metadata
8) Ensure the accuracy of data when multiuser try to update same data: A. Concurrency control software
B. OLTP C. DBA
1 2 3 4 5 6 7 8
Q2] Which of the following TRUE or FALSE:
(5 marks)
1) The result of union operation, is a relation that includes all tuples that are either in R or in S or in both R and S with elimination of duplicate tuples
2) In client server architecture, client machine provide services. 3) Atomic value means the value can be decompose into sub values 4) Number of attribute in the relation R called the degree of relation R 5) Delete operation can violate just the referential Integrity constraint
6) In relational Algebra To retrieve some column from given relation we use select operation 7) In ER model ,an n-ary relationship is equivalent to n binary relationships .
8) The result of Join operation, is a relation that includes all Attribute that are in both R and S. 9) Mapping of N-ary Relationship where n>2, create a new relations.
10) Undesirable dependencies are avoided when a relation is in 3NF IS Partial
Functional Dependencies. (
0.5 mark
for each)
Write your answer here:
1 2 3 4 5 6 7 8 9 10
Q3]Consider following relational database schema. Suppose that all the
relations were created by user X, who wants to grant the following privileges
to user accounts A, B, C, D, and E:
(7 marks)
Employee(Name, ssn, Bdate, Address, salary,dno) Department ( Dnumber, dname, mgr_ssn)
Dept_locations(dnumber, dlocation)
Project(pnumber, pname, plocation, dnumber)
1)
Account A can retrieve or delete any relation and can grant any of these privileges to otherusers.
(2 marks)
……… ………
2)
Account D can retrieve any attribute of EMPLOYEE or Daprtment and can modifyDepartment. .
(2.5 marks)
……… ……… ……… ………3)
Account E can retrieve any attribute of EMPLOYEE but only for EMPLOYEE tuples thathave Dno = 3. .
(2.5 marks)
……… ……… ……… ………
PART II:
(9 Marks)
STUDENT
Q4]
Using the above relations, write therelational algebraic expressions
thatID NAME YEAR
43 Jones 2
32 Burns 1
34 Cairns 3
21 Hughes 2
NEW-TUDENT
ID NAME YEAR
42 Smith 3
98 Turner 2
23 Murphy 2
34 Cairns 3
90 Noble 1
REPORT
SID CID MARKS
43 216 82
32 216 75
32 312 71
34 121 49
21 312 39
43 251 70
32 251 69
32 121 78
COURSE
CID TITLE LNAME
216 Databases Black
312 Software Black
251 Numerical Quinn
would derive the following results. Write the Final resulting relations. (
6 marks)
1) List the ID, NAME and YEAR of both new and current students who are in year 2.(3
marks)
……… ……… ……… ……… ……… ……… ……… ……… ……… ………
2) Generate a list containing, IDS, NAMES, YEARS, COURSE NUMBER and MARK for that course. for the current students only (3
marks)
……… ……… ……… ……… ……… ……… ……… ……… ……… ……… ……… ………
Q5] Consider the following six relations for an order-processing database
(3marks)
CUSTOMER (CustNo, Cname, City)
ORDER_ITEM (OrderNo, ItemNo, Qty) ITEM (ItemNo, Unit_price)
SHIPMENT (OrderNo WarehouseId, Ship_date) WAREHOUSE (WarehouseId, City)
Hint: Ord_Amt : refers to total dollar amount of an order; Odate: is the date the order was placed;
Ship_date : is the date when the order shipped from the warehouse. Assume that an order can be shipped from several warehouses.
In the following table, specify the primary and foreign keys for each relation. If a table does not have a foreign key, write “none” If the primary key is composites write (composites) after the attributes.
Table Primary Key Foreign Key
CUSTOMER ORDER
ORDER_ITEM ITEM
SHIPMENT WAREHOUSE
PART III: (15 Marks)
Q6 ]
Consider the following First Normal Form (1NF) dependency(9 Marks)
K,L M,N,O,P,Q
KN
P Q
1) Draw dependency diagram and answer the questions that follow:
(3 Marks)
……… ……… ……… ……… ……… ……… ……… ………diagram.
(2 Marks)
……… ……… ……… ……… ……… ……… ………3) Normalize it in Second Normal Form (2NF), show the dependency diagram for each table.
(2 Marks)
……… ……… ……… ……… ……… ……… ……… ……… ……… ……… ……… ………4) Normalize it in Third Normal Form (3NF), show the dependency diagram for each table.
(2 Marks)
……… ……… ………
Q7]. Consider the above ER Diagram to answer the following questions:
(7 marks)
1) Map the relationship type (Contract)
(2 marks)
……… ……… ……… ……… ………
2) What is the participating entity type of relationship type (Write) in the site of book participation
……… ………
3) What is the cardinality ratio or participation constraint of relationship type (publish) in the site of publisher participation
(1 marks)
……… ………
4) Map the entity type (Book) with all its relationship type
(3 marks)
……… ……… ……… ………..
