Download Database Design and Development - Assignment 2 and more Papers Computer Programming in PDF only on Docsity!
ASSIGNMENT 2 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 Hồ Ngọc Khánh Student ID GCS
Class GCD0901 Assessor name Phan Thanh Trà
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 Khanh
Grading grid
P2 P3 P4 P5 M2 M3 M4 M5 D2 D
Summative Feedback: Resubmission Feedback:
Grade: Assessor Signature: Date:
Signature & Date:
- A. Develop the database system
- I. Final Mock-up of the application.......................................................................................................................
- II. Data validation
- III. Queries to create database with results
- B. Produce queries.....................................................................................................................................................
- I. Queries to INSERT data with illustrations of final result
- II. Queries to UPDATE data with illustrations of final result
- III. Queries to DELETE data with illustrations of final result
- IV. Queries to SELECT data with illustrations of final result
- V. Advanced queries: Stored procedures, triggers, functions
- VI. Evaluate the effectiveness of the database solution
- VII. Produce technical and user documentation.
- Technical Document
- User Document............................................................................................................................................
- C. Test the system
- I. Test cases.........................................................................................................................................................
- II. Flowchart to show how the system works
- Figure 1: FINAL USER INTERFACE FIGURES AND TABLES
- Figure 2: SUCCESSFUL OPERATION
- Figure 3: FAILED OPERATION
- Figure 4: NOT NULL ATTRIBUTE AUTHORID
- Figure 5: UNIQUE ATTRIBUTE PHONENUMBER
- Figure 6: IDENTITY ATTRIBUTE BOOKID
- Figure 7: TABLES CREATED
- Figure 8: DATABASE DIAGRAM
- Figure 9: TABLE AUTHORS
- Figure 10: TABLE DEPARTMENTS
- Figure 11: TABLE BORROWERS
- Figure 12: TABLE BOOKS_INFO
- Figure 13: TABLE RECORDS
- Figure 14: BEFORE UPDATING BORROWDATE
- Figure 15: AFTER UPDATING BORROWDATE
- Figure 16: BEFORE UPDATING MIDDLENAME
- Figure 17: AFTER UPDATING MIDDLENAME
- Figure 18: BEFORE UPDATING BOOKTITLE AND PUBLISHYEAR
- Figure 19: AFTER UPDATING BOOKTITLE AND PUBLISHYEAR
- Figure 20: BEFORE DELETING AUTHORID
- Figure 21: AFTER DELETING AUTHORID
- Figure 22: BEFORE DELETING DEPARTMENTID
- Figure 23: AFTER DELETING DEPARTMENTID
- Figure 24: BEFORE DELETING BOOKID
- Figure 25: AFTER DELETING BOOKID
- Figure 26: TABLE BOOKS_INFORMATION
- Figure 27: GETTING RECORDS GO OFF DUE DATE
- Figure 28: GETTING RECORDS MADE IN THE LAST 7 DAYS
- Figure 29:PROCEDURE usp_GetBookRecord RESULT
- Figure 30:PROCEDURE usp_GetDateRecords RESULT
- Figure 31:PROCEDURE usp_GetDueDateRecords RESULT
- Figure 32: ERD
- Figure 33: MOCKUP INTERFACE
- Figure 34: INFORMATION INPUT AREA
- Figure 35: OPERATORS AREA
- Figure 36: STATISTICAL ACTIONS AREA
- Figure 37: RESULT TABLE AREA
- Figure 38: SCREENSHOT #1
- Figure 39: SCREENSHOT #2
- Figure 40: SCREENSHOT #3
- Figure 41: SCREENSHOT #4
- Figure 42: SCREENSHOT #5
- Figure 43: SCREENSHOT #6
- Figure 44: SCREENSHOT #7
- Figure 45: SCREENSHOT #8
- Figure 46: SCREENSHOT #9
- Figure 47: SCREENSHOT #10
- Figure 48: SCREENSHOT #11
- Figure 49: FLOWCHART OF WORKING SYSTEM
- Table 1: TEST FUNCTIONS
- Table 2: TEST CASES
After executing a query or clicking on any kind of operators, there will always be two result which
are success and failure. Below are the notification that will pop up if the operation is done executing.
Figure 2 : SUCCESSFUL OPERATION Figure 3 : FAILED OPERATION
II. Data validation
o INT: this is the data type I used for identifying BookID, AuthorID, DepartmentID or
BorrowerID. Its size can range from - 2147483648 to 2147483647 or 0 to 16777215 if
it is unsigned range. The size option sets the maximum width of the display.
o VARCHAR(size): this is the most common data type in this database. Size defines the
maximum column length in characters, which can range from 0 to 65535.
o DATETIME: this is a data type that I used in my Records table for BorrowDate and
DueDate. It is a combination of date and time. The format to enter in data is ‘YYYY-
MM-DD hh:mm:ss’
- Presence: All of my attributes are set to NOT NULL because they are needed for further
actions or explanation.
Figure 4 : NOT NULL ATTRIBUTE AUTHORID
- Unique: this constraint will show that the attribute only carries unique value Figure 5 : UNIQUE ATTRIBUTE PHONENUMBER
- Identity: this is the kind of data type whose values will automatically increase by 1
PublishYear int NOT NULL, AuthorID int NOT NULL FOREIGN KEY REFERENCES Authors(AuthorID), DepartmentID int NOT NULL FOREIGN KEY REFERENCES Departments(DepartmentID) ); CREATE TABLE Borrowers ( BorrowerID int NOT NULL PRIMARY KEY, FirstName varchar( 50 ) NOT NULL, MiddleName varchar( 50 ) NOT NULL, LastName varchar( 50 ) NOT NULL, PhoneNumber varchar( 50 ) NOT NULL UNIQUE ); CREATE TABLE Records ( BorrowerID int NOT NULL FOREIGN KEY REFERENCES Borrowers(BorrowerID), BookID int NOT NULL FOREIGN KEY REFERENCES Books_Info(BookID), BorrowDate datetime NOT NULL, DueDate datetime NOT NULL PRIMARY KEY (BorrowerID, BookID) );
TABLES CREATED: There is a table named Books_Information , this table is not hugely different from
the Books_Info but instead of DepartmentID and AuthorID, both of them will be replaced into the real
name so this table is easier to work further, for example with the statistical actions.
Figure 7 : TABLES CREATED
DATABASE DIAGRAM: This is the final database diagram, which will help us easier to code for the
application.
Figure 8 : DATABASE DIAGRAM
B. Produce queries
I. Queries to INSERT data with illustrations of final result
Code snippet for inserting data into tables:
INSERT INTO dbo.Departments VALUES ( 1 ,'Novel'), ( 2 ,'Information Technology'), ( 3 ,'Marketing'); INSERT INTO dbo.Authors VALUES ( 1 ,'Kenneth Grahame'), ( 2 ,'Ernest Hemingway'), ( 3 ,'Andrew Hunt'), ( 4 ,'Robert C. Martin'), ( 5 ,'Jack Trout'); INSERT INTO dbo.Books_Info VALUES ( 1 ,'The Wind in the Willows', 1908 , 1 , 1 ), ( 2 ,'Khac biet hay la chet', 2009 , 5 , 3 ), ( 3 ,'The Clean Coder', 2014 , 4 , 2 ), ( 4 ,'The Old Man and the Sea', 1952 , 2 , 1 ), ( 5 ,'The Pragmatic Programmer', 1999 , 3 , 2 ); INSERT INTO dbo.Borrowers VALUES ( 1 ,'Nguyen','Van','A','01587696456'),
Figure 12 : TABLE BOOKS_INFO Figure 13 : TABLE RECORDS
II. Queries to UPDATE data with illustrations of final result
All of the update queries in my application mostly require at least an input of information to start
with. Here I will show my update queries with the primary key input to update information. In addition,
the condition at WHERE can be any column with any value we want it to be.
- UPDATE table Records UPDATE Records SET BorrowDate = '2021- 06 - 16' WHERE BorrowerID = 3 ;
Here is the small example of updating BorrowDate with BorrowerID:
Figure 14 : BEFORE UPDATING BORROWDATE Figure 15 : AFTER UPDATING BORROWDATE
- UPDATE table Borrowers UPDATE Borrowers SET MiddleName = 'Ngoc' WHERE BorrowerID = 2 ;
Below is the example to show the glow up of table Borrowers after updating MiddleName
Figure 16 : BEFORE UPDATING MIDDLENAME
Figure 20 : BEFORE DELETING AUTHORID Figure 21 : AFTER DELETING AUTHORID
- DELETE table Authors DELETE FROM Departments WHERE DepartmentID = 4 ; Figure 22 : BEFORE DELETING DEPARTMENTID Figure 23 : AFTER DELETING DEPARTMENTID
- DELETE table Records DELETE FROM Records WHERE BookID = 2 ; Figure 24 : BEFORE DELETING BOOKID Figure 25 : AFTER DELETING BOOKID
IV. Queries to SELECT data with illustrations of final result
As I mentioned earlier, I had an extra table called Books_Information and it is a fuller version of the
table Books_Info. Hence, this table is created by columns from the older tables, and this is the code
snippet.
SELECT bi.BookID, bi.BookTitle, bi.PublishYear, d.DepartmentName, a.AuthorName INTO Books_Information FROM Books_Info AS bi, Departments AS d, Authors AS a WHERE d.DepartmentID = bi.DepartmentID AND a.AuthorID = bi.AuthorID; Figure 26 : TABLE BOOKS_INFORMATION
FROM Records AS r JOIN Books_Info AS bi ON r.BookID = bi.BookID WHERE @BookName = bi.BookTitle;
For instance, I went ahead and tried to see how many times the book “The Clean Coder” has been
borrowed. The good thing about it is we can change the book title as we go.
EXEC usp_GetBookRecord 'The Clean Coder'; Figure 29 :PROCEDURE usp_GetBookRecord RESULT
- The fourth statistical action is getting all records from a specific borrow date CREATE PROC usp_GetDateRecords (@date datetime) AS SELECT b.BorrowerID, b.FirstName, b.MiddleName, b.LastName, b.PhoneNumber, bi.BookID, bi.BookTitle, bi.PublishYear, bi.DepartmentName, bi.AuthorName, r.BorrowDate, r.DueDate FROM Records AS r JOIN Borrowers AS b ON r.BorrowerID = b.BorrowerID JOIN Books_Information AS bi ON r.BookID = bi.BookID WHERE @date = r.BorrowDate;
For instance, I tried to look for records made in 26- 06 - 2021 and there it is
EXEC usp_GetDateRecords '2021- 06 - 20'; Figure 30 :PROCEDURE usp_GetDateRecords RESULT
- The last statistical action is sort of similar to the fourth because this one search for due date CREATE PROC usp_GetDueDateRecords (@date datetime) AS SELECT b.BorrowerID, b.FirstName, b.MiddleName, b.LastName, b.PhoneNumber, bi.BookID, bi.BookTitle, bi.PublishYear, bi.DepartmentName, bi.AuthorName, r.BorrowDate, r.DueDate FROM Records AS r JOIN Borrowers AS b ON r.BorrowerID = b.BorrowerID JOIN Books_Information AS bi ON r.BookID = bi.BookID WHERE @date = r.DueDate;
For example, I searched for records that would go off at 26- 06 - 2021 and this is the result
EXEC usp_GetDueDateRecords '2021- 06 - 26';
Figure 31 :PROCEDURE usp_GetDueDateRecords RESULT
VI. Evaluate the effectiveness of the database solution
In data management, confidentiality and dependability are the first essential item. And the database
is designed to avoid ambiguous data access and to avoid relationships and security. A platform is
constructed, formed and maintained to create, modify and consume information conveniently. The data
is updated on an ongoing and unrepeated basis. The use of the database helps to generate more skilled
products, organized management and easy management.
The software will flexibly modify the database's size and complexity. The database is currently
connected to five interconnected tables. The database may be increased to 10 tables, or 100 tables based
on the demands to produce books and handle pupils borrowed.
VII. Produce technical and user documentation.
1. Technical Document
This document presents the standards declaration for library data management. Greenwich
University, like many other university systems, has a library. The requirement for a data management
system for libraries in order to optimize tasks.
As a result, the Database Library System is intended to ease library activities in online, such as
creating a new borrower, distributing books to borrowers, and keeping a list of all the items accessible in
the collection. This also benefits libraries by supplying information.
User requirements:
o Each of every book will have its own ID to indicate its location in the database. Also,
the BookID will also come along with author who wrote it, the department it belongs
to, book title and publish year.
