Transcript MS Access Module

M.S. Access Module
CAS 133
Russ Erdman
M.S. Access Module

Assignment Overview


Two options for the unit:

All students complete Units A, B and C

In class – create a movie database as a group

Out of class – do all of Unit D and some
additional activities on your own – You do not
have to attend class until the final
Practice and review for the Final

During last hour on the last day of class we will
work through the final.

Students choosing the out of class option may
want to attend or pick up a copy of the final to
practice.

The Final is 60 points. Some don’t finish, but
those that practice usually have no problems
M.S. Access Module
 In Class Option:
 We will build a video store inventory database.
 Work modules A, B, C on your own.
 Bring VHS or DVD cover to class on the date listed
in the class schedule – 5 pts.
 We will work problems 4 and 5 together in class.
 Note: You must attend the remaining classes!
Otherwise, you must do the out of class projects.
 During the last hour of the last class we will
practice the final – you will take a copy home to
practice.
M.S. Access Module
 What is Access?
 Not just a database (DB), but a Database Management
System (DBMS)
 Databases are to Access as documents are to Word and
spreadsheets are to Excel
 A DBMS is different from a word processing program
 Doesn’t just create a DB, but manages them.
 You can add security, define who uses them, manage data,
do math on data, query data and create reports.
 What is a database?
 Something you create and manage in Access
 A place for data
M.S. Access Module

Access does four different things with a database:
1.
Tables: create tables – like an Excel spreadsheet – and put
data into it. INPUT for a computer.
2.
Queries: create queries – questions your data – can’t easily
do it in Excel. PROCESSING for a computer.
What is highest expense in January budget? Sort or use
min/max in Excel, but you can get quicker answers with
Access.
A budget is good for Excel.
A mailing list is good for Access.
3.
Reports: create reports – for example, print out labels for
Christmas list or keep track of membership in an
organization.
Word is not good for this. OUTPUT for a computer.
4.
Forms: Input for computer controls what gets put into
database.
M.S. Access Module
 Data vs. Information
 Database is a storage place for data (raw facts and
figures)
 You can do data entry and have NO CLUE what it
is used for.
 Data is meaningless until you organize it.
 This is part of basic Information System theory –
take CIS 120 for more IS theory.
 If you find Access is meaningless to you after this
section of the class or your eyes glaze over, then
don’t go on into CIS for databases. (Perhaps
networking will appeal to you.)
M.S. Access Module
 There are other DBMS applications.


AS400 DBMS
Oracle DBMS

Oracle is a huge and powerful DBMS – more
powerful than Access.
 Access is small potatoes compared to Oracle, but still
powerful.



The PCC database is an Oracle DB called Banner.
The PCC DB has lots of records to keep track of
and lots of data – millions of records.
Records are organized into about 2000 tables.
M.S. Access Module
 Database terminology
 Entities: anything about which you want to know
something.
 Data are the facts; entities are what the facts are
about.
 Entities can be virtually anything - people, places,
events, or concepts.
 Entities are represented by tables
 Entity sets are collections of related entities, such
as students and classes
 These relationships are determined by the DB designer
 Entities or tables are the most important objects in an
Access database because they contain all of the data
within the DB.
M.S. Access Module
 Database terminology (cont)

Record (or tuple) is a row in the table.

Contains all the demographics or facts about a single subject in
the table.
 For example: in the Student Table or Entity a record would contain
all the information about one student.

Field of the record are the columns of the table.




A field is the smallest piece of information (or attribute) in a
database, such as the students name.
Fields are not usually meaningful in and of themselves.
Fields should be simplified or decomposed: separate first name, last
name and middle initial.
Key field: contains unique information for each record, such as a
students SSAN, an ID number, a part number, etc.
 Also called a primary key (PK) contains attributes that uniquely identify a
particular row. It can be assigned automatically by Access.
 A primary key may consist of a single attribute or a combination of
attributes - whatever is needed to provide unique identification.
M.S. Access Module
 Database terminology (cont)
 Relational database:
 Access is a relational database
 More than one table, such as the Customer, Sales
and Product tables can share information.
 Relational comes from the fact that two tables are
linked, or related, by a common field.
 One tables PK (primary key) may be an FK (foreign
key) in another table so they can be linked
(related) to each other.
 The PK is useful in searching the DB – a PK can’t
repeat.
M.S. Access Module
 How big is a database?
 DBs can get very large very fast.

PCC database has possibly a million records


The DB keeps a history of current courses and all previous
courses for the past 10 years.
There are as many courses as are listed in the Class Schedule
and as many as there has ever been back 10 years.
 Who owns the Tables (Entities)
 Course table is managed by Registration

Employee table is managed by Human Resources


That means they are responsible for security, data entry and
accuracy, etc.
Whole departments manage the PCC DB, not just one person.