Transcript Presentazione di PowerPoint

Microsoft Access 2010

Relational databases’ program






Part of the Microsoft Office package
Administer relational database
Update database through forms
Build complex queries
Create reports
Competitors: Oracle, PostgreSQL, MySQL
5 Aug 2014
1
Slide only for explanation, database theory will not be asked at exam
Single-table databases
Fields and records

1.
2.
3.
4.
5.
6.
5 Aug 2014
Header in the first line
Same content for every field
Each record refers to a single object
Records are independent
Records and fields are disordered
No field which can be calculated
2
Slide only for explanation, database theory will not be asked at exam
Multiple-table databases

Primary key


ID
Structured tables connected via relations

“one to many” or “many to one”


5 Aug 2014
Foreign key
“many to many”: junction table
3
Slide only for explanation, database theory will not be asked at exam
Example: Your library

It is a database of your books and their authors.

Suppose that each book has only one author.
5 Aug 2014
4
Slide only for explanation, database theory will not be asked at exam
Example: students and exams

It is a database which handles data regarding
students and their exams.
5 Aug 2014
5
Slide only for explanation, database theory will not be asked at exam
Example: students and exams 2

Expanded with information on
professors and their exams

5 Aug 2014
Many to many relation  another extra table
6
Northwind database example

Northwind 2003 database example



Copy the file to your Desktop
Enable
Access saves automatically data
modifications!!!
 Undo

is possible only for the last action
 File  Save


Saves only created objects inside the database file
To duplicate the database
 File Save Database As
5 Aug 2014
7
Table
Northwind database overview
 Home  View  Datasheet View
 Home  View  Design View


Fields types








5 Aug 2014
Text: max 255 alphanumeric characters
Memo: max 65,535 characters
Number and Currency and Autonumber
Date/Time
Yes/No
OLE object: link or embed external objects
Hyperlink
Primary key
8
Select query
a question which produces a virtual table
called view
 Create  Queries  Query Wizard
 Home  View  Design View




Show
Sorting
Criteria


Other fields values
The query asks for a value
 Query  Show tables
5 Aug 2014
9
Select query: virtual fields



Expression builder
Functions: DateDiff, DateAdd, Year, Date
Operators: Between, Like * ?

5 Aug 2014
Expression builder for validation rules and in
table validation rules: Like
10