Basic get acquainted assignment

Login to blackboard watch orientation Web-cast video, it is listed at the top of Blackboard/Videos.

Then watch getting started:14 Minute Video & corresponding Viewgraphs

Take blackboard Quiz 0 - Note you can complete entire course just using blackboard (blackboard has all key links).

Then review class Web-site, read syllabus and scan over this assignment list. Note this is your course guide for the entire course, you don't complete all assignments at once, you work top down on this assignment list.

Also watch the , and the Course/Info Certificate Videos, make sure you complete the questionnaire.

Consult this file for login information.

If new to Blackboard, or having problems completing the above steps explore Help on Using Course Management System Blackboard.

Checklist (check off each item after you complete it)...

• Print this assignment page by clicking the print button (this will be the guide for completing the course )
  Nota bene you can set you printer to Adobe pdf (for a pdf hard copy of this page)
  
• Print the Welcome announcement page (its 3 steps are a concise summary of course requirments)]
• The three step announcment and assignment page should be your first few pages in your cim205a notebook.
• Watch getting started videos and orientation web-cast
• complete Blackboard quiz 0 - basic course intro quiz
• review syllabus
• complete questionnaire

submit tutorial.sql, (10 possible points) login in to blackboard; you will need to watch all tutorial.sql assignment blackboard videos. The tutorial.sql blackboard videos provide a step by step tutorial on completing tutorial.sql. For tutorial.sql you should complete it using phpMyAdmin but you can also complete it a second time using either the putty or mysql work bench path.

Scripts to Create Fruit Tables and other cim205a tables.

For the putty path, you no longer get an automatic e-mail reply.
Useful software for alternate paths: textpad, notepad++, putty , filezilla and mysql.

Consistency Notes...

• all submit filenames are lower case and always end in .sql
• phpMyAdmin is mapping all functions names to upper case but we use all lowercase function names for submit
• all function arguments have a space before and after (we use sum( price ) not sum(price) )
• for fruit database all tables are lower case
• for fruit tables all field names are lower case except for ID, (i.e., fruitID)
• you should use accent marks to delimit table and field names
• never include database names in your queries, we will also be selecting a database.
  

Checklist...

• watch tutorial.sql blackboard videos
• submit tutorial.sql,
• complete blackboard quiz intro MySQL (Check both quiz grades using Blackboard --> tools --> My Grades)
  

submit fruitcmds.sql, (15 Possible Points) login in to blackboard; you will need to watch all fruitcmds.sql assignment blackboard videos.

The videos will assume you can login to both phpMyAdmin and submit. The file fruitcmds.sql and back up fruitcmds.sql.bak is in your cim home directory. Note files is automatically loaded when you login and select assignment using submit. Fruitcmds.sql is also online at cim/~premiere. This file has 3 MySQL commands and 12 comments. Blackboard has a set of videos and webcasts to help you with this assignment. For readability, always add comments and blank lines.

checklist...

• watch blackboard fruitcmds.sql video
• submit fruitcmds.sql - should be 15/15 if not wait until you finish select.sql (and fix it)
• Note I do check comments, so make sure you include them!!

phpMyAdmin Advance, complete the online blackboard video sets: phpMyAdmin and PHPMyAdmin Advance. Submit pma10.sql (10Points) for the first set, and submit pmaAdv.sql (25 points) for the second video set and also email me your phpMyAdmin report (10pts). CIM225 students complete only the first phpMyAdmin video set. You will be using phpMyAdmin application to create a fruit DB. Fruit DB is a simplified premiere like database used in the textbook. You must complete all steps detailed below and outlined in the viewgraph/videos. E-Mail me your finished report in Open Office, libre office or MSWord format (doc or docx) Google docs or PDF. Your final report will have....

1. Output Showing Content of the Five Tables – PHPMyAdmin Browse Export or alt^print screen or Print View
2. PHPMyAdmin Data Dictionary of updated tables.
In phpMyAdmin select your database, then click the Data Dictionary bottom link.
3. Final Graphic Schema of Views and Tables, illustrating Field Names, Types & Relationships.
In phpMyAdmin, select your database, select designer tab or more → designer. Use ALT^Print Screen or the PDF Print Button.
4. Essay commenting on the code and questions related to the three source files (fruitAlter.sql.html, fruitTrigger.sql.html and fruitRoutine.sql.html) that are part of pmaAdv.sql.
5. Include your submitted pmaAdv.sql file, make sure you comment the code. For example, if a query has the function format, clearly state why function format is being used. You need to both submit pmaAdv.sql and also included the commented version with your report. Your submitted version should have the same comments.

Checklist

1. Create the first two tables fruit Tables using PHPMyAdmin.
   Step One is identical to the CIM225 PHP/MySQL PHPMyAdmin assignment.
2. Submit pma10.sql
   
3. Create Three additional tables and add comments & relational view information to your tables. Your PHPMyAdmin has to include images of the content of these five tables.
4. Report must also include data dictionary of altered/updated tables.
5. Submit first 10 Queries of pmaAdv.sql
6. Create views and then create a graphic schema. Using phpMyAdmin's designer tab include a relational view graphic Schem.
7. Watch the more advance videos on constraints, triggers, routines and events.
8. Submit pmaAdv.sql (final 15 queries)
9. Answer essay questions add comments to all three source files (fruitAlter.sql, fruitTrigger.sql and fruitRouting.sql) and also make sure you add comments and include to pmaAdv.sql.
10. E-Mail me your final phpMyAdmin report.

submit select.sql, (10 Points) watch blackboard videos select.sql which teaches you how to use the submit command. The submit command includes an automatic grader for query based assignments. Video also complete queries 1-3 and 5. For this assignment you need to create 11 queries for these 11 comments...

1. #S1 display contents of table:fruit
2. #S2 display first three rows in table:inventory
3. #S3 display fruit name, inventory quantity sum, price and sum quantity*price displayed as value for each row in fruit
4. #S4 display number of types of fruit in table:fruit as count( * )
5. #S5 display table:fruit sorted by price in descending order
6. #S6 display name and cost  as pennies for fruit with ID = 1, try this out using the mulitplier as both a literal and a number not enclosed in single quotes.
7. #S7 display total number of pieces of fruit in inventory
8. #S8 display fruit table ordered by fruitID in ascending order
9. #S9 display fruitID for each row in table:inventory
10. #S10 display fruit name, and price from table:fruit
11. #S11 describe field names, and/or information about columns in table:inventory

If you want to work with a more complex database before starting the book, you can attempt this extra credit assignment.

checklist...

• watch blackboard videos select.sql
• submit select.sql
• verify your grades for both tutorial.sql and select.sql (it is listed after submit)
• if tutorial.sql is not 10/10 or fruitcmds.sql is not 15/15 fix them now

Read Chapter 1; Complete the premiere product exercises on page 21 questions 1..10, The ten questions in the book are listed after the next paragraph.

The database we are using is online. Once you have completed the 10 questions on page 21 (aka you have hand written answers), take blackboard Assignment Quiz One; which will verify that you have completed the written portion of the assignment. Assignment one Quiz can not be repeated it will count as part of your quiz grades. Using the results of Assignment quiz one and/or wait til you have completed chapter 3, submit p21.sql. (10 Points) Updated versions of questions adopted from book...

1. List names of all customers that have credit limit of 7500 or less; remember table and field names upper case.
2. List order numbers for orders placed by customer number 608 on 10/23/2007
3. List part number, description and value (price of part * quantity on hand) as "On-hand Value" of all parts in item class SG.
4. List part number and description of all parts in class HW
5. How many customers have a balance that exceeds their credit limit?
6. What is part number, description and price for least expensive part in DB
7. For each order, list order number, order date, customer number and customer name.
8. For each order placed on October 21, 2007 list order number, customer number and customer name.
9. List sales rep number and sales rep last name for every sales rep who represents at least one customer with credit limit of exactly $10,000.00. Don't list duplicate sales reps.
10. For each order placed on October 21, 2007 list the order number, part number, part description, and item class for each part ordered

Consistency notes...

• Nota Bene Premiere Database uses all Upper-Case letters for both table and field names.'
• Premiere Database is used in both the book and the corresponding book assignments.
• Fruit database tables are all lower-case letters. Fruit used medial caps, for table names consisting of two words, 2nd words start with capital letters.
• Premiere is using underscores to separate mutli-word table or field names.
• Function names and arguments are lower case.
• all filenames for book start with p then page number then .sql

checklist...

• Read chapter 1
• Hand write the answers to the 10 updated questions extracted from page 21.
• Take chapter 1 blackboard quiz
• Take assignment one blackboard quiz
• Assignment one blackboard quiz has all SQL quereis for submitting p21.sql, if you can submit p21.sql or wait until you cover chapter 3.

submit p58.sql (7 Points) Read chapter 2, drop all your fruit tables, then source or download (aka right^click) /home/premiere/public_html/MySQL-Premiere, once you have a local copy of the file, use PHPMyAdmin to import the create script. It is also advantageous to use the PHPMyAdmin designer tab to create a graphic schema. After you have created the premiere database, submit p58.sql consisting of these seven Queries...

1) Display names of all the premiere database tables;
2) Display the number of customers you have.
3) List the contents of the CUSTOMER table.
4) Describe the REP table.
5) List the contents of the ORDERS table.
6) List contents of REP table, sorted by highest commission first.
7) Display customer name with highest credit limit, hint select customer_name, order by, limit 1;

You should probably use PHPMyAdmin to help you create the queries, especially the sorted ones.

checklist

• read chapter 2
• take blackboard chapter 2 quiz
• submit p58.sql

Read chapter 3, submit p87.sql (10 Points)

For p87.sql write queries for this five comments and write comments for the second 5 queries...

1. #Display the first five rows in ORDER_LINE table.
2. # Display all your customer names along with their city and state.
3. #How many salesman do you have?
4. # Display the contents of PART Table
5. # Describe the PART table
6. select * from PART order by PRICE;
7. select sum( NUM_ORDERED*QUOTED_PRICE) from ORDER_LINE;
8. select max( Price ) from PART;
9. select r.LAST_NAME, Customer_name, balance, credit_limit
   from CUSTOMER as c, REP as r where c.REP_NUM = r.REP_NUM;
10. select Order_num, order_date from ORDERS
    where CUSTOMER_NUM in (select CUSTOMER_NUM from CUSTOMER
    where REP_NUM= 65);

You can keep submitting until you have 10/10, if you don't fix one query at a time

checklist

• read chapter 3
• take blackboard chapter 3 quiz
• submit p87.sql
• submit p21.sql
  

Read Chapter 4; submit p125.sql. (21 Points) Write MySQL commands to solve questions 1..21 on page 125. Proceed each command with a comment specifying the question number.

on Question 7 make sure you include both end points 10 and 25 and do this only one way.

Hint on question 20, you need to do a group by rep number right after the from table statement and then use a having clause with sum( balance ). Note the having clause enables the group by clause to be used as a where conditional, in this case sum(balance) refers to the sum of the balance relative to the group by REP_NUM. To sort the final output, just end the query with order by REP_NUM.

checklist

• read chapter 4
• take blackboard chapter 4 quiz
• submit p125.sql

Read Chapter 5; Chapter 5 on multiple tables is one of the hardest chapters; submit p157.sql, (20 Points) this consists of all Premiere Database queries, questions 1..20 pages 157-158, the following four questions have further clarification..

• Question 6 list customer number and customer name for all customers with active orders that were not placed on 2007-10-21.
• Question 8 the sort order becomes order by class, ORDERS.order_num and then ORDER_LINE.part_num;
• Question 9, find rep number, last name, first name, who represent a customr with credit limit of exactly 5000. This is not at least $5000 and also not $5000 credit limit after current balance.
• Question 12 # hint you will need to combine the part table with itself, look for the matching classes and display parts number in ascending order; Sort the output by the first part_num table, then by the second part_num table, You are displaying PART_NUM, DESCRIPTION from first table, then PART_NUM, DESCRIPTION, and CLASS from second table. If still stuck, reread the section "Joining a Table to Itself" pages 140-141 and pay special attention to the Q&A sidebar on page 141.
• Question 15 order by order_num ASC; Hint put select after union in parentheses, then add order by clause
• Question 16 MySQL doesn't have the intersect operator just use an and in the where clause. You can also consider using or having.
• Questions 20 order by part number, then by order number both in ascending order.
• Preface each MySQL command with the question number comment line.

Hints Do not even attempt to try to join more than three tables, use sub queries first to determine customer_num from customer_name, and part_num from description.

checklist

• read chapter 5
• take blackboard chapter 5 quiz
• submit p157.sql
• Verify your quiz grades on blackboard (blackboard --> tools --> my Grades)

Chapter 6 is relatively easy; submit p177.sql; write a SQL query for questions 1-10; Question 8 is two queries, 8 and 8b; Also added four display content of Table:NONAPPLIANCE queries, 2a, 5a, 7a and 9a. Note #2a, #5a, #7a, #9a and #8b are the five additional queries added to this assignment, The final Query list is...

1. # 1 create table 'NONAPPLIANCE' don't prefix table name 'NONAPPLIANCE' with your database - table fields are PART_NUM, DESCRIPTION, ON_HAND, CLASS and PRICE.
2. # 2 insert into NONAPPLIANCE all items that are not class 'AP'; For this one you need to use insert select command., Nota Bene I cannot check 1 and 2, they will pass automatically, but #2a will fail, if 1 or 2 are wrong make sure they are working.
3. #2a Display contents of NONAPPLIANCE sort by ascending Price
4. #3 change description of PART_NUM AT94 to Deluxe Iron
5. #4 increase price of all items CLASS SG by 2%
6. #5 add Part number TL92 description Trimmer, units on hand 11, class HW, price 29.95
7. # 5a display contents of NONAPPLIANCE Table
8. # 6 delete every part in NONAPPLIANCE of class SG
9. # 7 in NONAPPLIANCE change PART_NUM FD21 to CLASS NULL
10. #7a display contents of NONAPPLIANCE Table
11. # 8 Add new last Column ON_HAND_VALUE seven digit number with 2 decimal points to NONAPPLIANCE table
12. # 8b set all values of column ON_HAND_VALUE to ON_HAND*PRICE in table:NONAPPLIANCE
13. # 9 in NONAPPLIANCE table increase column DESCRIPTION to 30 characters
14. # 9a display contents of NONAPPLIANCE Table
15. # 10 Remove NONAPPLIANCE Table

checklist

• read chapter 6
• take blackboard chapter 6 quiz
• submit p177.sql
• Verify all grades for submit, 2nd row is total possible points for each assignment (note fruitcmds.sql, p88 & phpmyadmin grades are maintain in my personal spreadsheet)
  

  tutorial	fruitcmds	pma10	pmaAdv	select	p21	p58	p87	p125	p157	p177
  10	15	10	25	11	10	7	10	21	20	15

  
  
• Consider completing the extra credit assignments

Chapter 7 Database Administration and Chapter 8 MySQL Special Topis Are Extra Credit;

For your person gain, can look into MySQL Admin Tools.