Databases | SQL | Oracle | MS Access Answers

Draw a class diagram and use case diagram for suppliers of building construction . Write down all the methodclass.

example: getname()

Hillar Furniture Enterprise is a furniture store owned by Dr. Hillar Addo of AIT.

The following table shows the essential data required for this application.

Attribute Name Sample Value

Material_ID 3792

Material_ Name Hinges 3” locking

Unit_of_Measure Each

Standard_Cost GHC 5.00

Vendor_ID V300

Vendor_name Apex Hardware

Unit_price GHC 4.75

Terms_Code 1

Terms COD

a. Identify the functional dependencies for this data. You may assume the following

• Each material has one or more suppliers. Each supplier may have supply one or more items or may not supply any items.
• The unit price for a material may vary from one vendor to another
• The terms of a code for a supplier uniquely identifies the terms of a sale (e.g. code 2 means 10% net 30 days, etc.). The terms for a supplier are the same for all material items ordered from that supplier.

b. Convert your data into a set of 3NF relations, identifying all the primary and foreign keys 

Draw a class diagram and use case diagram for suppliers of building construction . Write down all the methodclass.

example: getname()

Suppose we have 999 records of employees Emp1, Emp2, Emp5, Emp6 and so on up to Emp998 and Emp999 stored in a sequence. Hence, records are nothing but a row in the table. In the employee table, we want to insert new records Emp3 and Emp4 in the sequence, and once we have done insertion we need to update and retrieve the record efficiently to optimize the performance of a database by minimizing the access time when query is being executed.

You are required to solve the problem with the index sequential access method with pros and cons in the given scenario.

10

25

50

75

100

Sort Order:   

Student ID Ascending

Student ID Descending

Post Date Ascending

Post Date Descending

Rating Ascending

Rating Descending

No record found.

Suppose we have 999 records of employees Emp1, Emp2, Emp5, Emp6 and so on up to Emp998 and Emp999 stored in a sequence. Hence, records are nothing but a row in the table. In the employee table, we want to insert new records Emp3 and Emp4 in the sequence, and once we have done insertion we need to update and retrieve the record efficiently to optimize the performance of a database by minimizing the access time when query is being executed.

You are required to solve the problem with the index sequential access method with pros and cons in the given scenario.

Suppose we have 999 records of employees Emp1, Emp2, Emp5, Emp6 and so on up to Emp998 and Emp999 stored in a sequence. Hence, records are nothing but a row in the table. In the employee table, we want to insert new records Emp3 and Emp4 in the sequence, and once we have done insertion we need to update and retrieve the record efficiently to optimize the performance of a database by minimizing the access time when query is being executed.

You are required to solve the problem with the index sequential access method with pros and cons in the given scenario.

Suppose we have 999 records of employees Emp1, Emp2, Emp5, Emp6 and so on up to Emp998 and Emp999 stored in a sequence. Hence, records are nothing but a row in the table. In the employee table, we want to insert new records Emp3 and Emp4 in the sequence, and once we have done insertion we need to update and retrieve the record efficiently to optimize the performance of a database by minimizing the access time when query is being executed.

You are required to solve the problem with the index sequential access method with pros and cons in the given scenario.

Get the distinct ids of videos that belong to the following genres.

genre_id genre

201 Comedy

202 Action

203 Thriller

211 Scifi

Note:

Sort the output in the descending order of

video_id

Expected Output Format

video_id contains 14 rows

...

genre_id genre_type

201 ACTION

202 COMEDY

203 THRILLER

204 ROMANTIC

205 TECHNICAL

206 HEALTH CARE

207 GAMING

208 MUSIC VIDEOS

209 TEASER

210 INSPIRATIONAL

211 SCI-FI

Get all the channel_ids that uploaded at least 20 videos in "AI/ML", "Cyber Security", "Data Science" or "Robotics" technologies between 2018 and 2021.

Example: If a channel publishes 5 videos in AI/ML, 10 videos in Cyber Security and 5 videos in Data Science, consider the channel.

Note:

Consider all the videos that have any of the technologies mentioned above in theirname

Sort the output in the ascending order of channel_id.

Expected Output Format

channel_id

Video Table

video_id name duration_in_secs published_datetime no_of_views channel_id

1000 Getting My Driver's License | Lele Pons 3652 2011-12-05 19:00 10619 367

1001 Apple iPhone X Review: The Best Yet! 4556 2021-01-19 20:12 140012 362

User_ID

User_Name

Salary

Department

201

William

10000

MRKT

202

Michael

20000

HR

203

Anthony

30000

MRKT

204

Aiden

40000

HR

205

Matthew

50000

IT

Perform the following queries for above table:

1.    Write a SQL query to display User name who is taking maximum salary.

2.    Write a SQL query to display second highest salary from user table.

3.    Find names of user starting with ‘A’.

4.    Find number of users working in department ‘HR’.

5.    Print details of users whose name ends with ‘m’ and contains 6 Alphabets.

6.    Print details of users whose salary between 20000 and 50000.

7.    How many records do we have?

8.    write a query to find How many jobs do we have?     

1. Write a query to find unique jobs from the user table.

10. Add Column Bonus Salary in usertable.