QUESTIDN 1 (3n MAKES.) A. The following diagram shows a table structure for a Project Management data. _u _u _u Iaa- Iaa- m m - - REQUIRED: (a) Discuss TWO [2) types of anomalies exhibited in the data structure. [2 Marks} {b} Suggest TEIREE {3) improvements to the le structure to help solve the above problems. [3 Marks} Consider yourself as a recruit in a small company. This company performs several business operations and maintains their customer information manually in a physical book As an Accounting Information System graduate, you understand the advantages and value of using a database management system to manage the customer data. REQUIRED: Prepare a Duhcroso PowerPoint slide presentation to convince the management to use a database management system soware (DBMS). Present at least FIVE {5) important inctionalities of DBMS and hon.r it can help in managing the customer information data better. Saved the document using your matric number and name. For example, 1 23456_YDUR_FUL L_NAl.'eIE .pptr [ll] Marks} C. After the presentation about the functionalities and advantages of using DBMS to manage customer information data in the company, the general manager is impressed and agrees to use DBMS in the company to manage customer information. The manager then asks you to develop a complete proposal for the development of the database system. Database Initial Study Database Design Implementation and loading Testing and evaluation Operation Maintenance and evaluation REQUIRED: Based on the database life cycle phases as depicted above, develop a complete proposal to show the process of the customer information system database development. Create a separate pdf file for the proposal. Saved the document using your matric number and name. For example, 123456_YOUR_FULL_NAME_Proposal.pdf (15 Marks)QUESTION 2 (20 MARKS) UUM hospital is a hospital that includes several departments and ward such as Orthopaedic, Pathology, Emergency, Dental, Gynaecology, Anaesthetics, I.C.U., Blood Bank, Operation Theater, Laboratory, M.R.I., Neurology, Cardiology, Cancer Department, Corpse, etc. Each department has its own consultants, specialists, medical officer, houseman, medical assistant, staff nurses, and cleaners. Patients having different kinds of illness will go to the specific department to get treatment and medication. If required, patients are admitted in the department ward and discharged after treatment. When patients go to the department front counter, they will be registered by the staff nurses. Medical assistants will then take each patients history of illness and do a blood pressure check. The report will be sent to the doctor, and the doctor will check up their illness. Based on the treatment, specific medication is prescribed, and the patient can go home. If the patient needs to be warded for further treatment, a bed in ward will be assigned to them, and their daily progress will be monitored and recorded. After the treatment in the ward is completed, the doctor discharges the patient, and the finance department will calculate all the costs that need to be paid by the patient. REQUIRED (a) Given the case study, identify FIVE (5) main entities involved in the situation. (2.5 Marks) (b) For each of the entities, identify the primary key and TWO (2) other attributes. (7.5 Marks) (c) Using the Crow's foot notation, create a complete Entity Relationship Diagram which includes all the Primary Key, Foreign Key, Relationship and Cardinalities based on the case study. (10 Marks)QUESTION 3 (50 MARKS) The Permata Minions Enterprise is a company that supplies books to most of the major bookstores in Kedah. This company also a distributor for various publishers. Established since 1999, the company was carrying on its business as supplier and distributor of books in the Northern Region of Malaysia. Its main items include various types of textbooks for schools, colleges and universities as well as other types of books for the other retail bookstores and communities nearby. The company sell both fiction and non-fiction types of books with various types of genres. Since the establishment of the company, most of its business transactions are recorded manually. Microsoft Excel has been used to record certain of their business transaction since 2011 when they first bought a computer with their cash register machine. The company have had to use the Microsoft Excel until they realised that their accounting and stock records, as well as their financial reports, will be more accurate if a new computerised accounting system can be implemented in their business. A lot of other advantages also can be gained by implementing that application. In 2019, the company appointed you as a practical student to develop a database system for the company. You are required to develop a database system using Microsoft Access. There are a few phases that need to be followed by you. Phase 1: Develop a Conceptual Design and an Entity Relationship Diagram (ERD) In this first phase, you are required to do the normalisation process based on the business documents gathered from the company. The objectives of the normalisation process are to be able to characterise the level of redundancy in a relational schema and to provide mechanisms for transforming schemas in order to remove redundancy. The first document that has been analysed by you is the company's invoice (see Figure 1). You needs to understand how the data can be stored in the database. At the same time, you need to identify the data type for each of the fields within the tables in the database. Phase 2: System Interfaces In the second stage, you are required to identify and design the forms as part of the user interfaces of the system. Based on the ERD, as well as the tables that have been created in the previous phase, you need to present the system contents and flow on how the system should work in the computerised environment. The system's interface design should be implemented in this phase. Phase 3: Queries Concurrently, while designing the system interfaces, you also need to identify some advanced form that requires you to conduct the queries using the Structured Query Languages (SQL). The invoice form, for example, requires you to run the SQL in the query section in Microsoft Access. Phase 4: Modules Some of the features in the system sometimes require you to apply basic programming codes. You are required to develop the algorithms for some of the event that occurs within the system. These codes need to be implemented in the modules section in Microsoft Access.Phase 5: Establish the System Internal Controls In order to enhance the credibility of the system, you are required to establish the internal controls that should be enabled in the application, such as the user credential and input control for the specific fields. Phase 6: Reporting The backbone of the system is to produce meaningful reports for decision-makers. Thus, this phase required the student to design various types of reports that can be produced from the system, especially to be used by the managers of the company. Business documents such as invoice and receipt also need to be developed and generated from the report section in Microsoft Access. Permata Minions Enterprise INVOICE No. 41, Jakin Sigink, Taman Stink Perdana Dade : 27/0672030 06010 Sick, Kedah, Malaysia Due Date : 2606 2020 Tel: 04-9381234 Fax: 04-9281235 Invoice No. : INV2030/1234 Cury. No. PD NO. : 123456 : 123444 BILL TUK SHIP TO: SALES INFO. Ali bin Ahmad Adi hin Ahmad Sale Person ID: 00 All Hookstore All Bookstore Sale Person Name: Arojugh binti Abu No. 18, Jalan Changlogy, Taman Changlogy No. 1, Taman Jitre 06010 Chapplogy Kedah 06000 Jitza, Kedah Tel: 04-9271234 Fax: 04-9271235 Tel: 04-9351324 Fan: 04-9251235 SHIP DATE SHIP NAME SHIPPING COST TERMS FedEx 21 10, Net 30 PRODUCT NO. DESCRIPTION UNIT PRICE TOTAL A Practical Guide to Database Design 10 9780078122159 Database System Concepts Sub Total OTHER COMMENTS: SST 6% 299.40 1. Total payment due in 30 days. Shipping 2. Please include the invoice number when paying. Discount Other Grand Total 5.389.40 Figure 1: Company's Invoice 6REQUIRED: (a) Write and produce the first normal form (1NF). (2 Marks) (b) Write and produce the second normal form (2NF) based on 1NF in (a). (3 Marks) (c) Write and produce the third normal form (3NF) based on 2NF in (b). (3 Marks) (d) Write and produce the fourth normal form (4NF) based on the 3NF in (c). (3 Marks) (e) Design the tables based on the entities and attributes identified in (d) using Microsoft Access. Please make sure that all the data types, primary keys and foreign keys have been assigned appropriately. Naming convention needs to be followed as well. (10 Marks) (f) Draw the relational database diagram in the same Microsoft Access file. The screenshot of the relational database diagram needs to be inserted in the answer sheet as well. Saved the document using your matric number and name. For example, 123456_YOUR FULL NAME.accdb (7 Marks) (g) Insert at least TEN (10) records for each table. Use SQL command to insert all of these records. (5 Marks) (h) Write SQL code to create a PRODUCT table. (2 Marks) (i) Column TOTAL as per Figure 1 is a calculated field that can be generated from the query of join table of PRODUCT and INVOICE_DETAILS. Write SQL code to calculate that TOTAL. (4 Marks) (i) Write SQL code that will display list of customers from Kedah. (2 Marks) (k) Display the result of the SQL that you run in (). (2 Marks) (1) Write SQL code to add new field named PRODUCT_CATEGORY in PRODUCT table. (2 Marks) (m) Suggest FIVE (5) meaningful reports that can be generated from this database. (5 Marks) END OF QUESTIONS