Can someone help me with all questions for number 2 thanksss TT

UNIVERSITI TEKNOLOGI PETRONAS EXTENDED ASSIGNMENT MA Y 2020 SEMESTER COURSE : TEB1103/SDB1113/SCB1033 – DATA AND INFORMATION MANAGEMENT DATE : 1 SEPTEMBER 2020 TIME : 9.00 AM – 8.59 AM (24 HOURS) INSTRUCTIONS TO CANDIDATES 1. The Extended Assignment (EA) is an open -book assessment. Students can refer to online resources, learning materials, textbooks, and other reading materials to answer the questions posted in the assessment. 2. Answer ALL questions. 3. The duration to c omplete the EA is TWENTY -FOUR (24) HOURS . 4. Students are allowed ONE (1) attempt to do the EA successfully where only ONE (1) duly completed EA submission is permitted. Multiple submissions are NOT allowed. 5. MAXIMUM file size for your EA submission to be uploaded to ULearn is 20MB . 6. Please upload your answers in ONE (1) PDF file . 7. Please make sure your answer in the PDF file is clea r and readable and name your file as follows: "your name_your ID_EA Answer" 8. Late submission and unclear/unreadable answer will not be accepted. NOTE: You are required to submit “ CERTIFICATION OF ORIGINALITY ” in the first page of your answer sheet. Universiti Teknologi PETRONAS TEB1103/SDB1113 /SCB1033 2 1. The relation schema for a database table in its First Normal Form (1NF) is given below. SONG_DEDICATION ( dedicationNo , programID , programName, discjockeyID, discjockeyName, songID, songName, singerID, singerName) [NOTE : primary key/composite key is underlined] a. Sketch a table with 10 lines of data based on the relation schema given above. The data must conform to the following functional dependencies: songID → songName, singerID, s ingerName dedicationNo, programID → programName, discjockeyID, discjockeyName, songID, songName, singerID, singerName programID → programName discjockeyID → discjockeyName singerID → singerName [10 marks] b. Using the functional dependencies given in part (a) , normalize the given relation schema until its Third Normal Form (3NF). [NOTE : Show and explain all steps.] [10 marks] c. Write Structured Query Language (SQL) code to create a table structure for each of the relation schemas. Each table must indicate primary key, and foreign key(s), if applicable. Include as well the NOT NULL, UNIQUE and CHECK constraints at least once, in any of the table structure. [20 marks] TEB1103/SDB1113 /SCB1033 3 2. Given the relation schemas below for plan e maintenance database . AIRPLANE ( planeRegistrationNo , planeModelNo ) PLANEMODEL ( planeModelNo , planeModelCapacity, planeModelWeight) TECHNICIAN ( techID , techName, techAddress, techTelno, techSalary) TECHNICALEXPERT ( techID , planeModelNo ) [NOTE : Primary keys are underlined, and foreign keys are in bold] a. Based on the relation schemas given, for each of the query types listed below: propose query request of your choice; and write its solution in both relational algebra expression and equivalent SQL code. i. Selection of rows from a table based on pattern matching. [5 marks] ii. Joining of two tables with only certain rows and columns, selected to be displayed. [5 marks] iii. Joining of three tables. [5 marks] iv. Joining of two tables using the approach of subquery. [5 marks] v. Aggregate function with grouping. [5 marks] vi. Set intersection involving two relations. [5 marks] TEB1103/SDB1113 /SCB1033 4 b. Suggest and create database views based on the given relation schemas, which enables users to: i. view columns from multiple tables. [5 marks] ii. update table data through the view. [5 marks] TEB1103/SDB1113 /SCB1033 5 3. Draw a logical Entity Relationship (ER) diagram for a database which is used to keep track of the whereabouts and purpose of visitors who enter the premise of an organization. Suggest at least FIVE (5) entities, each with at least THREE (3) attributes, and FIVE (5) sample queries that can be posed to the database. [20 marks] - END OF PAPER -