video rental database ex 1

5/12/2018 Video Rental Database Ex 1 - slidepdf.com

http://slidepdf.com/reader/full/video-rental-database-ex-1 1/20

Video rentalDatabase

Practice ProjectExercise one

A system for lending

videos by relating lists of

videos and members.

Using tables, setting

validation, avoiding data

duplication, relationships,

forms and sub forms.



Video rental example database Exercise 1In this exercise you will create a relational database that models the rental of

videos. You will also learn how to use data validation, create lookups, set data

types and other field properties, produce queries and create forms.

The main principle seen here is that as little data as possible is duplicated in more

than one table. All the members are listed in the members table; all videos are

listed in a videos table. Loans are recorded in a separate table that brings

together videos and members.

This strategy for reducing duplicate information has a number of advantages:

• Less computer storage and processing resources taken up by the database

• Easier to update member or video information

• More accurate and less prone to errors occurring

1. Create a new database called VIDEOSHOP1

Creating the tables to store the information

2. Now create a new table in design view with the field names and data types as

shown in the diagram.

3. Set MemberID to be a primary key

4. Save the table as tblMembers

5. Make another table as shown

6. Set VideoID to be a primary key.

7. Save the table as tblVideos.

of 12



Video rental example database Exercise 1

8. Now create a final table which store all the information about which members

have which videos. It will have the following fields.

9. Set LoanID to be the primary key

10. Save this table as tblLoans

of 12



Video rental example database Exercise 1Setting field length, validation rules and other field properties All of the strategies that follow are methods of data validation that make

accurate data entry easier and help to reduce errors. You might notice that

more than one method can been used on the same field in conjunction.

Data types

You have already used some basic validation by setting data type restricting the

type of data entered into each field (for example text cannot be entered into a

number field). Setting data

types reduces the likelihood of

entry of invalid data

Field length

In order to reduce errors andsave space in your database set

the field length of text fields to

a reasonable length. Default is

50.

For example

The members Title field need

not be longer than 4 characters

Refer to the table to set the

best field lengths:Setting field length reduces the likelihood of entry of extreme data

Validation rules

A validation rule further restricts

the range of data that can be

entered reducing the likelihood of

errors. In tblMembers: in the

properties box for the Title field

set the validation rule to = “Mr” Or“Miss” Or “Mrs” Or “Ms”.

In the properties for the Sex field

type = “M” Or “F”. Enter helpful

validation text that will display if

the user makes an error.

Validation rules reduce the chance

of entry of extreme data (data

outside of acceptable range)

It is important that members’ dateof birth is checked and validated

of 12

Table Field Field length

tblMembers Title 4

FName 25

LName 25

Address1 25

Address2 25Address3 25

PostCode 8

ContactPhone 11

Sex 1

tblVideos Title 25

Category 20

Certificate 2



Video rental example database Exercise 1so that it is sensible. This will enable age to be accurately calculated and checked

against the certificate of the video that they want.

Simple lookups

Access lets you use two different methods for creating lookups. In this simple

method you follow the wizard and type in a list. This method is more difficult to

edit later that the other method which will use later so make sure that you

get it right first time around.

1. Choose Lookup Wizard from the drop down list in the Data Type field

2. Choose the option I will type the values that I want

3. Follow the wizard and create a lookup for Mr, Mrs, Miss and Ms

of 12



Video rental example database Exercise 14. Create a lookup for categories of films in the videos table

of 12




Input masks vary greatly in their format and can be used to set the format of

data. In our example we will use input masks to ensure that first names and titles

are always text and always capitalised followed by lower case.

Using input masks reduces the likelihood of entry of erroneous data

Extension task:

You can easily look up other input masks in Access help and apply them to other

fields as appropriate

Default values

Where data is often repeated then a default value can be set. Default values are

very useful when you always want to record the exact time that a record was

created.

Set the default value for the Date field in tblLoans to Now().

This will now automatically record the data and time of each loan transaction.

Before you proceed further – enter some dummy data into

the videos and members tables – this will make your work

of 12



Video rental example database Exercise 1easier. Or you can import the data from the spreadsheet

files provided with this resource

of 12



Video rental example database Exercise 1Creating the lookups and relationships

Please note

While it is possible to create and edit relationships directly in the relationships

window; this method uses the lookup wizard to create the relationship.

1. Open the loans table in design view and select the lookup wizard from for the

videoID field

2. Choose the option I want the lookup column to look up values in a table or

query.

3. Click Next and select the videos table

4. Carry over all the fields except rental

of 12



Video rental example database Exercise 15. Ensure that the Hide key column check box is ticked and click Next

6. Complete the wizard

7. Repeat this process for memberID in the loans table selecting LName, FName

and DoB in that order when completing the lookup from the members table.

of 12



Video rental example database Exercise 1Creating lookups and relationships

1. Since the loans table will act as a link between the members and videos tables

there must be a common key in each table that provides the basis for the link.

This is known as a primary key – foreign key relationship. This design

feature of the database will allow individual videos, which only appear once in

the videos table, to appear many times in the loans table and members, who

also only appear once in the members table, to appear many times in the loans

table as they borrow many videos.

2. In order to make a link between tables MemberID and VideoID must be the

same data type in all of the tables in which they appear. In this case

autonumber and number are seen as the same data type

3. The MemberID and VideoID in the loans table, tblLoans are known as Foreign

Keys because the values in them will exist in primary keys in another table.

4. Once you have completed the lookup wizard for memberID and VideoID youshould be able to view the relations that you have created in the relationships

window.

5. Close all tables and click the relationships button on the toolbar

of 12



Relationships


of 12




Editing relationshipsIn order to create a true primary key foreign key relationship you must ensure

that only values that are present in the primary key of one table are allowed in the

corresponding foreign key in the related table. This is achieved using a one-to-many relationship (shown by a 1 and an infinity symbol) and can be achieved by

editing the relationships created by the lookup process

1. In the relationships window double click the relationship between the members

and loans tables. This will evoke the Edit Relationships dialogue

2. Tick all three check boxes and press OK

3. Repeat this process with the link between the loans and videos tables and

ticking the check boxes.

4. If the relationships match the diagram below then close and save.

of 12




1. Create a columnar layout form based on tblMembers using the form wizard.

Include all the fields. You should have a form similar to the one shown.

2. Save the form as subfrmMembers.

3. Repeat this process to make a sub form of the videos table.

4. Save the form as subfrmVideos.

Because the sub forms will be used just to view data - you should remove all

the navigation from the forms and lock the data entry fields1. Open the form in design view

2. Right click the black square in the top left hand corner

3. In the format tab; set Scroll Bars to Neither, Record Selectors,

Navigation Buttons and Dividing Lines to No

4. Close the Form properties dialogue and save the form

5. Still in design view, select all the data entry fields

6. Right click to get the properties of the fields

7. In the data tab; set Enabled to No and Locked to Yes

of 12




of 12

Note how the appearance of the sub form

has now changed. Repeat for

subfrmVideos




of 12



Toolbox button

Video rental example database Exercise 1Creating the transaction form1. Using the form wizard create a form based on tblLoans.

2. Call it frmLoans

4. You will need to use the toolbox. If it is not there press the toolbox button on

the toolbar.

1. Press the subform button and draw an outline on the screen (the exact size isnot important as it will resize to the size of the form).

2. In the wizard, make sure that you select existing forms selected and select

subfrmMembers then press next

of 12

Sub forms button




of 12



Video rental example database Exercise 11. If you have properly named the linking fields (in this case memberID) then

Access will assume that this is where you want to make the link and you will

see this dialogue:

1.

1. Otherwise select the define my own and, using the pull down lists, select

the MemberID from both the form column and the subform column shown.

2. Now select next and finish.

3. Repeat this subform procedure to insert a subform for the video detailsexcept this time create the link using the videoID field.

of 12



Video rental example database Exercise 14. Save the form and view it in datasheet view.

5. Use the drop down buttons to select an existing video and existing member.

Notice how the full details for each item you have chosen pop up in the sub

forms

6. to create a new record click to the new button at the bottom of the form

of 12

video rental database ex 1

Documents