LIS-reportdoc - MichaelWeiszcom by tyndale

VIEWS: 8 PAGES: 16

									                                                                                     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’

								
To top