/

00198

Sri Lanka Institute of Information Tech ology

.. ..
•• 1"£5 ••
~
••••••
§lI11r

B. Sc. Special Honours Degree/ Diploma in Information

Technology

Final Examination
Year 1, Semester 2 (2016)

ITI03 - Database Management Systems I

Duration: 3 hours 1

April 2016

Please read the following information prior to beginning work on this xam:

Instructions to Candidates:
• Answer all questions.

• The paper consists of 8 pages including the cover page.

• There are 6 questions. Answer ALL questions clearly and to the point. Make sure the answers
are neatly written, readable and clear.

31/03/2016
 0019

tlon) (20 marks)

PART A
'Fine Entertainments' is a leading company in music ·industry. They have decided to store
information about musicians who perform on its albums (as well other company data) in a
database. You are recruited as a Database designer to model their applicat on. Your first task is to
design a database for the requirements given below.

• Each musician that records at 'Fine Entertainments' has a SSN, NIC, name, address, and a
phone number. Poorly paid musicians often share the same addre s, and no musician has
more than one phone.

• All instrument that is used in songs recorded at 'Fine Entertain ents' has a name (e.g,
guitar, keyboard, flute) and a musical key (e.g C, B-flat, E-flat).

• Albums recorded on the 'Fine Entertainments' label has a title, a copyright date, format
(e.g, CD or MC) , and an album identifier. One album can be giv n in produced in many
formats.

• Each song recorded at 'Fine Entertainments' has a title and a auth r.

• Each musician may play several instruments and a given instru ent may be played by
several musicians.

• Each album has a number of songs on it, but no song may appear on more than one album.
• Each song is performed by one or more musicians, and a musician may perform a number
of songs.

• Each album has exactly one musician who acts as its producer. A musician may produce
several album, of course.

Read the scenario given above and provide answers to the following quest ons.

a) Suggest a suitable candidate key for the musician table. (I mark)

b) Identify multivalued attributes if any. (2 marks)
c) Identify a many-to-many relationship. Draw the ER segment showing with the relationship
with relevant entities, attributes and cardinality. (2 marks)
d) Write the schema for the Album table. (2 marks)
e) What is the degree of the Album table? (I mark)

Page 2 of8

31/03/2016
 00198

f) Show an instance of the Album table. (2 marks)

g) Write the cardinality of 'produce' relationship, which exists betwe n musician and album.
(2 marks)
h) In the three schema architecture where would you model the data about the above given
scenario? (1 mark)

PARTB
Consider the Employee table and Department table given below. The prim ry key of the employee
table is EID and the primary key of department table is DNo.

I .~~ployee
.------------------------1
IE ~IN~me Ip'?sition ISalary rA~IDeptNo _ D~partment
fE011lA. J. Perera ·IManager 1100,000 i35 IDI ~ No IName ILocation
1~,~2. IK.~: T.ilak ICl~~k
--~[4\000-- [40------- rm-~------- l'IHumanResource
JD .IColombo
~II-~ ..~ily~ ..:; l~~~?~~ta~t mm175~00g ·[30 ..
jD3 D
rm-1~~I:~m ....m,IMalabe
IE~3 . IR._~_an!,k~,·· _~eere~_el!tat}veI65!000 138 1I?2 D
~IFinance IKaduwela
IE04 10.
...
Kelum
..
IAdv~rtiser 168,000 137 ID4 -4 -IMarketi
[D g [Colombo
IE02 Ip· 1'limal ICler~_ 156,000 ~IDI

a) Identify 2 different integrity constraints violated in the Employe table. (2 marks)

b) The organization is going to outsource their marketing proces and they are going to
close "marketing' department. The table needs to be updated accordingly by deleting
the Marketing department record from the department table.
1. Is it possible to delete the Marketing department (D4) from the department table?
If not, what is the reason? (2 marks)
ii. If it is essential to delete the 'Marketing' department, stat three options that can
be used without making any violations. (3 marks)

Page 3 of8

31/03/2016
 .- .• --.-.-- ..!:!.~..!..t
•• ;u~~~-.=:I;t;O;;t;U:l-y;&' 1[''''--''''=1-1+10"+6 '''iH'i' i iU" 'j 1 jl~'
:::

Question 2 (15 marks)

Map the ER diagram given below to a relation schema

N 1

Page 4 of8

31/03/2016
 '·,
r
~: : : :

:. - 00198

Question 3 (15 marks)

Micro loans are small loans, which is beginning to gain popularity especially among borrowers in
developing countries. The idea is to bring venture lenders together using i formation technology.
Typically, the loans will be used to finance startup or development of the b rrower's company, so
that there is a realistic chance for repayment. The money in a loan can, unlike traditional loans,
come from many lenders. In this problem, you must create an E-R m del that describes the
information necessary to manage micro loans.

The following information form the basis for creating the model:

• Each borrower and lender must be registered with information about name, NIC and
address.
• Borrower should make a loan request, which contains the informat on about the requested
amount, loankequestll), payback period and purpose.
• A loan starts with a loan request and loan will be granted only if t e request is approved.
Loan has a granted date and amount. Loan will be no longer valid when the borrower
remove the loan request.
• Lenders can respond to the loan requests and loan will be passed when they commit to a
portion of the total amount of a loan request.
• When the commitments for the loan request covers the requested amount, the request is
converted to a loan. If enough commitments cannot be reach d, the loan request is
cancelled. A borrower can have more than one request, and more t an one loan at a time.
• The borrower chooses when he or she will make a payment. Every payment must be
registered in the database with a PaymentID, amount and a date (at most one payment per
loan per day). The lenders share the repayment based on how larg a part of the loan they
are responsible for.
I

• The borrowers are rated based on the loan repayment and borrow rs each lender can rate
the trust of the borrower.

Draw the ER diagram to model the following real world situation. Yo r diagram should show
entities, relationships along with the cardinality and suitable attributes inc uding the primary keys.

Page 5 of8

31/03/2016
 Question 4 (15 marks)

Consider the following relational schema describing the data of a stationary company.

Country (CountryKey, CName, RegionKey, CComment)

SUPPLIER (SuppiierKey, SName, Address, NationKey, one, AccountBaiance)

~
ITE _ SUPPL7Key, SupplierKey, A vailableQuantity, Supplyf'ost, SCoIIlPlent)

ITEM (ItemKey, !Name, MFGR, Brand, Type, Size, Container, RetaiiPrice)

Write down the following queries using relational algebra. You may use any valid algebra
expression.

a) Display the names of suppliers who have an account balance above 5 ,000. (2 marks)

b) Display the name of the suppliers who supply the brand 'NIKI'. (3 marks)

c) Find the name of items with available quantity greater than 1000 and a 'e supplied by a country
in 'East Asia' region. (3 marks)

d) Display the names of the suppliers who are not supplying pencils. (3 marks)

e) Display the names of the suppliers who supply more than 5 items. (4 marks)

Page 6 of8

31/03/2016
 00198

Question 5 (15 marks)

Consider the following relation:

Article (Article ID ,AuthorID, AuthorName , title, journal, issue, year, star' page, endpage, TR
ID)

ArticI title Author Author journal Journal y ar Start End TR-ID

e ID Name issue page page
ill .
42 Cuckoo Hashing Al K. James JAlg 51 2004 121 133 87

33 Data analysis A2 J. Peter JAlg 41 2001 69 85 62

39 t Dictionaries in less Al K. James SICOMP 31 2001 111 133 47

57 P vs NP resolved A3 W.Tim JACM 51 2008 1 3 99

77 What G··odel missed A2 J. Peter SICOMP 51 2008 1 5 98

Primary Key :- (Article ID ,Author ID)

The Functional Dependencies are:

ArticleID -7 Title, journal

AuthorID -7 AuthorName

Journal_Issue -7 Year, Startpage, Endpage

Answer the following questions using the table and functional dependencie given above.

a) Which normal form is the relation in? Explain your answer. (2 marks)

b) Decompose the relation into 3NF. For each step of the decompos tion procedure, state what

functional dependency is the decomposition based on, and give the relational schemas after the
step has been carried out. (13 marks)

Page 7 of8

31/03/2016
 00198 ~

Question 6 (20 marks)

Consider the following database schema of computer products:

Model_ Tbl (num, speed, ram, hd, price)
~
Computer Tbl (maker, model, category)
- /
Maker _Tbl ( name, address, phone)

Where
• Maker indicates the manufacturer of the computer
• Category takes values such as "desktop ", "laptop ", "server ";

Write SQL queries.

a) Write DDL statements to create the Computer table. (3 marks)
b) Find all laptop models and their makers. (2 marks)
c) Display the maker name and price of laptop models with ram 4 and hard disk size 500.
(3 marks)
d) Find the maker name and number of computer models made by ea h maker. Arrange the
output to display the number of models from highest value to lowest value.
(3 marks)
e) List down the name and phone number of makers who make more t an 6 laptop models.
(5 marks)
f) Find all makers who make the most expensive server. (4 marks)

*** End of Exam Paper ***

Page 8 of8

31/03/2016