Download DATA BASE MANAGEMENT SYSTEMS for mba elective subject of sv university and more Schemes and Mind Maps Database Management Systems (DBMS) in PDF only on Docsity!
UNIT – I
Q 1. What is Data Data is a collection of a distinct small unit of information. It can be used in a variety of forms like text, numbers, media, bytes, etc. it can be stored in pieces of paper or electronic memory, etc. Word 'Data' is originated from the word 'datum' that means 'single piece of information.' It is plural of the word datum. In computing, Data is information that can be translated into a form for efficient movement and processing. Data is interchangeable. What is Database A database is an organized collection of data, so that it can be easily accessed and managed. You can organize data into tables, rows, columns, and index it to make it easier to find relevant information. Database handlers create a database in such a way that only one set of software program provides access of data to all the users. The main purpose of the database is to operate a large amount of information by storing, retrieving, and managing data. There are many databases available like MySQL, Sybase, Oracle, MongoDB, Informix, PostgreSQL, SQL Server, etc. Database System Application Databases touch all aspects of our lives. Some of the major areas of application are as follows: Banking, Airlines, Universities, Manufacturing and selling, Human resources, Enterprise Information Sales: For customer, product, and purchase information. Accounting: For payments, receipts, account balances, assets and other accounting information. Human resources: For information about employees, salaries, payroll taxes, and benefits, and for generation of pay checks. Manufacturing: For management of the supply chain and for tracking production of items in factories, inventories of items in warehouses and stores, and orders for items. Online retailers: For sales data noted above plus online order tracking, generation of recommendation lists, and maintenance of online product evaluations. Banking and Finance: Banking: For customer information, accounts, loans, and banking transactions. Credit card transactions: For purchases on credit cards and generation of monthly statements. Finance: For storing information about holdings, sales, and purchases of financial instruments such as stocks and bonds; also for storing real-time market data to enable online trading by customers and automated trading by the firm. Universities: For student information, course registrations, and grades (in addition to standard enterprise information such as human resources and accounting). Airlines: For reservations and schedule information. Airlines were among the first to use databases in a geographically distributed manner. Telecommunication: For keeping records of calls made, generating monthly bills, maintaining balances on prepaid calling cards, and storing information about the communication networks. Database Languages A DBMS has appropriate languages and interfaces to express database queries and updates. Database languages can be used to read, store and update the data in the database. Types of Database Language
1. Data Definition Language DDL stands for Data Definition Language. It is used to define database structure or pattern. It is used to create schema, tables, indexes, constraints, etc. in the database.
Using the DDL statements, you can create the skeleton of the database. Data definition language is used to store the information of metadata like the number of tables and schemas, their names, indexes, columns in each table, constraints, etc. Here are some tasks that come under DDL: Create: It is used to create objects in the database. Alter: It is used to alter the structure of the database. Drop: It is used to delete objects from the database. Truncate: It is used to remove all records from a table. Rename: It is used to rename an object. Comment: It is used to comment on the data dictionary. These commands are used to update the database schema that's why they come under Data definition language.
2. Data Manipulation Language DML stands for Data Manipulation Language. It is used for accessing and manipulating data in a database. It handles user requests. Here are some tasks that come under DML: Select: It is used to retrieve data from a database. Insert: It is used to insert data into a table. Update: It is used to update existing data within a table. Delete: It is used to delete all records from a table. Merge: It performs UPSERT operation, i.e., insert or update operations. Call: It is used to call a structured query language or a Java subprogram. Explain Plan: It has the parameter of explaining data. Lock Table: It controls concurrency. 3. Data Control Language DCL stands for Data Control Language. It is used to retrieve the stored or saved data. The DCL execution is transactional. It also has rollback parameters. (But in Oracle database, the execution of data control language does not have the feature of rolling back.) Here are some tasks that come under DCL: Grant: It is used to give user access privileges to a database. Revoke: It is used to take back permissions from the user. There are the following operations which have the authorization of Revoke: CONNECT, INSERT, USAGE, EXECUTE, DELETE, UPDATE and SELECT. 4. Transaction Control Language TCL is used to run the changes made by the DML statement. TCL can be grouped into a logical transaction. Here are some tasks that come under TCL: Commit: It is used to save the transaction on the database. Rollback: It is used to restore the database to original since the last Commit Q 2. ER model(Entity Relationship) ER model stands for an Entity-Relationship model. It is a high-level data model. This model is used to define the data elements and relationship for a specified system. It develops a conceptual design for the database. It also develops a very simple and easy to design view of data. In ER modeling, the database structure is portrayed as a diagram called an entity- relationship diagram.
3. Relationship A relationship is used to describe the relation between entities. Diamond or rhombus is used to represent the relationship. Types of relationship are as follows: a. One-to-One Relationship When only one instance of an entity is associated with the relationship, then it is known as one to one relationship. For example, A female can marry to one male, and a male can marry to one female. b. One-to-many relationship When only one instance of the entity on the left, and more than one instance of an entity on the right associates with the relationship then this is known as a one-to-many relationship. For example, Scientist can invent many inventions, but the invention is done by the only specific scientist. c. Many-to-one relationship When more than one instance of the entity on the left, and only one instance of an entity on the right associates with the relationship then it is known as a many-to-one relationship. For example, Student enrolls for only one course, but a course can have many students. d. Many-to-many relationship When more than one instance of the entity on the left, and more than one instance of an entity on the right associates with the relationship then it is known as a many-to-many relationship. For example, Employee can assign by many projects and project can have many employees.
UNIT-
Q1.Introduction to SQL SQL is a short-form of the structured query language, and it is pronounced as S-Q-L or sometimes as See-Quell. This database language is mainly designed for maintaining the data in relational database management systems. It is a special tool used by data professionals for handling structured data (data which is stored in the form of tables). It is also designed for stream processing in RDSMS. You can easily create and manipulate the database, access and modify the table rows and columns, etc. This query language became the standard of ANSI in the year of 1986 and ISO in the year of 1987. If you want to get a job in the field of data science, then it is the most important query language to learn. Big enterprises like Facebook, Instagram, and LinkedIn, use SQL for storing the data in the back-end. Structure of SQL Queries SQL queries are the questions or requests imposed on the set of data to retrieve the desired information. The language we use to build these queries is structured query language i.e. SQL. In this context, we will be discussing the structure of SQL queries and try to understand them with some examples. What is the Basic Structure of SQL Queries? The fundamental structure of SQL queries includes three clauses that are select, from, and where clause. What we want in the final result relation is specified in the select clause. Which relations we need to access to get the result is specified in from clause. How the relation must be operated to get the result is specified in the where clause. select A1, A2,..
. , An from r1, r2,... , rm where P; In the select clause, you have to specify the
attributes that you want to see in the result relation In the from clause, you have to specify the list of relations that has to be accessed for evaluating the query. In the where clause involves a predicate that includes attributes of the relations that we have listed in the from clause. Though the SQL query has a sequence select, from, and where. To understand how the query will operate? You must consider the query in the order, from, where and then focus on select. So with the help of these three clauses, we can retrieve the information we want out of the huge set of data. SQL Syntax When you want to do some operations on the data in the database, then you must have to write the query in the predefined syntax of SQL. The syntax of the structured query language is a unique set of rules and guidelines, which is not case-sensitive. Its Syntax is defined and maintained by the ISO and ANSI standards. Following are some most important points about the SQL syntax which are to remember: You can write the keywords of SQL in both uppercase and lowercase, but writing the SQL keywords in uppercase improves the readability of the SQL query. Simple Example of SQL statement: SELECT "column name" FROM "table name"; Each SQL statement begins with any of the SQL keywords and ends with the semicolon (;). The semicolon is used in the SQL for separating the multiple Sql statements which are going to execute in the same call. Let's discuss each statement in short one by one with syntax and one example:
1. SELECT Statement This SQL statement reads the data from the SQL database and shows it as the output to the database user. Syntax of SELECT Statement: SELECT column_name1, column_name2, .…, column_nameN [FROM table name] [WHERE condition] [ ORDER BY order_column_name1 [ ASC | DESC ], ]; Example of SELECT Statement: SELECT Emp_ID, First Name, Last Name, Salary, City FROM Employee_details WHERE Salary = 100000 ORDER BY Last_Name This example shows the Emp_ID, First Name, Last_Name, Salary, and City of those employees from the Employee_details table whose Salary is 100000. The output shows all the specified details according to the ascending alphabetical order of Last_Name. UPDATE Statement This SQL statement changes or modifies the stored data in the SQL database. Syntax of UPDATE Statement: UPDATE table_name SET column_name1= new_value_1, column_name2 = new_value_2, , column_nameN= new_value_N [ WHERE CONDITION ]; Example of UPDATE Statement: UPDATE Employee_details SET Salary = 100000 WHERE Emp_ID = 10; This example changes the Salary of those employees of the Employee_details table whose Emp_ID is 10 in the table. DELETE Statement This SQL statement deletes the stored data from the SQL database. Syntax of DELETE Statement: DELETE FROM table_name [ WHERE CONDITION ]; Example of DELETE Statement:DELETE FROM Employee_details WHERE First_Name = 'Sumit'; This example deletes the record of those employees from the Employee_details table whose First_Name is Sumit in the table. CREATE TABLE Statement This SQL statement creates the new table in the SQL database. Example of CREATE TABLE Statement: CREATE TABLE Employee_details ( Emp_Id NUMBER(4) NOT NULL, First_name VARCHAR (30),Last_name VARCHAR(30),Salary Money, City VARCHAR(30) PRIMARY KEY (Emp_Id) );
SELECT DISTINCT column_name1, column_name2, ... FROM table_name; Example of DISTINCT Clause: SELECT DISTINCT City, Salary FROM Employee_details; This example shows the distinct values of the City and Salary column from the Employee_details table. COMMIT Statement This SQL statement saves the changes permanently, which are done in the transaction of the SQL database. Syntax of COMMIT Statement:COMMIT Example of COMMIT Statement: DELETE FROM Employee_details WHERE salary = 30000; COMMIT; This example deletes the records of those employees whose Salary is 30000 and then saves the changes permanently in the database. ROLLBACK Statement This SQL statement undoes the transactions and operations which are not yet saved to the SQL database. Syntax of ROLLBACK Statement: ROLLBACK Example of ROLLBACK Statement: DELETE FROM Employee_details WHERE City = Mumbai; ROLLBACK; This example deletes the records of those employees whose City is Mumbai and then undo the changes in the database. CREATE INDEX Statement This SQL statement creates the new index in the SQL database table. Syntax of CREATE INDEX Statement: CREATE INDEX index name ON table_name ( column_name1, column_name2, …, column_nameN); Example of CREATE INDEX Statement: CREATE INDEX idx_First_Name ON employee details (First Name); This example creates an index idx_First_Name on the First Name column of the Employee_details table. DROP INDEX Statement This SQL statement deletes the existing index of the SQL database table. Syntax of DROP INDEX Statement: DROP INDEX index name; Example of DROP INDEX Statement: DROP INDEX idx_First_Name; This example deletes the index idx_First_Name from the SQL database Q2.SQL Set Operation The SQL Set operation is used to combine the two or more SQL SELECT statements. Types of Set Operation Union UnionAll Intersect Minus
- Union The SQL Union operation is used to combine the result of two or more SQL SELECT queries. In the union operation, all the number of datatype and columns must be same in both the tables on which UNION operation is being applied. The union operation eliminates the duplicate rows from its resultset. Syntax SELECT column_name FROM table1 UNION SELECT column_name FROM table2; Example: The First tableOPs Concepts in Java ID NAME 1 Jack 2 Harry 3 Jackson The Second table ID NAME 3 Jackson 4 Stephan 5 David Union SQL query will be: SELECT * FROM First UNION SELECT * FROM Second; The resultset table will look like:
ID NAME 1 Jack 2 Harry 3 Jackson 4 Stephan 5 David
Example Using the above First and Second table. Minus query will be: SELECT * FROM First MINUS SELECT * FROM Second; The resultset table will look like: ID NAME 1 Jack 2 Harry Q3.what is Relational database design (RDD) Relational database design (RDD) models information and data into a set of tables with rows and columns. Each row of a relation/table represents a record, and each column represents an attribute of data. The Structured Query Language (SQL) is used to manipulate relational databases. The design of a relational database is composed of four stages, where the data are modeled into a set of related tables. The stages are: Define relations/attributes Define primary keys Define relationships Normalization Explains Relational Database Design (RDD) Relational databases differ from other databases in their approach to organizing data and performing transactions. In an RDD, the data are organized into tables and all types of data access are carried out via controlled transactions. Relational database design satisfies the ACID (atomicity, consistency, integrity and durability) properties required from a database design. Relational database design mandates the use of a database server in applications for dealing with data management problems. The four stages of an RDD are as follows: Relations and attributes: The various tables and attributes related to each table are identified. The tables represent entities, and the attributes represent the properties of the respective entities. Primary keys: The attribute or set of attributes that help in uniquely identifying a record is identified and assigned as the primary key
Relationships: The relationships between the various tables are established with the help of foreign keys. Foreign keys are attributes occurring in a table that are primary keys of another table. The types of relationships that can exist between the relations (tables) are: One to one One to many Many to many An entity-relationship diagram can be used to depict the entities, their attributes and the relationship between the entities in a diagrammatic way. Normalization: This is the process of optimizing the database structure. Normalization simplifies the database design to avoid redundancy and confusion. The different normal forms are as follows: First normal form Second normal form Third normal form Boyce-Codd normal form Fifth normal form By applying a set of rules, a table is normalized into the above normal forms in a linearly progressive fashion. The efficiency of the design gets better with each higher degree of normalization.
UINT-
Integrity Constraints: Integrity constraints are a set of rules. It is used to maintain the quality of information. Integrity constraints ensure that the data insertion, updating, and other processes have to be performed in such a way that data integrity is not affected. Thus, integrity constraint is used to guard against accidental damage to the database. Types of Integrity Constraint:
1. Domain constraints Domain constraints can be defined as the definition of a valid set of values for an attribute. The data type of domain includes string, character, integer, time, date, currency, etc. The value of the attribute must be available in the corresponding domain.
where ( select sum ) amount ) from loan where ( loan.bname = branch.bname >= ( select sum ) amount ) from account Where ( account.bname= branch.bname ))) Ensuring every loan customer keeps a minimum of $1000 in an account. create assertion balance-constraint check ( not exists ( select * from loan L ( where not exists ( select * from borrower B, depositor D, account A where L.loan# = B.loan# and B.cname = D.cname and D.account# = A.account# and A.balance >= 1000 ))) When an assertion is created, the system tests it for validity. If the assertion is valid, any further modification to the database is allowed only if it does not cause that assertion to be violated. This testing may result in significant overhead if the assertions are complex. Because of this, the assert should be used with great care. Some system developer omits support for general assertions or provides specialized form of assertions that are easier to test. Triggers: Triggers are the SQL statements that are automatically executed when there is any change in the database. The triggers are executed in response to certain events (INSERT, UPDATE or DELETE) in a particular table. These triggers help in maintaining the integrity of the data by changing the data of the database in a systematic fashion. Syntax create trigger Trigger_name (before | after) [insert | update | delete] on [table_name] [for each row] [trigger_body]
- CREATE TRIGGER: These two keywords specify that a triggered block is going to be declared.
- TRIGGER_NAME: It creates or replaces an existing trigger with the Trigger_name. The trigger name should be unique.
- BEFORE | AFTER: It specifies when the trigger will be initiated i.e. before the ongoing event or after the ongoing event.
- INSERT | UPDATE | DELETE : These are the DML operations and we can use either of them in a given trigger.
- ON[TABLE_NAME]: It specifies the name of the table on which the trigger is going to be applied.
- FOR EACH ROW: Row-level trigger gets executed when any row value of any column changes.
- TRIGGER BODY: It consists of queries that need to be executed when the trigger is called. Example Suppose we have a table named Student containing the attributes Student_id, Name, Address, and Marks. Now, we want to create a trigger that will add 100 marks to each new row of the Marks column whenever a new student is inserted to the table. The SQL Trigger will be: CREATE TRIGGER Add_marks BEFORE INSERT ON Student FOR EACH ROW SET new.Marks = new.Marks + 100;
The new keyword refers to the row that is getting affected. After creating the trigger, we will write the query for inserting a new student in the database. INSERT INTO Student(Name, Address, Marks) VALUES('Alizeh', 'Maldives', 110); The Student_id column is an auto-increment field and will be generated automatically when a new record is inserted into the table. To see the final output the query would be: SELECT * FROM Student; Advantages of Triggers
- Triggers provide a way to check the integrity of the data. When there is a change in the database the triggers can adjust the entire database.
- Triggers help in keeking User Interface lightweight. Instead of putting the same function call all over the application you can put a trigger and it will be executed. Disadvantages of Triggers
- Triggers may be difficult to troubleshoot as they execute automatically in the database. If there is some error then it is hard to find the logic of trigger because they are fired before or after updates/inserts happen.
- The triggers may increase the overhead of the database as they are executed every time any field is updated. Functional Dependency The functional dependency is a relationship that exists between two attributes. It typically exists between the primary key and non-key attribute within a table.
- X → Y The left side of FD is known as a determinant, the right side of the production is known as a dependent. For example: Assume we have an employee table with attributes: Emp_Id, Emp_Name, Emp_Address. Here Emp_Id attribute can uniquely identify the Emp_Name attribute of employee table because if we know the Emp_Id, we can tell that employee name associated with it. Functional dependency can be written as:
- Emp_Id → Emp_Name We can say that Emp_Name is functionally dependent on Emp_Id. Relational database design: Relational databases differ from other databases in their approach to organizing data and performing transactions. In an RDD, the data are organized into tables and all types of data access are carried out via controlled transactions. Relational database design satisfies the ACID (atomicity, consistency, integrity and durability) properties required from a database design. Relational database design mandates the use of a database server in applications for dealing with data management problems. The four stages of an RDD are as follows: Relations and attributes: The various tables and attributes related to each table are identified. The tables represent entities, and the attributes represent the properties of the respective entities. Primary keys: The attribute or set of attributes that help in uniquely identifying a record is identified and assigned as the primary key
Dependency is a crucial constraint on a database, and a minimum of one decomposed table must satisfy every dependency. If {P → Q} holds, then two sets happen to be dependent functionally. Thus, it becomes more useful when checking the dependency if both of these are set in the very same relation. This property of decomposition can be done only when we maintain the functional dependency. Added to this, this property allows us to check various updates without having to compute the database structure’s natural join.
3. Lack of Data Redundancy It is also commonly termed as a repetition of data/information. According to this property, decomposition must not suffer from data redundancy. When decomposition is careless, it may cause issues with the overall data in the database. When we perform normalization, we can easily achieve the property of lack of data redundancy. Normalization using Functional : Functional Dependency Functional dependency (FD) is a set of constraints between two attributes in a relation. Functional dependency says that if two tuples have same values for attributes A1, A2,..., An, then those two tuples must have to have same values for attributes B1, B2, ..., Bn. Functional dependency is represented by an arrow sign (→) that is, X→Y, where X functionally determines Y. The left-hand side attributes determine the values of attributes on the right-hand side. Armstrong's Axioms If F is a set of functional dependencies then the closure of F, denoted as F+, is the set of all functional dependencies logically implied by F. Armstrong's Axioms are a set of rules, that when applied repeatedly, generates a closure of functional dependencies. Reflexive rule − If alpha is a set of attributes and beta is_subset_of alpha, then alpha holds beta.
Augmentation rule − If a → b holds and y is attribute set, then ay → by also holds. That is adding attributes in dependencies, does not change the basic dependencies. Transitivity rule − Same as transitive rule in algebra, if a → b holds and b → c holds, then a → c also holds. a → b is called as a functionally that determines b. Trivial Functional Dependency Trivial − If a functional dependency (FD) X → Y holds, where Y is a subset of X, then it is called a trivial FD. Trivial FDs always hold. Non-trivial − If an FD X → Y holds, where Y is not a subset of X, then it is called a non-trivial FD. Completely non-trivial − If an FD X → Y holds, where x intersect Y = Φ, it is said to be a completely non-trivial FD. Normalization If a database design is not perfect, it may contain anomalies, which are like a bad dream for any database administrator. Managing a database with anomalies is next to impossible. Update anomalies − If data items are scattered and are not linked to each other properly, then it could lead to strange situations. For example, when we try to update one data item having its copies scattered over several places, a few instances get updated properly while a few others are left with old values. Such instances leave the database in an inconsistent state. Deletion anomalies − We tried to delete a record, but parts of it was left undeleted because of unawareness, the data is also saved somewhere else. Insert anomalies − We tried to insert data in a record that does not exist at all. Normalization is a method to remove all these anomalies and bring the database to a consistent state.
well as Zip itself. Neither Zip is a superkey nor
is City a prime attribute. Additionally, Stu_ID → Zip → City, so there exists transitive dependency. To bring this relation into third normal form, we break the relation into two relations as follows − Boyce-Codd Normal Form Boyce-Codd Normal Form (BCNF) is an extension of Third Normal Form on strict terms. BCNF states that − For any non-trivial functional dependency, X → A, X must be a super- key. In the above image, Stu_ID is the super-key in the relation Student_Detail and Zip is the super- key in the relation ZipCodes. So, Stu_ID → Stu_Name, Zip and Zip → City Which confirms that both the relations are in BCNF. Multi valued: Multivalued Dependency Multivalued dependency occurs when two attributes in a table are independent of each other but, both depend on a third attribute. A multivalued dependency consists of at least two attributes that are dependent on a third attribute that's why it always requires at least three attributes. Example: Suppose there is a bike manufacturer company which produces two colors(white and black) of each model every year. Here columns COLOR and MANUF_YEAR are dependent on BIKE_MODEL and independent of each other. In this case, these two columns can be called as multivalued dependent on BIKE_MODEL. The representation of these dependencies is shown below:
- BIKE_MODEL → → MANUF_YEA R
- BIKE_MODEL → → COLOR This can be read as "BIKE_MODEL multidetermined MANUF_YEAR" and "BIKE_MODEL multidetermined COLOR". Join Dependency Join decomposition is a further generalization of Multivalued dependencies. If the join of R1 and R2 over C is equal to relation R, then we can say that a join dependency (JD) exists. Where R1 and R2 are the decompositions R1(A, B, C) and R2(C, D) of a given relations R (A, B, C, D). Alternatively, R1 and R2 are a lossless decomposition of R. A JD ⋈ {R1, R2,..., Rn} is said to hold over a relation R if R1, R2,....., Rn is a lossless-join decomposition. The *(A, B, C, D), (C, D) will be a JD of R if the join of join's attribute is equal to the relation R. Here, *(R1, R2, R3) is used to indicate that relation R1, R2, R3 and so on are a JD of R. BIKE_MODEL MANUF_YEAR COLOR M2011 2008 White M2001 2008 Black M3001 2013 White M3001 2013 Black M4006 2017 White M4006 2017 Black
