Transcript Database

Database
A Computer Database is a structured
collection of records or data that is
stored in a computer system.
How Microsoft Access breaks down
a database
Database File: This is your main file that
encompasses the entire database and that is
saved to your hard-drive or floppy disk.
Example) StudentDatabase.mdb
Table:A table is a collection of data about a
specific topic. There can be multiple tables in a
database.
Example #1) Students
Example #2) Teachers
Field:Fields are the different categories within a
Table. Tables usually contain multiple fields.
Example #1) Student LastName
Example #2) Student FirstName
Datatypes:Datatypes are the properties of each
field. A field only has 1 datatype.
FieldName) Student LastName
Datatype) Text
Terms to Know
• Data Entry
– The process of getting information into a
database, usually done by people typing it in
by way of data-entry forms designed to
simplify the process.
• Dbms
– Database management system. A program
which lets you manage information
Terms to Know
• Flat File
– A database that consists of a single table.
• Key field
– You can sort and quickly retrieve information from a
database by choosing one or more fields to act as
keys.
• For instance, in a students table you could use a combination
of the last name and first name fields (or perhaps last name,
first name and birth dates to ensure you identify each student
uniquely) as a key field.
• Key fields are also used in relational databases to maintain
the structural integrity of your tables, helping you to avoid
problems such as duplicate records and conflicting values in
fields.
Terms to Know
• Primary key
– A field that uniquely identifies a record in a table.
• In a students table, for instance, a key built from last name + first
name might not give you a unique identifier (two or more Jane Does
in the school, for example). To uniquely identify each student, you
might add a special Student ID field to be used as the primary key.
• Foreign Key
– A key used in one table to represent the value of a primary key in
a related table. While primary keys must contain unique values,
foreign keys may have duplicates.
• If we use student ID as the primary key in a Students table (each
student has a unique ID), we could use student ID as a foreign key
in a Courses table, each student may do more than one course, the
student ID field in the Courses table will hold duplicate values.
Terms to Know
• Index
– A summary table which lets you quickly look
up the contents of any record in a table.
• Query
– A view of your data showing information from
one or more tables.
• Record
– A record contains all the information about a
single 'member' of a table.
Terms to Know
• Relational Database
– A database consisting of more than one table.
• SQL
– Structured Query Language (pronounced
sequel in the US; ess-queue-ell elsewhere).
– A computer language designed to organize
and simplify the process of getting information
out of a database in a usable form, and also
used to reorganize data within databases.
Create a Database
• When Microsoft Access first starts up,
available templates are displayed.
• Double-click the icon for the kind of
database you want to create.
 Practice
• Open Access
• Select Blank database.
• Save your database  go back to the File
menu and select “Save Database As”.
– Name your file “DBPractice”
Tables
• A table is a collection of data about a specific topic, such
as students or contacts. Using a separate table for each
topic means that you store that data only once, which
makes your database more efficient, and reduces dataentry errors.
– Tables organize data into columns (called fields) and rows
(called records).
Create a Table from scratch in
Design view
• Select the Create menu and choose to
create “Table Design.”
• Define each of the fields in your table.
– Under the Field Name column, enter the
categories of your table.
– Under Data Type column, enter the type you
want for your categories.
• The attribute of a variable or field that determines
what kind of data it can hold.
– Under the Description column, enter the text
that describes what you field is.
 Practice
• For our tutorial enter the following items:
• Save this table as “Student”
Primary Key
• One or more fields (columns) whose value or values uniquely
identify each record in a table.
– Does not allow Null values and must always have a unique
value.
– A primary key is used to relate a table to foreign keys in other
tables.
– NOTE: You do not have to define a primary key, but it's usually a
good idea. If you don't define a primary key, Microsoft Access
asks you if you would like to create one when you save the table.
 Practice
– For our tutorial, make the Soc Sec # field the primary key,
meaning that every student has a social security number and no
2 are the same.
• To do this, simply select the Soc Sec # field and select the primary
key button under the Table Tools Design menu.
• After you do this, Save the table
Advanced Table Features
w/Microsoft Access
• Assigning a field a specific set of
characters
Practice - Make a First Name only allow 15
characters.
•
•
•
•
•
Switch to Design View
Select the field you want to alter
At the bottom select the General Tab
Select Field Size
Enter the number of characters you want this field
to have (15)
Advanced Table Features
w/Microsoft Access
• Formatting a field to look a specific way
 Practice - Format Phone Number w/ Area Code (xxx)
xxx-xxxx
•
•
•
•
•
•
•
•
•
•
•
Switch to Design View
Select the field you want to format
At the bottom select the General Tab
Select Input Mask Box and click on the ... button at the right.
Select Phone Number option
Click on Next
Leave !(999) 000-0000 the way it is. This is a default.
Click Next
Select which option you want it to look like
Click Next
Click Finish
Advanced Table Features
w/Microsoft Access
• Selecting a value from a dropdown box with a set of
values that you assign to it. This saves you from typing it
in each time
 Practice – Being able to choose a city that is either Brookville,
Clayton, or Lewisburg
•
•
•
•
•
•
Switch to Design View
Select the field you want to alter (City)
At the bottom select the Lookup Tab
In the Display Control box, select Combo Box
Under Row Source Type, select Value List
Under Row Source, enter the values how you want them displayed,
separated by a comma. (Brookville, Clayton, Lewisburg)
– NOTE: This will not alphabetize them for you, so you will have to do
that yourself. It should look something like this:
• In the datasheet view you should see the change when you go to
the city field.
Switching Views
• To switch views from the datasheet
(spreadsheet view) and the design view,
simply click the button in the top-left hand
corner of the Home menu.
Datasheet View
Displays the view, which
allows you to enter raw data
into your database table.
Design View
Displays the view, which allows
you to enter fields, data-types,
and descriptions into your
database table.
Entering Data
• Click on the Datasheet View and simply
start "chugging" away by entering the data
into each field.
– NOTE: Before starting a new record, the field
you have set as your Primary Key must have
something in it. If you did not set a Primary
Key then it is OK.
 Practice
• Enter the following information into your
Student table.
• Save your Work
Manipulating Data
• Adding a new row
– Simply drop down to a new line and enter the
information
• Updating a record
– Simply select the record and field you want to update,
and change its data with what you want
• Deleting a record
– Simply select the entire row and hit the Delete Key on
the keyboard
• Sorting
– Simply choose a field and make selection.
Practice
• Create a second table titled Teacher that
contains the following Fields…
Forms
• A form is nothing more than a graphical
representation of a table. You can add, update,
delete records in your table by using a form.
– NOTE: Although a form can be named different from
a table, they both still manipulate the same
information and the same exact data. Hence, if you
change a record in a form, it will be changed in the
table also.
• A form is very good to use when you have numerous
fields in a table. This way you can see all the fields in
one screen, whereas if you were in the table view
(datasheet) you would have to keep scrolling to get the
field you desire.
Create a Form using the Wizard
• It is a very good idea to create a form using the wizard,
unless you are an advanced user. Microsoft Access
does a very good job of creating a form using the wizard.
The following steps are needed to create a basic form:
– Click on the Create menu
– Select Form Wizard
– Select the fields you want to view on your form.
• Most of the time you would select all of them.
– Click Next
– Select the layout you wish
– Click Next
– Give you form a name, and select Open the Form and enter
information
– Select Finish
– You should see your form.
Practice
• Enter the following data into your Teacher
table using the form you have created…
Relationships
• After you've set up multiple tables in your
Microsoft Access database, you need a
way of telling Access how to bring that
information back together again.
• First step - define relationships between
your tables.
• After you've done this, you can create
queries, forms, and reports to display
information from several tables at once.
Relationships
• A relationship works by matching data in
key fields - usually a field with the same
name in both tables.
– In most cases, these matching fields are the
primary key from one table, which provides a
unique identifier for each record, and a foreign
key in the other table.
Practice
• Teachers can be associated with the students they're responsible
for by creating a relationship between a teacher table and a student
table using Room fields.
• Steps for creating relationships between tables.
– Click on the Database Tools menu ---> Relationships
– Select the Tables you want to link together, by clicking on them and
selecting the Add Button
– Drag the primary key of the Parent table (Teacher in this case), and
drop it into the same field in the Child table (Student in this case.)
– Click Create to Save the Relationship
Reports
• A report is an effective way to present your
data in a printed format.
• Because you have control over the size
and appearance of everything on a report,
you can display the information the way
you want to see it.
Create a Report using the Wizard
• As with the Form, it is a very good idea to create a report using the
wizard, unless you are an advanced user. Microsoft Access does a
very good job using the wizard to create reports.
–
–
–
–
–
–
–
–
–
–
–
–
–
Click on the Create menu
Select Report Wizard
Select the fields you want to view on your report.
Click Next
Select any grouping you would like to see
Click Next
Select the order in which you would like the information displayed.
Click Next
Select the layout you desire
Click next.
Give you report a name.
Select Finish
You should see your report.
Queries
• Queries can be created using a wizard or in
design view. Using the design view give you
more control over the information you are
searching for.
– Lets say we want to see a report that shows student
information along with their homeroom teachers name
and what subject that teacher teaches.
• Select the Query Design button under Create menu.
• Add the tables you plan to use for your query.
• Use the drop down box on the far left to add all student
information to the query.
• In the second column from the left select that you want to
display the teachers last name.
• In the third column from the left select that you want to
display the teachers subject.
• View the saved query to see what is displayed.
Creating Mail Merge Labels
using a Wizard
• Microsoft Access lets you create Mailing Labels for your
database that you have. To do this do the following:
–
–
–
–
–
–
–
–
–
–
Click on the Labels button under the Create menu
Select the layout of your labels
Click Next
Select the font size and color you want on each label
Click Next
Set the label up the way you want it to look.
Click Next
Select how you want your labels sorted
Give your label report a name
Select Finish