Textbook Notes (290,000)
CA (170,000)
McMaster (10,000)

COMPSCI 1BA3 Chapter Notes -Relational Database Management System, Unique Key, Database Design

Computer Science
Course Code

This preview shows pages 1-3. to view the full 11 pages of the document.
Introduction to Database Concepts
Organizing Data
Field: single characteristic/attribute of a person, place, object, event or idea
Ex. Customer ID, first name, last name, phone number, contract amount, etc.
Table: collection of fields that describe person, place, object, event or idea
Field value: value or content in a field
Record: set of field values (each row of field values is a record)
Databases and Relationships
Relational database: collection of related tables
Common field: a field that occurs in separate tables that can be used to
connect records in those tables
Primary key: field/collection of fields whose values uniquely identify each
record in a table (no record has the same primary key value)
Foreign key: when a primary key from one table exists in another table but
not as the primary key
Foreign key does not have to have unique values, two tables are related
Relational Database Management Systems
Database management system (DBMS): software program that lets you
create databases and manipulate data in them
Relational database management system:
o Data organized as collection of tables
o Relationship between tables formed through common field
o Allows you to create database structures containing fields, tables,
table relationships
o Can easily add new records, change field values in existing records
and delete records
o Contains built-in query language (lets you get immediate answers to
questions about data)
o Has built-in report generator (lets you produce professional-looking
formatted reports)
o Protects database through security, control and recovery facilities
More than one user can enter, retrieve and analyze data from database
Holds massive amounts of data
Creating a Database
First screen that appears is Backstage view
Can get info about current database, create new one, open existing one
Template: predesigned database that makes it quick and easy to create new
Creating a Table in Datasheet View

Only pages 1-3 are available for preview. Some parts have been intentionally blurred.

Tables contain all of data in database
Should know the purpose of each field in a table
Should use descriptive names for fields that won’t take up a lot of space
Renaming the Default Primary Key Field
ID field is default primary key
Can rename default primary key field to select most appropriate field
Changing the Data Type of the Default Primary Key Field
Data type: determines what field values can be entered in a field
AutoNumber: a data type that automatically inserts a unique number into
field for every record (good for primary key)
Text: data type that allows values with letters, digits, other characters not
used in calculations
Adding New Fields
Can use Add & Delete group on fields tab or Click to Add column in table to
add new fields to datasheet
Datasheet view: shows contents of a table in rows (records) and columns
Field selector/record selector selects entire column or row
Saving a Table
New records immediately stored in database when entered
Table’s design (field names, layout changes) not saved automatically
Save a table using a name that best identifies the info it contains
Access will sort records in primary field in order when saved
Cannot add new record in middle of table, has to be at bottom
Opening a Table
Open table (or any Access object) by double-clicking its name in Navigation
Copying Records from Another Access Database
Can copy/paste records from a table into a database
Must have same structure as existing table
I.e. same fields, same design and characteristics in same order
Navigating a Datasheet
Can use vertical and horizontal scroll bars to navigate
Navigation buttons can also be used to move vertically through records
Buttons and descriptions on AC p. 27
Creating a Simple Query

Only pages 1-3 are available for preview. Some parts have been intentionally blurred.

Query: a question you ask about the data sorted in a database
Tell Access which fields you need and what criteria should use to select
records to answer question
Access displays only desired info so don’t have to navigate though all of data
Use Simple Query Wizard to create queries
Select wanted fields from Available Fields and move to Selected Fields
Query results not stored in database
Query design stored as part of database with specified name
Creating a Simple Form
Use a form to enter, edit and view records in a database
Present data in many customized and useful ways
Displays one record at a time so you can focus on values for one record
Layout view: view forms are displayed in
Use navigation buttons to display different records in form
Creating a Simple Report
Report: formatted printout (screen display) of the contents of one or more
Creates a report based on selected table or query
Offers a more visually appealing format for data
Dotted lines show where text will print on the page
Layout view can be used to resize columns so they appear on the same page
Print Preview shows how report will look when printed
Viewing Objects in the Navigation Pane
All Access Objects: lists all database objects (tables, queries, forms, reports)
Arrow on All Access Objects bar displays menu for ways to group/display
objects in Navigation Pane
Managing a Database
Manage databases to ensure they operate in most efficient way and data they
contain is secure
Compacting and Repairing a Database
Space occupied by deleted/replaced records does not automatically become
available for other records
Compacting: rearranges data and objects in a database to decrease file size
Makes more space on disk and can open and close database quicker
Use Compact & Repair Database option to fix damaged database
Might become damaged if exit Access too suddenly
Use Compact on Close option to compact and repair every time you close
Backing Up and Restoring a Database
You're Reading a Preview

Unlock to view full version