Lecture Access 1
Database Overview and Creating Tables Create an Employee Table
Why database for 110A
Because businesses generate a lot of data What are some ways in which businesses use data they collect? Data is needed to generate meaningful information The bottom layer of a three tiered app You'll be more prepared for BUS112 So it is important to have working knowledge of databases.
Overview of Access
What is a database?
A collection of related tables. The SJSU student database has many tables. Some table it might include are:
Students personal information table with your name, contact information and student ID Transcripts table WST status
Components of a Database
Field (aka column, attribute): Several Characters (e.g. Name) Record (aka row): Several Fields (e.g. your school record) Primary key: Unique identifier for the record (e.g. SSN) Table: Group of records e.g. (my class record). Database: Group of related tables
An example of data stored in a database table
Different DBMS available in market?
Oracle, DB2, Microsoft Access, Microsoft SQL Server, Firebird, PostgreSQL, MySQL, SQLite, FileMaker and Sybase Adaptive Server Enterprise (From Wikipedia Click on link to read more) For this class we focus on MS Access (2007) but you will learn a bit about SQL queries as well.
Let's look at one...
This is a student database...Called Our_students_in_class_exercise
Steps in creating database
Plan: Purpose of the database, its scope. Remember adding a bathroom after a house is built costs 10 times as much as planning the house with it. Design: Build conceptual data models, report formats, data dictionary. Develop: Create tables, queries, reports Implement: Populate the database and test Review: Periodic checks to ensure compliance
Creating your own databases
Say you are hired by a company to automate their employee payroll system. What are some of the fields that you can think of?
Creating your own databases
Some potential fields
EmployeeID Hire Date first name last name address phone number birth date Picture pay scale Hours worked
You can get this information by analyzing the existing records of the company.
Creating your own database
You may need several tables to create this database. Why? Creating several tables ensures easier access, faster processing, and removes redundancy You can join these tables with relationships using primary and foreign keys
Creating your own employee database
New database blank database select location where file needs to be saved. Name the database as employee. Create a new table using design view. Explore other views for enhanced learning. Notice different types of data types and associated properties. The first field is employeeID. Right click to make it primary key.
Creating a table
Add the remaining fields
Hire date : Date/time Last/FirstName: Text Street/City/State/Zip: (e.g. Text State 2chars) Phone: Text Birth Date: Text Save the table as EmpMain
Populating Tables
This means to add records You don't need to save as access automatically updates the table. You can delete, modify, add records now. You can delete, modify, and add fields too. However before you do this, you have to take into account the data integrity issues. You can play around with format, custom properties, and other options to enhance the experience.
Let's add some records
Add 3 records, completing the following fields:
EmployeeID HireDate LastName FirstName Street/City/State/Zip Phone BirthDate PayRateID (1A, 2B, 3A)
Relationships
PRIMARY KEY: It is a field (or combination of fields) that makes each record in a table unique. Every table must have a primary key and each record must have a unique value as its primary key Tables and their relationships to each other are stored Tables are related in a one-to-many (or one-to-one) relationship A foreign key is required to establish the relationship one table's foreign key is another table's primary key.
Primary Keys
PRIMARY KEY: It is a field (or combination of fields) that makes each record in a table unique. Every table must have a primary key and each record must have a unique value as its primary key.
Primary keys
Examples of Properties
Properties of the CustomerID Field Properties of the Gender Field
Data Types
DATA TYPE: Determines the type of data that can be entered and the operations that can be performed on that data Access recognizes the following data types: Number (can be used in calculations) Text the default data type for new fields (alphanumeric characters up to 255 per field) Memo (alphanumeric value of unlimited length) Date/Time AutoNumber (numeric value, automatically increases) Yes/No OLE Object Currency Hyperlink
Data Type
Importing Data
For your course project you'll be importing data for your student table into your database. First click on the "External Data" Tab in Access Select the Excel as the file type that you'll be getting the data from This starts a wizard
Importing Data
There are 3 options...create a new table with the imported data, add the imported data to an existing table (this requires that the imported and existing data are similar), or link a table. We'll do the first Browse to where the import file is and open Click open and OK
Importing Data
You should be a preview of the data, Click Next Select the checkbox that says "first row contains column headings" and Next Now you can see the column headings as your field names and you can modify each fields datatype if you need to and Next Select the key if you wish
Importing Data
Now name your table something helpful and your done. You can now go into the table in access and modify its design you'll probably need to...