Transcript Test Reviews *Exam 1

Test Review
“Exam 1: Prof.
McIntosh”
Computer Resource Center
REACH 2016
1
On The Test
• Very little “application” questions
• Topical Questions from Tutorial A
and B
• Query
• Case #1
2
On This Test Review
• Definitions of Key terms
• Quick Content Review
• An example of what a query
will look like on the test
• 15 Additional Practice
Questions
3
PowerPoint
• A copy of the PowerPoint can be
found at:
• http://reach.louisville.edu/tutoring/co
mputer/cistestreviews.html
4
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.
5
Basic Terminology
• Primary key field is a field in which each record has
a unique value. e.g. The SSN
• 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.
6
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:
1) The database of a video store would have one entity named
video and another named customer (These are physical
entities).
2) 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.
3) 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.
7
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.
8
Relationships
• In a one-to-one relationship, one instance of the first
entity is related to just one instance of the second
entity
• In a one-to-many relationship, one instance of the
first entity is related to many instances of the
second entity, but each instance of the second
entity is related to only one instance of the first
entity
• In a many-to-many relationship, one instance of the
second entity is related to many instances of the
second entity, and one instance of the second
entity is related to many instances of the first
9
Relationships (Examples)
• You have a specific driver license # that matches
only to you
*One to One
• 1 Employer has many Employees; 1 Guitar has many
Guitar Strings;1 Car has many Seats
*One to Many
• A book has more than one author, and each
author writes many books.
*Many to Many
10
Editing Relationships
Referential Integrity- a set of rules Access enforces to
maintain consistency between related tables when
you update data in a database.
-By using cascading referential integrity constraints,
you can define the actions that the Server takes when
a user tries to delete or update a key to which existing
foreign keys point.
-In order words, what happens when you delete or
update a cell that has corresponding relationships
with other cells?
-Should they also delete? Should they be updated
every time primary key changes?
11
12
Cascade Delete
• With Referential Integrity on:
• If you turn ON Cascade Delete
• Specifies that if an attempt is made to delete a row with
a key referenced by foreign keys in existing rows in other
tables, all rows that contain those foreign keys are also
deleted
• If you leave Cascade Delete OFF
• When deleting primary key, receive an error message
explaining cell cannot be deleted due to related cells.
• https://www.youtube.com/watch?v=LuC06E6CueI
13
Cascade Update
• If you turn ON Cascade Update
• Specifies that if an attempt is made to update a key
value in a row, where the key value is referenced by
foreign keys in existing rows in other tables, all the values
that make up the foreign key are also updated to the
new value specified for the key.
• If you leave Cascade Update OFF
• When updating primary key that has already established
relationships with foreign keys, receives error message.
• https://www.youtube.com/watch?v=B6mLVHXM7g4
14
Parent-Child, “Orphans”
• Parent-Child relationships exist when a foreign key
(child) belongs to a primary key (Parent)
• Referential Integrity exists to prevent “children” from
losing their connection to “parents”
• When a primary key (“parent”) is removed without
referential integrity, the child is now an “orphan”
15
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.
16
Types of Queries
Select Query
• Queries that answer a question
• “Selects” relevant data from database records
• Helps you get just the data you need in a
Datasheet view
Append Query
• Adds a record to the end of the table
• Works by selecting records from one or more data
sources and copying them to an existing table
17
Types of Queries
Update Query
• Designed to change data in records
• Often updates information in real time (think online
purchases and updating the on-hand amount of
that item)
• Begins as a “Select” query, then begins by clicking
“update”
Delete Query
• Deletes entire records from a table
• Works similar to update query (must start with select
query)
18
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.
19
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
Primary Key
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.
Foreign key
20
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.
21
Use Update or Delete?
Type of query
When to use it
Results
Use a delete query
To remove entire records
(rows) from a table or
from two related tables
simultaneously.
Delete queries remove all
the data in each field,
including the key value
that makes a record
unique
Use an update query
To delete individual field Makes it easier to delete
values from a table.
values by updating the
existing values to either a
null value (that is, no
data) or a zero-length
string (a pair of double
quotation marks with no
space between them). 22
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.
23
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.
24
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:
25
Quiz Questions
• (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
26
• (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
27
Scenario
• You have a table of medicines. One of them is now
banned. Create a query with the new
requirements.
• Fill in the Blank. This is a/an ____________ Query
Delete
28
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:
29
• (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
30
•
•
•
•
•
•
•
(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
31
•
•
•
•
•
•
(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
32
• (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
33
• (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
34
• (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
35
• 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 approach—which has no mechanism
for tagging, retrieving, and manipulating data—and the
____, which does have this mechanism.
• Database Approach
• Data Approach
• Datafile Approach
• Indexed file approach
36
• A ________ is a unit of information in a ________
•
•
A.
record, data source
•
B.
data source, field
•
C.
record, field
•
D.
field, record
37
• What do you call a primary key field included in
another table?
•
•
A.
Parent key
•
B.
Child key
•
C.
Foreign key
•
D.
Index
38