Unit 1

Introduction to DBMS

Q. 1) Write the database language?

Ans: Database language

Users can interact with DBMS using database languages. There are two types of database languages: -

1. The Data Definition Language (DDL)

2. The Data Manipulation Language (DML)

1 DDL: -

DDL is used to define the conceptual schema.

It includes: -

A. Entity

B. Attributes and types

C. Relation among entity sets.

The definition also includes constraints on values that can be assigned to a given attribute.

For example:

>create table emp (name varchar (20), emp_id number NOT NULL);

Execution will create an empty table emp. Definitions are stored along with the database.

E.g., Create table, view, index

Alter table.

Drop table, view, index

2 DML: -

i. DML allows user to manipulate data stored in database.

Ii. E.g.

●      Data Retrieval

●      Data Insertion

●      Data Deletion

●      Data Modification.

Iii. DML provides commands for data manipulation.

E.g., select name from emp

Where

Salary > 15000;

Iv. DML commands can be either in interactive mode or these commands can be

Embedded in programming languages like C, VB, Java etc.

Types of DML: -

a. Procedural: -

It requires a user to specify what data is needed and how to retrieve it from database. The user instructs the system to perform a sequence of options on the database to compute the desired result.

b. Non-Procedural: -

It requires a user to specify what data is needed without specifying how to get it.

3 DCL (Data Control Language): -

E.g. (commit, rollback, save point, grant, revoke.)

It consists of commands that control the user access to database objects.

Q. 2) What is DBMS? and also write the advantages of DBMS over file processing system?

Ans: DBMS (Data Base Management System)

Database

The database is a set of interrelated data used to efficiently retrieve, insert and remove data. It is also used in the form of a table, schema, views, and reports to organize the data, etc.

You can conveniently retrieve, attach, and delete information using the database.

Database management system (DBMS)

A DBMS is a program that enables database creation, specification and manipulation, allowing users to easily store, process and analyze information.

DBMS provides one with an interface or a tool to conduct different operations, such as building databases, storing data in them, updating data, creating database tables, and much more.

The DBMS also provides databases with privacy and security. In the case of multiple users, it also ensures data consistency.

Some Example of DBMS:

●      MySQL

●      Oracle

●      SQL Server

●      IBM DB2

Characteristics of DBMS

❏      Data stored into tables

❏      Reduced redundancy

❏      Query language

❏      Data consistency

❏      Security

❏      DBMS support transactions

❏      Support multiple user and concurrent access

Advantages

➢      No redundant data: Redundancy is eliminated by normalization of data. No data replication saves bandwidth and increases access time.

➢      Data security: In database systems, it is simpler to add access restrictions so that only authorized users can access the data. Each consumer has a different access set, so data is shielded from problems such as identity theft, data leaks, and data misuse.

➢      Easy recovery: Since database systems maintain data backup, in the event of a malfunction, it is easier to do a complete data recovery.

➢      Flexible: Database systems are more flexible than file processing systems.

➢      Data integrity: There may be times where it is appropriate to apply such restrictions to the data before entering it into the database. No mechanism is provided by the file system to verify these constraints automatically. Whereas by imposing user-defined data limitations on itself, DBMS preserves data integrity.

➢      Data concurrency: Concurrent data access means that more than one user is concurrently accessing the same data. Anomalies occurs when one user's modifications are lost due to changes made by other users. Any procedure to avoid anomalies is not provided by the file system. In comparison, DBMS offers a locking mechanism to avoid the occurrence of anomalies.

Q. 3) Define the data model, explain any two?

Ans: Data model

Data models are used to describe the design of a database at the logical level.

Some of the data models are: -

1. Entity-Relationship Model

2. Relational Model

3. Hierarchical Model

4. Network Model

5. Object Relational Model

1 Entity-Relationship(E-R) Model: -

●      The logical structure of a database can be expressed by an E-R diagram.

●      Relationships are defined in this database model by dividing the object of interest into an entity and its characteristics into attributes.

●      Using relationships, various entities are related.

●      To make it easier for various stakeholders to understand, E-R models are established to represent relationships in pictorial form.

The Main Components of ER Diagram are: -

a) Rectangles - Entity Sets

b) Ellipses - Attributes

c) Diamond - Relation among entity sets

d) Lines - Connects attributes to entity sets and entity sets to relationships.

Relationship can be of the type:

1:1→One to one

1:M →One to many

M:1 →Many to one

M:M →Many to many

The above E-R diagram has two entities: Teacher and Department

Teacher entity has three attributes:

Teacher_id

Teacher_name

Teacher_Subject

Department entity has two attributes: -

Dept_id

Dept_name

Relation Teaches in gives relationship between Teacher, Department.

2 Relational Model: -

●      Relational model uses a collection of tables to represent both data and the relationships among those data.

●      This model is useful for constructing a database that can then be converted into relational model tables.

●      Data is arranged in two-dimensional tables in this model and the relationship is preserved by storing a common field.

●      In the relational model, the basic structure of data is tables. In the rows of that table, all information relating to a specific category is stored.

Therefore, in the relational model, tables are also known as relations.

Sample relational database

1. Teacher Table

B.     Department Table

C.    Teaches in table

Q. 4) What are the components of DBMS?

Ans: Components of DBMS

You may split the database management system into five main components, which are

1. Hardware
2. Software
3. Data
4. Procedure
5. Database Access Language

In order to see how they all work together to form a database management system, let's have a basic diagram.

1. Hardware

When we say hardware, before any data is successfully stored in the memory, we mean computers, hard discs, I/O channels for data, and any other physical part involved.

When we run Oracle or MySQL on our personal computer, the hard disc of our computer, the keyboard we use to type all the commands, the RAM of our computer, the ROM, all become part of the hardware of the DBMS.

2.     Software

As this is the software that governs everything, this is the key part. More like a wrapper around the physical database, the DBMS program me gives us an easy-to-use interface for storing, accessing and upgrading data.

The DBMS program me is able to understand and translate the Database Access Language into actual database commands in order to execute them on the DB.

3.     Data

Data is the resource for which the DBMS is intended. The motive behind the creation of DBMS was to store and use information.

Data saved by the user is present in a standard database and meta data is stored.

Metadata is data about knowledge. To better understand the data contained in it, this is information stored by the DBMS.

4.     Procedure

Procedures refer to general instructions for using a method to handle a database. This includes procedures for setting up and installing a DBMS, logging in and logging out of the DBMS program me, database maintenance, backups, report creation, etc.

5.     Database Access language

Database Access Language is a basic language designed to access, attach, update and delete data stored in any database by writing commands.

In the Database Access Language, a user can write commands and send them to the DBMS for execution, which is then interpreted and executed by the DBMS.

The user can use the access language to build new databases, tables, insert data, fetch stored data, update data and remove data.

Q. 5) What do you mean by the overall structure of DBMS?

Ans: Structure of DBMS

A system of databases is divided into modules dealing with each of the overall system's responsibilities. A database system's functional components can be narrowly divided into the components of the storage manager and the query processor. The storage manager is essential since a large amount of storage space is usually needed for databases. The query processor is important because it simplifies and promotes access to data by supporting the database system.

It is the responsibility of the database system to convert, at the logical level, changes and queries written in a non-procedural language into an effective sequence of physical level operations.

The database system is divided into three components: Query Processor, Storage Manager, and Disk Storage

❖      Query processor

It interprets the requests (queries) received from the end user into instructions through an application program me. Also, the user request that is received from the DML compiler is executed.

The Query Processor comprises the following parts:

●      DML compiler

The DML statements are processed into low-level (machine language) instructions so that they can be implemented.

●      DDL interpreter

The DDL statements are processed into a table set containing meta data (data about data).

●      Query optimization

Typically, a question can be converted into any of a variety of alternative assessment plans that all offer the same outcome. The DML compiler also performs query optimization, which is to say, among the alternatives, it selects the lowest cost evaluation plan.

●      Query evaluation engine

That executes the low-level instructions that the DML compiler generates.

❖      Storage manager

A storage manager is a software module that provides an interface between the low-level data stored in the database and the system-submitted application programs and queries. Interaction with the file manager is the responsibility of the storage manager. Using the file system, which is typically supported by a conventional operating system, raw data is stored on the disc. The storage manager converts the different DML statements into low-level commands for the file system. It is also the duty of the storage manager to store, retrieve, and update data in the database.

The components for the storage manager include:

●      Authorization and integrity manager

This ensures role-based regulation of access, i.e., It checks whether or not the individual is entitled to conduct the requested activity.

When the database is updated, the integrity manager checks the integrity constraints.

●      Transaction manager

It manages simultaneous access by executing the operations in a scheduled manner in which the transaction is received. It also ensures that the database stays in a consistent state before and after a transaction has been executed.

●      File manager

It manages the space of the file and the data structure used in the database to represent information.

●      Buffer manager

The cache memory and data transfer between the secondary storage and the main memory are responsible for this.

❖      Disk storage

It includes the following elements-

●      Data files

It stores the data.

●      Data dictionary

It includes details about any database object's structure. It is the information repository that regulates metadata.

●      Indices

It provides faster retrieval of data items.

Q. 6) Write short notes on system catalogs?

Ans: System catalogs

A system catalogue is a group of tables and views that incorporate a database's vital information. The framework of the database is defined by any database consisting of a system catalogue and the information in the system catalogue.

For example, the language of the data dictionary (DDL) is saved in the system catalogue for every table in the database.

A critical element of a database is the system catalogue. There are items within the database, which include tables, views and indexes. The device catalogue is basically a collection of objects that contains information that defines:

It is possible to divide the device catalogue intended for implementation into logical groups of objects. This is to deliver tables that are not only available by the database administrator, but also by all other users of the database. For example, users may want to see the unique database rights they have been granted; however, they do not need to find out about the processes or internal structure of the database.

In order to obtain knowledge about the user's own objects as well as rights, a user normally looks up the system catalogue, while the database administrator must be able to ask about every occurrence or structure within the database.

Q. 7) Define the mapping constraints?

Ans: mapping constraints

●      A mapping constraint is a data constraint representing the number of entities to which a relationship set can relate to another entity.

●      It is most useful to define relationship sets involving more than two sets of individuals.

●      There are four possible mapping cardinalities for the binary relationship set R on an entity set A and B. They are as follows

1. One to one (1:1)
2. One to many (1:M)
3. Many to many (M:M)

One to one: As we have seen in the example above, only one instance of an entity is mapped to one instance of another entity. Consider the Department's HOD. Just one HOD in one department exists. That is, the relationship between the HOD agency and the Department is 1:1.

One to many: As we can guess now, there is one instance of an entity linked to many instances of another entity between one and several relationships. In his department, one manager oversees several employees.

Many to many: This is a relationship where multiple entity instances are linked to multiple entity instances.

Q. 8) What are the different categories of key?

Ans: keys

Keys are the entity's attributes, which define the entity's record uniquely.

Several types of keys exist.

These are listed underneath:

❏      Composite key

A composite key consists of two attributes or more, but it must be minimal.

❏      Candidate key

A candidate key is a key that is simple or composite and special and minimal. It is special since no two rows can have the same value at any time in a table. It is minimal since, in order to achieve uniqueness, every column is required.

❏      Super key

The Super Key is one or more of the entity's attributes that uniquely define the database record.

❏      Primary key

The primary key is a candidate key that the database designer chooses to be used as an identification mechanism for the entire set of entities. In a table, it must uniquely classify tuples and not be null.

In the ER model, the primary key is indicated by underlining the attribute.

●      To uniquely recognize tuples in a table, the designer selects a candidate key. It must not be empty.

●      A key is selected by the database builder to be used by the entire entity collection as an authentication mechanism. This is regarded as the primary key. In the ER model, this key is indicated by underlining the attribute.

❏      Alternate key

Alternate keys are all candidate keys not chosen as the primary key.

❏      Foreign key

A foreign key (FK) is an attribute in a table that, in another table, references the primary key OR it may be null. The same data type must be used for both international and primary keys.

❏      Secondary key

A secondary key is an attribute used exclusively (can be composite) for retrieval purposes, such as: phone and last name.

Q. 9) What are the different forms of Attributes?

Ans: forms of attributes

❏      Simple Attribute: These types of attributes have properties that cannot be further divided. For example, the attribute STUDENT ID, which cannot be further divided. Passport Number is a unique value that cannot be divided.

❏      Composite Attribute: It is possible to further split this kind of attribute into more than one basic attribute. For instance, an individual's address. Here, the address can be further divided as Door#, Street, City, State and Pin, which are simple attributes.

❏      Derived Attribute: Derived attributes are those whose meaning can be derived from other entity attributes in the database. For example, it is possible to obtain a person's age from the date of birth and current date. Examples of derived attributes are average wage, annual salary, total marks of a student, etc.

❏      Multi - valued Attribute: At any point in time, these attributes may have more than one value. The manager can have more than one worker working for him, an employee can have more than one email address, and the examples are more than one house etc.

❏      Stored Attribute: The attribute that gives the value of the derived attribute is referred to as the Stored Attribute. In the above case, age is derived from the date of birth. The Date of Birth is also a stored attribute.

Q. 10) Write short notes on data dependency?

Ans: Data dependency

In addition to users' data, a database system usually includes a lot of data. For instance, to easily locate and retrieve data, it stores information about data, known as metadata.

Modifying or modifying a collection of metadata once it is placed in the database is very difficult. But when a DBMS expands, in order to fulfil the users' requirements, it needs to evolve over time. It will become a boring and highly complicated job if the entire data is based.

Metadata itself follows a layered model, such that it does not impact the data at another level when we modify data on one layer. This data is independent, but is mapped to one another.

Types of data independences

❏      Physical data independence

Physical Data Independence is defined as the ability to make adjustments without affecting the higher-level schemas in the structure of the lowest level of the Database Management System (DBMS). Therefore, the Physical level adjustment does not result in any adjustments to the Conceptual or View levels.

❏      Logical data independence

Logical Data Independence is defined as the ability to make improvements to the Database Management System (DBMS) middle level structure without affecting the schema or application programs at the highest level. Changes to the conceptual level should also not result in any changes to the view levels or programs of the application.

Unit 1

Introduction to DBMS

Q. 1) Write the database language?

Ans: Database language

Users can interact with DBMS using database languages. There are two types of database languages: -

1. The Data Definition Language (DDL)

2. The Data Manipulation Language (DML)

1 DDL: -

DDL is used to define the conceptual schema.

It includes: -

A. Entity

B. Attributes and types

C. Relation among entity sets.

The definition also includes constraints on values that can be assigned to a given attribute.

For example:

>create table emp (name varchar (20), emp_id number NOT NULL);

Execution will create an empty table emp. Definitions are stored along with the database.

E.g., Create table, view, index

Alter table.

Drop table, view, index

2 DML: -

i. DML allows user to manipulate data stored in database.

Ii. E.g.

●      Data Retrieval

●      Data Insertion

●      Data Deletion

●      Data Modification.

Iii. DML provides commands for data manipulation.

E.g., select name from emp

Where

Salary > 15000;

Iv. DML commands can be either in interactive mode or these commands can be

Embedded in programming languages like C, VB, Java etc.

Types of DML: -

a. Procedural: -

It requires a user to specify what data is needed and how to retrieve it from database. The user instructs the system to perform a sequence of options on the database to compute the desired result.

b. Non-Procedural: -

It requires a user to specify what data is needed without specifying how to get it.

3 DCL (Data Control Language): -

E.g. (commit, rollback, save point, grant, revoke.)

It consists of commands that control the user access to database objects.

Q. 2) What is DBMS? and also write the advantages of DBMS over file processing system?

Ans: DBMS (Data Base Management System)

Database

The database is a set of interrelated data used to efficiently retrieve, insert and remove data. It is also used in the form of a table, schema, views, and reports to organize the data, etc.

You can conveniently retrieve, attach, and delete information using the database.

Database management system (DBMS)

A DBMS is a program that enables database creation, specification and manipulation, allowing users to easily store, process and analyze information.

DBMS provides one with an interface or a tool to conduct different operations, such as building databases, storing data in them, updating data, creating database tables, and much more.

The DBMS also provides databases with privacy and security. In the case of multiple users, it also ensures data consistency.

Some Example of DBMS:

●      MySQL

●      Oracle

●      SQL Server

●      IBM DB2

Characteristics of DBMS

❏      Data stored into tables

❏      Reduced redundancy

❏      Query language

❏      Data consistency

❏      Security

❏      DBMS support transactions

❏      Support multiple user and concurrent access

Advantages

➢      No redundant data: Redundancy is eliminated by normalization of data. No data replication saves bandwidth and increases access time.

➢      Data security: In database systems, it is simpler to add access restrictions so that only authorized users can access the data. Each consumer has a different access set, so data is shielded from problems such as identity theft, data leaks, and data misuse.

➢      Easy recovery: Since database systems maintain data backup, in the event of a malfunction, it is easier to do a complete data recovery.

➢      Flexible: Database systems are more flexible than file processing systems.

➢      Data integrity: There may be times where it is appropriate to apply such restrictions to the data before entering it into the database. No mechanism is provided by the file system to verify these constraints automatically. Whereas by imposing user-defined data limitations on itself, DBMS preserves data integrity.

➢      Data concurrency: Concurrent data access means that more than one user is concurrently accessing the same data. Anomalies occurs when one user's modifications are lost due to changes made by other users. Any procedure to avoid anomalies is not provided by the file system. In comparison, DBMS offers a locking mechanism to avoid the occurrence of anomalies.

Q. 3) Define the data model, explain any two?

Ans: Data model

Data models are used to describe the design of a database at the logical level.

Some of the data models are: -

1. Entity-Relationship Model

2. Relational Model

3. Hierarchical Model

4. Network Model

5. Object Relational Model

1 Entity-Relationship(E-R) Model: -

●      The logical structure of a database can be expressed by an E-R diagram.

●      Relationships are defined in this database model by dividing the object of interest into an entity and its characteristics into attributes.

●      Using relationships, various entities are related.

●      To make it easier for various stakeholders to understand, E-R models are established to represent relationships in pictorial form.

The Main Components of ER Diagram are: -

a) Rectangles - Entity Sets

b) Ellipses - Attributes

c) Diamond - Relation among entity sets

d) Lines - Connects attributes to entity sets and entity sets to relationships.

Relationship can be of the type:

1:1→One to one

1:M →One to many

M:1 →Many to one

M:M →Many to many

The above E-R diagram has two entities: Teacher and Department

Teacher entity has three attributes:

Teacher_id

Teacher_name

Teacher_Subject

Department entity has two attributes: -

Dept_id

Dept_name

Relation Teaches in gives relationship between Teacher, Department.

2 Relational Model: -

●      Relational model uses a collection of tables to represent both data and the relationships among those data.

●      This model is useful for constructing a database that can then be converted into relational model tables.

●      Data is arranged in two-dimensional tables in this model and the relationship is preserved by storing a common field.

●      In the relational model, the basic structure of data is tables. In the rows of that table, all information relating to a specific category is stored.

Therefore, in the relational model, tables are also known as relations.

Sample relational database

1. Teacher Table

B.     Department Table

C.    Teaches in table

Q. 4) What are the components of DBMS?

Ans: Components of DBMS

You may split the database management system into five main components, which are

1. Hardware
2. Software
3. Data
4. Procedure
5. Database Access Language

In order to see how they all work together to form a database management system, let's have a basic diagram.

1. Hardware

When we say hardware, before any data is successfully stored in the memory, we mean computers, hard discs, I/O channels for data, and any other physical part involved.

When we run Oracle or MySQL on our personal computer, the hard disc of our computer, the keyboard we use to type all the commands, the RAM of our computer, the ROM, all become part of the hardware of the DBMS.

2.     Software

As this is the software that governs everything, this is the key part. More like a wrapper around the physical database, the DBMS program me gives us an easy-to-use interface for storing, accessing and upgrading data.

The DBMS program me is able to understand and translate the Database Access Language into actual database commands in order to execute them on the DB.

3.     Data

Data is the resource for which the DBMS is intended. The motive behind the creation of DBMS was to store and use information.

Data saved by the user is present in a standard database and meta data is stored.

Metadata is data about knowledge. To better understand the data contained in it, this is information stored by the DBMS.

4.     Procedure

Procedures refer to general instructions for using a method to handle a database. This includes procedures for setting up and installing a DBMS, logging in and logging out of the DBMS program me, database maintenance, backups, report creation, etc.

5.     Database Access language

Database Access Language is a basic language designed to access, attach, update and delete data stored in any database by writing commands.

In the Database Access Language, a user can write commands and send them to the DBMS for execution, which is then interpreted and executed by the DBMS.

The user can use the access language to build new databases, tables, insert data, fetch stored data, update data and remove data.

Q. 5) What do you mean by the overall structure of DBMS?

Ans: Structure of DBMS

A system of databases is divided into modules dealing with each of the overall system's responsibilities. A database system's functional components can be narrowly divided into the components of the storage manager and the query processor. The storage manager is essential since a large amount of storage space is usually needed for databases. The query processor is important because it simplifies and promotes access to data by supporting the database system.

It is the responsibility of the database system to convert, at the logical level, changes and queries written in a non-procedural language into an effective sequence of physical level operations.

The database system is divided into three components: Query Processor, Storage Manager, and Disk Storage

❖      Query processor

It interprets the requests (queries) received from the end user into instructions through an application program me. Also, the user request that is received from the DML compiler is executed.

The Query Processor comprises the following parts:

●      DML compiler

The DML statements are processed into low-level (machine language) instructions so that they can be implemented.

●      DDL interpreter

The DDL statements are processed into a table set containing meta data (data about data).

●      Query optimization

Typically, a question can be converted into any of a variety of alternative assessment plans that all offer the same outcome. The DML compiler also performs query optimization, which is to say, among the alternatives, it selects the lowest cost evaluation plan.

●      Query evaluation engine

That executes the low-level instructions that the DML compiler generates.

❖      Storage manager

A storage manager is a software module that provides an interface between the low-level data stored in the database and the system-submitted application programs and queries. Interaction with the file manager is the responsibility of the storage manager. Using the file system, which is typically supported by a conventional operating system, raw data is stored on the disc. The storage manager converts the different DML statements into low-level commands for the file system. It is also the duty of the storage manager to store, retrieve, and update data in the database.

The components for the storage manager include:

●      Authorization and integrity manager

This ensures role-based regulation of access, i.e., It checks whether or not the individual is entitled to conduct the requested activity.

When the database is updated, the integrity manager checks the integrity constraints.

●      Transaction manager

It manages simultaneous access by executing the operations in a scheduled manner in which the transaction is received. It also ensures that the database stays in a consistent state before and after a transaction has been executed.

●      File manager

It manages the space of the file and the data structure used in the database to represent information.

●      Buffer manager

The cache memory and data transfer between the secondary storage and the main memory are responsible for this.

❖      Disk storage

It includes the following elements-

●      Data files

It stores the data.

●      Data dictionary

It includes details about any database object's structure. It is the information repository that regulates metadata.

●      Indices

It provides faster retrieval of data items.

Q. 6) Write short notes on system catalogs?

Ans: System catalogs

A system catalogue is a group of tables and views that incorporate a database's vital information. The framework of the database is defined by any database consisting of a system catalogue and the information in the system catalogue.

For example, the language of the data dictionary (DDL) is saved in the system catalogue for every table in the database.

A critical element of a database is the system catalogue. There are items within the database, which include tables, views and indexes. The device catalogue is basically a collection of objects that contains information that defines:

It is possible to divide the device catalogue intended for implementation into logical groups of objects. This is to deliver tables that are not only available by the database administrator, but also by all other users of the database. For example, users may want to see the unique database rights they have been granted; however, they do not need to find out about the processes or internal structure of the database.

In order to obtain knowledge about the user's own objects as well as rights, a user normally looks up the system catalogue, while the database administrator must be able to ask about every occurrence or structure within the database.

Q. 7) Define the mapping constraints?

Ans: mapping constraints

●      A mapping constraint is a data constraint representing the number of entities to which a relationship set can relate to another entity.

●      It is most useful to define relationship sets involving more than two sets of individuals.

●      There are four possible mapping cardinalities for the binary relationship set R on an entity set A and B. They are as follows

1. One to one (1:1)
2. One to many (1:M)
3. Many to many (M:M)

One to one: As we have seen in the example above, only one instance of an entity is mapped to one instance of another entity. Consider the Department's HOD. Just one HOD in one department exists. That is, the relationship between the HOD agency and the Department is 1:1.

One to many: As we can guess now, there is one instance of an entity linked to many instances of another entity between one and several relationships. In his department, one manager oversees several employees.

Many to many: This is a relationship where multiple entity instances are linked to multiple entity instances.

Q. 8) What are the different categories of key?

Ans: keys

Keys are the entity's attributes, which define the entity's record uniquely.

Several types of keys exist.

These are listed underneath:

❏      Composite key

A composite key consists of two attributes or more, but it must be minimal.

❏      Candidate key

A candidate key is a key that is simple or composite and special and minimal. It is special since no two rows can have the same value at any time in a table. It is minimal since, in order to achieve uniqueness, every column is required.

❏      Super key

The Super Key is one or more of the entity's attributes that uniquely define the database record.

❏      Primary key

The primary key is a candidate key that the database designer chooses to be used as an identification mechanism for the entire set of entities. In a table, it must uniquely classify tuples and not be null.

In the ER model, the primary key is indicated by underlining the attribute.

●      To uniquely recognize tuples in a table, the designer selects a candidate key. It must not be empty.

●      A key is selected by the database builder to be used by the entire entity collection as an authentication mechanism. This is regarded as the primary key. In the ER model, this key is indicated by underlining the attribute.

❏      Alternate key

Alternate keys are all candidate keys not chosen as the primary key.

❏      Foreign key

A foreign key (FK) is an attribute in a table that, in another table, references the primary key OR it may be null. The same data type must be used for both international and primary keys.

❏      Secondary key

A secondary key is an attribute used exclusively (can be composite) for retrieval purposes, such as: phone and last name.

Q. 9) What are the different forms of Attributes?

Ans: forms of attributes

❏      Simple Attribute: These types of attributes have properties that cannot be further divided. For example, the attribute STUDENT ID, which cannot be further divided. Passport Number is a unique value that cannot be divided.

❏      Composite Attribute: It is possible to further split this kind of attribute into more than one basic attribute. For instance, an individual's address. Here, the address can be further divided as Door#, Street, City, State and Pin, which are simple attributes.

❏      Derived Attribute: Derived attributes are those whose meaning can be derived from other entity attributes in the database. For example, it is possible to obtain a person's age from the date of birth and current date. Examples of derived attributes are average wage, annual salary, total marks of a student, etc.

❏      Multi - valued Attribute: At any point in time, these attributes may have more than one value. The manager can have more than one worker working for him, an employee can have more than one email address, and the examples are more than one house etc.

❏      Stored Attribute: The attribute that gives the value of the derived attribute is referred to as the Stored Attribute. In the above case, age is derived from the date of birth. The Date of Birth is also a stored attribute.

Q. 10) Write short notes on data dependency?

Ans: Data dependency

In addition to users' data, a database system usually includes a lot of data. For instance, to easily locate and retrieve data, it stores information about data, known as metadata.

Modifying or modifying a collection of metadata once it is placed in the database is very difficult. But when a DBMS expands, in order to fulfil the users' requirements, it needs to evolve over time. It will become a boring and highly complicated job if the entire data is based.

Metadata itself follows a layered model, such that it does not impact the data at another level when we modify data on one layer. This data is independent, but is mapped to one another.

Types of data independences

❏      Physical data independence

Physical Data Independence is defined as the ability to make adjustments without affecting the higher-level schemas in the structure of the lowest level of the Database Management System (DBMS). Therefore, the Physical level adjustment does not result in any adjustments to the Conceptual or View levels.

❏      Logical data independence

Logical Data Independence is defined as the ability to make improvements to the Database Management System (DBMS) middle level structure without affecting the schema or application programs at the highest level. Changes to the conceptual level should also not result in any changes to the view levels or programs of the application.