AF Unit 3. Creating a database. Tables and relationships
INDEX
1. Introduction 2.
2. Tables. Creating Tables 3.
2.1. Table definition area 3.
2.2. Fields 4.
2.3. Field properties 5.
2.4. Primary key 5.
2.5. Table data bar 8.
3. Relationships 11.
3.1. Types of database Relationships 11.
3.1.1 One to One relationships(1..1) 11.
3.1.2 One to Many (1..N) or Many to One relationships(N..1) 14.
3.1.3 Many (N) to Many(M) relationships(M..N) 15.
3.2. Foreign Key 16.
1. Introduction.
Databases are used to store information about objects or data.
You design tables for data, and define relationships between those tables.
Now, you will start creating the database file, adding the tables, and creating the relationships.
TO KNOW MORE/ Building LibreOffice tables
2. Tables. Create the Tables.
You have three different ways to create tables:
· through a table wizard: The wizard is only good for creating specific types of tables by picking them from the list of predefined field names.
· through design view: The design view is usually the best choice, and presents you with a list you fill in to create your table. We will use the design view to create our tables.
· through SQL statements: The SQL method requires you to know and understand the SQL language.
2.1 Table definition area.
This area is where you define the structure of the table.
Below you have the different parts of the screen:
· Field Name: Specifies the name of the data field. Note the database restrictions, such as the length of the name, special characters and spaces.
· Field type: Specifies the field type.
· Description: Specifies an optional description.
The row headers contain the following context menu commands:
· Cut: Cuts the selected row to the clipboard.
· Copy: Copies the selected row to the clipboard.
· Paste: Pastes the content of the clipboard.
· Delete: Deletes the selected row.
· Insert Rows: Inserts an empty row above the current row, if the table has not been saved. Inserts an empty row at the end of the table if the table has been saved.
· Primary Key: If this command has a check mark, the data field in this line is a primary key. By clicking the command you activate/deactivate the status. The command is only visible if the data source supports primary keys.
ACTIVITY: Web.TASK: Start to practice the window parts and actions vocabulary with this activity 2.0.
/ Window and actions vocabulary. Unit 3
2.2 Fields.
A table has a number fields set.
The fields have a name, a data type, a description and properties.
Open LibreOffice Base, and select Films DataBase. Follow the next steps:
· Select Tables from the Database panel on the left.
· In the Tasks panel, click on Create Table in Design View... to open the Design View dialog.
· Across the top you have labels for each of the elements of a field.
o Field Name: The name of the fields can't contain special characters such as the point, comma or a slash (backslash). The name can be blank spaces but we use the symbol (underscore _ ) to join words. For example, Id_film.
o Field Type: data type. When a field is of a data type it may not contain other data types. One field can only accept data corresponding to the specified field type. For example, it is not possible to enter a text in a number field.
o Description: The Description is optional for each field but it is useful for making notes about how a field is used. It will appear as a tip on column headings in the table.
· At the bottom, you see the Field Properties. This section will change according to the type of field.
2.3 Field properties.
It's necessary to enter properties for each selected data field. Depending on the database type, some input facilities may not be available.
· In the Default value box, enter the default contents for every new record. These contents can be edited later.
· In the Entry required box, specify whether or not the field may remain empty. Activate this option if this field cannot contain NULL values, i.e. the user must always enter data. Possible value (Yes-No).
· For the Length box, a combo box that provides the available choices may be shown that provides the available choices. Determine the length data can have in this field. The value will be corrected accordingly when it exceeds the maximum for this database.
· In the AutoValue. Choose if this field should contain AutoIncrement values. You can't enter data in fields of this type. An intrinsic value will be assigned to each new record automatically (by incrementing the previous record). Possible value (Yes-No).
· In the Format example. This is where you determine the output format of the data. The output format of the data is different if it's a number, an ID, a date, a time, a currency, etc.
HIGHLIGHTS
· NULL values represent missing unknown data.
· By default, a table column can hold NULL values.
· If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value.
· NULL values are treated differently from other values.
· NULL is used as a placeholder for unknown or inapplicable values
Note: It is not possible to compare NULL and 0; they are not equivalent.
2.4 Primary Key.
A table consists of a number of rows and each of which corresponds to a single database record. So, you have to identify all of these records. The solution is through the use of keys.
HIGHLIGHTSThe primary key of a relational table uniquely identifies each record in the table.
Here you have an example.
The student's first and last name would not be a good choice, as there is always the chance that more than one student might have the same name.
Imagine we have a Students table that contains a record for each student at a university.
The student's unique student number_Id would be a good choice for a primary key in the Students table.
Studentsnumber_Id / Last Name / First Name / Address / Number
12 / Bond / James / Main Street / 3
23 / Herrera / Carolina / Piccadilly circus / 33
45 / Bond / James / Madison Square garden / 58
Normally, it might be an attribute that is guaranteed to be unique (as for example the ID). If you don't have only attribute, it is possible to create a new attribute as key. Also you can have primary keys that are multiple attributes in combination of single attributes.
Every database table should have one or more columns designated as the primary key. The value this key holds should be unique for each record in the database.
Here you have another example.
If you have a table called Employees that contains personnel information for every employee, you have to select an appropriate primary key that uniquely identifies each employee. Your first thought might be to use the employee’s name.
But this doesn't work because it’s possible that you have two employees with the same name. A better choice might be to use a unique employee Id number that you assign to each employee when they’re hired.
Once you decide upon a primary key and set it up in the database, the database management system will enforce the uniqueness of the key. If you try to insert a record into a table with a primary key that duplicates an existing record, the insert will fail.
At the end LibreOffice Base has three ways of creating a primary key.
· Automatically add a primary key: Select to automatically add a primary key as an additional field.
· Use an existing field as a primary key: Select to use an existing field with unique values as a primary key.
· Define primary key by several fields: Select to create a primary key from a combination of several existing fields.
HIGHLIGHTS· The PRIMARY KEY constraint uniquely identifies each record in a database table.
· Primary keys must contain unique values.
· A primary key column cannot contain NULL values.
· Each table should have a primary key, and each table can have only ONE primary key.
· Set AutoValue to "Yes" so Base can automatically increment the value for each new record. The AutoValue datatype automatically increments the field each time you create a new record. While the number itself is meaningless, it provides a great way to reference an individual record in queries.
HIGHLIGHTSA primary key is necessary in every database table in order to uniquely identify each record.
The selection of a primary key is one of the most critical decisions you’ll make in the design of a new database. The most important constraint is that you must ensure that the selected key is unique.
So, it's important to make a good primary key because it's difficult to change it once you have finished the database. If you aren't able to find a candidate attribute, use an internally generated primary key.
The database management system can normally generate a unique identifier that has no meaning outside of the database system.
ACTIVITY: AF3-1. Complete my database. Working with tables. PART 1.TASK: So, now you will start creating a Table and practising what you have learnt. Follow the instructions and create a new table in Design view with the name Films. Here you have the steps, structure and specifications to create the Films table.
Film_Id
· In the first field, enter the name Film_Id.
· From the dropdown box in Field Type, select Integer.
· Add a description which is up to you.
· Under the field properties, change AutoValue to Yes.
· This will place a key icon in the box beside the field record showing it is the primary (or key) index. The value create the primary key, which uniquely identifies each record in the table.
Title
· In the second row, type Title for the name.
· Give this one a type VarChar.
· Again, the description is up to you.
· In the field properties, leave the length at 200.
Premier :The third field is premier with a type of Integer.
FilmType_Id : Field type: Integer.
Id_MediaType: Field type: Integer.
Id_Idiom: Field type: Integer.
Duration: Field type: Numeric.
Minimum_age: Field type: Numeric.
Price: Field type: Numeric.
PurchaseDate: Field type: Numeric.
At the end, Click on the save icon , and Base prompts you for a table name.
The table design should look like this:
2.5 Table data bar.
Use the Table Data bar to control the data view.
Table Data bar parts.
· First record: Go to the first record in the table.
· Previous record: Go to the previous record in the table.
· Next record: Go to the next record in the table.
· Last record: Go to the last record in the table.
· New record: Insert a new record into the current table When you click this button an empty row is added at the end of the table.
· Number of records: Display the number of records. For example, in this case record number 2 are selected in a table containing 4 records.
ACTIVITY: AF3-1. Complete my database. Working with tables. PART 2.TASK: So, now you type the Directors table and MediaType table
Create the Directors and MediaType Films table.
· For Directors, create three fields (data type in parenthesis):
o Director_Id, integer (AutoValue Yes) → Primary Key.
o Name, VarChar (length 50).
o Surname, Varchar (lengh 80).
The table design should look like this:
· For Media, create (data type in parenthesis) :
o MediaType_Id, integer (AutoValue Yes). → Primary Key.
o MediaType, VarChar (length 25).
The table design should look like this:
· For Idiom, create (data type in parentheses) :
o Idiom_Id, integer (AutoValue Yes). → Primary Key.
o Idiom, VarChar (length 25).
The table design should look like this:
3. Relationships.
When you create a database, you use separate tables for different types of entities. Some examples are: employees, departments, contracts, etc.
Sometimes you have to work with multiple tables that have relationships with each other.
Obviously you need to have relationships between these tables. For example, employees are employed at a department, employees have a contract, etc..
You have to represent these relationships in the database.
HIGHLIGHTS· A Relationship (in the context of databases) is a situation that exists between two relational database tables when one table has a foreign key that references the primary key of the other table.
· Relationships allow relational databases to split and store data in different tables.
3.1 Types of database Relationships.
There are several types of database relationships:
· One to One Relationships.
· One to Many or Many to One Relationships.
· Many to Many Relationships.
3.1.1 One to One Relationships (1..1).