microsoft access introduction. what is a database suppose you are a school administrator. you need...
TRANSCRIPT
Microsoft Access
Introduction
What Is a Database
• Suppose you are a school administrator. You need to have information about– Students– Faculty– Staff– Courses– Buildings– Financial Resources & Liabilities– Etc.
Some Questions
• Which students live in Honolulu?• What are the names of all students in ENG
101-02?• How large is the room used by ENG 101-02?• Which students does Prof. Jones teach this
semester?• How many students who live on campus are
have all morning classes?• Etc.
Database
• Database– Collection of data items which are organized so that they
can easily be searched, modified, added, removed, and otherwise maintained.
• Relational Database– Data items are organized by tables
• Database Management System (DBMS)– A software system that can search, modify, add and remove
items, and otherwise maintain a database
• Access Database: a relational database from MS
Table: Basic Unit of DatabaseSchool Database
• Student Table– ID, Name, Address, City, State, Phone #
• Course Table– Dept, Course #, Title, Description
• Classes Table– Course #, Section #, Instructor, Room #
• Equipment Inventory Table– Dept #, Inventory ID, Description, Status
Table: Basic Unit of DatabaseCompany Database
• Employees Table– SSN, Name, Location, Salary, Gender, Title
• Departments Table– depID, depName, Manager, Description
• Offices Table– offID, Location, Phone, Manager
• Inventory Table– invID, invDescription, invQuantity
Employees Table
Acct RepM$50,000PortlandMoon5678
ManagerM$70,000Los AngelesHeft6789
Acct RepF$38,000PortlandLaw7890
Acct RepF$37,000DenverMars4567
TraineeM$29,000DenverDanson3456
Acct RepF$48,000Los AngelesChan2345
ManagerM$67,000PortlandSmith1234
TitleGenderSalaryLocationNameSSN
•Back to Pivot Table
•Back to Primary Key
Basic Terms• Field
– Column
• Record– Row
• Table– Set of Records
• Database– Collection of Tables
• Database Management System (DBMS)– Software to manage Database
Access Objects• Table
– Contains collection of basic data
• Form– Displays user interface for data input and
output
• Query– Displays data which satisfy certain criteria
• Report– Produces printouts
Pivot Tables
• An Employees (ID, name, location, salary, gender, title) displays each employee.
• A pivot table computes summary statistics for a table, according to various fields.
Pivot Table for Employees
$48,444$33,000$59,000$51,667Grand Total
$29,000$29,000Trainee
$43,500$37,000$48,000$44,000Acct Rep
$68,500$70,000$67,000Manager
Ave SalaryAve SalaryAve SalaryAve SalaryTitle
Grand TotalDenverLos AngelesPortland
Location
To Employees Table
Relational Database
How Do You Answer the Following Questions
• At which Pamela Milgrom work?– Involves Employees and Locations tables
• Which employees are managers?– Involves Employees and Titles tables
• Which employees work in Boston office?– Involves Employees and Locations tables
Relationship
Designing a TableStudents Table
HyperlinkHomePageTextMajor
Y/NHawaiiResidentTextPhone
TextE-mailTextZIP
DateDateAdmittedTextState
NumberQualityPointsTextCity
NumberCreditsTextAddress
TextGenderTextLastName
Y/NFinancialAidTextFirstName
DateBirthDateTextSSN
TypeField NameTypeField Name
Designing a Table
• Include all data items that you can foresee.• Store data in its smallest parts: E.g.,
– LastName, FirstName, City, ZIP– But Address
• Do not use fields that can be calculated. E.g.,– Age– GPA
Primary Key• Primary Key—a field (or combination of fields) that
makes each record in a table unique To Employees Table
• Candidates in the Employees Table– ID– SSN– Email address– LastName + FirstName + Address– But not LastName, LastName + FirstName
• If none is available, insert an ID field (AutoNumber)
Properties
• Field Properties—characteristics of a field• For Example
– Field Size property– Format property– Input Mask property– Caption Property– Default Value property– Required property
Views
Field Properties
Creating a Query
• Query • Lets user to ask questions• Allows user to choose a subset of table that
satisfy certain conditions
• Given Students table• How many are from California?• Who are majoring in Business?• Who are from California AND majoring in
Business?
USA Table
• The fields in the USA table are:– Name– Capital– Nickname– Year established– Population– Area– Region (Pacific, Mountain, New England, South
Central, North Central, Middle Atlantic
Queries
• States in the Pacific region
• States in the Pacific OR New England region
• States with a population over 5,000,000
• States established since 1900
• States established before 1900 AND having a population over 5,000,000
• States established in the 1800’s
• States established in the 1700’s
Queries on Students
• List all Business Majors• List Business Majors from Florida• List Communications Majors Receiving Financial
Aid• List all Students from Florida• List Business Majors with Fewer than 60 Credits• List all Students with 60 to 90 Credits• List all Business Majors with Fewer than 60 Credits
or Communicatuons Majors born on or after April 1, 1985
Creating a Select Query
• 1. Open Our Students database– QueriesNew– Design View is open
• 2. Add the Students Table– Select Students tableAddClose. Maximize. Expand the
upper portion.
• 3. Create a query– Drag LstName field from Students table to the first column.– Drag FirstName, PHoneNUmber, Major, and Credits to the
QBE grid.– Adding & Deleting fields
• 4. Specify the Criteria– To display all UNDECIDED majors:
• In Criteria row for Major, type Undecided• In Sort under LastName field, select Ascending• Save• Type Undecided Major as the Query name. Click OK.
• 5. Run the Query– QueryRun (Run button)
• 6. Modify the Query– To display all UNDECIDED majors with more than 30 credits– Uncheck Show check box in the Major field– In Criteria row under credits, type >30.– Save.– Run
• 7. Create a Report– Open Our Students Database– Click Reports buttonCreate report by using Wizard– Tables/Queries list boxQuery: Undecided Major– Click >> button. Next.– Next, Next– Tabular layout, Portrait orientation, Soft Gray.– Title: Undecided Major. Finish
• 8. View the Report– Place date in the header.– Place your name in the footer.
Other Queries
• All students from California or Florida
• All Business students from Florida
• All Education students from California or Florida
• All Education students with credits over 30
• All Education students with credits between 30 and 60