Modelling a database - Sample Essay

Рis project will contain information on the processing involved in modelling a database for a Garment Manufacturing company. It will also document how to create letter and fax templates that make the work more efficient. The database will hold details about the suppliers, customers and stock etc. The garment style, size, how many ordered and deadline dates for the garments to be finished will also be data which is stored in the table. The end-user Mr Jordan will be able to recall information on any of his suppliers or customers.

He will be able to link what the customer orders to what he has to order from his suppliers or if the customers provides their own supplies he will see the amount needed. Mr Jordan will be able to send letters automatically to his customers and suppliers about work and orders. The database will be developed using Microsoft Access, the supplier and customer letters will be created in Microsoft Word and merged with data from Microsoft Access. Background to Organisation The Garment Manufacturing Company is called Air Ltd, it is located in Stratford and is owned by Mr Jordan. There are nine workers, which Mr Jordan is in charge of. He takes care of every aspect of the business himself apart from parts of the accounts. All of the other nine workers are arrange in a production line around the work place.

Mr Jordan deals with the customer enquiries, orders and suppliers etc. At the moment letters are created from scratch in Microsoft Word. Information Technology is not used much as it could be at the present time. Current procedures Customers get into contact with the company and talk about their order and the type of work required to meet their needs. This would include the design styles, materials and number of garments etc. Either the customer provide their own materials or they set out their order to Mr.

Jordan so he can go to his supplier. The company then makes a sample garment which is shown to the customer if they are pleased with it then production starts, if not then amendments are made. At the moment if letters need to be sent out to customers or suppliers they are created individually instead of making a template and using mail-merge. From customer orders Mr Jordan notes down how many garments to be made, style of garment and size on paper, all of this documentation is kept in a folder. Image of Mr Jordan’s customers order detail sheet. [image002. jpg].

The customer detail sheet is in tabular form where records are filled in about each of the individual garments. For example data such as size, colour and style etc. would be held on the sheet. Mr Jordan takes care of all of these sheets in a folder. When they are out dated and are no, longer of any use they are disposed of. Strengths of the current system The strengths of the following system are: i?? All of the workers are used to the current system this means that nobody has to be trained to use any new equipment. For example none of the workers have to be trained on how to use computers. i??

Most work is carried out by hand, this is a strength because if machines were used and any one of them broke down the production of the garments would have to be stopped. Weaknesses of the current system The weaknesses of the current system are: i?? All documentation is carried out on paper. i All letter to customers are created individually. i?? The business does not use e-mail to communicate with its customers or suppliers. i The business does not have a web site advertising its service and goods. i?? If vital business documents are lost, there is no backup. i There is no form of a factory outlet. Objectives for the new system i?? Store details about the customers permanently.i

Store details about the suppliers permanently. i?? Easily be able to input data concerning the ordering of materials. For example, style, material, size, colour etc. i?? Be able to print out order details for the worker to use. i?? Check his status with the suppliers. For example, material ordered etc. i?? Send out letters to customers using mail merge for better efficiency. i?? Print out an update of all deadlines to be met. End-user and functional requirements Store details about the customers permanently The database will need to store details of the name of the company, its address and the telephone number.

The user will sometimes want to display details of all the customers. It will be useful if the customer company names are in alphabetical order. The user might want to send the customers letters which means that the postcode will have to be stored in a separate field. Store details about the suppliers permanently The database will store details about the suppliers. For example the user will sometimes want to list the names in alphabetical order this means that the first name will have to be stored in a separate field to the middle and last name.

If the user wants to send the customers letters to inform them about orders etc he will need to store the post code part of the address in a different field. Store details about the suppliers permanently The database will store details about the suppliers name and address. The suppliers table will be similar to the customer table. But the user will send letters to the suppliers concerning the orderinf of raw materials etc. Easily be able to input data concerning the ordering of materials. For example, style, material, size, colour etc. The user will easily be able to in put the data using Microsoft access instead of inputting the data on paper using a hand made table where he could be creating a more efficient table using access.

Be able to print out order details for the worker to use. The user will be able to print out details of the customers orders. For example, the number of garments, style, material etc. Check his status with the suppliers. For example, material ordered etc. The user should be able to access details about what he has ordered and what there is to pay at the touch of button Send out letters to customers using mail merge for better efficiency. The user will be able to send out letters to the customers and suppliers quickly and efficiently using mail merge with Microsoft.

Access and Microsoft Word Print out an update of all deadlines to be met. The user will be able to print out a list of all deadlines to be met of all of the customers so the workers can work efficiently. Working Constraints End-user skill level The end-user(s) have used modern computers before based upon Windows 95. The user has used a modern computer to create letters and do calculations on Microsoft Excel. The user can only do simple calculations and nothing too complicated. The user can create letters for suppliers and customers by using a word processor. Mr Jordan is new to Microsoft Word but is getting the hang of it.

He does not know much about the advanced features of the software. Mr Jordan will need some form of support in using the advanced features. Mr Jordan can use any simple software, but will have to apply himself slightly to be able to get the hang of the advanced features. His experience with IT is very limited. The new system must be easy to understand so that the vital functions can be successfully carried out. No errors should occur, everything should be clear. Availability of hardware and software The previous software that Mr Jordan was using is called Lotus Word Pro96 but has recently purchased a new PC with Microsoft Office 2000

on it. This new computer is manufactured by Compaq and has the following specification: i?? 900Mhz processor i?? 64Mb RAM i?? 13Gigabytes HDD i?? 17 inch VDU i?? Cannon Bubble Jet printer BJC4200 i?? Microsoft Office 2000 Mr Jordan has an ideal specification if not too good for the functional requirements. The software in the school will easily correspond with Mr Jordan’s PC since in school Microsoft Office 97 is available. Time constraints The deadline for the project is to be completed for the 30^th March. This gives approximately nine weeks for development, testing and documentation.

Documentation will take up a great deal of the time this means that time will be a major constraint upon the completed system. Initial time plan Consideration of solutions Data such as company names and address’s will need to be stored along with ordering details etc. this means that a database must be used. A word processor will also be needed because letters have to be created for the customers. Mail merge could be used. There are two choices for the type of database: relational or Flat-file. Flat-file A Flat-file database will not be suitable because it would mean that all of the field would have to be stored in one table. For example, supplier, customer and order fields will all be in the same table.

This would then lead to there being data in the table that is not needed. For example, each time a customer made an order their name and address will have to be recorder in that record. If this type were to be used then it would take up a lot of space and hold useless data. It would also make it hard to search in the basic ways that you do. It will be hard to develop it so that the end-user will be able search for a certain supplier or customer without all of the other irrelevant details coming up. It would also make it increasingly difficult to use the mail merge facility.

Concluding this we now know that a spreadsheet is not suitable for our purpose because it is just like a Flat-file if not nearly enough the same thing. Relational database This form of storage is much more flexible than Flat-file because it allows the data to be split up into separate tables. This is more efficient because each table contains information about a single entity. Now that information is in its individual tables, data redundancy is avoided. By linking all of the tables the whole structure will be able to be searched in a variety of ways. There are a great amount of relational database options in the market but the two main ones are Microsoft Access and Lotus Approach. These are both very suitable choices.

Out of the two I will use Microsoft Access because it is more common compared with Lotus Approach and also because I am more familiar with the Microsoft environment. Overall I feel that Microsoft Access is easier to understand the instructions are clearer which makes it easier to follow. The terminology used in Access is better than that used in Lotus Approach. The form will be used to enter record of orders, suppliers and customers etc. This will make the business more organised and thus more efficient.

The queries will be used to see who is ordering what and how much is owed to suppliers or owed by customers. To produce the templates for the letter I have chosen Microsoft word because it can easily be linked to Microsoft Access. It will also enable the letter to look professional as you can set any of your own preferences. For example, margin width, header and footer etc. Functions in Microsoft word such as MacroButtons, Date fields can make the end-user develop the letter template for his suitability. Other functions like AutoCorrect and AutoText will give the user an option for creating many types of customised letters for different kinds clients.

My other options for word processors were Lotus WordPro, WordPad, Microsoft Publisher, Microsoft Paint and Word Perfect. These were not as suitable as Microsoft Word because some of them do not allow me carry out the advanced features that I wish to and also because most of them will not be able to link with Microsoft Access. File names, file location and installation There will be two files – a database and a letter template. Name of database file – AirLtd. mdb Name of letter template – StandardLetter. dot. These files will be stored in different locations on the end-users local computer. AirLtd. mdb C:\My Documents.

The entities contain no repeating attributes, there is no irrelevant data held. The entity relationships look like this: [image006. jpg] Field definitions Customer Table Field Name Data Type Comments Validation CustomerID AutoNumber Automatically generated Primary Key Self-Validating Company Name Text (Size 30) Enter company name – maximum 30 characters Presence check Contact Name Text (size 50) Enter company name – maximum 50 characters Presence check Address 1 Text (Size 30) Property number and road name Presence check Address 2 Text (Size 20) Town Presence check Address 3 Text (Size 20) Region.

Presence check Post Code Text (Size 6) Enter Post Code Format Telephone Number (Size 14) Enter the land line phone number Format Fax Number (Size 14) Enter the land line phone number Format E-mail Text (size 50) Enter the e-mail address Presence Check Order Conditions Table Field Name Data Type Comments Validation CustomerID AutoNumber Automatically generated Primary Key Self-Validating Date Ordered Date (Short) Enter date Self-Validating and Presence Check Time Given Enter amount of time given Profit Available Currency Enter amount Self-Validating and Presence Check Order Table Field Name Data Type.

Comments Validation CustomerID AutoNumber Automatically generated Primary Key Self-Validating Style Text (size 50) Enter company name – maximum 50 characters Presence check Material Text (size 50) Enter company name – maximum 50 characters Presence check Colour Text (size 40) Enter company name – maximum 40 characters Presence check Size Number Number Presence check Fittings Text (size 70) Enter company name – maximum 70 characters Presence check Supplier Table Field Name Data Type Comments Validation SupplierID AutoNumber Automatically generated Primary Key Self-Validating Supplier Name Text (size 40).

Enter company name – maximum 40 characters Presence check Contact Name Text (Size 60) Enter company name – maximum 60 characters Presence check Address 1 Text (Size 30) Property number and road name Presence check Address 2 Text (Size 20) Town Presence check Address 3 Text (Size 20) Region Presence check Post Code Text (Size 6) Enter Post Code Format Telephone Number (Size 14) Enter the land line phone number Format Fax Number (Size 14) Enter the land line phone number Format E-mail Text (size 50) Enter the e-mail address Presence Check Account Number Number (Size 8) Enter number (8 digits) Format and Presence Check.

Queries Query Name Type Tables Used Action Payment Overdue Select Forms General Properties All of the forms will contain the following features: Background – Transparent Titles – font = Times New Roman;

Size 18 Labels – font = MS Sans Serif; Size 8; Type plain Text Boxes – Style = sunken / raised; Backstyle = flat; sizes 3 or 4cm Buttons – Width = 2. 3; Height = 0. 9 List boxes – normal Short Cut Keys The following short cut keys will be used on all of the forms: [ALT] + H = HELP [ALT] + C = CLOSE [ALT] + A = ADD [ALT] + D = DELETE

References Visible links Hidden links: 1. http://www. coursework. info/.