Tran_BUS 112 Assignment 2 Fall 2023
docx
keyboard_arrow_up
School
San Jose State University *
*We aren’t endorsed by this school
Course
112
Subject
Information Systems
Date
Dec 6, 2023
Type
docx
Pages
7
Uploaded by ConstableCrabPerson2017
Part 1: MySQL
1. Write a query to find all customers whose balance is less than average customer
Balance.
SELECT *
FROM CUSTOMER
WHERE CUS_BALANCE < (SELECT AVG(CUS_BALANCE) FROM CUSTOMER);
2. Write a query to find all customers with a balance of more than 150 but less than 350.
Sort the results by customer balance (lowest first).
SELECT *
FROM CUSTOMER
WHERE CUS_BALANCE > 150 AND CUS_BALANCE < 350
ORDER BY CUS_BALANCE;
3. Write a query to find out the name of the vendor who supplied the product with
product code 'SM-18277'.
SELECT V.V_NAME
FROM VENDOR V
JOIN PRODUCT P ON V.V_CODE = P.V_CODE
WHERE P.P_CODE = 'SM-18277';
4. Write a query to generate first name, last name of customers along with invoice number
and invoice date where invoice date is between Jan 11 and February 21, 2018.
SELECT C.CUS_FNAME, C.CUS_LNAME, I.INV_NUMBER, I.INV_DATE
FROM CUSTOMER C
JOIN INVOICE I ON C.CUS_CODE = I.CUS_CODE
WHERE I.INV_DATE BETWEEN '2018-01-11' AND '2018-02-21';
5. Find a list of vendor/vendors who supplied ‘Power painter, 15 psi., 3-nozzle’.
SELECT VENDOR.*
FROM VENDOR
INNER JOIN PRODUCT ON VENDOR.V_CODE = PRODUCT.V_CODE
WHERE PRODUCT.P_DESCRIPT = 'Power painter, 15 psi., 3-nozzle';
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
6. Write a query that summarizes the value of products currently in inventory. Note that
the value of each product is a result of multiplying the units currently in inventory by the
unit price. Sort the results in descending order by subtotal.
SELECT P.P_CODE, P.P_DESCRIPT, P.P_QOH, P.P_PRICE, (P.P_QOH * P.P_PRICE) AS Subtotal
FROM PRODUCT P
ORDER BY Subtotal DESC;
Part 2: Quiz
1. Visit www.myacademic.org and log in. (You can use Gmail or create a new account).
Note: Firefox browser is not supported. Done.
2. Take the practice quiz 4 on Database Systems. You can take the quiz as many times as
you wish. This is for review only. Done.
3. While taking the quiz, create a document and write the correct answers for each
question with explanations. Here is the format to submit the answers.
Question 1: Write the question.
Correct Answer: Write the correct answer.
Explanation: Provide explanation in one or two sentences.
Create a document with answers and explanations.
Question 1: Which of the following is a characteristic of a table that is in First Normal Form (1NF)?
Correct Answer: It has no repeating groups.
Explanation: First Normal Form (1NF) requires that a table has no repeating groups to ensure each attribute contains atomic values and doesn't contain multiple values in a single field.
Question 2:
A table that records customer orders with a column for "items ordered" that includes multiple items in a single cell would violate 1NF.
Correct Answer: True
Explanation: It's true because in the First Normal Form (1NF), each cell in a table should hold a single, simple value. When a table combines multiple items into one cell, it violates this rule by not having individual values in each cell.
Question 3:
While normalization has many benefits, some potential disadvantages are increased complexity and slower query performance.
Correct Answer: True
Explanation: It can make the database schema more complex and potentially slow down queries due to table joins that take more time.
Question 4:
Please select the best answer. A relation is in 1NF if it doesn't contain any _ ?
Correct Answer: multi-valued or composite attributes
Explanation: A relation is in First Normal Form (1NF) if it doesn't contain any repeating groups. This means that each column in the table holds atomic, indivisible values, and there are no arrays, lists, or sets of values within a single cell or attribute.
Question 5:
A table that contains order details, such as OrderID, ProductID, Quantity, and Price, violates 2NF if the Price attribute is dependent only on the ProductID attribute, not on the entire primary key (OrderID and ProductID).
Correct Answer: True
Explanation: In 2NF, non-key attributes must depend on the entire primary key, not just part of it.
If "Price" depends only on "ProductID" and not the full primary key ("OrderID" and "ProductID"), it violates 2NF.
Question 6:
In 3NF, a table is in second normal form (2NF) and every non-key column is functionally dependent on the primary key.
Correct Answer: True
Explanation: Each non-key column's value is determined by the entire primary key, ensuring data integrity and eliminating partial dependencies.
Question 7:
Which of the following is a characteristic of a table that is in Second Normal Form (2NF)?
Correct Answer: IAll of these.
Explanation: A table in Second Normal Form (2NF) should have no repeating groups: Each column should contain individual values without arrays or sets, no partial dependencies: Non-
key attributes should depend entirely on the entire primary key, and It may have a composite primary key.
Question 8:
A table is in 2NF if the table is in 1NF and what other condition is met?
Correct Answer: Each non-key attribute must be dependent on the entire primary key, not just part of it.
Explanation: In 2NF, each non-key attribute must rely on the entire primary key to avoid partial dependencies and ensure proper database organization.
Question 9:
A table that is 3NF is also 1NF.
Correct Answer: True
Explanation: A table that is in 3NF is also in 1NF because 3NF builds upon the foundational rules of 1NF. In order to get to 3NF, a table must first meet the requirements of 1NF.
Question 10:
Which of the following is a characteristic of a table that is in First Normal Form (1NF)?
Correct Answer: All of these
Explanation: In First Normal Form (1NF), a table has no repeating groups, meaning each column contains atomic (indivisible) values, and there are no arrays, lists, or sets of values within a single cell or column. These criteria ensure that the table is well-organized, without redundancy or ambiguity, which is the essence of 1NF.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
WHAT TO SUBMIT
Create one document that consists of Part 1 and Part 2. Then upload it in Canvas
under Assignment 2.
Part 1: Create a word or pdf file with the SQL queries along with screenshots of the
outputs.
Part 2: Create a word or pdf file and write the correct answers for each question with
explanations.