Download ASSIGNMENT 1 FRONT SHEET and more Assignments Database Programming 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 3/01/2021 DateReceived1stsubmission Re-submissionDate DateReceived2ndsubmission Student Name Khuc Thanh Long Student ID GCH Class GCH 0902 Assessor name Vuong Thi Nhung 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 Long Grading grid
P1 M1 D
Summative Feedback: ResubmissionFeedback:
Grade: AssessorSignature: Date: InternalVerifier’sComments: Signature&Date:
Introduction:
Scenario:
History: I am an employed as a Database Developer in FPT Software. FPT University is expanding day by day, so many students apply to study. And one of the places that students go to the most is the school's library. Currently FPT University is having a difficult problem that is the school's library management, currently FPT University has 70 classes in which each The class has 20-30 students only in the Hanoi area. The school library system is showing signs of slow loading. Less student storage and some data security related issues. We will develop more databases to store so we can store more data. First, the school library's data management software can control which students borrow books from the school library. In the database of the library management software it is possible to store the name of the student, the student's code, the title of the book borrowed by the student, the name of the author of the book, the book ID. The old library management system is often not equal to the new one, so we should completely replace it with a new system to meet more needs of students when borrowing books at the library. Scope: I hope this inviting database system can better improve the problems of the old system of FPT University library. I can implement several features of this new database system by the library. Objectives:
- Manage who borrow
- Librarian can schedule date borrow and return
- Show Book Borrow Details
- Manage which author of books student borrow
- Manage date return …
User and system requirements:
All the necessary attributes about Students, Librarians, Subjects and most importantly the management of book borrowers
User requirements:
Manage Library System can be use Libririan, Student can watch date return, date borrow and name of books.
- Librarians: manage all information related to books create/edit/remove ( Books ID, Author, Publisher), book brrowing, book return, date borrowing, date return. Librarian can create/edit/remove students’ accounts, view status of borrowed books, status returned books.
- System Admin: can full control database, manage servers, recover system.
- Students: Give infor Students and borrow books and return books.
System requirements:
- Log in ( only Librarians, students )
- Log out (only Librarians, Students)
- Save
- User friendly interfaces
- Performance: very fast, can meet 10000 connections
- Sercurity: Password, data transmission should be secure (HTTPS) ERD :
Business rules/constraint:
Library System:
- Student can borrow 4 books at most at a time.
- Student have to return books within 7 days.
- Each time of extention is 7 days
- If students don’t return the books, they will fined
- Librarians can sent requests about new books have in library, input information related books.
- When students return books, librarian can check students’ informations that person borrow in library system. Check with the student’s card.
- When students come to the library, the librarian takes the student ID card then checks student information and lets students find books and borrow books.
- In the case if students don’t pay fine, librarians will send announcement to trainning department.
➢ Book Author vs Author Relationship: many to one
- Book table: This table contains BOOK ID (pk) ,NAME OF BOOK and PUBLISH DATE each book will have a different ID for students to choose the easiest way.
- Author table: This table contains AUTHOR ID (pk), NAME OF AUTHOR, DATE OF BIRTH and PHONE NUMBER. A author can write one or many books, one books can wrote by one author
- Book author table: This table constains BOOK ID AND AUTHOR ID.
- Book borrow details table: this table constains ISBN (pk), BORROW ID (pk), EXTENDED DATE, NO OF BOOK, STATUS.
- Borrow table: This table contains BORROW ID (pk), DATE OF BORROW, DUE DATE, LIBRARIAN ID. A librarian can lend many books, many books can be borrowed by a librarian
- Librarian table: This table contains LIBRARIAN ID (pk) NAME OF LIBRARIAN, USERNAME (fk).
- Account table: This table contains USERNAME, ROLE, PASSWORD. One account can sign in by a librarian, a librarian can sign in one account
- Return table: This table contains ISBN (pk), DATE OF RETURN, BORROW ID (pk), RETURN STATUS. One student
Data validation and constraint:
Book Attributes Data type Null PK/FK Format/Constraint/Domain value BookID Int identity (1000,1) Not null Primary key B1xxxx ISBN Char (10) Not null Book name Nvarchar (100) Not null Author Attributes Data type Null Pk/Fk Format/Constraint/Domain value AuthorID Int identity (1000,1) Not null Primary key AuthorName Nvarchar (100) Not null DOB Date Yyy/Mmm/Ddd Phone Number Nvarchar (100)
Book borrow details Attributes Data type Null Pk / Fk Format/Constraint/Domain value BorrowID Int identity(1000,1) Not null Primary key Extdate Date Yyy/Mmm/Ddd Nobook Nvarchar(11) Not null Status Nvarchar(100) Not null Borrow book Attributes Data type Null Pk/Fk Format/Constraint/Domain value BorrowID Int identity(1000,1) Not null Primary key Date of Borrow Date Yyy/Mmm/Ddd Return book Attributes Data type Null Pk/Fk Format/Constraint/Domain value ISBN Char (11) Not null Primary key Borrow ID Int identity(1000,1) Not null Date of return Date Yyy/Mmm/Ddd ReturnStatus Nvarchar(1000) Not null Account Attributes Data type Null Pk/Fk Format/Constraint/Domain value Username Char (100) Not null Primary key Role Nvarchar(1000) Not null Password Nvarchar(100) Not null Librarian Attributes Data type Null Pk/Fk Format/Constraint/Domain value LibrarianID Int identity (1000,1) Not null Primary key Name of librarian Nvarchar(100) Not null Username Nvarchar (100) Not null
