Question:EXON (PVT) Ltd. Sri Lanka provides Specialized Software Engineers for other Companies ontemporary basis to work for their projects. Worked time of EXON Software Engineers atother companies are recorded in the following table NICN (National Identity Card Number) isused in EXON to identify their Software Engineers Uniquely.(Table given)NICN,"Contract No",Hours,"Employee Name","Company ID","Company Location"892660522V,"IT 2014",27,A.Janith,"IT 001",Kandy880944993V,"IT 2014",84,T.Tharaka,"IT 001",Kandy842564347V,"IT 1995",42,C.Asela,"IT 120",Colombo892660522V,"IT 1995",42,A.Janith,"IT 120",Colombo1. What is Normalization?2. Define 1st Normal Form, 2nd Normal Form and 3rd Normal Form3. To which normal form this table belongs to4. Identify the Primary key of this relation and justify your selection5. Identify Fully Functional Dependencies and Partial Dependencies on the Primary Key6. Normalise the above mentioned table to 2nd Normal Form7. Identify the Transitive Dependencies on above 2nd Normal Form tables8. Normalise above mentioned tables to 3rd Normal Form9. Design an ER diagram for 3rd Normal Form.(Note: Illustrate the tables in DBML (Database Manipulation Language) and display thePrimary Key and Foreign Key in all the relations)Additional GuidelinesStudent should design a normalized ER diagram according to the following guideline1. Student has to convert a given Un-normalized data structure to its third normal form.2. Each process/ step of normalization should be described.3. Design a normalized ER diagram.

ext[ 000048 ]KIRAN ZEESHANEmployee ID :000048Department :Acedemic DepartmentDesignation :Teaching StaffEmployee Grade :-Blood Group:B PositiveEmployee Type :ContractEmployment:ConfirmedGender :FemaleContact :0332-7898045EmploymentPosition AssignmentEmployee RoasterPayrollBank AccountAddressEducationContact InformationSkillSubject RatingExperienceCards DetailDiseasesAttendanceBlock ActivityVideo IntegrationLoan HistoryLeave History New Delete SaveS.NoEmployment TypeReasonFrom DateTill Date112/31/9998

Question No. 44Under the e-Shram portal, workers will be issued an e-Shram card containing a _________ digit unique number.

Business organizations are given in column X and examples in column Y in the following tables.X Y1. State corporation2. State department3. State companiesA – Sri Lankan AirlinesB – Department of EducationC – Lanka Phosphate Ltd.D – University Grants CommissionE – Sri Lanka Port AuthorityF – Department of HealthG – Ceylon Electricity BoardSelect the the answer which correctly matches column X and column Y.(1) 1-DEG, 2-BF, 3-AC (2) 1-EB, 2-ADG, 3-FC (3) 1-AB, 2-FA, 3-CDF(4) 1-AB, 2-DC, 3-EFG (5) 1-BDF, 2-AC, 3-EF14. Main objective of green entrepreneur is,(1) to upgrade standard of living (2) to expand the market(3) to introduce new product and services (4) to use wasted resources(5) to generate positive impact on natural environment15. The following are some features and functions of money.A – General acceptance B – Medium of exchangeC – Difficult to create counterfeit D – Store of valueE – Easy to carry F – DivisibilityG – Standard of deferred paymentOut of above, the characteristics of money are,(1) A, B, C, D (2) B, C, F, G (3) A, C, E, F(4) B, C, F, G (5) A, C, E, F

Question 1: Draw the ER model for the basic information given below. Use the Chen Notation for cardinality and participation constraint. (10 points) All Pakistan Software association holds workshops in collaboration with different software houses. They wish to record detailed information regarding the following aspects. • Intellect delivering workshop – They wish to record complete details about the person who delivers lectures in workshop. Record ID, Name, Email, Address (street, city, state, zip code), Contact number,current job, and qualification details. • Workshop – Record workshop ID, Year, Theme and Venue. • Venue – The venue can be some university, hotel or conference room in a software house. We need to record LocationID, Name, Address (street, city, state,zip code) and Contact number. • Participants – For a participant, record ParticipantID, Name, Designation, Affiliating Institute and Charges. The participating entities have come up with the following rules: • A total of 200 participants are allowed in each workshop each year on a first come, first served basis. • Charges vary with the designation of the participant. • Accommodation is not provided by any host and other expenses are also not entertained.

Write queries for the following.Employee ( EID, Name, Age, Salary)Department ( DID, D_Name, EID,Country)(1) Create a view Emp_India which contains the name, age and salary of Indianemployees.(2) Display the name of employee in descending order whose Country starts with ‘I’