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.