Fundamentals of RDBMS
• A Relational Database management System(RDBMS) is a database management system based on the relational model introduced by E.F Codd.
• In relational model, data is stored in relations(tables) and is represented in tuples(rows).
• RDBMS is used to manage Relational database.
• Relational database may be a collection of organized sets of tables related to each other, and from which data can be accessed easily.
• Relational Database is the most commonly used database these days.
What is Table?
• In Relational database model, a table is a collection of data elements organised in terms of rows , columns.
• A table is additionally considered as a convenient representation of relations.
• But a table can have duplicate row of data while a true relation cannot have duplicate data.
• Table is that the most simplest type of of data storage.
What is a Tuple?
• A single entry in a table is called a Tuple or Record or Row.
• A tuple in a table represents a set of related data.
• For example, the above Employee table has 4 tuples/records/rows.
1 Adam 34 13000
What is an Attribute?
• Attribute(fields) refers to columns of table
• Employee table consist of four attributes, ID, Name, Age and Salary
• Attribute Domain
• When an attribute is defined in a relation(table), it is defined to hold only a certain type of values, which is known as Attribute Domain.
• Hence, the attribute Name will hold the name of employee for every tuple. If we save employee's address there, it will be violation of the Relational database model.
Atomic Domain
• A domain is atomic if elements of the domain are considered to be indivisible units.
• The domain of Marital Status has a set of possibilities: Married, Single, Divorced.
• The domain of Shift has the set of all possible days: {Mon, Tue, Wed…}.
• The domain of Salary is the set of all floating- point numbers greater than 0 and less than 200,000.
• The domain of First Name is the set of character strings that represents names of people.
Data types
• Numeric data types like int, tinyint, bigint, float, real etc.
• Date and Time data types
E.F. Codd rules
• E.F Codd was a Computer Scientist who invented the Relational model for Database management.
• Based on relational model, the Relational database was created.
• Codd proposed 13 rules popularly known as Codd's 12 rules to test DBMS's concept against his relational model.
• Codd's rule actually define what quality a DBMS requires in order to become a Relational Database Management System(RDBMS).
• Till now, there is hardly any commercial product that follows all the 13 Codd's rules.
• Even Oracle follows only eight and half(8.5) out of 13.
Rule 0: Foundation Rule
• This rule states that for a system to qualify as an RDBMS, it must be able to manage database entirely through the relational capabilities.
Rule 1: Information rule
• The data stored in a database, may it be user data or metadata, must be a value of some table cell. Everything in a database must be stored in a table format.
Rule 2: Guaranteed Access
• Every single data element (value) is guaranteed to be accessible logically with a combination of table- name, primary-key (row value), and attribute- name (column value). No other means, such as pointers, can be used to access data
Rule 3: Systematic treatment of NULL
• Null has several meanings, it can mean missing data, not applicable or no value. It should be handled consistently. Also, Primary key must not be null, ever. Expression on NULL must give NULL
Rule 4: Active Online Catalog
• The structure description of the entire database must be stored in an online catalog, known as data dictionary, which can be accessed by authorized users. Users can use the same query language to access the catalog which they use to access the database itself.
Rule 5: Powerful and Well-Structured Language
• One well structured language must be there to provide all manners of access to the data stored in the database. Example: SQL, etc. If the database allows access to the data without the use of this language, then that is a violation.
Rule 6: View Updating Rule
• Different views created for various purposes should be automatically updatable by the system.
Rule 7: High-Level Insert, Update, and Delete Rule
• There must be Insert, Delete, Update operations at each level of relations. Set operation like Union, Intersection and minus should also be supported.
Rule 8:Physical data independence
• Any modification in the physical location of a table should not enforce modification at application level.
Rule 9:Logical data independence
• The logical data in a database must be independent of its user’s view (application). Any change in logical data must not affect the applications using it. For example, if two tables are merged or one is split into two different tables, there should be no impact or change on the user application. This is one of the most difficult rule to apply.
Rule 10: Integrity Independence
• The database should be able to enforce its own integrity rather than using other programs. Key and Check constraints, trigger etc, should be stored in Data Dictionary. This also make RDBMS independent of front-end.
Rule 11:Distribution Independence
• A database should work properly regardless of its distribution across a network. Even if a database is geographically distributed, with data stored in pieces, the end user should get an impression that it is stored at the same place. This lays the foundation of distributed database.
Rule 12:Nonsubversion Rule
• If low level access is allowed to a system it should not be able to subvert or bypass integrity rules to change the data. This can be achieved by some sort of looking or encryption.
Comments
Post a Comment