Introduction to Database Systems

Oracle is one of the Relational Database Management Systems that came out of the 1960s but is still widely used and deployed today. It's still under active development and is adding new modern features to it.  

This article will go over the outline of the courses, and relational model and the relational algebra for the topics of the courses. This course is the design and implement of database management systems. 

There's Relational Databases, Storage, Execution, Concurrency Control, Recovery, Distributed Databases, Potpourri. 

Databases are used everywhere. Any computer site is going to be a database. Everyone has database problems, many things can be reduced to database problems. Database tries to model some aspect of the real world.

Let's say that we want Spotify. A database keeps track of the various artists that we have and their albums. We want to write this code ourselves. The simplest database would be where we store our data in comma-separated values, and in the application code, we would store this data in comma-separated values (.csv files) that we manage our own code.

Let's say we have 2 entities in our database, with our artist, the album, and the year of the album.

 

Let's see when ICE cube went solo.

for line in file:

    record = parse(line)

    if  "Ice Cube" == record[0]:

          return record[1]

However, this is a bad way of managing data! How do we know we don't have a spelling mistake, or if Ice Cube changes his name?  What if someone overwrites the album year with a stream? What if the album has multiple artists? Well, that's problematic. You need to write specialized logic to deal with these problems. How do we find a record? A for loop is hard if we have a billion albums? 

What if the application is written, and now I want to use the same database with the application written in another language? My mobile phone application might be written in another language, and I might have to duplicate that application to parse that file. What if we now want to create a new application that uses the database? What if two treads try to write the same thread at the same time? Data might be garbled! How do I ensure that my data is safe? 

You want a database management system to manage this for you, allowing applications to store and analyze information in a database, so you're not reinventing the wheel all over again. A general-purpose system is designed to allow applications to design, create, update, and administer databases. 

Database applications are difficult to build and maintain. There's a tight coupling between logical and physical layers. You have to know what queries your app would execute. You would have to refactor the code every single time. Especially with Amazon, Microsoft, Google, and Cloud Computing. People were fixing up software they didn't need to. So, a relational model was proposed.Ted Codd proposed:

1. Store database in simple data structured

2. Access data through high-level language

3. Physical storage left up to implementation. 

We would define storing all our table and accessing them through a high-level language. We would just say "Hey, we want you to compute this answer, please do it for me." 

There are now very complex query plans and have optimizers that have better jobs than what humans can do. Now the physical storage strategy is left up to the implementation of the database system. 

For some applications, their database may want to be stored in one way, and if the application evolves it may be better to store in another way we don't have to change any of the application code, a clean separation between the local and physical layers.  

A data model is a collection of concepts for describing data in a database. A schema is a description of a particular collection of data using a given data model. (What is the data we are storing?)

Relational data model is one of several others (Key/Vale, Graph, Document, Column-family, Array, Hierarchical, Network). JSON is part of NoSQL. Relational data models can model various things, with various benefits over basic data models. Array/Matrix is used in Machine Learning. Hierarchical and Network are the original data models, which are pretty obsolete. You don't want to be using these things if you are part of a new startup. 

We want to specify a valid instance of a database given the schema, and how to manipulate/access the data given the database. 

A relation is an unordered set that contains the relationship of attributes that represent entities.

A tuple is a set of attributes for the instance for the entity for our relation. All the values had to be atomic/scalar values in the original data model. In recent times, you now can store arrays and JSON objects in relational database. The special value NULL is a member of every domain them. How you store them is left up to the implementation. 

An n-nary relation is a table with n columns. A primary key uniquely identifies a single tuple, and some DBM's automatically create an internal primary key (id) if you don't identify one, to uniquely identify the tuple.

A foreign key specifies that an attribute from one relation has a map to a tuple in another relation. MySQL the auto-generation is AUTO_INCREMENT, and SEQUENCE for SQL. 

A foreign key means an attribute from one relation exists in at least one tuple in another relation. This helps to maintain integrity across certain relations. We may want to store multiple artists that can collaborate on an album together. 

To sore multiple artists on a single album, we basically have artist id and mapping it to the album id. 

Data manipulation languages help to store and retrieve information from a database. 

Procedural specifies high-level strategy of DBMS for the result and Non-Procedural specifies only what data is wanted and how not to find it. Procedural language runs by relational algebra. A non-procedural level would deal mainly with relational calculus for query optimization and plans. 

The fundamental operations to manipulate tuples takes one or more relations as inputs and outputs a relation (select, projection, union, intersection, difference, product, join). 

Select chooses a subset of the tuples from a relation that satisfies a selection predicate. The easy way to remember this is by making a relational element a sigma, or the first in the image above. Here finds the tuples where aid= 'a'2' and bid > 102 etc. Don't specify the order, it's up to the optimization algorithm.

Projection generates a relation with tuples that contain specific attributes.

A union operation generates a relation that contains all the tuples in either the first relation, the second relation, or both of them. 

The intersection generates a relation that contains only the tuples that appear in both relation.

Difference is taking all the tuples that appear in the first relation but not in the second one. You use EXCEPT for this opration.

The PRODUCT operation generates a relation that contains all possible relations of tuples from all of the combinations. This is done in SQL by CROSS JOIN.

The JOIN generates a relation that contains all tuples that are a combination of two tuples with common values for one or more attributes. The NATURAL JOIN looks for matches based on the name. 

Relational algebra still defines the high-level steps of how to compute a query.  It tells the order to do those steps. The first example I do the join then the filtering, and the second one I do the filtering first, then the natural join. The efficiency can be vastly different. 

A better approach is to state the high-level answer that you want the DBMS to complete.

The relational model is independent of any query language implementation. SQL is the de-facto standard. 

To find where ice cube went solo

SELECT year FROM artists

       WHERE name = "Ice Cube";

The system can adapt and improve itself to the situation.

Databases are ubiquitous and are important. Relational algebra defines the primitives for processing queries on a relational database, and we see relational algebra again about query optimization and execution.