Friday, September 12, 2014

Library System Using MS Access Part 1


1
 
(Note: for Educational Purposes)
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.

No comments:

Post a Comment