CONFIDENTIAL 1 AC/JUN 2021/AIS275
UNIVERSITI TEKNOLOGI MARA
COMMON TEST
COURSE : DATA MANAGEMENT
COURSE CODE : AIS275
EXAMINATION : JUNE 2021
TIME : 1 HOUR 30 MINUTES
INSTRUCTIONS TO CANDIDATES
1. This question paper consists of 3 parts: Part A : MCQ
Part B : 2 questions
Part C : Case study
2. Answer ALL questions in English.
3. Please submit your HANDWRITTEN answers in ONE (1) pdf file.
DO NOT TURN THIS PAGE UNTIL YOU ARE TOLD TO DO SO
This examination paper consists of 6 printed pages
© Hak Cipta Universiti Teknologi MARA CONFIDENTIAL
CONFIDENTIAL 2 AC/JUN 2021/AIS275
PART 1 (10 MARKS)
SECTION A
This part consists of 10 multiple-choice questions. Answer ALL questions.
1. Which of the following is NOT the important question used in the planning phase of the
System Design Life Cycle (SDLC)?
A. Should the existing design be customised?
B. Should the existing design be modified?
C. Should the existing design be continued?
D. Should the existing design be replaced?
2. The feasibility must address the following aspects EXCEPT
A. the technical aspects of hardware and software requirements.
B. the system cost.
C. the operational cost.
D. the computer-aided systems engineering (CASE).
3. Normalization works through a series of stages called normal forms. For most purposes
in business database design, _____ stages are as high as you need to go in the
normalization process.
A. two
B. three
C. four
D. five
4. Attribute A _____ attribute B if all of the rows in the table that agree in value for attribute
A also agree in value for attribute B.
A. determines
B. derives from
C. control
D. owns
5. A table that has all key attributes defined, has no repeating groups, and all its attributes
are dependent on the primary key is said to be in _____.
A. 1NF
B. 2NF
C. 3NF
D. 4NF
© Hak Cipta Universiti Teknologi MARA CONFIDENTIAL
CONFIDENTIAL 3 AC/JUN 2021/AIS275
6. A(n) _____ exists when there are functional dependencies such that Y is functionally
dependent on X, Z is functionally dependent on Y, and X is the primary key.
A. partial dependency
B. repeating group
C. atomic attribute
D. transitive dependency
7. Each table _____ represents an attribute.
A. column
B. row
C. dimension
D. value
8. The _____ relationship is the “relational model ideal.”
A. 1:1
B. 1:M
C. M:1
D. M:N
9. Another name for a composite entity is a(n) _____ entity.
A. bridge
B. linked
C. directive
D. associative
10. When you define a table’s primary key, the DBMS automatically creates a(n) _____ index
on the primary key column(s) you declared.
A. key
B. composite
C. unique
D. primary
(10 marks)
© Hak Cipta Universiti Teknologi MARA CONFIDENTIAL
CONFIDENTIAL 4 AC/JUN 2021/AIS275
PART B
Answer ALL questions.
QUESTION 1
a) Identify any THREE (3) steps of Conceptual Designs.
(3 marks)
b) Below is the relational schema of an invoice table:
Invoice (Inv_No, Date, Cust_ID, Name, Part_No, Desc, Price, Tax_rate, No_Used)
Required:
Normalize the relation into 3NF. Show your answer from 1NF to 3 NF. Underline all the
primary keys and understrike (*) all the foreign keys.
(6 marks)
(Total: 9 marks)
QUESTION 2
Assume that you are using the database composed of the tables shown in figure below:
Table Name: SUPPLIER
SUPPLIER_Code SUPPLIER_Name SUPPLIER_Address SUPPLIER_Phone
SP001 Tan Ling Enterprise No 56, Taman Sri 012-5788908
Wang, Tapah
SP002 Perniagaan Jahan Lot 4714, 2, Kg. 014-5023456
Padang, Sungkai
SP003 Serba Serbi Sdn P.O Box 22, Mergong 017 -5434543
Bhd Barriage, Alor Setar
Table Name: STOCK
Inventory_Code SUPPLIER_Code Prod_ID Qtt_On_Hand EQO_Level
ST001 SP001 PDD01 RM20,000 200
ST002 SP002 PMB03 RM10,000 100
ST005 SP003 PCC05 RM15,000 150
© Hak Cipta Universiti Teknologi MARA CONFIDENTIAL
CONFIDENTIAL 5 AC/JUN 2021/AIS275
Table Name: PRODUCT
Prod_ID Prod_Name Cost_Per_Unit Sales_Price
PDD01 Prego Spaggeti sauce 200ml RM4.50 RM6.60
PMB03 Prego Mushroom sauce 200ml RM5.00 RM7.00
PCC05 Prego Cabonara sauce 200ml RM6.00 RM8.00
Required:
Complete the table’s exhibit entity integrity and referenctial integrity. Answer YES or No and
provide brief explanation for our answer.
Entity Entegrity
Table YES/NO Explanation
SUPPLIER
STOCK
PRODUCT
Referential Entegrity
Table YES/NO Explanation
SUPPLIER
STOCK
PRODUCT
(6 marks)
(Total: 15 marks)
© Hak Cipta Universiti Teknologi MARA CONFIDENTIAL
CONFIDENTIAL 6 AC/JUN 2021/AIS275
PART C
Tapah Bus Elite SDN. BHD. operates a bus rental service and needs your assistance in designing
a database system for their company. The following is the information regarding to their business
rules:
• Customers are identified by customer ID, name, phone number, address, and license
number.
• Buses are organized into three (3) different types of classes. This includes shuttle bus,
tour bus and minibus. The buses are identified by registration number, maker, model and
year.
• For each rental, the company record the date, rental period, and rate.
• Each bus needs to be checked and sent for services periodically in case of any faults. The
buses will be given a service ticket; then sent to the bus service department. Here, the
system needs to record the date sent and returned for the services along with the
information about the service needed.
• Mechanics will handle the service based on the tickets and are identified by mechanic ID,
name and address. They will also need to record details on the service done to each bus
and the cost.
Required:
a) Based on the above business process information, construct an Entity Relationship Diagram
(ERD) that show all entities, primary key(s), foreign key(s), relationships, and connectivity.
(15 marks)
END OF QUESTION PAPER
© Hak Cipta Universiti Teknologi MARA CONFIDENTIAL
