Answered step by step
Verified Expert Solution
Question
1 Approved Answer
I need the solution of all these questions . Problem 3: Normalization (20 pts) Consider the following table and functional dependencies: PASSENGER FLIGHTNO COMPANY DEPARTURE
I need the solution of all these questions .
Problem 3: Normalization (20 pts) Consider the following table and functional dependencies: PASSENGER FLIGHTNO COMPANY DEPARTURE ARRIVAL COMPANY PASSENGER PHONE NAME CITY CITY PHONE James 76767676 1001 MEA Beirut Paris 9611111111; 9611111111 Mary 05050505 1003 Lufthansa Frankfurt London 0491231231; 0493334445 Robert 06060606 1001 MEA Beirut Paris 9611111111; 9611111111 John 81818181 1005 MEA Istanbul Beirut 9611111111; 9611222222 James 12312312 1003 Lufthansa Frankfurt London 0491231231; 0493334445 Smith 25252525 1015 Air France Paris Rome 0339998888 PASSNEGR_NAME -> PASSENGER_PHONE FLIGHT_NUM -> COMPANY, DEPARTURE_CITY, ARRIVA_CITY COMPANY -> COMPANY_PHONE 1) The table is susceptible to anomalies. Provide examples of insertion, deletion, and update anomalies 2) Describe and illustrate the process of normalizing the table into 3NF Problem 4: SQL Queries (30 pts) Consider the following relations. The primary keys are underlined while the foreign keys are in italic. User (Uld, Username, Password, Profile, Brief, Joindate) Post Code, Title, Details, PublishDate, views, status, type) Comment (Uld, PostCode, Description, commentDate) Write the corresponding SQL queries for the below questions 1. Write a query to retrieve the Username and Profile of all Users ordered alphabetically. 2. Write a query to find the titles of post having status 'pending' and published after '1/1/2022'. 3. List the username and profile for user with id=100. 4. Find the names of users who comments on post code 110 and 120Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started