Database Design Part 2, Assignments of Database Programming

Download Database Design Part 2 and more Assignments Database Programming in PDF only on Docsity!

Unit 4: Database Design and Development

ASSIGNMENT 2

Full Name: Nguyễn Hoàng Long

Student ID:GCS

Date:15/10/

Class:GCS0805_PPT

Assessor Name: Nguyễn Văn Sơn

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 Nguyễn Hoàng Long Student ID GCS

Class GCS0805 Assessor name Nguyễn Văn Sơn

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

Grading grid

P2 P3 P4 P5 M2 M3 M4 M5 D2 D

ASSIGNMENT 2 BRIEF

Qualification BTEC Level 5 HND Diploma in Computing

Unit number Unit 04: Database Design & Development

Assignment title

Academic Year

Unit Tutor

Issue date Submission date

IV name and date

Submission Format:

Format: This assignment is an Individual assignment and specifically including 2 documents:

(1) sql file of your code and represent your code to your mananger (tutor)

(2) a report document

You must use font Calibri size 12, set number of the pages and use multiple line spacing at

1.3. Margins must be: left: 1.25 cm; right: 1 cm; top: 1 cm and bottom: 1 cm. The reference

follows Harvard referencing system. The recommended word limit is 2.000-2.500 words.

You will not be penalized for exceeding the total word limit. The cover page of the report

has to be the Assignment front sheet 2.

Submission Students are compulsory to submit the assignment in due date and in a way requested by

the Tutors. The form of submission will be a soft copy posted on

http://cms.greenwich.edu.vn/

Note: The Assignment must be your own work, and not copied by or from another student or from

books etc. If you use ideas, quotes or data (such as diagrams) from books, journals or other sources, you

must reference your sources, using the Harvard style. Make sure that you know how to reference

properly, and that understand the guidelines on plagiarism. If you do not, you definitely get fail

Unit Learning Outcomes:

LO2 Develop a fully functional relational database system, based on an existing system design.

LO3 Test the system against user and system requirements.

LO4 Produce technical and user documentation

Assignment Brief and Guidance:

You are employed as a Database Developer for a large IT consultancy company. The company has been

approached by FPT university which is expanding due to the growth of the number of students. FPT is

currently facing difficulties in dealing with managing the university. It decided to develop several

academic systems to manage the university easier including: Online Library system, Student Grading

System, Attendance System, CMS System, Scheduling System, Enrolment Systems, and so on.

You are tasked to select one of those systems to develop database for FPT university. Your tasks are to:

Work with FPT to find out about current requirements for each system

Analyze the requirements and produce clear statements of user and system requirements.

Design a relational database system using appropriate design tools and techniques

Develop a fully functional relational database system, based on an existing system design.

Test the system against user and system requirements.

Produce technical and user documentation

Part 2 (Assignment 2)

Once the designs have been accepted by your manager you have been asked to:

1. Develop the database system using evidence of user interface, output and data validations and

querying across multiple tables.

You want to include more than just the basics so you will implement a fully functional database system

which will include system security and database maintenance features.

You have decided to implement a query language into the relational database system. The developed

system will be demonstrated to your manager.

Your manager has asked you to include in the report:

2. Assessing whether meaningful data has been extracted through the use of query tools to produce

appropriate management information.

3. Evaluating the effectiveness of the database solution in relation to user and system requirements,

and suggest improvements.

4. Once the system has been developed, you will test the system and your manager will complete a

witness statement indicating how your tests are performing against user and system requirements.

You will produce a brief report assessing the effectiveness of the testing, including an explanation of the

choice of test data used.

5. Lastly you will produce technical and user documentation which will be given to the company.

You want to provide some graphical representations for ease of reference in the technical guide, so you

have decided to produce a technical and user documentation for a fully functional system, including

diagrams showing movement of data through the system, and flowcharts describing how the system

works.

 Update……………………………………………………………………………….  Delete……………………………………………………………………………...... P4 Test the system against user and system requirements…………………….. P5 Produce technical and user documentation………………………………...…. Reference………………….……………………………………………………………… ANSWER P2.Develop the database system with evidence of user interface, output and data validations, and querying across multiple tables.

1.Relation Schemas

Borrow BorrowID StudentId StaffID BorrowDay

Staff StaffID StaffName StaffPhone StaffAddress Student StudentID StudentName StudentClass StudentPhone Book BookID BookName AuthorID Price TypeID Status BorrowDetail BorrowID BookID Quantity Status Author AuthorID AuthorName AuthorAddress AuthorBiography Type of book TypeID (^) Book Type Name

2. Create Database called “Library”

create database Library go use Library go

3. Create tables of Library

Create table Student( studentID varchar( 9 ) primary key not null, studentName varchar( 30 ) not null, studentClass varchar( 10 ) not null, studentPhone int not null, studentAddress varchar( 50 ), ); Create table Staff( staffID varchar( 9 ) primary key not null, staffName varchar( 30 ) not null, staffPhone int not null, staffAddress varchar( 50 ), ); Create table Borrow( borrowID varchar( 8 ) primary key not null, studentID varchar( 9 ) not null, staffID varchar( 9 ) not null, borrowDay date not null, returnDay date not null, foreign key (studentID) references Student,

values('AUT73192', 'Joanne Rowling', 'Yate , United of Kingdom', 'Author of the series Harry Potter'); insert into Author(authorID, authorName, authorAddress, authorBiography) values('AUT73191', 'Stephen King', 'USA', 'The King of Horror has more 350M sold worldwide'); insert into Author(authorID, authorName, authorAddress, authorBiography) values('AUT73190', 'Sandra Cisneros', 'Mexico, USA', 'The voice of women of color in the literary flow'); insert into Author(authorID, authorName, authorAddress, authorBiography) values('AUT73189', 'Charles Dickens', 'Portsmouth , United of Kingdom', 'The most famous writer of the Queen Victoria era.'); insert into Author(authorID, authorName, authorAddress, authorBiography) values('AUT73188', 'Eric Arthur Blair', 'United of Kingdome', 'One admired English nibs of the 20th century'); insert into Author(authorID, authorName, authorAddress, authorBiography) values('AUT73187', 'Pham Huy Hoang', 'Viet Nam', 'He is a developer'); insert into Author(authorID, authorName, authorAddress,authorBiography) values('AUT73186', 'Steven Levy', 'America', 'An journalist has written books about IT.'); go

Table:TypeofBook

---------------- Insert data into Type_of_Book table ------------------- insert into Type_of_Book(typeID, bookTypeName) values('TYPE1234', 'Adventure'); insert into Type_of_Book(typeID, bookTypeName) values('TYPE1235', 'Horror'); insert into Type_of_Book(typeID, bookTypeName) values('TYPE1236', 'Science-fiction'); insert into Type_of_Book(typeID, bookTypeName) values('TYPE1237', 'History'); insert into Type_of_Book(typeID, bookTypeName) values('TYPE1238', 'Short story'); insert into Type_of_Book(typeID, bookTypeName) values('TYPE1239', 'Bildungsroman'); insert into Type_of_Book(typeID, bookTypeName) values('TYPE1240', 'Novel'); insert into Type_of_Book(typeID, bookTypeName) values('TYPE1241', 'Political satire'); insert into Type_of_Book(typeID, bookTypeName) values('TYPE1242', 'Dystopian'); insert into Type_of_Book(typeID, bookTypeName) values('TYPE1243', 'Non-fiction');

go

Table:Book

---------------- Insert data into Book table ----------------- insert into Book(bookID, bookName, authorID, typeID, price, [status]) values('BOK12345', 'Harry Potter va hon da phu thuy', 'AUT73192', 'TYPE1234', '40', 'False'); insert into Book(bookID, bookName, authorID, typeID, price, [status]) values('BOK12346', 'Harry Potter va phong chua bi mat', 'AUT73192', 'TYPE1234', '30', 'False'); insert into Book(bookID, bookName, authorID, typeID, price, [status]) values('BOK12347', 'Harry Potter va chiec coc lua', 'AUT73192', 'TYPE1234', '30', 'True'); insert into Book(bookID, bookName, authorID, typeID, price, [status]) values('BOK12348', 'Harry Potter va hoang tu lai', 'AUT73192', 'TYPE1234', '50', 'False'); insert into Book(bookID, bookName, authorID, typeID, price, [status]) values('BOK12349', 'Carrie', 'AUT73191', 'TYPE1235', '55', 'True'); insert into Book(bookID, bookName, authorID, typeID, price, [status]) values('BOK12350', 'The dark tower', 'AUT73191', 'TYPE1235', '60', 'False'); insert into Book(bookID, bookName, authorID, typeID, price, [status]) values('BOK12351', 'The institute', 'AUT73191', 'TYPE1236', '40', 'True'); insert into Book(bookID, bookName, authorID, typeID, price, [status]) values('BOK12352', 'The shining', 'AUT73191', 'TYPE1235', '37', 'True'); insert into Book(bookID, bookName, authorID, typeID, price, [status]) values('BOK12353', 'The house on mange street', 'AUT73190', 'TYPE1237', '20', 'True'); insert into Book(bookID, bookName, authorID, typeID, price, [status]) values('BOK12354', 'Woman Hollering Creek and Other Stories', 'AUT73190', 'TYPE1238', '20', 'True'); insert into Book(bookID, bookName, authorID, typeID, price, [status]) values('BOK12355', 'David Copperfield', 'AUT73189', 'TYPE1239', '30', 'True'); insert into Book(bookID, bookName, authorID, typeID, price, [status]) values('BOK12356', 'The Pickwick Papers', 'AUT73189', 'TYPE1240', '25', 'False'); insert into Book(bookID, bookName, authorID, typeID, price, [status]) values('BOK12357', 'A Tale of Two Cities', 'AUT73189', 'TYPE1237', '15', 'False'); insert into Book(bookID, bookName, authorID, typeID, price, [status]) values('BOK12358', 'Great Expectations', 'AUT73189', 'TYPE1237', '30', 'False'); insert into Book(bookID, bookName, authorID, typeID, price, [status]) values('BOK12359', 'Animal Farm', 'AUT73188', 'TYPE1241', '25', 'True'); insert into Book(bookID, bookName, authorID, typeID, price, [status]) values('BOK12360', 'Nineteen Eighty-Four', 'AUT73188', 'TYPE1242', '33', 'False'); insert into Book(bookID, bookName, authorID, typeID, price, [status]) values('BOK12361', 'Homage to Catalonia', 'AUT73188', 'TYPE1243', '22', 'True'); go

insert into borrowDetail(borrowID, bookID, quantity, [status]) values('BOR98758', 'BOK12357', '1', 'False'); insert into borrowDetail(borrowID, bookID, quantity, [status]) values('BOR98757', 'BOK12351', '1', 'True'); insert into borrowDetail(borrowID, bookID, quantity, [status]) values('BOR98756', 'BOK12360', '1', 'False'); insert into borrowDetail(borrowID, bookID, quantity, [status]) values('BOR98755', 'BOK12358', '1', 'False'); go

Table:Student

---------------- Insert data into Student table --------------------- insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GCS091979', 'Nguyen Ngo Minh Tien','GCS0805','0945354419','128/24 Cau Kinh , kp4'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GCS091978', 'Le Van Sang','GCS0801','0919785423','65/85 Nguyen Trai'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GCS091977', 'Nguyen Tuan Anh', 'GCS0804','0919919378','23/94 Tran Hung Dao'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GCS091976', 'Le Quang Ngoc', 'GCS0802', '0919919377','12/74 Aqours'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GCS091975', 'Dinh Thi Van Tho', 'GCS0805', '0919919379', 'Duong so 1,quan binh tan'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GCS091974', 'Nguyen Thi Thuy Trang', 'GCS0801', '0966003678', 'Tran Van Giau, quan binh tan'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GCS091973', 'Nguyen Thi Thu Oanh', 'GCS0804', '0966003679', 'Binh Chanh'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GCS091972', 'Quach Dieu Huong', 'GCS0805', '0966003680', 'Ha Noi'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GCS091971', 'Dinh Nhu Thanh', 'GCS0805', '0945354420', 'Duong so 1, quan binh tan'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GCS091970', 'Ho Nguyen Nguyen', 'GCS0805', '0933006489', '5/10 Cau Kinh'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GBS084569', 'Nguyen Hong Dan', 'GBS0605', '0951753491', '95/75 Nijisaki'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GBS084568', 'Phan Thanh Dat', 'GBS0604', '0951753490', '65/91 Aqours'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GBS084567', 'Nguyen Quoc Viet', 'GBS0603', '0954369728', '128/2c Cau Kinh'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GBS084566', 'Tran Nguyen Tap', 'GBS0601', '0954369722', '258/12/64/95 Saint snow'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GBS084565', 'Nghiem Thi Huong', 'GBS0603', '0954369720', '213/46 Azalea'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GBS084564', 'Nguyen Dat Thinh', 'GBS0602', '0934587291', '74/12/5/9 Printempt'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GBS084563', 'Truong Quang Duy Thinh', 'GBS0601', '0911234584', '12/23/34/45/ BiBi'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GBS084562', 'Huynh Thanh Hiep', 'GBS0602', '0258741369', '98/87/76/54/2 Han Mac Tu'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GBS084561', 'Dang Dong Vi', 'GBS0604', '0258741368', '1234/5678 Nguyen Hue');

insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GBS084560', 'Nguyen Anh Thu', 'GBS0605', '0258746980', '25/52 Lily white'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GDS073687', 'Kotori Minami', 'GDS0404', '0120919960', '12/9 Printemp'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GDS073686', 'Honoka Kousaka', 'GDS0404', '0030819960', '3/8 Printemp'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GDS073685', 'Umi Sonoda', 'GDS0404', '0150319960', '15/3 Lily white'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GDS073684', 'Maki Nishikino', 'GDS0402', '0190419960', '19/4 Bibi'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GDS073683', 'Rin Hoshizora', 'GDS0402', '0021119950', '2/11 Lily white'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GDS073682', 'Hanayo Koizumi', 'GDS0402', '0150119960', '15/1 Printemp'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GDS073681', 'Eli Ayase', 'GDS0405', '0281019950', '28/10 BiBi'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GDS073680', 'Nico Yazawa', 'GDS0405', '0160719950', '16/7 BiBi'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GDS073679', 'Nozomi Tojo', 'GDS0405', '0090619950', '9/6 Lily white'); insert into Student(studentID, studentName, studentClass, studentPhone, studentAddress) values ('GDS073678', 'Hanamaru Kunikida', 'GDS0401', '0120032001', '12/03 CYaron'); go

Table:Staff

---------------- Insert data into Staff table ---------------- insert into Staff(staffID, staffName, staffPhone, staffAddress) values ('NIJ123987', 'Setsuna Yuki', '0951753640', '546/9 Fuji'); insert into Staff(staffID, staffName, staffPhone, staffAddress) values ('NIJ123986', 'Kasumi Nakasu', '0951753641', '545/10 Fuji'); insert into Staff(staffID, staffName, staffPhone, staffAddress) values ('NIJ123985', 'Ayumu Uehara', '0951753642', '544/11 Fuji'); insert into Staff(staffID, staffName, staffPhone, staffAddress) values ('NIJ123984', 'Shizuku Ousaka', '0951753643', '543/12 Fuji'); insert into Staff(staffID, staffName, staffPhone, staffAddress) values ('NIJ123983', 'Karin Asaka', '0951753642', '542/13 Fuji'); insert into Staff(staffID, staffName, staffPhone, staffAddress) values ('NIJ123982', 'Ai Miyashita', '0951753641', '541/14 Fuji'); insert into Staff(staffID, staffName, staffPhone, staffAddress) values ('NIJ123981', 'Kanata Konoe', '0951753640', '540/15 Fuji'); insert into Staff(staffID, staffName, staffPhone, staffAddress) values ('NIJ123980', 'Emma Verde', '0951753639', '539/16 Fuji'); insert into Staff(staffID, staffName, staffPhone, staffAddress) values ('NIJ123979', 'Rinna Tennouji', '0951753638', '538/16 Fuji'); insert into Staff(staffID, staffName, staffPhone, staffAddress) values ('QUE123456', 'Lanzhu Zhong', '0987654321', '123/45 Hong Kong'); go

4/ Database Diagram

2/ Update

-To fix incorrect information , I use update query to update that information

update Student SET studentName='Long' Where studentID='GBS084560'

Before:

After: 3/Delete

-For information that doesn’t matter or exist, I use delete query to delete that information.

Delete Staff Where StaffName='Rinna Tennouji' Before:

After: P4 Test the system against user and system requirements.

No

Test

case

Input data Expected

output

Actual output Result

1 Select

all

inform

ation

of

Studen

t

Select

*from

Student

It will

show full

informatio

n of all

Student

Pass

2 Select

all

inform

ation

Select

*from

Staff

It will

show full

informatio

n of all

Pass

Detail borrowed

and status

**P5 Produce technical and user documentation ---Showbook Procedure--- Create procedure Showbook AS Begin select from Book End ---ShowStudent Procedure--- Create procedure ShowStudent AS Begin select from Student End ---Show Book Fee Procedure--- Create procedure Fee AS Begin select Book.BookName, Book.Price from Book, borrowDetail where borrowDetail.bookID = Book.bookID; End ---Show Borrowed Detail Procedure--- Create procedure ShowBorrowInformationOf

AS Begin select Borrow.BorrowID, Borrow.borrowDay, Student.StudentName, Staff.StaffName, Book.BookName, Book.Price, borrowDetail.quantity from Borrow, Student, Staff, Book, borrowDetail where Borrow.borrowID = borrowDetail.borrowID and borrow.studentID = Student.studentID and Borrow.staffID = Staff.staffID and borrowDetail.bookID = Book.bookID; End