Description
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.
- 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.
- (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.
- (8 points) Consider the table BookInfo. What would be appropriate data types for the following columns: ISBN, BookName, BookPrice and CopiesNo. Explain your choices.
- (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.
- (Invalid)
INSERT INTO BorrowInfo VALUES
(1,234,42, ‘2018/09/14’, ‘2018/10/14’,NULL,NULL) - (Invalid)
INSERT INTO BorrowInfo VALUES
(345,‘eight’,‘2018/10/11’, ‘2018/11/10’,NULL,NULL) - (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.
- (20 points) Consider the Addresses and Customers tables.
- Write a query to display all the CustomerIDs and their addresses in the following format:
1: 100 East Ridgewood Ave., Paramus, NJ 07652
- 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.
- Modify the previous query (#b) to show only the billing address. Remember you did something similar in one of the homeworks.
- 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.
- 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.
- (16 points) Consider the Products table
- Write a query to display all column information about Products that start with the letters ‘G’ or ‘F’. Sort the result set by ProductName.
- 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.
- 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.
- 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).