Get documents about "LIS-reportdoc - MichaelWeiszcom
Document Sample
Weisz 1
Introduction
Most cities operate at least one public library. These libraries contain many
branches, each with many books and related materials such as movies, music, and
magazines (now referred to as “materials” throughout this report). In this specific library,
customers who have signed up and received a library card are permitted to borrow copies
of these items. Our branches may have more than copy of a specific item. They then must
return their item(s) within a twenty-one day period of time. If the item(s) are returned
after the due date, the customer is fined twenty-five cents per item per day, including
weekends and holidays.
The purpose of this project is to create an information system for all the branches
of this public library. There is a need for a database in third normal form, which will be
implemented across all the library‟s systems for the employees and customers.
Employees will need a system built into their computers/terminals for creating new
customers on the library system, finding materials in stock at branches, lending materials,
searching through lent materials, reporting who owes money in late fees, and other
related tasks. The customers may search the library database for materials at many of the
terminals located in each branch. On the employee computer systems and terminals, there
will be forms needed to input the proper data and update the database to reflect these
changes. Some forms will be needed by users to query data in the database as well. The
employees will also need a way to print reports of important data to view in a more
simplistic way. The database will also have to be secured by placing permissions on
particular tables that should only be viewable to certain groups of users. Lastly, a size
Weisz 2
estimate will have to be calculated on the database for the current year and for three years
in the future.
The entity relationship diagram needed for this database is fairly complex. There
is a „Branches‟ table, which has information about each branch of the library, such as the
branch‟s id, name, and address. The next table is the „customers‟ table, which contains
data such as the customer‟s unique id, name, address, and the branch id of the library of
which the customer is a member. Another child table of the „Branches‟ table is the
„Stock‟ table. This table contains the branch id, material id, and an integer field for
quantity of each material. This table stores how many of each material is in stock at each
branch. There is then a „Materials‟ table, which is a parent of the „Stock‟ table. This table
contains a unique identifier for each material, a department id code, the type of material
(i.e. book, DVD), a card catalog number, and all additional fields needed for storing data
on each material for queries. One of the parents to this „Materials‟ table is the
„Departments‟ table. The „Departments‟ table contains a unique department id, along
with the department name and a field for information on the department. There is also a
„Lending‟ table, which keeps records of library transactions for lent materials. This is a
child table of the „Branches‟ and „Customers‟ table. Here there are fields that contain a
unique id for each transaction, customer id, branch id, rent and due dates, and a foreign
key referencing the „Latefees‟ table. The „Latefees‟ table contains a unique id and a field
for the amount of money owed in late fees, and is in a one to one relationship with the
„Lending‟ table. In summary, that means that each customer has a record in the „Latefees‟
table. If the customer does not owe a late fee, the value for the field in the table is zero;
Weisz 3
else it will be the owed amount. The last table used in this diagram is the „Copies‟ table,
which is used to track which copy of a material is being lent currently. This table contains
the material id, the copy number, and the transaction id. It is a child of both the „Stock‟
and „Lending‟ tables. To better understand this model, review the visual diagram for it
further below in this report.
Weisz 4
Forms and Reports
In this new information system being designed for the library, there will be a need
by users for the use of forms and the creation of reports. Both of these elements are key
in the successful design of this new database system and will need to be utilized by all
groups. Forms and reports for employees and upper management will be necessary to
perform their job responsibilities and run the library system. Form for customers and
visitors will provide a user-friendly interface for them to find materials in each library
branch.
There are many forms needed for this system being designed. For the employees,
there will need to be a few forms they will need to access to properly run the library and
its information system. In order to add new customers into the system so they may later
borrow books, there will need to be a new customer form. This will input all the basic
information about the customer into the system in the „customers‟ table. There will also
need to be a form available to employees for customers to borrow materials, which will
write to the „lending‟, „copies‟, and „stock‟ tables. The managers of each library branch
will have access to those forms as well, plus a form for importing new materials into
inventory via the „stock‟ table. The president or CEO of the library chain will have access
to all these forms. Not only to those forms, but also to three more forms: one to modify
the „branches‟ table, one to modify the „departments‟ table, and one to modify the
„materials‟ table. These are abilities that globally modify important aspects of the
database: areas that employees, customers, and even managers should have no access.
Also the customers need to have a form to use in order to search the „materials‟ table and
Weisz 5
„stock‟ table together. The customer form will be accessed from the terminals/computers
for customers and visitors in the library. This will allow customers to search for books,
music, and more, by a variety of attributes, in order to see which branch of the library has
the item and whether or not it‟s in stock. If a particular material is in stock, it will also
display the card catalog number (field name „ccn‟) so the user may find the book.
This new system will also need the ability of printing reports to particular groups
of users. The employees and upper management will need several reports to complete
their job duties using the new database system. One of these reports will be the ability for
their computer to print out records of current books being lent from the library. Another
report is one that prints each customer who owes late fees and how much money in late
fees he/she owes. Those are the most frequent requests of reports needed. Other ad hoc
reports may be required, and would most likely involve the „materials‟, „stock‟, and
„customers‟ tables. The president or CEO will need to be able to report on all of these
tables as well, and also have access to all other tables for ad hoc reports.
Weisz 6
Entity Relationship Diagram
The following entity relationship diagram shows the database structure and
relationships for the library‟s new information system. This system will keep track of all
the data from every branch of the library. A detailed explanation of this model is above in
this report.
Weisz 7
SQL Table Design
Since this database is to be created using the language SQL, each table must be
created using SQL code. Tables must be created with their proper fields, each with
appropriate data types and lengths for integrity purposes, and the proper primary key and
foreign key references. The following is the SQL statements for each of the eight tables
being designed for this library system.
„BRANCHES‟ TABLE
CREATE TABLE BRANCHES
( BRANCHID INTEGER NOT NULL,
NAME CHAR(30),
ADDRESS CHAR(40),
CITY CHAR(15),
STATE CHAR(2),
ZIP CHAR(9),
PRIMARY KEY(BRANCHID))
„CUSTOMERS‟ TABLE
CREATE TABLE CUSTOMERS
( CUSTID INTEGER NOT NULL,
FNAME CHAR(15),
LNAME CHAR(15),
ADDRESS CHAR(40),
CITY CHAR(15),
STATE CHAR(2),
ZIP CHAR(9),
BRANCHID INTEGER NOT NULL,
PRIMARY KEY(CUSTID),
FOREIGN KEY(BRANCHID) REFERENCES BRANCHES)
Weisz 8
„LENDING‟ TABLE
CREATE TABLE LENDING
( TRANSID INTEGER NOT NULL,
CUSTID INTEGER NOT NULL,
BRANCHID INTEGER NOT NULL,
RENTDATE DATETIME,
DUEDATE DATETIME,
RETURNED BIT,
LATEFEEID INTEGER NOT NULL,
PRIMARY KEY(TRANSID),
FOREIGN KEY(CUSTID) REFERENCES CUSTOMERS,
FOREIGN KEY(BRANCHID) REFERENCES BRANCHES,
FOREIGN KEY(LATEFEEID) REFERENCES LATEFEES)
„LATEFEES‟ TABLE
CREATE TABLE LATEFEES
( LATEFEEID INTEGER NOT NULL,
LATEFEE DECIMAL(5,2),
PRIMARY KEY(LATEFEEID))
„DEPARTMENTS‟ TABLE
CREATE TABLE DEPARTMENTS
( DEPID INTEGER NOT NULL,
NAME CHAR(15),
INFO CHAR(40),
PRIMARY KEY(DEPID))
„MATERIALS‟ TABLE
CREATE TABLE MATERIALS
( MATID INTEGER NOT NULL,
DEPID INTEGER NOT NULL,
MATTYPE CHAR(2),
TITLE CHAR(30),
AUTHOR CHAR(20),
EDITOR CHAR(20),
PUBLISHER CHAR(15),
PUBLOC CHAR(15),
YEAR INTEGER,
ISBN CHAR(15),
CCN CHAR(20),
PRIMARY KEY(MATID),
FOREIGN KEY(DEPID) REFERENCES DEPARTMENTS)
Weisz 9
„STOCK‟ TABLE
CREATE TABLE STOCK
( BRANCHID INTEGER NOT NULL,
MATID INTEGER NOT NULL,
QUANTITY INTEGER,
PRIMARY KEY(BRANCHID,MATID),
FOREIGN KEY(BRANCHID) REFERENCES BRANCHES,
FOREIGN KEY(MATID) REFERENCES MATERIALS)
„COPIES‟ TABLE
CREATE TABLE COPIES
( MATID INTEGER NOT NULL,
COPYNUM INTEGER NOT NULL,
TRANSID INTEGER NOT NULL,
PRIMARY KEY(MATID,COPYNUM),
FOREIGN KEY(MATID) REFERENCES STOCK,
FOREIGN KEY(TRANSID) REFERENCES LENDING)
Weisz 10
Database Size Estimate
Database size estimates are an approximated prediction of the size of a database
in a certain amount of time. These sizes can be predicted based on the maximum size in
bytes for each field used in each table the database. Assuming the maximum amount of
bytes is used in each field in each record, the maximum size per record can be calculated.
From there the database size can be estimated and even predicted at future periods of
time. These sizes are important to measure the rate of data growth for a company or
business. The size predictions are necessary because essential hardware for running the
system is dependant upon this estimation. As one can see, in doing database size
estimates, many assumptions must be made.
The following table shows the maximum bytes for one record in each table of the
database. This was calculated by adding together the maximum size (in bytes) for each
field in each table.
TABLE NAME SIZE OF ONE RECORD
BRANCHES 100 BYTES
CUSTOMERS 104 BYTES
STOCK 12 BYTES
MATERIALS 149 BYTES
DEPARTMENTS 59 BYTES
LENDING 33 BYTES
COPIES 12 BYTES
LATEFEES 8 BYTES
The next step in performing this estimate is to calculate the maximum size of the
database at this moment in time. Assumptions must be made on how many records have
Weisz 11
been created for each table. From that data we can calculate the approximate current size
of the database.
TABLE NAME NUMBER OF RECORDS
BRANCHES 10 RECORDS
CUSTOMERS 10,000 RECORDS
STOCK 5,000,000 RECORDS
MATERIALS 500,000 RECORDS
DEPARTMENTS 30 RECORDS
LENDING 30,000 RECORDS
COPIES 30,000 RECORDS
LATEFEES 30,000 RECORDS
The sizes of records look high in some areas but they are for a reason. In this data
we assume the library only has 10 branches. Between all 10 branches the library has a
total of 10,000 customers. Each branch has 30 departments for materials. The library has
a database of 500,000 materials. In reality that number is most likely much great, but we
are going to assume that‟s all they carry at the moment. More materials would be added
as time continues. The stock table shows the quantity of how many materials are at each
branch so number 5,000,000 is derived from 500,000 materials multiplied by 10
branches. Over time branches might clear some of the lending transaction logs, so at this
moment it is assumed that 30,000 transactions are logged on the system. Since the
„latefees‟ table was in a one to one relationship with the „lending‟ table, it also has 30,000
records. The copies table follows that same behavior and also approximately has 30,000
records. To calculate the maximum amount of bytes currently stored in each table, we
multiply the maximum byte size by the number of records in each table.
Weisz 12
TABLE NAME MAXIMUM TOTAL SIZE
BRANCHES 10,000 BYTES
CUSTOMERS 1,040,000 BYTES
STOCK 60,000,000 BYTES
MATERIALS 74,500,000 BYTES
DEPARTMENTS 1,770 BYTES
LENDING 990,000 BYTES
COPIES 360,000 BYTES
LATEFEES 240,000 BYTES
TOTAL SIZE 137,141,770 BYTES
OR… 130.79 MEGABYTES
From here we can multiply that size by different rates in order to estimate the size
of the database in one, two, and even three years. The size in one year will be calculated
at 50% more than the current size. The second year‟s growth will be 30% in addition to
the first year. The third year will be 20% in addition to the second. Shown below is the
estimated size of the database for the next three years. Sizes will be shown in megabytes,
which is a more relevant unit to express this data.
CURRENT 130.79 MEGABYTES
AFTER 1 YEAR 196.19 MEGABYTES
AFTER 2 YEAR 255.05 MEGABYTES
AFTER 3 YEAR 306.06 MEGABYTES
Weisz 13
Database Security
Database security is another important issue to consider when designing a
database. Depending on security permissions that are set by the database administrator,
users may have read access, write access, or even no access to particular tables or even
columns. Access on certain SQL commands can also be placed. Users tend to be placed
in groups and those groups are restricted with permissions, rather than restricting access
individually. This is normally performed to hide sensitive data such as credit card
numbers, passwords, and social security numbers.
In the library‟s information system, four groups of users will exist in the system.
Each group will have a different set of security access rights to tables and columns. The
groups in this specific system are: president/CEOs, managers, employees, and customers.
All the users within these groups must have a different set of permissions based on their
job responsibilities. This specific system does not hold any sensitive data, and any data
that may be considered somewhat sensitive is in its own table. Therefore all permissions
will be granted and revoked on tables only and not individual columns. The permissions
on the database tables will be as follows.
President or CEO(s) (and of course the DBA)
TABLE NAME READ WRITE
BRANCHES X X
CUSTOMERS X X
STOCK X X
MATERIALS X X
DEPARTMENTS X X
LENDING X X
COPIES X X
Weisz 14
Branch Managers
TABLE NAME READ WRITE
BRANCHES X
CUSTOMERS X X
STOCK X X
MATERIALS X
DEPARTMENTS X
LENDING X X
COPIES X X
Employees
TABLE NAME READ WRITE
BRANCHES X
CUSTOMERS X X
STOCK X X
MATERIALS X
DEPARTMENTS X
LENDING X X
COPIES X X
Customers
TABLE NAME READ WRITE
BRANCHES X
CUSTOMERS
STOCK X
MATERIALS X
DEPARTMENTS X
LENDING
COPIES
First of all, the president and/or CEO(s) must have access to every table in the
database. Unlike managers they have permission to add new materials, departments, and
branches of the library into the database. These tasks are too vital and risky to be done by
a manager or any positions under that. Managers are able to read every table due to the
Weisz 15
fact that each table has important data that all managers need to be able to read to
perform his/her job duties. Managers cannot write into the „branches‟, „departments‟, and
„materials‟ tables. The reasons of the first two were just explained. The managers are not
allowed to write to the „materials‟ table because they only need to update what materials
are found in their branch, which is done in the „stock‟ table instead. The employees have
the same permissions as the managers. The only difference is the forms that are used by
each group of users; more specifically for access to the „stock‟ table. The manager has a
form to update new materials into their stock. The employees only need to update that
table through the system, by changing the field „quantity‟, when a book is checked out or
returned. Lastly, customers only have permissions from terminals to view the different
branches and searching for materials that are in stock and being able to list by
departments. The security permissions listed above reflect all these abilities.
Not only are read and write permissions important to know for each group, but so
are SQL command permissions. Even though no one other than the DBA or
president/CEO(s) will have access to the SQL server to directly execute commands,
permissions must still be defined.
SQL COMMAND GROUPS WITH ACCESS
CREATE DBA/President/CEO(s)
ALTER DBA/President/CEO(s)
DROP DBA/President/CEO(s)
INSERT All groups except Customers
UPDATE All groups except Customers
DELETE DBA/President/CEO(s)/Managers
SELECT All groups
Others (WHERE, etc) All groups
Weisz 16
SQL Queries/Reports
The software installed on library computers and terminals will interact with the
database to perform queries. Those queries will then be executed on the SQL Server, and
the results are returned to the system to be displayed to the user. The following are
example queries that would be common to be run against the database.
List all low populated branches with less than 1000 registered customers.
SELECT branches.name, COUNT(customers.custid)
FROM branches INNER JOIN customers
ON branches.branchid = customers.branchid
GROUP BY branches.name
HAVING COUNT(customers.custid) < 1000
List all customers who owe late fees and on what items at branch three.
SELECT customers.fname, customers.lname, materials.title, materials.author, latefees.latefee
FROM customers, lending, latefees, materials, copies, stock
WHERE customers.custid = lending.custid
AND lending.latefeeid = latefees.latefeeid
AND lending.transid = copies.transid
AND copies.matid = stock.matid
AND stock.matid = materials.matid
AND lending.branchid = 3
AND latefees.latefee > 0
How many copies of the book The Pearl by John Steinbeck are in stock at branch two?
SELECT materials.title, materials.author, stock.quantity, materials.ccn
FROM materials INNER JOIN stock
ON materials.matid = stock.matid
WHERE materials.author LIKE ‘John Steinbeck’
AND materials.title LIKE ‘The Pearl’
