Unit0-2

Data Modelling and SQL

Q1) Explain in brief relational data model

A1) Relational data model is the primary data model, which is used widely around the world for data storage and processing. This model is simple and it has all the properties and capabilities required to process data with storage efficiency.

Concepts

Tables−In relational data model, relations are saved in the format of Tables. This format stores the relation among entities. A table has rows and columns, where rows represents records and columns represent the attributes.

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

Relation instance− A finite set of tuples in the relational database system represents relation instance. Relation instances do not have duplicate tuples.

Relation schema− A relation schema describes the relation name (table name), attributes, and their names.

Relation key−Each row has one or more attributes, known as relation key, which can identify the row in the relation (table) uniquely.

Attribute domain−Every attribute has some pre-defined value scope, known as attribute domain.

Constraints

Every relation has some conditions that must hold for it to be a valid relation. These conditions are called Relational Integrity Constraints. There are three main integrity constraints −

Key Constraints

There must be at least one minimal subset of attributes in the relation, which can identify a tuple uniquely. This minimal subset of attributes is called key for that relation. If there are more than one such minimal subsets, these are called candidate keys.

Key constraints force that −

Key constraints are also referred to as Entity Constraints.

Domain Constraints

Attributes have specific values in real-world scenario. For example, age can only be a positive integer. The same constraints have been tried to employ on the attributes of a relation. Every attribute is bound to have a specific range of values. For example, age cannot be less than zero and telephone numbers cannot contain a digit outside 0-9.

Referential integrity Constraints

Referential integrity constraints work on the concept of Foreign Keys. A foreign key is a key attribute of a relation that can be referred in other relation.

Referential integrity constraint states that if a relation refers to a key attribute of a different or same relation, then that key element must exist.

Q2) Write short notes on database schema and database instance

A2)

Database Schema

A database schema is the skeleton structure that represents the logical view of the entire database. It defines how the data is organized and how the relations among them are associated. It formulates all the constraints that are to be applied on the data.

A database schema defines its entities and the relationship among them. It contains a descriptive detail of the database, which can be depicted by means of schema diagrams. It’s the database designers who design the schema to help programmers understand the database and make it useful.

Fig 1 - Database schema

A database schema can be divided broadly into two categories −

Database Instance

It is important that we distinguish these two terms individually. Database schema is the skeleton of database. It is designed when the database doesn't exist at all. Once the database is operational, it is very difficult to make any changes to it. A database schema does not contain any data or information.

A database instance is a state of operational database with data at any given time. It contains a snapshot of the database. Database instances tend to change with time. A DBMS ensures that its every instance (state) is in a valid state, by diligently following all the validations, constraints, and conditions that the database designers have imposed.

Q3) Explain in brief  integrity constraints

A3)

Integrity Constraints

Types of Integrity Constraint

Fig 2 - Integrity Constraint

1. Domain constraints

Example:

2. Entity integrity constraints

Example:

3. Referential Integrity Constraints

Example:

4. Key constraints

Example:

Q4) What is Foreign Key in DBMS? Explain

A4)

A foreign key is different from a super key, candidate key or primary key because a foreign key is the one that is used to link two tables together or create connectivity between the two.

Here, in this section, we will discuss foreign key, its use and look at some examples that will help us to understand the working and use of the foreign key. We will also see its practical implementation on a database, i.e., creating and deleting a foreign key on a table.

What is a Foreign Key

A foreign key is the one that is used to link two tables together via the primary key. It means the columns of one table points to the primary key attribute of the other table. It further means that if any attribute is set as a primary key attribute will work in another table as a foreign key attribute. But one should know that a foreign key has nothing to do with the primary key.

Use of Foreign Key

The use of a foreign key is simply to link the attributes of two tables together with the help of a primary key attribute. Thus, it is used for creating and maintaining the relationship between the two relations.

Example of Foreign Key

Let's discuss an example to understand the working of a foreign key.

Consider two tables Student and Department having their respective attributes as shown in the below table structure:

In the tables, one attribute, you can see, is common, that is Stud_Id, but it has different key constraints for both tables. In the Student table, the field Stud_Id is a primary key because it is uniquely identifying all other fields of the Student table. On the other hand, Stud_Id is a foreign key attribute for the Department table because it is acting as a primary key attribute for the Student table. It means that both the Student and Department table are linked with one another because of the Stud_Id attribute.

In the below-shown figure, you can view the following structure of the relationship between the two tables.

Q5) Create Foreign Key constraint with example

A5)

On CREATE TABLE

Below is the syntax that will make us learn the creation of a foreign key in a table:

So, in this way, we can set a foreign key for a table in the MYSQL database.

In case of creating a foreign key for a table in SQL or Oracle server, the following syntax will work:

On ALTER TABLE

Following is the syntax for creating a foreign key constraint on ALTER TABLE:

Q6) What are some actions that are linked with the actions taken by the foreign key table holder?

A6)

Referential Actions

There are some actions that are linked with the actions taken by the foreign key table holder:

1) Cascade

When we delete rows in the parent table (i.e., the one holding the primary key), the same columns in the other table (i.e., the one holding a foreign key) also gets deleted. Thus, the action is known as Cascade.

2) Set NULL

Such referential action maintains the referential integrity of both tables. When we manipulate/delete a referenced row in the parent/referenced table, in the child table (table having foreign key), the value of such referencing row is set as NULL. Such a referential action performed is known as Set NULL.

3) Set DEFAULT

Such an action takes place when the values in the referenced row of the parent table are updated, or the row is deleted, the values in the child table are set to default values of the column.

4) Restrict

It is the restriction constraint where the value of the referenced row in the parent table cannot be modified or deleted unless it is not referred by the foreign key in the child table. Thus, it is a normal referential action of a foreign key.

5) No Action

It is also a restriction constraint of the foreign key but is implemented only after trying to modify or delete the referenced row of the parent table.

6) Triggers

All these and other referential actions are basically implemented as triggers where the actions of a foreign key are much similar or almost similar to user-defined triggers. However, in some cases, the ordered referential actions get replaced by their equivalent user-defined triggers for ensuring proper trigger execution.

Q7) What is relational algebra and what are its types give examples also?

A7)

Relational algebra is a procedural query language. It gives a step by step process to obtain the result of the query. It uses operators to perform queries.

Types of Relational operation

Fig 3 - Relational operation

1. Select Operation:

Where:

σ is used for selection prediction
r is used for relation
p is used as a propositional logic formula which may use connectors like: AND OR and NOT. These relational can use as relational operators like =, ≠, ≥, <, >, ≤.

For example: LOAN Relation

Input:

Output:

2. Project Operation:

Where

A1, A2, A3 is used as an attribute name of relation r.

Example: CUSTOMER RELATION

Input:

Output:

3. Union Operation:

A union operation must hold the following condition:

Example:

DEPOSITOR RELATION

BORROW RELATION

Input:

Output:

4. Set Intersection:

Example: Using the above DEPOSITOR table and BORROW table

Input:

Output:

5. Set Difference:

Example: Using the above DEPOSITOR table and BORROW table

Input:

Output:

6. Cartesian product

Example:

EMPLOYEE

DEPARTMENT

Input:

Output:

7. Rename Operation:

The rename operation is used to rename the output relation. It is denoted by rho (ρ).

Example: We can use the rename operator to rename STUDENT relation to STUDENT1.

 Q8) Explain join operation and its different types with examples

A8)

A Join operation combines related tuples from different relations, if and only if a given join condition is satisfied. It is denoted by ⋈.

Example:

EMPLOYEE

SALARY

Result:

Types of Join operations:

Fig 4 – Join Operation

1. Natural Join:

Example: Let's use the above EMPLOYEE table and SALARY table:

Input:

Output:

2. Outer Join:

The outer join operation is an extension of the join operation. It is used to deal with missing information.

Example:

EMPLOYEE

FACT_WORKERS

Input:

Output:

An outer join is basically of three types:

a. Left outer join:

Example: Using the above EMPLOYEE table and FACT_WORKERS table

Input:

b. Right outer join:

Example: Using the above EMPLOYEE table and FACT_WORKERS Relation

Input:

Output:

c. Full outer join:

Example: Using the above EMPLOYEE table and FACT_WORKERS table

Input:

Output:

3. Equijoin:

It is also known as an inner join. It is the most common join. It is based on matched data as per the equality condition. The equi join uses the comparison operator(=).

Example:

CUSTOMER RELATION

PRODUCT

Input:

Output:

Q9) What is relational calculus and what are its types explain with examples?

A9)

Relational Calculus

Types of Relational calculus:

Fig 5 - Relational calculus

1. Tuple Relational Calculus (TRC)

Notation:

Where

T is the resulting tuples

P(T) is the condition used to fetch T.

For example:

OUTPUT: This query selects the tuples from the AUTHOR relation. It returns a tuple with 'name' from Author who has written an article on 'database'.

TRC (tuple relation calculus) can be quantified. In TRC, we can use Existential (∃) and Universal Quantifiers (∀).

For example:

Output: This query will yield the same result as the previous one.

2. Domain Relational Calculus (DRC)

Notation:

Where

a1, a2 are attributes
P stands for formula built by inner attributes

For example:

Output: This query will yield the article, page, and subject from the relational javatpoint, where the subject is a database.

Q10) Explain SQL commands and what are its types give examples also?

A10)

Types of SQL Commands

There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.

Fig 5 – SQL Commands

1. Data Definition Language (DDL)

Here are some commands that come under DDL:

a. CREATE It is used to create a new table in the database.

Syntax:

Example:

b. DROP: It is used to delete both the structure and record stored in the table.

Syntax

Example

c. ALTER: It is used to alter the structure of the database. This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute.

Syntax:

To add a new column in the table

To modify existing column in the table:

EXAMPLE

d. TRUNCATE: It is used to delete all the rows from the table and free the space containing the table.

Syntax:

Example:

2. Data Manipulation Language

Here are some commands that come under DML:

a. INSERT: The INSERT statement is a SQL query. It is used to insert data into the row of a table.

Syntax:

Or

For example:

b. UPDATE: This command is used to update or modify the value of a column in the table.

Syntax:

For example:

c. DELETE: It is used to remove one or more row from a table.

Syntax:

For example:

3. Data Control Language

DCL commands are used to grant and take back authority from any database user.

Here are some commands that come under DCL:

a. Grant: It is used to give user access privileges to a database.

Example

b. Revoke: It is used to take back permissions from the user.

Example

4. Transaction Control Language

TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only.

These operations are automatically committed in the database that's why they cannot be used while creating tables or dropping them.

Here are some commands that come under TCL:

a. Commit: Commit command is used to save all the transactions to the database.

Syntax:

Example:

b. Rollback: Rollback command is used to undo transactions that have not already been saved to the database.

Syntax:

Example:

c. SAVEPOINT: It is used to roll the transaction back to a certain point without rolling back the entire transaction.

Syntax:

5. Data Query Language

DQL is used to fetch the data from the database.

It uses only one command:

a. SELECT: This is the same as the projection operation of relational algebra. It is used to select the attribute based on the condition described by WHERE clause.

Syntax:

For example: