INDEX

 Introduction
 Explain the following terms: (1) Entity and entity set. (2) Attribute and
attribute set. (3) Relationship and relationship sets.
 Explain the domain constrains and key constrains.
 Explain in detail about various key constraints used in the database system.
 What is lossless join property? What is lossy decomposition?
 Consider the following tables: Employee (Emp_no, name , Emp_city)
Company (Emp_no, Company_name,salary)
 Write a SQL query to display Employee name and company name.
 Write a SQL query to display employee name, employee city, company
name and salary of all the employees whose salary >10000.
 Write a query to display all the employees working in ‘XYZ’ company.
 Acknowledgement
 Reference
 INTRODUCTION

Database is a collection of interrelated data which helps in the efficient retrieval,

insertion, and deletion of data from the database and organizes the data in the form
of tables, views, schemas, reports, etc. For Example, a university database
organizes the data about students, faculty, admin staff, etc. which helps in the
efficient retrieval, insertion, and deletion of data from it.
1. Explain the following terms: (1) Entity and entity set. (2) Attribute and attribute
set. (3) Relationship and relationship sets.
Ans:

Entity and Entity set:

An entity in DBMS is a real-world object that has certain properties called attributes that define
the nature of the entity. Entities are distinguishable, each entity in a pair of entities has a property
that makes one entity different from the other entity.

For example: If we consider a car entity, it can have its attributes as a car's registration number,
car's model, car's name, car's color, number of seats that are there inside the car, etc.

In the car entity, the attribute registration number can be taken as the primary key since the
registration number can never be the same for two different cars.

Entities are divided into two categories:

Tangible entities: tangible entities are the entities that physically exist in the real world. For
example entity of book etc.

Nontangible entities: Nontangible entities are entities that do not physically exist in the real
world. For example entity of email id.

Entity set:

An entity set in DBMS is a set that collectively represents a group of entities of a similar type.

For example: An entity set in DBMS is a set that collectively represents a group of entities of a
similar type.

Attribute and attribute set:

Attribute:

In the relational database, attributes are the characteristic properties that define all the items
belonging to a specific category applied to every column cell.

Types of attribute:

 Simple attribute: Simple attributes can’t be divided any further. For example, a student’s
contact number. It is also called an atomic value.
 Composite attribute: it is possible to break down composite attribute. For example, a
student’s full name may be further divided into first name, second name and last name.
  Derived attribute: This type of attribute does not include in the physical database.
However, their values are derived from other attributes present in the database. For
example, age should not be stored directly. Instead, it should be derived from the DOB of
that employee.
 Multivalued attribute: Multivalued attributes can have more than one values. For
example, a student can have more than one mobile number, email address etc.

Relationship and relationship set:

Any association between two entities is known as a relationship between those two entities.
This relationship in DBMS is represented using a diamond shape in the entity-relationship
diagram. An entity-relationship diagram is a graphical representation of entities and the
relationships that exist between them.

A set of relationships of the same type is known as relationship set.

Four types of relationships can exist between two entities of tables

 One-to-One Relationship: When a single element of an entity is associated with a single

element of another entity, it is called a one-to-one relationship.
 One-to-Many Relationship: When a single element of an entity is associated with more
than one element of another entity, it is called a one-to-many relationship.
 Many-to-One Relationship: When more than one element of an entity is related to a
single element of another entity, then it is called a many-to-one relationship.
 Many-to-Many Relationship: When more than one element of an entity is associated with
more than one element of another entity, this is called a many-to-many relationship.

2. Explain the domain constrains and key constrains.

Ans:

Relational models make use of some rules to ensure the accuracy and accessibility of the data,
these rules or constrains are known as relational Integrity Constrains. These constrains are
checked before performing any operation like insertion, deletion or updation on the data present
in a relational database.

Domain Constraint: It specifies that every attribute is bound to have a value that lies inside a
specific range of values. It is implemented with the help of the attribute Domain concept.

Key Constraint: It states that every relation must contain an attribute or a set of attribute
(primary key) that can uniquely identify a tuple in that relation. This key can never be NULL or
contain the same value for two different tuples.

3. Explain in detail about various key constraints used in the database system.
Ans:

There are many types of key constraints in DBMS:

Not Null:

 Null represents a record where data may be missing data or data for that record may be
optional
 Once not null is applied to a particular column, you cannot enter null values to that
column and restricted to maintain only some proper value other than null
 A not-null constraint cannot be applied at table level

Unique:

 Sometimes we need to maintain only unique data in the column of a database table, this
is possible by using a unique constraint
 Unique constraint ensures that all values in a column are unique

Default:

 Default clause in SQL is used to add default data to the columns

 When a column is specified as default with some value then all the rows will use
the same value, each and every time while entering the data we need not enter that value
 But default column value can be customized, it can be overridden when inserting a data
for that row based on the requirement.

Check:

 Suppose in real-time if you want to give access to an application only if the age entered
by the user is greater than 18 this is done at the back-end by using a check constraint
 Check constraint ensures that the data entered by the user for that column is within the
range of values or possible values specified.
 Primary Key:

A primary key is a constraint in a table that uniquely identifies each row record in a
database table by enabling one or more the columns in the table as the primary key.

 If you try to enter duplicate value while inserting in the row you are displayed with an
error
 Hence primary key will restrict you to maintain unique values and not null values in that
particular column

Foreign Key:

 The foreign key a constraint is a column or list of columns that points to the primary key
column of another table
 The main purpose of the foreign key is only those values are allowed in the present table
that will match the primary key column of another table.

4. What is lossless join property? What is lossy decomposition?

Ans:

Lossless Join Decomposition-

 Consider there is a relation R which is decomposed into sub relations R1,R2,

…..,Rn
 This decomposition is called lossless join decomposition when the join of the sub
relations results in the same relation R that was decomposed.
 For lossless join decomposition, we always have-
R1 ⋈ R2 ⋈ R3 ……..⋈Rn = R
Where ⋈ is a natural join operator

Lossy join Decomposition-

  Consider there is a relation R which is decomposed into sub relations R1, R2, ….,
Rn
 This decomposition is called lossy join decomposition when the join of the sub
relations does not result in the same relation R that was decomposed.
 The natural join of the sub relations is always found to have some extraneous
tuples.

R1 ⋈ R2 ⋈ R3 ……..⋈Rn ⊃ R

Where ⋈ is a natural join operator

5. Consider the following tables: Employee (Emp_no, name , Emp_city) Company

(Emp_no, Company_name,salary)

I. Write a SQL query to display Employee name and company name.

II. Write a SQL query to display employee name, employee city, company
name and salary of all the employees whose salary >10000.
III. Write a query to display all the employees working in ‘XYZ’ company.

Ans:

1. select name,company_name
from employee,company
where employee.emp_no=company.emp_no;
2. select employee.name,employee.emp_city,company.company_name,company.salary
from employee,company
where company.salary>10000 AND employee.emp_no=company.emp_no;
3. select employee.name
from employee,company
where company.company_name='IBM' AND employee.emp_no=company.emp_no;
 ACKNOWLEDGEMENT

First of all I would like to thank Mr. Samit Mondal of our college for encouraging me to write
this report. I am glad to admit that this report has intensely helped me in learning, creating and
writing. Our knowledge, creativity and self-development have got a new aspect to expose and
explore themselves. I acknowledge our sincere gratitude to our teacher. I am fortunate to have
worked under him.

I would also like to thank Mr. Manas Roy who has given valuable suggestion in making this
report. Last of all, I would like to thank all my family members, friends and many of my friends
who have helped me completing this project.

Thank you all.

 REFERENCE

First of all thanks to my teacher and all my friends for helping me to do this assignment, I made
this assignment by collecting from books, websites, youtube from different places, by reading
myself I made this assignment.