CONFIDENTIAL (CDIJAN 2024/1CT200/1TS232 UNIVERSITI TEKNOLOGI MARA FINAL EXAMINATION COURSE : INTRODUCTION TO DATABASE MANAGEMENT / INTRODUCTION TO DATABASE MANAGEMENT SYSTEMS COURSE CODE IcT200/ITS232 EXAMINATION : JANUARY 2024 TIME : 3HOURS 1 i) Answer PART A in the Objective Answer Sheet. il) Answer PART B and C in the Answer Booklet. Start each answer on a new page. 3, Do not bring any material into the examination room unless permission is given by the invigilator. 4, Please check to make sure that this examination pack consists of: i) the Question Paper il) an Answer Bookiet ~ provided by the Faculty i) an Objective Answer Sheet — provided by the Faculty 5. Answer ALL questions in Engish DO NOT TURN THIS PAGE UNTIL YOU ARE TOLD TO DO SO This examination paper consists of 13 printed pages (© Hak Cipta Universiti Teknologi MARA CONFIDENTIAL,CONFIDENTIAL, 2 CDIJAN 2024/1CT200/TS232 PARTA QUESTION 1 Below are listed the disadvantages of database systems EXCEPT. A. increased costs B_ easy to manage C_ maintaining currency D_ frequent upgrade/replacement cycles (1 mark) QUESTION 2 Which of the following is NOT related to the problems faced during the usage of a File System Data Processing before the implementation of the DBMS system? ‘A. Difficulty in getting quick answers. This is due to the need to write programs to produce even the simplest reports makes and make ad hoc queries impossible. B_ Easy to make changes to existing structures. This is because the location of the file and folders are kept in separate physical cabinets. (1 mark) QUESTION 3 Which of the following TWO statements are TRUE regarding a centralized and distributed database based on their location? |. centralised database supports data located at a single site. I. centralised database supports only one user at a time. I. distributed database supports data distributed across several sites. IV. Adistributed database supports multiple users at a time. A landil. B illand Iv. © Nand Ill. D land iv. (1 mark) (© Hak Cipta Universiti Teknologl MARA. ‘CONFIDENTIALCONFIDENTIAL, 3 CDIJAN 2024/1CT200/IT$232 QUESTION 4 Representation of relational database's entities, attributes within those en relationships between those entities is referring to. A_ Relational method B_ Relational model © Relational diagram D_ Relational approach and (1 mark) QUESTION 5 The disadvantages of the network model are as followings EXCEPT. A Cumbersome. B Lack of ad hoc query capability placed burden on programmers to generate code for reports. C_ Support structural independent D_ Structural change in the database could produce havoc in all application programs. (1 mark) In data abstraction| JERBU KA.. indent? A. External and | B_ Internal and Physical model C_ Conceptual and Internal model D_ Internal model only QUESTION 6 (1 mark) QUESTION 7 “A customer may generate one or more invoices”. ‘An invoice is generated by one and only one customer". These statements are referring to A. Relationships are bi-directional B_ Relationships are unidirectional © Relationships are semi-directional D_ Relationships are full-directional (1 mark) (© Hak Cipta Universiti Teknolog! MARA CONFIDENTIALCONFIDENTIAL, 4 CDIJAN 2024/1CT200/TS232 QUESTION 8 Which of the following statements about the primary key is CORRECT in order to ensure the entity integrity? |. Allof the values in the primary key must be unique. I. No key attribute in the primary key can contain a null. Il, The primary key must appear in another table as foreign key. IV. The primary key is not used for data retrieval. A tonly B landil Cc I,land tll D Allofthe above (1: mark) QUESTION 9 To avoid null, some designers use special codes, known as to indicate the absence of some value Not null ome | TERBUKA Algebra vom> (1 mark) QUESTION 10 A(n) is a DBMS component that stores data about data and also data that are ‘external to the DBMS. A. Index B View C Data dictionary D Data schema (1 mark) QUESTION 11 What is the primary purpose of an alternate key (secondary key) in a relational database? A. To improve query performance. B_ To provide an alternative way of uniquely identifying records. C To establish relationships between tables. D To enforce data consistency. (4 mark) (© Hak Cipta Universiti Teknologi MARA CONFIDENTIALCONFIDENTIAL 5 CDIJAN 2024/1CT200/IT$232 QUESTION 12 Refer to the following diagram. Which of the following statements is CORRECT about the above relationship? A Each course will be offered at least one class. B_ Each course will be offered only one class, © For each course, no class will be offered. D There are some courses that will be not offered. (1 mark) QUESTION 13 Ghent eos TE Each lecturer condi era ‘managed by at least one lecturer. The above business rule is an example of _. A. 0-1 relationship B 1-1 relationship © 1=Mrelationship D_ M-—N relationship (1 mark) (© Hak Cipta Universiti Toknologl MARA. CONFIDENTIALCONFIDENTIAL 6 CDIJAN 2024/CT200/ITS232 QUESTION 14 Given the following table. fStaffiD BpouseName earMaried [root jasrina Rami [1970 frooe [Karimah Abd Salim 985 002 Varina Mahmud pois 005 ULL poe 008 Ku Nora Mond Azrin 2020 005 [Pbsul Rahim Mahmud ozs ‘The SpouseName can be classified as_. A Multivalued attribute B_ Single-valued attribute C Foreign key D Composite attribute (1 mark) QUESTION 15 a ER BAI KA, A When all repe: are directly related to the p B_ When any repeating fields have been removed and the table is given a primary key. When all repeating entries of data are removed and the fields in each table are directly related to the primary key and no transitive dependencies. D_ None of the above is true. (1 mark) QUESTION 16 The following statements are the disadvantage of denormalization EXCEPT: ‘A. Data updates are less efficient because table are large. B_ Reduces system speed. CC. Indexing is more inconvenient. D_ Easy for creating virtual tables known as views. (1 mark) (© Hak Cipta Universiti Teknologi MARA, CONFIDENTIALCONFIDENTIAL 7 CDIJAN 2024/1CT200/ITS232 QUESTION 17 is when a non-key attribute determines another non-key attribute. Partial dependency Full dependency Transitive dependency Derived attribute com> (1 mark) QUESTION 18 There are five phases in the system development life cycle (SDLC). Which of the following is an activity in phase five? A analysis B_ maintenance C planning D_ implementation (1 mark) QUESTION 19 nesnnn we foe oft He hI fh Corrective maintenance adaptive maintenance com> (4 mark) QUESTION 20 ‘The following phase include in Database Life Cycle (DBLC) EXCEPT. A Planning B_ Database design CC Implementation and loading D_ Testing and evaluation (1 mark) (© Hak Cipta Universiti Teknologl MARA CONFIDENTIALCONFIDENTIAL 8 CDIJAN 2024/ICT200/TS232 PARTB QUESTION 1 a) Historically, organizations use manual file systems to facilitate their data organization and management. However, the system then evolved to the computerized file systems. i) List TWO reasons why manual file systems evolved to computerized file systems. ')_ List THREE problems faced by organizations in using computerized file systems. (5 marks) b) The most popular way of classifying a database is based on its use, i) Briefly explain Transactional Database and Analytical Database, by how they are used. ii) List THREE features of the Data Warehouse. (5 marks) QUESTION 2 a) Explain the difference between an entity and an attribute in the context of an Entity Relationship Model (ERM). Provide ONE example for each, (4 marks) b) Write TWO (2) business rules for the following diagram. oo — F oma — paws | te [° Medeata | i = | | (4 marks) (© Hak Cipta Universiti Toknologl MARA. CONFIDENTIALCONFIDENTIAL 9 CDIJAN 2024/1CT200/IT S232 QUESTION 3 Given the following relational schemas: LIFT (Lift_ID, Building_No, Lift_Capacity) BUILDING (Building_No, Building_Name) STAFF (Staff_No, First_Name, Last_Name) MAINTENANCE (Lift_ID, staff_No, Maintenance_Date) a) i) Identify Primary key and the foreign key(s) (if any) for each table. ji) Briefly Expfa satisfies the entity integrity. (6 marks) b) Create a crow’s foot Entity Relationship (ER) Diagram for all tables in the relational schema, (5 marks) (© Hak Cipta Universiti Teknologi MARA CONFIDENTIAL,CONFIDENTIAL 10 (CDIJAN 2024/1CT200/ITS232 QUESTION 4 Given the following table named Student. (StuaicNum Fame Pate OF Birth se (200718.07-5678____ALINARAMILT fiariv/2020 E [pr0z26-02-6221 jUSOR AHMAD __pa70212021 80503-07-9982 jARLINA SALMAN [19/05/2079 5 peor0T-07-7643 BAAD HARUN T1202 B a) What is a derived attribute? Why is the derived attribute not recommended to be added in the table or relation? ) Identity the derived attribute from the above table. (5 marks) QUESTION 5 The following questions will be based on the given relational schema of the Client Rental: ClientRental (clien{No, clientName, {propertyNo, propertyAddress, reniStar, relitFinish, rentPrice, ownerNt “TE, R UW Assume that a clieft can fent BI! KA. ted by more than one client. a) State the stage of normalization for the above relational schema. Give TWO (2) reasons for your answer. (3 marks) b) Identify and list TWO (2) primary keys. (2 marks) ) Identify and list the partial and transitive dependencies. (3 marks) d) Normalize into 3NF. Show all works for each normalization stage. (7 marks) e) Draw the ERD to show the relationships among all the normalized tables. (5 marks) (© Hak Cipta Universiti Teknologi MARA CONFIDENTIALCONFIDENTIAL 1 CDIJAN 2024/1 T200/TS232 QUESTION 6 a) Conceptual design is one of four stages in database design process. State another THREE (3) stages of database design process. (3 marks) b) Briefly explain conceptual design in database design process. (3 marks) TERBUKA (© Hak Cipta Universiti Toknologl MARA. CONFIDENTIAL,‘CONFIDENTIAL, 2 CDIJAN 2024/1CT200/1TS232 PARTC QUESTION 1 A law firm named Kamil & Co. Melaka received many cases since the year they formed the ‘company. Therefore, they decided to employ the database designers to create a database that will be used to store the information of important entities in the company’s business activities. The following are the current system description that associated with Kamil & Co. Melaka firm. + The client who would like to acquire the lawyers from this firm must register their details in the registration form, The information that must be recorded are the client's number, name, address, city, state, postal code, telephone number and the date of birth. ‘+ The lawyers’ details that are stored in the system are the lawyers’ id, name, address, city, state, postal code, telephone, date of birth, qualification, specialty and bar. + Sometimes a client may have more than one case to be heard in court. The data of case id, case description and the type of cases must be documented for future references! + There are case. Crit robbery, a exists to pi breach, property damage, negligence and defamation. The system needs to store the type of punishments for these two different cases. The criminal case will store the custody description while the civil case will record on financial penalty amount. + The lawyer manages many cases but for each case it must be handled by only that lawyer. The case will be assigned to one court only. The courts’ data that need to be stored are the courts’ id, name, type, city, state and postal code. Each court has many judges assigned to it. The judges’ id, name, telephone number and years of practice must be recorded. Each judge is assigned to exactly one court. {© Hak Cipta Universiti Teknologi MARA CONFIDENTIALCONFIDENTIAL 13 CDIJAN 2024/1CT200/IT S232 Based on the above current system description, answer the following questions: a) Draw a complete Entity Relationship diagram based on the information given above. Show all entities, attributes, relationships and connectivities involved. (16 marks) b) List TWO (2) example of reports that can be produced from this database system. (4 marks) END OF QUESTION PAPER TERBUKA (© Hak Cipta Universiti Teknologi MARA, CONFIDENTIAL