Final Project (100 points) Fall 2020/IT 2060
(44 points) Answer questions 1-4 in a Word Document (.rtf, .doc, .docx, .pdf). Write complete sentences with correct grammar and spelling. You will lose points if there are typos or gibberish sentences.
1. (20 points) Consider the diagram of a Library database below. There are four relationships in the database. Describe each of the relationships in words, identifying the type of relationship shared between tables and the Foreign key, using the following format:
Customers and Orders have a One-to-Many relationship (FK: CustomerID). One Customer can make many Orders. One Order can have one Customer.
No other improvisation of the format will be accepted.
2. (10 points) What would be the SELECT query to join all five tables? You should be able to answer this question based on the relationships and the foreign keys.
3. (8 points) Consider the table BookInfo. What would be appropriate data types for the following columns: ISBN, BookName, BookPrice and CopiesNo. Explain your choices.
4. (6 points) Here are some information about the columns in the BorrowInfo table.
| Column Name | Data Type | Length | Identity | Allow NULLs | DefaultValue |
| BorrowID | int | 4 | Yes | No | No |
| ReaderNo | int | 4 | No | No | No |
| BookCopyID | Int | 4 | No | No | No |
| BorrowDate | smalldatetime | NA | No | No | GetDate() |
| ReturnDate | smalldatetime | NA | No | Yes | BorrowDate + 30 days |
| ActualReturnDate | smalldatetime | NA | No | Yes | No |
| Memo | varchar | 300 | No | Yes | No |
Here are some insert statements for this table. Explain why they are invalid or valid.
a. (Invalid) INSERT INTO BorrowInfo VALUES (1,234,42, ‘2018/09/14’, ‘2018/10/14’,NULL,NULL)
b. (Invalid) INSERT INTO BorrowInfo VALUES (345,‘eight’,‘2018/10/11’, ‘2018/11/10’,NULL,NULL)
c. (Valid despite skipping columns) INSERT INTO BorrowInfo ([ReaderNo], [BookCopyID], [BorrowDate]) VALUES (245, 100, ‘2018/11/29’)
(56 points) Answer all the following questions in a .sql file. Use MyGuitarShop Database.
The goal of the following questions is for you to practice writing queries based on all that you have learnt in the course. You will not be given hints on the topic- do you have to use a join or group by or add a where clause or use functions- you have to figure that out. Read the questions, examine the tables and figure out how to write the query based on your knowledge. Some of them are similar to what you have done in homework and in the class.
If not mentioned, use appropriate & meaningful names for all columns. Do not leave any column names blank. Points will be deducted otherwise.
1. (20 points) Consider the Addresses and Customers tables.
a. Write a query to display all the CustomerIDs and their addresses in the following format:
1: 100 East Ridgewood Ave., Paramus, NJ 07652
b. Write another query to display the Customer Names, IDs and addresses in the following format:
Allan Sherwood, CustomerID: 1, Address: 100 East RidgeWood Ave., Paramus, NJ 07652
It is ok to display multiple addresses for one customer.
c. Modify the previous query (#b) to show only the billing address. Remember you did something similar in one of the homeworks.
d. Write a query to display the CustomerIDs that have more than one address, that is, a customer that has one shipping address and a different billing address.
e. Write another query (which will be a modification of the previous query #d) to display the full names of these customers with more than one address.
2. (16 points) Consider the Products table
a. Write a query to display all column information about Products that start with the letters ‘G’ or ‘F’. Sort the result set by ProductName.
b. Write a query that displays two columns: CategoryID and the number of products in each category. Only show the CategoryID(s) that have more than two products.
c. Write a query that extracts and displays the first word in the ProductName, which represents the name of the company that manufactures the Product. For example, for the ProductName ‘Gibson Les Paul’, Gibson is the name of the company. Remove any duplicates from the result set.
d. Write a query to display two columns: the name of the manufacturing company and the number of products produced by it. Show only those companies that have more than one product in the table. You will have to use some code from the previous query (#c).
3. (8 points) Consider the Orders and the OrderItems tables
a. Write a query to show the different CardTypes used and the number of times they were used to make orders. Sort the result set so the CardType that was used most is displayed first.
b. Write a query to display the number of orders and the amount of the orders made each month. The resulting table should look similar to the following: Month #Orders #OrderTotal March 6 4881.00 April 25 24243.73 May 10 8167.69
4. (12 points) Consider all the tables. For these problems, you have to decide which tables to use.
a. Write a query to display the names of the customers who had ordered more than one type of product and the total amount of money they spent on those orders.
b. Write a query to answer this question: How many types of unique products were bought from each category? Display the CategoryName and #UniqueProductTypes that customers bought from that category.
c. Write a query to answer this question: Which customer(s) did not make any order? Extract and display the id of the EmailAddress of the customers.
The id of an email is the part of the string before the @. For example, in the email abcd@yahoo.com, abcd is the id.
Submission Upload both the word document and sql to Blackboard under Submit Assignments ->Final Project. Name your files as YourUsername_FP.sql and YourUsername_FP.docx (.doc/.rtf/.pdf) e.g. If I were to upload it, it would be mutsuday_FP.sql and mutsuday_FP.docx
"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"
