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
 TERTIARY AND VOCATIONAL EDUCATION COMMISSION

COMMON WRITTEN EXAMINATION – 2020/ 2021

(a) “This table is not in first normal form”. Evaluate this statement.
(Marks 04)
(b) How do we bring this table into first normal form? (Marks 06)

Question 05

1.) Discuss two reasons to use the Entity Relationship (ER) Diagrams? (Marks 03)

2.) Contrast DELETE and TRUNCATE commands? (Marks 05)

3.) Consider relation R=(A,B,C,D) with the following Functional Dependencies (FDs)
AB C, C D and D A
a. List all candidate keys of R.
b. Is R in 3NF? or BCNF? (Marks 06)
4.) Define Denormalization? (Marks 06)

Question 06

A Product table of a database is given below.

Attribute Description Data type Primary Key/

Name Foreign Key

PCODE Product Code CHAR(10) Primary Key

PDESC Product Description CHAR(35)

PINDATE Stocking Date DATE

POH Units Available INT

PMIN Minimum units INT

PPRICE Product Price DECIMAL(8,2)

PDISC Discount Rate DECIMAL(4,2)

SCODE Supplier Code INT Foreign Key

Here, a supplier may supply many products and some suppliers do not yet supply products.
For example, a supplier list may include potential suppliers. If a product is supplied by a
supplier, that product is supplied only by a single supplier. Some products are not supplied
by a supplier. For example, some products may be produced “in-house”.
Page 3 of 5
 TERTIARY AND VOCATIONAL EDUCATION COMMISSION

COMMON WRITTEN EXAMINATION – 2020/ 2021

Part of the Product table is given below.

PCODE PDESC PINDATE POH PMIN PPRICE PDISC SCODE

060001 Power 03-May-06 8 5 Rs.7000.00 0.05 25595

painter

062113 Saw blade 13-Jun-06 32 12 Rs. 950.00 0.03 21333

069344 Metal screw 15-Jun-06 18 6 Rs.1400.00 0.00 23444

1.) Write the SQL code that will create the table structure for above table. (Marks 08)

2.) Write the SQL code that will enter the first two data rows into the table (Marks 06)

3.) Suppose that 20 units of product 069344 have been ordered and they have just arrived.
Write the SQL code to update the required table to show the new arrivals. (Marks 06)

Question 07

Production tracking is important in many manufacturing environments (e.g., the

pharmaceuticals industry, children‟s toys, etc.). The following ER diagram captures
important information in the tracking of production. Specifically, the ER diagram captures
relationships between production lots (or batches), individual production units, and raw
materials.

Page 4 of 5
 TERTIARY AND VOCATIONAL EDUCATION COMMISSION

COMMON WRITTEN EXAMINATION – 2020/ 2021

LotNumber

CreateDate serial-# exactWeight

Cost-Of-Materials

Production ProductType
Lot Includes
Units

qualityTest ProductDesc

Created From Units

type

Raw material-ID
Materials

UnitCost

Convert the ER diagram into a relational database schema. Be certain to indicate primary
keys and referential integrity constraints. (Marks 20)

Page 5 of 5