Database Design Report - Assessment 1, Basic Library System Analysis

This report analyzes the database design for a basic library system, addressing business rules and assumptions, including the relationships between books, authors, publishers, borrowers, and copies. It includes an Entity-Relationship Diagram (ERD) and a data dictionary detailing table attributes such as BookTitle, ISBN, Title, and PublicationDate, along with their data types, lengths, and ranges. The report also covers backup and recovery procedures, outlining backup schedules, different backup types (consistent, inconsistent, full, and partial), and database restoration processes. References include sources on SQL Server database normalization and ERD tutorials. This report is a comprehensive overview of database design principles and practical considerations for data management within a library context, providing a solid foundation for understanding database structures and operational procedures.

Assignment 1
May 17
2019
Student ID:
Student Name:

Report
Business Rules and Assumption
1. A book can have any number of authors.
2. There will be only one publisher for a book.
3. The complete detail of publishers is necessary to be stored into the database.
4. A book can have any number of copies.
5. A borrower can loan any number of books.
6. A borrower can reserve only one book at a time.
7. The start date and end date of reservations is necessary to be stored into the database.
8. The borrowing data and due date of loaned book is necessary to be stored into the
database.

ER Diagram
(Cinergix Pty Ltd. 2011)
(B. Peter n.d.)

Data Dictionary
Table Attributes Alias Descript
ion
Data
Sour
ce
Data
Struct
ure
Type Leng
th
and
Form
at
Range
BookTi
tle
ISBN ISBN ISBN
code of
the book.
It is the
primary
key of
the
BookTitl
e table.
Book Intege
r
1111
1
11111111
11-
21474836
471
Title Title It is the
title of
the book.
Book Varch
ar
50
xxxx
x
Publication
Date
Publication
Date
It is the
publicati
on date
of the
book.
Book dateti
me
’01-
01-
2019’
“01-01-
1000” to
“31-12-
9999”
Backup and Recovery Procedures
Backup Schedule
Backup schedule is one of the best methods of database backup software. It removes the process of
manual backup. The software schedules the backup of database. The backup software provides different
schemes to take backups and the user can select any one of them e.g. take backup after 12 hours, 6
hours etc.
This is best way to take database backup because manual backup is not a good choice because
sometimes the user may forget.

Backup Type
The backup helps in restoring the data in case of data loss. There are different types of backups –
1. Consistent backup
2. Inconsistent backup
3. Full backup
4. Partial backup
Consistent Backup
In consistent backup process the complete data of the file is taken for backup from the same
point of time. In this all read/write and control files are taken for backup from the same system
change number.
Inconsistent Backup
In inconsistent backup process the complete data of the file is taken for backup from the point
where the system was open to the shutdown of the system.
Full Backup
In this process whole database files are backed up like read/write files along with control files.
Consistent backup is used in full database.
Partial Backup
In this process whole database files are not backed up. Only changed files are backed up.
There are different backup processes available in the backup software’s. The above mentioned
backup procedures are usually available in all. (Oracle 2019)
Backup Restore Process
Database restoration is a process to restore the whole database or the database to a specified
point. There are different restoring processes basically it depends upon the cause of failure of the
database. There are different ways to restore database-
1. The user can restore the whole database.
2. The user can restore only faulted files.

References
B. Peter (n.d.), SQL SERVER Database Normalization Basics for Developers, [Online].
Available: http://www.nullskull.com/a/1629/sql-server-database-normalization-basics-for-
developers.aspx. [Accessed: 18-May-2019]
Cinergix Pty Ltd. (2011), Ultimate Guide to ER Diagrams, [Online]. Available:
http://creately.com/blog/diagrams/er-diagrams-tutorial/. [Accessed: 18-May-2019]
Oracle (2019), Database Backup and Recovery Basics, [Online]. Available:
https://docs.oracle.com/cd/B19306_01/backup.102/b14192/intro001.htm. [Accessed: 18-May-
2019]