Planning A Relational Database

First Steps in Creating the Working Version of Your Database Project. The planning you do at this stage can form part of your project write up. However, the main purpose of the steps below is to make sure you know what to do and to write Remember – all we are doing at this point is to create a working version – later on (not now) we will need to repeat this process and take screenshots as we go. Creating the database does not take long once you are familiar with what you intend to do – taking screenshots now, for the implementation, will just slow you down and ruin your clarity of thought. Step 1. Planning the OUTPUT from the system See example in appendix one a. Draw out a rough plan of the things your system will create – such as reports. In our example a library will need i. Library cards ii. Book list – for various reports iii. Letters to borrowers of overdue books (to the borrower and to their form tutor / year head / parents) iv. List of loans – for various reports Step 2. Plan the FIELDS See example in appendix two Look through the output and identify each of the separate pieces of information – such as name, date, address, library card number etc – each of these, and some you have missed, will be the fields you need in the next step. Step 3. Planning out the TABLE structure See example in appendix three a. If you’re not too sure what tables and fields to use then start with a single table. Don’t worry too much about data types, field sizes, validation, input masks and such things – we’ll come to that later. Use the example in appendix one below and fill in all of the fields you think you will need. b. Fill in four or more examples of the data you will use. Step 4. ORMALISIG (Making the Database relational) See example in appendix four Carefully examine each of the fields in turn and ask your self the question. Will there ever be an occasion where the information in this field will need to be written into the table more than once ? Don’t confuse this with the display of this information on more than one report. If your answer to that question is yes, then that information needs to be in a separate table. Whenever the information needs to be referred to it should be retrieved from that table – using the unique key field associated with the piece of information. Doing all of this undoubtedly seems a bit of a pain, but its needed for the coursework since properly normalised databases (relational databases) only have one copy of each bit of information, so they run quicker and there is less chance of inconsistencies within the data.

Appendix two

List of borrowers Forename, last name, id number, class, form teacher, head of year

Letters to borrowers Address line 1, Address line 2, Address line 3, Address line 4, Postcode, Forename, last name, date of loan, date due back, title of book

List of books Title, Author, ISBN, Publisher, Book number, format (hardback etc), subjects area covered, topics covered, review,

Library card Forename, last name, id number, name of school

Appendix one

Borrowers head of year

Borrowers form teacher

Borrowers class

Date loan due back

Date of loan

Borrower Post code

Borrower address line 4

Borrower address line 3

Borrower address line 2

Borrower address line 2

Borrower address line 1

Book Review

Topics Covered

Subject covered

Book format

Book number

Book publisher

Book ISBN

Book author

Book Title

name of school

Borrower id number

Borrower last name

Borrower Forename

Appendix three

Borrowers head of year

Mrs Smith

Borrowers form teacher

Mr Wood

Borrowers class

11 South

Date loan due back

06/01/09

17/01/09

Date of loan

12/12/08

07/01/09

Borrower Post code

SK8 6LA

Sk7 5TN

Borrower address line 3

Greater Manchster

Greater Manchester

Borrower address line 2

Stockport

Stockport

Borrower address line 2

Chealde Hulme

Cheadle Hulme

Borrower address line 1

75 Hulme Hall Road

10 Upping Street

Book Review

Lots of text to go in here !!

Lots of text to go in here !!

Topics Covered

Access, Databases

Access, Databases

Subject covered

ICT

ICT

Book format

A4 Softback

A4 Softback

Book number

12341234

12341234

Book publisher

Heineman

Heineman

Book ISB

123456789

123456789

Book author

Christopher P Bacon

Christopher P Bacon

Book Title

GCSE ICT Projects

GCSE ICT Projects

name of school

Hulme Hall – Seniors

Hulme Hall – Seniors

Borrower id number

12345

22234

Borrower last name

Colin

Smith

Borrower Forename

Wood

Fred

Borrower address line 4

Mrs Smith

Mrs Ormiston

Mr Wood

Mrs Wood-Hope

11 South

Year 4

07/01/09

07/01/09

12/12/08

12/12/08

SK7 1LA

SK5 4HG

Middle Earth The Shire

Greater Manchester

Hobbiton Hill

Stockport

1 Bag End

Bramhall

Dunraomin

10 Homestead Close

Comments to go in here

More comments in here

Toasters, Microwaves

Singing

Home Economics

Music

A5 Spiral bound

A5 Hardback

54345643

34523432

Payne-Galway

Hodder Murray

456456987

67456456456

Kenneth Wood

John S Bark

A day in the life of a Kitchen Appliance

Choral Harmonising

Hulme Hall – Seniors

Hulme Hall – Juniors

223344

5345234

Beaver

Jones

Archibald

Thomas

Borrower address line 1

name of school

Borrower id number

Borrower last name

Borrower Forename Reviews Borrower ID Book ID Review Review ID

Borrowers form teacher

Borrowers class

Date loan due back

Date of loan

Borrower Post code

Book Review

Topics Covered

Subject covered

Book format

Book number

Book publisher

Book ISBN

Book author

Book Title Subject Description / Name Subject ID

Borrowers First name Surname Address line 1 Address line 2 Address line 3 Address line 4 Post code Form Borrower ID

The fields from appendix three have been separated into different tables. In each of these tables there should not be any repeating values – if there are then another table should be created. For instance. If the same book is borrowed more than once then we should not have to type in the author and title each time – so these details are placed in a separate table. The table with the details of each loan has a field which says which book the loan is about and that field is linked to the books table so the correct book details for each loan can be looked up. This same process is repeated for borrowers. The loan table has a link to the key field in the borrowers table so details of the correct pupil can be looked up. Since the same person can borrow the same book more than once another field (loan ID) is set up to identify each loan separately. This same process can and should be repeated for each aspect in the database that is repeated.

Name Form Teacher ID

Borrower address line 2

Teachers

Borrower address line 2

Title Author Topics Publisher Format Subject ID Book ID

Borrower address line 3

Loans Borrower ID Loan ID Loan date Return date Returned Y/N Book ID

Borrower address line 4

Books

Appendix four Borrowers head of year

Publisher / Book format – The same publisher and the same book format will be used for more than one book- to be properly normalised the database should really separate out the publishers and the book formats into their own table and then make a link to them in the books table, not doing so was just laziness on my part

Reviews – more than one pupil may review a book, and each pupil may review more than one book – so a join table recording the pupil ID and the book ID is needed to store the details, just like with the loans.

Teachers – each pupil only has one form teacher, so there will only ever be one link between the teacher table and a particular pupil, all that is needed is to record he teacher ID in the pupils record – no join table is needed

Loans – same pupil and same book on more than one occasion is possible – so a join table is needed

A join table with the key fields of each of the two tables involved will only be needed if there are going to be more than one instance of those two key fields being linked. – Difficult to understand – but consider these examples