Part A: Answer the following questions as comments in your .sql file. What are t
ID: 3752644 • Letter: P
Question
Part A: Answer the following questions as comments in your .sql file.
What are the three main purposes of a database? In your own words, briefly describe what the statement “a database maintains integrity of the data” means?
What does a table in a database represent? Provide an example.
What are the three types of relations in a relational database? Which is the most common? Provide an example (not in the lecture) of one of the three relations.
Given the following database diagram of the MyGuitarShop database, identify the type of relations the tables share. Identify the Foreign key(s) in each relationship. Example:
Customers make Orders. One-to-many (CustomerID).
One Customer can make many Orders. One Order can have one Customer.
Part B: Use the given MyGuitarShop database to answer these questions. Submit one query per question. In these exercises, you’ll enter and run your own SELECT statements.
1. Write a SELECT statement that shows four columns from the Products table: ProductCode, ProductName, ListPrice, and DiscountPercent. Then, run this statement to make sure it works correctly.
Add an ORDER BY clause to this statement that sorts the result set by ListPrice in descending sequence. Then, run this statement again to make sure it works correctly. This is a good way to build and test a statement, one clause at a time.
2. Write a SELECT statement that shows one column from the Customers table named FullName that joins the LastName and FirstName columns. Format this column with the last name, a comma, a space, and the first name like this:
Doe, John
Sort the result set by LastName in ascending sequence.
Only display the contacts whose last name starts with letters from M to Z.
3. Write a SELECT statement that shows these column names & data from the Products table:
ProductName The ProductName column
ListPrice The ListPrice column
DiscountPercent The DiscountPercent column
DiscountAmount A column that’s calculated from the previous two columns
DiscountPrice A column that’s calculated from the previous three columns - - don’t forget the order of arithmetic operations (PEMDAS/BODMAS)
Sort the result set by DiscountPrice in descending sequence.
4. Write a SELECT statement that shows these column names & data from the OrderItems table:
ItemID The ItemID column
ItemPrice The ItemPrice column
DiscountAmount The DiscountAmount column
Quantity The Quantity column
ItemTotal A column that’s calculated by multiplying the item price with the quantity
DiscountTotal A column that’s calculated by multiplying the discount amount with the quantity
DiscountItemTotal A column that’s calculated by subtracting the discount amount from the item price and then multiplying the result by the quantity- don’t forget the order of arithmetic operations (PEMDAS/BODMAS)
Sort the result set by ItemTotal in descending sequence.
Only display rows where the ItemTotal is greater than 500.
5. Write a SELECT statement that shows these columns from the Orders table:
OrderID The OrderID column
OrderDate The OrderDate column
ShipDate The ShipDate column
Show only the rows where the ShipDate column contains a null value.
files:
Orders Customers Orderltems CustomerlD EmailAddress Password FirstName LastName ShippingAddressID BillingAddrssID OrderID CustomerlD OrderDate ShipAmount TaxAmount ShipDate ShipAddressID CardType CardNumber CardExpires BillingAddressID ItemlD OrderID ProductID ItemPrice DiscountAmount Quantity Addresses Products AddressID CustomerlD Linel Line2 City State ZipCode Phone Disabled ProductID CategorylD ProductCode ProductName Description ListPrice DiscountPercent DateAdded Categories CategoryID CategoryNameExplanation / Answer
Please Note: I have answered Part A and all of its subparts. Please Re-Post for Part B.
Part - A :
Answer)
The main purposes of a database are to:
a) Store information in an accurate and effective way
b) Retrieve information
c) Management of data - update the data
A database maintains the integrity of the data. Data Integrity is regarding the maintenance of a stable and consistent database where the data is accurate, consistent over the lifetime of the data. Data Integrity manages the data in such a way that the data quality is maintained in the database and any changes in the database is stable and doesn't corrupt the database or have incorrect or incomplete information in the database. Data Integrity is to ensure that the quality of the database and the data in it should be of the same good quality which was expected to be. Any unintended changes should not do any damage to the integrity of the data and intentional changes have to be consistent with the data and the changes in the database have to be stable and of good quality such that later retrieval of data doesn't lead to problems.
A table in a database represents a set of data elements which are stored into a model of vertical columns (identifiable by name) and horizontal rows. A table has rows and columns and is used to store data.
Example :
Employee Table has columns: (EmployeeId, EmployeeName, Address, Telephone).
The three types of relations in a relational database are:
a) One to One relationship - where both the tables have only one record on each side of the relationship,
b) One to many relationship - where primary key table contains only one record which relates to many records in the related table.
c) Many to many relationship - where each record in the tables can relate to more than one record of the other table.
Customers makes Orders.
One-to-many
(CustomerID).
One Customer can make many Orders. One Order can have one Customer.
Customers : Addresses = One-to-many, where one customer can have many addresses.
Orders : OrderItems = One-to-many, where one Order can have many OrderItems.
Products : Categories = One-to-one, where one product can be of only one category
Products : Orders = many-to-many, where more than one product can be in the more than one Orders.