Libre Office Database program

gb12345

InsideInsurance has made some good points.

In particular the facts that very few organisations use the open source office solutions and that only the very smallest businesses use Access.

If you are planning on working for a large organisation then unless you are planning on becoming a DBA it is very unlikely that you would be involved in the building of the databases. More likely is that you would occasionally be running queries against the data.

If this is the case then as II says, you will need to be looking at learning to use one of the front ends, such as Toad - which is available for Oracle, SQL Server, DB2 and a number of other databases. You will also need to become familiar with at least the basics of SQL. There are also subtle syntax differences between the different flavours of SQL that need to be considered.

System

InsideInsurance wrote: »

All but the smallest companies are dumping Access as quickly as money allows as it was never designed to be a commercial solution or be part of a business critical solution

What are you actually trying to achieve? What jobs are you hoping to get?



Start by reading about the basics of relational databases as this will be agnostic to the database used.

A database is effectively a series of spreadsheets. For each row in each spreadsheet you create a "Primary key" which is its unique identifier within that spreadsheet. They are called relational databases because in a second spreadsheet you again have a unique key but may cross reference the key from another spreadsheet (this is called a foreign key)

So in the example of students attending lessons as above, you have the first table/ spreadsheet that contains a list of all the students with StudentID as the key. Then for each lesson they attend you insert a new into the Lessons table and rather than having to type in all the students details again you simply enter their StudentID

This has a number of advantages, for one, if a student changes their name you only have to change it once in the Student table and not in every lesson they've taken. You can also do aggregate functions very easily and so can for example identify all students that have been to more than 5 lessons


If youre wanting to do anything "serious" with databases then you may want to think about using a more commercial database. All of the big players (Microsoft, Oracle, IBM etc) now have a free "express" version of their software that you can install and run on your home computer. They lack a few of the most advanced features and normally limited to a few million records but is plenty for learning.

Their disadvantage is they are a true database which is a backend only and do not have a front end built into them. There are plenty of free front ends you can get but they tend to have a steeper learning curve than Access

Don't write off Access, it still makes a very good front end for your databases on a back end SQL server or even a separate backend Access database.

If an Access database is written correctly then upscaling to SQL server is simple with the bonus that the user does not see any apparent change in their app

fenlander_uk

For business applications you should check out Microsoft SQL Server. There is an Express version of SQL Server 2008 that you can download and use for free. Versions of SQL Server for large business applications are very expensive, but you can learn the basics with Express. For web-based applications, the standard combination is MySQL in conjunction with PHP scripting on an Apache server. You can do a complete install of everything necessary to build a database-driven website on a Windows computer by downloading XAMPP.

Access is a fine tool, but it was always designed as a desktop (i.e. single user) application and it does not work well in a multi-user environment. Nowadays, many personal tasks that used to require a program like Access can be done with Excel instead.

bod1467

As above, you can download and install the SQL Server Express database.

http://www.microsoft.com/en-us/sqlserver/editions/2012-editions/express.aspx

MS Access will work fine as a front-end/interface. Database in LibreOffice may or may not - I don't know. You could also look at SQL Server Management Studio Express 2012

InsideInsurance

!!!!!! wrote: »

Don't write off Access, it still makes a very good front end for your databases on a back end SQL server or even a separate backend Access database.

If an Access database is written correctly then upscaling to SQL server is simple with the bonus that the user does not see any apparent change in their app

I wouldnt go as far as saying it makes a VERY good front end, it can become acceptable when you put the backend on SQL Server.

Personally, I would take an Access system, move the backend and keep the front (if the front was done well). I wouldn't build a brand new application with an Access front end and SQL back

I am certainly not writing it off and it is certainly still in active use, a client 2 years ago had the whole of about £4b of business being exclusively calculated by an access database... though when I left it had been fully replaced (by me)

stefano

So, for what I can understand, and my understanding is limited, most businesses work with databases, but the system is already set up, it's just a matter of using it.
Going back to my original exercise, I would have to set up the database from scratch, and each table is basically like a spreadsheet, and they can all relate to each other.
Am I on the right track?

gb12345

stefano wrote: »

So, for what I can understand, and my understanding is limited, most businesses work with databases, but the system is already set up, it's just a matter of using it.

Yes, unless you are specifically being employed to be involved in the set up of a new database, it is likely that you will only be using it.

stefano wrote: »

Going back to my original exercise, I would have to set up the database from scratch, and each table is basically like a spreadsheet, and they can all relate to each other.
Am I on the right track?

That is right - a series of related spreadsheets. See II's explanation earlier for how the tables relate to each other and the use of Primary and Foreign keys.

InsideInsurance

stefano wrote: »

So, for what I can understand, and my understanding is limited, most businesses work with databases, but the system is already set up, it's just a matter of using it.

And unless you are part of an MI/ reporting team or IT then using it is going to be just the same as using this website which is also backended onto a database.

For security the back end database should be totally shielded from employees and only the front end application is what you will see and it will be locked down so that you cannot write your own queries. If it wasnt then a malicious employee could write the command to delete the whole database.

Mr_Toad

My input, for what it's worth.

I've worked with commercial and military databases for decades and I'm still learning.

My field of expertise is Oracle but once you understand relational databases then the similarities are evident.

If you want to work with databases then forget Access and the Libre Office versions. There's nothing wrong with them as such but it's a bit like saying you want to get into the heavy haulage business and buying a Transit van.

They are easy to install and you can use them to learn about databases, I have an Access database that I use to catalog my book collection. I used Access because it enabled me to create the database in under 2 hours without any programming. Doing this wouldn't teach you much!

Have a look at My SQL it's open source and is becoming very popular with businesses who are looking to save on licensing costs for more expensive products.

You'll need something like My SQL Workbench which is an open source management GUI for My SQL. My SQL is now owned by Oracle.

If you're serious then you can download the free learning version of Oracle 11g Enterprise from here.

http://www.oracle.com/technetwork/products/express-edition/overview/index.html

From what I can gather this is a Windows version.

If you are really serious you should be aware that the majority of Oracle Databases are installed on Linux servers, in particular servers running OEL (Oracle Enterprise Linux). You can download a copy of the free version of this from here.

http://www.oracle.com/technetwork/community/developer-vm/index.html

This version is a prebuilt VM and requires a Virtual Machine to run so you will also need to download Oracle VirtualBox, also free. Once installed you want the Database App Development VM which includes the following;

• Oracle Linux 5
• Oracle Database 11g Release 2 Enterprise Edition
• Oracle TimesTen In-Memory Database Cache
• Oracle XML DB
• Oracle SQL Developer
• Oracle SQL Developer Data Modeler
• Oracle Application Express 4.1
• Oracle JDeveloper
• Video chats with engineers and experts

Not only can you use this to learn about databases but also the underlying Linux OS and how the database files are stored.



It might seem very daunting when you could just use Libre Office Base or Access but Oracle or My SQL are far more relevant to the business world and the Oracle user base is huge, global, massive and as such there are thousands of websites, tutorials, forums, documents, code examples and databases you can download to help you.


It's a huge learning curve but don't be put off, give it a go and good luck.

System

Mr_Toad wrote: »

If you are really serious you should be aware that the majority of Oracle Databases are installed on Linux servers, in particular servers running OEL (Oracle Enterprise Linux). You can download a copy of the free version of this from here.

http://www.oracle.com/technetwork/community/developer-vm/index.html

This version is a prebuilt VM and requires a Virtual Machine to run so you will also need to download Oracle VirtualBox, also free. Once installed you want the Database App Development VM which includes the following;

• Oracle Linux 5
• Oracle Database 11g Release 2 Enterprise Edition
• Oracle TimesTen In-Memory Database Cache
• Oracle XML DB
• Oracle SQL Developer
• Oracle SQL Developer Data Modeler
• Oracle Application Express 4.1
• Oracle JDeveloper
• Video chats with engineers and experts

Not only can you use this to learn about databases but also the underlying Linux OS and how the database files are stored.

Thanks for that. I wasn't aware that ready built VMs were available.