Alternatives to Microsoft Access on the Mac File Maker Pro. FileMaker is probably the best known database application for the Mac. It has a feature set comparable to Microsoft Access, but with a strong focus on forms (layouts) as the primary way of accessing databases. May 21, 2001 As Mac users confront the geeky realities associated with Unix as the core of Mac OS X, they may not be aware of their newly acquired capability to run powerful relational database software. In part one of this article, I discussed the basics of how relational databases work (see TidBITS-580 ). The move to the Unix-based OS X brought a lot of new database options to the Mac. Popular open source relational databases like MySQL and PostgreSQL can run happily in the background on your Mac. While FileMaker has a nice GUI and the open source databases are inherently command-line applications, there are some nice GUI clients that ease you. There was a time, when we were all new to personal computers, that we loved to build and use databases. A computer is the perfect tool for that kind of record keeping. However, in time, we all. KeePass Password Safe is a free, open source, lightweight, and easy-to-use password manager for Windows, Linux and Mac OS X, with ports for Android, iPhone/iPad and other mobile devices. With so many passwords to remember and the need to vary passwords to protect your valuable data, it’s nice to have KeePass to manage your passwords in a.
Love it or hate it, Mac OS X ships with Unix under its hood. As a user, I worry the Mac experience could degrade into editing brittle text configuration files and typing obscure and unforgiving commands. As a programmer, I’m overjoyed because we Mac users now have access to certain industrial-strength software. This is the type of software that drives Fortune 500 companies, calculates extremely complex chemical reactions, and generates the movies we watch. Since I don’t make movies and I’m not a scientist, I’m most interested in the business side of this software. In particular, I’m interested in relational databases.
On the classic Mac OS, FileMaker Pro and 4D dominate the database scene. I’m partial to the newcomer Valentina, while other folks swear by Helix RADE or Frontier [for context, see Matt Neuburg’s articles on these last two. -Adam]. Unfortunately, none of these databases qualify as 'industrial strength.' Don’t get me wrong: they do their jobs well, but they lack the qualities that many database professionals crave: SQL and ACID. But before we dive into those two acronyms, let me introduce you to the relational database model. In the next installment of this article, we’ll look at some of the relational databases that become available to Macintosh users under Mac OS X.
<https://tidbits.com/getbits.acgi?tbart=05814>
<https://tidbits.com/getbits.acgi?tbart=05351>
Relational Databases — Although there are many different types of databases (free form, hierarchical, network and object relational to name a few), the relational database model is the favorite of businesses.
Introduced by mathematician Dr. E. F. Codd in the early 1970s, the model is simple (though most books like to obscure it behind mathematical jargon). Imagine a spreadsheet where you keep a list of your customers:
Notice that you have three columns of information, with each column dedicated to holding a certain nugget of information. You have four customers, each represented by a distinct row.
The relational model calls this data layout a 'table;' a relational database contains one or more tables. Although similar in concept to a spreadsheet, a table is different in that each column can hold only one type of data. For example, it would be illegal to put text into the 'CUSTOMER_ID' column – it can hold only numbers. Also, unlike a spreadsheet, the relational model doesn’t allow cells to hold formulas (each cell must stand alone and can’t refer to another cell).
If you’re used to thinking of databases as a bunch of index cards (as in FileMaker), here’s a helpful guide: a table is analogous to a stack of cards, a row is analogous to a single card (a record), and a column represents a single field on a card.
Now, let’s say you want to keep track of your customers’ purchases. You whip up another table:
You can add rows to this table as customers make purchases. Each purchase has a 'CUSTOMER_ID' column, which can be used to relate a purchase with a customer. For instance, in this table we know that Purchase #1 was made by Customer #1.
Let’s explore how these relationships can work. Given a PURCHASE_ID, it’s easy for us to retrieve the purchaser’s email address. Suppose we’re interested in the fourth purchase; its CUSTOMER_ID field is set to 3. By scanning our customer list for customers with an ID set to 3, we discover a Michael Spindler, email address <[email protected]>.
Relationships can also work the other way: given a CUSTOMER_ID, we can work backwards to compile a list of purchases made. Let’s start off with Steve Jobs, who has a CUSTOMER_ID of 1. Now we scan our purchase list, where we discover three rows with matching CUSTOMER_ID fields: purchases 1, 3, and 5.
By following good design rules when setting up your tables, your database will have little or no duplicate data and will accept only valid data. Another perk is that nothing in your database is tied to a specific program – if you outgrow your current database program, you can move to another without much effort.
Finally, relational databases are very scalable. You can start off on a $400 PC running Linux and migrate the same database to $400,000 IBM big iron. The only difference is speed and reliability. You can see why businesses like relational databases.
Now that you know the general idea about relational databases, we can decode the SQL and ACID acronyms I mentioned earlier.
SQL — SQL stands for Structured Query Language, and is correctly pronounced by spelling out its letters ('ess cue el'). Some folks pronounce it 'sequel,' however this is incorrect: there was a language named SEQUEL that was SQL’s forerunner. A minority pronounce SQL as 'squeal,' which never truly caught on, probably for the same reason SCSI was never pronounced 'sexy' – it sounded silly in the boardroom. ('We’ll need to attach a sexy drive to our squeal server.' Sure you’re going to say that to the big boss.)
SQL is the standard language used to communicate with relational databases. Because it’s actually a full language, users, developers, and software programs can use it to create, alter, and delete tables and the rows of information they contain. The use of a standard language opens relational databases up to a wide variety of interfaces and access methods that would have to be written from scratch individually for other types of databases. That accounts for one of the limitations of traditional Macintosh databases.
Like HTML, SQL is a declarative language. It contains no variables or loops, and is easy to learn even for the non-programmer. With a non-declarative language, you must spell out the steps necessary to complete a task. A declarative language, on the other hand, simply allows you to state the desired end-result. SQL is an older language, and although it is case insensitive, convention capitalizes almost everything. Here’s a valid SQL statement to create the customer table discussed above:
This command creates a table named CUSTOMER with three columns: CUSTOMER_ID, NAME and EMAIL. The CUSTOMER_ID column is defined to hold a number, while the NAME and EMAIL columns are respectively defined to contain 100 and 200 characters.
It’s easy to enter information into a table using the INSERT verb:
Space prohibits me from detailing the syntax for altering and deleting rows and tables, but it’s just as easy as creating and inserting tables and rows.
The key SQL verb is SELECT, which allows you to access and filter information from the database. For example, we can look up a customer’s email address like so:
Here’s the result you get back:
The result takes the form of a table. Granted, in this case it’s a table with only one column, but it’s a table nonetheless.
As a final example, given a name, the following query displays all of a customer’s purchases. It’s okay if you don’t understand it, I just wanted to show off a little of what you can do with SQL.
Relational Database Software For Windows
Here’s the result:
The ACID Test — ACID stands for 'Atomicity, Consistency, Isolation, and Durability.' These are the features that separate the pros (Oracle, PostgreSQL) from the minor leaguers (FileMaker Pro, 4D). When your business rides on the quality of your information, ACID is the feature set that helps you sleep at night.
Atomicity (pronounced 'atom-ih-sit-ee') comes from the word atom and its original meaning: that which is indivisible. In a database, that means that multiple operations are all bundled up into one indivisible transaction. Either all of the transaction’s operations take place, or none of them do. This helps to ensure the database is in a valid state at all times.
Consistency is the principle that only operations that meet all the database’s validity constraints are allowed. The end effect of this is that illegal operations aren’t allowed, whether they are external (perhaps users enter invalid data) or internal (perhaps a disk fills up and a required row can’t be added).
Best Relational Database Software
In this wild Web world, databases have to deal with multiple concurrent modifications. But what happens when Alice’s transaction is modifying the table that Bob’s transaction is reading? Isolation ensures that Bob’s transaction sees the table as it existed before Alice’s transaction started or after it completed, but never the intermediate state.
Finally, Durability is the principle that once a transaction is completed, a mere system crash won’t wipe it out. In the real world, this means that transactions aren’t considered completed until the all the information has been written to a disk.
What’s Available? In the next installment of this article, I’ll cover the merits of a handful of database applications that can be run under Mac OS X, such as MySQL, FrontBase, and speculation about Oracle’s possible entry into the Mac field.
[Jonathan 'Wolf' Rentzsch is the embodiment of Red Shed Software and runs a monthly Mac programmer get-together in Northwest Illinois.]
Collate and Manage data conveniently with Best Database management app for mac – Managing hefty database could be a daunting task if you don’t follow a proper methodology. However, if you are a mac user then there are a plethora of options which makes things expedient for you.
There are several third party database management applications for mac which are not only easy to install but are convenient to use. If you are in a hunt for some of the best database management app for mac then you must know about the following apps.
Ninox for mac
Ninox for mac is an easy to use flat-file database system through which the users can create or manage heavy databases in minutes. This database management app for mac has excellent features like grouping, calculations, multiple filters and table relations. Its ability to sync through iCloud makes the data accessible from multiple devices.
Sequel Pro for mac: Best database management app for mac
Sequel pro for mac is an excellent database management app for mac users who need to work upon MySQL databases. This application allows the user to choose their preferred language which includes Ruby, PHP, BASH or Python.
It has the ability to automatically highlight the custom queries. The user can also customize the query editor font. One of the most magnificent facets of this application is that the user can easily set up a connection with the local MySQL server on their mac device which further makes it one of the best database management app for mac.
Querious for mac
With this DB application for mac working on vast databases has become an easy task. Some of the best features of this application are its ability to view, search, edit, export, import, configure and monitor the MySQL databases.
Querious for mac allows the user to open raw CSV or Tab files and further lets them split, join, add, remove, reorder and process various rows and columns. This database management app for mac doesn’t only deal with managing the data. It also enables you to check the status of a server such as the number of connections, bandwidth used and the queries running on the server.
iDatabase for mac:
iDatabase for mac is a convenient database application for the mac users who wish to manage multiple data which may include their expenses, projects, membership lists etc. The application presents around 22 ready to access database templates through which the users can manage any kind or type of information.
Some of the popular templates include inventory, movie catalog, accounts, projects, records, expenses, events, vehicle management amongst others. iDatabase for mac can be easily synchronized through WiFi for iPhone.
Valentina Studio for mac:
Valentina studio for mac has excellent features which include database schema editor through which the user can easily create and modify schema objects of supported databases which include a table, views, fields, enums etc.
With the help of diagram editor, the user can design new diagrams and explore various existing databases visually. Some of the other convenient features include SQL editor, Query Builder, Related data editor amongst others.
The mac users surely have umpteen options to choose from when it comes to the best database management app for mac. However, the above-mentioned mac database management apps are considered to be the best database management apps for mac. Expedient features and easy user interface make database management an effortless procedure for the mac users.