Unit - 2

Relational Model

Q. 1) Write down the Codd’s rules?

Ans: Codd’s Rules

These principles can be extended to any database system that only uses its relational features to handle stored data. This is a simple rule that serves as the basis for all the other rules.

1. Information Rule: The data contained in a database must be the value of a table cell, whether it is user data or metadata. It is important to store everything in a database in a table format.

2.     Guaranteed Access Rule: Each data element must be accessible by means of the name of the table, its primary key, and the name of the attribute whose meaning is to be determined.

3.     Systematic Treatment of NULL values: A systematic and uniform treatment must be given to the NULL values in a database. This is a very relevant rule since it is possible to interpret a NULL as one of the following: information is missing, information is not known or information is not applicable.

4.     Active Online Catalog: The definition of the structure of the whole database must be stored in an online catalogue, known as a data dictionary, accessible by registered users. The same query language can be used by users to access the catalogue that they use to access the database itself.

5.     Comprehensive Data Sublanguage Rule: A database should be available in a language that is supported for the process of description, manipulation and transaction management.

6.     View Updating Rule: Various views that are generated for different purposes should be automatically modified by the framework.

7.     High level insert, update and delete rule: High-level addition, upgrading, and removal must be assisted by a database. This must not be limited to a single row, which means that union, intersection and minus operations must also be assisted in order to generate data record sets.

8.     Physical data independence: At each relationship level, the Relational Model should support insert, remove, update, etc. operations. Set operations such as Union, Intersection and minus should also be endorsed.

9.     Logical data independence: Any alteration of a table's logical or conceptual schema does not involve modification at the level of the application. Merging two tables into one, for example, does not impact access to the application, which is difficult to do.

10. Integrity Independence: Changed integrity restrictions at the database level do not implement changes at the application level.

11. Distribution Independence: For end-users, the distribution of data over different locations should not be noticeable.

12. Non-Subversion Rule: Low level access to data should not be able to circumvent honesty rules to alter data.

Q. 2) Write short notes on the schema diagram?

Ans: schemas diagram

●      An instance of the database is called the data that is stored in the database at a specific moment in time.

●      A database's overall architecture is called a schema.

●      The skeleton structure of the data base is a database schema. It reflects the rational view of the database as a whole.

●      A schema includes schema objects such as a table, a foreign key, a primary key, views, columns, data types, processes stored, etc.

●      By using a visual diagram, a database schema can be represented. This diagram displays the objects of the database and their relationship to each other.

●      Database designers develop a database schema to assist programmers whose software communicates with the database. The database development method is called data modelling.

Only certain elements of a schema, such as the name of the record type, data type, and constraints, can be seen in a schema diagram. You can't define other things through the schema diagram. The provided figure, for instance, does not indicate either the data form of each data item or the relationship between different files.

Real data changes very often inside the database. For example, the database changes whenever we add a new grade or add a student. The data is called the instance of the database at a given moment of time.

Q. 3) How to convert ER diagrams into tables?

Ans: Converting ER diagrams into Tables

Using notations, the database can be represented, and these notations can be reduced to a set of tables.

Each entity set or relationship set can be represented in tabular form in the database.

The ER diagram is given below:

There are some points for converting the ER diagram to the table:

In the given ER diagram, LECTURE, STUDENT, SUBJECT and COURSE forms individual tables.

In the STUDENT entity, STUDENT_NAME and STUDENT_ID form the column of STUDENT table. Similarly, COURSE_NAME and COURSE_ID form the column of COURSE table and so on.

In the given ER diagram, COURSE_ID, STUDENT_ID, SUBJECT_ID, and LECTURE_ID are the key attribute of the entity.

A hobby is a multivalued feature inside the student table. Thus, multiple values cannot be expressed in a single column of the STUDENT table. We therefore generate a STUD HOBBY table with the STUDENT ID and HOBBY column names. We create a composite key by using both columns.

Student address is a composite attribute in the given ER diagram. CITY, PIN, DOOR#, Path, and STATE are included. These attributes will merge as an individual column in the STUDENT table.

In the STUDENT table, the derived attribute is age. It can be determined by measuring the difference between the present date and the date of birth at any time.

You can convert the ER diagram to tables and columns using these rules and assign the mapping between the tables.

The following is the table structure for the given ER diagram:

Q. 4) What do you mean by relational model?

Ans: Relational Model

The primary data model is the Relational Data Model, which is commonly used for data storage and processing around the world. This model is simple and has all the features and functionality needed to process data with efficiency in storage.

The relational model can be interpreted as a table with rows and columns. Each row is called a tuple. There's a name or attribute for each table in the column.

Basic terminology:

Table: Relationships are saved in the format of tables in a relational data model. The relationship between entities is stored in this format. A table includes rows and columns, where rows represent information, and attributes are represented by columns.

Tuple: A tuple is called a single row of a table, which contains a single record for that relationship.

Domain: It includes a set of atomic values that can be adopted by an attribute.

Attribute: In a specific table, it includes the name of a column. Every Ai attribute must have a domain, a domain (Ai)

Relational instance: The relational example is represented in the relational database structure by a finite set of tuples. There are no duplicate tuples for relation instances.

Relational schema: The name of the relationship and the name of all columns or attributes are used in a relational schema.

Relational key: Each row has one or more attributes in the relational key. It can uniquely identify the row in the association.

Example: STUDENT Relation

Q. 5) Define Enterprise constraints and view?

Ans: Enterprise constraints

Enterprise constraints are additional rules that users or database managers define and may be based on several tables, often referred to as semantic constraints.

Some explanations are here.

●      There can be a maximum of 30 students for a class.

●      A maximum of four classes per semester can be taught by an instructor.

●      An employee is unable to engage in more than five programs.

●      An employee's compensation cannot exceed the employee's manager's salary.

View

A view is just a relationship, but rather than a definition, we store a definition, A set of tuples.

Views can be lowered using the command DROP VIEW.

How can you treat DROP TABLE if there is a table view?

●      The DROP TABLE command allows the user to define this choice.

●      Create VIEW (name, grade) YoungActiveStudents AS SELECT

S.name, E.grade FROM Students S, Registered E WHERE S.sid =

E.sid and S.age<21 S.age

Q. 6) What do you mean by convention in ER diagram?

Ans: Conventions

The choice of names for entity types, attributes, relationship types, and (particularly) functions is not always straightforward when designing a database schema. Names that express, as far as possible, the meanings attached to the various constructs in the schema should be selected.

For entity forms, rather than plural ones, we prefer to use singular names since the name of the entity type refers to each particular entity belonging to that type of entity.

We will use the convention in our ER diagrams that entity type and relationship type names are uppercase letters, attribute names are capitalized by their initial letter, and position names are lowercase letters.

In general practice, the nouns appearing in the narrative tend to give rise to entity form names, given a narrative definition of the database requirements, and the verbs tend to imply names of types of relationships. Names of attributes typically come from additional nouns which define the nouns corresponding to the types of entity.

Q. 7) Define the term “Relationship “?

Ans: Relationship

In the Entity-Relation Model, a relationship is used to define the relationship between two or more entities. In the ER diagram, it is illustrated by a diamond shape.

For example, in college student studies, employees work in a department. Here, the links are 'research in' and 'works in'.

Degree of Relationship

A partnership is called the degree of a relationship in which a variety of different individuals participate.

Degree of relationship can be categorized into the following types:

1. Unary Relationship:

A relationship in which a single group of individuals is involved is referred to as a unary relationship. For instance, in a company, an employee manages or supervises another employee.

2. Binary Relationship: When a relationship includes two people, it is considered a binary relationship.

3. Ternary Relationship: When a relationship contains three individual sets, a ternary relationship is called.

4. N-nary Relationship:

If a relationship includes more than three entity sets, an n-ary relationship is named.

Q. 8) Define nulls and entity integrity?

Ans: Nulls

In databases, what value or placeholder you use to reflect a missing value is a common problem. This is solved with null in SQL. It is used to denote values which are absent or unknown.

To show these values, the keyword NULL is used. NULL is not even as much of a particular attribute as it is an indicator. Don't think of NULL as being zero or blank, but it's not the same thing. The values are zero (0) and blank " ".

Each NULL value is commonly considered to be different from any other NULL in the database. The outcome is assumed to be UNKNOWN when a NULL is involved in a comparison process.

Entity integrity

●      The honesty restriction of the organisation states that the primary key value should not be zero.

●      This is because the primary key value is used to define the relationship between individual rows, and if the primary key has a null value, then those rows cannot be identified.

●      A table may contain a null value other than the field for the primary key.

Example

Q. 9) Describe the word “Entity “?

Ans: Entity

The object, location, person, or event that stores data in the database may be an entity. In an object-relationship diagram, a rectangle represents an entity.

Examples of an individual include a student, course, boss, employee, patient, etc.

Entity type:

A list or a set of entities having certain common attributes is an entity type. In a database, a name and a list of attributes define each type of entity.

Entity set:

It is a set (or collection) of entities of the same kind that share attributes or related properties.

For example, it is possible to describe the category of individuals who are lecturers at a university as an entity-set lecturer. Similarly, the collection of students of the organisation could represent the community of all university students.

Q. 10) What is referential integrity?

Ans: Referential Integrity

●      Between two relations or tables, the referential integrity constraints are defined and used to preserve the consistency between the tuples in two relationships.

●      If an attribute of the foreign key of the relationship R1 has the same domain(s) as the primary key of the relationship R2, then the foreign key of R1 is said to refer to or refer to the primary key of the relationship R2.

●      Foreign key values in the R1 relationship tuple can either take the primary key values for a certain R2 relationship tuple, or they can take NULL values, but cannot be zero.

Example

Unit - 2

Relational Model

Q. 1) Write down the Codd’s rules?

Ans: Codd’s Rules

These principles can be extended to any database system that only uses its relational features to handle stored data. This is a simple rule that serves as the basis for all the other rules.

1. Information Rule: The data contained in a database must be the value of a table cell, whether it is user data or metadata. It is important to store everything in a database in a table format.

2.     Guaranteed Access Rule: Each data element must be accessible by means of the name of the table, its primary key, and the name of the attribute whose meaning is to be determined.

3.     Systematic Treatment of NULL values: A systematic and uniform treatment must be given to the NULL values in a database. This is a very relevant rule since it is possible to interpret a NULL as one of the following: information is missing, information is not known or information is not applicable.

4.     Active Online Catalog: The definition of the structure of the whole database must be stored in an online catalogue, known as a data dictionary, accessible by registered users. The same query language can be used by users to access the catalogue that they use to access the database itself.

5.     Comprehensive Data Sublanguage Rule: A database should be available in a language that is supported for the process of description, manipulation and transaction management.

6.     View Updating Rule: Various views that are generated for different purposes should be automatically modified by the framework.

7.     High level insert, update and delete rule: High-level addition, upgrading, and removal must be assisted by a database. This must not be limited to a single row, which means that union, intersection and minus operations must also be assisted in order to generate data record sets.

8.     Physical data independence: At each relationship level, the Relational Model should support insert, remove, update, etc. operations. Set operations such as Union, Intersection and minus should also be endorsed.

9.     Logical data independence: Any alteration of a table's logical or conceptual schema does not involve modification at the level of the application. Merging two tables into one, for example, does not impact access to the application, which is difficult to do.

10. Integrity Independence: Changed integrity restrictions at the database level do not implement changes at the application level.

11. Distribution Independence: For end-users, the distribution of data over different locations should not be noticeable.

12. Non-Subversion Rule: Low level access to data should not be able to circumvent honesty rules to alter data.

Q. 2) Write short notes on the schema diagram?

Ans: schemas diagram

●      An instance of the database is called the data that is stored in the database at a specific moment in time.

●      A database's overall architecture is called a schema.

●      The skeleton structure of the data base is a database schema. It reflects the rational view of the database as a whole.

●      A schema includes schema objects such as a table, a foreign key, a primary key, views, columns, data types, processes stored, etc.

●      By using a visual diagram, a database schema can be represented. This diagram displays the objects of the database and their relationship to each other.

●      Database designers develop a database schema to assist programmers whose software communicates with the database. The database development method is called data modelling.

Only certain elements of a schema, such as the name of the record type, data type, and constraints, can be seen in a schema diagram. You can't define other things through the schema diagram. The provided figure, for instance, does not indicate either the data form of each data item or the relationship between different files.

Real data changes very often inside the database. For example, the database changes whenever we add a new grade or add a student. The data is called the instance of the database at a given moment of time.

Q. 3) How to convert ER diagrams into tables?

Ans: Converting ER diagrams into Tables

Using notations, the database can be represented, and these notations can be reduced to a set of tables.

Each entity set or relationship set can be represented in tabular form in the database.

The ER diagram is given below:

There are some points for converting the ER diagram to the table:

In the given ER diagram, LECTURE, STUDENT, SUBJECT and COURSE forms individual tables.

In the STUDENT entity, STUDENT_NAME and STUDENT_ID form the column of STUDENT table. Similarly, COURSE_NAME and COURSE_ID form the column of COURSE table and so on.

In the given ER diagram, COURSE_ID, STUDENT_ID, SUBJECT_ID, and LECTURE_ID are the key attribute of the entity.

A hobby is a multivalued feature inside the student table. Thus, multiple values cannot be expressed in a single column of the STUDENT table. We therefore generate a STUD HOBBY table with the STUDENT ID and HOBBY column names. We create a composite key by using both columns.

Student address is a composite attribute in the given ER diagram. CITY, PIN, DOOR#, Path, and STATE are included. These attributes will merge as an individual column in the STUDENT table.

In the STUDENT table, the derived attribute is age. It can be determined by measuring the difference between the present date and the date of birth at any time.

You can convert the ER diagram to tables and columns using these rules and assign the mapping between the tables.

The following is the table structure for the given ER diagram:

Q. 4) What do you mean by relational model?

Ans: Relational Model

The primary data model is the Relational Data Model, which is commonly used for data storage and processing around the world. This model is simple and has all the features and functionality needed to process data with efficiency in storage.

The relational model can be interpreted as a table with rows and columns. Each row is called a tuple. There's a name or attribute for each table in the column.

Basic terminology:

Table: Relationships are saved in the format of tables in a relational data model. The relationship between entities is stored in this format. A table includes rows and columns, where rows represent information, and attributes are represented by columns.

Tuple: A tuple is called a single row of a table, which contains a single record for that relationship.

Domain: It includes a set of atomic values that can be adopted by an attribute.

Attribute: In a specific table, it includes the name of a column. Every Ai attribute must have a domain, a domain (Ai)

Relational instance: The relational example is represented in the relational database structure by a finite set of tuples. There are no duplicate tuples for relation instances.

Relational schema: The name of the relationship and the name of all columns or attributes are used in a relational schema.

Relational key: Each row has one or more attributes in the relational key. It can uniquely identify the row in the association.

Example: STUDENT Relation

Q. 5) Define Enterprise constraints and view?

Ans: Enterprise constraints

Enterprise constraints are additional rules that users or database managers define and may be based on several tables, often referred to as semantic constraints.

Some explanations are here.

●      There can be a maximum of 30 students for a class.

●      A maximum of four classes per semester can be taught by an instructor.

●      An employee is unable to engage in more than five programs.

●      An employee's compensation cannot exceed the employee's manager's salary.

View

A view is just a relationship, but rather than a definition, we store a definition, A set of tuples.

Views can be lowered using the command DROP VIEW.

How can you treat DROP TABLE if there is a table view?

●      The DROP TABLE command allows the user to define this choice.

●      Create VIEW (name, grade) YoungActiveStudents AS SELECT

S.name, E.grade FROM Students S, Registered E WHERE S.sid =

E.sid and S.age<21 S.age

Q. 6) What do you mean by convention in ER diagram?

Ans: Conventions

The choice of names for entity types, attributes, relationship types, and (particularly) functions is not always straightforward when designing a database schema. Names that express, as far as possible, the meanings attached to the various constructs in the schema should be selected.

For entity forms, rather than plural ones, we prefer to use singular names since the name of the entity type refers to each particular entity belonging to that type of entity.

We will use the convention in our ER diagrams that entity type and relationship type names are uppercase letters, attribute names are capitalized by their initial letter, and position names are lowercase letters.

In general practice, the nouns appearing in the narrative tend to give rise to entity form names, given a narrative definition of the database requirements, and the verbs tend to imply names of types of relationships. Names of attributes typically come from additional nouns which define the nouns corresponding to the types of entity.

Q. 7) Define the term “Relationship “?

Ans: Relationship

In the Entity-Relation Model, a relationship is used to define the relationship between two or more entities. In the ER diagram, it is illustrated by a diamond shape.

For example, in college student studies, employees work in a department. Here, the links are 'research in' and 'works in'.

Degree of Relationship

A partnership is called the degree of a relationship in which a variety of different individuals participate.

Degree of relationship can be categorized into the following types:

1. Unary Relationship:

A relationship in which a single group of individuals is involved is referred to as a unary relationship. For instance, in a company, an employee manages or supervises another employee.

2. Binary Relationship: When a relationship includes two people, it is considered a binary relationship.

3. Ternary Relationship: When a relationship contains three individual sets, a ternary relationship is called.

4. N-nary Relationship:

If a relationship includes more than three entity sets, an n-ary relationship is named.

Q. 8) Define nulls and entity integrity?

Ans: Nulls

In databases, what value or placeholder you use to reflect a missing value is a common problem. This is solved with null in SQL. It is used to denote values which are absent or unknown.

To show these values, the keyword NULL is used. NULL is not even as much of a particular attribute as it is an indicator. Don't think of NULL as being zero or blank, but it's not the same thing. The values are zero (0) and blank " ".

Each NULL value is commonly considered to be different from any other NULL in the database. The outcome is assumed to be UNKNOWN when a NULL is involved in a comparison process.

Entity integrity

●      The honesty restriction of the organisation states that the primary key value should not be zero.

●      This is because the primary key value is used to define the relationship between individual rows, and if the primary key has a null value, then those rows cannot be identified.

●      A table may contain a null value other than the field for the primary key.

Example

Q. 9) Describe the word “Entity “?

Ans: Entity

The object, location, person, or event that stores data in the database may be an entity. In an object-relationship diagram, a rectangle represents an entity.

Examples of an individual include a student, course, boss, employee, patient, etc.

Entity type:

A list or a set of entities having certain common attributes is an entity type. In a database, a name and a list of attributes define each type of entity.

Entity set:

It is a set (or collection) of entities of the same kind that share attributes or related properties.

For example, it is possible to describe the category of individuals who are lecturers at a university as an entity-set lecturer. Similarly, the collection of students of the organisation could represent the community of all university students.

Q. 10) What is referential integrity?

Ans: Referential Integrity

●      Between two relations or tables, the referential integrity constraints are defined and used to preserve the consistency between the tuples in two relationships.

●      If an attribute of the foreign key of the relationship R1 has the same domain(s) as the primary key of the relationship R2, then the foreign key of R1 is said to refer to or refer to the primary key of the relationship R2.

●      Foreign key values in the R1 relationship tuple can either take the primary key values for a certain R2 relationship tuple, or they can take NULL values, but cannot be zero.

Example