Lab Assignment 6: Designing a Relational Database
Objective: Help students design a relational database with
multiple tables, establish relationships between them, and populate the tables
with sample data.
Step 1: Create a New
Database
·
Open Microsoft Access
and create a new database file.
·
Save the database with
an appropriate name (e.g., "LibrarySystemDB").
Step 2: Define the
Tables
·
In the newly created
database, define multiple tables related to an imaginary library system. For
example:
·
Table 1: Books
·
Fields: BookID
(AutoNumber, Primary Key), Title, AuthorID, ISBN, Genre, PublishedYear,
CopiesAvailable
·
Table 2: Authors
·
Fields: AuthorID
(AutoNumber, Primary Key), AuthorName, BirthYear
·
Table 3: Borrowers
·
Fields: BorrowerID
(AutoNumber, Primary Key), BorrowerName, Email, MemberSince
·
Table 4: Transactions
·
Fields: TransactionID
(AutoNumber, Primary Key), BookID, BorrowerID, TransactionDate, DueDate,
ReturnDate
Step 3: Establish
Relationships
·
Set up relationships
between these tables to ensure referential integrity. In the "Database
Tools" tab, click on "Relationships."
·
Add the Books and
Authors tables to the Relationship window and connect them using AuthorID as
the related field.
·
Add the Transactions and
Books tables, connecting them using BookID as the related field.
·
Add the Transactions and
Borrowers tables, connecting them using BorrowerID as the related field.
·
Enforce referential
integrity and cascade updates and deletes where necessary.
Step 4: Populate the
Tables
·
Input sample data into
each table to demonstrate the relationships. For example:
·
In the Authors table, add
information about different authors.
·
In the Books table,
include information about various books, specifying the AuthorID.
·
In the Borrowers table,
add details about library members.
·
In the Transactions
table, record transactions of books being borrowed by specific borrowers.
Step 5: Create a Simple
Query
·
To showcase how the
relational database works, create a query that displays the names of authors
and the titles of books they have written. Use the "Query Design"
tool to build this query.
Step 6: Run the Query
·
Execute the query to
display the results, showing the connection between authors and their books.
Step 7: Prepare a
Documentation
·
Document the structure
of the database, including the tables, fields, and relationships established.
·
Explain the purpose and
use case of the database (e.g., a library system).
·
Provide sample queries
and demonstrate how to retrieve meaningful information using these
relationships.
·
Explain the importance
of a well-designed relational database.
Step 8: Presentation
(Optional)
·
Have students present
their designed database to the class, explaining the choices they made and how
the relationships work to achieve a specific purpose.
This assignment helps students understand how to
design a relational database, set up relationships, and work with multiple
tables. It also reinforces the concept of referential integrity, which is
crucial for maintaining data consistency in a database system. Students will
gain practical experience in designing databases for real-world applications.