Fundamentals and concepts of Database models and relational database
Introduction to Database Models
Databases are at the centre of most information systems in everyday use, therefore it is important that they are designed and built using appropriate methods to ensure that they meet users’ requirements whilst being robust and maintainable. A database system is usually regarded as the database which contains related tables of data maintained by a database management system (DBMS), along with applications that provide controlled access to the database. (Elmasri and Navathe, n.d.)
In order to build an effective database system it is important to understand and apply the database development lifecycle, which includes the following phases:-
1. Strategy and planning
2. Requirements analysis
6. Operations and maintenance.
1. Strategy and planning – typically the cycle starts with the strategy and planning phase to identify the need and scope of a new system.
2. Requirements analysis phase – a more detailed requirements analysis will be carried out which will include identifying what the users require of the system; this will involve conceptual analysis.
3. Design phase – this will involve producing a conceptual, logical and physical design. To undertake these processes it is important to be able to understand and apply the data modelling techniques which are covered in this book. When a suitable logical design has been obtained the development phase can begin.
4. Development phase – this involves creating the database structure using an appropriate Database Management System (DBMS) and usually includes the development of applications that provide a user interface consisting of forms and reports which will allow controlled access to the data held in the database. This book will show how the Oracle relational database management system and the Oracle Application Express (APEX) application developer tool can be used for this purpose.
5. Deployment/implementation – when the system has been developed it will be tested, it will then be deployed ready for use.
6. Operations and maintenance – following the system release for use it will be maintained until it reaches the end of its useful life, at this stage the development lifecycle may restart.
Conceptual Data Modelling
Why do you need to model?
In many environments modelling is used to ensure that a product will satisfy the user’s requirements before it is produced. For example, an architect may use a scale model of a building so the client can see what it will look like before it is built. This allows for any changes to be made to the design following feedback and before any expensive building work takes place. Similarly, a modelling approach is needed when designing a database system so that interested parties can check that the design will satisfy the requirements.
How do you model a database system?
In order to design an effective database system you need to be able to understand an organisation’s information needs and, in particular, identify the data needed to satisfy these needs. Entity Relationship modelling (Chen P. 1976) is an important top-down analysis technique which is used to show the structure of the data used by a system. Initially, a conceptual model is produced which is independent of any hardware or DBMS system; this is achieved by using an Entity Relationship Diagram (ERD) or alternatively a UML Class Diagram (CD). This modelling technique will be used to determine how this business data is structured and show the relationships between the different data entities. The model forms the basis for the design of the database system that will be built. (Redmond, Wilson and Carter, 2012)
Logical Data Models:
Logical data models add further detail to conceptual model elements and refine the structure of the domain; they can be defined using Entity-Relationship or UML Class models. Logical data models help to define the detailed structure of the data elements in a system and the relationships between data elements. They refine the data elements introduced by a Conceptual data model and form the basis of the Physical data model.
Physical Data Models:
A Physical Data Model visually represents the structure of data as implemented by a relational database schema. In addition to providing a visual abstraction of the database structure, an important benefit of defining a Physical Data Model is that you can automatically derive the database schema from the model. This is possible due to the richness of meta-data captured by a Physical Data Model and its close mapping to aspects of the database schema, such as database Tables, columns, Primary keys and foreign keys. (Silberschatz, Korth and Sudarshan, 2011)
The Entity Relationship Diagram (ERD)
The Entity Relationship Diagram (ERD) shows “entities” and the “relationships” that link them. The entities represent the data items needed by the system and the relationships show how the entities are related to one another. An “entity” is formally called an “entity type” and can be defined as:
“A group of objects with the same properties which are identified by the enterprise as having an independent existence.” (Hernandez, 2013, pp.1)
There are a number of notations used for drawing ERDs; this book will show you how to use the commonly used Crow’s foot notation. In addition, as the Unified Modelling Language (UML) is becoming more widely established, Appendix A shows how the UML Class Diagram can also be used for data modelling. Using the Crow’s foot notation, each entity type is modelled on the ERD as a round-cornered box with the entity name inside it e.g.
An example ERD for the Music System from Appendix B is shown below. This shows four entities – represented by round edge boxes – which are needed; production COMPANY, their music CDs which consist of TRACKs (i.e. songs). Each track is classified by a music CATEGORY (e.g. Pop, Rock). The lines and their symbols linking the entities are the relationships which provide further information about the entities.
Music System ERD
In order to produce an ERD you need to identify all the entity types that are relevant to the system being modelled. Do not confuse an entity type with the occurrence of an entity.
If modelling using the Unified Modelling Language (UML) then the term “instance” is used to refer to an entity occurrence.
Often many entities can be identified, although they are not always relevant to the needs of the system being considered, so care needs to be taken to ensure that only those that are needed are added to the ERD.
The following are examples of typical entity types:
For a business system: CUSTOMER, ORDER, INVOICE. For a university system: STUDENT, LECTURER, COURSE.
Entities often fall into one of the following categories:
Physical – CAR, BUILDING
Human – CUSTOMER, EMPLOYEE Place – FACTORY, SCHOOL
Group – DEPARTMENT, TEAM Document – INVOICE, PAYSLIP.
Producing the ERD
When you have identified the entity types, these need to be added to the Entity Relationship Diagram (ERD). Although ERDs can be drawn by hand, it is good practice to use a Computer Aided Software Engineering (CASE) tool to ensure your models can be amended easily and presented in a professional form to others. There are many CASE tools available to support modelling.
The QSEE tool can be used to draw ERDs and UML Class Diagrams and is available to download from: http://www.leedsbeckett.ac.uk/qsee/
When you have identified your entity types you then need to identify their attributes. An attribute is defined as follows:
Each entity will usually have a number of attributes. These are the individual items of data that you need to hold for each occurrence of an entity type. In some situations a relationship between a pair of entities may also yield attributes;
The entity type INVOICE may include the following attributes:
Invoice number, Invoice date, Invoice amount and Customer code.
An example entity occurrence of the entity type INVOICE would be as follows:
Invoice number 1102
Invoice date 12-Jan-2015
Invoice amount 1000
Customer code C101
Types of Relational Models
Depending on the data you’re working with, you can set up one of several relational database models. In each of these models, however, you need to differentiate between a child table (also called a dependent table or a controlled table) and a parent table (also called a primary table or a controlling table). The child table is the one that is dependent on the parent table to fill in the definition of its records. The Contacts table, for example, is a child table because it is dependent on the Companies table for the company information associated with each person. (Silberschatz, Korth and Sudarshan, 2011)
The One-To-Many Model
The most common relational model is one where a single record in the parent table relates to multiple records in the child table. This is called a one-to-many relationship. The sales leads example is a one-to-many relationship because one record in the Companies table can relate to many records in the Contacts table (in other words, you can have multiple sales contacts from the same firm). In these models, the “many” table is the one where you add the foreign key.
Another example of a one-to-many relationship is an application that tracks accounts- receivable invoices. You need one table for the invoice data (Invoices) and another for the customer data (Customers). In this case, one customer can place many orders, so Customers is the parent table, Invoices is the child table, and the common field is the Customer table’s primary key. (Augsten and Bo?hlen, n.d.)
The One-to-One Model
If your data requires that one record in the parent table be related to only one record in the child table, you have a one-to-one model. The most common use of one-to-one relations is to create separate entity classes to enhance security.
In a hospital, for example, each patient’s data is a single entity class, but it makes sense to create separate tables for the patient’s basic information (such as the name, address, and so on) and his or her medical history. This enables you to add extra levels of security to the confidential medical data (such as a password). The two tables then become related based on a common “PatientID” key field.
Another example of a one-to-one model is employee data. You separate the less-sensitive information such as job title and startup date into one table, and restricted information such as salary and commissions into a second table. If each employee has a unique identification number, you use that number to set up a relationship between the two tables.
Note that in a one-to-one model, the concepts of child and parent tables are interchangeable. Each table relies on the other to form the complete picture of each patient or employee. (Hernandez, 2013)
The Many-to-Many Model
In some cases, you might have data in which many records in one table can relate to many records in another table. This is called a many-to-many relationship. In this case, there is no direct way to establish a common field between the two tables.
-Redmond, E., Wilson, J. and Carter, J. (2012). Seven databases in seven weeks. Dallas, Tex. Raleigh, N.C.: Pragmatic Bookshelf.
-Silberschatz, A., Korth, H. and Sudarshan, S. (2011). Database system concepts. New York: McGraw-Hill.
-Augsten, N. and Bo?hlen, M. (n.d.). Similarity joins in relational database systems.
-Elmasri, R. and Navathe, S. (n.d.). Fundamentals of database systems.
-Hernandez, M. (2013). Database design for mere mortals. Harlow: Addison Wesley.