TERTIARY AND VOCATIONAL EDUCATION COMMISSION
COMMON WRITTEN EXAMINATION – 2020/ 2021
All rights reserved.
Diploma in Information & Communication Technology
NVQ Level 05 –Semester I
Database Systems I – Theory K72T001M01 Three Hours
Answer only 05 questions including Question 01 and 02
Question 01
Construct an ER diagram to model a database for the given information.
Suppose that you are designing a schema to record information about reality shows
on TV. Your database needs to record the following information:
For each reality show, its name, genre, basic_info and participants name. Any
reality show has at least two or more participants.
For each producer, the company name, company country. A show is produced
by exactly one producer. One producer produces exactly one show.
For each television, its name, start year, head office. A television may
broadcasts multiple shows. Each show is broadcasted by exactly one television.
For each user, his/her username, password, and age. A user may rate multiple
shows and a show may be rated by multiple users. Each rating has a score of 0
to 10.
(Marks 20)
Question 02
The following tables form part of a database held in a relational DBMS.
Hotel (hotelNo, hotelName, city)
Room (roomNo, hotelNo, type, price)
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
Guest (guestNo, guestName, guestAddress)
Hotel contains hotel details and hotelNo is the primary key. Room contains room details for
each hotel and (roomNo, hotelNo) forms the primary key. Booking contains details of the
Page 1 of 5
, TERTIARY AND VOCATIONAL EDUCATION COMMISSION
COMMON WRITTEN EXAMINATION – 2020/ 2021
bookings and (hotelNo, guestNo, dateFrom) forms the primary key and Guest
contains guest details and guestNo is the primary key.
Write the following queries in SQL using above relations:
1.) Find hotel name and city of all hotels. (Marks 02)
2.) Find the names and addresses of all guests who are living in London, alphabetically ordered
by name. (Marks 06)
3.) Find all double or family rooms (type = „D‟ or „F‟) with a price below Rs 25000.00 per
night, in ascending order of price. (Marks 07)
4.) Find the bookings for which no „dateTo‟ has been specified. (Marks 05)
Question 03
1.) List two data manipulation (DML) commands in SQL. (Marks 02)
2.) Discuss two characteristics of DBMS. (Marks 06)
3.) Compare two differences between File system and DBMS. (Marks 06)
4.) Give a brief note on the term data independence in the context of relational databases.
(Marks 06)
Question 04
1.) Illustrate three schema architecture in brief. (Marks 06)
2.) Normalization is a process within logical design. What is the general goal of normalization?
(Marks 04)
3.)
Product ID Colour Price
1 red, green 15.99
2 yellow 23.99
3 green 17.5
4 yellow, blue 9.99
5 red 29.99
Product_Table
Page 2 of 5
COMMON WRITTEN EXAMINATION – 2020/ 2021
All rights reserved.
Diploma in Information & Communication Technology
NVQ Level 05 –Semester I
Database Systems I – Theory K72T001M01 Three Hours
Answer only 05 questions including Question 01 and 02
Question 01
Construct an ER diagram to model a database for the given information.
Suppose that you are designing a schema to record information about reality shows
on TV. Your database needs to record the following information:
For each reality show, its name, genre, basic_info and participants name. Any
reality show has at least two or more participants.
For each producer, the company name, company country. A show is produced
by exactly one producer. One producer produces exactly one show.
For each television, its name, start year, head office. A television may
broadcasts multiple shows. Each show is broadcasted by exactly one television.
For each user, his/her username, password, and age. A user may rate multiple
shows and a show may be rated by multiple users. Each rating has a score of 0
to 10.
(Marks 20)
Question 02
The following tables form part of a database held in a relational DBMS.
Hotel (hotelNo, hotelName, city)
Room (roomNo, hotelNo, type, price)
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
Guest (guestNo, guestName, guestAddress)
Hotel contains hotel details and hotelNo is the primary key. Room contains room details for
each hotel and (roomNo, hotelNo) forms the primary key. Booking contains details of the
Page 1 of 5
, TERTIARY AND VOCATIONAL EDUCATION COMMISSION
COMMON WRITTEN EXAMINATION – 2020/ 2021
bookings and (hotelNo, guestNo, dateFrom) forms the primary key and Guest
contains guest details and guestNo is the primary key.
Write the following queries in SQL using above relations:
1.) Find hotel name and city of all hotels. (Marks 02)
2.) Find the names and addresses of all guests who are living in London, alphabetically ordered
by name. (Marks 06)
3.) Find all double or family rooms (type = „D‟ or „F‟) with a price below Rs 25000.00 per
night, in ascending order of price. (Marks 07)
4.) Find the bookings for which no „dateTo‟ has been specified. (Marks 05)
Question 03
1.) List two data manipulation (DML) commands in SQL. (Marks 02)
2.) Discuss two characteristics of DBMS. (Marks 06)
3.) Compare two differences between File system and DBMS. (Marks 06)
4.) Give a brief note on the term data independence in the context of relational databases.
(Marks 06)
Question 04
1.) Illustrate three schema architecture in brief. (Marks 06)
2.) Normalization is a process within logical design. What is the general goal of normalization?
(Marks 04)
3.)
Product ID Colour Price
1 red, green 15.99
2 yellow 23.99
3 green 17.5
4 yellow, blue 9.99
5 red 29.99
Product_Table
Page 2 of 5
