CMSI 486: Welcome to Week 04

This Week's Class Agenda

• 'Tables', Keys, and Normalization
• ACID and CRUD
• Constraints and Stored Procedures
• More SQL Language
• MySQL
• PostGreSQL

'Tables', Keys, and Normalization

OK, so officially, the entities in a Relational Database are called just that: entities, and the things that they have that are their parts [so to speak] are called attributes. However, in the vernacular [or the parlance of our times] we frequently refer to these things as tables, with each table having columns and rows much like a spreadsheet or a matrix. Within that context, there will be [or should be] one column of the table which is used to uniquely identify or differentiate any row from any other row in that table. This column [attribute] is called a key. There are several different types of keys as we saw on week 3.

In order to organize the database components to make things more effective, we go through a process known as normalization. This process helps to reduce data repetition within the database, and also helps improve the integrity of the data. There are several objectives of normalization, as specified by Edgar Codd when he invented the idea of relational databases in the early 1970's:

• Make the relational database model more informative to its users
• Reduce the need for restructuring the schema as new data types are brought in
• Reduce the number of undesirable dependencies within the schema and the data

Notice the word dependencies in the last item. This is one of the main concepts that we need to consider when working to normalize. A dependency is a relationship between two attributes within an entity, such that one of them can determine the other. For example, you might have a table that contains student information, with fields for student ID and student name. This would be a dependency, because each student has a unique student ID, while there may be multiple named Bartholemew Leznorsmorphels. The student name, then, depends on the student ID. However, the reverse is NOT true.

There are several types of dependencies:

• Trivial Functional: occurs when one attribute can be defined from a collection of attributes that contains that attribute. For example, the dependency {A,B} —> B is trivial. If you already know the value of B, you don't need the {A,B} to determine its value.
• Full Functional: occurs when you already meet the requirements for a functional dependency and the set of attributes on the left side of the functional dependency statement cannot be reduced any further. For example, {SSN, age} —> name is a functional dependency, because the SSN can determine the name. However, it is not a full functional dependency because you can remove age from the left side and still determine the name from the SSN. On the other hand, the dependency {Employee_number,Hours} —> weely_pay *is* a full funcitonal dependency, because you need both Employee_number and Hours values to determine how much the employee is paid for the week.
• Transitive: occur when there is an indirect relationship that causes a functional dependency. For example, A —> C is a transitive dependency when it is true only because *both* the dependencies A —> B and B —> C are true.
• Multivalued: occur when the presence of one or more rows in a table implies the presence of one or more other rows in that same table. For example, imagine a company that manufactures many models of a product, but always makes both black and white colors of each model. An entity that contains the model name, color and year made for each product has a multivalued dependency in that table, because if there is a row for a certain model name and year made in the color black, there *must* also be a similar row which will correspond to the white version of that same product name and year made.

Why do we care?

Dependencies are important because they are what we need to deal with to normalize the database to make it more efficient. The process is to break the data apart, reorganizing it based on the logical relationships between the entities/attributes, to minimize the duplication of the data within the schema. Why? Because duplicated data can waste storage space, for one thing, but [perhaps more importantly] any duplicate data can lead to inconsistencies in the data. [Think about the reasons we eschew magic numbers in our code.] The idea, though, is to be discriminating about the process – we try to strike a balance between a logical, well-normalized schema, and having queries that will execute quickly and that are easy to understand and maintain.

There are several types or levels of normal forms, as they are called, which are [usually] reached by getting rid of the dependencies in the schema [which we've identified above]. Normal forms use numbers for their levels [mostly], and each successively higher number is built on the previous levels. For example, if a database is in first normal form, there will be no redundant data in any row. This means two conditions are met: 1) every row has only one value in each column; and 2) there are no columns that duplicate information. An example of something that is NOT in first normal form would be a table with the columns name, first_name, last_name, address, and house_number because there is duplicate information in the columns of each row, and multiple values in the name and address columns. To normalize this table, you would remove the name column and change the name of the address column to be street_name. Frequently this can be done within the table.

1st

2nd

Next, a database is in second normal form if there is no repeating or redundant data across any rows in the same table. For example, consider a table that keeps track of people buying books, that has the columns userID, firstname, lastname, title, author, and pagecount. When Mabel Smith buys two books, now we have duplicate information in the table, because Mabel's name is in every row that corresponds to a book she bought. Further, if she buys multiple books by the same author, THAT information will also be duplicated. Reaching second normal form frequently means we need to split up the tables so that the information doesn't get repeated. In this case, we would make a table for users that would contain Mabel's information, a second table for books that can track what user bought what book, a third table for authors which contains author information, and even a fourth table for books that links the book title and number of pages to the author[s] that wrote the book.

Next up, a database is in third normal form if there is no data in any table that is *NOT* dependent on that table's primary key. If you have normalized to second normal form, there may actually be no need to do anything to get to third normal form, because every table will already be there. However, it is a good idea to check, just for insurance. For instance, assume you have started with a table that contains addresses. If you think of the addresses as NOT related to the user but as being stand-alone things, then you can separate the addresses from the user records in the database, and provide an index in the user table to match them up. Further, you caneven break the address components out into separate tables, because when you think about it, the street address depends upon the zip code, the zip code relies on the city, and the city depends on the state. A database in third normal form will actually have separate tables for each of these entities.

3rd

There are several more normal forms: BCNF comes after third, and fourth comes after that. There is even a fifth normal form. However in practice, these last three are not used too often, partly because of the law of diminishing returns

. You may find out that in trying to achieve BCNF, you spend a large amount of time re-arranging things, only to find that you really haven't improved the processing or structure by all that much. So, part of what you will learn from experience, is when you should *STOP* normalizing and go with what you have.

ACID and CRUD

These are a couple of acronyms that you should remember. Remeber them because of the importance of the database concepts they represent, NOT because they are cute acronnyms. They reflect key elements that are part of every database management system [DBMS].

ACID stands for:

• Atomicity: No this is not a place in North Korea. This is the idea that an operation on the database must happen in its entirety or not at all. It can be thought of as the all or nothing rule. This is the part of database operations which involves transaction processing to ensure that the data in the database remains consistent with every operation.
• Consistency: this is the concept that ensures the data in the database is valid, not only within the database itself, but also any data that is allowed into the database. When a transaction results in invalid data, the database reverts to its previous state, which abides by all customary rules and constraints.
• Isolation: this is the idea that transactions are securely and independently processed at the same time without interference, but it does not ensure the order of transactions. This is the same idea I have mentioned before about my wife and myself trying to withdraw our last $20 from two different ATM's. Isolation of the transactions ensures that one of us must wait for the completion of the transaction of the first one to get in.
• Durability: In the above example, user Beej may withdraw $20 ONLY after user Jené’s transaction is completed and is successfully updated in the database. If the system fails before Jené’s transaction is logged in the database, Jené cannot withdraw any money, and the account returns to its previous consistent state, which is considered durable because it will not have changed since the operation failed.

CRUD stands for the four basic operations that can be performed in a database:

• Create: This operation ADDS data into the database. The SQL operation that performs this function is the INSERT keyword.
• Retrieve: This operation pulls data OUT of the database. This is the SELECT keyword.
• Update: The UPDATE keyword modifies data in one or more records of the database.
• Delete: Finally, the delete operation is performed by the DELETE keyword in SQL.

Constraints and Stored Procedures

Constraints

Constraints in database parlance are just that: things that must be taken into account when performing any operation on the data, which may limit or even prohibit the operation to take place. In some database engines, constrains also are used [required] to designate the primary key for a table. There are several types of constraints. Here are a few that are noteworthy:

• NOT NULL: this constraint ensures that there MUST BE A VALUE in this column for every record in the table; can be applied to any attribute in a table
• PRIMARY KEY: this constraint tells the database engine that this column is to be used as the primary key for the table, and hence will uniquely identify any row in the table.; it is actually a combination of the two keywords NOT NULL and UNIQUE [see below]. This can be done in several ways, differing syntaxes for different database engines
• FOREIGN KEY: this constraint uniquely identifies a record in ANOTHER table in the database; we've seen this in class already
• CHECK: this constraint tells the database engine to verify that all values in a column meet a specific condition;
• UNIQUE: ensures that every value in a column of the table is unique from every other value in that column; can be done in two ways, either by adding it to the declaration as Index int NOT NULL UNIQUE or after the declarations as UNIQUE Index, or even using the CONSTRAINT keyword, as CONSTRAINT constr01 UNIQUE (Index) [where constr01 is the constraint name]
• DEFAULT: sets a default value for a column for when an INSERT statement doesn't specify one
• INDEX: constraint to create an index on the database tables, so that queries can perform operations more quickly and efficiently
• ADDING OR DROPPING A CONSTRAINT: Adding is pretty straightforward, just add the constraint. Different engines have slightly different syntax for this process, and some even allow more than one way to do it in their version of SQL. Dropping a constraint, however, can be more complex. Depending on what the constraint is linked to, meaning perhaps some other table in the database, you may have to remove something from a different table using an ALTER TABLE command, prior to removing the constraint.

Stored Procedures

Stored Procedures are a way of programming repetitive or frequently-used database operations so that they can be done by a much simpler query that if the query was being written and executed by the underlying application. For example, instead of having the query…

      SELECT AMT_AssetTable.AssetID
            ,AMT_AssetTable.AsBuiltRevision
            ,AMT_AssetTable.AssetType
            ,AMT_AssetTable.PreviousRecord
            ,AMT_AssetTable.SerialNumber
            ,AMT_AssetTable.LastCalDate
            ,AMT_AssetTable.CalCycleDurationDays
            ,AMT_AssetTable.LastPMDate
            ,AMT_AssetTable.PMCycleDurationDays
            ,AMT_AssetTable.LocationBldgRm
            ,AMT_AssetTable.AssetDescription
            ,AMT_StatusValuesTable.StatusValue
            ,AMT_HistoryLogTable.HistoryLogID
            ,AMT_HistoryLogTable.BEMSID
            ,AMT_HistoryLogTable.EntryDate
            ,AMT_HistoryLogTable.EntryType
            ,AMT_HistoryLogTable.Title
      FROM AMT_AssetTable
      INNER JOIN AMT_StatusValuesTable
      ON AMT_AssetTable.StatusIndexID = AMT_StatusValuesTable.StatusIndexID
      INNER JOIN AMT_HistoryLogTable
      ON AMT_AssetTable.AssetID = AMT_HistoryLogTable.AssetID
      WHERE AMT_AssetTable.AssetID LIKE 'G123461';
            

Code from Microsoft SQL Server 2014

…you could have the following query…

        EXEC AMT_QueryAssetInfoWithStatusAndHistory 'G123456';
            

Not only does this make your application code much cleaner and easier to read/maintain, it also improves the security of your database, because you can grant stored procedure permissions to specific users, and keep everyone else from executing them.

Stored procedures are not available in all database engines. Some, like older versions of Microsoft Access, have no such facility. Others provide the ability, but may call it by another name. But no matter what it's called or how it's implemented, it provides you with the ability to write functions, include if/then statements and loops, and much more.

The MySQL Database Engine

MySQL is an open source database management system. It is highly popular because of its high reliability, ease of use and high performance. MySQL is used for many latest applications that are built on Apache, Linux, Perl/PHP etc. Many popular organizations such as Google, Alcatel Lucent, Facebook, Zappos and Adobe rely on this database management system.

MySQL can run on more than twenty platforms that include MAC OS, Windows, Linux, IBM AIX, HP-UX and provides much flexibility. A wide variety of database tools, services, training and support is provided by MySQL database system. MySQL comes in several different editions:

• Enterprise Edition: This edition provides OLTP [On Line Transaction Processing] database applications and also delivers high quality performance. Its capabilities include rollback, row level locking, full commit and crash recovery. To manage as well as improve performance of large database systems, database partitioning is also allowed by this edition. The Enterprise Edition includes MySQL Enterprise Backup, Enterprise Monitor, Query Analyzer and MySQL WorkBench.
• Standard Edition: This edition also provides OLTP applications as well as high performance. The standard edition also includes InnoDB that makes it ACID compliant and a transaction-safe database. To deliver scalable applications and high performance, replication is also allowed by this database system.
• Classic Edition: It is the ideal database system for OEMs, VARs and ISVs that use MyISAM storage engine to develop read intensive applications. The classic edition is easy to use and requires low administration. However, this edition is only for VARs, ISVs and OEMs. One can easily upgrade to more advanced editions from the classic edition.

The SQL Server Database Engine

SQL Server is a RDBMS (Relational Database Management System) developed by Microsoft. This system works on Transact-SQL which is a set of programming extensions from Microsoft and Sybase. T-SQL adds other features that include error and exception handling, transaction control, declared variables and row processing. However, Sybase developed the original SQL Server back in 1980s. The final version was called SQL Server 4.2 that was developed in collaboration with Ashton-Tate, Sybase and Microsoft for OS/2.

SQL Server 2005 was launched in the month of November 2005. This version provided enhanced reliability, flexibility, security and scalability to database applications. The features provided by SQL Server are:

• Database Mirroring – Using SQL Server, one can set up automatic failure recovery in case of a standby server.
• Online Indexing Operations – SQL Server also allows concurrent modifications such as inserts, deletes and updates.
• Management Studio – Management studio is a set of tools that allows users to deploy, troubleshoot and develop SQL server databases.
• Data Partitioning – Efficient management of indexes and large tables is provided with data partitioning which is enhanced with index portioning and native tables.

For Businesses, SQL server also provides Integration Services, Reporting Services, Data mining, key performance indicators, clustering support, proactive caching and repot building. It also provides integration with Microsoft Office tools [of course].

The PostGreSQL [or Postgres] Database Engine

PostgreSQL is an ORDBMS or Object Relational Database Management. It was developed at the Berkeley Computer Science Department of University of California. It is also an open source database management system and comes from the original Berkeley code. A large part of SQL standard is supported by PostgreSQL and it offers many features such as transactional integrity, triggers, foreign keys, multiversion concurrency control, complex queries and views. The user can extend the PostgreSQL by adding new index methods, procedural languages, functions, operators, data types and aggregate functions. As it is open source it can be modified, distributed or used by everyone free of cost for academic, commercial or provide use.

The differences between PostGres and MySQL are as follows:

• PostgreSQL is feature–rich as compared to MYSQL as it provides stored procedures, views, cursors, and sub-queries that are not supported by stable version of MySQL.
• There is s large community to support MySQL as it is used more as compared to PostgreSQL. A larger number of resources such as books, internet are available to assist users of MySQL, whereas this not necessarily the case with PostgreSQl.
• MySQL is considered to be faster than PostgreSQL as the former one was designed in such a way to be speedy, whereas PostgreSQL was designed as a fully featured database software.
• MySQL uses the GNU GPL whereas PostgreSQL is released under the BSD license.

Coming up…

• Transaction processing
• Availability
• Data Integrity