Need Help with OpenOffice

[OpenOffice9000]

So I typed a long message but it got deleted because the website logged me out...

TLDR: I need help with my OpenOffice file for my assignment creating relational databases using tables, queries, and forms.

The assignment itself:

The following is a list of essential data items to be recorded. As noted above, additional fields may be required as keys for the tables you will create from these fields.

date of rental

due date

media item title

media item category

category description

category code

customer ID#

customer lastname

customer firstname

customer address

If only a single table were made from all the fields listed above, the result would be a system in which every rental would have to include duplicate customer and media item details. In addition to wasting input time duplicating data, such a system would also be highly prone to error, because any change to customer data (a change of address, for example) would also result in different records showing different content for the same fields, unless every historical record for that customer was updated.

The relational database model was designed to solve those problems by identifying key data entities, separating them into their own tables, and relating the tables using foreign keys. (The foreign key is usually the primary key from one table, placed in one or more other tables to create linkages.)

This allows for customer details, for example, to be recorded once, in a table reserved for that purpose, and referenced in other tables by a unique identifier (key), such as Customer ID#.

Your task is to create a simple relational database from the field list above. The required tables are Customers, Media, Categories, and Rentals; each of which should be properly provided with a primary key, and each of which should be related to other tables as required.

All four database tables should be populated with records as follows:

Customers—at least 10

Categories—three records, one describing each of the media types: DVD, Blu-Ray, or Game

Media—at least 30 records, representing a selection of DVD movies, Blu-Ray movies, and Games.

Rentals—at least 20 records.

---

I have the tables created but am struggling to figure out how to create any query or forms. I am not even sure what the assignment is asking me to query.

The tables need to be related in a way where say if you look up customerID, it will show you their rentals, or customer information. If you look up media, it should be able to show you which categories they belong. I know what needs to be related, I just have no idea how to apply it into a query or form. How do I create a query or form to make those relations?

Another problem I am having is the tables won't let me create relationships. I can create a line, save it, but then when I reopen it, the relationship line disappears. Here is what I have at the moment:

https://i.imgur.com/H48JOTH.png

The tables itself are filled with information already. How do I create the queries or forms? Thank you.

[UnklDonald418]

There is documentation available for download from
https://documentation.libreoffice.org/e ... mentation/
Chapter 4 of the Base Handbook covers Forms and chapter 5 covers Queries.
Queries are used to retrieve data from database tables. For instance a query like

Code: Select all

SELECT * FROM "Customers" ORDER BY "LastName" ASC, "FirstName" ASC;

should display a list of customers in alphabetical order by LastName and then FirstName.

I think you are confused by

The foreign key is usually the primary key from one table, placed in one or more other tables to create linkages

A Foreign Key is not a Primary Key, it simply stores a value matching a Primary Key value from another table. That match allows the records in two tables to be linked to each other.
So, in the Rentals table Rentals ID should be the only Primary key. Remove the Primary Key designation for Customer ID because it is a Foreign Key referencing the Primary Key in the Customers table.
Likewise, in the Media table, remove the Primary Key designation from Category Code because it is a Foreign Key referencing the Primary Key in the Categories table.
As long as the linked fields are of the same data type you should be able to establish and save relationships between your tables.

[OpenOffice9000]

I know how to create the forms/queries using the wizard. I am confused about what stuff I am actually having to link. I understand the concept of the query but I just don't know how it pertains to my assignment or what I need to do.

Lets use rental and customer for example. When I look up RentalID, I want it to show the customer ID, customer first and last name, customer address, rental date, due date. How would I do something like that?

[UnklDonald418]

I already pointed out the issues with foreign keys in Rentals and Media tables. There is another issue with the Media table where there is a duplication of data. Do you see it?
I am uploading something similar to what you are trying to do. It includes the relationships I believe you were trying to establish.

Demo22Rentals.odb

(31.88 KiB) Downloaded 198 times

There are two forms:
RentalEntryForm uses a MainForm SubForm combination to display of all Rentals on the SubForm table for the selected Customer in the MainForm table. This form can also be used to enter new customers, and add new rental transactions. MediaEntryForm displays a list of the media items in inventory, and also allows entry of new items. One feature to note on this form is the use of a ListBox to display/select a category.
There is one query that demonstrates

show the customer ID, customer first and last name, customer address, rental date, due date.

If you right click on the QueryRentalToCust and select Edit, the GUI dialog will display the query structure.
Or, select Edit in SQL View to see the query in SQL, the language of relational databases. You didn't mention, but as part of this class are you also learning SQL?
Other useful queries might be to generate a list of items that are still checked out, or maybe a list of past due items.

There is still a missing link that would allow all the tables to be linked. Do you see what needs to be done?

[OpenOffice9000]

Hey mate. Your response definitely helped a bit. I sent you a PM and if you could take a look when you have some time that would be great thanks.