Would it be accurate to say that everything that Excel does Access can do and more, and that everything Access does SQL Server can do and more? And where does Oracle come into the equation?
Hmm, I don’t think so. Excel, Access and SQL Server are all different beasts and are used for different things. Excel is for spreadsheets and graphs. Some people try to keep databases in Excel, but they are usually way to complex and get too complicated after a while. Access is for small to medium sized databases, and it is an entire “system”. Using VBA (Visual Basic for Applications) it encompasses reporting, input (forms) and even data-based web pages.
SQL Server (and here is where your statement really breaks down) is “just” the database. No reporting. No forms. Everything has to be done either programatically or with outside software (such as Access, or Crystal Reports). In this case Access can do more than SQL, but SQL can do more of it!
Oracle is just another “type” of large backend database like MSSQL Server.
I guess my question was relating more to the server side (if that can even apply in Excel).
Excel is a spreadsheet program. It is great for running numbers and calculating totals. I personally would not use Excel as a database, unless you are working with a flat file (not a relational model). Excel is better suited for reporting on data that you extract from a database source (such as Access, SQL Server or Oracle) than as a database itself.
Access is a relational database product. It is a desktop program (meaning that you don’t need expensive servers to run it.) For most of your everyday database tasks, Access should server fine. Access does have it’s limitations, however. There is a limit to the size of the database. In addition, while you can run Access on a network and support multiple users, it cannot do so beyond a handful of users. Once you have more than a few people hitting the database at once, it’s time to move on.
SQL Server and Oracle are two different enterprise relational database programs. (From this point, I’ll speak from SQL Server, which I know well). SQL Server cannot do everything Access can do (it wasn’t meant to). For example, Access let’s you build front end logic (user interfaces, forms, etc) which allow a user to access and modify data. SQL Server (and Oracle) are designed to be back end tools. A developer would typically create the physical database on SQL Server and create user interfaces and business objects in other languages (C, C++, Visual Basic, etc.)
That’s just a brief overview. If you want more info, please feel free to email me or post here and I’ll try to answer your questions.
Zev Steinhardt
What exactly is a relational database?
A relational database is a database which is made out of relations… which is just a fancy name for tables.
OK, bit more detail needed. A relation (table) is made up of tuples (rows) and attributes (columns). Each tuple has the same number of attributes, in the same order as every other tuple. Other than that, tuples are not ordered (you can shuffle all the lines in the table without losing information), and neiter are attributes (you can move all the columns around without losing any information as well).
So there you are. Most (if not all - I’ve never come across an exception) relational database software insists that, for each tuple, you only have one value per attribute. This is called First Normal Form, and is only the start of an exciting journey called database normalisation, which has all sorts of benefits in terms of optimizing information storage and making you grind your teeth. Do you want to know about normalization? i can take you as far as Third Normal Form off the top of my head; after that, I’ll need to look in the book.
(The book, by the way, being C.J. Date’s An Introduction to Database Systems, volume 1).
(and do you also want to know why I spell “normalization” two different ways? Because I’m a free spirit, that’s why!)
Database administrator here.
As far as Microsoft SQL Server vs. Oracle:
Oracle is used for large enterprise databases, e.g. an airline reservations database. SQL Server is used more for small to medium-size systems, an area in which SQL Server and Oracle are locked into a bitter struggle for domination.
SQL Server is probably easier to setup since many people have a familiarity with Windows due to their experience with PCs. But Oracle has many more options for configuration and tuning, and of course runs on a wide variety of platforms, whereas SQL Server is a Windows only database.
In plain English, with examples:
A relational database is one in which each record in one file is (or can be) hooked up to one or more records in a totally different file based on a field that they share in common.
Let’s say you have one file called “Customers”; visualize it more or less like an Excel spreadsheet, with columns for First Name, Last Name, Address, etc.
Now, let’s say you have a second file, called “Orders”; think of this as being like a second spreadsheet, except that each line is a separate order for a product. Each order was, of course, placed by a customer. If each customer had a unique “customer number”, (which would be one column in the Customers sheet), and each Order line also had a customer number column, each customer would be RELATED to however many order records in the Orders file that had the same customer number, which could be anywhere from zero (i.e., you’ve got the customer in your database but no orders have been placed by that customer yet) to hundreds (a customer that places many many orders).
Now let’s add a third file, called “Products”. Each order is not only an order placed by a customer, it is also an order for a product. So our Products file will have a column (field) called “SKU Number” and each order record in the Orders file will also have a SKU Number field. Of course, each order could be an order for one or SEVERAL products, so we could set it up to be a field that can contain multiple entries or we could set up a fourth file, “Order Items”, in which each record is an item of an order (and related by order number to Orders).
From our Order database file, we want to create an Order Confirmation form to FAX or otherwise send to the customer. We want it to display customer data (which is in Customers), product information (from Products), and subtotals, tax, total number of items ordered, shipping and handling, and grand total, which is information best conceptualized at the Orders level (although the individual prices are in Products). No problem, just drag the related fields onto your order form and define Calculation fields to represent the total of related-products’ prices, apply taxes, and so on.
From our Customers database file, it would be useful to see how many orders placed during any given date range, although the orders themselves are kept track of in another file. No problem, define a field in Customers equal to the sum of related records in Orders.
From Products, we want to know which of our items are moving rapidly, so it is nice to have each product record display the sum of orders for that product, so we define a calc field as the sum of order records related to this file by the same SKU number.
From this beginning point, we can add inventory tracking, shipment tracking, accounts billable, and so on; you can see how different types of data link up with other types based on what they have in common. And once you set up those links, the related data is right at your fingertips.
Steve Wright:
Me:
:ahem: Well, you can have multiple values in FileMaker, although not in all types of fields, only fields formatted as “text”. A text field with a hard return and additional data afterwards can be treated as a single field with multiple values, and the definition of “related data” set up through that field as relationship-key will see records as related if they match ANY of the substrings separated by hard return.
In most database systems, you would not have this option and, in the example I gave above, you would have no choice but to create a merge file and view the data through a portal.