At the end of this lesson-
- 1. You will be able to explain different database Model.
- 3. You will be able to explain different key Fields(Candidate key, Primary key, Composite primary key and Foreign key).
Database Model: A database model is a type of data model that determines the logical structure of a database and fundamentally determines in which manner data can be stored, organized and manipulated. The most popular example of a database model is the relational model, which uses a table-based format. Some database models:
- 1. Linear model
- 2. Entity-relationship model
- 3. Relational model
- 4. Hierarchical database model
- 5. Network model
- 6. Object-oriented database model
Entity-relationship model: Entity-relationship model is a model used for design and representation of relationships between data. In this database model, relationships are created by dividing object of interest into entity and its characteristics into attributes.
In short, to understand about the ER Model, we must understand about:
- Entity and Entity Set
- Attributes and Types of Attributes.
- Keys
- Relationships
Entity/Record/Tuple/Row: An Entity is generally a real-world object which has characteristics and holds relationships in a DBMS. A record is a group of related fields within a table that are relevant to one particular person, company, or item in a database. Records appear as rows in the database table. e.g. a student is an entity.
Table/Entity set: If a Student is an Entity, then the complete data set of all the students will be the Entity Set. A database table is composed of records and fields that hold data. Tables are also called datasheets. Each table in a database holds data about a different, but related subject.
Attribute/Field/Column: A field is a part of a record that contains a single piece of data for an object in the record. On the other hand, the columns of a table are called attributes, which are the describing characteristics of each record. If a Student is an Entity, then student’s roll no., student’s name, student’s age, student’s gender etc will be its attributes. An attribute can be of many types, here are different types of attributes defined in ER database model:
- Simple attribute: The attributes with values that are atomic and cannot be broken down further are simple attributes. For example, student’s age.
- Composite attribute: A composite attribute is made up of more than one simple attribute. For example, student’s address will contain, house no., street name, pincode etc.
- Derived attribute: These are the attributes which are not present in the whole database management system, but are derived using other attributes. For example, age can be derived from date of birth.
- Single-valued attribute: As the name suggests, they have a single value.
- Multi-valued attribute: And, they can have multiple values. e.g Mobile number.
Attribute value: The value of a particular attribute is called attribute value.
Key: A field in a record that holds unique data which identifies that record from all the other records in the table or database.If the attribute roll no. can uniquely identify a student entity, among all the students, then the attribute roll no. will be said to be a key.
Relationship: When an Entity is related to another Entity, they are said to have a relationship. For example, A Class Entity is related to Student entity, because students study in classes, hence this is a relationship. Depending upon the number of entities involved, a degree is assigned to relationships.
There are three types of relationship that exist between Entities.
- Binary Relationship
- Recursive/ Unary Relationship
- Ternary Relationship
For example, if 2 entities are involved, it is said to be Binary relationship, if 3 entities are involved, it is said to be Ternary relationship, and so on.
ER Diagram: ER Diagram is a visual representation of data that describes how data is related to each other. In ER Model, we disintegrate data into entities, attributes and setup relationships between entities, all this can be represented visually using the ER diagram.
Components of ER Diagram: Entity, Attributes, Relationships etc form the components of ER Diagram and there are defined symbols and shapes to represent each one of them. Let’s see how we can represent these in our ER Diagram.
Let’s see the following table:
Relationship between above two tables is shown bellow using ER diagram:
Relational Database Model: Relational Database Model is the base of Modern database technology. In this model, the database is divided into some logical units. Each logical unit is called a table also known as relations, each of which consists of columns and rows. Each table may have multiple fields but must have a key field.The tables of a database are related with each other through primary key and foreign key, that’s why this model is called Relational Database Model. Each row, also called a tuple, includes data about a specific instance of the entity in question, such as a particular employee.
The following tables show relational model among different tables having information about mobile:
Key Fields: A field in a record that holds unique data which identifies that record from all the other records in the table or database. Account number, product code and customer name are typical key fields. As an identifier, each key value must be unique in each record. In the following table “Roll” is called key field.
There are four types of key fields. They are-
- 1. Candidate key
- 2. Primary key
- 3. Composite primary key
- 4. Foreign key
Candidate key: A candidate key is a column, or set of columns, in a table that can uniquely identify any database record without referring to any other data. It can act as a Primary Key for a table to uniquely identify each record in that table.There can be more than one candidate key. In the following table “Roll” and “NID” are called candidate key.
- A candiate key can never be NULL or empty. And its value should be unique.
- There can be more than one candidate keys for a table.
- A candidate key can be a combination of more than one columns(attributes).
Primary key: A primary key is a column in a table that can uniquely identify all the records of a table. Primary key is a kind of candidate key. In the following table “Roll” is called primary key.
A primary key’s main features are:
- 1. It must contain a unique value for each row of data.
- 2. It cannot contain null values.
Composite primary key: A composite primary key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table. But the attributes which together form the Composite key are not a key field independently or individually. In the following table “Roll” and “Section” together will form the primary key, hence it is a composite primary key.
Foreign key: A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It acts as a reference of the primary key of another table, thereby establishing a link between them. In the following two relational tables “S_code” field of Teacher_info table is called foreign key.
Hierarchical database model: The hierarchical model organizes data into a tree-like structure, where each record has a single parent or root. Sibling records are sorted in a particular order. That order is used as the physical order for storing the database. This model is good for describing many real-world relationships.
This model was primarily used by IBM’s Information Management Systems in the 60s and 70s, but they are rarely seen today due to certain operational inefficiencies.
Network model: The network model builds on the hierarchical model by allowing many-to-many relationships between linked records, implying multiple parent records. Based on mathematical set theory, the model is constructed with sets of related records. Each set consists of one owner or parent record and one or more member or child records. A record can be a member or child in multiple sets, allowing this model to convey complex relationships.
It was most popular in the 70s after it was formally defined by the Conference on Data Systems Languages (CODASYL).
Object-oriented database model: This model defines a database as a collection of objects, or reusable software elements, with associated features and methods. There are several kinds of object-oriented databases:
A multimedia database incorporates media, such as images, that could not be stored in a relational database.
A hypertext database allows any object to link to any other object. It’s useful for organizing lots of disparate data, but it’s not ideal for numerical analysis.
The object-oriented database model is the best known post-relational database model, since it incorporates tables, but isn’t limited to tables. Such models are also known as hybrid database models.
Lesson Evaluation-
Knowledge Based Questions:
- a. What is attribute/field/column?
- a. What is row/entity/record/tuple?
- a. What is entity set/data table?
- a. What is relational database model?
- a. What is key field?
- a. What is primary key?
- a. What is composite primary key?
- a. What is candidate key?
- a. What is foreign key?
Comprehension Based Questions:
- b. ‘Field and record are not same’-explain it.
- b. ‘Field is not dependent on record’- explain it.
- b. Why composite primary key is used?
- b. Primary key is not similar to foreign key –explain.
- b. Write down the role of primary key in Database relation.
Creative Questions:
Multiple Choice Questions:
Written by,
- Mizanur Rahman (Mizan)
- Lecturer of ICT, Shaheed Bir Uttam Lt. Anwar Girls’ College ,Dhaka Cantonment
- Author at www.edupointbd.com
- Software Engineer at mands IT
- Former Lecturer of ICT, Cambrian College, Dhaka
- Email: mizanjust@gmail.com
- Cell: 01724351470