|
MS ACCESS ACTIVITY 17.1 –
PRIMARY KEY & TABLE RELATIONSHIPS
A LIBRARY SYSTEM
Part 1 – Creating Two Primary
Tables
Instructions:
1.
Lunch MS Access.
2.
Create a new Blank Database and save as <Your Initials>_Library System,
Save the table as Students and set
the StudentID field as the primary key. Refer to the table specification as
found below.
FIELD
NAME
|
DATA
TYPE
|
FIELD
PROPERTIES
|
Format/(Data
for Lookup Wizard)
|
StudentID
|
Autonumber
|
“SN-“0000
|
|
FirstName
|
Text
|
>[Blue]
|
|
LastName
|
Text
|
>[Red]
|
|
City
|
Lookup Wizard
|
Calauag, Lopez, Guinayangan,…
|
|
HomePhone
|
Text
|
Input Mask
|
!(9999) 000-0000
|
YearLevel
|
Lookup Wizard
|
I, II, III, IV
|
|
Section
|
Text
|
St. Bridget to St. John Vianney
|
|
Photo
|
Attachment
|
(Collect ID photos at least 1x1 in)
|
3.
Create a table that will contain the information
for all the books in the library. Save the table as Books.
a.
Use the fields below and set BookID as the primary key.
Field Name
|
Data Type
|
BookID
|
Autonumber
|
Title
|
Text
|
Category
|
Text
|
Available
|
Yes/No
|
Notes
|
Memo
|
b.
Change the Format
of the BookID field to “BK-“0000.
c.
Create a Lookup Wizard for the Category Field. Type the following:
“Computer”, “Health”, “Literature”, “Mathematics”, “Religion”, “Science”.
d.
Place a default value in the Available field so that it will return
the value Yes (check mark) every
time a new book is added.
e.
For now, encode at least fifteen records. You
may use the sample items below.
f.
Save the changes and close the Table.
Part 2 - Creating Junction Table
4.
Create a new Table in Design View that will hold all of the completed Transactions. See
specifications below:
a.
Set the Format
property for DateBorrowed, DueDate,
and DateReturned to Short Date
Field
Name
|
Data
Type
|
BorrowerID
|
Number
|
BookNumber
|
Number
|
DateBorrowed
|
Date/Time
|
DueDate
|
Date/Time
|
DateReturned
|
Date/Time
|
b.
Click DateBorrowed
and add expression to the Default Value
property. Type =Date() and press
Enter. The expression will return the current system date as a default value.
c.
On DueDate’s
Default Value property, type =Date()+7.
d.
Save the Table as Transactions.
e.
If prompted to create a primary key, choose No.
f.
Close the table and save your Database.
Part 3 – Creating Relationships
5.
Open the Relationships
document tab and show the three tables. Add them all to the relationship window.
a.
Drag the StudentID
field from the Students Table into
the BorrowerID field in the
Transaction Table to create the relationship.
b.
Click Enforce
Referential Integrity and check the other two options. Click Create.
c.
To create a many-to-many relationship, Drag the BookID field from the Books table to
the BookNumber field in the Transaction table.
d.
Click Enforce
Referential Integrity and check the other two options. Click Create.
Note: This is an individual
activity, and all Students shall present their individual output.
