Test Reviews

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 tables
  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 numberetc.

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 sensitivityetc 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 in
  formation 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 tables 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
  keySelect 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 tables 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
• Dont 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 fields name
  exactly as you did in the table definition.
• Dont 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
  CriteriaNo.

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 todays
  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 employees 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. Thats 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
  employees 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.

ltgt ? 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.
Field          
Table          
Sort          
Show          
Criteria          
Or          
39
Answer to practice query 2
Field  Last Name First Name Date Hired State  
Table  Employee Employee Employee Employee  
Sort  Ascending  Ascending      
Show          
Criteria    gt12/31/1999  DE  
Or          
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.

Field          
Table          
Sort          
Show          
Criteria          
Or          
41
Answer to practice query 6
Field  Last Name  First Name Week    Hours  Salaried
Table  Employee  Employee  Hours Worked  Hours Worked  Wage Date
Sort          
Show          
Criteria      Enter Week    No
Or          
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
  employeeselect 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 a subform
• 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

51
Importing Data
52
Importing Data
Choose the correct worksheet
Make sure to select the first row contains column
headings box
53
Importing Data
Choose the default settings for each field you
are importing
54
Importing Data
Choose an appropriate name for your tableyou
can change your table design from Design button
55
Small Quiz

• (Database Concepts) The metadata for a database
  describes which of the following properties of a
  database?
• It defines the fields in the database tables
• It defines the structure of the database tables
• It defines the sizes and formats of the fields in
  the database tables
• It identifies the primary keys
• All of the answers provided are correct

56

• (Database Concepts) Because it is relatively
  common and everyone has one, the date of birth is
  often used as a primary key in a database table
  for identifying employees within a corporation.
• True
• False
• (Database Concepts) What is the term for a
  collection of related records, such as the scores
  for all of the students in a given section of
  CIS300?
• Field
• Character
• Item
• Table
• Query

57

• (Database Concepts) Which of the following is the
  smallest piece of data that can be stored within
  a database? (select at least one, but no more
  than two answers)
• bit
• byte
• character
• field
• record
• table

58

• (Database Concepts) Which of the following is a
  reason why Microsoft Access is a popular choice
  for a database management system?
• Widely available on Intel-based laptops and
  desktop PC platforms
• Relatively inexpensive compared to other RDBMS
  products such as Oracle
• Available in the premium editions of the
  Microsoft Office suite
• Relatively easy to construct a simple database
• All of the answers provided are correct

59

• (Database Concepts) Which of the following terms
  is often used to refer to the columns shown in a
  database table? (select at least one, but no more
  than two answers)
• row
• data element
• metadata
• field
• record
• data type

60

• (Database Concepts) A field within a database is
  defined as a single item of data that is common
  to all records and occurs as an individual _____
  within a table.
• cell
• column
• row
• value
• entry

61

• (Microsoft Access) The Report Wizard is an easy
  to use feature in Access that guides you through
  a series of questions and then generates a
  customized report based on the answers you
  provide.
• True
• False

62

• (Microsoft Access) Which of the following types
  of fields is used to produce a value from an
  expression or function?
• Calculated
• AutoNumber
• OLE Object
• Indexed
• Number

63

• (Microsoft Access) Which of the following objects
  provides a simple approach for less-experienced
  users to extract information from a database?
  (select at least one, but no more than two
  answers)
• Forms
• Macros
• Modules
• Queries
• Reports
• Tables

64

• While a database itself is a collection of
  several related files, the program used to build
  databases, populate them with data, and
  manipulate the data is called a(n)
• _DBMS Database Management System.
• There are two overall approaches to maintaining
  data the traditional file approachwhich has no
  mechanism for tagging, retrieving, and
  manipulating dataand the ____, which does have
  this mechanism.
• Database Approach
• Data Approach
• Datafile Approach
• Indexed file approach

65
(No Transcript)
66
(No Transcript)