Techopedia explains Relational Database Design (RDD)
Techopedia 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.
Examples of relational databases
Many database products implement the relational database model. They either use a SQL database for processing or can at least process SQL statements for requests and database updates. These databases range from small, desktop systems to massive cloud-based systems. They can be either open source and internally supported, open source with commercial support, or commercial closed-source systems. Here are a few of the more popular ones:
MySQL
MySQL is a common and easy to start a low-memory/disk/CPU database. It supports all the basic SQL commands, along with transactions and Atomicity, Consistency, Isolation, Durability (ACID) performance. MySQL is the most common database integrated with WordPress sites.
PostgreSQL
PostgreSQL is also open source. It provides enterprise features such as security, scalability, and support for more automation through a command-line interface, as well as direct access over the web. PostgreSQL supports stored procedures, which is a more complex programming language built on top of SQL. Teams can use stored procedures to do data extraction, transform, and load between systems. Examples of this use case include claims processing for insurance processing and order processing for complex orders. Postgres also works with qGIS or Geo Server to store and save global information.
For a closer look, see also "Database Deep Dives: PostgreSQL."
Db2
Db2 is a commercially supported relational database from IBM that has both a cloud-hosted version and an on-premises version.
The Library Management System database keeps track of readers with the following considerations –
- The system keeps track of the staff with a single point authentication system comprising login Id and password.
- Staff maintains the book catalog with its ISBN, Book title, price(in INR), category(novel, general, story), edition, author Number and details.
- A publisher has publisher Id, Year when the book was published, and name of the book.
- Readers are registered with their user_id, email, name (first name, last name), Phone no (multiple entries allowed), communication address. The staff keeps track of readers.
- Readers can return/reserve books that stamps with issue date and return date. If not returned within the prescribed time period, it may have a due date too.
- Staff also generate reports that has readers id, registration no of report, book no and return/issue info.
ER Diagram of Library Management System
This Library ER diagram illustrates key information about the Library, including entities such as staff, readers, books, publishers, reports, and authentication system. It allows for understanding the relationships between entities.
Entities and their Attributes –
- Book Entity : It has authno, isbn number, title, edition, category, price. ISBN is the Primary Key for Book Entity.
- Reader Entity : It has UserId, Email, address, phone no, name. Name is composite attribute of firstname and lastname. Phone no is multi valued attribute. UserId is the Primary Key for Readers entity.
- Publisher Entity : It has PublisherId, Year of publication, name. PublisherID is the Primary Key.
- Authentication System Entity : It has LoginId and password with LoginID as Primary Key.
- Reports Entity : It has UserId, Reg_no, Book_no, Issue/Return date. Reg_no is the Primary Key of reports entity.
- Staff Entity : It has name and staff_id with staff_id as Primary Key.
- Reserve/Return Relationship Set : It has three attributes: Reserve date, Due date, Return date.
Relationships between Entities –
- A reader can reserve N books but one book can be reserved by only one reader. The relationship 1:N.
- A publisher can publish many books but a book is published by only one publisher. The relationship 1:N.
- Staff keeps track of readers. The relationship is M:N.
- Staff maintains multiple reports. The relationship 1:N.
- Staff maintains multiple Books. The relationship 1:N.
- Authentication system provides login to multiple staffs. The relation is 1:N.
Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry-ready
Theorem Proving System (TPS) is also known as an automated proving system. Theorem proving that is applied to real-time systems design and verification generally uses several definitions and different theorems to basically help to design, implement, validate, and also verify requirements. These proving methodologies can also provide a greater level specification of software requirements along with detailed description of the implementation. These implementations then might be checked against specification simply to ensure that either it is correct or not. Automated theorem proving generally focuses and aims upon ‘identifying’ or ‘finding’ aspect.
Different Theorem Prover Systems :
There are number of theorem prover systems that are good and are related to our work. Some of them are given below :
- Interactive Mathematical Proof System (IMPS) :
Main aim of IMPS is simply to provide mechanized support just for traditional techniques of mathematical reasoning. It also provides large primitive inference steps simply to facilitate human control of deductive process and human comprehension of resulting proofs. It is also very strong alternative to PVS and also might have been required for software inspection. Logic of IMPS also allows partial functions and usually undefined terms. - Isabelle :
Isabelle is basically generic proof assistant or generic theorem-proof system. It usually provides framework in which various logics can be added by simply specifying their syntax and rules of inference. It was initially developed at University of Cambridge and Technische Universitat Munchen. It also provides meta-logic also known as weak type theory that can be required to encode object logics such as First-Order Logic (FOL), Higher-Order Logic (HOL), etc. It is also being considered as tool for implementing various logics and also defining and examining exotic proof systems.
- Higher Order Logic (HOL) Theorem Proving System :
HOL Theorem Prover is basically general and most widely used computer program for construction od specifications and formal proofs in higher-order logic. It is also used as an open platform for general theorem-proving research, and simply as a platform for just formalized mathematics. It also supports reasoning in various areas, along with hardware design and verification, proofs regarding real-time systems, compiler verification, program correctness, and also program refinement. There are basically four different HOL Systems that are handled, maintained, and developed nowadays i.e. HOL4 stems from HOL88 system, HOL Light, ProofPower, and HOL Zero. It is also used as a platform for theorem proving research. - Prototype Verification System (PVS) :
PVS generally provides mechanized support for formal specification and verification. It is usually prototype system for simply writing specifications and also constructing proofs. PVS is totally implemented and available freely. It also includes a number of predefined theories, theorem prover, different utilities, and documentation. Its specifications are generally organized into parameterized theory. It also provides extremely expressive and natural specifications. - Theorem Prover System (TPS) :
It is basically developed as a part of program of research on methods of proving theorems of higher-order logic and first-order logic. Rather than PVS, TPS can be required for our work but PVS is more accessible. It also contains formula editor that simply facilitates the construction of new formulas from other formulas that are already known to TPS.
Languages & Systems RDAMS
- Languages for Relational Database Management Systems (RDBMS) are actively being developed, among which one of the most prominent is the Structured Query Language (SQL) originally proposed by the IBM Corporation
Comments
Post a Comment