PHP and MySQL Library Tutorial

General
Site Root
Database Home
Conect to MySQL
MySQL_Login.php
nav.php
dump.php

Clean Up resets the
database to contain
a few sensible
records.

Person Table
Create
Drop

Insert
Delete
Update
Search
Date-Search

Row Dump
Better View
Table View
Form View

Book Table
Create
Drop

Insert
Delete
Update
Search

Table View
Form View

Loan Table
Create
Drop

Insert
Delete

Search

Table View

Documentation
Data Requirements
User Interface Design
Entity Relationship Diagrams
Data Flow Diagrams
Data Dictionary
Implementation Notes
Table Designs
Goals, Testing and Appraisal

Note - Create, Drop and Clean Up would be hidden on a real life system.
Contact - nbauers at samphire dot demon dot co dot uk
Copyright © - You may use and reproduce this material in a "not for profit" setting.

Entity Relationship Diagrams

This relationship, as it stands, is not normalised. If a person borrowed more than one book, they would have to be entered into the database more than once. To remove this defect, information about people must not appear in the books table and information about books must not appear in the people table.

This arrangement of tables allows the Person table to contain information about people and no other information. Likewise the Book table contains information about books and nothing else. The Loan table contains records with foreign key values that uniquely reference a single customer and a single book.

The diagram below shows the table structures and how they are linked. Business rules are encoded in the diagram such that

A person can have zero or more loans
A book can have zero or more loans
A loan always has exactly one person
A loan always has axactly one book
If a loan exists, there must also exist a book and a person so the book or the person may not be deleted before the loan is deleted.

Even this structure is not properly normalised because, if an author writes more than one book, his or her information will be repeated in the book table and likewise for publishers publishing more than one book. As no complex author or publisher information is present in this database, this lack of normalisation is not a serious problem.