The 5 Steps of Normalisation
Identify and Extract Non-Key Dependencies.
Data-Modelling
– A data model is concerned with the question 'What data does the system need to store and what is the best way to store it?'
Data modelling uses two techniques to achieve this:
ERM Modelling is a top- down approach:
Normalisation is a bottom-up approach:
An Entity is any data object. This can be an object or a person. An Entity is any object that a system needs to store data about. Eg. Customer, Product
An Attribute is the data item or elements that make up the Entity.
The Occurrence of any Entity describes the Entity eg. Customer Name. The Entity can represent a number of Occurrences.
Primary Keys are unique identifiers. If a system is to work than we must be able to uniquely identify every Occurrence of an Entity.
Enterprise Rule is a rule the business imposes onto the system.
Relationships link the above.
4 Principles of Database Developing:
Exercise:
The Library System: Worked Example
A library keeps records of loans, books and members. It stores member's names, addresses, status (junior or senior), loan limit (number of books a member may borrow) and date of birth; members are given individual member numbers when they join the library. The library also stores information about its books: title, authors, publisher, publication date, ISBN and purchase price. As some books are very popular, the library often buys several copies of the same book. All loans are for three weeks. The library needs to be able to record, edit and delete member details; record, edit and delete book details; record loans and returns; and reserve books. Overdue notices are to be sent when books are overdue. It also wants its library system to automatically update member status. The system must also record the current price of a book.
The following enterprise rules can be applied to the library data.
Entities
Member Name
Address
ID Number
Date of Birth
Loan Limit
Book ISBN
Price – Purchase
Price – Current
Publication Date
Reservation
