Library Database System: Normalization, ER Diagram, and Implementation

This document presents a comprehensive solution to a library database system assignment. It begins with an overview of the system, including a table of contents. The solution details the normalization process, progressing from the 1st to the 3rd normal form, optimizing the database structure. An ER diagram visually represents the entities and relationships within the database, such as books, CDs, members, and registers. A data dictionary provides detailed information on each table's attributes, including data types and constraints. The core of the assignment involves the database implementation, with SQL scripts to create tables for items, books, CDs, members, and registers. The document also includes SQL INSERT statements to populate the tables with sample data, and SQL SELECT statements to display the data, demonstrating the functionality of the database. The solution is well-structured and addresses all requirements of the assignment brief.

Running head LIBRARY DATABASE SYSTEM
Library Database System
Name of the student:
Name of the University:
Author note:

1
LIBRARY DATABASE SYSTEM
2.1 Table of Contents
2.2 Normalization.......................................................................................................................2
2.3 ER Diagram..........................................................................................................................3
2.4 Data Dictionary....................................................................................................................3
2.5 Database Implementation.....................................................................................................5

2
LIBRARY DATABASE SYSTEM
2.2 Normalization
1st Normal form
ItemID CategoryType Name Price Author ISBN MemberID Name Phone Address
2nd Normal form
BookID CategoryType Name Price Author MemberID Name Phone
CdID CategoryType Title Price Type MemberID Name Phone
3rd Normal Form
Item Table
Category_num Type
Book Table
BookID CategoryID Name Price Author ISBN
CD Table
CdID CategoryID Title Price Type Time
Member
MemberID Name Phone Address

3
LIBRARY DATABASE SYSTEM
Registers
RegID MemberID TypeID CategoryID Borrow_date Return_date
2.3 ER Diagram
2.4 Data Dictionary
Item Table
TYPE VARCHAR2(20)
CATEGORY NUMBER
Book Table
BOOKID NUMBER

4
LIBRARY DATABASE SYSTEM
CATEGORY NUMBER
NAME VARCHAR2(50)
PRICE NUMBER
AUTHOR VARCHAR2(50)
ISBN NUMBER
CD Table
CDID NUMBER
CATEGORY NUMBER
TITLE VARCHAR2(50)
TYPE VARCHAR2(20)
PRICE NUMBER
TIME NUMBER
Member Table
MEMBERID NUMBER
NAME VARCHAR2(20)
PHONE NUMBER
ADDRESS VARCHAR2(50)
Register table
REGID NUMBER

5
LIBRARY DATABASE SYSTEM
TYPEID NUMBER
CATEGORY NUMBER
MEMBERID NUMBER
DATE_BORROW DATE
DATE_RETURN DATE
2.5 Database Implementation
CREATE TABLE ITEM
(
TYPE VARCHAR2(20) NOT NULL , CATEGORY NUMBER NOT NULL ,
CONSTRAINT ITEM_PK PRIMARY KEY (CATEGORY)
);
CREATE TABLE BOOK
(
BOOKID NUMBER NOT NULL , CATEGORY NUMBER NOT NULL , NAME
VARCHAR2(20) NOT NULL , PRICE NUMBER NOT NULL , AUTHOR
VARCHAR2(10) NOT NULL , ISBN NUMBER , CONSTRAINT BOOK_PK
PRIMARY KEY ( BOOKID , CATEGORY )
);
CREATE TABLE CD
(

6
LIBRARY DATABASE SYSTEM
CDID NUMBER NOT NULL , CATEGORY NUMBER NOT NULL , TITLE
VARCHAR2(20) NOT NULL , TYPE VARCHAR2(20 BYTE) NOT NULL , PRICE
NUMBER NOT NULL , TIME NUMBER NOT NULL , CONSTRAINT CD_PK
PRIMARY KEY ( CDID , CATEGORY )
);
CREATE TABLE MEMBER
(
MEMBERID NUMBER NOT NULL , NAME VARCHAR2(20 BYTE) NOT
NULL , PHONE NUMBER(10, 0) NOT NULL , ADDRESS VARCHAR2(50
BYTE) NOT NULL , CONSTRAINT MEMBER_PK PRIMARY KEY
( MEMBERID )
);
CREATE TABLE REGISTER
(
REGID NUMBER NOT NULL , TYPEID NUMBER , CATEGORY
VARCHAR2(20 BYTE), MEMBERID VARCHAR2(20) , DATE_ BORROW DATE
NOT NULL , DATE_RETURN DATE , CONSTRAINT REGISTER_PK PRIMARY
KEY ( REGID )
);
Insert into tables

7
LIBRARY DATABASE SYSTEM
INSERT INTO "HR"."BOOK" (BOOKID, CATEGORY, NAME, PRICE, AUTHOR, ISBN)
VALUES ('1', '1', 'OZ', '21', 'L. Frank Baum', '1231231231')
INSERT INTO "HR"."BOOK" (BOOKID, CATEGORY, NAME, PRICE, AUTHOR, ISBN)
VALUES ('2', '1', 'HARRY POTTER', '12', 'JK ROWLING', '2342342342')
INSERT INTO "HR"."BOOK" (BOOKID, CATEGORY, NAME, PRICE, AUTHOR, ISBN)
VALUES ('3', '1', 'GOT', '43', 'Jan Mike', '3453453453')
INSERT INTO "HR"."BOOK" (BOOKID, CATEGORY, NAME, PRICE, AUTHOR, ISBN)
VALUES ('4', '1', 'POSTMASTER', '34', 'RABINDRANATH TAGORE', '4564564564')
INSERT INTO "HR"."BOOK" (BOOKID, CATEGORY, NAME, PRICE, AUTHOR, ISBN)
VALUES ('5', '1', 'GITANJALI', '65', 'RABINDRANATH TAGORE', '5675675675')
INSERT INTO "HR"."BOOK" (BOOKID, CATEGORY, NAME, PRICE, AUTHOR, ISBN)
VALUES ('6', '1', 'OLIVER TWIST', '45', 'CHARLES DICKENS', '6786786786')
INSERT INTO "HR"."BOOK" (BOOKID, CATEGORY, NAME, PRICE, AUTHOR, ISBN)
VALUES ('7', '1', 'EARTH ENCYCLOPEDIA', '23', 'JOHN DOE', '7987897897')
INSERT INTO "HR"."BOOK" (BOOKID, CATEGORY, NAME, PRICE, AUTHOR, ISBN)
VALUES ('8', '1', 'MYSTERY 11', '33', 'JANE DOE', '8908908908')
INSERT INTO "HR"."BOOK" (BOOKID, CATEGORY, NAME, PRICE, AUTHOR, ISBN)
VALUES ('9', '1', 'THE SHINING', '11', 'STEPHEN KING', '3213213213')
INSERT INTO "HR"."BOOK" (BOOKID, CATEGORY, NAME, PRICE, AUTHOR, ISBN)
VALUES ('10', '1', 'DAVID COPPERFIELD', '44', 'CHARLES DICKENS', '4324324324')
INSERT INTO "HR"."BOOK" (BOOKID, CATEGORY, NAME, PRICE, AUTHOR, ISBN)
VALUES ('11', '1', 'ALIBABA', '56', 'Richard F. Burton', '5435435435')

8
LIBRARY DATABASE SYSTEM
INSERT INTO "HR"."BOOK" (BOOKID, CATEGORY, NAME, PRICE, AUTHOR, ISBN)
VALUES ('12', '1', 'ALLADIN', '56', 'Antoine ', '6546546546')
INSERT INTO "HR"."BOOK" (BOOKID, CATEGORY, NAME, PRICE, AUTHOR, ISBN)
VALUES ('13', '1', 'GULIVERS TRAVEL', '22', 'Jonathan Swift', '7657657657')
INSERT INTO "HR"."BOOK" (BOOKID, CATEGORY, NAME, PRICE, AUTHOR, ISBN)
VALUES ('14', '1', 'GIFT OF NILE', '23', 'JANE MASON', '8768768768')
INSERT INTO "HR"."BOOK" (BOOKID, CATEGORY, NAME, PRICE, AUTHOR, ISBN)
VALUES ('15', '1', 'WAR AND PEACE', '50', 'LEO TOLSTOY', '9879879879')
Commit Successful
INSERT INTO "HR"."CD" (CDID, CATEGORY, TITLE, TYPE, PRICE, TIME) VALUES
('1', '2', 'THE KILLING', 'ACTION', '22', '123')
INSERT INTO "HR"."CD" (CDID, CATEGORY, TITLE, TYPE, PRICE, TIME) VALUES
('2', '2', 'THE SHINING', 'HORROR', '33', '200')
INSERT INTO "HR"."CD" (CDID, CATEGORY, TITLE, TYPE, PRICE, TIME) VALUES
('3', '2', 'BLAIR WITCH PROJECT', 'HORROR', '44', '87')
INSERT INTO "HR"."CD" (CDID, CATEGORY, TITLE, TYPE, PRICE, TIME) VALUES
('4', '2', 'THE HOLE', 'CRIME', '22', '90')
INSERT INTO "HR"."CD" (CDID, CATEGORY, TITLE, TYPE, PRICE, TIME) VALUES
('5', '2', 'BATMAN', 'ACTION', '33', '100')

9
LIBRARY DATABASE SYSTEM
INSERT INTO "HR"."CD" (CDID, CATEGORY, TITLE, TYPE, PRICE, TIME) VALUES
('6', '2', 'AVENGERS', 'ACTION', '44', '102')
INSERT INTO "HR"."CD" (CDID, CATEGORY, TITLE, TYPE, PRICE, TIME) VALUES
('7', '2', 'THE GODFATHER', 'CRIME', '55', '115')
INSERT INTO "HR"."CD" (CDID, CATEGORY, TITLE, TYPE, PRICE, TIME) VALUES
('8', '2', 'PULP FICTION', 'CRIME', '11', '186')
INSERT INTO "HR"."CD" (CDID, CATEGORY, TITLE, TYPE, PRICE, TIME) VALUES
('9', '2', 'FIGHT CLUB', 'ACTION', '24', '150')
INSERT INTO "HR"."CD" (CDID, CATEGORY, TITLE, TYPE, PRICE, TIME) VALUES
('10', '2', 'ORPHAN', 'HORROR', '45', '90')
INSERT INTO "HR"."CD" (CDID, CATEGORY, TITLE, TYPE, PRICE, TIME) VALUES
('11', '2', 'CONJURING', 'HORROR', '43', '60')
INSERT INTO "HR"."CD" (CDID, CATEGORY, TITLE, TYPE, PRICE, TIME) VALUES
('12', '2', 'FOREST GRUMP', 'DRAMA', '23', '106')
INSERT INTO "HR"."CD" (CDID, CATEGORY, TITLE, TYPE, PRICE, TIME) VALUES
('13', '2', '12 ANGRY MEN', 'DRAMA', '34', '125')
INSERT INTO "HR"."CD" (CDID, CATEGORY, TITLE, TYPE, PRICE, TIME) VALUES
('14', '2', 'THE MATRIX', 'ACTION', '43', '145')
INSERT INTO "HR"."CD" (CDID, CATEGORY, TITLE, TYPE, PRICE, TIME) VALUES
('15', '2', 'HOME ALONE', 'COMEDY', '33', '200')
Commit Successful

10
LIBRARY DATABASE SYSTEM
INSERT INTO "HR"."MEMBER" (MEMBERID, NAME, PHONE, ADDRESS) VALUES
('1', 'JOHN', '1212121212', 'MANCHESTER')
INSERT INTO "HR"."MEMBER" (MEMBERID, NAME, PHONE, ADDRESS) VALUES
('2', 'JANE', '3322332233', 'LONDON')
INSERT INTO "HR"."MEMBER" (MEMBERID, NAME, PHONE, ADDRESS) VALUES
('3', 'MILY', '4433443344', 'MANCHESTER')
INSERT INTO "HR"."MEMBER" (MEMBERID, NAME, PHONE, ADDRESS) VALUES
('4', 'MAX', '1122112233', 'LIVERPOOL')
INSERT INTO "HR"."MEMBER" (MEMBERID, NAME, PHONE, ADDRESS) VALUES
('5', 'ROSS', '1020304050', 'OXFORD')
INSERT INTO "HR"."MEMBER" (MEMBERID, NAME, PHONE, ADDRESS) VALUES
('6', 'RACHEL', '5040302010', 'LONDON')
INSERT INTO "HR"."MEMBER" (MEMBERID, NAME, PHONE, ADDRESS) VALUES
('7', 'CHANDLER', '0102030405', 'LIVERPOOL')
INSERT INTO "HR"."MEMBER" (MEMBERID, NAME, PHONE, ADDRESS) VALUES
('8', 'MONIKA', '0504030201', 'BATH')
INSERT INTO "HR"."MEMBER" (MEMBERID, NAME, PHONE, ADDRESS) VALUES
('9', 'JOEY', '1133225544', 'MANCHESTER')
INSERT INTO "HR"."MEMBER" (MEMBERID, NAME, PHONE, ADDRESS) VALUES
('10', 'PHOEBE', '1534211510', 'YORK')

11
LIBRARY DATABASE SYSTEM
Commit Successful
UPDATE "HR"."REGISTER" SET DATE_BORROW = TO_DATE('2018-08-12 00:00:00',
'YYYY-MM-DD HH24:MI:SS') WHERE ROWID = 'AAAFbyAAEAAAAIOAAA' AND
ORA_ROWSCN = '1808417'
INSERT INTO "HR"."REGISTER" (REGID, TYPEID, CATEGORY, MEMBERID,
DATE_BORROW, DATE_RETURN) VALUES ('2', '3', '2', '3', TO_DATE('2018-08-10
00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2018-10-14 00:00:00', 'YYYY-MM-
DD HH24:MI:SS'))
INSERT INTO "HR"."REGISTER" (REGID, TYPEID, CATEGORY, MEMBERID,
DATE_BORROW, DATE_RETURN) VALUES ('3', '2', '1', '4', TO_DATE('2018-08-12
00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2018-10-11 00:00:00', 'YYYY-MM-
DD HH24:MI:SS'))
INSERT INTO "HR"."REGISTER" (REGID, TYPEID, CATEGORY, MEMBERID,
DATE_BORROW, DATE_RETURN) VALUES ('4', '1', '1', '1', TO_DATE('2018-08-12
00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2018-09-06 00:00:00', 'YYYY-MM-
DD HH24:MI:SS'))
INSERT INTO "HR"."REGISTER" (REGID, TYPEID, CATEGORY, MEMBERID,
DATE_BORROW, DATE_RETURN) VALUES ('5', '14', '1', '5', TO_DATE('2018-09-06
00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2018-09-16 00:00:00', 'YYYY-MM-
DD HH24:MI:SS'))