Data Model is an abstract model that represents the data objects, data flow

between these data objects, and the interrelationship between these data
objects. It is a way of storing data on a computer so that it can be used in a
more efficient manner for further purposes.
Data model or data structure consists of following fundamental elements:
1. Data object :
The data object is actually a location or region of storage that contains a
collection of attributes or groups of values that act as an aspect,
characteristic, quality, or descriptor of the object. A vehicle is a data object
which can be defined or described with the help of a set of attributes or data.
Different data objects are present which are shown below:
 External entities such as a printer, user, speakers, keyboard, etc.
 Things such as reports, displays, signals.
 Occurrences or events such as alarm, telephone calls.
 Sales databases such as customers, store items, sales.
 Organizational units such as division, departments.
 Places such as manufacturing floor, workshops.
 Structures such as student records, accounts, files, documents.
2. Attributes :
Attributes define the properties of a data object. The attribute is a quality or
characteristic that defines a person, group, or data objects. It is actually the
properties that define the type of entity. An attribute can have a single or
multiple or range of values as per our needs.
There are three types of attributes:
1. Naming attributes –
To name an instance of a data object, naming attributes are used. User
naming attributes identify user objects such as Login_names and User_Id
for some security purpose. For example- Make and model are naming
attributes in a vehicle data object.
2. Descriptive attributes –
These attributes are used to describe the characteristics or features or the
relationship of the data object. Sometimes also referred to as relationship
attributes. For example- In a vehicle, the color of a data object is a
descriptive attribute that describes the features of the object.
3. Referential attribute –
These are the attributes that are used to formalize binary and associative
relationships and in making reference to another instance in another
table. For example- The data object is a referential attribute in a vehicle.
3. Relationship :
The relationship represents the connection or relation between different data
objects and describes association among entities. Relationships are of three
types: one-to-many, many-to-many, and many-to-one.
For example, toy and shopkeeper are two objects that share the following
relationship:
 The Shopkeeper order toys.
 The shopkeeper sells toys.
 The shopkeeper shows toys.
 The Shopkeeper stocks toys.

Types of Keys in Relational Model (Candidate,

Super, Primary, Alternate and Foreign)
Keys are one of the basic requirements of a relational database
model. It is widely used to identify the tuples(rows) uniquely in the
table. We also use keys to set up relations amongst various columns
and tables of a relational database.
Different Types of Database Keys
 Candidate Key
 Primary Key
 Super Key
 Alternate Key
 Foreign Key
 Composite Key
Candidate Key
The minimal set of attributes that can uniquely identify a tuple is
known as a candidate key. For Example, STUD_NO in STUDENT
relation.
 It is a minimal super key.
 It is a super key with no repeated data is called a candidate key.
 The minimal set of attributes that can uniquely identify a record.
 It must contain unique values.
 It can contain NULL values.
 Every table must have at least a single candidate key.
 A table can have multiple candidate keys but only one primary
key.
 The value of the Candidate Key is unique and may be null for a
tuple.
 There can be more than one candidate key in a relationship.
Example:
STUD_NO is the candidate key for relation STUDENT.
Table STUDENT
STUD_NO SNAME ADDRESS PHONE

1 Shyam Delhi 123456789

2 Rakesh Kolkata 223365796

3 Suraj Delhi 175468965

 The candidate key can be simple (having only one attribute) or

composite as well.
Example:
{STUD_NO, COURSE_NO} is a composite
candidate key for relation STUDENT_COURSE.
Table STUDENT_COURSE
TEACHER_N
STUD_NO O COURSE_NO

1 001 C001

2 056 C005

Note: In SQL Server a unique constraint that has a nullable

column, allows the value ‘null‘ in that column only once. That’s
why the STUD_PHONE attribute is a candidate here, but can not be a
‘null’ value in the primary key attribute.
Primary Key
There can be more than one candidate key in relation out of which
one can be chosen as the primary key. For Example, STUD_NO, as
well as STUD_PHONE, are candidate keys for relation STUDENT but
STUD_NO can be chosen as the primary key (only one out of many
candidate keys).
 It is a unique key.
 It can identify only one tuple (a record) at a time.
 It has no duplicate values, it has unique values.
 It cannot be NULL.
 Primary keys are not necessarily to be a single column; more than
one column can also be a primary key for a table.
Example:
STUDENT table -> Student(STUD_NO, SNAME,
ADDRESS, PHONE) , STUD_NO is a primary key
Table STUDENT
STUD_NO SNAME ADDRESS PHONE

1 Shyam Delhi 123456789

2 Rakesh Kolkata 223365796

3 Suraj Delhi 175468965

Super Key
The set of attributes that can uniquely identify a tuple is known as
Super Key. For Example, STUD_NO, (STUD_NO, STUD_NAME), etc. A
super key is a group of single or multiple keys that identifies rows in
a table. It supports NULL values.
 Adding zero or more attributes to the candidate key generates
the super key.
 A candidate key is a super key but vice versa is not true.
 Super Key values may also be NULL.
Example:
Consider the table shown above.
STUD_NO+PHONE is a super key.

Relation between Primary Key, Candidate Key, and Super Key

Alternate Key
The candidate key other than the primary key is called an alternate
key.
 All the keys which are not primary keys are called alternate keys.
 It is a secondary key.
 It contains two or more fields to identify two or more records.
 These values are repeated.
 Eg:- SNAME, and ADDRESS is Alternate keys
Example:
Consider the table shown above.
STUD_NO, as well as PHONE both,
are candidate keys for relation STUDENT but
PHONE will be an alternate key
(only one out of many candidate keys).

Primary Key, Candidate Key, and Alternate Key

Foreign Key
If an attribute can only take the values which are present as values
of some other attribute, it will be a foreign key to the attribute to
which it refers. The relation which is being referenced is called
referenced relation and the corresponding attribute is called
referenced attribute. The referenced attribute of the referenced
relation should be the primary key to it.
 It is a key it acts as a primary key in one table and it acts as
secondary key in another table.
 It combines two or more relations (tables) at a time.
 They act as a cross-reference between the tables.
 For example, DNO is a primary key in the DEPT table and a non-
key in EMP
Example:
Refer Table STUDENT shown above.
STUD_NO in STUDENT_COURSE is a
foreign key to STUD_NO in STUDENT relation.
Table STUDENT_COURSE
TEACHER_N
STUD_NO O COURSE_NO

1 005 C001

2 056 C005

It may be worth noting that, unlike the Primary Key of any given
relation, Foreign Key can be NULL as well as may contain duplicate
tuples i.e. it need not follow uniqueness constraint. For Example,
STUD_NO in the STUDENT_COURSE relation is not unique. It has
been repeated for the first and third tuples. However, the STUD_NO
in STUDENT relation is a primary key and it needs to be always
unique, and it cannot be null.

Relation between Primary Key and Foreign Key

Composite Key
Sometimes, a table might not have a single column/attribute that
uniquely identifies all the records of a table. To uniquely identify
rows of a table, a combination of two or more columns/attributes
can be used. It still can give duplicate values in rare cases. So, we
need to find the optimal set of attributes that can uniquely identify
rows in a table.
 It acts as a primary key if there is no primary key in a table
 Two or more attributes are used together to make a composite
key.
 Different combinations of attributes may give different accuracy
in terms of identifying the rows uniquely.
Example:
FULLNAME + DOB can be combined
together to access the details of a student.

Different Types of Keys

Types of Relationship in Database

A database is a structured data set that is usually electronically
written in a computer system and stored as data. With their primary
purpose being to maintain, keep, and extract data correctly,
databases are important. In many practical situations, data is never
an isolated file, and its correlations may be hidden. The occurrence
of patterns in data is important to identify for effective data
management. Here enters the word of data relationships in
databases.
What is a Relationship In Database?
A relationship in a DBMS exists when the variable has a connection
with the properties stored in different tables. Such relationships help
the organization of entities intertwined with each other, ultimately
enabling efficient data processing. They’re exhibited usually via
keys in a table, which is either columns or fields that specify a
distinctive arrangement for each record.
As another example, one can think of the university
database. Students, courses, and instructors, variably, might
become tables. The relationship between the two tables is that
courses indicate which courses the student can enroll in. Database
management facilitates the reliable data apostrophe and
performance of complex operations due to the fact that it
guarantees the quality and identity of the data.
Why are Relationships Important in a
Database?
Relationships are crucial in a database for several reasons:
 Data Integrity: The relationships make the necessary links
between data sets and ensure that information kept and joined
together is accurate. In this way, the consistency and correctness
of the data are maintained.
 Efficient Data Retrieval: The relationship permits the complete
search of data among multiple tables, storing fewer inputs and
executing the query quickly, respectively.
 Normalization: Relations remain priorities that ensure the sync
of data and achieving a level of relevance.
 Complex Data Analysis: Relationships empower users for state-
of-the-art querying, aggregation, and analysis; thereby, users
would be deriving meaningful insights from their data.
 Scalability: It has turned out that well-defined relationships, in
their turn, are the key issue that helps the database to be
scalable, thus having a structure that is easy to manage and
could be further adapted to possible expanding business needs.
Types of Relationships in a Database
1. One-to-One (1:1) Relationship
In one to one relationships, a record is present in one table along
with its corresponding existing relation, and the vacant relation
among the records is present in another table. The type
of relationship we are talking about is not as usual, and it is
normally used when two entities that belong to a specific set need
to be stored independently for normalization or security purposes. In
another case, a person’s employees’ data consists of a record in the
“personal details” table in a human resources database.
2. One-to-Many (1:N) Relationship
A relationship where the items from one table can be linked to only
one or many items from another table is called a one-to-many
relationship; in some cases, one item from the first table correlates
with only one item from the second table. This connection becomes
very strong in that it is particularly used to describe situations where
one object can be linked to many similar or identical objects. For
example, in an online store backend database, every customer may
place multiple orders, yet the master customer record stays the
same. If a record has more than one order, these are obtained from
the backend database.
3. Many-to-Many (N:M) Relationship
The duality of a many-to-many relationship is characterized by the
presence of multiple records belonging to a table in association with
multiple records from another table. The interconnection of these
relationships follows a junction table format, which is the component
that holds both tables together. In the many-to-many relationship
model, a wide variety of complex relationships can be established
where each entity has many related entities. Such a database for a
music streaming service could have a table representing each track
that belongs to multiple playlists, and each of them could contain
multiple tracks.

Structural Constraints of Relationships

in ER Model
Prerequisite – ER Model To understand Structural Constraints, we must take
a look at Cardinality Ratios and Participation Constraints. Cardinality Ratios
of relationships : The entities are denoted by rectangle and relationships by

diamond. There are numbers

(represented by M and N) written above the lines which connect
relationships and entities. These are called cardinality ratios. These
represent the maximum number of entities that can be associated with each
other through relationship, R. Types of Cardinality : There can be 4 types
of cardinality –

1. One-to-one (1:1) – When one entity in each entity set takes part at most
once in the relationship, the cardinality is one-to-one.
2. One-to-many (1: N) – If entities in the first entity set take part in the
relationship set at most once and entities in the second entity set take
part many times (at least twice), the cardinality is said to be one-to-many.
3. Many-to-one (N:1) – If entities in the first entity set take part in the
relationship set many times (at least twice), while entities in the second
entity set take part at most once, the cardinality is said to be many-to-one.
4. Many-to-many (N: N) – The cardinality is said to be many to many if
entities in both the entity sets take part many times (at least twice) in the
relationship set.
Participation Constraints : Participation Constraints tell us that the
participation in a relationship can either be total or partial.
 When
each entity in an entity set participates in a relation, it is called Total
Participation. However, when all entities in the given entity set do not
participate in a relation, it is called Partial Participation. Structural
Constraints : Structural Constraints are also called Structural properties of a
database management system (DBMS). Cardinality Ratios and Participation
Constraints taken together are called Structural Constraints. The name
constraints refer to the fact that such limitations must be imposed on the
data, for the DBMS system to be consistent with the requirements.

The Structural
constraints are represented by Min-Max notation. This is a pair of
numbers(m, n) that appear on the connecting line between the entities and
their relationships. The minimum number of times an entity can appear in a
relation is represented by m whereas, the maximum time it is available is
denoted by n. If m is 0 it signifies that the entity is participating in the relation
partially, whereas, if m is either greater than or equal to 1, it denotes total
participation of the entity. Note – Number of times an entity participates in a
relationship is same as the number appearance of the entity in the tuples.

A weak entity type in a database is an entity that cannot be uniquely identified by its
own attributes alone. Instead, it depends on a "strong" or "owner" entity type to provide some or
all of its key attributes. Here’s a detailed look at weak entity types:

Characteristics of Weak Entity Types

1. Lack of a Primary Key:

o Weak entities do not have a primary key of their own. Instead, they are identified
by a combination of their own attributes and the primary key of the related strong
entity.
 2. Existence Dependency:
o A weak entity type is existence-dependent on another entity type (the strong
entity). It cannot exist without a corresponding instance of the strong entity.
3. Partial Key (Discriminator):
o A weak entity has a partial key, also called a discriminator or partial identifier.
This is an attribute or set of attributes that uniquely identifies the weak entity
within the context of its associated strong entity. However, the partial key alone is
not sufficient to uniquely identify the weak entity across all instances.
4. Identifying Relationship:
o The relationship between a weak entity and its strong entity is known as an
identifying relationship. This relationship is typically represented by a double
diamond in ER diagrams and is essential for defining the weak entity’s key.
5. Total Participation:
o Weak entities usually participate totally in the identifying relationship with the
strong entity. This means every instance of the weak entity must be associated
with an instance of the strong entity.

Example of a Weak Entity Type

Consider a scenario in a hospital database:

 Strong Entity Type: Patient

o Primary Key: PatientID
 Weak Entity Type: Prescription
o Attributes: MedicineName, Dosage
o Partial Key: MedicineName
o The weak entity Prescription is dependent on the Patient entity. The
combination of PatientID (from the Patient entity) and MedicineName
uniquely identifies each Prescription.

ER Diagram Representation

In an Entity-Relationship (ER) diagram:

 Strong Entity Type: Represented by a rectangle.

 Weak Entity Type: Represented by a double rectangle.
 Identifying Relationship: Represented by a double diamond.
 Total Participation: Represented by a double line connecting the weak entity to the
relationship.

Entity-Relationship (ER) diagrams are a type of diagram used to model the

structure of a database. They visually represent the entities in a database, the relationships
between these entities, and the attributes that describe them. ER diagrams are a fundamental tool
in database design.

Components of an ER Diagram

1. Entities:
o Definition: An entity represents a real-world object or concept that can be distinctly
identified. Entities typically correspond to tables in a relational database.
o Representation: Entities are depicted as rectangles.
o Example: Employee, Department, Customer, Product.

2. Attributes:
o Definition: Attributes describe the properties or characteristics of an entity.
o Representation: Attributes are represented by ovals connected to their corresponding
entity.
o Types:
 Simple Attribute: Cannot be divided further (e.g., FirstName).
 Composite Attribute: Can be divided into sub-parts (e.g., FullName can be split
into FirstName and LastName).
 Derived Attribute: Calculated from other attributes (e.g., Age from
BirthDate).
 Multi-valued Attribute: Can have multiple values (e.g., PhoneNumbers).
o Example: Employee might have attributes like EmployeeID, Name, Address, Phone.

3. Relationships:
o Definition: Relationships illustrate how two or more entities are related to each other.
o Representation: Relationships are represented by diamonds, connected to the entities
they relate to.
o Types:
 One-to-One (1:1): An entity in set A is related to one and only one entity in set
B, and vice versa.
 One-to-Many (1

): An entity in set A is related to multiple entities in set B, but an entity in set B is

related to only one entity in set A.

 Many-to-Many (M

): Entities in set A can be related to multiple entities in set B, and entities in set B
can be related to multiple entities in set A.

o Example: An Employee works in a Department (One-to-Many relationship).

4. Primary Key:
o Definition: A primary key is a unique attribute or combination of attributes that uniquely
identifies each instance of an entity.
 o Representation: In an ER diagram, the primary key attribute is usually underlined.

5. Foreign Key:
o Definition: A foreign key is an attribute in one entity that links to the primary key of
another entity, establishing a relationship between the two.
o Representation: There is no specific symbol for a foreign key in standard ER diagrams,
but it is implied by the relationship lines.

6. Weak Entity:
o Definition: A weak entity depends on another entity (a strong entity) for its existence
and does not have a primary key of its own.
o Representation: Represented by a double rectangle, and its relationship with the strong
entity is shown by a double diamond.

Example ER Diagram

Imagine a simple ER diagram for a university database:

 Entities:
o Student with attributes StudentID, Name, DOB.
o Course with attributes CourseID, CourseName.
o Enrollment (weak entity) with attributes EnrollmentID, Grade.

 Relationships:
o Student "enrolls in" Course (One-to-Many relationship from Student to
Enrollment and Course to Enrollment).
o Course "is taught by" Professor (Many-to-Many relationship).

Naming conventions in a Database Management System (DBMS) are

essential for creating clear, consistent, and easily maintainable database schemas. By adhering to
standard naming conventions, developers can improve the readability and organization of the
database. Here are some common naming conventions used in DBMS:

General Naming Guidelines

1. Case Sensitivity:
o Choose a case style and stick with it consistently. Common styles include:
 snake_case: employee_name
 camelCase: employeeName
 PascalCase: EmployeeName

2. Descriptive Names:
 o Use names that clearly describe the entity, attribute, or object’s purpose.
o Avoid vague abbreviations unless they are widely recognized.

3. Avoid Reserved Words:

o Do not use SQL reserved words as names for tables, columns, or other database objects.
Reserved words include terms like SELECT, TABLE, INSERT, etc.

4. No Spaces or Special Characters:

o Avoid using spaces, special characters, or symbols in names. Use underscores or other
conventions for separation.
o Example: Use order_details instead of Order Details.

Naming Conventions for Specific DBMS Objects

1. Tables:
o Singular vs. Plural: Choose either singular or plural names and be consistent.
 Example: Customers (plural) or Customer (singular).
o Descriptive Names: Names should clearly reflect the table’s content.
 Example: Employees, Orders, Products.

2. Columns:
o Descriptive Names: Use descriptive names that clearly indicate the data stored in the
column.
 Example: FirstName, OrderDate, ProductPrice.
o Prefixing:
 Primary Keys: Often named as ID or [TableName]ID.
 Example: EmployeeID, OrderID.
 Foreign Keys: Name foreign keys to include the name of the referenced table.
 Example: CustomerID, OrderID_FK.

3. Primary Keys:
o Naming Pattern: Use ID or [TableName]ID to indicate primary keys.
 Example: CustomerID, OrderID.

4. Foreign Keys:
o Naming Pattern: Include the name of the table being referenced, often with a suffix like
_id or _fk.
 Example: CustomerID, OrderID_FK.
o Indicates Relationship: This convention helps in understanding relationships between
tables.

5. Indexes:
o Naming Pattern: Start with IDX_ followed by the table name and the column(s)
involved.
 Example: IDX_Employee_LastName, IDX_OrderDate.

6. Constraints:
 o Naming Pattern: Use prefixes to indicate the type of constraint:
 Primary Key: PK_ (e.g., PK_Employees)
 Foreign Key: FK_ (e.g., FK_Orders_Customers)
 Unique Constraint: UN_ (e.g., UN_Username)
 Check Constraint: CK_ (e.g., CK_Price_Positive)
o This helps in quickly identifying the role of the constraint.

7. Relationships (Junction Tables):

o Naming Pattern: Use both table names separated by an underscore for many-to-many
relationships.
 Example: StudentCourse, ProductCategory.

8. Views:
o Naming Pattern: Use vw_ or view_ as a prefix to indicate that the object is a view.
 Example: vw_CustomerOrders, view_SalesSummary.

9. Stored Procedures:
o Naming Pattern: Use a verb or action word followed by the object’s name.
 Example: sp_GetCustomerOrders, sp_UpdateOrderStatus.

10. Triggers:
o Naming Pattern: Use a prefix like trg_ and include the table name and the action.
 Example: trg_BeforeInsert_Orders, trg_AfterUpdate_Inventory.

Examples of Naming Conventions

 Tables: Employees, Departments, Orders

 Columns: EmployeeID, DepartmentName, OrderDate
 Primary Key: CustomerID, OrderID
 Foreign Key: CustomerID, ProductID_FK
 Indexes: IDX_Employee_LastName, IDX_OrderDate
 Constraints:
o Primary Key: PK_Customers
o Foreign Key: FK_Orders_Customers
o Unique: UN_Email
o Check: CK_Age_Limit