Homework

1

ITM 300 - Mashaw, HW # 10 Name: This HW looks a little bit long, but, potentially, you can get up to 7 points from this HW, the points over 4 points are extra credit. Because of the Thanksgiving recess, this HW will be accepted after Thanksgiving on November 30th, 2020.

 Read Chapter 15 about Data and Competitive Advantage: Databases, Analytics See the following videos, make a short comment (a sentence is OK), so that I know you saw it (Search in YouTube)

 What is Database and DBMS? | Concept Simplified using Animation  Relational Database Concepts  Database Tutorial for Beginners  Entity Relationship Diagram (ERD) Tutorial - Part 1  SQL: Learning the basics of the SELECT statement

Part I . 1. Identify the record relationship between the following pairs of entities (If you write a few examples of data, it makes it easier to answer, check/show the relationship in both direction)

2. Assume ORACLE can do whatever a good DBMS is supposed to do. Compare Access, Excel and Oracle, and recommend one for a small company. (hint: Use your definition/functions of DBMS, write the criteria, and compare.)

The criteria (functions of a DBMS) Oracle Excel Access

Student

CourseTaken

Customer invoices

Courses

products

Student

membership

clubs

2

3. SQL: Consider the database for the customers of a club Members

Phone Name City Zip Area-Code YTD * Ch, 7 Ch, 30 Ch, 20 Ch, 10 Ch, 3 Currency

* YTD: Tot Year To Date Purchase Amount Products

Product Code Name Description color Ch,5 Ch 25 Ch 50 Ch 10

Purchases

Phone ProdID Quantity SalesmanCode Ch, 7 Ch, 5 Integer Ch, 2

Write a few examples of data for understanding, and then write the SQL Statements to retrieve data for the following queries. (You don't have to use Access, just write the statements here)

a. Show all the information about customers

b. Show all purchase Information

c. Show the Name and address of all customers who live in S.F.

d. Show all the information for the members who are in 510 area code

e. Show the Product ID's and the amount for customer whose phone is: "555 1234"

f. Show all the Products that Salesman Joe Brown sold; his code is: JB

g. Show the information for customers who have purchased more that $5000 (so far)

h. Show the phone numbers of the best customers (define it as having Year to Date of more than $15,000)

3

Part II DB design Part II DB Design A student has been selling material which is needed by other students for school work. She started to sell Scantron, and then extended to sell all kinds of products that a student may need. She even sells text books. Now she needs to store her buyers names, address, phone, and the product name, code, color, description as well as the order information such as quantity sold to, the date and price. 1. What are entities? 2. Identify the attributes for each entity, and the Primary key for each entity (write attributes under each entity.

3. Write a dictionary for each entity

4

Part III. Implementation, Access Hands on; GUI Queries, SQL, Forms, and Reports

A. Submit your current (updated) performance sheet, including HW 1, 2, 3, 4, 5, 6, 7, 8, and 9; Test 1 and test 2 points, and extra credits. Make sure it is correct and up to date.

B. Access: Use the Students- CourseTaken - Courses that you created for the previous HW. (If you have not created the Student Database, go ahead and create it now, the structure, dictionary, and sample of data is in the previous HW. ) 1. Create at least five GUI Queries that uses multiple tables, make up a condition for each, per instructions (Create ->Query Design , get the Table(s) that you want, and then drag fields that you want,; set the condition. Make sure you assign a descriptive name to your Query before saving, 2. Set the links (Tools -> relationship)

Note: Pay attention to the record relationship between the tables. The relationship should be 1 to many; if it does not show, check the dictionary, keys, data type and length)

Clip the menu that shows 1:many relationship (snipper), and the screen that shows the link 3. Create several SQL Queries, make up a condition for each (1. Open one of your saved Queries, get the SQL interface, erase whatever is on the interface, Write your own SQL. Let the first one to be SELECT * FROM students)

Do some queries using SQL interface. Do as it was instructed in the Class. 4. Create a few Forms of your choice single or multi table form Particularly one for Student – Course Taken, and one for Courses – Course Taken. 5. Create a few Reports, student Reports, or course Taken , or courses.

Database Questions For test 3 (keep it to study) For Part II of the Database, you need to know, (you don't need to turn it in), What is a Query?

An Object?

Join Operation ?

Primary key?

Composite key?

Foreign Key?

Data mining?

Schema?

SubSchema ?

Need to be able to Explain:

What are database models? Which one is the most used model, which is easy to comprehend.

For your own benefit (you don't have to turn it in) a. Do a few on-fly what if analysis b. Do a few case study/Scenario analysis (show the Scenario menu,) c. Set a goal for the final grade and do a couple of goal seeking cases.

5

In relational model, what terminology is used for

A File?

A Record?

A Field?

In relational model, what is the mechanism for linking tables?

What are two kinds of Query language that you learned in Access?

What is the de facto standard language in relational model?

What is a Datawarehouse , What are they used for?

What are the steps for designing a Database?

What are the types of database, which one is easier to use for average user, or Manager?

Send Questions for the Test 3. (make up at least 5 T/F questions, I will combine and create multiple choice questions. , for example 1. Which of the following is True? a. Excel is a DBMS b. Access is only a file manager c. A primary key must retrieve only one record d. all of the above are true 2. Which tool did you use to create a table with your dictionary ?

a. Database wizard b. Simple Table wizard c. Create Table d. Create Table Design

3. Which is a valid data type in Access?

a. Number b. Text c. Currency d. Entity

4 Which object is used to create a form?

a. Tables and Queries b. Tables only c. Tables and reports d. Queries and reports

5. Which tool did you use to create a query object?

a. Database wizard b. Simple Table wizard c. Create Query Wizard d. Create Query Design

6. Which tool did you use to create a Form object?

a. Database wizard b. Simple Table wizard c. Create Form Wizard d. Create Form Design