The history of database development may sound like a fast trip to boredom, but it's actually much more interesting than you might think. The landmark paper "A Relational Model of Data for Large Shared Data Banks" was published in 1969 by E.F. Codd, proposing the relational model for databases that has remained the standard ever since. In a nutshell, the relational model separates the logical organization of the database from the physical storage method.

An introductory course in database development will most likely contain the following topics. Notice the prevalence of the word "relational" in these topics.

  • Relational databases
  • Database management
  • SQL query formulation
  • Application development
  • Database development
  • Modeling data
  • Relational table normalization
  • Integration and design
  • Design of physical databases
  • Database administration
  • Transaction management
  • Data warehouse technology/management
  • Client-server processing
  • Distributed databases
  • Object database management systems


A good place to stay up to date on database developments is by visiting Database Journal. This website is current and popular.

Relational Databases

The core of every application architecture is some kind of data store, because only through these data stores can we achieve persistence in our applications.  This is the backbone of every website and application in software, whether it's a customer/orders database on an e-store, posts on a forum, or files in an operating system.  It is the job of data architects, developers, and database administrators to work out the best ways to model and store information in these data stores.

Database systems can follow a number of different models.  One such model is the relational model, and it is commonly found as the back end component on websites.  This can be contrasted to the naïve concept of a ‘flat file’ database, which in its extreme could be represented in a simple spreadsheet.  There are similarities between both: a table has columns and rows, much like a spreadsheet, where columns define headings and rows define entities within your database.  At the intersection of each row and column, therefore, we find a single property defining that entity.  In a relational model, a single entity has a single dedicated row.  For instance, let’s imagine we run a web store.  A ‘Customer’ table would have 1 row for each customer.  The columns then would describe different attributes of those customers.  For example:

ID   | NAME        | PHONE    | STATE | EMAIL        

------------------------------------------------------------

1    | Bob Smith   | 123-4567 | NY    | bob@smith.com

It is important to note that there is a unique identifier (the ID) on the row.  This is because these attributes are not unique: a phone number might have multiple customers, and names are not unique either.  For instance, let’s say Bob’s wife Alice lives at the same address and makes an order from our company.  She too is a customer but has the same phone number as Bob.  Similarly, another customer with a different address might also be called Bob Smith.  It wouldn’t do if we could not store customers with identical attributes in our database due to these collisions.  As a result, our ID is what’s known as a primary keyThis allows our database to look as follows:

ID   | NAME        | PHONE    | STATE | EMAIL              

------------------------------------------------------------

1    | Bob Smith   | 123-4567 | NY    | bob@smith.com      

2    | Alice Smith | 123-4567 | NY    | alice@smith.com    

3    | Bob Smith   | 765-4321 | TX    | bob.smith@email.com

Our database, however, would be useless if it could only contain customer information.  We also need products to sell to our customers:

PRODUCT_ID  | PRODUCT            | PRICE 

------------------------------------------

1           | Big Widget         | $30.00

2           | Small Widget       | $10.00

Again, our ID (here PRODUCT_ID) is unique.  It is conceivable that we may have multiple products of the same name (perhaps to sell in different regions) or multiple products at the same price, and so we instead need to be able to generate these artificial, unique identifiers.

Finally we must consider how to actually implement the relationship between customers and products.  Customers will be able to make orders of any of our products, and clearly we cannot model orders as part of customers or products, as they are another independent entity.  Instead, we must build a table to model orders.  This makes use of a new concept: Foreign keys.  Foreign keys are references to primary keys from other tables.  They have the same values but are no longer constrained by uniqueness.  As a result, we can say that one customer has multiple products in an order, like so:

ORDER_ID | CUSTOMER_ID | PRODUCT_ID | QUANTITY

----------------------------------------------

1        | 1           | 1          | 3       

1        | 1           | 2          | 1       

Note that we have two rows.  These indicate that customer id 1 (Bob) has placed an order for 3 large widgets (product id 1) and 1 small widget (product id 2).  They share an order number, because it is a single order.  We could just as easily represent multiple orders by the same person, because there isn’t a unique key for this table.

These entity relationships are often represented graphically in what’s known as an Entity Relationship Diagram.  Below is a simple example which reflects our examples above.  Pay attention to the "crow’s feet" that show which side of the relationship is 'one' and which is 'many' – for instance, one customer has many orders.  Likewise, one order has many products.

Knowing how to model and represent data entities in such relationship diagrams is a core skill used in both academic and commercial development.  Databases excel at the manipulation and storage of large volumes of data, and can quickly become impossibly complex.  Only through understanding these core concepts can one succeed in the field of data architecture or database administration.

SQL Query Formulation

Being able to store data in databases is only half of the picture.  Developers and Database Administrators (DBAs) must be able to pull data back out of databases efficiently and effectively.  This is done by writing queries against databases.  This article specifically deals with SQL queries (where SQL stands for Structured Query Language), but other database systems exist and have their own query languages (for instance NoSQL on something like Mongo DB).  SQL queries are most commonly associated with relational database schemas, although exceptions exist.

The language of a query in SQL is driven by two concepts.  The first is that of the reserved keywords which can vary between implementations of SQL (the previous link refers to Microsoft SQL Server’s implementation, Transact SQL), but are largely identical.  These keywords are used to leverage certain operations on the database, and can be thought of as being like the function library if you were to compare databases to coded applications.  The second element is the schema, which is to say the set of tables, views and stored procedures that exist on the database, and are akin to the variables that exist in your application. 

By far the most common SQL keyword is SELECT.  SELECT is used to retrieve data from a table, either by specifying specific columns and rows or simply requesting everything.  SELECT is paired with the FROM keyword, in order to specify which table you are selecting your data from.  The simplest version of select simply returns everything from a table (where TABLE is the name of a table in this example):

SELECT * FROM TABLE

However, we can begin to narrow down on either the columns or rows we are selecting from, as we rarely wish to extract the full contents of a table.  We do this by specifying the column names, separated by commas, instead of using the asterisk:

SELECT COLUMN1, COLUMN2 FROM TABLE

It should be noticed that in some implementations you will see objects like column names and table names wrapped with square brackets.  This is generally considered good practice as it allows flexibility in the naming of these objects. 

Selecting the entire contents of two columns remains a relatively crude operation.  Instead we will want only certain rows.  As a result, we can introduce the WHERE keyword to our SELECT… FROM statement.  WHERE allows us to pick certain criteria to filter our results on, which we supply with the keyword.  As an example, therefore, our query would be expanded to look like this:

SELECT COLUMN1, COLUMN2 FROM TABLE
WHERE COLUMN1 = VALUE

The conditions and values used can vary.  For instance, Text columns must be compared against other values in quotation marks, such as NAME = ‘Bob’, but can also use the ‘like’ keyword to do partial matches.  For instance, NAME LIKE ‘%ob’ would match ‘Bob’ or ‘Rob’, but not ‘Robert’, and ‘Bob%’ would match ‘Bob’ or ‘Bobby’.  Such pattern matching can quickly become complex and is a topic and of itself.  If the values are numeric, we can also compare to conditions such as greater than (>) or less than (<) – but we do not use quotation marks for the value.  For instance, ORDER_NUMBER > 3.  We can then combine these with AND or OR to produce complex logic – like WHERE ORDER_NUMBER = 3 AND NAME LIKE ‘BOB%’.

Understanding how to perform these types of queries gives you the fundamental building block for any SQL-based database interrogation.  This allows you to move on to advanced topics like joins, CTEs, Stored Procedures and inner queries.  Indeed if you wish to undertake a career in software, whether as a developer, tester or even product manager/business analyst, you will either be required to know how to write SQL queries or find it extremely beneficial in performing related duties.

If you wish to practice what you have read here, websites like w3schools often provide live demos where you can run queries against a virtual database.  This enables you to practice without having to set up and populate your own database, which can be a large task in and of itself.  SQL queries can become very complex and perform poorly, so it is definitely worth learning the basics

To fulfill our tutoring mission of online education, our college homework help and online tutoring centers are standing by 24/7, ready to assist college students who need homework help with all aspects of database development. Our computer science tutors can help with all your projects, large or small, and we challenge you to find better onlinedatabase development tutoring anywhere.

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
For faster response, you may skip assigning directly to a tutor to receive the first tutor available.
That tutor may not be available for several hours. Please try another tutor if you're in a hurry.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Get help from a qualified tutor
Live Chats