Implementation
A physical implementation of your design using a proprietary database (Access) submitted on a floppy or compact disk. Your database should include at most 5 Test data per Entity and user interface elements (Forms). -10 marks
Learning outcome 3
Presentation
Well-structured overall report, with clearly marked out sections and appropriate use of English, table of contents, appendix section and references. -10 marks
INTRODUCTION
I have chosen to design a database for a hair dressing salon called Ellos y Ellas, . They are a medium sized company that deals with quite a few customers and offer differents kind of services.
ELLOS Y ELLAS
33 CRANBOURNE STREET
LEICESTER SQUARE
LONDON WC2H 7AD
020724066603
The Systems Life Cycle
I am going to document the complete production of the system I am going to use the systems life cycle. The systems life cycle will help me on my approach designing the system. The stages of the There are several variations of the cycle but below is the most common, sometimes the stages tend to change base on the approach that the designer is going to use.
I will use Traditional Waterfall Approach
- Feasibility Study – investigate the system and decide if there is need for a new system and also if the system is basically really needed, base on the total cost of the design.
- Analysis – analyse the requirements and produce a specification
- Design – the design is produced
- Implementation – produce software and install hardware
- Testing – system is fully tested
- Maintenance – there are four types of maintenance:
- Perfective maintenance: where you make the system easier to use or to add any kind of new facilities or updates.
- Adaptive maintenance: where you make changes to suit any kind of changes in working environment.
- Corrective maintenance: where you change something because of an errors or the system has malfunction has been discovered.
- Preventive maintenance: where to made changes to the program to avoid any kind of problems in the future.
- Evaluation – evaluation of the system and if it meets the requirements of the user an any future updates.
SYSTEM DEVELOPMENT LIFE CYCLE
MAINTENANCE PROCESS
SDLC MAINTENANCE PROCESS
ANALYSIS SPECIFICATION AND SYSTEM REQUIREMENTS
FACT FINDING TECHNIQUES
Fact-finding techniques used:
OBSERVATION
No credit cards or debit card are accepted, everything must be pay in cash.
The staff is also pay in cash.
Nobody can book appointments.
All the information is store in cabinets and folders
Very difficult to get information from customers as there is no much space on the salon.
INTERVIEW
The interview was done with the manager of the hairdressing salon.
The potential users of the system will be Alberto and the four shop assistants. I went into hairdressing salon and asked Alberto some questions. Below are the questions and the answers.
- How many customers do you have?
A: between 750 and 800
- Do you store any kind of information about your customers?
A: yes but only personal information
- What kind of information may that be?
A: Things like, name, surname, contact number, address and date of birth.
- How many staff do you have?
A: Only four
- Do you store any kind of information about your employees?
A: yes but also personal information.
- The same kind of information that you store about customers or
any additional kind of information?
A: not additional information, the same information.
- What kind of services do you offer your customers?
A: dyes hair
Haircuts
Hair massages
Shaving
- What are the prices of these services?
A: dyes hair is £35
Hair cuts is £15 for men and £25 for women
Hair massages is £20
Shaving is £10
- What kind of products do you sell here?
KERASTASE BAIN OLEO-RELAX (£10.00)
REDKEN ALL SOFT SHAMPOO (£10.00)
L'OREAL SERIE EXPERT PERM ENERGY SHAMPOO (£10.00)
L'OREAL TECNI.ART - A HEAD CLAY (£10.00)
L'OREAL TEC NI ART AIR FIX FIRM HOLD SPRAY (£10.00)
L'OREAL TEC NI ART GLUE (£10.00)
L'OREAL TEC NI ART 'FIX ANTI-FRIZZ’. (£10.00)
- Have you any knowledge of Microsoft Access?
Not, I do not know how to use Microsoft Access, but I am sure some of the shop assistants do. We could pay a training for our staff if the system improves the service on the salon.
- In what ways will the system help you and the shop?
It will save my staff and me a lot of time when we need to track down the stock or what products are about to finish.
STAFF QUESTIONER
The questioner was done to one of the staff; the point for doing this is because I wanted to get another point of view from someone of the staff.
- Approximately how many customers would you have daily?
___________________________ Customers
- How many of these customers buy hair care products from here? (Please tick)
0-5%
5-10%
10-15%
15-20%
20-25%
25-30%
35-40%
40%+
- What is your basis for seeing and attending customers? (Please tick)
Members (with a member no.)
Regular Customers
Any Customer
Other (Please State) _____________________________________
- What details do you record on customers? (tick as appropriate)
Name
Address
Telephone number
Member number
Other (Please State)
_____________________________
Stock
- How many products do you currently have on stock? ___________products
- Is the system – Please tick)
Computerised
Manual?
- Where do you store your files? (Please tick)
On computer
In a filing cabinet
Other (Please state)
______________________________________________
- How do you separate different files? (Tick as appropriate)
Customers
services
Products
Suppliers
Staff
Other (Please state)
______________________________________________
Bookings
- Do you allow customer to book appointments? (Please tick)
Yes
Not
______________________________________________
Employees and Information
- How many people work here?
_______________________________
- How do you communicate any kind of info information to the other?
_____________________________________________________________________
- What information is passed on to Mr Alberto?
_____________________________________________________________________
- Where is the information found? e.g. customer information?
_____________________________________________________________________
- How do you get the information you need from the customer?
Future System
- Do you think there is a need for a new system?
Yes / No
- Would you like the new system to be (tick as appropriate)
Computerized or
Manual
Other
- What problems do you personally find with the current system? (Please tick)
- Time consuming
- Takes up a lot of space
- Causes loss of stock
- Difficult to use
PROBLEMS IN THE PRESENT SYSTEM
The system Ellas Y Ellas is using now is creating a problem for the company as it has been growing over the past few years.
Too much time lose when looking for any customers files mainly because it is paper base, this causes delays as customer’s personal information get lost or the staff just do not know where that information is.
Personal information that is misplace and the staff could find will tend to create lots of data duplications as the member details will have to be store on the filing cabinet again
Just keeping all this information on the computer will solve this problems.
FACT-FINDING TECHNIQUES INFORMATION
The reason for doing a questionnaire and an interview is that I would get a different point of view, most of the questions are the same with the same answer, but I am getting the point of view of the manager and the point of view of members of the staff.
When I was observing around the salon I saw that it was going to be very difficult to get the customer information as the salon is quite small and there is not space for someone standing around asking questions to the customer, so I came with the idea of introducing to Mr Alberto a collection form as the one below.
Data collection form:
The first seven questions are closed questions, as they have no alternate answer. I put these in as they are needed to store information into the system.
‘How did you hear about us?’ This is an open question as a wide number of answers can be given. This should be asked so they can find out the main places where people are finding out about them.
The system must include the following features:
It must have the ability to add new information, edit/amend information and delete information. In addition, it must have the facility to implement efficient searches-specific searches by membership number, by name etc. The system should make the best use of storage facilities giving easy Access to relevant data for each employee. Finally, the system has to be a user friendly.
THE FEASIBILITY OF A COMPUTER BASED SYSTEM
The are many advantages of using a compurized system for the hairdressing salon Ellos y Ellas they are:
The files can be backed up, if the computer is affect by a virus or the system craches or if there is a fire, then no all files will be lost .
It could be update or change more easily.
The files could be found more easily.
HARDWARE
Mr. Alberto is willing to spend around £1500 on at laptop where the system will be design. With that amount of money we will be able to find a computer more than capable of Carrying out all the tasks that the system will do.
Personally I would recommend a cheaper computer , between £700 and £800 but Mr. Alberto has explain to me that he wants a computer that he can use for other programs and to connect to the internet if he needs to, he also wants various inputs such as scanner, wed cam.
Below are the specifications on a laptop Alberto should be looking for:
- A fast 1.5 to 1.7GHz Pentium 4 processor, so the computer opens the programs very quickly.
- 256-512 MB of RAM to make sure that programs run smoothly and that other programs can be run at the same time.
- A CD-ROM drive to install new programs etc.
- CD re-writer as a form of backup.
- Scanner
- Keyboard to input data into the computer
- Mouse
- Printer-Laser to output customer details etc in very good quality.
- Speakers to output data.
SOFTWARE
Microsoft Access is one if not the most used program to create any kind of database.
As we know not program is perfect and Microsoft Access is not exception.
ADVANTAGES:
- Access can create forms to enter the user’s details of any kind of information that needs to be stored into the system.
-
Access can also uses four different query languages such as DDL “Data Definition Language, DML Data Manipulation language, DCL “Data Control Language” and RGL Report Generation Language.
Which means it can perform advances searches.
- Access has relational tables which are link together to prevent data duplication.
DISADVANTAGES
- Access is commonly use by professionals systems designers , this means the system is very powerful and detailed which could make it more complicated to use for a not expert or without any computer knowledge.
ENTITY RELATIONSHIP DIAGRAMS – CONCEPTUAL MODEL
ENTITY RELATIONSHIP DIAGRAM – LOGICAL MODEL
DATABASE SCHEME
UNF
MEMBERS: (TOTAL_OF_SERVICES, D.O.B, CONTACT_NUMBER,
ADDRESS, L_NAME, F_NAME)
STAFF: (F_NAME, L_NAME, ADDRESS, CONTACT_NUMBER, D.O.B,
POSITION)
SERVICES: (HAIR_CUT_STYLE, SHAVING, PRICE.)
PRODUCTS: (PRODUCT, PRICE, SUPPLIER.)
SUPPLIER: (COMPANY, ADDRESS, TEL_NUMBER)
1NF
STAFF: (STAFF_ID, F_NAME, L_NAME, ADDRESS, CONTACT_NUMBER,
D.O.B, POSITION)
MEMBERS: (STAFF_ID, MEMBER_ID, TOTAL_OF_SERVICES, D.O.B,
CONTACT_NUMBER, ADDRESS, L_NAME, F_NAME)
SERVICES: (MEMBER_ID, SERVICES_ID,HAIR_CUT_STYLE, SHAVING,
PRICE.)
PRODUCTS: (SERVICES_ID, PRODUCT_ID, PRODUCT, PRICE, SUPPLIER.)
SUPPLIER: (PRODUCT_ID, COMPANY_ID, COMPANY, ADDRESS,
TEL_NUMBER)
2NF/3NF
STAFF: (STAFF_ID, F_NAME, L_NAME, ADDRESS, COUNTY, POST_CODE,
CONTACT_NUMBER, D.O.B, POSITION, START_DATE)
MEMBERS: (STAFF_ID, MEMBER_ID, TOTAL_OF_SERVICES, D.O.B,
CONTACT_NUMBER, ADDRESS, COUNTY, POST_CODE,
L_NAME, F_NAME JOINING_DATE)
SERVICES: (MEMBER_ID, SERVICES_ID,HAIR_CUT_STYLE, SHAVING,
PRICE.)
PRODUCTS: (SERVICES_ID, PRODUCT_ID, PRODUCT, PRICE, SUPPLIER.)
SUPPLIER: (PRODUCT_ID, COMPANY_ID, COMPANY, ADDRESS, COUNTY,
POST_CODE, TEL_NUMBER)
DATA DICTIONARY
STAFF TABLE
CUSTOMERS TABLE
SERVICES TABLE
PRODUCTS TABLE
SUPPLIERS TABLE
IMPLEMENTATION
THE TABLES
On the design of this system I am going to use five different tables:
Staff: this table include all the personal information about the staff that works for Mr. Alberto.
Customers: this table will include all the customers personal information.
Services: this table will include all the information about the services offer to the customers.
Products: this table will show all the information about the products that the salon sells or uses for the customers.
Suppliers: this table will show all the companies that supplies products to Ellas y Ellas.
STAFF TABLE
On the right had side of the page we can see a copy of the table on the desing view.
on the staff id the data type is autonumber which means that the number is automaticly generate by the program, this is also the primary key as it is the unique identifier for each member of staff.
on start date and D.O.B (Date Of Birth) the data type is date/time and is set as is set as short date. As you probably notice the data type on the contact number is set as text, the reason for it being text instead of numbery data type is because the numery data type willnor allow a the numbert to start a zero or will not allow a leading zero and the text will.
CUSTOMER TABLE
CUSTOMER_ID
STAFF_ID
F_NAME
L_NAME
ADDRESS
COUNTY
POST_CODE
CONTACT_#
JOIN_DATE
D_O_B
We can also see the table in design view, I already explain the reason of the text as data type on the contact number instead of the numery data type.
On this design the autonumber is set on the customer as this will become the unique idenfier for each member.
The staff id key is also there on this table the data type is number as access will not allow two outonumbers on the same table.
The staff id now has become the foreing key on the member table, this way the relationship between the two tables will be create
Ona members of staff can see/have many customers.
SERVICES TABLE
On this table we got the same as in all the other, I have a primary key (services_id) and a forenin key (customer_id) and like in the tables before this will create a relationship between the services table and the customers table and this realionship will be join by the relationship between staff and customers.
One customer can have many services
PRODUTCS TABLE
The only different in this table is that I have two foreign keys.
One being services_id from the services table and the other one being company_id from the supplier table which is the one below.
As usual I have a primary key (product_id) the two foreing key and the primary will create the relationship between the services table and the products table and the realionship between the suppliers table and the products table.
One services can use many products and many products can have one supplier.
SUPPLIERS TABLE
On this table we got one primary key and one foreign key the relationship between the tables is show above.
RELATIONSHIP BETWEEN THE TABLES
this is a relational database where there is a link between each table, this link is crate between the primary key, the primery is connected to the other table and then it becomes the foreign key on the other table.
This is a copy of the relationships between the tables on my design.
QUERIES
I am using parameter queries for my database, this are a little more complicate than simple queries this is a query that before it runs it as the user to enter the criteria of the information they want to see, after the information has been enter it goes an looks for any member with that membership number or surname.
On my query for customers I have use three different parameter one for the customer id, but probably the customer will no remember it (not membership card will be given to customers.) the the query ask for the customers last name and then it asks for the customers date of birth.
PROTOTYPES OF USER INTERFACE
All the form prototypes will tend to change this is only a basic idea of the user interface will look like; changes will be make to make sure the system is a easy to use as possible.
Figure 1.1 will be the main menu, probably a couple of changes after the design has been finish but this is the basic idea.
Fig 1.1
Figure 1.2 will show the user all the customers but depending on which member of staff has attended them before and the form is call customer by staff.
Fig 1.2
Figure 1.3 will show the products existing and new products and at the same time, the company that supplies those products that may come into the store the form is call products.
Fig 1.3
Figure 1.4 will show the services that each customer requires and them the will be save on the database as a way to keep record of all the customers, this form will be called services.
Fig 1.4
Figure 1.5 shows the new customers form, the new customer information will be enter base on which member of staff the customer wants to be seen by.
Fig 1.5
Figure 1.6 will allow the user to enter details of any new member of staff that may start working on the future.
Fig 1.6
Figure 1.7 will show the form to enter any new supplier that the shop could start using on the future.
Fig 1.7
EVALUATION
So far my design seems to work well, maybe I would change a couple of things like putting the date and time on the all the forms.
I would also start to do it a little bit sooner and that would leave me more time to look over it several times and spot any error on the program.
I got the feeling that something is no working right on the database but I haven’t been able to find out what that is apart from that I am very happy with the fact findings techniques I used.
But there are a lot of thing that I need to work on a little be harder.
REFERENCES
- ACCESS 97, FURTHER SKILLS
By Sue Coles and Jenny Rowley
2001
- Requirements Analysis and System Design: Developing Information Systems with UML
By Leszek Maciaszek
2001
CARLOS MONTOYA SYSTEMS ANALYSIS
