Understanding Keys in Relational Databases
Keys play a crucial role in relational databases by uniquely identifying records and establishing relationships between tables. Primary keys are essential for data integrity, ensuring each entity is identified uniquely. Learn about primary keys, types of keys, and examples of creating tables with primary keys in SQL.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Keys Keys play an important role in the relational database. It is used to uniquely identify any record or row of data from the table. It is also used to establish and identify relationships between tables.
Example ID is used as a key in the Student table because it is unique for each student. In the PERSON table, passport_number, license_number, SSN are keys since they are unique for each person.
Primary Key It is the first key used to identify one and only one instance of an entity uniquely. An entity can contain multiple keys, as we saw in the PERSON table. The key which is most suitable from those lists becomes a primary key. In the EMPLOYEE table, ID can be the primary key since it is unique for each employee. In the EMPLOYEE table, we License_Number and Passport_Number as primary keys since they are also unique. For each entity, the primary key selection is based on requirements and developers. can even select
Primary Key It is a unique key. It can identify only one tuple (a record) at a time. It has no duplicate values, it has unique values. It cannot be NULL. Primary keys are not necessarily to be a single column; more than one the column can also be a primary key for a table. Eg:- STUDENT table SNO is a primary key SNO SNAME ADDRESS PHONE
Examples CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (ID) );
Example CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CONSTRAINT PK_Person PRIMARY KEY (ID,LastName) In the example above there is only ONE Primary_Key (PK_Person). However, the VALUE of the primary key is made up of TWO COLUMNS (ID + LastName).
SQL PRIMARY KEY on ALTER TABLE ALTER TABLE Persons ADD PRIMARY KEY (ID); Primary Key constraint on multiple columns, use the following SQL syntax: ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY K EY (ID,LastName);
Drop Primary Key ALTER TABLE Persons DROP PRIMARY KEY; ALTER TABLE Persons DROP CONSTRAINT PK_Person;
Candidate key A candidate key is an attribute or set of attributes that can uniquely identify a tuple. Except for the primary attributes are considered a candidate key. The candidate keys are as strong as the primary key. key, the remaining
Example In the EMPLOYEE table, id is best suited for the primary key. The rest of the attributes, like SSN, Passport_Number, License_Number, etc., are considered a candidate key.
Super Key Super key is an attribute set that can uniquely identify a tuple. A super key is a superset of a candidate key.
Example In the above EMPLOYEE table, for(EMPLOEE_ID, EMPLOYEE_NAME), the name of two employees can be the same, but their EMPLYEE_ID can't be the same. Hence, this combination can also be a key. The super key would be EMPLOYEE-ID (EMPLOYEE_ID, EMPLOYEE-NAME), etc.
Foreign key Foreign keys are the column of the table used to point to the primary key of another table. Every employee works in a specific department in a company, and employee and department are two different entities. So we can't store the department's information in the employee table. That's why we link these two tables through the primary key of one table. We add the primary key of the DEPARTMENT table, Department_Id, as a new attribute in the EMPLOYEE table. In the EMPLOYEE table, Department_Id is the foreign key, and both the tables are related.
Person Table PersonID LastName FirstName Age 1 Hansen Ola 30 2 Svendson Tove 23 3 Pettersen Kari 20 Orders Table OrderID OrderNumber PersonID 1 77895 3 2 44678 3 3 22456 2 4 24562 1 The "PersonID" column in the "Orders" table points to the "PersonID" column in the "Persons" table.
Create Table CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFEREN CES Persons(PersonID) );
Alternate key There may be one or more attributes or a combination of attributes that uniquely identify each tuple in a relation. These attributes or combinations of the attributes are called the candidate keys. One key is chosen as the primary key from these candidate keys, and the remaining candidate key, if it exists, is termed the alternate key. In other words, the total number of the alternate keys is the total number of candidate keys minus the primary key. The alternate key may or may not exist. If there is only one candidate key in a relation, it does not have an alternate key.
Example employee relation has two attributes, Employee_Id and PAN_No, that act as candidate keys. In this relation, Employee_Id is chosen as the primary key, so the other candidate key, PAN_No, acts as the Alternate key.
Composite key Whenever a primary key consists of more than one attribute, it is known as a composite key. This key is also known as Concatenated Key.
Example In employee relations, we assume that an employee may be assigned multiple roles, and an employee may work on multiple projects simultaneously. So the primary key will be composed of all three attributes, namely Emp_ID, Emp_role, and Proj_ID in combination. So these attributes act as a composite key since the primary key comprises more than one attribute.
Artificial key The key created using arbitrarily assigned data are known as artificial keys. These keys are created when a primary key is large and complex and has no relationship with many other relations. The data values of the artificial keys are usually numbered in a serial order
Example The primary key, which is composed of Emp_ID, Emp_role, and Proj_ID, is large in employee relations. So it would be better to add a new virtual attribute to identify each tuple in the relation uniquely.
Constraints On modeling the design of the database we can put some restrictions like what values are allowed to be inserted in the relation, what kind of modifications and deletions are allowed in the relation. These are the restrictions we impose on the relational database. In models like ER models, we did not have such features. relational
Cont.. Constraints in the databases can be categorized into 3 main categories: Constraints that are applied in the data model is called Implicit constraints. Constraints that are directly applied in the schemas of the data model, by specifying them in the DDL(Data Definition Language). These are called as schema-based constraints or Explicit constraints. Constraints that cannot be directly applied in the schemas of the data model. We call these Application based or semantic constraints.
Mainly Constraints on the relational database are of 4 types Domain constraints Key constraints Entity Integrity constraints Referential integrity constraints
Domain constraints Every domain must contain atomic values(smallest indivisible units) it means composite and multi- valued attributes are not allowed. We perform datatype check here, which means when we assign a data type to a column we limit the values that it can contain. Eg. If we assign the datatype of attribute age as int, we cant give it values other then int datatype.
Example EID Name Phone 01 Dinesh Sharma 123456789 234455667 Explanation: In the above relation, Name is a composite attribute and Phone is a multi-values attribute, so it is violating domain constraint.
Key Constraints or Uniqueness Constraints These are called uniqueness constraints since it ensures that every tuple in the relation should be unique. A relation can have multiple keys or candidate keys(minimal superkey), out of which we choose one of the keys as primary key, we don t have any restriction on choosing the primary key out of candidate keys, but it is suggested to go with the candidate key with less number of attributes. Null values are not allowed in the primary key, hence Not Null constraint is also a part of key constraint.
ACID Properties in DBMS A transaction is a single logical unit of work that accesses and possibly modifies the contents of a database. Transactions access data using read and write In order to maintain consistency in a database, before and after the transaction, certain properties are followed. These are called ACID properties. operations.
Cont.. We mean that either the entire transaction takes place at once or doesn t happen at all. There is no midway i.e. transactions do not occur partially. Each transaction is considered as one unit and either runs to completion or is not executed at all. It involves the following two operations. Abort: If a transaction aborts, changes made to the database are not visible. Commit: If a transaction commits, changes made are visible. Atomicity is also known as the All or nothing rule .
Example Consider the following transaction T consisting of T1 and T2: Transfer of 100 from account X to account Y.
Consistency This means that integrity constraints must be maintained so that the database is consistent before and after the transaction. It refers to the correctness of a database. Referring to the example above, The total amount before and after the transaction must be Total before T occurs = 500 + 200 = 700. Total after T occurs = 400 + 300 = 700. Therefore, the database is consistent. Inconsistency occurs in case T1 completes but T2 fails. As a result, T is incomplete. maintained.
Isolation This property ensures that multiple transactions can occur concurrently without inconsistency of the database state. Transactions occur independently without interference. Changes occurring in a particular transaction will not be visible to any other transaction until that particular change in that transaction is written to memory or has been committed. This property ensures that the execution of transactions concurrently will result in a state that is equivalent to a state achieved these were executed serially in some order. leading to the
Cont.. Let X= 500, Y = 500. Consider two transactions T and T . Suppose T has been executed till Read (Y) and then T starts. As a result, interleaving of operations takes place due to which T reads the correct value of X but the incorrect value of Y and sum computed by T : (X+Y = 50, 000+500=50, 500) is thus not consistent with the sum at end of the transaction: T: (X+Y = 50, 000 + 450 = 50, 450). This results in database inconsistency, due to a loss of 50 units. Hence, transactions must take place in isolation and changes should be visible only after they have been made to the main memory.
Durability This property ensures that once the transaction has completed execution, the updates and modifications to the database are stored in and written to disk and they persist even if a system failure occurs. These updates now become permanent and are stored in non-volatile memory. The effects of the transaction, thus, are never lost.
Some important points Responsibility for maintaining properties Property Atomicity Transaction Manager Consistency Application programmer Isolation Concurrency Control Manager Durability Recovery Manager
Advantages of ACID Properties in DBMS 1. Data Consistency: ACID properties ensure that the data remains consistent and accurate after any transaction execution. 2. Data Integrity: ACID properties maintain the integrity of the data by ensuring that any changes to the database are permanent and cannot be lost. 3. Concurrency Control: ACID properties help to manage multiple transactions occurring concurrently by preventing interference between them. 4. Recovery: ACID properties ensure that in case of any failure or crash, the system can recover the data up to the point of failure or crash.
Disadvantages of ACID Properties in DBMS 1. Performance: The ACID properties can cause a performance overhead in the system, as they require additional processing to ensure data consistency and integrity. 2. Scalability: The ACID properties may cause scalability issues in large distributed systems where multiple transactions occur concurrently. 3. Complexity: Implementing the ACID properties can increase the complexity of the system and require significant expertise and resources. Overall, the advantages of ACID properties in DBMS outweigh the disadvantages. They provide a reliable and consistent approach to data 4. Management, ensuring data integrity, accuracy, and reliability. However, in some cases, the overhead of implementing ACID properties can cause performance and scalability issues. Therefore, it s important to balance the benefits of ACID properties against the specific needs and requirements of the system.
TCL Commands TCL Languages. These commands are used for maintaining consistency of the database and for the management of transactions made by the DML commands. Transaction Control stands for
Applications of TCL 1. Committing Transactions: TCL statements can be used to commit a transaction, which means to permanently save the changes made during the transaction to the database. 2. Rolling Back Transactions: TCL statements can be used to roll back a transaction, which means to undo the changes made during the transaction and restore the database to its previous state. 3. Setting Transaction Isolation Levels: TCL statements can be used to set the transaction isolation level, which determines the level of concurrency and consistency in the database. 4. Savepoints: TCL statements can be used to set savepoints within a transaction, allowing for partial rollback if needed. 5. Managing Transactions in Stored Procedures: TCL statements can be used in stored procedures to manage transactions within the scope of the procedure.
TCL Commands 1.COMMIT 2.ROLLBACK 3.SAVEPOINT
Commit This command is used to save the data permanently. Whenever we perform any of the DML command like -INSERT, DELETE or UPDATE, these can be rollback if the data is not stored permanently. So in order to be at the safer side COMMIT command is used. Syntax: commit;
Rollback This command is used to get the data or restore the data to the last savepoint or last committed state. If due to some reasons the data inserted, deleted or updated is not correct, you can rollback the data to a particular savepoint or if savepoint is not done, then to the last committed state. Syntax: rollback;
