Download assignment 1 database design and development and more Assignments Computer Vision in PDF only on Docsity!
ASSIGNMENT 1 FRONT SHEET
Qualification TEC Level 5 HND Diploma in Computing Unit number and title Unit 04: Database Design & Development Submission date Date Received 1st submission Re-submission Date Date Received 2nd submission Student Name Nguyen Minh Anh Student ID BH Class SE06203 Assessor name Ha Ngoc Linh Student declaration I certify that the assignment submission is entirely my own work and I fully understand the consequences of plagiarism. I understand that making a false declaration is a form of malpractice. Student’s signature Anh Grading grid
P1 M1 D
❒ Summative Feedback: ❒ Resubmission Feedback:
Grade: Assessor Signature: Date: Signature & Date:
TABLE OF FIGURE
Figure 1 The offline library cannot operate due to the epidemic.................................................................................................................................. 6 Figure 2 Information of book ......................................................................................................................................................................................... 8 Figure 3 Information of Staff.......................................................................................................................................................................................... 9 Figure 4 Information of member ................................................................................................................................................................................. 10 Figure 5 Login ............................................................................................................................................................................................................... 11 Figure 6 Information of Borrowing .............................................................................................................................................................................. 12 Figure 7 Entity – Relationship Diagram........................................................................................................................................................................ 14 Figure 8 Login table ...................................................................................................................................................................................................... 19 Figure 9 Member table ................................................................................................................................................................................................ 20 Figure 10 Author table ................................................................................................................................................................................................. 20 Figure 11 Book table .................................................................................................................................................................................................... 20 Figure 12 Staff table ..................................................................................................................................................................................................... 21 Figure 13 Borrow table ................................................................................................................................................................................................ 21
I. Introduction.
In an ever-evolving higher education landscape, universities face many challenges in effectively managing their growing student populations. FPT University - a prominent institution experiencing rapid expansion, found itself grappling with the daunting task of streamlining its administrative processes. To overcome these barriers, FPT University sought the expertise of our major IT consulting firm, recognizing the need for innovative solutions to improve efficiency and ensure management seamless. As a Database Developer in our organization, I have been given the important responsibility of leading the development of several academic systems, one of which is the Online Library system. By harnessing the power of cutting-edge technology and leveraging our
expertise, our goal is to create a powerful and user-friendly platform that revolutionizes the way FPT University manages Manage your vast collection of educational resources. Through this endeavour, we strive to empower both students and faculty with a rich and seamless online library experience, ultimately propelling FPT University towards an efficient and productive future. more advanced technology.
II. Body.
1.User and system requiremen :
1.1.Overview about the problem:
Currently, all schools have libraries but these offline libraries have become outdated in the age of technology, they have many major defects that cause inconvenience to users. First, readers/users have to go to the library at a fixed location, which takes a lot of time. Next, the procedure for searching and borrowing books is also complicated because the library has many readers and a very large number of books. Library management is a difficult problem, it is difficult for managers to know the status of all books and borrowers. In addition, the Covid-19 epidemic is the biggest reason why libraries cannot operate, wasting resources and personnel. Therefore, FPT University needs a database to build an online library system. solve the above problems.
Record all information on the loan slip, its details so that you can double-check if necessary. Know borrower information, repayment date and status of paid books. Each person can only borrow a maximum of 3 books and the repayment period is within 1 month; When returning the book, the book's condition will be checked and noted. 1.2.2. Requirement To make the requirements easy to understand, I will use the User Story format below to describe the requirements. As a library manager, I wanted a system that could perform a number of important actions so that they help me manage the library easily. The most basic operations are entering, looking up complete information of books as well as their status, recording book loan slip with loan details, viewing reader information, can add, edit, delete information of book, last name, see the status of the returned. View statistics of readers' borrowing, paying and interest activities. As a reader, I would like to edit and view personal information along with account password, some information such as loan date, employee name, book title should be clear so as not to be confused about the details this information. 1.2.3. Input/Output The input/output to the system will be the data of information of readers, staffs, and books, etc. ➢ Input (Put data in):
- Add new members to the Member table when member registration is requested.
- Add information about new authors to the Author table when a new author publishes a book.
- Add information about new books to the Book table when new books are added to the library. ➢ Output (Get data out):
- Query information about books in the Book table to display a list of books available in the library.
- Query information about members and books that members have borrowed in the Borrow table to display each member's book borrowing history.
- Query information about employees in the Staff table to display a list of employees working at the library.
- Information of books: Figure 2 Information of book
Figure 4 Information of member
Figure 5 Login
➢ Build tables and relationships: Based on the ERD diagram and normalization rules, create tables and relationships between them. Determine primary keys and foreign keys to link between tables. ➢ Design queries and data manipulations: Determine the queries and data operations needed to meet user requirements. Ensure that these queries and operations are performed efficiently and securely. ➢ Build interfaces and applications: Develop interfaces and applications so that users can interact with the library management system conveniently and easily. Make sure that the interface and application match the user's requirements and needs. ➢ Testing and implementation: Conduct system testing to ensure its correctness and effectiveness. Then deploy the system and put it into practical use.. In terms of entities, it has Login, Member, Staff, Borrow, Author, Book.
2.2. Database design with explanations
Logical design (ERD) of database: Library management system
Figure 7 Entity – Relationship Diagram Relationships between tables in a database system: ➢ The Login table contains the user's login information. Each record in this table has an ID_Login as the primary key for identification and a Username and Password pair for login authentication.
Database normalization is the process of designing and building a database structure so that it complies with normalization principles, in order to eliminate data repetition, minimize unnecessary functional dependencies, and Optimize data storage and retrieval. Here is an example of how to normalize a database for the Login, Member, Author, Book, Staff, Borrow tables: Login:
- username (primary key, data type: Varchar): stores the user's login name.
- password (data type: Varchar): stores the user's password. Member:
- member_id (primary key, data type: Int): stores the member's ID.
- member_name (data type: Varchar): stores the name of the member.
- member_email (data type: Varchar): stores member's email. Author:
- author_id (primary key, data type: Int): stores the author's ID.
- author_name (data type: Varchar): stores the author's name. Book:
- book_id (primary key, data type: Int): stores the ID of the book.
- book_title (data type: Varchar): stores the title of the book.
- author_id (foreign key, data type: Int): binds to the Author table to identify the author of the book. Staff:
- staff_id (primary key, data type: Int): stores the employee's ID.
- staff_name (data type: Varchar): stores the employee's name. Borrow:
- borrow_id (primary key, data type: Int): stores the ID of the book borrowed.
- member_id (foreign key, data type: Int): linked to the Member table to identify members borrowing books.
- book_id (foreign key, data type: Int): binds to the Book table to identify the book being borrowed.
- To be able to standardize data, we must follow three basic forms of normalization: NF1, NF2, and NF3.
- The conditions can be simply understood as follows: ➢ NF1: Create a primary key and ensure that the data is unique ➢ NF2: Satisfy to NF1 and generates subkeys ➢ NF3 : Satisfies NF2 and does not create a transitive relationship between attributes
- Details are as follows:
- NF1 : ➢ The tables: Login; Member; Author; Book; Staff; Borrow all have primary keys and no attributes depend on other attributes. Therefore, NF1 has been completely standardized.
- NF2 : ➢ Every attribute depends on the entire primary key and does not depend on any attribute keys in the table's other attribute groups.
- NF3 : ➢ In this NF3 condition, I have passed the NF3 requirement because my tables do not create transitive relationships between the attributes in the tables.
- This part is also a very important part of the database system for the online book system with 6 tables as created in the design part: Login; Member; Author; Book; Staff; Borrow. With the database relational model, it will help us convert attributes and relationships between them into database tables.
- The requirement of the system is to allow students to search for the book they want by book genre, book title, author as well as by the publisher they want. In addition, the expiration date of the book loan vouchers that students have registered for will be automatically calculated and notified to students. The admin department will have the function to view students' book borrowing history, edit book borrowing history as well as the overdue date of returning books for students. In addition to the above, the admin also has the function of adding, editing, deleting, and updating books.
- From the above, I have to create 6 tables with separate attributes for each table as follows:
- Information is arranged accurately in each table to help reduce redundancy and ensure standardization in the database
- Below are detailed information about the tables and attributes of each table, along with the data types of each attribute: ❖ Login table Figure 8 Login table ❖ Member table
Figure 9 Member table ❖ Author table Figure 10 Author table ❖ Book table Figure 11 Book table
