FUNDAMENTALS OF DATABASE DESIGN

BRIEF HISTORY    

In the late 1960s the mathematician Dr E F Codd, who was then working at the IBM San Jose Research Laboratory, proposed a major step forward in database systems. He suggested that the rigid principles of mathematics could be used to design, create and manage a database system. Codd's ideas were first published in 1970 in a seminal paper 'A Relational Model of Data for Large Shared Data Banks'.

This research gave birth to the relational model on which relational databases are based. It also led to the development of a database analysis and design methodology known as normalisation. This methodology addressed the problems associated with data duplication, insertion, deletion and updates. These issues will be discussed in more detail later.It is important to note that this design methodology is concerned with determining the contents of a database and is independent of the constraints of the final physical database chosen.  

One of the rules proposed by Codd was that a relational database should include a common language that is used to: 

• create the database
• store and manipulate the data within it 

• manage security.

The language that was widely adopted was  Structured Query Language (SQL) (commonly pronounced 'sequel'). 

DATABASE DEFINE 

A database is a collection of records stored on some type of media. Storage in the past has included punch cards, paper tape, magnetic tapes and disks. Previously, different departments in a company would design their own databases with their own copies of data. So, for example, there would be multiple copies of employee details held in various systems and departments, eg: Human Resources, Pensions and Project Management. Let us look at an example of an employee 'Billie Jones' and different departments holding her details: 

Information held by Human Resources	Information held by IT Department
Surname: Jones	Surname: Jones
Forename: Billy	Forename: Billie
DOB: 28/08/1980	DOB: 28/08/1980
Department: IT	Department: IT
Address: 1 Bath Street	Address: 23 Hope Street
Gender: Female	Gender: Female
Salary: £23,000	Salary: £27,000

ADVANTAGES AND LIMITATIONS  

A good database management system (DBMS) should provide the following advantages over a conventional system:

Advantages

1. Reduced data redundancy
2. Reduced updating errors and increased consistency
3.  Greater data integrity and independence from applications programs
4.  Improved data access to users through use of host and query language
5.  Improved data security
6.  Reduced data entry, storage, and retrieval costs

However, the following can be viewed as some of the limitations of a database 

Disadvantages

1. Database systems are complex, difficult, and time-consuming to design
2. Substantial hardware and software start-up costs
3.  Damage to database affects virtually all applications programs
4.  Extensive conversion costs in moving form a file-based system to a database system  
5.  Initial training required for all programmers and users

STAGES IN CREATING A DATABASE  

The process of creating a database can be broadly divided into two main stages:

1. Data analysis- involves using a formalised methodology to create a database design. Two widely used methods are Entity Relationship Modelling (ER) and Normalisation. During this Unit we will be examining the latter. It is important to note that a database design is independent of the final database system chosen. Therefore, the same design can be physically implemented in different types of databases.
2.  Physical implementation -of that design in a database system. The database system used in this course is Oracle, a Relational Database Management System (RDBMS) from Oracle Corporation. However, there are many other RDBMSs used in industry, eg: MySQL (open source)DB2 (IBM) and SQLServer (Microsoft).

As you move from a database design to a physical implementation, different terminology is used. However, often the terms are interchangeable. The following diagram explains some of the terms used at the different stages. 

Entity 

An entity is any object in the system that we want to model and store information about. Entities are usually recognizable concepts, either concrete or abstract, such as person, places, things, or events which have relevance to the database. Some specific examples of entities are Employee, Student, Lecturer. An entity is analogous to a table in the relational model.Note - the convention is to use singular names when identifying entities. Entities are represented by rectangles (either with round or square corners):  

Attribute 

An attribute is an item of information which is stored about an entity. For example, the entity 'lecturer' could have attributes such as staff id, surname, forename, date of birth, telephone number, etc. An attribute can only appear in one entity, unless it is the key attribute in another entity. In a traditional filing system an attribute equates to a field in a record. 

http://www.sqa.org.uk/e-learning/MDBS01CD/page_02.htm#Brief