OK, so we know that you're here to weigh the technical differences between relational and non-relational databases. But before diving right in. Learn about how databases work, what a relational database is, the importance of database management systems and SQL. A database management system (DBMS) is the software which controls the A relational database stores data in tables.
Database Design Objective A well-designed database shall: This is because duplicate data not only waste storage spaces but also easily lead to inconsistencies. Ensure Data Integrity and Accuracy: Databases are usually customized to suit a particular application. No two customized applications are alike, and hence, no two database are alike.
Guidelines usually in terms of what not to do instead of what to do are provided in making these design decision, but the choices ultimately rest on the you - the designer. Define the Purpose of the Database Requirement Analysis Gather the requirements and define the objective of your database, e. Drafting out the sample input forms, queries and reports, often helps. Gather Data, Organize in tables and Specify the Primary Keys Once you have decided on the purpose of the database, gather the data that are needed to be stored in the database.
Divide the data into subject-based tables. Choose one column or a few columns as the so-called primary key, which uniquely identify the each of the rows. Primary Key In the relational model, a table cannot contain duplicate rows, because that would create ambiguities in retrieval. To ensure uniqueness, each table should have a column or a set of columnscalled primary key, that uniquely identifies every records of the table.
For example, an unique number customerID can be used as the primary key for the Customers table; productCode for Products table; isbn for Books table.
A primary key is called a simple key if it is a single column; it is called a composite key if it is made up of several columns. The primary key is also used to reference other tables to be elaborated later.
Relational Databases Looking at the Whole Picture
You have to decide which column s is to be used for primary key. The decision may not be straight forward but the primary key shall have these properties: The values of primary key shall be unique i. For example, customerName may not be appropriate to be used as the primary key for the Customers table, as there could be two customers with the same name. The primary key shall always have a value.
Introduction to Information Technology/Relational Databases
In other words, it shall not contain NULL. Consider the followings in choose the primary key: The primary key shall be simple and familiar, e. The value of the primary key should not change. Primary key is used to reference other tables. If you change its value, you have to change all its references; otherwise, the references will be lost.
For example, phoneNumber may not be appropriate to be used as primary key for table Customers, because it might change. Primary key often uses integer or number type.
But it could also be other types, such as texts. However, it is best to use numeric column as primary key for efficiency. Primary key could take an arbitrary number. This arbitrary number is fact-less, as it contains no factual information. Unlike factual information such as phone number, fact-less number is ideal for primary key, as it does not change.
Primary key is usually a single column e. But it could also make up of several columns. You should use as few columns as possible. Let's illustrate with an example: Name may not be unique. Phone number and address may change.
A Quick-Start Tutorial on Relational Database Design
Hence, it is better to create a fact-less auto-increment number, say customerID, as the primary key. Create Relationships among Tables A database consisting of independent and unrelated tables serves little purpose you may consider to use a spreadsheet instead. The schemas of operational databases reflect the operational needs of the applications that access them, often resulting in a reasonably normalized schema with some portions of it denormalized for performance reasons.
Reporting databaseson the other hand, are typically highly denormalized with significant data redundancy within them to support a wide range of reporting needs. Every technology has its strengths and weaknesses, and RDBMS technology is not an exception to this rule. Luckily there are ways that you can mitigate some of these challenges, and encapsulation is an important technique to do so.
Your Greatest Ally Encapsulation is a design issue that deals with how functionality is compartmentalized within a system. You should not have to know how something is implemented to be able to use it. The implication of encapsulation is that you can build anything anyway you want, and then you can later change the implementation and it will not affect other components within the system as long as the interface to that component did not change.
For example, consider your bank. How do they keep track of your account information, on a mainframe, a mini, or a PC? What database do they use? You just walk up to a teller and do whatever transactions you wish.
By encapsulating access to a database, perhaps through something as simple as data access objects or something as complex as a persistence framework, you can reduce the coupling that your database is involved with.
The Implementation Strategies for Persisting Objects in RDBs chapter compares and contrasts various encapsulation strategies that you have available to you. For now assume that it is possible to hide the details of your database schema from the majority of the developers within your organization while at the same time giving them access to your database.
Some people, often just the Agile DBA s responsible for supporting the database, will need to understand and work with the underlying schema to maintain and evolve the encapsulation strategy. One advantage of encapsulating access to your database is that it enables application programmers to focus on the business problem itself.
The application programmers will work with these data access classes, not the database. This enables your Agile DBA to evolve the database schema as they need to, perhaps via database refactoringsand all they need to worry about is keeping the data access classes up to date.
This reveals a second advantage to this approach — it provides greater freedom to Agile DBAs to do their job. Figure 4 depicts the concept of encapsulating access to your database, showing how the best case scenario of Figure 2 and the worst case scenario of Figure 3 would likely change.
In the best-case scenario your business source code would interact with the data access objects that in turn would interact with the database. The primary advantage would be that all of the data-related code would be in one place, making it easier to modify whenever database schema changes occurred or to support performance-related changes. However, from the point of view of the application programmer this is a much easier change to detect and act on — with the database encapsulation strategy in place the application programmers are only dealing with program source code e.
A relationship shows how each entity is related to another. Junction Tables[ edit ] Junction tables aka Bridge Tables are tables designed to handle many-to-many relationships between two groups. They create "junctions" between data sets, allowing associations with one another. For instance, if a data set had a group for people's names and a group for people's classes, a junction table would allow correlations between the two.
This is a many-to-many relationship since more than one student can be in a single class, and one class can have several students.
Constraints[ edit ] Constraints make it possible to further restrict the domain of an attribute. For instance, a constraint can restrict a given integer attribute to values between 1 and Constraints provide one method of implementing business rules in the database.
SQL implements constraint functionality in the form of check constraints.