Notes

TO CONSTRUCT THE ER-DIAGRAM FOR THE LIBRARY



Objective: To construct the ER-Diagram for the library.


Theory: An ER diagram is a graphical representation of entities and their relationships. It is used to model the data in a database. Entities are objects that have a unique identity, such as books, students and librarians and Relationships are the connections between the entities. For example, a book can be checked out by a student.



Procedure: The procedure for creating an ER diagram of a library with entities including students, books, and staff:

Identify the entities: The first step in creating an ER diagram is to identify the entities that will be included in the diagram. For a library system, the main entities include students, books, and staff.

Define the attributes: Once the entities have been identified, we need to define the attributes for each entity.
Student (student_id, name, address, contact, gender)
Staff (staff_id, name, contact, designation)
Books (bid, title, publication, author)

Determine the relationships: We need to determine the relationships between the entities. For example, a student can borrow multiple books, and a book can be borrowed by multiple students. This is a many-to-many relationship that can be represented using a junction table or entity. Similarly, staff members can manage multiple books, and a book can be managed by multiple staff members.


Create an ER diagram:






















For our library system, we have identified three main entities: student, staff, and book. The student entity includes attributes such as student_id, name, address, contact, gender. The staff entity includes attributes such as staff_id, name, contact, designation. The book entity includes attributes such as bid, title, publication, author.

To represent the relationships between these entities, we have created a relationship called "issue" using a diamond symbol. This relationship links the student, staff, and book entities and includes attributes such as "given_by", "taken_by", and "book". The "given_by" attribute represents the staff member who issued the book to the student, the "taken_by" attribute represents the student who borrowed the book, and the "book" attribute represents the book that was borrowed. The relationship also includes attributes date and issue_type.


Conclusion: In this lab work, we learned how to create an ER diagram for a library management system. We identified the entities and relationships in the system, and we created a graphical diagram to represent them. This process helped us to understand the data in the system and to design a database that can store and manage it effectively.




2. TO IMPLEMENT GIVEN CONCEPTUAL MODEL (ER-DIAGRAM) USING RELATIONAL MODEL


Objective: To implement given conceptual model (ER- diagram ) using relational model


Theory:
Conceptual Model
A conceptual model in DBMS (Database Management System) is a representation of the overall structure and contents of a database. It is an abstract, high-level description of the data that is stored in the database and the relationships between the data. The main purpose of a conceptual model is to provide a clear and concise understanding of the data requirements of an organisation, and to serve as a basis for designing the physical database. It is created in the early stages of the database design process and is typically developed using a data modelling language, such as ER (Entity-Relationship) diagram of UML (Unified Modeling Language).
Relational Model
In a relational model, data is organised in two-dimensional tables and the relationship is maintained by storing a common field by using primary key and foreign key. This model was introduced by E.F Codd in 1970, and since then it has been the most widely used database model, in fact, we can say the only database model used around the world. The basic structure of data in the relational model is tables. All the information related to a particular type is stored in rows of that table. Hence, tables are also known as relations in relational models. In the coming tutorials we will learn how to design tables, normalise them to reduce data redundancy and how to use Structured Query languages to access data from tables.
Here are four types of database languages and their uses:
Data definition language (DDL)
Data definition language (DDL) creates the framework of the database by specifying the database schema, which is the structure that represents the organisation of data. Its common uses include the creation and alteration of tables, files, indexes and columns within the database. This language also allows users to rename or drop the existing database or its components. Here's a list of DDL statements:
Create: Create a new database such as a table, index or column
Alter: Changes the structure of the database or object
Drop: Deletes the database or existing objects
Rename: Renames the database or existing objects.
Truncate: It is used to delete tables in database instances.
Comment: It is used to comment on the data directory.
Data Manipulation language (DML)
Data manipulation language (DML) provides operations that handle user requests, offering a way to access and manipulate the data that users store within a database. Its common functions include inserting, updating and retrieving data from the database. Here's a list of DML statements:
Insert: Adds new data to the existing database table
Update: Changes or updates values in the table
Delete: Removes records or rows from the table
Select: Retrieves data from the table or multiple tables

Data control language (DCL)
Data control language (DCL) controls access to the data that users store within a database. Essentially, this language controls the rights and permissions of the database system. It allows users to grant or revoke privileges to the database. Here's a list of DCL statements:
Grant: Give a user access to the database
Revoke: Removes a user’s access to the database

Transaction control languages (TCL)
Transaction control language (TCL) manages the transactions within a database. Transactions group a set of related tasks into a single, executable task. All the tasks must succeed in order for the transaction to work. Here's a list of TCL statements:
Commit: Carries out a transaction
Rollback: Restores a transaction if any tasks fail to execute

Lab work
















Conclusion:

In this lab work, we learned how to implement the given conceptual model or known as the er diagram using relational models. We learned about the conceptual and relational implementation on the given database. This process helped us to understand the data in the system and to design a database that can store and manage it effectively.


3. TO PERFORM DML QUERIES ON THE GIVEN DATABASE


Objective: To perform DML queries on the given database

Theory:
DDL stands for Data Definition Language, and it is used to create, modify, and delete database objects such as tables, indexes, and views. DDL statements are used to define the structure of the database, including the schema and the constraints on the data.

Some common DDL queries include:

CREATE TABLE: This statement is used to create a new table in the database. It specifies the name of the table, the columns and their data types, and any constraints or indexes on the data.

ALTER TABLE: This statement is used to modify an existing table in the database. It can be used to add or remove columns, change data types, and modify constraints or indexes.
DROP TABLE: This statement is used to delete a table from the database.

By performing DDL queries, we can modify the structure of the database to better suit our needs. This allows us to ensure that the database is properly organised, efficient, and easy to use.



Procedure: We perform the following DDL queries based on the given database.

Drop any existing "library" database:

Command: DROP DATABASE IF EXISTS library;

Create a new "library" database:

Command: CREATE DATABASE library;

Select the "library" database:

Command: USE library;

Create the "student" table:

Command: CREATE TABLE student (

student_id INT NOT NULL PRIMARY KEY, name VARCHAR(30) NOT NULL, address varchar(50) NOT NULL, gender

contact VARCHAR(50) NOT NULL);

5. Create the "staff" table:

Lab work: In the given “library” database, perform the following operations.
Insert at least 5 records in each table.
Delete any two records with same conditions
Update/changes the value of any two attributes based on some condition.
















Conclusion:
In this lab work, we learned how to perform DMl queries on the given database. models. This process helped us to understand the data in the system and to design a database that can store and manage it effectively.
4. TO EXECUTE SIMPLE RETRIEVAL QUERIES IN MYSQL


Objective: To execute simple retrieval queries in mysql

Theory:
Simple retrieval refers to the process of retrieving data from a database using basic SQL queries. Simple retrieval queries are typically used to extract specific information from a database without modifying the data itself.
Select *
Select statements query the database to find information in one or more tables, and return the query as a result set. It is used to fetch the information from the database which is already stored there. The SELECT statement is used to retrieve records from one or more tables.
Syntax of Select * is
SELECT * FROM table_name;

Select specific columns
It can be done one of the following ways:
Query data from a single column
Here , SELECT statement is used to retrieve a single column from given table
The basic syntax is
SELECT column_name FROM table_name [WHERE Conditions];

Querying data from multiple columns
To query data from multiple columns, we specify a list of commas-separated columns names. The basic syntax is
SELECT column1, column2, ... FROM table_name [WHERE Conditions];

Querying data from all columns of a table
Here we can use the shorthand asterisk (*) to instruct Oracle to return data from all columns of a table. The basic syntax is
SELECT * FROM employees [WHERE Conditions] ;

Select limited number of rows and columns
To select a limited number of rows and columns in a table using a simple retrieval query in DBMS, we can use the LIMIT and SELECT clauses. The basic syntax is
SELECT column1, column2, ... FROM table_name LIMIT num_rows;

Select using Condition
To select data using a condition in a simple retrieval query in DBMS, you can use the WHERE clause. The basic syntax is
SELECT column1, column2, … FROM table_name WHERE condition;

Select using multiple conditions
To select data using multiple conditions in a simple retrieval query in DBMS, you can combine conditions using logical operators such as AND, OR, and NOT. The basic syntax is
SELECT column1, column2, ... FROM table_name WHERE condition1 AND/OR/NOT condition2 AND/OR/NOT condition3 ...;

Select using simple MYSQL functions
In MySQL, you can use various built-in functions to perform simple operations on data while selecting it from a table. Here are some examples of using simple MySQL functions in a SELECT statement:
Concat:
This function can be used to concatenate two or more strings into a single string. For example:
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

Upper/Lower
These functions can be used to convert a string to all uppercase or all lowercase letters, respectively. For example:
SELECT UPPER(first_name) AS upper_first_name, LOWER(last_name) AS lower_last_name FROM employees;

Sum / Avg / Min / Max
These functions can be used to perform basic calculations on numeric values in a column. For example:
SELECT SUM(salary) AS total_salary, AVG(salary) AS average_salary, MIN(salary) AS min_salary, MAX(salary) AS max_salary FROM employees;

Lab work:






Conclusion:

In this lab work, we learned how to execute simple retrieval queries in mysql. This process helped us to understand the data in the system and to design a database that can store and manage it effectively.










5. TO STUDY THE DIFFERENT TYPES OF JOIN IN MYSQL


Objective: To study different types of join in MYSQl

Theory:
Joins can be simply defined as the combining or merging the related tuples from the two different relations into a single type. It can be said that it is similar to Cartesian products except the fact that in Cartesian products, we get all the possible combinations of relations while in join only those combinations can be formed that satisfies some matching conditions. A cartesian product is followed by a selection process that results in joins.

MySQL supports the following JOIN types: INNER JOIN, OUTER JOIN (further divided into LEFT JOIN and RIGHT JOIN), SELF JOIN, and CROSS JOIN. The type of the JOIN defines how tables are related in a query. You can join more than two tables.

INNER JOIN
Compares all rows from all tables specified in a query with each other and returns records with matching values as a new result table.

LEFT OUTER JOIN
Returns the result table with all rows from the left table and only those rows from the right table that match the JOIN condition (non-matching rows are returned with NULLs).

RIGHT OUTER JOIN
Returns the result table with all rows from the right table and only those rows from the left table where the JOIN condition is fulfilled (non-matching rows are returned
with NULLs).

CROSS JOIN
Combines each row from one table with each row from another table and thus returns a new result table with all possible row combinations from each table.

FULL OUTER JOIN
MySQL does not directly support FULL OUTER JOIN that returns both matching and non-matching rows from the joined tables, but you can combine LEFT and RIGHT OUTER JOINs to achieve the same result.

SELF JOIN
Compares a row with other rows within the same table or extracts hierarchical data - table aliases should be used instead of repeating the same table name in a query.


Lab work:
Create a database schema as shown below and execute the given queries.

CUSTOMER (cid, cname, gender, country )
PRODUCT ( pid , pname, price)
ORDER ( cid, pid, quantity, total_amount )

And we execute the following queries as listed below:
Obtain the result of customer × ORDER
Obtain the result of customer ⋈ ORDER
Obtain the result of customer ⟕ ORDER
Obtain the result of customer ⟖ ORDER
Obtain the result of customer ⟗ ORDER
Find the name of customer having total amount greater than 5050
Find the name of the product having maximum order.




















Conclusion:

In this lab work, we learned about different types of joins in mysql and how to execute them respectively in the database's data. This process helped us to understand the data in the system and to design a database that can store and manage it effectively.