The detail in the pdf file.
BCS360 Final Project submission Instructions Prof. Amani Please Read carefully On SQL server account, you have to upload the tables to your account on SQL server. Your database has to have at least 5 tables. The number of rows is 20 rows. You can add more if you want. The tables have to be filled with data in order to run SQL queries. You can use the following website https://mockaroo.com/ Remember, this website fills out the data randomly. Therefore, to build the relationship between foreign key and primary key, you have to have same values in both columns in different tables. Your queries won’t work if there is not same values of foreign key and primary key even if you build referential integrity constrain. Therefore, in this case, you have to fill out this part manually. The referential integrity constraint is a rule specifying that every value of a foreign key matches a value of the primary key. On Blackboard, Deadline: 12/02/2019 You have to upload the followings: First a documented project: PDF file Name the a documented project as following FirstName_LastName_FinalProject For example: Amani_Ayad_FinalProject The document should have the followings: Page1: The cover page. Page 2: Introduction of your system, the scope, answering the followings: What does your system do? What is the important entities in your system? What is the activities and transactions can occur in your system Next Page: Full ER of your system. Remember, foreign keys are not shown in ER. Next Page: fill out the table. For example: https://mockaroo.com/ BCS360 Final Project submission Instructions Prof. Amani Next Page: List all tables in term of relations. Show the foreign key. Primary key is underlined. For example: TEXTBOOK (ISBN, Title, Publisher, Copyright) PUBLISHER (PublisherName, Street, City, State, Zip) Publisher in TEXTBOOK is a foreign key that references the primary key PublisherName in PUBLISHER. Next Page: show the script that has all create statements for your tables. Next Page: select two questions from each chapter and apply them on your system. The total is 10 queries. Feel free to run more queries. Try with complicated ones. The more complicated, the better evaluation and understanding of your system. You have to provide the question, query and the output. Chapters are 3,4,5,6,9,13 The questions are provided at the end. Next page: Summary: try to answer these questions: How much do you like your system? What is the difficult part? What did you learn? That’s the end of the documented project. Second: SQL queries: RELATIONSHIP CARDINALITY PARENT CHILD TYPE MAX MIN DRIVER CORRECTION_NOTICE Strong 1:N M-O CORRECTION_NOTICE VIOLATION Weak_ID- Dependent 1:N M-O BCS360 Final Project submission Instructions Prof. Amani Name the script that creates your database table as following Create_tables.sql Add this script to the following queries. After you select two SQL from each chapter, SQL file has to be named as the following pattern ch3_1, ch3_2, ch4_1,…… Add all your SQL queries in a folder and name it as SQL_QUERIES with create_tables.sql Then add SQL_QUERIES folder and a documented project in one zipped folder and upload it to the blackboard. Name the zip folder as following FirstName_LastName_FinalProject360 For example: Amani_Ayad_FinalProject360 BCS360 Final Project submission Instructions Prof. Amani Chapter 3 How to retrieve data from a single table Exercises Enter and run your own SELECT statements In these exercises, you’ll enter and run your own SELECT statements. 1. Write a SELECT statement that returns 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 list price 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 returns 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 last name in ascending sequence. Return only the contacts whose last name begins with a letter from M to Z. 3. Write a SELECT statement that returns these column names and data from the Products table: ProductName The ProductName column ListPrice The ListPrice column DateAdded The DateAdded column Return only the rows with a list price that’s greater than 500 and less than 2000. Sort the result set in descending sequence by the DateAdded column. 4. Write a SELECT statement that returns these column names and 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 Sort the result set by discount price in descending sequence. BCS360 Final Project submission Instructions Prof. Amani 5. Write a SELECT statement that returns these column names and data from the OrderItems table: ItemID The ItemID column ItemPrice The ItemPrice column DiscountAmount The DiscountAmount column Quantity The Quantity column PriceTotal A column that’s calculated by multiplying the item price by the quantity DiscountTotal A column that’s calculated by multiplying the discount amount by the quantity ItemTotal A column that’s calculated by subtracting the discount amount from the item price and then multiplying by the quantity Only return rows where the ItemTotal is greater than 500. Sort the result set by item total in descending sequence. Work with nulls and test expressions 6. Write a SELECT statement that returns these columns from the Orders table: OrderID The OrderID column OrderDate The OrderDate column ShipDate The ShipDate column Return only the rows where the ShipDate column contains a null value. 7. Write a SELECT statement without a FROM clause that creates a row with these columns: Price 100 (dollars) TaxRate .07 (7 percent) TaxAmount The price multiplied by the tax rate Total The price plus tax To calculate the fourth column, add the expressions you used for the first and third columns. BCS360 Final Project submission Instructions Prof. Amani Chapter 4 How to retrieve data from two or more tables Exercises 1. Write a SELECT statement that joins the Categories table to the Products table and returns these columns: CategoryName, ProductName, ListPrice. Sort the result set by CategoryName and then by ProductName in ascending order. 2. Write a SELECT statement that joins the Customers table to the Addresses table and returns these columns: FirstName, LastName, Line1, City, State, ZipCode. Return one row for each address for the customer with an email address of
[email protected]. 3. Write a SELECT statement that joins the Customers table to the Addresses table and returns these columns: FirstName, LastName, Line1, City, State, ZipCode. Return one row for each customer, but only return addresses that are the shipping address for a customer. 4. Write a SELECT statement that joins the Customers, Orders, OrderItems, and Products tables. This statement should return these columns: LastName, FirstName, OrderDate, ProductName, ItemPrice, DiscountAmount, and Quantity. Use aliases for the tables. Sort the final result set by LastName, OrderDate, and ProductName. 5. Write a SELECT statement that returns the ProductName and ListPrice columns from the Products table. Return one row for each product that has the same list price as another product. (Hint: Use a self-join to check that the ProductID columns aren’t equal but the ListPrice column is equal.) Sort the result set by ProductName. 6. Write a SELECT statement that returns these two columns: CategoryName The CategoryName column from the Categories table ProductID The ProductID column from the Products table Return one row for each category that has never been used. (Hint: Use an outer join and only return rows where the ProductID column contains a null value.) BCS360 Final Project submission Instructions Prof. Amani 7. Use the UNION operator to generate a result set consisting of three columns from the Orders table: ShipStatus A calculated column that contains a value of SHIPPED or NOT SHIPPED OrderID The OrderID column OrderDate The OrderDate column If the order has a value in the ShipDate column, the ShipStatus column should contain a value of SHIPPED. Otherwise, it should contain a value of NOT SHIPPED. Sort the final result set by OrderDate. BCS360 Final Project submission Instructions Prof. Amani Chapter 5 How to code summary queries Exercises 1. Write a SELECT statement that returns these columns: The count of the number of orders in the Orders table The sum of the TaxAmount columns in the Orders table 2. Write a SELECT statement that returns one row for each category that has products with these columns: The CategoryName column from the Categories table The count of the products in the Products table The list price of the most expensive product in the Products table Sort the result set so the category with the most products appears first. 3. Write a SELECT statement that returns one row for each customer that has orders with these columns: The EmailAddress column from the Customers table The sum of the item price in the OrderItems table multiplied by the quantiy in the OrderItems table The sum of the discount amount column in the OrderItems table multiplied by the quantiy in the OrderItems table Sort the result set in descending sequence by the item price total for each customer. 4. Write a SELECT statement that returns one row for each customer that has orders with these columns: The EmailAddress column from the