Test Reviews “Exam 1: Profs. Kendra, Goyal & McIntosh”

1. Test Reviews “Exam 1: Profs. Kendra, Goyal & McIntosh” Computer Resource Center REACH 2014

2. Database Design Basic Terminology: • A table consists of data that is arrayed in rows and columns. • A row of data is called a record. • A column of data is called a field. Thus, a record is a set of related fields. The fields in a table should be related to one another in some way.

3. Basic Terminology • Primary key field is a field in which each record has a unique value. e.g. The SSN • Compound Primary Key is used when there is no single field whose values are all different. In that case, two or more fields are combined. The combination of the fields’ values should be unique. • Foreign key is the primary key of another table. e.g. suppose we need to link the employee table with the Hours worked table for payroll purposes. The Employee ID number (EIN) is the primary key of the employees table but the foreign key of the hours worked table. *Every foreign key must be associated with a primary key in another table.

4. Basic Terminology • A form is a database object that is created from an existing table to make the process of entering data more user-friendly • A query is the database equivalent of a question that is posed about data in a table (or tables). • Queries can be designed to search multiple tables but these tables should be connected by a join operation.

5. Database Design Database design concepts: Entities an entity is a tangible thing or an event. It is a person, place, thing or concept about which data can be collected. Consider the following examples: • The database of a video store would have one entity named video and another named customer (These are physical entities). • Organizations incur expenses from paying hourly employees and purchasing materials from suppliers. Hours worked and purchases are event entities in the database of most organizations. • The library lends books for free. If you were to think of checking out a book as a sales transaction for zero revenue, how would you handle the revenue generating event? The event entity here is the number of checkouts.

6. Database Design • Relationships you should consider the relationship of each entity to the other entities you have identified. • Cardinality of any relationship can be one-to-one, one-to-many, or many-to-many. • A one-to-one relationship is like A book is published by one company or A shark lives in one place (the ocean). • There is a many-to-many relationship between the records in the doctor table and records in the patient table because doctors have many patients, and a patient could have several doctors; • A one-to-many relation between the department table and the doctor table because each doctor may work for only one department, but one department could have many doctors.

7. Database Design • A book can have more than one author. An author can write more than one book. How would you describe the relationship between authors and books? Many-to-many • A member can borrow any number of books at one checkout. A book can be checked out more than once. How would you describe the relationship between books and checkouts? Many-to-many

8. Database Design • Attributes an attribute is a characteristic of an entity. These attributes become the table’s field. • E.g. what are the attributes for the entity “Customer”? Customer ID, First name, Surname, Date of Birth, Address and Phone no. • What are the attributes for the entity “Fashion Model”? Name, Height, Weight, Dress size, Hair color and Eye color.

9. Database Design Rules • Rule 1: You do not need a table for the business The database represents the entire business. Thus in the practice example* The library is not an entity. *practice problem at the end of tutorial A. • Rule 2: Identify the entities in the business description In our example the entities are Members, employees and books.

10. Database design rules • Rule 3: Look for relationships among the entities look for relationships between entities; one-to-many and many-to-many. In our example: one-to-many: a member can check out more than one book. • Rule 4: Look for attributes of each entity and designate a primary key. Attributes of members: name, DOB, phone no., email address, member ID card number …etc. Employees: name, # of hours worked, job title,…etc. Books: name, authors, type, status, member ID card number…etc. Primary Key Foreign key

11. Database Design Rules • Rule 5: Avoid data redundancy you should not include extra (redundant) fields in a table. Redundant fields take up extra disk space and lead to data entry errors because the same value must be entered in multiple tables. Rule 6: Do not include a field if it can be calculated from other fields A calculated field is made using the query generator as we will see later. ** You should realize the importance of accuracy, case sensitivity…etc in designing databases.

12. Metadata & Hierarchy of Data • Metadata: Data about data. Metadata describes how and when and by whom a particular set of data was collected, and how the data is formatted. A text document's metadata may contain information about how long the document is, who the author is, when the document was written, and a short summary of the document. • Hierarchy of Data:refers to the systematic organization of data, often in a hierarchical form. Data organization involves fields, records, files and so on.

13. Data Dictionary • Data Dictionary: In database management systems, a file that defines the basic organization of a database. A data dictionary contains a list of all files in the database, the number of records in each file, and the names and types of each field. Most database management systems keep the data dictionary hidden from users to prevent them from accidentally destroying its contents. • Data dictionaries do not contain any actual data from the database, only bookkeeping information for managing it. Without a data dictionary, however, a database management system cannot access data from the database.

14. Creating tables • Create tab  Table design (in the tables group) • Fill in the table’s fields.Choose a suitable data type for each field. • For example text  Last Name Date/time  Date Hired Yes/No  US Citizen • Change the lengths of the text fields from 255 to 30 spaces.

15. Creating tables • We need to make the Employee ID a primary key:Select the Employee ID field then in the Table tools Design click Primary Key tab • After you finish click the File tab  Save object as  then name your table • Note that this is different from Save Database as which saves the whole database.

16. Creating Compound Primary Key • The two fields must be appear one after the other in the table definition screen (plan ahead for that format). • Highlight one field, hold down the control key and highlight the next field. • Go to table tools  design tab Primary Key

17. Adding records to a table • Double click the table’s name in the navigation pane at the left of the screen then start typing data directly into the cells. • Enter your data one field value at a time. • Each time you finish entering a value, press Enter to move the cursor to the next cell. • After you enter the data in the last cell in a row, the cursor moves to the first cell in the next row and Access automatically saves the record • No need to save through the File tab.

18. Creating Queries • Using Calculated Fields in Queries: • E.g. suppose we have the following table. • if you have an existing field containing the number of boxes of Girl Scout cookies sold, you may want to see how much money was collected for each cookie sale.  In this example, the boxes sold for $3.95 each.

19. Using Calculated Fields in Queries • In this case we will create a calculated field in a query. • Create tab Query design Don’t Forget the Colon. Also the field name must be enclosed in square brackets.

20. Using Calculated Fields in Queries • How to format the calculated field output? • 1. select the output column by clicking the line above the calculated field expression. • 2. the column darkens to indicate the selection. • 3. Design tab  property sheet  format

21. Avoiding errors when making calculated fields • Do not enter the expression in the criteria cell. Enter it in the Field cell. • Spell, capitalize, and space a field’s name exactly as you did in the table definition. • Don’t use parentheses or curly braces instead of the square brackets.

22. “Relating” two or more tables by the join operation • Suppose you want to see the last names, employee IDs, wage rates, salary status, and citizenship only for US citizens and hourly workers. • Problem: the data is spread across two tables. • Solution: add both tables and pull down the five fields you need. • Step 1: Create tab  Query design • Step 2: Click one table name and hold down the ctrl button while choosing the other table name. • Step 3: start pulling down the fields you need and add the criteria expressions.

23. “Relating” two or more tables by the join operation

24. “Relating” two or more tables by the join operation • you can use calculated fields using more than one table. • Just follow the same steps and add the calculated field in the design view like what we did earlier.

25. Totals Queries • Assume that you want to see two pieces of information for hourly workers: • The average wage rate • 110 percent of the average rate Step1: Create the first query, click the design tab  Totals button in the show/hide group. This will give us the average of the wage rate field. Note that: you should type the revisedheading for the wage rate field, i.e. Average rate: wage rate. N.B. we need the average of this field. Also to get the hourly workers only, enter Criteria=No.

26. Totals Queries • Now begin a new Query.But instead of basing it on a table, we will base it on the previous query. • Design the new query and create a calculated field inside it.

27. Using the Date() function in Queries • Access has two important date function features: • The built in Date() function which gives today’s date. • Date arithmetic lets you subtract one date from another to obtain the difference-in number of days- between two calendar dates.

28. Using the Date() function in Queries • Suppose you want to give each employee a $1 bonus for each day the employee has worked. • So, you need to calculate the number of days between the employee’s date of hire and the date the query is run. Date arithmetic

29. Using time arithmetic in Queries • Access allows you to subtract the values of time fields to get an elapsed time. • In Access, subtracting one time from the other yields the decimal portion of a 24-hour day.E.g. if the employee worked 8 hours, the time arithmetic function yields 0.333. That’s why we multiply by 24.

30. Update Queries • Suppose you want to give all non-salaried workers a $0.5 pay raise.If you have 3 workers  change the wage rate data in the table.If you have 3000 workers  it would be much faster and more accurate to change the wage rate by using an update query that adds $0.5 to each non-salaried employee’s wage rate.

31. Update Queries • First start by making a select query. • Then click the update button in the query type group.

32. Update Queries We will write the updated data in the Update to line in the QBE grid. We will update only the non-salaried workers by using a filter under the salaried field.

33. Update Queries • When you run the query, the following warning message will appear. • Click yes, and the records will be updated.

34. Delete Queries • Delete queries work like the update queries. • E.g. Suppose your company is purchased by the state of Delaware. So you need to delete or “fire” all employees who are not exclusively Delaware residents. <>  not

35. Scenario • You have a table of medicines. One of them is now banned. Create a query with the new requirements. • So what is the type of this query? Delete Query

36. Parameter Queries • Suppose you want to know how many hours a particular employee has worked. • Run a select query. • You will get a message to enter the employee ID.Enter your employee IDand you will get thedesired information

37. Practice Queries (p.33  37) • #2 Create a query that shows the last name, first name, date hired, and state for employees who live in Delaware or were hired after 12/31/99. The primary sort (ascending) is on last name, and the secondary sort (ascending) is on first name. The primary sort field must be to the left of the Secondary sort field in the query setup.N.B. you have 3 tables.Employee  Last Name, First Name, Employee ID, Street Address, City, State, Zip, Date Hired, Us Citizen.Wage Data  Employee ID, Wage Rate, Salaried.Hours Worked  Employee ID, Week #, Hours.

38. Use this table to solve the practice query.

39. Answer to practice query #2

40. Practice Queries • #6 Create a parameter query that shows the hours employees have worked. Have the parameter query prompt for the week number. The output headings should be Last Name, First Name, Week #, and hours. This query is for non-salaried employees only.N.B. you have 3 tables.Employee  Last Name, First Name, Employee ID, Street Address, City, State, Zip, Date Hired, Us Citizen.Wage Data  Employee ID, Wage Rate, Salaried.Hours Worked  Employee ID, Week #, Hours.

41. Answer to practice query #6

42. Creating Reports • Create basic ungrouped report: • Select the table  create tab  report

43. Creating Reports • Create Grouped Report: • Design tab  Group and Sort button in the grouping and tools group. • Click the Add group buttonthen select the desired table (Employee ID)

44. Creating Reports • To total the hours worked by each employee:select the Hours column heading.Then on the Design tab  totals button in the grouping and totals group  Sum Layout View

45. Creating Reports • Design tab  Report view from the views group Report view (final view)

46. Making Forms • First select the table or query you want to base the form on then select Create tab  form • When you create a form within another form  this is called asubform • The Subform is useful when the form is based on one or more tables.

47. Making Forms • Step 1: select the table  create  Form • Take the form into design view.Design tab  make sure that the use control wizard option is selected  click the subform/subreport button.

48. Making Forms Select use existing tables and Queries  Next Use your cursor to stretch out the box under your main menu. This dialog box will appear

49. Making Forms • Choose the required table from the list  Next  select choose from a list  Next  Finish.

50. Importing Data • Open the Excel application and create a spreadsheet. • In the Access application: External data tab  Excel in the import and link group