database management system, pdf is attached

COURSEWORK SECOND SEMESTER 2019/2020 SESSION (A192) COURSE CODE & NAME : BKAS3053 DATABASE MANAGEMENT SYSTEM LECTURER'S NAME : ASSOC. PROF. TS. DR. AIDI AHMI DR. MOHD HISHAM MOHD SHARIF LECTURE GROUP : A, B, C, D INSTRUCTIONS: 1. 2. 3. 4. 5. 6. 7. This paper contains THREE (3) questions in SEVEN (7) pages, including the cover page. Type ALL answers in THIS FILE and save it as pdf. Saved the document using your matric number and name. For example, 123456_YOUR_FULL_NAME.pdf You also required to save and answer some of the questions using Microsoft PowerPoint, a Proposal using another pdf file and Microsoft Access. Please create, save and name that file using your matric number and name as follow: 123456_YOUR_FULL_NAME.pptx 123456_YOUR_FULL_NAME_Proposal.pdf 123456_YOUR_FULL_NAME.accdb Upload the all the FOUR (4) files to UUM Online Learning on or before 9.00 AM, July 16, 2020 Please ensure that you DO NOT plagiarise your friend or allow your friend to plagiarise your work. Plagiarised work attracts outright cancellation of the submitted course work. ALL THE BEST QUESTION 1 (30 MARKS) A. The following diagram shows a table structure for a Project Management data. Proj_Num 1 1 1 2 2 3 3 4 4 4 Proj_Name Abc Abc Abc Klm Klm Mas Mas Vid Vid Vid Emp_Name Ali Rose Bakar Rose Dayang Ali Zain Zain Rudy Bakar Job_Code 18 18 18 15 15 13 13 19 19 19 Job_Hour 90 90 90 70 70 98 98 100 100 100 Proj_Hours 17 17.5 19 20 22 12 15 23 24 19 Emp_Phone 019-101 019-102 019-103 019102 011304 019-101 012386 012-386 014272 019-103 REQUIRED: (a) Discuss TWO (2) types of anomalies exhibited in the data structure. (2 Marks) (b) B. Suggest THREE (3) improvements to the file 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 Microsoft PowerPoint slide presentation to convince the management to use a database management system software (DBMS). Present at least FIVE (5) important functionalities of DBMS and how it can help in managing the customer information data better. Saved the document using your matric number and name. For example, 123456_YOUR_FULL_NAME.pptx (10 Marks) 2 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. 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. INVOICE Permata Minions Enterprise Date Due Date Invoice No. Cust. No. PO No. No. 41, Jalan Sintok, Taman Sintok Perdana 06010 Sintok, Kedah, Malaysia Tel: 04-9281234 Fax: 04-9281235 BILL TO: Ali bin Ahmad Ali Bookstore No. 18, Jalan Changloon, Taman Changloon 06010 Changloon, Kedah Tel: 04-9271234 Fax: 04-9271235 SHIP TO: Adi bin Ahmad Ali Bookstore No. 1, Taman Jitra 06000 Jitra, Kedah Tel: 04-9251234 Fax: 04-9251235 SHIP DATE SHIP NAME SHIPPING COST TERMS 27/5/2020 FedEx 100.00 2% 10, Net 30 PRODUCT NO. DESCRIPTION A Practical Guide to Database Design Database System Concepts 9781138578067 9780078022159 : 27/05/2020 : 26/06/2020 : INV2020/1234 : 123456 : 123444 SALES INFO. Sale Person ID: 007 Sale Person Name: Aminah binti Abu QUANTITY 40 30 OTHER COMMENTS: 1. Total payment due in 30 days. 2. Please include the invoice number when paying. UNIT PRICE 76.00 65.00 Sub Total SST 6% Shipping Discount Other Grand Total TOTAL 3,040.00 1,950.00 4,990.00 299.40 100.00 5,389.40 Figure 1: Company's Invoice REQUIRED: (a) Write and produce the first normal form (1NF). (b) (2 Marks) 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) (j) Write SQL code that will display list of customers from Kedah. (2 Marks) (k) Display the result of the SQL that you run in (j). (2 Marks) (l) 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 COURSEWORK SECOND SEMESTER 2019/2020 SESSION (A192) COURSE CODE & NAME : BKAS3053 DATABASE MANAGEMENT SYSTEM LECTURER'S NAME : ASSOC. PROF. TS. DR. AIDI AHMI DR. MOHD HISHAM MOHD SHARIF LECTURE GROUP : A, B, C, D INSTRUCTIONS: 1. 2. 3. 4. 5. 6. 7. This paper contains THREE (3) questions in SEVEN (7) pages, including the cover page. Type ALL answers in THIS FILE and save it as pdf. Saved the document using your matric number and name. For example, 123456_YOUR_FULL_NAME.pdf You also required to save and answer some of the questions using Microsoft PowerPoint, a Proposal using another pdf file and Microsoft Access. Please create, save and name that file using your matric number and name as follow: 123456_YOUR_FULL_NAME.pptx 123456_YOUR_FULL_NAME_Proposal.pdf 123456_YOUR_FULL_NAME.accdb Upload the all the FOUR (4) files to UUM Online Learning on or before 9.00 AM, July 16, 2020 Please ensure that you DO NOT plagiarise your friend or allow your friend to plagiarise your work. Plagiarised work attracts outright cancellation of the submitted course work. ALL THE BEST QUESTION 1 (30 MARKS) A. The following diagram shows a table structure for a Project Management data. Proj_Num 1 1 1 2 2 3 3 4 4 4 Proj_Name Abc Abc Abc Klm Klm Mas Mas Vid Vid Vid Emp_Name Ali Rose Bakar Rose Dayang Ali Zain Zain Rudy Bakar Job_Code 18 18 18 15 15 13 13 19 19 19 Job_Hour 90 90 90 70 70 98 98 100 100 100 Proj_Hours 17 17.5 19 20 22 12 15 23 24 19 Emp_Phone 019-101 019-102 019-103 019102 011304 019-101 012386 012-386 014272 019-103 REQUIRED: (a) Discuss TWO (2) types of anomalies exhibited in the data structure. (2 Marks) (b) B. Suggest THREE (3) improvements to the file 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 Microsoft PowerPoint slide presentation to convince the management to use a database management system software (DBMS). Present at least FIVE (5) important functionalities of DBMS and how it can help in managing the customer information data better. Saved the document using your matric number and name. For example, 123456_YOUR_FULL_NAME.pptx (10 Marks) 2 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. 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) 3 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) 4 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. 5 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. INVOICE Permata Minions Enterprise No. 41, Jalan Sintok, Taman Sintok Perdana 06010 Sintok, Kedah, Malaysia Tel: 04-9281234 Fax: 04-9281235 Date Due Date Invoice No. Cust. No. PO No. BILL TO: Ali bin Ahmad Ali Bookstore No. 18, Jalan Changloon, Taman Changloon 06010 Changloon, Kedah Tel: 04-9271234 Fax: 04-9271235 SHIP TO: Adi bin Ahmad Ali Bookstore No. 1, Taman Jitra 06000 Jitra, Kedah Tel: 04-9251234 Fax: 04-9251235 SHIP DATE SHIP NAME SHIPPING COST TERMS 27/5/2020 FedEx 100.00 2% 10, Net 30 PRODUCT NO. DESCRIPTION A Practical Guide to Database Design Database System Concepts 9781138578067 9780078022159 : 27/05/2020 : 26/06/2020 : INV2020/1234 : 123456 : 123444 SALES INFO. Sale Person ID: 007 Sale Person Name: Aminah binti Abu QUANTITY 40 30 UNIT PRICE 76.00 65.00 Sub Total SST 6% Shipping Discount Other Grand Total OTHER COMMENTS: 1. Total payment due in 30 days. 2. Please include the invoice number when paying. Figure 1: Company's Invoice 6 TOTAL 3,040.00 1,950.00 4,990.00 299.40 100.00 5,389.40 REQUIRED: (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) (j) Write SQL code that will display list of customers from Kedah. (2 Marks) (k) Display the result of the SQL that you run in (j). (2 Marks) (l) 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 7