Databases | SQL | Oracle | MS Access Answers

Consider the following relation with set of functional dependencies

R(ABCDEF)

AB->CD, CD->EF, BC->DEF, D->B, CE->F

a) Identify the candidate keys in the above relation (with proper steps followed).

b) Identify which normal form this relation is in and reason for the answer.

create a table and paste the screenshots of the execution done of the given questions:

Write q query to find all the employees whose salary is between 50000 to 100000.

Write a query to find the names of employees that begin with S.

Write a query to retrieve the EmpFname and EmpLname in a single column as FullName. The first name and the last name must be separated with space.

Write a query to fetch all employees who also hold the managerial position using inner join.

Write a query to fetch the department-wise count of employees sorted by departments count in ascending order.

Explain data normalization with examples. 

Create SQL statement (JOIN) to display a list with salesman name, customer name and their cities for the salesmen and customer who belongs to the same city. (Use the JOIN statement))
Question 2- 10 pts
Create a function that calculate the total commission
Question 3 -10pts
Create a store procedure that displays the names of salesman located in Paris and their commission

Make a mailing list of vendors who provide bikes. Include a Type column that says Vendor. Make sure each distinct vendor only appears once. (5 rows)

Perform steps 1 and 2 of the CSDP for the following output reports.

Retailer Item Quantity sold

Infoware SQL+ 330

Zappo Pascal 330

WordLight 200

SoftwareLand SQL+ 300

Zapppo Pascal

The names and gender of various people are indicated below:

Male: Fred, Tom

Female: Ann, Mary, Sue

1.                 Express the information about Fred and Ann in unary facts.

2.                 Draw a conceptual schema diagram based on this choice

3.                 Express the same information in terms of binary elementary facts.

4.                 Draw a conceptual schema diagram based on this choice.

Note: For the rest of this exercise, avoid using unary facts.

Bohlale Clothing is located in a number of malls such as Maseru Mall, Pioneer Mall, Sefika etc . Each location

is identified by its location ID, address town/city, contact number and business type Business Type can be either

a store or a warehouse.

b) Bohlale employs many employees on different position, where each employee has a location, first name,

surname, title (salesman, buyer, receiver, and manager), address city/town contact number, fulltime or part time

and rate.

c) The main mandate of the store is to sell clothes and other dressing accessories. Each clothing product has a

unique ID, description, category (shirt, dress, trousers etc), gender (clothes for woman or man), manufacturer ID,

cost price and sales price.

d) Clothing in Bohlale store have varies sizes and colors. The store requires information on the size in which the

clothing item is manufactured together with the color.

Implemen the following business rules in SQL database:

a) The purchase order value must never exceed M500 000. In instances where the purchase order surpasses M500

000 , a message should be displayed

b) The selling price must be greater than the unit buying price

c) Clothing with the same ID cannot be sold to two customers

d) A message should be displayed when stock levels are less than 10 for each product in stock

Case Study

Due to COVID-19 regulations Bohlale Clothing has decided to implement an online system to manage purchasing,

inventory and sales of its products. You have been requested to create a functional database to ensure that the following

information is captured:

a) Bohlale Clothing is located in a number of malls such as Maseru Mall, Pioneer Mall, Sefika etc . Each location

is identified by its location ID, address town/city, contact number and business type Business Type can be either

a store or a warehouse.

b) Bohlale employs many employees on different position, where each employee has a location, first name,

surname, title (salesman, buyer, receiver, and manager), address city/town contact number, fulltime or part time

and rate.

c) The main mandate of the store is to sell clothes and other dressing accessories. Each clothing product has a

d) List new stock received on a given date (order by branch