Creating a computer system for a video rental shop
sis
Introduction
ABZ World is a video rental shop, located in Stoke Newington town centre. It is a relatively small shop and has 300 members, who live in and around Stoke Newington. The shop is owned and managed by Abdullah Elgayar. He set up the business two years ago and has two other staff working in the shop on a part time basis.
Abdullah currently has a filing system that occupies a great deal of space and is very time consuming to analyse. He gives his members cards and has to manually record which video members are hiring. The old paper filing system is very inefficient in notifying when members are overdue with bringing the videos back. This in turn means other customers are unsatisfied when the video they want isn't in stock and so Abdullah loses out on revenue. His shop has not got much space and the filing system is taking 1/4 of the shop floor, more spaces are needed for videos and the shop at the moment is looking crap and tight costumes feel isolated and do not get to look around and look at covers of videos as the shop can get cramp, card board standing posters have been scraped this makes advertisement of movies hard. Costumers will not know when the new videos have arrived this make business in his shop go down.
More and more children are coming to the shop and Abdullah says "I have very little movies for young children if I don't sort it out they will go someplace else.
He is also opening another Abz World in wood green centre about 15 miles away and he would like to link up both shops.
Abdullah possesses greater than average IT skills and is keen to introduce a computer-based system for the rentals of his videos. If we make both shops have a computerized system Abdullah will be able to link both shops and have plenty of space for videos and advertisements and costumers can return videos in both shops.
The system both old and new is needed to:
* Keep track of members and their details.
* Keep track of the number of videos each member of staff rents out to customers.
* Have a listing of all the staff in the shop
* Have a list of all the videos in stock
* Have a list of all the videos rented
* Enable a showing of which videos are rented by which customers
* Show when the video is due back
* Be able to add/delete members, videos and staff
* Link up both shops
Investigation Methods
An interview was arranged with Abdullah and a list of points to be discussed was drawn up before the interview. Also I contacted Abdullah by telephone and e-mail to pick up certain points.
Topics for discussion:
* The reasons for requiring the new information system
* The problems with the current manual system
* The methods currently used to record data
* The information that needs to be kept in each of the following areas:
Staffing
Videos
Rentals
Members
Video costs
* The required output
* Any hardware or software limitations - for example what was the hardware and software to be used?
* The budget (Money wise)
Summary of the Interview
The nature of the paper-based system was discussed. Basically everything including customers, videos, staff and rentals are all written out manually onto a standard form every time something needed to be updated.
There are a few problems with this filing system:
Keeping track of when videos are due back.
Because all the data is kept in files you must continuously check if people are not bringing data back. Obviously this can be very time consuming and errors are likely to be made, resulting in lost videos, lost assets and revenue. It is also not immediately obvious when a date is overdue because there is so much writing on the forms and so you cannot always see an overdue video.
Seeing how well the staff are doing.
There is nothing in the old system that tells Abdullah how well his staff are doing. If he knew how many videos his staff sold each month he could perhaps reward them with bonuses or even employee of the month awards.
Stock levels.
It can also be a nuisance for Abdullah to go into the storeroom to get a video only to find that the video has already been rented out. There is no way to check if the video is in or out before he goes into the storeroom. This can be quite serious because while he is in the storeroom it can leave the shop unstaffed with potential thieves in the shop.
The amount of time it takes to document a new member.
With the manual system it can be very time consuming, just entering data for the new member. The form has no tick boxes and Abdullah has to write it all in full by hand.
There are no links.
All the different files are all kept separate from each other and so there are no links between: The video the person who rented the video and the staff member who rented the video out. If these files were linked relevant data and information could be retrieved.
Concluding Remarks
Mr Elgayar the end user is used to his position and is suffering from a paper based management system that is inefficient. Abdullah realises his problems and is very willing to become more efficient in his workplace.
Objectives for the New Systems
Qualitative
. Data entry should be as intuitive as possible.
2. The input screens should explain themselves.
3. Data entry should be quick.
4. The mechanics of the application chosen should not be apparent to the user.
5. A main menu should be loaded automatically when the database is loaded, and the whole system should be menu driven.
Quantitative
. It should be possible to rent a video without referring to video or member input screens.
2. It should be possible to input new videos, staff members, and customers in less than thirty seconds.
3. It should be possible to provide reports in less than 60 seconds in each of the following areas:
* Videos rented
* The staff that rented the videos out
* Staff currently employed
* Videos owned by the store
* Customer details
System the video shop is using now:
They have files in a filing cabinet in alphabetical order of name and ID number and have forms which are filled out when a video is taken out and returned and they will also have the history of the customer.
e.g.
To add a new member they use this form:
Constraints
Hardware
This report explains the specification of the proposed Computer that the library will need buy, and the advantages and disadvantages of buying these computers.
Midi-Tower Case ATX
This is a stylish steel case. Both side's are spring loaded and easily removed by pulling the handle on the side panel.
It is more compact and takes less space compared to old cases.
This is the casing that holds all the components, and is the case that all peripherals connect to, such as the monitor, keyboard, mouse, and speakers.
The weakness point it can not take as many hardware as the bigger version
ATX Motherboard.
The ATX motherboard rotates the orientation of the board 90 degrees. This allows for a more efficient design, with disk drive cable connectors closer to the drive bays and the CPU closer to the power supply and cooling fan, an ATX motherboard is required to use the newest Intel processors.
This is board that hold the core components of a computer and connects them together. The CPU, graphics card, sound card, Hard drives all connect to the motherboard.
ATX power Supply
This is the power supply that powers the computer. Connects the main 240V and converts it 16V. It powers components inside the case, such as the motherboard, hard drives, DVD drive, and floppy drive. Gold plated connectors provide perfect conduction. Also it is noiseless.
Intel Pentium III 1GHZ Processor
This is the main part of the computer. It is a hardware component, which act upon instructions to process, input and output information. It determines the speed of the computer, and the software that can run on the computer. A P3 1GHz processor can handle multimedia applications, it will suit the leisure centre as they will not require extensive applications.
28MB SDRAM
Synchronous Dynamic Random Access Memory. This is the memory that computer uses while running, its non permanent and all is lost once the computer is turned off. The memory is used for running applications, the more the memory the better. It is slower than the new 512MB DIMM but cheaper.
20GB HDD
This is the size of the Hard drive that stores user data and installed applications. It would be more suitable if twice that hard disk space was used. As the price difference would not be that much different.
Audio Controller
This processes the Audio output from applications, a speaker must be connected to hear the sounds. This would work well for the leisure centre. A more advanced card such as one that outputs digital surround sound will not be needed as it is only a luxury addition which is not necessary important for the centre.
Integrated 56k V.90 modem
This is the modem that converts digital signal to analogue and sends across the telephone line. It connects computers to Internet Service Providers (ISP) such as AOL and Freeserve by a normal telephone line. It has no use in a network environment since the computer will be connected via the Ethernet Cards to a server. And the Server will be connected to the internet for outside connection to web for web browsing. The connection by a modem is slow, ADSL or Cable connections would be more suitable for the leisure centres as more then one computer can use the internet and it will run much faster.
Intergraded 10/100 Fast Ethernet LAN
This is the network card that can connect two computers together, or connect a computer to central server. It will allow file sharing and peripheral sharing such as a printer.
8xDVD-ROM drive
For accessing DVD-ROM disks, that can hold information as much as 16GIGS on one disc. Eight speed is sufficient for the leisure centre as they will not be copying DVD's so will not need a faster one.
Windows XP
The operating software is the back bone that other software run on, and is used for management of user information.
The weakness of Windows ME is that some software programs will not function properly and some of the new software would not run on it at all. It will suit home user only and does not suit business organisation.
2 months Collect and return warranty(up gradable to 3 years)
This covers the computer for any faults that may occur. If a fault occurs then the computer is collected and returned once fixed without any charges.
05 key WIN XP keyboard
Standard keyboard for input data which is transferred to the computer. But the keys ...
This is a preview of the whole essay
The weakness of Windows ME is that some software programs will not function properly and some of the new software would not run on it at all. It will suit home user only and does not suit business organisation.
2 months Collect and return warranty(up gradable to 3 years)
This covers the computer for any faults that may occur. If a fault occurs then the computer is collected and returned once fixed without any charges.
05 key WIN XP keyboard
Standard keyboard for input data which is transferred to the computer. But the keys are small and will be hard for novice users to use.
A newer keyboard would be more suitable, one with shortcut buttons and hand rest, which puts less strain on the wrists and allows the shoulders to relax. Repetitive strain injury (RSI) is a common complaint of keyboard users ans a comfortable wrist posture helps to avoid it
3 button mouse
A very common handheld pointing device it allows the user to navigate applications and icons. The mouse will allow the user to do tasks like selecting, drawing, scrolling pages and many other useful tasks.
Studies showed users holding the mouse with their fingertips not palms, it could lead to pain in user's hand in long run
80W speakers
Standard PC speakers to hear Audio output. It will take the sound being sent from the computer's sound card to allow the user to hear the sounds.
Advantages
* Good price
* 12 month warranty
* Have all the essential facilitates required by the leisure centre
* Ethernet card included which allows networking later on and fast transfer of data.
* Intel Pentium 3 1GHz processor, this is fast possessor that can handle most applications.
* DVD-ROM drive is included so CD/DVD can be played on the computer.
Disadvantages
* User may struggle with new software as it does not have high processing power and memory.
* The windows ME operating system is for home use only, it has a lot of bugs, and may cause the computer to crash or stop working.
* The hardware suppliers gave a vague specification for Tower Case, motherboard and the ATX power supply
* Only 20GB of hard disk space, this my not be enough
* Win 98 keyboard, this is a old keyboard which does not take advantage of windows ME keys such as logoff . Also it does not have a palm rest.
* 128MB SDRAM this may not be enough memory for some applications the leisure centre may use.
Software:
Office XP which includes word excel, access and PowerPoint.
Member requirements
Purpose of process
The purpose of the process is to record all information about videos in the library, videos on loan, reserved videos, as well as to record specific details about all members, and produce reports about all borrowed videos, overdue videos and list of reserved videos.
Description of process
Members come to the library to borrow or to reserve videos, the library allows them to borrow and if the video is not in the library the member will make reservation. The library will send a notification to the borrower if he/she has not returned by the due date
The library will hold the video once the other borrower has returned the video. The video is held on reservation for four days. If the member does not pick it up within these days, the library will cancel the reservation and put the video back on the shelf. Reports are produced for all reserved and overdue videos.
Member details
information
The purpose of the system
* Information about Members, videos, borrowing, borrowing line and reservation information will be stored in five separate files.
* Allows entry of information via data entry screens about members, videos and borrowing or reserving
* Allows the updating and the deletion of information
* Production of document such as reserved videos, overdue video/videos reminders
List of potential sources
* Interviewing staff and managers who are using, entering or receiving all different data for the system
* General information about the library for example annual reports, brochures, standard operating procedures and strategic plan, or other long-range planning documents.
* Analysis of the documents used to record information about how to record information about members, videos and borrowing .etc
* Analyse all the existing documentation such as reports, letters, and transaction. etc
* Identifying data which may be internally generated within the system. for example total fine calculation
* Questionnaires to collect facts and opinion from respondents
* Visit and see how similar libraries managing information
The school have given me a budget of £10,000 to design a simple and flexible system that can be used by a person with not that much of an experience on a computer, because some of the staff have no experience because they've always been using a paper base system, although they will still get some training by myself as part of the negotiation done.
Computers must be purchased with the necessarily hardware, software needed (output devices such as: printers scanners etc) and a network with the computers and hardware devices must be established.
As letters are handwritten at the moment some of them may not be understandable to read, to solve this problem I have decided to create and include a button within the database programme which will be linked to Microsoft word where the letter may be typed making them clear to understand and read. By developing a new database system there will be no loss of data as it all saved on the computer system. This will reduce the amount of loss of paper work. Supervisor doesn't need to check the old paper base work.
User's IT Skills and Knowledge
The end user Mr Elgayar is an experienced user of Word and has a basic knowledge of the operation of Access. He is also quite good at using Excel. His keyboard skills are standard. He has used Publisher for the making of cards but never on a professional basis.
The Systems Life Cycle:
We have been told that we should only document the design, implementation and testing stages of our design. However if I were to document the complete production of the system I would have used the systems life cycle. The systems life cycle helps designers to approach the design of a system in a methodical way. There are several variations of the cycle but below is the most common.
. Problem Definition - the problem will be defined by the user
2. Feasibility Study - investigate the system and decide if there is need for a new system
3. Analysis - analyse the requirements and produce a specification
4. Design - the design is produced
5. Construction
6. Testing - system is fully tested
7. Implementation - system implemented and users trained
8. Maintenance - there are three types of maintenance:
a. Perfective maintenance, where you make the system easier to use or you add new facilities
b. Adaptive maintenance, where you make changes to suit the changes in working environment
c. Corrective maintenance, where you change something because of errors discovered in the original system
9. Evaluation - evaluation of the system and if it meets the requirements of the user
Methods of Gathering data:
If I had had to collect my own user information on the requirements of the new system and what the problems were with the new system there are four techniques I could use these are: -
. Interviewing staff at different levels about the present system and what they would like to see from the new system.
2. Sending out questionnaires and analyzing results.
3. Observing current procedures with current system.
4. Examine current business and systems documents and outputs.
Design
The Feasibility of a Computer Based System:
It has been required that I should produce a computer based system for the video libary. There are many advantages to a computer based rather than a non-computerised system these advantages include: -
* Computer systems can be backed up so that if there is a fire or the computers contract a virus, the files are not lost altogether. However if the system was not computerised and there was a fire, the data would be lost. Which could lead to a loss of customer due to a lack to reliability to the customer.
* A computer system can be modified and changed more easily than a non-computerised system. E.g. if you wanted to change the format of the customer data entry forms on a computer it could be done in a few minutes but manually on a non computerised system it would take a very long time to rewrite the records if they were held on card.
* A computerised system can find files within seconds where as on a card filing system it would take people a lot longer as it has to all be done by people and then the process is subject to human error.
Going on my recommendation of a computerized system I now have to provide and recommend the hardware and software most suitable for the system.
Hardware:
The hardware of a machine is the physical machinery, which makes up the computer. In order to start to think about designing a system I need to first of all think about the possible use of different types of hardware: -
* Keyboard - used mainly to enter in data about the customer, parcel and the parcels destination.
* Visual Display Unit - A user-friendly way to display the database system.
* Digital scales (for weighing parcels) - so weights have been calculated accurately
* Computer mouse - to select different function options.
* Screen Touch Screen - to speed up the input of data and other functions within the database system.
* Screen pens - to speeded up processes within the system especially the input of data.
I have decided to keep the hardware simple any only have a computer, keyboard, VDU and mouse. This is relatively cheep compared with the other options and will be sufficiently simple for the users to handle.
Choices of Software:
There are three main choices of software these are Excel a spreadsheet, Access a database or a high level language such as Pascal. All have advantages and disadvantages.
Excel:
Excel has many advantages, which include the following points and features: -
* Excel allows you to insert buttons or macros these allow you to open and close forms at the click of a button. They also allow you to do things like run a query or calculation.
* Excel allows you to highlight different cells in colour and fill effects. You can highlight a cell by first clicking on the cell you want and then the fill colour icon, which brings up a range of colour, you can also colour the text or lines.
* There is a formula bar at the top, which displays the formula of the cell you are viewing; here you can also add things to the formula.
* You can create look-up tables.
* You can create graphs using the Chart Wizard tool.
* In Excel you can also copy the cell of a formula from cell you cell. This can be very useful when using a formula to calculate a certain equation.
* You can name cells, which makes identifying cells easier.
* Excel is a multi dimensional spreadsheet, this means that you can have as many sheets as you want, this is also known as a three dimensional spreadsheet.
Excel has disadvantages as well as advantages, these include: -
* If you change the name of a page after making a macro it can't find the correct page because it has changed names. This means you then have to go into Visual Basic and manually change the name of the page on the actual piece of code. This all takes time and if you change more than one page's name it will take a very long time.
* With the labels of the graph they will not change with the data so you end up having to go over the whole process of making a graph again.
Access:
Access also has its advantages and disadvantages, which need to weigh up against Excel's advantages and disadvantages to come up with the right choice of software. Access' advantages include: -
* Access can perform advanced queries.
* Access can make forms to insert data.
* Access is very user friendly.
* In Access you can have macros to go between forms and queries.
* Access has lots of facilities which help to improve the appearance these include- Word Art, Paint, text and line fill, Spell check, Clip art, Importing pictures
* Access is not a flat file database so you can also create relationships between tables of data.
* It has a query language, which means it can perform advanced searches.
* You can hide certain forms out of view.
* You can put a password on the system to prevent people tampering with it.
* There are writing tools including spelling and punctuation tools available.
* Access can have relational tables, which are linked together. This avoids repetition in entering in data as more than one department of the system can share one bit of data.
Access has disadvantages as well as advantages these include: -
* Access is more sophisticated than Excel and is therefore harder to use for a novice.
* Access is used by professional system designers who design systems for end users. This means the system must be very powerful and very detailed. However it is more complicated for a non-expert to use.
High Level Language Such as Pascal
I have decided not to use a high level language, as I do not have the necessary knowledge to produce a system written in a high level language such as Pascal.
Advantages
* It can be customized to suit the company's needs.
Disadvantages
* Developer does not have sufficient enough knowledge.
* I would take a long time to code some areas where as in Access they are easily done in tables.
Evaluation of possible software solutions
I have decided to recommend the use of Access to the clients as being most suitable because it seems to offer more facilities and features, which will help me in designing a program for the, clients.
Access is more usable than both Excel and Pascal and the end users will find the system easier to use as the system will save time by cutting out repetitive tasks and will give them quick access to the information they need. A system designed is Access should give a very user-friendly interface and will function in the way the client's want in to. Access is a quick database and is quick to open files and change to new pages. Access will really provide a solution to the client's needs, as it is a diverse program, which can be used in many different ways. Another very good thing about Access is that it will be very easy to upgrade, introduce new functions and make modifications as and when they are necessary.
Comparison of Possible Solutions
A number of possible solutions could be considered:
* Use of Excel Spreadsheet
* Use of Access Database
Excel is a spreadsheet of considerable power including database facility. However the relationships required would be difficult to implement in the time scale available. The final solution might also be difficult for the end user to understand and maintain.
Access has all the capabilities required and is the preferred option for the end user. I have experience of using Access and some Visual Basic knowledge that can be used for enhanced customisation.
Using Access it will be possible to:
* Create and edit tables
* Create and edit relationships
* Create forms and reports based on tables
* Create customised input screens and using Visual Basic facilitate searches and validation procedures
* Create queries to find specific data
* Create a menu driven system
* Implement security provision.
The system should take approximately 4 months to complete and has the proposed delivery date of the 30th of April. It will come complete with a progress report, a system guide and details on the testing completed.
Database Design
The Database contains FOUR entities:
* Staff
* Videos
* Members
* Rentals
These are related as shown below (in normalised form illustrating many to one relationship from rentals table to the other three).
These relationships will be confirmed in the database relationship facility.
Data Requirement Definition (partial data dictionary)
Video
Attribute name
Comments
Data type and length
Validation
Video ID
AutoNumber
Unique Primary Key
Video Name
Text (50)
Genre
Combo Box- (A list of different genres)
Text (50)
Certificate
Combo Box- U PG 12 15 or 18
Text (50)
Price (3 Nights)
Combo box- £2.00, £2.50 or £3.00
Currency
Staff
Attribute name
Comments
Data type and length
Validation
Staff ID
AutoNumber
Unique Primary Key
Surname
Text(50)
First name
Text(50)
Address
Text (50)
Town
Text (50)
DOB
Date
Phone Number
Text(10)
Customer
Attribute name
Comments
Data type and length
Validation
Customer ID
Automatically incremented
Long Integer
Unique Primary Key
Title
Combo box- Mr, Mrs or Miss
Text (50)
First Name
Text(15)
Surname
Text(15)
Address
Text(50)
Town
Combo box
Text(50)
postcode
Text(10)
DOB
Date/Time
Rentals
Attribute name
Comments
Data type and length
Validation
Customer ID
Automatically incremented
Long Integer
Unique Primary Key
Staff ID
Automatically incremented
Long Integer
Unique Primary Key
Video ID
Automatically incremented
Long Integer
Unique Primary Key
Date Rented
Date/Time
Date of Return
Date/Time
Cost of Rental
Currency
Design of Input Forms
Four Data Input Forms are needed
* Customers
* Staff
* Videos
* Rentals
Customers Form
This will be needed to input and update the Members of Video World. Customers are automatically numbered. For this reason automatic incrementation is used. This form will be accessed from the editing form, which is reached from the main form. There will be a button to add customers and delete customers and also a find button. In addition the end user wished for a sub form that detailed the videos that the customer had already rented out. There will be a button to re-enter the editing form.
The Staff Form
This will be needed to input and delete the staff of Video World. Staffs are given automatically incremented ID numbers. This form will also be accessed from the editing form. There will be a button to add and delete staff. There will be a button to re-enter the editing form.
The Video Form
This will be needed to input and delete the videos in Video World. Staffs are given automatically incremented ID numbers. This form will be accessed from the editing form. There will be a button to add videos and a button to delete videos and a list box will be used to view details of videos already allocated. There will also be a find button to locate any video that needs editing or deleting. There will be a button to re-enter the editing form.
The Rentals Form
This will be needed to input the rented videos. This form will be accessed from the Main Menu. There will be a button for return videos and a button to delete videos and a list box will be used to view details of videos already allocated. There will also be three subforms that will show data once the IDs of staff, customers and videos are entered. There will also be a button to re-enter the Main Menu.
Menu Design
The menu structure will be based upon the following plan
Report Design
The design of the reports shall be generated using Access facilities to provide a professional look. Abdullah would like the reports to show a lot of detail in a small space and so I think that a columnar report will be best suited.
Queries
Queries are an integral part of the operation of any Access Database. The queries implemented will enable reports to be formed, which will enable the specification required by the end user to be fulfilled:
Security
A password will be attached to enable Abdullah and his staff to have immediate access to the Database. There is no need for different levels of security.
List of entities and attributes
Member
Member ID
First name
Surname
Address
Post Code
Telephone number
videos
Video ID
Title of the video
Video ISBN number
Author
Edition number
Cost
Publisher
Borrowing
Transaction number
Date of borrowing
Member ID
Transaction line
Transaction number
Video ID
Date of returning
Reserving
Reserving Number
video ID
Member ID
Date of reserving
Advantages gained by using Computerising the system
* Staff can enter information easily and quickly which will save time and money
* Staff can find details about members or videos very easy.
* Accuracy
* It can store large amount of date
* Speed of Access
Limitation
* Expenditure for hardware and software
* Training manpower
* Coping with technical failure
* Paying for software licence
Security measure
Physical environment includes:
Fire is the most hazards to computer systems. : A proper fire safety plan is an essential feature of security procedures. In order to prevent fire, site must be built in appropriate material, fire doors are clearly marked, and smoke detector and distinguisher are placed around the building.
Water such as flooding; computers must not be placed in lower levels or in basement
Heat, dust, etc.
Work place must maintain good ventilation and also maintain good cleaning guidelines
Good Management measures include:
* Maintaining and inventory of departmental computer equipment.
* Maintaining an inventory of other computer equipment in use by the department.
* Log out system
* Sensitive information must keep in safe or in another building.
* Identifying appropriate levels of security relative to risk.
* Routinely backing up and another copy must be saved in another site.
* Routinely check for and update programmed threat detection tools as needed as well as train users in virus prevention and recognition techniques.
Data integrity
* Data integrity means, in part, that you can correctly and consistently navigate and manipulate the tables in the database. it ensures that the value of the primary key can never be a null value.. The integrity rule requires that insert, update, and delete operations maintain the uniqueness and existence of all primary keys.
* Referential integrity. Foreign keys are used in relational databases to implement relationships between the records in different tables. For example, in an invoice table, customer number is a foreign key that is used to refer to the customer number primary key in the customer table. Referential integrity ensures that all foreign keys in one table have matching primary key values in the related table. In the invoice table example, if a customer record is deleted in the customer table and the deleted customer record has one or more invoices in the invoice table referencing the customer table, there is referential integrity error.
Tabular analyses
Name of attributes
Input Forms
Output Reports
Member form
Borrowing form
Vidoes
form
Reserving form
Overdue
report
Reserving
report
Videos out report
Member ID
X
X
X
X
X
X
First name
X
X
X
X
X
X
Member Surname
X
X
X
X
X
X
Address
X
X
X
X
X
X
Post Code
X
X
X
X
X
X
Telephone number
X
X
X
X
X
X
Post code
X
X
X
X
X
X
Videos ID
X
X
X
X
X
X
Title of the video
X
X
X
X
X
X
Videos ISBN number
X
X
X
X
Author
X
X
X
X
X
X
Edition number
X
X
X
Cost
X
X
X
Fine
X
Transaction number
X
X
Date of borrowing
X
X
X
Date of returning
Reserving Number
X
X
Date of reserving
X
X
(E-R diagram)
Borrowing Table
Member table
Transaction line
Video Table
+
Reserving Table
Data dictionary
Table
Field name
Data type
Length
DEC
Description
Primary key
Foreign key
Member
Member ID
NU
6
0
Unique Member identity
P
Member surname
CH
20
-
Member last name
Member first name
CH
20
-
Member first name
Member address
CH
50
-
Member address
Member telephone number
CH
5
-
Member telephone number
Videos
video ID
NU
0
-
Unique video ID identifier
P
video Title
CH
50
-
video title
Author
CH
30
-
Author name
ISBN
NU
2
-
video ISBN number
Cost
NU
4
2
Price of video
Edition
NU
0
-
Edition number
Publisher
CH
20
-
Name of the publisher
Borrowing
Transaction number
NU
5
-
Unique Transaction number
P
Date of borrowing
DA
8
-
Date of borrowing
Member ID
NU
4
-
Unique member identity
F
Transaction line
Transaction number
NU
5
-
Unique transaction identity
Compound Key
Video ID
NU
6
-
Unique video identity
Date of returning
DA
8
-
Date of returning the video
Reservation
Reservation Number
NU
6
-
Unique reservation identity
P
Video ID
NU
0
-
Unique video ID identifier
F
Member ID
NU
6
-
Unique member ID identifier
F
Date of reservation
DA
8
-
The date of reservation
Domain
Domain value
ID number
Integer
Names
Character
Date
Date values
Currency
Sterling
List of all attribute and its domain values
Attributes
Domain
Member ID
ID number
Surname
Names
First Name
Names
Member address
Names
Date of borrowing
Date
Date of returning
Date
Author name
Names
Reserving number
ID number
Transaction number.
ID number
video ID
ID number
Date of reserving
Date
Cost
Currency
Year of publication
Date
All attributes which are likely to be generated internally to the system are:
-Member ID (Auto number)
2-Transaction number (Auto number)
3-overdue days 4- The end date to keep the reserved video
Normalisation of First normalisation form INF
The in the table for member contains
Member ID
Member name
Member surname
Member address
Post Code
Telephone No
From the above we can say that all the attributes values are atomic. We can do more normalisation to the member address to address 1 and address 2, but we do not need it. So the member table is normalised to 1FNF
To check for the 2 NF. We found that every non-key attribute is functionally dependent on the unique identifier (Member ID). So if we No the member ID, we can determine first name, second name, address and telephone number.
To check for the 3NF, It shows that all non-key attributes are mutually independent
The second Table is video table. It contains:
Video ID
Title
Publisher
Year of publication
ISBN number
Author name
All the attributes values are atomic, therefore it is normalised to the 1NF
To check for the 2 NF. We found that every non-key attribute is functionally dependent on the unique identifier (video ID). So if we No the video ID, we can determine all video's details
To check for the 3NF, It shows that all non-key attributes are mutually independent. There is no calculated attributes in the entity
The third table is borrowing, it contains:
Transaction number
Member ID
Date of borrowing.
All the attributes values are atomic, therefore it is normalised to the 1NF
To check for the 2 NF. We found that every non-key attribute is functionally dependent on the unique identifier (Transaction number). So if we No the transaction number we can determine the Member ID and the borrowing date
To check for the 2 NF. We found that every non-key attribute is functionally dependent on the unique identifier (transaction number). So if we know the video ID, we can determine all video's details
To check for the 3NF, It shows that all non-key attributes are mutually independent. There is no calculated attributes in the entity
The forth table is transaction line
Transaction number
Video ID
Date of returning
All the attributes values are atomic, therefore it is normalised to the 1NF
To check for the 2 NF. We found that every non-key attribute is functionally dependent on the unique identifier (Transaction number and Video ID).
To check for the 3NF, It shows that all non-key attributes are mutually independent. There is no calculated attributes in the entity.
The fifth table is for reservation
Reservation number
Video ID
Date of reserving
Member ID
All the attributes values are atomic, therefore it is normalised to the 1NF
To check for the 2 NF. We found that every non-key attribute is functionally dependent on the unique identifier (transaction number
To check for the 3NF, It shows that all non-key attributes are mutually independent. There is no calculated attributes in the entity.
The Data Flow
Data Flow Diagram
To help me design an effective and structured database system I have drawn a data flow diagram to show the logical movement of the data through a system, however it does not show how the data is stored.
There are different levels of data flow diagrams, I have drawn a level 1 diagram that can also be known as a context diagram. A data flow diagram has many symbols, which mean specific things the symbols I have used are: -
* External Entities - can also be known as a data source or destination. In the following diagram the Customer is the External Entity.
* Processes - are operations performed on the data. In the following diagram there are five processes each named P1, P2 ...etc.
* Data Stores - are logical stores for data, which are not physical in the following data, flow table they are labelled D1 and D2.
Entity-Relationship diagrams
Entity-relationship diagrams are diagrams, which show the relationship between entities in a database. Entities are things of interest to an organization about which data is held. In order to be able to draw an entity-relationship diagram you need to know the degree and the name of the relationship. The degree of the relationship will come under one of three categories:
. One-to-one, e.g. relationship between Person and National Insurance number which is unique to every person.
2. One -to-many, e.g. relationship between Customer and Order.
3. Many-to-many, relationship between Customer and video
System Design
Top Down Design
A top down design is the technique of cutting down and simplifying a complicated problem into the major tasks which need to be completed and then cutting down the major tasks into modules. It is mainly used in programming but the principle can be used to help design my system.
Modular Design
The system can be divided into different modules, and these modules can then be drawn in a structure diagram to show how the modules relate to one another and to form the whole solution to the problem. On the following page there is a structure diagram of the proposed system.
USER INTERFACE
When thinking about the user interface you should think about who the system is for, what tasks the system is performing, and what environment the system will be in and whether the technology is feasible.
Outputs
There will be two main outputs of my system and all need to be user friendly.
Tables
Video table
Borrowing table
Member table
reservation table
Queries
Video query
videos out
Request
Reservation
Fine
Notification
Full details
Forms
Full details form
Borrowing form
Member form
reservation form
Reservation form
Transaction form
Video form
Full menu form
Full details
Review reports
Printing reports
Opening forms
Reports
Overdue and fine reports
Requesting report
Videos out report
Tables
The tables are linked together to make a relational database. The tables are linked by means of a common field. One of the common fields must be a key field and the other is known as a foreign key.
The purpose of having a relational database is that by linking tables you can use data from all of the tables not just one. For example instead of writing the customer's information twice, once in the customer table and once in the order form, you can just enter in the customer ID in the order table and the information will come up, this is all down to the relational links.
Entering data
Now that the structure of the database is in place how the user is to enter in the data is the next task. The entering of the data should be user friendly, quick and as far as possible not prone to human error, because time is very important.
To make entering data easier I have decided to use the auto form function, which is quick and effective at designing simplistic and easy to use forms. All you have to do is follow the various self-explanatory steps, which the wizard takes you through. Below are the forms I have used in my database
USER INTERFACE
When thinking about the user interface you should think about who the system is for, what tasks the system is performing, and what environment the system will be in and whether the technology is feasible.
Outputs
There will be two main outputs of my system and all need to be user friendly. These outputs are as follows:
Query outputs:
The results of my queries could either be displayed in a table or in a form created by a wizard. The presentation of the results would better in a form this is because of a number of reasons, which include:
* It is easier to read from forms than tables.
* Forms display one record at a time, rather than all of them at once, like on a list, which make it harder to isolate one particular record.
* Forms are better for presentation as they can have images on them and you can change the font and size.
* With tables you cannot have buttons, where as, you can with forms.
Over all forms are better for this type of system as they are more user friendly because they look less intimidating than tables and require less work on behalf of the user as they can have buttons on them to take the user to and from different forms and menus.
Each form will have a title to identify it to the user. The forms will not be cluttered and if anything will have a maximum of two images. This will insure that the results are easily readable and not interfered by unnecessary clutter. The field headings will be written in a clear easily read , this will make the information clear and easy to read. This is better than fonts such as Algerian which although is fancy, is difficult to read.
Forms
Methods of Data Entry
The user only needs to enter data into the database about the customer, parcel, destination and order. To make data entry easy I have written instructions for the user on how and what information to enter in but on the whole it is self explanatory and very easy to get used to. Also as explained earlier I have even included list boxes so information doesn't always need to be written in manually by the user thus cutting down on human or syntax error. The screen print below shows one such list box which brings up possible suggestions for peoples name titles.
However if there was a duke the user simply has to type in the title and the system will accept it. This is shown below.
The user will have very easy access to the data entry forms, which requires the user to simply click on the appropriate button using a computer mouse, which will take them to the required form. This will not require users to have knowledge about opening and closing forms in a raw database. Then once in the appropriate form the user will be able to use a keyboard and mouse to type in the appropriate information.
Over all the user will use the keyboard for data entry and will use the mouse for the operation of the system (e.g. buttons).
Tables and relations :
I will be creating a relational database where tables work collectively rather than individually. For this to happen there is a need for links between the tables, the links need to be between one key field in a table and a foreign key in another. The foreign key and primary key must ideally both be called the same thing and spelt exactly the same and essentially be of the same field type so that there is not a syntax error.
Security and Integrity of Data
Integrity
Input Masks
Input masks can be used in fields in tables to format data and have some sort of control over what values can be entered into them. Input masks consist of literal characters along with other special characters, which determine the kind of value that can be entered. Input masks are mainly used in text and date/time fields, but can be used in number or currency fields. I will be using input masks to make sure the users don't make obvious errors when inputting data. By obvious I mean, for example, not putting capitals at the beginning of a name or putting too many digits in a phone number.
List Boxes
List boxes are drop down boxes, which display a range of possible categories for a user to choose from. Once clicked, the chosen value will be transferred to what ever field the box is connected to. This will help to prevent data entry errors or syntax errors and acts as a form of validation.
Safety
Back up recommendations
To insure the safety of the database I would suggest daily back ups are made so that if the system for some reason looses its integrity, the company will have a recent back up to fall back on. This type of a daily back up is called a differential backup. This will insure that the majority of customer's orders will not be affected and therefore the company will not lose customers. The regular backup should be made to a floppy disk, there should be a floppy disk for daily back ups done on Saturdays, Tuesday s and Thursday. Another floppy disk should be used to data back ups on Mondays, Wednesday s and Fridays. This means that if for some reason the system goes down and the daily back up that day had gone wrong, there is always the other floppy disk which even though is dated may still be of some use.
Security
Security is provided by making sure there are safeguards to ensure that data is not accidentally or maliciously corrupted.
Passwords
The system will have a password, which prevents the user from changing the design or appearance of forms. All the user will be able to do is enter the appropriate information in the right sections. The only person who will have total access to all areas both the users and behind the scenes of the database will be the administrator.
Encryption
Encryption is where data appears garbled unless you know how to decrypt the data.
Other Security Recommendations
I would suggest the user have various security measures for the system, these include: -
* A security manager who can oversea and monitor all terminal activities.
* Keyboards and monitors to be locked via a password when they are not being used or are left unattended.
A screen layout for a user interface screen
Screen layout design for open all data input forms
Screen layout design for open all data output reports
Screen lay out for all data input forms
Member Form 1
Book Form
Borrowing form
Reserving form
Specification of the query criteria including any sorting
This query is designed for a report to be sent to the borrowers who did not return the borrowed videos in time and also show how many days these videos been delayed.
Three tables will be used in this query: member table to get member details, borrowing table to get borrowing date and transaction table to get transaction ID and member ID. A calculated field will be generated called days: Date Diff("d"),{Date of borrowing},Now))
In the criteria row of the QBE >14 will be inserted, where 14 are the allowed time for borrowing. This query will calculate the number of delayed days
A simple example of the use SQL
When a user create a query , Access automatically convert his action to programming language called Structured Query Language (SQL)
For example the table blow shows different video with different prices
This SQL allows selecting all videos price more than £20
SELECT: video, cost
FROM; Table1
WHERE. cost)>"20"
video
cost
Computing
7
Access 97
45
Database design
35
Web design
8
The result after this query is applied will be all the books which cost more than £20:
Access
Database design
Report layout designs
Overdue books report
Books out.
Not
ification report
Test Plan
The reason for the test plan is to see that the designed library database is able to function well with different operations
1-member record