Relational Database Management System (RDBMS)
Relational Database Management System (RDBMS) Concepts.
What is Data?
• Data is a collection of facts, such as numbers, words, measurements, observations or just descriptions of things.
• Raw data – that needs to be processed to give it meaning.
• Information – processed data which have meaning.
• Data, in the context of databases, refers to all the single items that are stored in a database, either individually or
as a set.
What is Database?
• Base : “a structure on which something rests”
• Data Base – “a structure that holds data”.
What is RDBMS?
• A relational database is a type of database and its based in relational model of data (E.F Codd in 1970).
• Stores and provides access to data points that are related to one another.
• To ensure that data is always accurate and accessible, relational databases follow certain integrity rules.
• The relational model means that the logical data structures—the data tables, views, and indexes—are separate from the physical storage structures.
• Data in a relational database is organized into tables also called Relation.
• A relational database management system (RDBMS) is a program that allows you to create, update, and administer a relational database.
• Most relational database management systems use the SQL language to access the database.
Advantages of RDBMS:
• Enhanced Data Security.
• Retain Data Consistency.
• Better Flexibility and Scalability.
• Easy Maintenance.
What is a Table, Column and Row?
| Table | Column | Row |
| • Relational database system contains one or more objects called tables. • The data or information for the database are stored in these tables. • Tables are uniquely identified by their names • The underlying file system may have a limit on the number of files that represent tables. • Individual storage engines may impose engine-specific constraints. |
• Column is a set of data values, all of a single type, in a table. • Columns define the data in a table. • A column may contain text values, numbers, or even pointers to files in the operating system. • Column name is unique in table. • Table need at least one column. • Field = Column. • Field is the intersection of a row and a column. |
• In database terms, a row is a collection of fields that make up a record. • A row is also called a record. • Each row represents a unique record. • A table can contain zero or more rows. When there are zero, it said to be empty table. • The number of rows in the table is limited only by the storage capacity of the server. |
Different Types of Keys?
1.Candidate Key:
• A Candidate key is an attribute or set of attributes that uniquely identifies a record.
• Table can have multiple candidate keys.
• Among the set of candidate, one candidate key is chosen as Primary Key.
2.Primary Key:
• Primary key is a set of one or more fields (columns) of a table that uniquely identify a record in database table.
• A table can have only one primary key and one candidate key can select as a primary key.
• The primary key should be chosen such that its attributes are never or rarely changed.
• Cannot contain “NULL”.
• Primary key field contain a clustered index.
3. Secondary Key:
• Candidate keys that are not selected as primary key.
• Can also work as a primary key.
• Also called Alternate key
4.Unique Key:
• A unique key is a set of one or more attribute that can be used to uniquely identify the records in table.
• Unique key is similar to primary key but unique key field can contain a “Null” value but primary key doesn’t allow “Null” value.
• Unique field contain a non-clustered index.
5.Composite Key:
• Composite key is a combination of more than one attributes that can be used to uniquely identity each record.
• A composite key may be a candidate or primary key.
6. Foreign Key:
• Foreign Key is a field in database table that is Primary key in another table.
• Foreign key is used to generate the relationship between the tables.
• A foreign key can accept null and duplicate value.
What is a Transaction and what are ACID properties?
Transaction:
• A transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit.
• Transactions access data using read and write operations.
• A transaction groups SQL statements so that they are either all committed, which means they are applied to the
database, or all rolled back, which means they are undone from the database.
• If any operation within the transaction fails, the entire transaction will fail.
• Transfer $1000.00 from my Savings Account 9187 to Checking Account 7819.
Acid Properties:
• ACID is a concept (and an acronym) that refers to the four properties of a transaction in a database system, which are: Atomicity, Consistency, Isolation and Durability.
• These properties ensure the accuracy and integrity of the data in the database.
• Ensuring that the data does not become corrupt as a result of some failure.
• Because of the ACID properties, we can focus on the application logic instead of failures, recovery and sync of the data.
Atomicity:
• A transaction must be an atomic unit of work.
• that is, either all of its operations are executed or none.
• The transaction should be completely executed or fails completely, if one part of the transaction fails, all the transaction will fail.
• This provides reliability because if there is a failure in the middle of a transaction, none of the changes in that transaction will be committed.
Consistency:
• The database must remain in a consistent state after any transaction.
• ensures that the transaction maintains data integrity constraints, leaving the data consistent.
• It also refers to the correctness of a database.
• The data that is saved in the database must always be valid.
• The data will be valid according to defined rules, including any constraints.
• If you have a column that does not allow negative numbers, and try to add or modify a record, using a value lower than zero on this column, the transaction will fail.
Isolation:
• All the transactions will be carried out and executed as if it is the only transaction in the system. No transaction will affect the existence of any other transaction.
• Ensuring that the transaction will not be changed by any other concurrent transaction.
• For example, if two clients are trying to buy at the same time the last available product on the web site, when the first user finishes the shopping, it will make the transaction of the other user be interrupted.
Durability:
• Once a transaction is completed and committed, its changes are persisted permanently in the database.
• Ensures that the information that is saved in the database is immutable until another update or deletion transaction affects it.
• For this purpose, the completed transactions are recorded on permanent memory devices (nonvolatile) such as hard drives, so the data will be always available, even if the DB instance is restarted.
What is Normalization and different forms of Normalization ?
Why we need Normalization:
• Data Redundancy.
• Waste of disk space.
• Creates maintenance problems.
• Inconsistent dependency.
What is Normalization:
• Normalization is the process of organizing data in a database, in order to reduce data redundancy and improve data integrity
• First proposed by Edgar F. Cord as part of his relational model.
• Systematic approach of decomposing tables to eliminate data redundancy(repetition) and undesirable characteristics like Insertion, Update and Deletion Anomalies.
• It is a multi-step process that puts data into tabular form, removing duplicated data from the relation tables.
• Normalization works in accordance with a series of so-called normal forms.
• 1NF, 2NF, 3NF….
• Although other levels of normalization are possible, third normal form is considered the highest level necessary for most applications.
1NF or First Normal Form:
A relation is in 1NF If …
• It Contains only atomic values, An atomic value is a value that cannot be divided
• There are no Repeating Groups, A repeating group is a series of fields/attributes that are repeated throughout a database table.
2NF or Second Normal Form:
A relation is in 2NF If …
• It is in 1NF.
• All partial key dependencies are removed, where an attribute in a table depends on only a part of the primary key and not on the whole key.
3NF or Third Normal Form:
A relation is in 3NF If …
• It is in 2NF.
• Non-Primary key columns shouldn’t depend on the other non-Primary key columns.
• There is no transitive functional dependency.
See Also: