Please produce a set of normalized relations from the following table: INVOICE Invoice Customer Customer Customer Customer Product Date Invoice # ID # Name Address Phone # Style Product Unit Product Product Invoice VAT Total Description Price ID # Quantity Total
Q: Please convert following figure into a relational schema. In each table, the primary key and foreign…
A: Here the entity BankBranch is a weak entity and therefore we need to include primary key of Bank as…
Q: The manager of a consulting firm has asked you to evaluate a database that contains the table…
A: The, answer has given below:
Q: Get a list of the number of products reviewed for every retailer
A: TABLES The tables based on the given forms are shown below. Product ( PID, ModelName, Category,…
Q: B) Use subquery to answer the following questions: Work on the “Products", “Suppliers", and…
A: EXPLANATION: The SELECT command is used to select the required fields from the particular table or…
Q: Q3. Based on the type of relationship, determine which table contains the foreign key.
A: Based on the type of relationship,determine table contains the foreign key
Q: List the vendor names and address(city, state,zipcode), invoice total for all the vendors who have…
A: There are 2 table which will be joined by outer join. One table contains vendor name,…
Q: Please convert following figure into a relational schema. In each table, the primary key and foreign…
A: ANSWER:-
Q: Given the following tables (Primary Key, Foreign Key): CUSTOMER (CustomerNumber, Name, Address)…
A: Example- Considering the given tables, a view can be created for the details of sales done on a…
Q: Please convert following figure into a relational schema. In each table, the primary key and foreign…
A: Here the entity BankBranch is a weak entity and therefore we need to include primary key of Bank as…
Q: CREATE A SQL QUERY FROM QUESTION BELOW: List the name of the division that has more than one…
A: A query is to retrieve the data and execute it using commands. The subquery is defined as a query…
Q: Create a table with emp name. emp_no , emp_name , emp_grade , emp_salary , dept_no ,…
A: SQL query: creating table SQL> CREATE table emp 2 ( 3 emp_no NUMBER, 4 emp_name…
Q: Display the information about the managers and clerks from the column ‘Job’ in the table emp, order…
A: Answer: SELECT * FROM emp WHERE Job=="managers" OR Job=="clerks" ORDER BY deptno;
Q: Please convert following figure into a relational schema. In each table, the primary key and foreign…
A: The question is figure out the relational schema.
Q: Using the Company Database to execute following query write SQL queries. Query #1: Retrieve First…
A: Given, the columns to display: Fname, Lname,Dname Tables to use: EMPLOYEE, Department, Project…
Q: Write a query to display the manager name, department name, department phone number, employee name,…
A: Write a query to display the manager name, department name, department phone number, employee name,…
Q: Please convert following figure into a relational schema. In each table, the primary key and foreign…
A: Here the BankBranch entity is a weak entity and therefore we need to include the primary key of Bank…
Q: Create Department table Create table department with given columns Department department_id number…
A: In the above listed we have to create a table named department with the given table schema. Note as…
Q: Using the INVOICE table structure shown in figure below answer the following question. Write the…
A:
Q: 1. How many reviews for every product?
A: Let us suppose that the table is created as given below: create database DB; use DB; create table…
Q: Create Student table Develop a SQL Query Refer the below schema, and create the student table. Refer…
A: In Student table, we have a primary key student_id and foreign key department_id which is…
Q: For each different job_id group in the employee table, show the average salary and job_id for the…
A: SELECT Average_salary, Job_id FROM employees WHERE EXISTS (SELECT *…
Q: Show the total number of reviews of most liked product per retailer in every city Here is the…
A: The details about the table are given with table name and different entities. Here the table name is…
Q: List all products (productid, ProductDescription) and order information (orderID and orderDate)…
A: Join operation is used to fetch data records from two or more tables based on the common field.…
Q: Explain the SQL function categories. Describe the important features and applications of SQL…
A: - With our guideline restrictions we are allowed to answer only the related questions. - Answering…
Q: 1. Display detail of all employees from Employee table. Answer: 2. Display name of employees, who…
A: The first three parts will be answered 1 To select all records from a table, SELECT along with *…
Q: The code: "SELECT SupplierlID FROM Products Order By SupplierName DESC;" extracts data from Products…
A: Sol1: Current procedure terminology (CPT) is a code such as reflex testing and is an integrated part…
Q: Please convert following figure inn a relational schema, In cach table, the primery key and foreign…
A: Here the entity BankBranch is a weak entity and therefore we need to include primary key of Bank as…
Q: For the same table customer, each row is called * fields attributes foreign key tuples
A: This MCQ is based on RDBMS.
Q: Write SQL Query for each AMerican player (nation='America'), find the player's name and surname and…
A: Query for each AMerican player (nation='America'), find the player's name and surname and the date…
Q: Q1: Create the following related tables using appropriate data field specification: (Hint: Define…
A: CREATE - This command is used to create a new table in the database. ALTER- This command is used to…
Q: List the Departments's Names that have no employees (EMP and DEPT tables) select…
A: Answer: SELECT EMPNO,NVL(COMM, 0) FROM EMP; To display the word 'unassigned' wherever a NULL value…
Q: Write a query to display the manager name, department name, department phone number, employee name,…
A: SQL QUERY: SELECT M.EMP_FNAME AS [Manager FNAME], M.EMP_LNAME AS [Manager LNAME], D.DEPT_NAME,…
Q: create Table Name: PRODUCTS…
A: Given: create Table Name: PRODUCTS FIELD NAME DATA TYPE…
Q: Please convert following figure into a relational schema. In each table, the primary key and foreign…
A: Here entity Branch is a weak entity and therefore it will include primary key of bank as prime…
Q: Find all manager names and all the employees each manager supervises
A: The given sample table has EMPNO as the primary key and MGR a foreign key which is refereing to same…
Q: List the product ID from the highest selling product in France (Inner join ) Write a query to…
A: We need to write queries for given scenarios.
Q: Display the product ID and the number of orders placed for each product. Show the results in…
A: SQL (Structured Query Language) is a database computer language primarily designed for the…
Q: List the balance characteristics of the customers who have made purchases during the current invoice…
A: SELECT CUS_CODE, CUS_BALANCE FROM CUSTOMER WHERE CUSTOMER.CUS_CODE IN (SELECT DISTINCT…
Q: a)Create 3 tables(product_categories,products,product_sale) with primary key,foreign key in each…
A: Initially 3 tables are created product, product_category, product_sale. Each table is related to…
Q: Q2 Consider the following relations: BRANCH (branch no, street, area, city, degree, Tel_no, Fax_no)…
A: 1. select * from branch where branch_no<453 and area='34 east'; 2. select * from staff where…
Q: Please convert following figure into a relational schema. In each table, the primary key and foreign…
A: A relational schema is a set of relational tables and associated items that are related to one…
Q: amed company with these columns: Company_id varchar(255) Company_na
A: Create a table named company with these columns: Company_id varchar(255) Company_name varchar(255)…
Q: Please produce a set of normalized relations from the following table: INVOICE Invoice Customer…
A: The primary key for the given relation is {Invoice#, CustomerID#} The functional dependency possible…
Q: Rewrite the query below to exclude vendors from TN or KY: SELECT VENDOR.V_CODE,…
A: I have given an answer in step 2.
Q: Given the following relations (the primary keys are bold): MATCH (CodM, Home Team VisitingTeam,…
A: SQL Statement of Create and Insert : Create Table Match(CodM int primary key,HomeTeam…
Q: Table name: Supplier Column Name Role Data Type Length Constraint supplierID Stores the ID of…
A: Syntax : Alter table table_name Add column_name datatype , foreign key(col_name) references…
Q: CREATE A SQL QUERY FROM QUESTION BELOW: List the name of the division that has more than one…
A: In this question we have given 4 table and we have to create all four table . After that we have to…
Q: Write a query to display the name of the department that has the maximum staff count order by…
A: As per the given problem statement we have to develop a SQL query to display the name of the…
Step by step
Solved in 2 steps
- a) Put the following relation in BCNF: STUDENT_ACTIVITY STUDENT_ACTIVITY (StudentID, StudentName, Activity, ActivityFee, AmountPaid) StudentID StudentName Activity ActivityFee AmountPaid 1 100 Jones Golf 65.00 65.00 2 100 Jones Skiing 200.00 0.00 3 200 Davis Skiing 200.00 0.00 4 200 Davis Swimming 50.00 50.00 5 300 Garret Skiing 200.00 100.00 6 300 Garrett Swimming 50.00 50.00 7 400 Jones Golf 65.00 65.00 400 Jones Swimming 50.00 50.00 b) Put the following relation in BCNF: Student(SSN, sName, address, HScode, HSname, HScity,GPA, priority) Assuming a student can attend more than one high school, and priority is determined by GPAEmployee: (EID, Name, City, Address) Department: (DID, Department_Name, City, budget) Works_IN: (EID, DID, Annual_Salary, Start_Date) Manages: (ManagerID, DepartmentID, rank) DID and EID in Works_IN relation are foreign keys referencing the primary keys of the Department and Employee relations respectively. Manager_ID and Department_ID in the Manages relation are foreign keys referencing the primary keys of the Employee and Department respectively. From the relational schemas illustrated above, use the different relational algebra operators and operations (p, 6, n, n, U, -, A) discussed in class to build the following queries: Display employees (by name and address) from "Chicago" and with an annual salary between 60000 (included) and 90000 (excluded) then rename the output as "ChicagoEmployees" 1. 2. For each employee, who works in the "Design" department since January 2009, display the employee's name and the employee's annual salary 3. Find the name and rank of the manager who…Employee: (EID, Name, City, Address) Department: (DID, Department_Name, City, budget) Works_IN: (EID, DID, Annual_Salary, Start_Date) Manages: (ManagerID, DepartmentID, rank) DID and EID in Works_IN relation are foreign keys referencing the primary keys of the Department and Employee relations respectively. Manager_ID and Department_ID in the Manages relation are foreign keys referencing the primary keys of the Employee and Department respectively. From the relational schemas illustrated above, use the different relational algebra operators and operations (p, o, n, N, U, -, X) discussed in class to build the following queries: Display employees (by name and address) from "Chicago" and with an annual salary between 60000 (included) and 90000 (excluded) then rename the output as "ChicagoEmployees" 1. 2. For each employee, who works in the “Design" department since January 2009, display the employee's name and the employee's annual salary 3. Find the name and rank of the manager who…
- Any relation must have at least ONE Select one: a. Foreign Key b. Composite key c. Alternate key d. Candidate keyQ1. Create these two relations, then insert the tuples. (Instructor relation: ID is PK, dept_name is FK, set name not null and unique, every tuple in salary must be greater than 35000 and less than 100000) (Department relation: dept_name is PK) Department dept_name building budget Biology Comp. Sci. Elec. Eng. Watson 90000 Taylor Taylor Painter 100000 85000 Finance 120000 History Painter 50000 Music Packard 80000 Physics Watson 70000 Instructor ID dept name salary name Comp. Sci. Finance 10101 Srinivasan 65000 12121 Wu 90000 15151 Mozart Music 40000 Physics History Physics Comp. Sci. History Finance 22222 Einstein 95000 32343 El Said 60000 33456 Gold 87000 45565 Katz 75000 58583 Califieri 62000 76543 Singh 80000 76766 Crick Biolo 72000 Comp. Sci. Elec. Eng. 83821 Brandt 92000 98345 Kim 80000Name: exis Johnsin Using this unnormalized table and the five functional dependencies below, determine the first, second, and third normal forms. In other words, normalize Relation1 up to third normal form. Unnormalized Table: Relation1 A B CD Primary key: A, B Functional dependencies fd1: A, B C, D, E, F, G fd2: B C fd3: AD E-F fd4: fd5: G, BH First Normal Form Second Normal Form Third Normal Form G E F H
- convert it to relations that are normalizationQ2/ write the schema that descibes the following relations: 1- The course relation course.id title deptname Biology Biology Biology Intro. to Computer Science Comp. Sci. Comp. Sci. Comp. Sci. Comp. Sci. Database System Concepts Comp. Sci. Elec. Eng. Finance History Music Physics credits BIO-101 Intro. to Biology 4 BIO-301 BIO-399 CS-101 CS-190 CS-315 CS-319 CS-347 EE-181 FIN-201 HIS-351 MU-199 Genetics 4 Computational Biology 4 4 Game Design Robotics Image Processing Intro. to Digital Systems Investment Banking World History Music Video Production Physical Principles PHY-101 2- The section relation course.id secid building rooIJumber timeslot.id semester ynar BIO-101 BIO-301 CS-101 CS-101 CS-190 CS-190 CS-315 CS-319 CS-319 CS-347 EE-181 1 Summer Summer Fall 2009 Painter 514 514 101 101 3128 2010 Painter 2009 Packard 1 Spring 1 Packard 2009 Taylor 2009 Taylor 2010 | Watson 2010 2010 Spring Spring E Spring 3128 120 100 3128 3128 Spring 2. Watson 2010 Taylor 2009 Taylor 2009 Taylor…Regional Labs is a company that conducts research and development work on a contract basis for other companies and organizations. The table below shows data that Regional Labs collects about projects and the employees assigned to them. This data is stored in a relation (table) named PROJECT: PROJECT(ProjectID, EmployeeName, EmployeeSalary)
