AVCE ICT
UNIT 6 Database Design
HMV
I have chosen to design a new database system for HMV for this assignment so that they will be able to store information about their customers, suppliers, employees, order details, different products and shippers.
HMV is the UK and Ireland’s leading specialist retailer of Music, DVD/Video, computer games and related products.
The company operates around 200 stores in key shopping locations nationwide, covering up to over 1million sq. ft. of trading space, as well as having a successful online store at www.hmv.co.uk.
HMV’s very first store was officially opened in July 1921 by the celebrated British composer, Sir Edward Elgar on London’s Oxford Street. Since that time, the company have become better and more popular with the public.
HMV is dedicated to offering its customers access to the widest possible range of recorded music titles, DVD’s and computer games across all formats.
Current problem with the company
The main problem with the company currently is that most of their business is done by paperwork. This can lead to the staffs becoming very busy and not having time to do other work. It is also space consuming because the company will have to buy files and filing cabinets which may get full quickly and they may have to pay more in order to get extra space for the files and records.
Other then these two problems because of it, there is also the fact that it is time consuming as well and will be difficult to find the information they require quickly. This can prove to be a big problem for the company since they are a big business with lots of customers and employees etc. So if they do not organise their filing system properly then it may lead to them losing customers and therefore losing profit and sales.
Data Requirements
The database will be based on a list of entities and their attributes like; order number, product name, product ID, supplier name, supplier address, customer ID, employee first name, second name, employee ID, employee birth date, employee address and phone number.
Customer Table:
Employee Table:
Order Table:
Product Table:
Supplier Table:
This is the list of all the data I will include in the database. There are 6 entities with different attributes. The entities are in bold below with their attributes following:
Customer: (customer ID, contact name, customer address, city, postcode, date of birth, employee telephone number)
Employee: (employee ID, employee name, address, city, postcode, employee telephone number, hire date, date of birth)
Orders: (order ID, unit price, order date, required date)
Product: (product ID, product name, product description, stock)
Shipper: (shipper ID, shipper name, shipped date, shipped via, ship address, city, postcode, country, shipper telephone number)
Supplier: (supplier ID, ...
This is a preview of the whole essay
This is the list of all the data I will include in the database. There are 6 entities with different attributes. The entities are in bold below with their attributes following:
Customer: (customer ID, contact name, customer address, city, postcode, date of birth, employee telephone number)
Employee: (employee ID, employee name, address, city, postcode, employee telephone number, hire date, date of birth)
Orders: (order ID, unit price, order date, required date)
Product: (product ID, product name, product description, stock)
Shipper: (shipper ID, shipper name, shipped date, shipped via, ship address, city, postcode, country, shipper telephone number)
Supplier: (supplier ID, supplier name, supplier address, city, postcode, phone number)
Entity Relationship Diagram
There are 3 types of relationships between two entities. They are:
- One-to-one
- One-to-many
- Many-to-many
Each entity will have attributes associated with it. An attribute is a characteristic, for example the Customer entity will have attributes like; Customer ID, Customer Name, Customer Address, Customer Phone Number etc. Also each entity in a database must have at least one attribute called a Primary Key which individually identifies it. For example Customer ID would be the Primary Key of the Customer entity.
Customer with Order:
Here the relation between the Customer and Order is one-to-many. This is because there can be one customer making more than one order. The primary key that I made on the Customer table is the Customer ID because it is unique to every customer whereas a customer may have the same name as another customer, this is why Customer Name is not made as the primary key.
Order with Product:
Here the relationship between the Order and Product is one-to-many. This is because there can be one order made by a customer for many products. The primary key that I made on the Order table is the Order ID because it is unique with every order.
Supplier with Product:
Here the relationship between Supplier and Product is one-to-many. This is because one supplier can supply more than one product to a customer. The primary key for the Product entity is the Product ID, and Supplier ID for the Supplier entity. This is because they are unique to their entities.
All these tables are linked by at least one field or attribute. The entity relationship diagram shows us how the entities are linked together. This is the entity relationship diagram for all the entities:
This is how my entities are related. A customer can have many orders, and each order can have more than one product then the supplier can provide more than one product.
Design
Form Design:
Here I will show my design for my forms that I will create and show I have created later on in my implementation.
Menu:
Customer Details Form:
Employee Details Form:
Shipper Information Form:
Supplier Information Form:
Order Details Form:
Product Details Form:
All of these forms can be accessed through my menu page (shown first). Each button on the menu page is linked to each of these forms for easy access and exit.
Report Design:
Here I will show my design for my reports that I will create and show I have created later on in my implementation.
Invoice Report:
Implementation
Table
The first thing I did when creating my database system is creating the table. I made 6 different tables which include; Customer Details, Employee Details, Order Details, Product Details, Supplier Information, and Shipper Information.
In my first table (Customers) I entered fields that are relevant to gaining information about the customer, like their name, address, phone number etc. I also entered the data type for that field like whether it was an AutoNumber, Text, Date/Time etc.
After this I added a lookup on the Title field that I entered. By doing so, you can now select which Title belongs to you. I did this by clicking on the Data Type section where it said Text, and selected Lookup Wizard from the list. This window came up
I selected the option ‘I will type the values that I want’ then it took me to the next window where I was asked to enter what I would like to enter in the field.
After typing in all the values I wanted to add, I clicked Next and it took me to the window where I was asked to give this column a label, which I called Title.
I clicked Finish and it was done.
Next I inserted an Input Mask for the Postcode and Date of Birth. I did this by clicking on the Postcode or Date of Birth field, then going to Input Mask on the bottom of the screen and clicked on the ‘…’ symbol
It then took me to this window
I selected the Postcode for when I was creating my postcode, and the Short Date for when I was creating the date of birth section.
Once I clicked next it asked me what placeholder character I would like to use then I clicked Next and then it told me that all the information needed to create the Input Mask has been taken, so I clicked Finish and it was done
After creating the fields, I added all the customer names and details
I did the same thing, including Lookup Wizard, Input Mask, and filling in the details for all my forms (employee, order, supplier, shipper, and product information).
Once I had created and filled in all my forms, I was able to link them together. This is how my forms are linked together:
This relationship diagram shows how each table is linked together.
Reports
Once this was done, I created my reports. I did this by clicking on ‘Create report by using wizard’. Once clicked on this, a window popped up like this
I selected all the appropriate fields to do with the report I was creating (Invoice report). Once I select all the fields I needed I clicked on Next and a next window came up asking if I want to add any group levels.
I clicked Next because I didn’t need any so it took me to the next page which was allowing me to sort records in either ascending or descending order.
Next I was asked how I want the layout of my report to look. I selected Columnar and clicked on Next.
After this I was asked what style I would like to use. I selected Corporate and clicked Next again.
Finally I was asked to give the report a name. I named it Invoice and clicked Finish.
This is what the Invoice Report looked like once I had finished.
Forms
Once all of this was done, I began creating my forms. I made my Main Menu page first so that I know what will be going where. I started by clicking on Forms, then I clicked on Create form in Design view
Once I clicked on this it took me to this page, where I was able to edit it and add what ever button etc. I wanted by using the Toolbox
I turned the background to pink so that it goes well with the HMV logo I added
Since I had started this, I had to begin creating the rest of the forms that will be linked to the buttons created here. I started with the Customer Details form. To do this I had to click on Create form by using wizard which took me to this window
I added all the available fields in the order required into the selected fields section and clicked Next. I was then taken to a screen like this
Here I can choose what kind of layout I would like to use for my form. There are six different types of layouts, but I chose Columnar for mine.
After this window I was asked what style I will like for he form, I selected the style I wanted and clicked Next
The final step was to give my form a title, which I named ‘Customer Details’ then clicked Finish
This is what my customer details page looked like once I clicked on finish
I then added buttons to the page by clicking on the Command Button from the Toolbox and made a little command button on the form where I wanted to put it. By doing so, a window popped up like this
From here I could choose any command for the button I want. For example if I wanted to find a record, I would click on the Record Navigation category, then from the actions section I would click on Find Record. This is what comes up when I click Next
It gives me the option of choosing between giving the button a picture or a name. Once I have selected it, I click Next and it takes me to the next window asking me to give the button a name
I named it ‘Find Record’ then clicked on Finish.
I did this with all the buttons I added in. The buttons I added include adding new records, saving records, deleting records, printing records, opening word and previewing the report. This is how the customer form looked like when I had finished creating it
I did this to all the forms I created. This is how each one of them looks now
Customer Details:
Employee Details:
Shipper Information:
Supplier Information:
Order Details:
Product Details:
After I created my forms, it was time to finish my Main Menu page. I started adding in buttons linked to these forms. I did this by going to the design view of my main menu, then creating a command button on it. When I did this, a window popped up like this
I went to Form Operations from the categories section and from there I went to Open Form.
I then had to select which form I would like the command button to open
For the Customer Details button, I selected the ‘Customer’ form. Then after clicking Next it took me to this next window which I clicked on ‘Open the form and show all the records.’
After clicking Next on this window, I was taken to another window where it asked me to either give the button a name or give it a picture. I chose to give it the name ‘Customer Details’.
It finally took me to the last window where I was asked to give the command button a name. I named it ‘Customer Details’, then clicked Finish and the button was created.
I did the same thing for every button, linking them to their appropriate page. This is how the Main Menu looked when it was finished
Queries
Once I had finished my forms, I went on to do my Query’s. I created my query by going to Queries then clicking on ‘Create query by using wizard’ and this window popped up
Here I was asked to select all the appropriate fields into the query.
Once I selected all the fields I needed it took me to this next window where I was asked whether I would like a detailed or summary query.
I chose ‘Detailed’ and clicked Next which took me to this window where I was asked to enter a name for this query.
I named it Total Price and clicked Finish. This query was now completed and this is what it looks like when I select the query
Next thing I did was to create an update query to deduct the orders from the product table. I did this by clicking on ‘Create query in design view’, then a window like this showed up asking me to select which tables I would like to include in my update query.
I added Product and Order then clicked on Add.
I then added Stock into the Field section by dragging it in there and I also linked the Product ID from the Product table to the Product ID in the Order table
I also added the following formula into the Update To section:
[Product]-[Stock]-[Order]-[Quantity]
This deducts the quantity ordered from the stock level in stock. After the update the new value for the quantity in stock will appear in the Product table.
Testing
This is how I will test the database:
- Test each object (form, report, and query) to make sure it meets requirements.
- Testing each menu item and command button under differing circumstances.
- Testing the effects of invalid and extreme data.
- Testing the system as a whole, checking results against expected results.
(Test No.1)
Here you see how I tested the date of birth field by typing in 54/75/6543
This is what popped up when I pressed enter or clicked away
It does not allow me to enter an invalid date.
(Test No.2)
Here I opened my Main Menu form and click the Exit button near the bottom.
It then closed the form and returned to this screen.
(Test No.3)
I opened the Customer Details form, and then I clicked on the ‘Add Record’ button
It then takes me to a blank form where I can type in new customer details.
(Test No.4)
Once I open the Customer Details form, I go to a record and click on the ‘Delete Record’ button.
When I click on this, the following message pops up. I simply click Yes then the record will be deleted .
(Test No.5)
To print out the form, I simply opened the Customer Details form and clicked on the ‘Print’ button. It automatically printed out my form.
(Test No.6)
I opened up the Customer Details form and clicked on the ‘Preview Form’ button
It then opened up the invoice report.
(Test No.7)
To test the ‘Find Record’ button, I opened up a Customer Details form the clicked on the ‘Find Record’ button
It then took me to the following window where I had to type in the Customer ID number that I was searching for. I typed in the number 2 then pressed Find Next.
Once I did this it took me straight to the customer details of the customer ID number 2
(Test No.8)
I opened the Customer Details form again and clicked ‘Find Record’, then I typed in the letter ‘a’ several times until it finally stopped. I counted it and there was exactly 30 characters there just as I had made it.
(Test No.9)
To begin this test, I first opened up the Customer Details form. Then I clicked on ‘Add Record’. From here I was presented with a blank form to fill out. I simply type in a number first in the postcode section but it wouldn’t allow me to type anything because it is invalid.
This proves that it will not allow an invalid postcode to be written.
User Guide
Firstly you must open the database. You do this by clicking on My Computer, then clicking on ‘HMV Database’
The database should now have opened. The Main Menu form should also have come up automatically when you get on to the database. The form will look like this
You will then be able to click on any of the buttons to get to the desired form. First is the Customer Details button. If you click on this you will enter the customer details page
On this form you will be able to view all the customer’s details and also add new ones. If you wish, you may also use the buttons below the details to do more things. These include; finding another record, adding a new record, saving the form, deleting a record, printing a record, opening word and viewing the invoice report. Once you have finished on this form you may exit the page by clicking on the ‘Stop’ button at the bottom. By doing so you will return to the main menu again.
Next button on the main menu screen is the Employee Details page. Once you have clicked on this button, the employee details form will appear
From here you may use the bottom buttons to; find a record, add a new record, delete a record, print a record or save the form. Once you have finished you can click the ‘Stop’ button to exit.
Next button on the main menu is the Shipper Information button. This button will take you to the following form
On this form you may which to; find a shipper, add a new shipper detail, delete a shipper, save the form, or print the form from the buttons near the bottom. Click ‘Stop’ again and you will return to the main menu screen.
After the shipper information button there is the Supplier Information form. When you click on this button you will be taken to the following form
Here you may; find a supplier, add a new supplier, delete supplier information, save the form, or print the form. Then you may exit from the ‘Stop’ button.
Order Details is next on the list, from there you will be able to find a specific order, add a new order information, delete an order, print an order, save the form, or view the invoice report.
Press ‘Stop’ to return to the main menu again.
Product Details is the last button on the form. It takes you to the page with details about the products. Here you may; find a new product, add a product, delete a product, save the form, or print the form. Once you have finished you may click the ‘Stop’ button to return to the main menu again.
Once you have finished using the whole database system, you may wish to exit it completely by clicking on the ‘Exit’ button on the bottom of the main menu screen.
