Search for notes by fellow students, in your own course and all over the country.
Browse our notes for titles which look like what you need, you can preview any of the notes via a sample of the contents. After you're happy these are the notes you're after simply pop them into your shopping cart.
Document Preview
Extracts from the notes are below, to see the PDF you'll receive please use the links above
Databases for beginners
02-DBBA
Marek Kręglewski
1
About the course
• Steven Roman, Access Database Design &
Programming (3rd Edition), O’Reilly 2002
• Curtis D
...
microsoft
...
microsoft
...
aspx#BMpartsofadatabase
2
Basics about a database?
• A database is a structured collection of records
...
• Databases versus spreadsheets
– easy manipulation of data
3
Single table database
ISBN
Title
AuID
AuName
AuTel
PubID
PubName
PubTel
Price
0-99-999999-9
Emma
1
Austen
111-111-1111
1
Big House
123-456-7890
20
...
00 zł
0-91-045678-5
Hamlet
5
Shakespeare
555-555-5555
2
Alpha Press
999-999-9999
20
...
00 zł
0-555-55555-9
Macbeth
5
Shakespeare
555-555-5555
2
Alpha Press
999-999-9999
12
...
00 zł
0-55-123456-9
Main Street
9
Smith
123-222-2222
3
Small House
714-000-0000
23
...
00 zł
0-321-32132-1
Balloon
2
Sleepy
222-222-2222
3
Small House
714-000-0000
34
...
00 zł
0-321-32132-1
Balloon
11
Grumpy
321-321-0000
3
Small House
714-000-0000
34
...
g
...
00 zł
0-11-345678-9
Moby Dick
3
49
...
00 zł
0-123-45678-0
Ulysses
2
34
...
00 zł
0-321-32132-1
Balloon
3
34
...
00 zł
0-555-55555-9
Macbeth
2
12
...
00 zł
0-91-335678-7
Faerie Queen
1
15
...
00 zł
0-99-999999-9
Emma
1
20
...
00 zł
1-22-233700-0
Visual Basic
1
25
...
16
Building relations
Relation many-to-many
Table S
A1
Table S/T
A2
A3
A4
A5
A1
Table T
B1
B1
B2
B3
B4
17
Enforcing referential integrity
• Cascade Update Related Fields – the
values of foreign keys change following
changes of the values of the primary key
• Cascade Delete Related Records –
deleting a record from the primary field in
a relationship causes a deletion of all
related records in the second table
18
Indexing field values
•
•
•
•
Purpose: speed up access to specific data
Used in large tables
Updating of all indexes every time a table record is updated
or added
Example
Index of towns
Table of shops
Gdańsk
•
1
Plus
Toruń
Kraków
•
2
Piotr
Poznań
Poznań
•
3
Tesco
Kraków
Poznań
•
4
Tesco
Poznań
Toruń
•
5
Plus
Gdańsk
19
Principles for building a database
• Types of attributes:
– Identification
– Information
– Identification+information
• Example 1: {PubID,PubName,PubTel,FoundYear}
Ident
Ident+inform
Inform
ISBN
Title
PubID
PubName
1-1111-1111-1
Macbeth
1
Big House
• Example 2:
2-2222-2222-2 Hamlet
1
5-5555-5555-5
2
ABC Press
20
Queries
• Database – data located in tables + relations
• Query – primary mechanism for retrieving information
from a database, consists of questions presented to the
database in a predefined format – an expression stored
in a database having a unique name
• Answer to the query – a computed table = Dynaset
• SQL – Structured Query Language
• Types of queries:
– Select query
– Action queries (Make-Table, Append, Update, Delete)
– Crosstab query
21
Creating a query in MS Access
22
Design View of a query
Drug and drop principle
23
Selection criteria
• Specifying criteria:
–
–
–
–
A value of an expression
Use of criteria operators: <, >, <=, >=, <>
BETWEEN, e
...
BETWEEN 2 AND 5,
LIKE, e
...
LIKE „*[b-d]k[0-5]?#”
• Logical operators: OR, AND
– e
...
„Smith” OR „Jones”
• Mathematical operators: +, - ,*, /, \, MOD, ^
• Text operator:
&
• Date/Time fields
– Format #2009-06-19#
#16:00#
#4:00PM#
– Date/time functions: Date()
Day(date), Month(date), Year(date), Weekday(date)
24
Calculation on groups of records
25
Parameter query
When you run the query, you will be prompted to supply the maximum price
26
Crosstab query
27
Database Normalization Basics
• Purpose: - eliminating redundant data
- ensuring logical relations of dependent data
• The normal forms
- 1NF, 2NF, 3NF, BCNF
- guidelines only
- hierarchical structure of NF
• First Normal Form (1NF)
- eliminate duplicative columns
- create separate tables for each group of related data
and define primary keys
e
...
Authors = Jones, H
...
(incorrect)
28
Database Normalization Basics
• Second Normal Form (2NF)
- meet all requirements of the 1NF
- remove subsets of data that apply to multiple rows and
place them in separate tables
- create relationships between new tables using foreign
keys
• Example - table of addresses:
{Town, Street, HouseNumber, HouseColor, SizeOfTown}
attribute of Town
29
Database Normalization Basics
• Third Normal Form (3NF)
- meet all requirements of the 2NF
- remove columns that are not dependent upon primary key
• Example
{ISBN, Title, NumberOfBooks, UnitPrice, TotalValue}
where: TotalValue = NumberOfBooks*UnitPrice
Correct form:
{ISBN, Title, NumberOfBooks, UnitPrice}
30
Database Normalization Basics
• Fourth Normal Form (Boyce-Codd NF = BCNF)
- meet all requirements of the 3NF
- remove all multi-valued dependencies
• Example
{Town, Street, HouseNumber, ZIPcode}
where: combination of {Town, Street}
determines
{Zipcode}
Correct form:
{Street, HouseNumber, ZIPcode}
and {ZIPcode, Town}
31
Decomposition of tables
• Relations between data must be conserved
AuID
AuName
PubID
A1
Smith, John
P1
A2
Smith, John
P2
• Decomposition
AuID
AuName
AuName
PubID
A1
Smith, John
Smith, John
P1
A2
Smith, John
Smith, John
P2
• Display all John Smiths
AuID
AuName
PubID
A1
Smith, John
P1
A1
Smith, John
P2
A2
Smith, John
P1
A2
Smith, John
P2
32
Example: Relation ORDERS not normalized
No_order
IDsupplier
Name_
Supplier
Address_
Supplier
IDpart
Name_
part
Qty
Warehouse
Address_
Warehouse
001
300
VW
Wolfsburg,
Rotestrasse 10
53
Carburetor
100
5
Warszawa,
Chopina 3
57
Crankshaft
50
5
Warszawa,
Chopina 3
59
Mudguard
500
6
Warszawa,
Mozarta 25
54
Carburetor
500
5
Warszawa,
Chopina 3
32
Wheel
100
6
Warszawa,
Mozarta 25
002
400
WSK
Świdnik,
Kraszewskiego 5
003
500
VW
Polska
Antoninek,
Słowackiego 2
88
Engine
15
7
Warszawa,
Bacha 3
004
600
FIAT
Bielsko-Biała,
Mickiewicza 25
58
Mudguard
400
6
Warszawa,
Mozarta 25
21
Alternator
50
7
Warszawa,
Bacha 3
53
Carburetor
200
5
Warszawa,
Chopina 3
57
Crankshaft
30
5
Warszawa,
Chopina 3
59
Mudguard
20
6
Warszawa,
Mozarta 25
005
006
300
300
VW
VW
Wolfsburg,
Rotestrasse 10
Wolfsburg,
Rotestrasse 10
33
Relation ORDERS in the first normal form (1NF)
eliminates duplicative columns
No_order
IDsupplier
Name_
Supplier
Address_
Supplier
IDpart
Name_
part
Qty
Warehouse
Address_
Warehouse
001
300
VW
Wolfsburg,
Rotestrasse 10
53
Carburetor
100
5
Warszawa,
Chopina 3
001
300
VW
Wolfsburg,
Rotestrasse 10
57
Crankshaft
50
5
Warszawa,
Chopina 3
001
300
VW
Wolfsburg,
Rotestrasse 10
59
Mudguard
500
6
Warszawa,
Mozarta 25
002
400
WSK
Świdnik,
Kraszewskiego 5
54
Carburetor
500
5
Warszawa,
Chopina 3
002
400
WSK
Świdnik,
Kraszewskiego 5
32
Wheel
100
6
Warszawa,
Mozarta 25
003
500
VW
Antoninek,
Słowackiego 2
88
Engine
15
7
Warszawa,
Bacha 3
004
600
FIAT
Bielsko-Biała,
Mickiewicza 25
58
Mudguard
400
6
Warszawa,
Mozarta 25
004
600
FIAT
Bielsko-Biała,
Mickiewicza 25
21
Alternator
50
7
Warszawa,
Bacha 3
005
300
VW
Wolfsburg,
Rotestrasse 10
53
Carburetor
200
5
Warszawa,
Chopina 3
005
300
VW
Wolfsburg,
Rotestrasse 10
57
Crankshaft
30
5
Warszawa,
Chopina 3
006
300
VW
Wolfsburg,
Rotestrasse 10
59
Mudguard
20
6
Warszawa,
Mozarta
3425
Relation ORDERS in the second normal form (2NF)
all attributes fully dependent on primary keys
No_order
IDsupplier
Name_supplier
Address_supplier
IDpart
Name_part
Qty
Warehouse
Diagram of functional
dependencies in the
relation ORDERS
Address_warehouse
35
No_order
IDsupplier
Name_
supplier
Address_Supplier
001
300
VW
Wolfsburg,
Rotestrasse 10
002
400
WSK
Świdnik,
Kraszewskiego 5
003
500
VW
Antoninek,
Słowackiego 2
004
600
FIAT
Bielsko-Biała,
Mickiewicza 25
005
300
VW
Wolfsburg,
Rotestrasse 10
006
300
VW
Wolfsburg,
Rotestrasse 10
SUPPLIER_ON_ORDER
2NF
PARTS_ON_ORDER
No_order
IDpart
Qty
001
53
100
001
57
50
001
59
500
002
54
500
002
32
100
IDpart
Name_part
Warehouse
Address_Warehouse
003
88
15
53
Carburetor
5
Warszawa, Chopina 3
004
58
400
57
Crankshaft
5
Warszawa, Chopina 3
004
21
50
58
Mudguard
6
Warszawa, Mozarta 25
005
53
200
59
Mudguard
6
Warszawa, Mozarta 25
005
57
30
54
Carburetor
5
Warszawa, Chopina 3
006
59
20
32
Wheel
6
Warszawa, Mozarta 25
88
Engine
7
Warszawa, Bacha 3
21
Alternator
7
Warszawa, Bacha 3
PARTS_IN_WAREHOUSE
36
Relation ORDERS in the third normal form (3NF)
remove column not dependent upon primary key
No_order
IDsupplier
Diagram of functional
dependencies in the relation
SUPPLIER_ON_ORDER
Name_supplier
Address_supplier
IDpart
Diagram of functional
dependencies in the relation
PARTS_IN_WAREHOUSE
Name_part
Warehouse
Address_warehouse
No_order
IDpart
Qty
Diagram of functional
dependencies in the relation
PARTS_ON_ORDER
37
ORDER_TO_SUPPLIER
No_order
IDsupplier
001
300
002
400
003
500
004
600
005
300
006
300
3NF
No_order
IDsupplier
SUPPLIERS
IDsupplier
Name_ Supplier
Address_Supplier
300
VW
Wolfsburg, Rotestrasse 10
400
WSK
Świdnik, Kraszewskiego 5
500
VW
Antoninek, Słowackiego 2
600
FIAT
Bielsko-Biała, Mickiewicza 25
IDupplier
Name_supplier
Address_Supplier
WAREHOUSES
Warehouse
Address_Warehouse
5
Warszawa, Chopina 3
6
Warszawa, Mozarta 25
7
Warszawa, Bacha 3
Warehouse
Address_Warehouse
38
PARTS
PARTS_IN_WAREHOUSE
PARTS_ON_ORDER
as above
IDpart
Name_ part
21
Alternator
32
Wheel
53
Carburetor
54
Carburetor
57
Crankshaft
58
Mudguard
59
Mudguard
88
Engine
IDpart
Warehouse
53
5
57
5
58
6
59
6
54
5
32
6
88
7
21
7
3NF
IDpart
Name_part
IDpart
Warehouse
39
Example: ORDERS
40
Structured Query Language - SQL
Why to use SQL in addition to the Design View?
- not all SQL functions can be used from the Design View level
- SQL can be used in other applications (Excel, Word, Visual Basic)
- SQL is a standard query language which can be used outside the
Access program
One can easily switch between Design View and SQL View
SQL is a procedure language which tells what to do, and not
how to do
...
42
Sorting
SELECT PUBLISHERS
...
PubTel
FROM PUBLISHERS
ORDER BY PUBLISHERS
...
PubName AS [Publisher’s Name],
PUBLISHERS
...
PubName;
Publisher’s Name
Publisher’s Phone
Alpha Press
999-999-9999
Big House
123-456-7890
Small House
714-000-0000
44
Filtering
SELECT COUNT (ISBN) AS [How many books from Alpha Press?]
FROM BOOKS
WHERE (PubID=1);
How many books from Alpha Press?
6
SELECT COUNT(ISBN) AS [Number of books],
MIN(Price) AS Min_Price,
MAX(Price) AS Max_Price,
AVG(Price) AS Avg_Price
FROM BOOKS;
Number of books
Min_Price
Max_Price
Avg_Price
15
12
...
00 zł
29
...
Title;
Available books
59
Functional queries
SELECT CUSTOMER!Surname & " " & CUSTOMER!First_name AS Person,
Count([CUST/BOOKS]
...
CustID=[CUST/BOOKS]
...
DateBack
HAVING ((([CUST/BOOKS]
...
It generally serves three purposes:
1) To allow users to perform data entry
...
2) To allow users to enter custom information, and based on that
information perform a task
...
3) To allow users a method of navigating through the system
...
Forms: bound or unbound
A bound form has a RecordSource, a table or query to which the form is
"tied" or "based"
...
66
Creating Forms in Microsoft Access
Remember: there is a large
number of pre-defined forms
...
67
Form Design
68
Creating forms – Form Wizzard
Form facilitating introduction of a new customer to the database:
1) Select the data
source
2) Select the form
fields
69
Creating forms – the layout and style
70
Creating forms – the form title
The form can be
modified in Design
View mode
...
We
don’t want to grant employees full access to view or edit
customer records
...
74
The form after the edition of properties
Opening the form: no earlier
records can be seen
The CustID is automatically
assigned, First_name and
Surname are typed in
...
Access 2007 disables all potentially unsafe code or other
components, regardless of the version of Access that you used
to create the database
...
76
You trust the disabled content:
•Trust the database only for the current session (while the
database is open) Click Options on the Message Bar
...
You don't trust the database:
Ignore the Message Bar
...
77
Trust a database for the current session
1
...
78
2
...
Access downloads the database template
and creates a new database, and the Message Bar appears
...
On the Message Bar, click Options
...
4
...
79
Create a trusted location
Start Office Access 2007 (you do not need to open a database to
complete these steps)
...
Click Trust Center, and then click Trust Center Settings
...
Click Add new location
...
81
The Microsoft Office Trusted Location dialog box appears
...
By default, the folder must reside on a local drive
...
82
Move a database to a trusted location
If you have a database open
Click the Microsoft Office Button
...
In the Save As dialog box, navigate to the trusted location,
and then click Save
...
Open in exclusive
mode
84
Encrypt by using a database password
Open the database (in Exclusive mode) that you want to encrypt
...
The Set Database Password dialog
box appears
...
Open the encrypted database as you open any other
database
...
The Password Required dialog box appears
...
Type your password in the Enter database password box,
and then click OK
...
On the Database Tools tab, in the Database Tools group, click
Decrypt Database
...
When you create an
...
accde file, you can
package the file, apply a digital signature to the package, and then
distribute the signed package to other users
...
Enter a name for the signed
package in the File name box,
and then click Create
...
accdc file
and places it in the location that
you choose
93
Extract and use a signed package
1
...
2
...
accdc) as
the file type
...
Locate the folder that contains your
...
4