Database Management System

Relationships in Database Relationship Relationship is nothing but an association among two or more entities. Entities take part in relationships. We can often identify relationships with verbs or verb phrases. As shown in Fig. 12.1 One-to-One Relationships One-to-Many Relationships Many-to-One Relationships Many-to-Many Relationships Fig. 12.1 Home Previous

Attributes and Relational Data Models in ERP Systems ERP Management System - Attributes and Relational Data Model Attributes Attributes are single-valued properties of either an entity-type or a relationship-type, such as last name, first name, MIS, address, and birth date. Fig. 11.1 Relational Data Model The relational data model provides conceptual tools to design the database schema of a relational database. It describes the data, relationships between that data, data semantics, and constraints on the data in the relational database. Fig. 11.2 Previous Next

ERP Management System Design Design and Develop Conceptual Data Model for an ERP Management System ER Diagram An Entity Relationship Diagram (ERD) displays the relationships of entity sets stored in a database. ER diagrams help explain the logical structure of databases, based on three basic concepts: Entities: Recognizable real-world things, either living or non-living, that are to be represented in the database. Attributes: Properties or details of an entity. Relationships: How entities interact with each other within the system. Enterprise Resource Planning (ERP) ERP is the integrated management of main business processes, often in real time, mediated by software. It is typically a suite of integrated applications that an organization uses to collect, store, manage, and interpret data from many business activities, and can be either local or cloud

SQL Joins and Nested Queries SQL Joins and Nested Queries A join clause is used to combine rows from two or more tables, based on a related column between them. Here are the different types of the JOINs in SQL: (INNER) JOIN: Returns records that have matching values in both tables. LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table. RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table. FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table. CARTESIAN JOIN or CROSS JOIN: There is a join for each row of one table to every row of another table. This usually happens when the matching column or WHERE condition is not specified. SELF JOIN: A table is joined to itself, meaning each row of the table is joined with itself and all other rows depending on ce

Use of SUBSTRING Function Use of Substring Functions The SUBSTRING function in SQL allows extraction of a specified portion of a string based on the user's requirements. This function is commonly used to manipulate and retrieve parts of string data from database fields. Syntax for SUBSTRING() SUBSTRING(Expression, Starting Position, Length) The parameters are as follows: Expression: The string expression from which to extract the substring. Starting Position: The starting index from which to begin extraction, where the first character in the string is 1. Length: The number of characters to extract from the string starting from the Starting Position. QUERY to Create Table CREATE TABLE team(name VARCHAR(90), address VARCHAR(50), phone_no INT, age INT); As shown in Fig. 8.1: Fig. 8.1 QUERY to Extract Substring SELECT SUBSTRING(name, 1, 3) AS ExtractedString FROM team; Previous Next

Use of ORDER BY Statement Use of order by statement The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword. Syntax* SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC; QUERY* CREATE TABLE stud_113 (Rollno NUMBER(5), name VARCHAR(10), address VARCHAR(15), phoneno NUMBER(10), age NUMBER(5)); As Shown in Fig...7.1 Output Fig... 7.1 QUERY* INSERT INTO stud_113 VALUES(3); INSERT INTO stud_113 VALUES(2); INSERT INTO stud_113 VALUES(5); INSERT INTO stud_113 VALUES(4); INSERT INTO stud_113 VALUES(1); INSERT INTO stud_113 VALUES(6); As Shown in Fig...7.2 Output Fig... 7.2 QUERY* SELECT * FROM stud_113 ORDER BY rollno DESC; As Shown in Fig...7.3 Output Fig... 7.3 Previous Next

Use of Aggregate Functions (Cont...) Use of Aggregate Functions (Cont...) QUERY* select count(distinct Company_name) from Product; As Show Fig...6.1 Output Fig... 6.1 QUERY* select company_name, count(*) from product group by company_name; As Show Fig...6.2 Output Fig... 6.2 QUERY* select MAX(quantity) from Product; As Show Fig...6.3 Output Fig... 6.3 QUERY* select MIN(quantity) from Product; As Show Fig...6.4 Output Fig... 6.4 Previous Next

Use of Aggregate Functions Use of Aggregate Functions SQL Aggregate Function - SQL aggregation function is used to perform calculations on multiple rows of a single column of a table. It returns a single value and is also used to summarize the data. Types of Aggregate Function: Count Function - COUNT function is used to count the number of rows in a database table. It can work on both numeric and non-numeric data types. COUNT function uses COUNT(*) that returns the count of all the rows in a specified table. COUNT(*) considers duplicate and Null. Sum Function - Sum function is used to calculate the sum of all selected columns. It works on numeric fields only. AVG Function - The AVG function is used to calculate the average value of the numeric type. AVG function returns the average of all non-Null values. MAX Function - MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected

To write a query with DML commands. To write a query with DML commands: DML (Data Manipulation Language): The SQL commands that deal with the manipulation of data present in the database belong to DML or Data Manipulation Language, and this includes most of the SQL statements. It is the component of the SQL statement that controls access to data and to the database. Basically, DCL statements are grouped with DML statements. List of DML commands: INSERT: It is used to insert data into a table. QUERY* INSERT INTO BOOKS VALUES (1012, 'C+', 'ABC', 'Xyz', 800, 3, 2); UPDATE: It is used to update existing data within a table. QUERY* UPDATE Customers SET ContactName = 'Alfred Schmidt', City = 'Frankfurt' WHERE CustomerID = 1; DELETE: It is used to delete records from a database table.

Create a student table and describe the schema of the student table Create a student table and describe the schema of the student table: Rollno number(6), Name varchar(15), Dept varchar(10), City varchar(15), DOB date, Gender char(1) QUERY* create table student (Rollno number(6), Name varchar(15), Dept varchar(10), City varchar(15), DOB date, Gender char(1)); Fig. 3.1 Add foreign key constraint for the column rollno from studmarks that refers rollno from student table. QUERY* alter table student add foreign key (Rollno) references stu_marks(Rollno); desc student; Fig. 3.2 Remove the column city from the student table. QUERY* alter table student drop column City; Fig. 3.3 Previous Next

Create a table studmarks with following attributes name and data type Create a table studmarks with following attributes name and data type: Rollno number (6), Regnumb number(14), semester number(1), cgpa number (5) and perform some other operations. QUERY* Create table studmarks (rollno number(6), regnumber number(14), semester number(1), cgpa number(5)); Desc studmarks; As Shown Fig...2.1 Fig... 2.1 Alter the table: Add the constraints UNIQUE for regnumber attribute from table. QUERY* Alter table studmarks modify (regnumber number(14) UNIQUE); As Shown Fig...2.2 Fig... 2.2 Remove the constraints for the regnumber attribute. Modify the data type of regnumber to varchar(16). Previous Next

Database Management System Write a query with a function of DDL commands such as create, alter and drop. DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database. DDL is a set of SQL commands used to create, modify, and delete database structures but not data. List of DDL commands: CREATE: This command is used to create the database or its objects (like table, index, function, views, store procedure, and triggers). QUERY: create table bca2 (stu_name varchar(6), stu_roll int, stu_marks number(7)); OUTPUT: DROP: This command is used to delete objects from the database. QUERY: drop table bca2; OUTPUT: