lOMoARcPSD|53039978
Case study
HND in Computing (ESOFT Metro Campus)
Scan to open on Studocu
Studocu is not sponsored or endorsed by any college or university
Downloaded by Alexine Sevidzem (sevidzemalexine@[Link])
lOMoARcPSD|53039978
Bachelor of Software Engineering
Centre for IT Education (CITES)
Department of Electrical and Computer Engineering
The Open University of Sri Lanka
EEI3266 – Information Systems and Data Management
Case Study Submission
Student Number (S92069545)
Student Name (S.P.I. Hewanayaka)
1|Page
Downloaded by Alexine Sevidzem (sevidzemalexine@[Link])
lOMoARcPSD|53039978
Q1)
Entity Relationship Diagram for Library System
2|Page
Downloaded by Alexine Sevidzem (sevidzemalexine@[Link])
lOMoARcPSD|53039978
Q2)
• Employee – Salary
Job_start_date
ID
• Customer – Name
Address
Phone_number
Date_of_Birth
User_nane
Password
ID
Fines
Q3)
• Customer - (Name, Address, Phone_number, Date_of_birth)
• Card - (ID, User_name, password, fines)
• Book - (ID, ISBN, Title, First_author, Losses_price, Late_price, Status,
Location)
• Branch - (name, Phone_number, address)
• Employee - (ID, Salary, Job_start_date)
Customer
Name Address Phone_Number Date_of_Birth
Card
ID User_Name Password Fines
Book
ID ISBN Title First Author Losses_Price Late_Price Status Location
Branch
Name Address Phone_Number
3|Page
Downloaded by Alexine Sevidzem (sevidzemalexine@[Link])
lOMoARcPSD|53039978
Employee
ID Salary Job_start_date
Q4)
Create database LIBRARY;
Use LIBRARY;
Create table BOOK (
ID int(15),
ISBN varchar(20),
title varchar(50),
location varchar(30),
first_author varchar(25),
status varchar(30),
losses_price int(5),
late_price int(5),
Primary key (ID));
Insert into BOOK values
(000000000000001, 'ISBN0001', 'Madol Duwa', '1st cupboard', 'Martin Wikkramasinghe',
'Available', 100, 10),
(000000000000002, 'ISBN0002', 'Heen Saraya', '1st cupboard', 'Kumarathunga Munidasa',
'Available', 200, 10),
(000000000000003, 'ISBN0003', 'Magul Kama', '1st cupboard', 'Kumarathunga Munidasa',
'Checked Out', 100, 20),
(000000000000004, 'ISBN0015', 'Cloud Computing', '6th cupboard', 'Heshan Witharana',
'Available', 500, 15),
(000000000000005, 'ISBN0024', 'Object Oriented Programming', '5st cupboard', 'Sathya
Edirisinghe', 'Checked out', 300, 50);
4|Page
Downloaded by Alexine Sevidzem (sevidzemalexine@[Link])
lOMoARcPSD|53039978
Create table EMPLOYEE (
ID int(5),
Job_Start_Date date,
Salary int(10),
Primary key (ID));
Insert into Employee values
(00001, '2005/10/11', 30000),
(00002, '2007/12/01', 20000),
(00003, '2015/07/23', 35000),
(00004, '2008/11/08', 25000),
(00005, '2014/03/31', 15000);
Create table BRANCH (
Name varchar(20),
Address varchar(50),
Phone_number varchar(10),
Primary key (Name));
Insert into BRANCH values
('Badulla', 'No4 - main road - Badulla', '0715324569'),
('Anuradhapura', 'No7 - main road - Anuradhapura', '0745324549'),
('Galle', 'No76 - main road - Galle', '0419524569'),
('Mathara', 'No89 - beach road - Mathara', '0415322589'),
('Rathnapura', 'No4 - main road - Rathnapura', '0475324589');
Create table CARD (
ID int(10),
Password varchar(10),
User_name varchar(25),
fines int(10),
Primary key (ID));
5|Page
Downloaded by Alexine Sevidzem (sevidzemalexine@[Link])
lOMoARcPSD|53039978
Insert into CARD values
(00001, 's123', 'S.P.I. Hewanayaka', 20),
(00002, 's234', '[Link]', 10),
(00003, 's345', 'H.V. Kanishka', 50),
(00004, 's456', 'A.S. Kumara', 45),
(00005, 's567', 'H.N.S. Anushka', 30);
Create table CUSTOMER (
Name varchar(25),
Address varchar(100),
Phone_number varchar(10),
Date_of_Birth date,
ID int(10),
foreign key (ID) references CARD (ID));
Insert into CUSTOMER values
('W.L.N. Chathuranga', 'No65 - main road - Embilipitiya', 0712838702, '1997/04/18',
00001 ),
('G.Y.A. Weerasinghe', 'No7 - main road - Badulla', 0712523702, '1991/09/12', 00002),
('R.U.I. Fernando', 'No80 - main road - Anuradhapura', 0711588705, '2005/12/15', 00003),
('Y.P.N. Karunarathna', 'No236 - main road - Mathara', 0712838418, '1984/04/10', 00004),
('S.K.L. Wijesinghe', 'No75 - beach road - Galle', 0712838654, '1977/11/11', 00005);
6|Page
Downloaded by Alexine Sevidzem (sevidzemalexine@[Link])
lOMoARcPSD|53039978
7|Page
Downloaded by Alexine Sevidzem (sevidzemalexine@[Link])
lOMoARcPSD|53039978
8|Page
Downloaded by Alexine Sevidzem (sevidzemalexine@[Link])
lOMoARcPSD|53039978
9|Page
Downloaded by Alexine Sevidzem (sevidzemalexine@[Link])
lOMoARcPSD|53039978
Q5)
1. select status from BOOK Where ID=000000000000003;
2. select * from CARD where ID=00001;
10 | P a g e
Downloaded by Alexine Sevidzem (sevidzemalexine@[Link])
lOMoARcPSD|53039978
3. delete from BOOK where ISBN = 'ISBN0002';
4. update BOOK set status= 'Checked out' where id=000000000000001;
11 | P a g e
Downloaded by Alexine Sevidzem (sevidzemalexine@[Link])
lOMoARcPSD|53039978
5. select fines from CARD where ID=4;
5. select ID, fines from Card;
12 | P a g e
Downloaded by Alexine Sevidzem (sevidzemalexine@[Link])
lOMoARcPSD|53039978
13 | P a g e
Downloaded by Alexine Sevidzem (sevidzemalexine@[Link])