Define the Logical Structures of an Entity
Okay, now that you understand some of the basics of entities, attributes, and data types, let's start building a database blueprint!
The relational data model makes sure data is only entered or updated in one location, keeping the database from potential issues. That said, it consists of a number of linked tables that provide access to all data related to a particular record or set of records.
Remember, entities are single items that can be described like books, authors, customers, orders, and so on. They are generally written with a capital letter and translate into database tables where they are often broken into two or more tables when you normalize (update data in only one place).
An entity instance would be a single occurrence of an entity and is equivalent to one record in the database. In the example below, the Publishers table, followed by a record from that table defined by the primary key PublisherID, which has a value of 1 in this instance.
You build on the idea of an entity with entity types, which are collections of two or more tables with similar attributes. They often describe very similar or related data such as in the entity type below: the Customer table with customer details, the Orders table with order details, and the OrderDetails table with line item details for each order.
Define Primary and Foreign Keys
The primary key is the attribute which uniquely identifies a record in a table.
The foreign key is that same unique value but in a different table, providing a link between the two tables.
Primary keys are almost always numeric and are often generated automatically by the database management system when a new record is inserted. This tends to be one of the easiest ways of maintaining consistently unique primary key values with little worry on the part of those who built or maintain the tables.
In the diagrams above, PK identifies the primary keys, and FK identifies the foreign keys. For example, in the Orders table above, the attribute OrderID is the unique identifier for this table, so it is defined as the primary key. The attribute CustomerID links back to the Customers table, where CustomerID is defined as that table's primary key, so in the Orders table it is defined as a foreign key.
Let’s Recap!
Entities are the building blocks for the logical design process and are anything that can be described.
An entity instance is a single occurrence of an entity.
Entity types are collections of two or more entities with similar properties.
Primary keys uniquely identify a record within a database.
Foreign keys are the link from one table to the primary key in another table.
Now that we're familiar with relational data models and the primary and foreign keys that connect your data, let's discuss the constraints that will help you manage your data relationships.
