Data Modeling, Types of Keys and Types of Attributes in DBMS

• Data modeling is a crucial aspect of database design and development.
• It allows us to organize and represent data in a structured manner, facilitating retrieval, and manipulation.
• Data modeling helps in organizing and structuring data elements into logical groups such as entities, attributes, and relationships.
• Data models serve as a blueprint for database design, guiding the development of database schemas, tables and indexes.
• One popular approach to data modeling is the Entity-Relationship (ER) model.

What is Entity?

• An entity in data modeling represents a real-world object, such as a person, place, thing, or event.
• It is a distinct and identifiable concept that we want to store data about in our database.
• Entities have attributes that define their properties or characteristics.
• Example: For the "Product" entity in an e-commerce database, attributes could include Product ID, Name, Price, and Quantity.
• Entities can have relationships with other entities, representing associations or connections between them.
• Example: In a library database, the "Book" entity may have a relationship with the "Author" entity through a "WrittenBy" relationship.

Entity Types & Entity Sets

• An entity type is a category or classification of entities that share common characteristics.
• For example, if we consider the "Person" entity, the entity type could be "Employee" or "Customer."
• An entity set, on the other hand, is a collection or group of instances of an entity type.
• Each instance within an entity set is unique and can be uniquely identified.

What are Attributes ?

• An entity's attributes are its qualities or characteristics.
• They provide additional details or information about the entity.
• Attributes can be of different types, such as text, numbers, dates, or Boolean values.
• Each attribute has a name and a domain that defines its range of possible values.
• Example: For an entity like "Student," attributes could include Student ID, Name, Age, Gender, Address, etc.

What are Keys?

• Keys are attributes combinations that uniquely identify an entity inside a group of entities.
• They ensure data integrity and help establish relationships between entities.
• Example: In a "Student" table, the Student ID can be designated as the primary key because each student has a unique ID.

Types of Keys

• Primary Key
• Super Key
• Candidate Key
• Foreign Key

Primary Key

Uniqueness

• A primary key uniquely identifies each record in a table.
• This ensures that each record has a unique key value, preventing any duplication within the dataset.
• Example: In a "Orders" table, the Order ID could serve as the primary key, ensuring each student has a unique identifier.

1CREATE TABLE Orders (
2    OrderID INT PRIMARY KEY,
3    CustomerName VARCHAR(50),
4    CustomerAge INT
5);
6

Data Integrity

• The primary key constraint enforces data integrity by preventing duplicate or null values in the primary key column.
• Example: Trying to insert a new record with an existing Student ID as the primary key would result in an error, maintaining data consistency.

Indexing

• Primary keys are automatically indexed in most database systems, which improves search performance for queries involving primary key lookups.
• Example: Querying for a specific student's information using their Student ID (primary key) would be faster due to indexing.

Relationships

• Primary keys are commonly utilized to create connections or associations among different tables.
• In a one-to-many relationship, the primary key from one table serves as a foreign key in another.

Consistency and Updates

• Updates and modifications to records are more manageable and accurate when a primary key is defined,
• as it provides a unique identifier for targeting specific records.
• Example: Updating a student's information based on their Student ID ensures that only the intended record is modified, maintaining data consistency.

Super Key

• It may contain more attributes than required to form a key.
• Consider a "Students" table with attributes {Student ID, Name, Email, Phone}.
• The combination of {Student ID, Email} forms a super key because it can uniquely identify each student.
• Even if two students have the same name or phone number, they will have different Student IDs or Email addresses, making this combination unique.

1CREATE TABLE Students (
2    StudentID INT PRIMARY KEY,
3    Name VARCHAR(50),
4    Email VARCHAR(100) UNIQUE,
5    Phone VARCHAR(15),
6    -- Define the combination of StudentID and Email as a super key
7    CONSTRAINT SuperKey_Constraint UNIQUE (StudentID, Email)
8);

Combination of Attributes

• Super keys can be formed by combining multiple attributes.
• Example: In a "Books" table with attributes {Book ID, Title,},
• a combination like {Title, Author} could be a super key if no two books have the same title and author combination.

Candidate Key

• A candidate key is a minimal set of attributes that can uniquely identify an entity.
• It is a subset of the super key and has no redundant attributes.
• Example: In a "Students" table, the combination of StudentID and Email can be a candidate key if it uniquely identifies each student.

Uniqueness Constraint

• Every candidate key needs to guarantee that there are no duplicate combinations of values for the key attributes within the table's records.
• Example: Ensuring that no two students have the same StudentID and Email combination in the "Students" table.

Primary Key Selection

• From the candidate keys, one is typically chosen as the primary key to serve as the main unique identifier for the table.
• Example: Selecting StudentID as the primary key from the candidate keys in the "Students" table.

What are Alternate Keys?

• Any candidate key that is not chosen as the primary key is known as an alternate key, providing additional unique identification options.
• Example: If StudentID is chosen as the primary key, Email becomes an alternate key in the "Students" table.

1-- Create a table for web developers with a candidate key (DeveloperID)
2CREATE TABLE Developers (
3    DeveloperID INT PRIMARY KEY,
4    Name VARCHAR(100) NOT NULL,
5    Email VARCHAR(100) UNIQUE,
6    SkillID INT,
7    CONSTRAINT CK_Developers_CandidateKey UNIQUE (Name, Email) -- Candidate key constraint
8);
9
10-- Insert sample data into Developers table
11INSERT INTO Developers (DeveloperID, Name, Email, SkillID) VALUES
12(1, 'Harjas Singh', 'harjas@example.com', 3),
13(2, 'Mahak Chugh', 'mahak@example.com', 4),
14(3, 'Jasraj Singh', 'jasraj@example.com', 1);

• a table named "Developers" for storing information about web developers.
• It defines the DeveloperID as the primary key, ensuring each developer has a unique identifier.
• The Name and Email columns are defined to store the developer's name and email address, respectively.
• The Email column has a UNIQUE constraint, ensuring each email address is unique in the table.
• The SkillID column is used to store the developer's skill level or expertise.
• A candidate key constraint named "CK_Developers_CandidateKey" is added,
• combining the Name and Email columns as a candidate key to enforce uniqueness based on these attributes.
• Sample data is inserted into the Developers table using the INSERT INTO statement,
• providing values for DeveloperID, Name, Email, and SkillID for three developers.

Foreign Key

• A foreign key is an attribute or set of attributes in one table that references the primary key or a unique key in another table.
• Example: In an "Orders" table, the CustomerID column can be a foreign key that references the CustomerID primary key in the "Customers" table.

Relationship Establishment

• Foreign keys establish relationships between tables, defining dependencies and ensuring referential integrity between related data.
• Example: Linking order records to customer records using the CustomerID foreign key in the "Orders" table.

Cascading Actions

• Foreign keys can specify cascading actions, such as cascading updates or deletes, to maintain data consistency across related tables.
• Example: Updating or deleting a customer's record automatically updates or deletes corresponding order records linked via foreign keys.

Null and Default Values

• Foreign keys allow for NULL values, indicating optional relationships, and can also have default values for data insertion.
• Example: Allowing orders without a specified customer by accepting NULL values in the CustomerID foreign key column.

1-- Create a Skills table with SkillID as the primary key
2CREATE TABLE Skills (
3    SkillID INT PRIMARY KEY,
4    SkillName VARCHAR(50) NOT NULL
5);
6
7-- Create a Developers table with a foreign key (SkillID) referencing Skills table
8CREATE TABLE Developers (
9    DeveloperID INT PRIMARY KEY,
10    Name VARCHAR(100) NOT NULL,
11    Email VARCHAR(100) UNIQUE,
12    SkillID INT,
13    FOREIGN KEY (SkillID) REFERENCES Skills(SkillID)
14);
15
16-- Insert sample data into Skills and Developers tables
17INSERT INTO Skills (SkillID, SkillName) VALUES
18(1, 'HTML'),
19(2, 'CSS'),
20(3, 'JavaScript');
21
22INSERT INTO Developers (DeveloperID, Name, Email, SkillID) VALUES
23(1, 'Harjas Singh', 'harjas@example.com', 3),
24(2, 'Mahak Chugh', 'mahak@example.com', 2),
25(3, 'Jasraj Singh', 'jasraj@example.com', 1);

• a table named "Developers" to store developer information.
• DeveloperID : These lines define the columns in the table, including DeveloperID, Name, Email (with a unique constraint), and SkillID.
• FOREIGN KEY (SkillID) REFERENCES Skills(SkillID): This line adds a foreign key constraint to the SkillID column, referencing the Skills table's SkillID column.

Types of Attributes

• Composite Attributes
• Derived Attributes
• Multivalued Attributes

Composite Attributes

• Composite attributes are attributes that can be divided into subparts.
• For example, a "Full Name" attribute can be divided into "First Name" and "Last Name."
• Composite attributes help in organizing complex data structures by breaking down larger attributes into manageable parts.
• In database tables, composite attributes are represented as separate columns for each sub-attribute.
• Composite attributes are commonly used in entity-relationship modeling to represent hierarchical or structured data.

Derived Attributes

• Derived attributes are those that are computed or derived based on other attributes.
• They are not directly stored in the database but can be calculated from existing attributes when required.
• Example: Age is a derived attribute that can be calculated based on the "Date of Birth" attribute in a person's record.
• Derived attributes reduce redundancy by storing calculated values instead of storing them directly.
• Derived attributes are dynamic and can change automatically when the base attributes they depend on are updated.

Multivalued Attributes

• Multivalued attributes can have multiple values for a single entity instance.
• For example, a "Skills" attribute for an employee entity can have multiple skills associated with it.
• Example: In a "Skills" attribute for developers, a developer may have multiple skills such as "React js,"Nextjs" and "Three.js"
• In database design, multivalued attributes are represented using separate tables linked by foreign keys within a single attribute.
• Multivalued attributes provide flexibility in representing complex data where entities can have multiple related values.

Conclusion