Database Design

Introduction

Every Weekend I rent a Video or DVD from my local videostore in Dungannon, and when I ask about new releases the employee behind the desk sits rummaging through lots and lots of file paper. I can see where they have taken down details of the person renting the filming in one column and in another the film number. I think they would be better off with a computer, so I put that idea to them and they said, “how would a computer help us?” so I would like to give them my answer by designing a database for them that not only is organised but very secure.

Explanation of databases and why they would be of use to this system

What is a database?

A database is a collection of information put together in an organised way so that it is easy to retrieve. The first databases systems built upon the SQL standard started to appear at the beginning of the 1980’s from Oracle with Oracle Version 2, and later SQL/DS from IBM, as well as a host of other systems from other companies.

The definition of a database is a systematically arranged collection of computer data,

Uctured so that it can be retrieved or manipulated.

Databases have been a staple of business computing from the very beginning of the digital era. In fact, the relational database was born in 1970 when E.F. Codd, a researcher at IBM, wrote a paper outlining the process. Since then, relational databases have grown in popularity to become the standard.

Data and information

Data is information for example if a student gets a mark in test this is data, but in until you get the total it was out of, the mark stays as data. When we are giving the total mark then and only then is it information. It has been processed into information and is of use. This is the difference between Data and Information:

Data is useless and has no meaning.

Information has meaning and can be used.

Databases on computer

Databases are put on computers for a number of reasons

1. It is easier to search for a record.

2. There is no unnecessary duplication.

3. You can change delete or print from the database.

4. When you print a report it is tidy and easy to understand.

A report is any output, which is produced from the data held electronically in the computer system. Figure 1 will help you understand this process;

Relational Databases

A relational database stores all its data inside tables, and nothing more. All operations on data are done on the tables themselves or produces another tables as the result. The user and application program interface to a relational database is the structured query language. SQL statements are used both for interactive queries for information from a relational database and for gathering data for reports.

In addition to being relatively easy to create and access, a relational database has the important advantage of being easy to extend. After the original database creation, a new data category can be added without requiring that all existing applications be modified.

Employee Employee Address Telephone

Number Name Number

100001 Freely IP 23, Everglade Terrace 725181

100002 Grew TG 24, Everglade Terrace 725981

100003 Strap AJ 8, Bum Town 727389

Each of the rows in this table is called a record, there is record for each employee, and the items in the columns are called fields. So we are able to say without any reservation that the above table is a record-structured relational database

How a database will aid this system

The way the shop keeps records now is really untidy and outdated. With file pages lying about, it is very hard to find out where the videos that have been rented out and who they are rented by and their phone number. I think a table will give the advantages and disadvantages:

Advantages

Disadvantages

Organised.

If the employee is not computer familiar they will need trained.

Easy to search for a record.

Expensive to get installed.

When printing it, it will be professional looking.

User Friendly.

Take up less space.

Visit To The Video Store

I went to the video store and gave them a questionnaire to answer. The questionnaire is in Appendix one. From the questionnaire I found out that the owner is only there on a weekly basis. The employee had a computer to use before but it was stolen in a burglary. So from this I was able to find that the employee has got computer experience. The name of the business is called Cinema World. They keep records on file paper since the burglary. They find this system untidy and want the shop to become paperless. Only the two employees use the system. There is always lose of records but fortunately they are records from months gone by. They don’t keep records and have not got a filling system. The employer always said that if he got a computer in again he wouldn’t know much about the software or hardware to buy.

Aims

My aims are

* To provide an easy to work database for the users.

* To design an organised database.

* To make user names and passwords for them.

* I want to keep it secure so I won’t let them have access to any options to be able to edit or delete the whole database.

* I will try to keep a paperless shop. The only paper I would want is the paper printed to keep manual records.

* Show the employees how to produce a report.

* Show the employee how to run a query.

* Show the employee how to send e-mail.

* To make letter for the customers, mail merge.

Explanation of the entities and attributes

Entities

An entity is a thing or object of importance about which data must be captured. All things aren’t entities – only those about which information should be captured.

Information about an entity is captured in the form of attributes and/or relationships. If something is a candidate for being an entity and it has no attributes or relationships, it isn’t an entity.

Attributes

An attribute describes information about an entity that must be captured. Each entity has zero or more attributes that describe it, and each attribute describes exactly one entity. Each entity instance has exactly one value – possibly NULL – for each of its attributes. An attribute value can be a numeric, a character string, a date, a time or some other basic data value.

I will draw up a table that will help you understand the entity types;

Entity Types

Example

Costumer_ID

Unique number

Customer_First_Name

Customers first name.

Customer_Surname

Customers surname

Customer_Street

Street they live at.

Customer_Town

Town they live in

Customer_Post_Code

Customers post code

Customer_Phone_No

Phone Number

Video_ID

ID Number

Video_Name

The Name of the film

Video_Category

The Category Thriller,horrer,Romantic

Video_Cert

The certificate U,12A,PG,15,18

Video_Rental_Price

The Price to rent out per night

Video_Date_Out

The date rented

Video_Date_Returned

The date returned

Video_Copies

The number of copies.

Rent_Customer_ID

Customer ID number

Rent_Video_ID

Video ID Number

Rent_date_out

Date Of Video Rented out

Rent_date_returned

Date of video returned

Logical data models

Logical data modelling is a graphic-intensive technique that results in a data model representing the definition, characteristics, and relationships of data in a business, technical, or conceptual environment. Its purpose is to describe end-user data to systems and end-user staff.

Various methods of data modelling exist, each using a host of conventions and tools. The most popular approach is called the entity-relationship (ER) approach, developed by Peter Chen in the late 1970s. Although a number of authors and tool designers have modified and expanded ER concepts, most still have a strong Chen flavor. Also, with the introduction of CASE tools, the number of diagrammatic conventions that the data modeler must master has increased sharply. (Taken from http://www.dbmsmag.com/9506d16.html)

Entity Relationship-diagram

1 1

Customer can rent

Many videos

M M

VIDEO is a one to many relationship because one video can be rented by many customers.

CUSTOMER is a one to many relationship because one customer can rent many videos

Explanation of normalisation

Normalisation is a process for analysis, it takes out all the repeating information. It is a step by step method, which produces entities and attributes and reveals the relationship between entities.

Entity types – VIDEO STORE

Unnormalised

Customer (Customer_ID, Customer_surname, Customer_firstname, customer_street, customer_town, customer_postocde, customer_phone (Video_name, category, cert, rental_price, date_out, date_returned, video_copies))

1NF

Remove repeating groups

Customer (Customer_ID, Customer_surname, Customer_firstname, customer_street, customer_town, customer_postocde, customer_phone, Video_ID)

Video (Video_ID, Video_name, Video_category, Video_cert, Video_rental_price, date_out, date_returned video_copies)

2 NF

Functional dependency

Customer (Customer_ID, Customer_surname, Customer_firstname, customer_street, customer_town, customer_postocde, customer_phone )

Rent (Customer_ID, Video_ID, date_out, date_returned)

Video (Video_ID, Video_name, Video_category, Video_cert, Video_rental_price, video_copies)

3 NF

Customer (Customer_ID, Customer_surname, Customer_firstname, customer_street, customer_town, customer_postocde, customer_phone )

Rent (Customer_ID, Video_ID, date_out, date_returned)

Video (Video_ID, Video_name, Video_category, Video_cert, Video_rental_price, video_copies)

Task 4

User Documentation

Step one

In order to load up the spreadsheet you have to click on the Microsoft access icon on the desktop when this has been done then you will have the following appear;

You enter blank database by clicking on the and then clicking on the OK button.

Step two

Once I click ok for a blank databse the computer will ask me to select a place to save my work and what to save it as. I will save mine in my AVCE folder as Videodb.mbd.

Step three

The window for table’s queries forms reports, macros and modules. From here I will click the NEW button shown below.

Then the following screen will appear, I will then click design view which is highlighted below.

After clicking on new the window below will appear. I went to design view and then typed in the name I want the table to be saved as.

Deleting a record

To delete a record I placed a button on the forms as shown below

When I click delete a record a message comes up and asks if you are sure you want to delete the record like so:

To Delete a record click the delete record button.

Adding a record

To add a record in customer or in video I put in the main switchboard add a customer or video like so:

Amending a record

I put in Edit record for each form, video and customer like so:

After I clicked this it brings me to the first record of whatever form I open, customer or video form. I am able to change, edit or delete a record.

How to use a form

To fill a form in first you need to fill in the empty boxes. Some of the boxes will have a combo box. After all the boxes are completed you must save your work. If you need to go back and edit the form you can simply do this by clicking on the design view button on the top tool bar. This can be found at the left-hand side of the screen. This is my customers data entry form to fill it in you must use the next customer ID which is 3 digits long then fill in the customers surname it will automatically start with a capital letter because there is an input mask in. The first name is the same, the street is also the same. The town is a lookup table, which means you choose one from the list. The postcode is an input mask. The phone number is all numbers. The video ID is a lookup table so you could choose the video from the table.

This is a screen dump of my rent entry form. First you must find the customer in the ID field which is a look up. Then find the video they want to rent in the video ID Lookup field then the date must be typed in and if any specific date is mentioned

My other form is the video data entry form which to use you need the video number, name, category, certificate, rental price, the date out, the date returned and the number of copies there are.

Technical documentation

This is my design for the customer table as you can see the first field is the customer ID and it is a number it is also the key field for this table.

This is my design for the rent table there is no key field as there are two foreign key fields which means two fields are the key fields in another table.

This is the design for the video table the key field is the video ID.

This is an example of lookup as the video category has only a few possibilities I put it in a lookup table. Which means on the forms you can chose from the scroll down table. I have displayed how to make one later on in my assignment.

This is an example of validation, as the user must enter in the deposit for the video. It must be between �2 and �10. If it is not then a message comes up which explains you must enter a price between the following range.

This is an example of my input masks, I think best one for this is the postcode as all post codes are the same which is letter, letter number

Entering data into the database

The design screen below will now appear and then you can start to type in the attributes of the entities. These entities will be Customer, Video and Rent.

When we are in the design view we can then enter the data into the Field name column, this will be the Key field that is in the first row. I will not have an AutoNumber as the number that I want will be 5 characters long and AutoNumber’s will start at 1,2,3,4 and so on.

There is a little key shape at the side of the row number that indicates that the patient number is the Key field. In order for me to only allow 5 digits I type 99999 this will allow values from 00001 to 99999 to be entered in this field.

Creating a lookup box

To create a lookup you must first go to the field name you want to be a lookup. At the bottom there are two windows one called general and one called lookup, click lookup and this window will appear and you then click combo box.

When you fill this in and save it you can go to your form and the table you entered, as a combo box will be like this when you click on it:

Creating a relational database

When all the tables have been created then we have to create a link between the tables; this is called a relational database. To do this I must click on a button on the toolbar at the top of the page as follows:

After you click this button, this window will appear and you must link the fields to the others by clicking on the field name and dragging it to another table.

When I drag a field across and unclick in a different table this window opens

This shows the relationship between the two fields as you can see it is a one to many relationship type.

When you click create this creates the relationship and this will happen to the two fields. The line shows they are joined.

Creating the forms

To create the form you click new and this window will appear at this click form wizard and choose the table or query where the data comes from at the bottom of the window

When you fill in the data’s table or query this window will appear. You choose which fields you want to be in the form. Click next and the next window will appear.

This window will ask what set up you want your form to be in I selected justified

Basically this window asks you to select a background.

Click next and the following window comes up.

This window asks what you want to call your form. Type that in and hit finish and up comes your form.

Your form:

To change the form you click the design view button at the top left-hand side. This window will appear where you can change the layout, add in a title and buttons.

Creating A Report

To create a report to need to go to reports and new, this window will appear highlight report wizard then choose the table you want to create a report for.

After you complete the above window click ok and the following window will appear this window is for grouping your report choose one and click next

Then this window will appear which is for sorting your field’s e.g. in alphabetical order or by numeric order.

Click next and the following window will appear. This window will ask you what style you would like for your report and it also gives you a preview of the style, select one and click next.

This window asks you to choose the layout of your report, if you want it in landscape or in portrait view.

After completing the last window this window will appear which asks you to give your report a name as you can see I have named mine rprtReport.

Your report will look like this when you have finished and as in the form you can change the design by click the design view key.

Creating a macro

To create a macro you go to macros and this window will open up. It is blank now but when you fill in the actions you want the macro to carry out it will be complete.

As you can see I want my macro to open a form so I select this in the action I then have to select which for I want opened. I can also select the format in which the window is opened in.

When I am finished I must save it as you can see I have saved it as Mcrvideo.

I then must see if it is working. To do this I go to my form and go to the design of it, I then add in a button and select run macro when this is finished I can name the button. Which I have called close window as shown below.

Creating the main menu

To create the main menu I had to go to tools and add ins, then switchboard manager.

This window will appear, you then click edit to add in the actions you want carried out.

Here I clicked new to name the items on the switchboard and select its actions.

This is the window where I can type in the name, action and where it will take you to when you click on it.

This is what the main switchboard looks like. I put in a second menu and added a button in to take me to it. Its called the video menu.

This is what the menu looks like you can go to the video forms and the rent forms.