ASSIGNMENT [1] ON [DATABASE FOR DATA SCIENCE]

Name Deadline

[Abass NDIAYE] [Date, Time]

March 23, 2025 2024-2025

Lecturer: [Leila Zahhafi]

The objective of this assignment is to design a database schema for the AIMS
Library Management System (ALMS). This system should support key library oper-
ations, including book management, borrowing activities, user records, and research
materials.
Our task is to:

• Provide a data dictionary explaining all database tables, attributes, and data
types.

• List all tables and clearly define the relationships between them.

• Explain the role of each table in the system.

Data Dictionary
Table: Users
This table stores information about all library members, including students, lecturers
and staff.

Attribute Data Type Description Constraints

Unique ID PRIMARY KEY,
user id INT
for each user AUTO INCREMENT
Full name of
name VARCHAR(100) NOT NULL
the user
ENUM(’student’,
ENARAR(100) User’s email
email UNIQUE of user
’teacher’, ’staff’, address
’visitor’)
type NOT NULL
User’s
registration date DATE registration NOT NULL
date
Membership
expiration date DATE expiration
date

[Link] 1418 Mbour-Thies, phone (+221) 33 956 7693, [Link] Page 1 of 5

Table: Books
This table contains details of all books and their availability status.

Attribute Data Type Description Constraints

Unique ID PRIMARY KEY,
book id INT
for each book AUTO INCREMENT
Title of the
title VARCHAR(255) NOT NULL
book
Author of the
author VARCHAR(100) NOT NULL
book
Year of
publication year YEAR
publication
ENUM(’physical’,’ Format of the
format NOT NULL
’digital’) book
ENUM(’available’,
’bvailability
availability NOT NULL
’borrowed’,
status

Table: Borrowings
This table manages book borrowings.

Attribute Data Type Description Constraints

Unique ID
PRIMARY KEY,
borrowing id INT for each
AUTO INCREMENT
borrowing
ID of the user
who FOREIGN KEY
user id INT
borrowed the ([Link] id)
book
ID of the
FOREIGN KEY
book id INT borrowed
([Link] id)
book
Date of
borrowing date DATE NOT NULL
borrowing
Due date for
due date DATE
return
Actual return
return date DATE
date

[Link] 1418 Mbour-Thies, phone (+221) 33 956 7693, [Link] Page 2 of 5

Table: Fines
This table manages fines for late returns.

Attribute Data Type Description Constraints

Unique ID PRIMARY KEY,
fine id INT
for each fine AUTO INCREMENT
ID of the user FOREIGN KEY
user id INT
with the fine ([Link] id)
DECIMAL(10, Amount of
amount NOT NULL
2) the fine
ENUM(’paid’, Status of the
status NOT NULL
’unpaid’) fine

Table: Staff
This table contains staff information.

Attribute Data Type Description Constraints

Unique ID
PRIMARY KEY,
staff id INT for each staff
AUTO INCREMENT
member
Full name of
name VARCHAR(100) the staff NOT NULL
member
Role of the
role VARCHAR(100) NOT NULL
staff member
Work
shift VARCHAR(50)
schedule

Table: Events
This table manages events organized by the library.

Attribute Data Type

Description Constraints
Unique ID PRIMARY KEY,
event id INT
for each event AUTO INCREMENT
Title of the
title VARCHAR(255) NOT NULL
event
Date of the
event date DATE NOT NULL
event
Description
description TEXT
of the event

[Link] 1418 Mbour-Thies, phone (+221) 33 956 7693, [Link] Page 3 of 5

Table: Participants
This table manages user participation in events.

Attribute Data Type Description Constraints

Unique ID
PRIMARY KEY,
participation id INT for each
AUTO INCREMENT
participation
ID of the
FOREIGN KEY
user id INT participating
([Link] id)
user
ID of the FOREIGN KEY
event id INT
event ([Link] id)

Database Tables and Relationships

Schema Entity-Relationship

Relational Schema
• Users (user-id,name,email,type,registration-date,expiration-date)

• Books (book-id,title,author,publication-year,format,availability)

• Borrowings (borrowing-id,user-id (FK),book-id (FK),borrowing-date,due-date,return-

date)

[Link] 1418 Mbour-Thies, phone (+221) 33 956 7693, [Link] Page 4 of 5

 • Fines (fine-id,user-id (FK),amount,status)

• Staff (staff-id,name,role,shift)

• Events (event-id,title,event-date,description)

• Participants (participation-id,user-id (FK),event-id (FK))

NB: FK means Foreign Key

[Link] 1418 Mbour-Thies, phone (+221) 33 956 7693, [Link] Page 5 of 5