Database System

Figure 1 shows an extract from a company report, which describes projects being worked upon by employees. Project Management Report Project Code: PC010 Project Title: Pensions System Project Manager: Daud Alam Project Budget: RM 24,500 Employee No. Employee Name Hourly Rate Department No. Department Name S10001 Ali Smith L004 IT RM 22.00 S10030 Wahid Jones L023 Pensions RM 18.50 S21010 Fahmi Lewis L004 IT RM 21.00 S00232 Rahim Smith L003 Programming RM 26.00 Total Staff on Project: 4 Average Hourly Rate: RM 21.88 Figure 1: Project Management Report for Project Code: PC101 A) Phase 1 - Database Design 1. Insert three (3) new data for Employee No., Employee Name, Department No., Department Name and Hourly Rate in Project Management Report for Project Code: PC010 as shown in Figure 1. Draw your new report for Project Code: PC010. [1.5 marks] 2. Draw Unnormalized Table after step A)1 above. [2 marks] [See Next Page MTD3033 : Database System 3 3. Based on Unnormalized Table from step A)2, you need to perform complete normalization process until Third Normalization Form (3NF). Draw complete normalization table for each normalization process. [9 marks] B) Phase 2 - Database Development Based on the answer from Third Normalization Form (3NF) in step no A)3, develop the database using any Database Management System (DBMS), you must follow the following activities for your database development process. Activity 1 - SQL - Data Definition Language 1. Creating a Database. Database Name SQL Statement Output [2.5 marks] 2. Creating Table. SQL Statement Output Tables Name Table 1: Table 2: Table 3: Table 4: [10 marks] Activity 2 - SQL - Data Manipulation Language Based on the information from Activity 1 - SQL - Data Definition Language, perform the following activity: Insert at least two (2) data for ALL tables that have been created in Activity 1 SQL Statement Output Insert Table 1: Table 2: Table 3: Table 4: + An Activity 3 - SQL - Select Statement Based on the information from your Activity 1 - SQL - Data Definition Language and Activity 2 - SQL - Data Manipulation Language, write a complete SQL statement and output for: 1. Write a SELECT statement to retrieve all columns and all rows from any table. SQL Statement Output Your requirement (example requirement: To retrieve student name and student address from student table) [2.5 marks] Write a SELECT statement to retrieve two (2) columns and all rows from any table. Your requirement SQL Statement Output + + [2.5 maj 3. Write a SELECT statement that retrieve any four (4) columns from any two (2) tables. Your requirement SQL Statement Output [2.5 marks] 3. Write a SELECT statement that retrieve any four (4) columns from any two (2) tables. Your requirement SQL Statement Output [2.5 marks] Write SELECT statements that is used comparison operators. Output + Comparison Your requirement SQL Statement Operators = equal to > greater than >= greater than or equal to