Introduction to Databases and Entity-Relationship Model
Introduction to Databases
Definitions
Database: A collection of interrelated data that can be managed jointly.
Entity-Relationship Model: A theoretical approach to model data from a database using diagrams.
Entity: An element of reality with associated data that exists independently. Entities are usually identified with names, for example, customers, items, rooms, students, etc.
Relationship: A connection between at least two entities. Related entities must exist before the relationship can be established. Relationships are often identified by verbs: reservations (room for customers), purchase (of products by customers), a student takes courses (the relationship would be “carried”), etc.
Table: Relationships and entities are modeled on a computer with tables. Each attribute of the entities and relationships is modeled by “fields” or “columns.” The data from each entity or relationship is stored in a row or record in the table. A table can have many records, usually limited by the computer’s hard disk capacity. To distinguish each record from others, primary keys are defined.
Primary Key
The primary key of a table is a field or fields that uniquely identify each record or row in the table. This means each record in the table has a different value in the field(s) that constitute the primary key. For example, each Spanish citizen has a unique ID number that should never be repeated. Another example of a primary key is the student ID number in a student center. There may be several students with the same name, but they will not have the same student ID number. Examples of incorrect keys are: name + last name (can be repeated for different people), product code in a sales table (because the same item can be sold multiple times to different people), date of sale (even with hours, minutes, and seconds, as multiple sales can occur simultaneously).
Typically, all tables have a primary key, which allows us to identify individual records to retrieve, modify, or delete their data.
Foreign Key
A foreign key is a field or fields defined in a table that are the primary key in another table. This is why we use the term “foreign.”
Comments on Normalization
It is not recommended to go beyond the second normal form, as doing so implies greater data redundancy in tables, which can become excessive.
Implementing Entity-Relationship Model Relations in a Database
Relationships, based on the number of entities they connect, can be 1 to 1, 1 to many (1 to ‘n’), or many to many (‘n’ to ‘m’).
1 to 1 and 1 to many relationships are modeled by adding the primary key of one entity as a foreign key in the second entity. Example: A hotel customer (primary key ID) may have made several reservations on different dates. By adding the ID field as a foreign key in the reservations table, we implement a 1 to many relationship between the client and reservations.
To implement many-to-many relationships (‘n’ to ‘m’), it is necessary to create a new table that includes the primary keys of the related tables. For example, consider items in a supermarket and the customers who buy them. An item can be purchased by many customers, and a customer can purchase many items. In these cases, you need to create an “Orders” table, which includes the customer’s primary key (e.g., ID) and the item’s primary key (e.g., product code). To normalize these tables, they should be divided into an “Order Headers” table with the order number as the primary key, plus the date, total order amount, and customer ID, and an “Order Lines” table with the order number as a foreign key, plus the product code, quantity, unit price, total price, and line number on the receipt or invoice.
Comments on Access
The data types for fields or columns in Access are:
- Text
- Integer
- AutoNumber (takes values from 1, 2, etc.)
- Number with decimals
- Currency
- Date
- Time
- Memo (used to store binary objects such as photos, music, video, large text fields, etc.)
AutoNumber Field
The AutoNumber field automatically takes integer values starting from 1. For this reason, it is often used in the primary keys of tables. When inserting a record into a table, you cannot assign a value to an AutoNumber field, as the Access database itself stores the next value for all AutoNumber fields in the database.
If a field is a foreign key referencing an AutoNumber field (primary key in another table), it must be defined as a number type. This is because the foreign key must be able to store the values defined in the table where the AutoNumber field is the primary key. For example, if you use an AutoNumber primary key for hotel reservations in the “ReservationNumber” field, then the “ReservationNumber” field in the “Bills” table, which is a foreign key, must be defined as an integer type instead of AutoNumber.
Relationships in Access
These relationships do not model the relationships between entities but rather the relationships between fields that are primary keys in one table and foreign keys in another. They always relate the same field in different tables. It doesn’t make sense to link different fields, such as a product code with a sales number. Always associate the same data in different tables: ID to ID, product code to product code, etc.
If we select “Enforce Referential Integrity,” it means we cannot insert a record into the “foreign” table if there is no associated record in the “main” table with that primary key value. For example, to insert a hotel reservation, there must be a customer in the Customers table with the specified ID.
If we select “Cascade Delete,” it means that when we delete a customer from the “Customers” table, the Access database will automatically delete all reservations for that customer ID, as it is a foreign key in the “Reservations” table. The same happens if we select “Cascade Update,” but in this case, changes made to the primary key values are propagated to tables where it is a foreign key.