signature assignment relational database table

• Table of Contents
• Course Home
• Assignments
• Peer Instruction (Instructor)
• Peer Instruction (Student)
• Change Course
• Instructor's Page
• Progress Page
• Edit Profile
• Change Password
• Scratch ActiveCode
• Scratch Activecode
• Instructors Guide
• About Runestone
• Report A Problem
• 2.3 Converting ERD to a relational model
• 2.2. Entity-relationship diagrams" data-toggle="tooltip">
• 2.4. ERD alternatives and variations' data-toggle="tooltip" >

Before you keep reading...

Runestone Academy can only continue if we get support from individuals like you. As a student you are well aware of the high cost of textbooks. Our mission is to provide great books to you for free, but we ask that you consider a $10 donation, more if you can or less if $10 is a burden.

Making great stuff takes time and $$. If you appreciate the book you are reading now and want to keep quality materials free for other students please consider a donation to Runestone Academy. We ask that you consider a $10 donation, but if you can give more thats great, if $10 is too much for your budget we would be happy with whatever you can afford as a show of support.

2.3. Converting ERD to a relational model ¶

In this chapter we explain the process of creating a relational database from an entity-relationship model. While many steps are largely mechanical, a number of decisions need to be made along the way. We will explore the trade-offs for each decision. We will use the computer manufacturer data model from Chapter 2.2 as our example.

This chapter assumes you are familiar with the basics of the relational model of the database, including tables and primary and foreign key constraints. The necessary foundations are covered in either Part I (Chapters 1.1 and 1.7 ) or Part III ( Chapter 3.1 ).

There are many ways to represent the relational database: logical or physical data models ( Chapter 2.4 ), text or tabular descriptions, or SQL code. Which you use will depend on your development process and needs. In this chapter, we will provide simple text descriptions in tabular format.

We start with the basic conversion rules, converting pieces of our example data model as we go. The full set of tables resulting from our conversion is given at the end of the chapter.

2.3.1. Entities ¶

The first step in building a relational database from an ERD is creating a table from each entity in the data model. Weak entities need slightly different handling than regular entities, so we will address them separately, starting with regular entities.

2.3.1.1. Regular entities ¶

First, decide on a name for the table - this does not have to be the same as the entity name! There are many naming schemes for tables. If you are building a database for a company or organization that has naming standards, you will of course want to follow those. Otherwise, choose a basic approach and be consistent. For example, some databases use plural nouns for tables, while others use singular nouns. In our data model from Chapter 2.2 , the entity employee might become a table named employee or employees . Another naming issue arises with table names containing multiple words; some databases choose to run these together, while others employ underscore characters. For example, the entity assembly line could become a table named assemblyline or assembly_line . In our examples below, we will use singular nouns and underscores.

Most attributes for the entity should be converted to columns in the new table. Do not create columns for derived attributes, as these values are not intended to be stored. Do not create columns for multivalued attributes; we will address these later. For composite attributes, create columns only for the component attributes, not the composite itself. As with entities, you will need to decide on a name for each new column, which does not have to be the same as the attribute name. You will also need to specify a type and any constraints for the column. Determining appropriate types for some columns may require consultation with your data domain experts. Constraints may be added as appropriate. In the descriptions below, we will use simple type and constraint descriptions, rather than SQL syntax.

Choose a key attribute (every regular entity should have at least one) and use the column created from it as the primary key for the new table. If the entity has multiple key attributes, you will need to decide which one makes most sense as a primary key. Simpler primary keys are usually preferred over more complex ones. If desired, you can constrain the columns resulting from other keys to be not null and unique similar to primary key columns. For example, an employee table might use a company generated ID number as its primary key, and also include a column for a government issued ID number which we would want to constrain to prevent duplicates.

Here is our ERD depiction of the employee entity:

Here is a preliminary conversion of the employee entity into a relational table named employee :

This is not yet the final employee table! We will add additional columns to the table when we address the relationships that the employee entity participates in.

2.3.1.2. Weak entities ¶

Weak entities are converted into tables in nearly the same way as regular entities. However, recall that a weak entity has no identifying key attribute. Instead, it has a partial key, which must be combined with the key of the parent entity. In our example, the assembly line entity is weak. Its partial key, the number of the assembly line within a particular factory, must be combined with the factory identity for full identification.

The table created from a weak entity must therefore incorporate the key from the parent entity as an additional column. The primary key for the new table will be composed of the columns created from the parent key and from the partial key. Additionally, the column created from the parent key should be constrained to always match some key in the parent table, using a foreign key constraint.

Here is the ERD of assembly line and its parent entity, factory :

Using the above guidelines, we should create tables factory and assembly_line , and include a column in assembly_line for values from the city column of factory . A good choice of name for these “borrowed” columns is to concatenate the original table and column names together; in our case, this gives us the column factory_city . (We will use the term “borrow” in reference to this process of inserting a column in one table to hold values from the primary key column of a related table.) Here is the preliminary conversion of factory and the final conversion of assembly line :

2.3.2. Relationships ¶

Relationships can be handled using a few different approaches, depending on the cardinality ratio of the relationship. Most generally, we can create a table to represent the relationship. This kind of table is known as a cross-reference table, and acts as an intermediary in a three-way join with the two (or more) tables whose entities participate in the relationship. As we will see, some cardinality ratios permit simpler solutions.

2.3.2.1. Many-to-many ¶

Many-to-many relationships are the most general type of relationship; a database structure accommodating a many-to-many relationship can also accommodate one-to-many or one-to-one relationships, as “one” is just a special case of “many”. The challenge for many-to-many relationships is how to represent a connection from a record in one table to multiple records in the other table. While modern SQL allows array valued columns in tables, not all databases support them. The traditional solution is to create a cross-reference table.

Given a table A and a table B , we create a cross-reference table with columns corresponding to the primary keys of A and B . Each row in the cross-reference table stores one unique pairing of a primary key value from A with a primary key value from B . Each row thus represents a single connection between one row in A with one row in B . If a row in A is related to multiple rows in B , then there will be multiple entries with the same A primary key value, paired with each related B primary key value.

For example, our ERD indicates a many-to-many relationship between the entities vendor and part . A computer part (such as an 8TB hard drive) can come from multiple sellers, while sellers can sell multiple different computer parts:

We create tables vendor and part following the guidelines above, and then create the cross-reference table vendor_part . (It is common to name a cross-reference table using the names of the two tables being related, although other schemes can of course be used.) Note that the supplies relationship also has a relationship attribute, price , which we can incorporate into the cross-reference table. The result, with some fictional data, is pictured below:

Data in the cross-reference table is constrained in several ways. First, we only want to store the relationship between rows once, so we make the combination of primary keys from the related tables into a primary key for the cross-reference table. In our example, the primary key is the combination of vendor_name and part_number . Second, each of the borrowed primary key columns should be constrained to only hold values that are present in the original tables, using foreign key constraints.

Table descriptions for vendor , part , and the vendor_part cross-reference table are given below:

2.3.2.2. One-to-many ¶

As a special case of many-to-many relationships, one-to-many relationships can be implemented in the database using a cross-reference table as above. We have another choice, however. Observing that rows on the “many” side of the relationship can be associated with at most one row from the “one” side, we can choose to capture the relationship by storing the primary key of the “one” side table in the “many” side table.

In our ERD, the employee entity participates in one-to-many relationships with both factory and itself:

There is also a one-to-one relationship between employee and factory , which we will deal with in the next section.

Considering first the works at relationship, we see that each employee works at no more than one factory. Therefore, we can include a column for the factory’s city in the employee table. For consistency with previous choices, we will call this column factory_city . This column should be constrained by a foreign key referencing the factory table.

We also have the supervises relationship to deal with. In the same fashion as above, we should include a column in the employee table containing primary keys from the employee table. However, we should give careful consideration to the name we give this added column; employee_id would be a very misleading choice! A better choice is to consider the role of the employee whose id will be stored, and call the column supervisor_id .

With these changes, the employee table now looks like:

Using a cross-reference table instead of the above scheme is a perfectly valid choice, and may be preferable if there is any chance the data model might change such that the one-to-many relationship becomes many-to-many. In our example ERD, a given computer model is built at only one factory (while factories can build multiple different models); however, it would not be surprising if, at some point, we want to allow for models to be built at multiple locations. We might choose to use a cross-reference table for the relationship between factory and model in anticipation of this possibility.

2.3.2.3. One-to-one ¶

One-to-one relationships can be considered a special case of one-to-many relationships, so you can utilize either approach suitable for one-to-many relationships. In most cases, it will be preferable to borrow the primary key from one table as a foreign key in the other table. Using this approach, you could borrow from either side; however, one choice is often preferable to another.

In our example, we have a one-to-one relationship, manages , between employee and factory . We could therefore add another column to the employee table, this time for the city of the factory that the employee manages. However, most employees do not manage factories, so the column will end up containing many NULL values.

On the other hand, every factory should have a manager (implied by the total participation of factory in the relationship). It makes perfect sense, then, to add a column to the factory table for the employee managing the factory. This is another situation in which it makes sense to name the column for the role of the employee in this relationship, so we will call the new column manager_id .

Here is the completed factory table:

In some rare cases, it may make sense to handle a one-to-one relationship by simply merging the participating tables into one table. This should probably be reserved for situations in which both entities have total participation in the relationship.

2.3.2.4. Higher arity relationships ¶

For relationships with three or more participants, a cross-reference table incorporating primary keys from each of the participating tables is the best choice.

2.3.2.5. Identifying relationships ¶

Identifying relationships for weak entities are necessarily one-to-many or one-to-one. However, the conversion of the weak entity already incorporates a column containing primary key values from the parent table. This suffices to capture the relationship.

2.3.3. Multivalued attributes ¶

Multivalued attributes can be used to model a few different scenarios. As a result, there are multiple choices for how to store multivalued data in a relational database.

In the simplest case, a multivalued attribute is used when a list of arbitrary values needs to be stored, but there is no particular expectation that the values will be examined in a search of the database. In this case, an array-valued column may be an appropriate choice for databases that support such columns.

When there is a need to query the values associated with a multivalued attribute, or for databases that do not support array-valued columns, the best choice may be to make a simple table with two columns, one for the primary key of the owning table, and one for the values themselves. Each entry in the table associates one value with the instance of the entity.

In our example, computer models can be marketed to customers for different applications, such as gaming, video editing, or business use. This is represented in our data model with the multivalued application attribute:

We might, then, implement the model entity and its attributes using the following two tables:

Many applications also require the values associated with a multivalued attribute to be restricted to a certain list of values. In this case, an additional table is used. The additional table exists just to contain the allowed values, allowing us to constrain the data to just those values. For more complex values, an artificial identifier may be added as a primary key, and the primary key used in the multivalued attribute table instead of the values themselves, in which case the multivalued attribute table becomes a cross-reference table. For small lists of simple values (as in our example), this adds unnecessary complication.

For our example, we will constrain the application column using a foreign key constraint referencing this simple table:

2.3.4. Full model conversion ¶

In this section, we collect together all of the tables produced from our example data model, using the approach outlined above. For each table we include a short explanation of how the table relates to the data model.

The employee table contains columns for the attributes of the employee entity and foreign keys implementing the relationships works at and supervises .

The factory table contains columns for the attributes of the factory entity and a foreign key implementing the relationship manages . The throughput attribute is not reflected in the table, as it is a derived attribute. The throughput of a factory can be computed by summing the throughputs of the assembly lines in the factory.

The assembly_line table implements the assembly line weak entity. It incorporates a foreign key referencing the factory parent entity. Its primary key is composed of the parent entity key ( factory_city ) and the partial key ( number ).

The model table contains columns for the attributes of the model entity. Only the component attributes of the composite attribute designation are included; as designation was also the key attribute for model , the model table has a composite primary key. The table also includes a foreign key implementing the builds relationship. As mentioned in the text above, the builds relationship could alternatively be implemented using a cross-reference table connecting factory and builds , but we have opted for the simpler solution here. We assume that the designation of computer models includes the name of the computer line (e.g. “Orion”) and some particular version of the computer line, which we call the “number” of the model. These versions may contain letters as well as numbers (e.g., “xz450”), which is why a column named “number” is implemented as text.

The model_application table implements the multivalued attribute application of the model entity. Each row of the table contains a single application value describing a particular computer model. Note that, as the model entity has a composite primary key, the model_application table has a composite foreign key referencing its parent ( not two separate foreign keys for each component of the parent key). Additionally, we constrain the values in application to come from a set list of possible values, contained in the application table (below).

The application table contains a simple list of unique values which are available to insert into the model_application table.

The part table contains columns for the attributes of the part entity. The column part_number here, similar to the model “number” above, can contain characters as well as numbers, so again we use a text type column.

The model_part table is a cross-reference table implementing the can use relationship.

The vendor table contains columns for the attributes of the vendor entity. Only the component attributes of the contact info attribute are reflected.

The vendor_part table is a cross-reference table implementing the supplies relationship. In addition to the foreign keys for the tables it relates to, it contains a column for the price attribute of the relationship.

2.3.5. Self-check exercises ¶

This section has some questions you can use to check your understanding of how to convert ERDs to a relational database.

Q-1: Entities in our ERD become tables in our relational database. What do relationships become?

Any relationship can be converted into a cross-reference table. Is that the only possibility?

Foreign keys

One-to-one and one-to-many relationships can be converted into foreign keys in our database. Are those the only cardinality ratios?

Merging of tables

One-to-one relationships can result in merging tables, although this is rare.

All of the above

Each of the methods above can be applied, depending on the cardinality ratio of the relationship and other factors.

Q-2: Consider the ERD below. We create tables a and b , each of which have a primary key column named “id”. (Assume there are additional columns from attributes not shown.) What is the simplest way to convert the relationship between A and B ?

Create a column named “a_id” in table b , and make it a foreign key referencing table a .

Since a row in b could be related to multiple rows in a , we would need to store multiple ID values in column a_id . Some databases would permit this, but it would complicate queries and updates on the database.

Create a column named “b_id” in table a , and make it a foreign key referencing table b .

This is the simplest solution, assuming we do not expect the relationship to change to many-to-many in the future.

Create a cross-reference table, a_b , containing columns a_id and b_id as foreign keys referencing a and b respectively.

This is an allowable conversion. Is it the simplest?

Merge tables a and b into a new table.

This is not a good choice; while such a structure can be made to work, it is not considered good database design and is prone to errors. We would say that this table is not properly normalized . We explore normalization in Part 3 .

Q-3: Consider the ERD below. We create table r with primary key column id . What should table w look like?

The table should have a column partial as primary key. Additionally, create a cross-reference table r_w .

Partial keys cannot become primary keys. They do not represent unique identifiers for the instances of the weak entity.

The table should have columns partial and r_id . The primary key is partial . Add a foreign key constraint on r_id referencing r .

The table should have columns partial and r_id . The primary key is a composite of r_id and partial . Add a foreign key constraint on r_id referencing r .

The table should have columns partial and r_id . The primary key is r_id . Add a foreign key constraint on r_id referencing r .

The parent key is not a sufficient key for the weak entity; there will be multiple rows in w with the same values for r_id . Therefore it cannot be a primary key.

Q-4: Consider the ERD below. We create tables c and d , each of which have a primary key column named “id”. How should we handle the relationship between C and D ?

Borrow the primary key from one table as a foreign key into the other table (either direction is fine). Add a column named “x” into the table with the foreign key column.

Create a cross reference table c_d with columns c_id , d_id , and x . Make a composite primary key using c_id and d_id . Add foreign key constraints on c_id and d_id referencing c and d , respectively.

Create a cross reference table c_d with columns c_id and d_id . Make a composite primary key using c_id and d_id . Add foreign key constraints on c_id and d_id referencing c and d , respectively. Create another table, c_d_x , with columns c_id , d_id , and x . Table c_d_x has primary key x , and a foreign key constraint on c_id and d_id referencing table c_d .

This could almost work (you would need a different primary key for c_d_x ), but it is unnecessarily complicated.

Create a cross reference table c_d with columns c_id and d_id . Make a composite primary key using c_id and d_id . Add foreign key constraints on c_id and d_id referencing c and d , respectively. Add column x to either c or d .

The values for x will differ for different combinations of c and d . There is no good way to capture the dependence of x on d , for example, if we put the column in c .

Q-5: Which of the following statements is false ?

Composite attributes result in columns for each component as well as the composite.

We do not create a column for the composite, just the components.

Multivalued attributes usually require an additional table in the database.

This is true. In some cases it may be possible to use array-valued columns to handle a multivalued attribute, but otherwise we need an additional table or tables.

No column is created for derived attributes.

This is true. Derived attributes are not intended to be stored, as they can be computed from other values in the database.

If an entity has a composite key attribute, the resulting table will have a composite primary key.

This is true.

Want to create or adapt books like this? Learn more about how Pressbooks supports open publishing practices.

Chapter 8 The Entity Relationship Data Model

Adrienne Watt

The entity relationship (ER) data model has existed for over 35 years. It is well suited to data modelling for use with databases because it is fairly abstract and is easy to discuss and explain. ER models are readily translated to relations. ER models, also called an ER schema , are represented by ER diagrams.

ER modelling is based on two concepts:

• Entities ,  defined as tables that hold specific information (data)
• Relationships,  defined as the associations or interactions between entities

Here is an example of how these two concepts might be combined in an ER data model:  Prof. Ba (entity) teaches (relationship) the Database Systems course (entity) .

For the rest of this chapter, we will use a sample database called the COMPANY database to illustrate the concepts of the ER model. This database contains information about employees, departments and projects. Important points to note include:

• There are several departments in the company. Each department has a unique identification, a name, location of the office and a particular employee who manages the department.
• A department controls a number of projects, each of which has a unique name, a unique number and a budget.
• Each employee has a name, identification number, address, salary and birthdate. An employee is assigned to one department but can join in several projects. We need to record the start date of the employee in each project. We also need to know the direct supervisor of each employee.
• We want to keep track of the dependents for each employee. Each dependent has a name, birthdate and relationship with the employee.

Entity, Entity Set and Entity Type

An entity is an object in the real world with an independent existence that can be differentiated from other objects. An entity might be

• An object with physical existence (e.g., a lecturer, a student, a car)
• An object with conceptual existence (e.g., a course, a job, a position)

Entities can be classified based on their strength. An entity is considered weak if its tables are existence dependent.

• That is, it cannot exist without a relationship with another entity
• The Spouse table, in the COMPANY database, is a weak entity because its primary key is dependent on the Employee table. Without a corresponding employee record, the spouse record would not exist.

An entity is considered strong if it can exist apart from all of its related entities.

• Kernels are strong entities.
• A table without a foreign key or a table that contains a foreign key that can contain nulls is a strong entity

Another term to know is entity type which defines a collection of similar entities.

An entity set is a collection of entities of an entity type at a particular point of time. In an entity relationship diagram (ERD), an entity type is represented by a name in a box. For example, in Figure 8.1, the entity type is EMPLOYEE.

Existence dependency

An entity’s existence is dependent on the existence of the related entity. It is existence-dependent if it has a mandatory foreign key (i.e., a foreign key attribute that cannot be null). For example, in the COMPANY database, a Spouse entity is existence -dependent on the Employee entity.

Kinds of Entities

You should also be familiar with different kinds of entities including independent entities, dependent entities and characteristic entities. These are described below.

Independent entities

Independent entities , also referred to as kernels, are the backbone of the database. They are what other tables are based on. Kernels have the following characteristics:

• They are the building blocks of a database.
• The primary key may be simple or composite.
• The primary key is not a foreign key.
• They do not depend on another entity for their existence.

If we refer back to our COMPANY database, examples of an independent entity include the Customer table, Employee table or Product table.

Dependent entities

Dependent entities , also referred to as derived entities , depend on other tables for their meaning. These entities have the following characteristics:

• Dependent entities are used to connect two kernels together.
• They are said to be existence dependent on two or more tables.
• Many to many relationships become associative tables with at least two foreign keys.
• They may contain other attributes.
• The foreign key identifies each associated table.
• Use a composite of foreign keys of associated tables if unique
• Use a composite of foreign keys and a qualifying column
• Create a new simple primary key

Characteristic entities

Characteristic entities provide more information about another table. These entities have the following characteristics:

• They represent multivalued attributes.
• They describe other entities.
• They typically have a one to many relationship.
• The foreign key is used to further identify the characterized table.
• Use a composite of foreign key plus a qualifying column
• Employee ( EID, Name, Address, Age, Salary) – EID is the simple primary key.
• EmployeePhone ( EID, Phone ) – EID is part of a composite primary key. Here, EID is also a foreign key.

Each entity is described by a set of attributes (e.g., Employee = (Name, Address, Birthdate (Age) , Salary).

Each attribute has a name, and is associated with an entity and a domain of legal values. However, the information about attribute domain is not presented on the ERD.

In the entity relationship diagram, shown in Figure 8.2, each attribute is represented by an oval with a name inside.

Types of Attributes

There are a few types of attributes you need to be familiar with. Some of these are to be left as is, but some need to be adjusted to facilitate representation in the relational model. This first section will discuss the types of attributes. Later on we will discuss fixing the attributes to fit correctly into the relational model.

Simple attributes

Simple attributes are those drawn from the atomic value domains; they are also called single-valued attributes . In the COMPANY database, an example of this would be: Name = {John} ; Age = {23}

Composite attributes

Composite attributes are those that consist of a hierarchy of attributes. Using our database example, and shown in Figure 8.3, Address may consist of Number, Street and Suburb. So this would be written as → Address = {59 + ‘Meek Street’ + ‘Kingsford’}

Multivalued attributes

Multivalued attributes are attributes that have a set of values for each entity. An example of a multivalued attribute from the COMPANY database, as seen in Figure 8.4, are the degrees of an employee: BSc, MIT, PhD.

Derived attributes

Derived attributes  are attributes that contain values calculated from other attributes. An example of this can be seen in Figure 8.5.  Age can be derived from the attribute Birthdate. In this situation,  Birthdate is called a stored attribute,  which is physically saved to the database.

An important constraint on an entity is the key. The key is an attribute or a group of attributes whose values can be used to uniquely identify an individual entity in an entity set.

Types of Keys

There are several types of keys. These are described below.

Candidate key

A candidate key is a simple or composite key that is unique and minimal.  It is unique because no two rows in a table may have the same value at any time. It is minimal because every column is necessary in order to attain uniqueness.

From our COMPANY database example, if the entity is Employee (EID, First Name, Last Name, SIN , Address, Phone, BirthDate, Salary, DepartmentID), possible candidate keys are:

• First Name and Last Name – assuming there is no one else in the company with the same name
• Last Name and DepartmentID – assuming two people with the same last name don’t work in the same department

Composite key

A composite key is composed of two or more attributes, but it must be minimal.

Using the example from the candidate key section, possible composite keys are:

• Last Name and Department ID – assuming two people with the same last name don’t work in the same department

Primary key

The primary key is a candidate key that is selected by the database designer to be used as an identifying mechanism for the whole entity set. It must uniquely identify tuples in a table and not be null. The primary key is indicated in the ER model by underlining the attribute.

• A candidate key is selected by the design er to uniquely identify tuples in a table. It must not be null.
• A key is chosen by the database designer to be used as an identifying mechanism for the whole entity set.  This is referred to as the primary key. This key is indicated by underlining the attribute in the ER model.

In the following example, EID is the primary key:

Employee ( EID , First Name, Last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID)

Secondary key

A secondary key is an attribute used strictly for retrieval purposes (can be composite), for example: Phone and Last Name.

Alternate key

Alternate keys  are all candidate keys not chosen as the primary key.

Foreign key

A foreign key (FK) is an attribute in a table that references the primary key in another table OR it can be null. Both foreign and primary keys must be of the same data type.

In the COMPANY database example below, DepartmentID is the foreign key:

Employee (EID, First Name, Last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID)

A null is a special symbol, independent of data type, which means either unknown or inapplicable. It does not mean zero or blank. Features of null include:

• No data entry
• Not permitted in the primary key
• Should be avoided in other attributes
• An unknown attribute value
• A known, but missing, attribute value
• A “not applicable” condition
• Can create problems when functions such as COUNT, AVERAGE and SUM are used
• Can create logical problems when relational tables are linked

NOTE: The result of a comparison operation is null when either argument is null. The result of an arithmetic operation is null when either argument is null (except functions that ignore nulls).

Example of how null can be used

Use the Salary table (Salary_tbl) in Figure 8.6 to follow an example of how null can be used.

To begin, find all employees (emp#) in Sales (under the jobName column) whose salary plus commission are greater than 30,000.

• SELECT emp# FROM Salary_tbl
• WHERE  jobName = Sales AND
• (commission + salary) > 30,000  –> E10 and E12

This result does not include E13 because of the null value in  the commission column. To ensure that the row with the null value is included, we need to look at the individual fields. By adding commission and salary for employee E13, the result will be a null value. The solution is shown below.

• WHERE jobName = Sales AND
• (commission > 30000 OR
• salary > 30000 OR
• (commission + salary) > 30,000  –>E10 and E12 and E13

Relationships

Relationships are the glue that holds the tables together. They are used to connect related information between tables.

Relationship strength is based on how the primary key of a related entity is defined. A weak, or non-identifying, relationship exists if the primary key of the related entity does not contain a primary key component of the parent entity. Company database examples include:

• Customer( CustID , CustName)
• Order( OrderID , CustID, Date)

A strong, or identifying, relationship exists when the primary key of the related entity contains the primary key component of the parent entity. Examples include:

• Course( CrsCode , DeptCode, Description)
• Class( CrsCode, Section , ClassTime…)

Types of Relationships

Below are descriptions of the various types of relationships.

One to many (1:M) relationship

A one to many (1:M) relationship should be the norm in any relational database design and is found in all relational database environments. For example, one department has many employees. Figure 8.7 shows the relationship of one of these employees to the department.

One to one (1:1) relationship

A one to one (1:1) relationship is the relationship of one entity to only one other entity, and vice versa. It should be rare in any relational database design. In fact, it could indicate that two entities actually belong in the same table.

An example from the COMPANY database is one employee is associated with one spouse, and one spouse is associated with one employee.

Many to many  (M:N) relationships

For a many to many relationship, consider the following points:

• It cannot be implemented as such in the relational model.
• It can be changed into two 1:M relationships.
• It can be implemented by breaking up to produce a set of 1:M relationships.
• It involves the implementation of a composite entity.
• Creates two or more 1:M relationships.
• The composite entity table must contain at least the primary keys of the original tables.
• The linking table contains multiple occurrences of the foreign key values.
• Additional attributes may be assigned as needed.
• It can avoid problems inherent in an M:N relationship by creating a composite entity or bridge entity. For example, an employee can work on many projects OR a project can have many employees working on it, depending on the business rules. Or, a student can have many classes and a class can hold many students.

Figure 8.8 shows another another aspect of the M:N relationship where an employee has different start dates for different projects . Therefore, we need a JOIN table that contains the EID, Code and StartDate.

Example of mapping an M:N binary relationship type

• For each M:N binary relationship, identify two relations.
• A and B represent two entity types participating in R.
• Create a new relation S to represent R.
• S needs to contain the PKs of A and B. These together can be the PK in the S table OR these together with another simple attribute in the new table R can be the PK. 
• The combination of the primary keys (A and B) will make the primary key of S.

Unary relationship (recursive)

A unary relationship, also called recursive, is one in which a relationship exists between occurrences of the same entity set. In this relationship, the primary and foreign keys are the same, but they represent two entities with different roles. See Figure 8.9 for an example.

For some entities in a unary relationship , a separate column can be created that refers to the primary key of the same entity set.

Ternary Relationships

A ternary relationship is a relationship type that involves many to many relationships between three tables. 

Refer to Figure 8.10 for an example of mapping a ternary relationship type. Note  n-ary means multiple tables in a relationship. (Remember, N = many.)

• For each n-ary (> 2) relationship, create a new relation to represent the relationship.
• The primary key of the new relation is a combination of the primary keys of the participating entities that hold the N (many) side.
• In most cases of an n-ary relationship, all the participating entities hold a many side.

characteristic entities : entities that provide more information about another table

composite attributes : attributes that consist of a hierarchy of attributes

composite key : composed of two or more attributes, but it must be minimal

dependent entities : these entities depend on other tables for their meaning

derived attributes : attributes that contain values calculated from other attributes

derived entities : see dependent entities

EID : employee identification (ID)

entity : a thing or object in the real world with an independent existence that can be differentiated from other objects

entity relationship (ER) data model :  also called an ER schema , are represented by ER diagrams. These are well suited to data modelling for use with databases.

entity relationship schema : see entity relationship data model

entity set :a collection of entities of an entity type at a point of time

entity type : a collection of similar entities

foreign key (FK) : an attribute in a table that references the primary key in another table OR it can be null

independent entity : as the building blocks of a database, these entities are what other tables are based on

kernel : see independent entity

key : an attribute or group of attributes whose values can be used to uniquely identify an individual entity in an entity set

multivalued attributes : attributes that have a set of values for each entity

n-ary : multiple tables in a relationship

null : a special symbol, independent of data type, which means either unknown or inapplicable; it does not mean zero or blank

recursive relationship : see unary relationship

relationships : the associations or interactions between entities; used to connect related information between tables

relationship strength :  based on how the primary key of a related entity is defined

secondary key  an attribute used strictly for retrieval purposes 

simple attributes : drawn from the atomic value domains

SIN : social insurance number

single-valued attributes : see simple attributes

stored attribute : saved physically to the database

ternary relationship : a relationship type that involves many to many relationships between three tables.

unary relationship : one in which a relationship exists between occurrences of the same entity set.

• What two concepts are ER modelling based on?
• Identify the primary key for each table.
• Identify the foreign key in the PLAY table.
• Identify the candidate keys in both tables.
• Draw the ER model.
• Does the PLAY table exhibit referential integrity? Why or why not?
• Define the following terms (you may need to use the Internet for some of these): schema host language data sublanguage data definition language unary relation foreign key virtual relation connectivity composite key linking table
• Identify the primary and foreign key(s) for each table.
• Does the TRUCK table exhibit entity and referential integrity? Why or why not? Explain your answer.
• What kind of relationship exists between the TRUCK and BASE tables?
• How many entities does the TRUCK table contain ?
• Identify the primary key in each table.
• Identify the foreign key in the BookOrders table.
• Are there any candidate keys in either table?
• Does the BookOrders table exhibit referential integrity? Why or why not?
• Do the tables contain redundant data? If so which table(s) and what is the redundant data?
• Identity all the kernels and dependent and characteristic entities in the ERD.
• Which of the tables contribute to weak relationships? Strong relationships?
• Looking at each of the tables in the school database in Figure 8.15, which attribute could have a NULL value?  Why?
• Which of the tables were created as a result of many to many relationships?

Also see Appendix B: Sample ERD Exercises

Attribution

This chapter of  Database Design (including images, except as otherwisse noted) is a derivative copy of  Data Modeling Using Entity-Relationship Model  by  Nguyen Kim Anh  licensed under  Creative Commons Attribution License 3.0 license

The following material was written by Adrienne Watt:

• Nulls section and example

Database Design - 2nd Edition Copyright © 2014 by Adrienne Watt is licensed under a Creative Commons Attribution 4.0 International License , except where otherwise noted.

Share This Book

Structure of Relational Databases

We have briefly introduced ourselves to relational databases in the previous article . But a superficial understanding is not enough for us, is it? Let's go beyond the surface and delve deeper into the structure and terminology of relational databases.

Table Structure

In relational databases, information is stored in tables linked to each other. The tables themselves consist of:

• rows, which are called "records"
• columns, which are called "fields" or "attributes"

In each table, each column has a predetermined data type. For example, these types can be:

• VARCHAR (string data type)
• INTEGER (numeric data type)
• DATETIME (date and time data type)

And each row in the table must have the corresponding type for each column. The DBMS will not allow an attempt to add an arbitrary string to a field with the DATETIME type.

To find out the data types of the attributes, you can execute the SQL command DESCRIBE and specify the table name:

Alternatively, you can look at the ERD diagram of the database schema:

Primary Key

Any database management system has a built-in system of data integrity and consistency. This system works on a set of rules defined in the database schema. Primary keys and foreign keys are just some of these rules.

To avoid ambiguity in searching tables, there are primary keys, or "key fields".

A key field (primary key) is a field (or set of fields) whose value uniquely identifies a record in the table.

If we refer to our aforementioned table, FamilyMembers , then its key field is member_id . Using this rule, the DBMS will not allow us to create a new record where the member_id field is not unique.

It is worth noting that the presence of a primary key is not necessary, and data integrity can be determined, for example, at the application level.

Foreign Key

A foreign key is a field (or set of fields) in one table that refers to the primary key in another table.

The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

The foreign key rule guarantees that when creating records in the child table, the value of the field that is the foreign key exists in the parent table.

The presence of a foreign key is the same optional requirement as in the case of a primary key.

If the foreign key is not defined, the database management system will still work, but it will not verify that, for example, when creating a record in the Purchase table, the buyer_id and good_id fields contain values that are defined in the corresponding tables in the id field.

Which of the following statements is false regarding keys in relational databases?

Database schema design 101 for relational databases

This database schema design guide walks you through walk through the basics of creating and designing schemas for relational databases.

Getting started with a relational database can seem like a daunting task. Whether you’re coming from a NoSQL database or you’ve never used a database before, I’m going to talk you through designing a relational database and am hoping to answer the following questions:

What is a relational database?

• How are relationships made in the database?
• What are the steps to take to ensure an efficient database?

A relational database is one way to store data related to each other in a pre-defined way. By pre-defined, we mean that at the time of the creation of the database, you can identify the relationships that exist between different entities or groups of data. Relational databases are great for storing structured data that should model the relationship between real-life entities.

The anatomy of a relational database:

• Tables: Data representing an entity organized into columns in rows.
• Properties: Attributes that you want to store about an entity.
• Relationships: The relationships between tables.
• Indexes: Useful for connecting tables and making quick look-ups.

A relational database is made up of two or more tables with a variable number of rows and columns. Tables are unique to the entity they represent. Each column represents one specific property associated with each row in the table, and the rows are the actual records stored in that table. To illustrate the magic of a relational database, we’ll be designing a database for a retailer that wants to manage their products, customers, orders, and employees.

Design a database for a new retailer in town. This retailer really cares about customer relationships and wants to reward customers who meet a spending goal and gift these top customers on the 1 year anniversary of their first purchase. This retailer needs a way to organize products by price and category to make smart recommendations to their customers based on their age. This retailer also wants to track the best-performing employees to reward those with the highest sales with a raise at the end of the year.

Designing the database schema

The schema is the structure that we define for our data. The schema defines the tables, relationships between tables, fields, and indexes.

The schema will also have a significant impact on the performance of our database. By dedicating time to the schema design, we will save ourselves a headache in the future. One tool that will help us design our schema is an ERD, entity-relationship diagram. We’ll use Lucidchart to build out our ERD, and you can sign up for free. This diagram will allow us to visualize our entities and their relationships.

Here are the major to-dos when designing our schema we will cover in this post:

• Understand business needs
• Identify entities
• Identify properties/fields on those entities
• Define relationships between tables

Step 1: Understand business needs

The first step in designing a relational database schema is to understand the needs of the business. This will help us determine what type of information we should be storing. For example, if we are working with a retailer that wants to offer an anniversary gift for clients on their first anniversary, we would have to store the date a customer joins.

A recap of the requirements for our customers:

• Store customer spending to-date
• Store customer anniversary date of first purchase
• Store customer’s age
• Store employee sales total in dollar amount
• Store products and include a category and price property

Step 2: Define entities, aka tables

Once that is clear, the next step is defining the entities we want to store data about. These entities will also be our tables. Following the retailer example, our entities should be:

This could extend to add more entities like stores if there are multiple storefront locations, manufacturers, etc., depending on the needs of the business. For this blog post, we’ll just be working with the four entities we defined above to meet the needs of our fictitious client. We can represent an entity in our ERD with a rectangle and the table/entity name at the top.

Step 3: Define properties, aka fields

Once we’ve identified our entities, we should define what fields we want to store about these entities. One important thing to keep in mind is that each table, or entity, should have one unique, identifying property. This unique value is known as the primary key, and this helps us differentiate records from each other. For example, if we have two customers with the same name or same birthdate, we would have to spend some time figuring out which customer is the one we intend to work with.

Two common ways to come up with a primary key:

• Programmatically generate a unique value
• Assign an integer that automatically increases with each new entry

All of these are straightforward and were taken directly from the specs that the business gave us. For example, the business wants to know which customer made the purchase, which employee made the sale, and which products were in the order. In the Orders table, you will noticed that we reference a customerID , employeeID , and productID to meet those needs.

Step 4: Define relationships

Once we’ve defined our entities and their properties, we can think about how these tables relate to each other. The cornerstone of relational databases is that tables are often related. A parent table will have a unique primary key column, and a child table will have its own primary key and then a parent_id column that references the parent table. We have already inadvertently done this when we defined the properties in the preceding step. For example, the customers table has a customerID , which is the primary key. In the Orders table, we set an orderID as the primary key and reference the customerID to denote which customer made the order. Similarly, we also have a column referencing the Employees table, employeeID , to denote which employee made the sale.

When a primary key appears in another table, that field is called a foreign key in that table. The relationship between primary keys and foreign keys creates the relationship between tables.

You’ve done it

We’ve covered the main steps to take when designing your database schema: understand the business needs, define entities, define properties, and define relationships. Designing your database schema can be scary because with traditional relational databases, some schema changes can bring your whole application down and cause you to lose data. With PlanetScale’s branching feature , you can branch your schema like your code. Test your schema changes in an isolated environment, and once you are happy with your new schema, you can merge your changed branch into your main production branch without experiencing any downtime or data loss. Sign up for a PlanetScale account to get started.

Increasing PlanetScale plan limits to include billions of reads

Business intelligence for everyone

Create seamless in-product analytics

Self-service BI in 5 minutes

Advanced tools and controls

White-glove treatment

to see how to set up and publish a dashboard

Get answers in a few clicks

Pull threads in your data

See who did what, when

Share insights with anyone, anywhere

For advanced data users

Set boundaries around your data

A starting point for questions

Keep your data secure and private

Go beyond VLOOKUP

New tools for dashboard creators, data sharers, and more

Guides on working with data

News, updates, and ideas

Join a live event or watch on demand

Real companies, real data, real stories

Share and connect with other users

Find an expert partner

Practical advice from our community

Modeling financial data with Metabase

See how others work with data

Keep things organized

Database table relationships

Understanding how tables in a database can relate to each other., example one-to-one relationship, example one-to-many relationship, many-to-many relationship, further reading.

Relationships are meaningful associations between tables that contain related information — they’re what make databases useful. Without some connection between tables in a database, you may as well be working with disparate spreadsheet files rather than a database system.

As we covered in our short overview of databases , databases are collections of tables, and those tables have fields (also known as columns). Every table contains a field known as an entity (or primary) key, which identifies the rows within that table. By telling your database that the key values in one table correspond to key values in another, you create a relationship between those tables; these relationships make it possible to run powerful queries across different tables in your database. When one table’s entity key gets linked to a second table, it’s known as a foreign key in that second table.

Identifying the connections you’ll need between tables is part of the data modeling and schema design process — that is, the process of figuring out how your data fits together, and how exactly you should configure your tables and their fields. This process often involves creating a visual representation of tables and their relationships, known an entity relationship diagram (ERD) , with different notations specifying the kinds of relationships. Those relationships between your tables can be:

Giving some thought to how your tables should relate to each other also helps ensure data integrity, data accuracy, and keeps redundant data to a minimum.

One-to-one relationship

In a one-to-one relationship, a record in one table can correspond to only one record in another table (or in some cases, no records). One-to-one relationships aren’t the most common, since in many cases you can store corresponding information in the same table. Whether you split up that information into multiple tables depends on your overall data model and design methodology; if you’re keeping tables as narrowly-focused as possible (like in a normalized database ), then you may find one-to-one relationships useful.

Let’s say you’re organizing employee information at your company, and you also want to keep track of each employee’s computer. Since each employee only gets one computer and those computers are not shared between employees, you could add fields to your Employee table that hold information like the brand, year, and operating system of each computer. However, that can get messy from a semantic standpoint — does computer information really belong in a table about employees? That’s for you to decide, but another option is to create a Computers table with a one-to-one relationship to the Employee table, like in the diagram below:

In this case, the entity key from our Employee table serves as the foreign key for the Computers table. You may have computers that aren’t yet assigned to employees, and this modeling ensures that you can still keep records for them the Computer table. And if an employee leaves the company, you’ll only need to update one field, and you can easily link a computer to a new employee.

The exact formatting of the lines used to connect tables in an ERD (known as crow’s foot notation) varies; sometimes you’ll see plain lines indicating a one-to-one relationship, other times those lines will have crosshatches.

One-to-one relationships can be also useful for security purposes, like if you want to store sensitive customer information in a separate table, which you link to your main Customers table with a foreign key.

One-to-many relationship

One-to-many relationships are the most common type of relationships between tables in a database. In a one-to-many (sometimes called many-to-one) relationship, a record in one table corresponds to zero, one, or many records in another table.

For example, think about tables for customers and their orders, like in Metabase’s Sample Database, where one record from the People table can be linked to many records in the Orders table. In this case, one customer can place many orders, and those multiple order records will all link back to a single record in the People table. That connection is codified through the User_ID field, which is a primary key in the People table and a foreign key in the Orders table. The diagram below shows how these two tables relate to each other:

While the one person can be linked to many orders, the reverse is not true — orders are only linked to a single record in the People table, and don’t have many customers.

A many-to-many relationship indicates that multiple records in a table are linked to multiple records in another table. Those records may only be associated with a single record (or none at all) but the key is that they can and often are linked to more than one. Many-to-many relationships aren’t very common in practical database use cases, since adhering to normalization often involves breaking up many-to-many relationships into separate, more focused tables.

In fact, your database system may not even allow for the creation of a direct many-to-many relationship, but you can get around this by creating a third table, known as a join table , and create one-to-many relationships between it and your two starting tables.

In this sense, the Orders table in Metabase’s Sample Database acts as a join table, creating an intermediate link between People and Products . An ERD of the Sample Database would look something like the image below, where each relationship is specified by the type of line used to connect the tables:

Technically speaking the Products and Orders tables have a one-to-many relationship, in that one product can be associated with many orders. But according to our fake company’s database, people seem to only order a single product (they’ll buy like five Lightweight Wool Computers for whatever reason). A real-world (and perhaps more business-savvy) implementation of this database would probably include a join table between the two, making it so orders could contain many different products.

• The Data Model page: editing metadata
• Joins in Metabase

Did this article help you?

Thanks for your feedback!

Database Design: Entities, Attributes, and Relationships

About this article

In this article, we talk about entities, attributes, and relationships. We explore how we can discover them in the business domain and how they relate to tables, columns, and relationships in the database domain.

This the second post on a series exploring the subject of relational database design for the full-stack developer.

For quick access, here is the list of all the articles on the topic:

• Introduction to Relational Databases
• Database Design: Entities, Attributes, and Relationships ( this article )
• Database Design: Entity-Relationship Diagrams
• Database Design: Normalization
• Database Design: Entity-Relationship Diagram to Structured Query Language
• Deploying PostgreSQL for development and testing
• Structured Query Language Cheat Sheet
• Working with databases from Python

Introduction

As a full-stack developer, you work on many programming projects that required a database. For some of them, you use an existing database. For others, however, you must design and implement a database from scratch.

The quality of your database design has a direct impact on the quality of your final application. A well-designed database that accurately models the business domain entities results in software that is sturdy, reliable, and fast.

In this post, we use an example to illustrate how to discover entities, attributes, and relationships from a problem statement provided by a client. This discovery is a necessary first step for designing a relational database for a full-stack project.

This article relies on the process described by Fidel A. Captain in his excellent book “Six-Step Relational Database Design.” You should reference this book for a more in-depth look at relational database design.

Problem Statement

Before any database design takes place, you must obtain from your client a problem statement. The statement should clearly express the business problem you are solving and the data your application should track.

It is crucial to get the problem statement right. It should be concise while avoiding to omit important details.

Case Study: Atelier-M Store

Atelier-M Store sells personalized leather accessories on Instagram. The store posts product pictures and customers place orders following a link to a Google Form.

Sales are good, and the current method for taking and tracking orders is limiting further growth. Atelier-M hired you to build an online store.

The problem statement follows:

A small shop wants an online store to sell personalized leather accessories. The store application must keep track of the following:

• The customers
• The orders placed by customers and the details of the orders
• The products
• The personalizations requested on the products
• The packages requested for each product in the orders
• The payments received for the orders
• If the order is a gift, we must track the cards selected and the personalized messages that should be handwritten on the cards
• The staff that can access the store’s admin dashboard
• The delivery options
• The delivery addresses for the orders

Discover Entities and Assign Attributes

Entity identification.

Once you have the problem statement, the first step is to identify the entities the app must track. Entities are objects of interest in the business domain. They map to tables in the database and classes in code.

You can find entities in the problem statement by spotting nouns or collective nouns. Applying this technique in our case study, we get:

• The orders placed by customer sand the details of the orders
• The packages requested for each product in the orders.

This exercise produces an initial list of entities as follows: customers , orders , order details , products , personalizations , packages , payments , cards , messages , staff , delivery options , and addresses .

Refining the list, you could argue that  customers  and  staff  are application users with different roles and permissions. So, you can replace customers and staff with  users  and add entities for  roles  and  permissions .

Now, let’s take a look at cards and  messages . A customer places an order as a gift. In that case, the order would include a card with a handwritten message.

There are several types of cards to select from (i.e., birthday, Christmas, and so on), and your client could add new card types in the future. To accurately track them, cards should be an entity.

On the other hand, a gift order has one message. The customer enters the message text when placing the order. In this case, you should track the messages as attributes of orders.

Regarding  addresses , you may think of them as attributes of  users . However, users can place orders for delivery to different addresses (i.e., home, work, and so on), and attributes should describe one and only one characteristic of an entity. It is clear then that you must track addresses as an entity.

Pro Tip: About Naming Conventions

In a future article, we implement the database design from this series in PostgreSQL. For this reason, it is helpful to use PostgreSQL naming conventions for tables and columns while discovering entities and entity attributes. The naming rules we follow are:

• All identifiers are lowercase words (i.e., username ).
• If an identifier consists of several words, we use an underscore to separate each word in it (i.e., last_name ).
• Identifiers for table names are plural nouns (i.e., order_details ).
• Identifiers for table columns are singular nouns (i.e., quantity ).

If you are implementing your design in other RDBMS, use the naming conventions for that system.

The final list of entities after applying our naming convention is users , roles , permissions , orders , order_details , products , personalizations , packages , payments , cards , delivery_options , and addresses .

Attribute Assignment

Attributes are properties that describe an entity’s characteristics. Attributes map to database table columns, and as mentioned in Introduction to Relational Databases , both table columns and attributes should describe precisely one property of the entity.

The process of identifying attributes can be long and tedious. Meetings with the client and client’s staff are necessary to capture relevant attributes.

Reviewing the client’s processes, primarily process documentation, forms, and records kept either on paper or by electronic means, is essential for complete identification of all attributes.

In our case study, customers place orders by filling a Google Form. A Google Sheet captures the results from each form’s submission. From the study of this Google Sheet’s structure and meetings with your client, you come up with the following list of attributes for each of the discovered entities.

We used Excel to build list of entities and attributes for Atelier-M store’s database

Once you finish identifying attributes for the entities, you should search for candidate attributes for primary keys. A primary key is an attribute or a set of attributes that uniquely identifies an instance of an entity.

For example, in your entity  users , you could select  email  as a candidate primary key. After all, emails are unique and identify a particular user.

There is a problem, however, with that primary key. People can, and often do, change emails. If a user changes the email, records related to the old email become orphaned.

A common practice for assigning primary keys is to add an  id  attribute to each entity. The id auto-increments with each new instance of the entity, ensuring its uniqueness.

For the case study, you use precisely this technique and end up with the following diagram:

Entities and Attributes with Primary Keys for Atelier-M store’s database

Derive Relationships

Now that you have a clear picture of the entities and their attributes, you can proceed with exploring the relationships between entities. For this task, you use an Entity-Entity Matrix.

An Entity-Entity Matrix is just a table where both the column headings and the row headings are the entities discovered in the previous step.

For the case study, using Excel, you build the following Entity-Entity Matrix:

We used Excel to build this Entity-Entity Matrix

It is important to note that you only use the bottom half of the matrix. Since each half of the matrix mirrors the other, using both would be redundant.

Each cell in the matrix holds the relationship between the entities in its row and column. You should identify these relationships and write in each cell a verb that represents it.

If there is no relationship between two entities, leave the cell blank.

The resulting Entity-Entity Matrix for our case study follows:

Finished entity-entity matrix for Atelier-M database

Going over the matrix cell by cell, you can state:

• Users have roles
• Roles have permissions
• Users place orders
• Order details belong to orders
• Order details contain products
• Products have personalizations
• Orders use packages
• Orders have payments
• Orders use cards
• Orders have delivery options
• Addresses belong to users

Pro Tip: Unary Relationships

Note that in our exercise, no entity is related to itself. Keep in mind that those relationships (known as unary relationships) sometimes exist. One example would be the users’ entity in a social media application where users follow users.

In this article, you discovered the entities, attributes, and relationships for the database design of an online store. In the next article , you will use this information to create an entity-relationship diagram for implementation in PostgreSQL.

• “Six-Step Relational Database Design.” by Fidel A. Captain
• Difference Between Entity and Attribute in Database
• PostgreSQL naming conventions
• Relational database design question – Surrogate-key or Natural-key?
• N-ary relationship types

You may also like:

Share this:

Javier Feliu

I am a full-stack developer from Panama. I enjoy programming in Python and JavaScript. My current interests include database design, building RESTful APIs, workflow automation, containerization, and cloud deployments.

Questions? Comments? Leave a reply to start a conversation... Cancel reply

Discover more from wander in dev.

Subscribe now to keep reading and get access to the full archive.

Type your email…

Continue reading

HomeWork-Tutorial

Dat 210 wk 1 – apply: signature assignment: relational database table.

• Description

Imagine you work for an independent grocery store with 20 employees. The business owner has tasked you with creating a relational database that will track employee names, IDs, positions (e.g., cashier, manager, clerk, or night crew), and salaries.

Create a relational database table in Microsoft® Excel® for the grocery store using the provided data points.

Apply the universal rules (unique primary keys, unique column names, no duplicate rows) to your table.

Include all necessary null values.

Display in the table the requested database queries:

• INSERT INTO Account: VALUES (188820, ‘Wendall Glass’, $12/h)
• UPDATE Account: Set salary = $12/h WHERE ID = 128872
• DELETE FROM Account WHERE ID = 244332

Calculate the grocery store’s SUM salary and the AVG salary per position.

Submit your assignment.

• Database Design
• Database Structure
• Relational Database Model
• Relationally Complete
• Relational Completeness

Linking Database Tables

• SQL in RDBMS
• Create Table SQL
• Database Change Management
• Relational DB Structure
• Database Life Cycle
• Database Design Strategy
• Subject Approach DB Design
• Design Subject
• Application Approach Design
• Schema Architecture
• Three Schema Arch
• DBLC Design Stages
• DBLC Stages
• Post Design Stages
• Design Tools
• DB Design Strategy Conclusion
• Requirements Analysis
• Define Business Objects
• BO Characteristics
• Document Requirements
• Business Rules Importance
• Interview Data Users
• Data Flow Diagram
• Creating User Views
• Calculated Fields
• Requirements Documentation
• Requirements Analysis Conclusion
• Attributes and Entities
• Entities Conceptual Model
• Store Entity Values
• Rules for Creating Identifiers
• Entity Instances
• Attribute Domains Types
• Multivalued Attribute
• Multi-Valued Attributes
• Multivalued Attribute(resolve)
• Entity Attribute Constraints
• Module Summary
• ER Diagrams
• ERD Concept
• Three Relationship Types
• One to One Relationships
• Identify one To One
• One to many Relationships
• Identify One to Many
• Define many To Many
• Identify Many To Many
• Many to Many Challenges
• Convert m:n to 1:n
• Mandatory Optional Relationship
• Optional Participation Relationships
• ERD Conclusion
• ERD Conventions
• Three Model Types
• idef1x Crowsfoot Chen Models
• Entity Attribute Relationship
• ER Diagram Types
• Crow's Foot Notation
• DB Participation Types
• Interview End- Users
• Verbalize ER Diagram
• ER diagram model Types

Linking Relational Database Tables using Primary and Foreign Keys

• Relational Model: At the heart of relational databases is the relational model, which organizes data into tables (or relations). Each table is a collection of rows (records) and columns (attributes).
• Data Linking: The real strength of this model lies in its ability to link these tables through relationships, typically using keys (primary keys and foreign keys).
• Data Integrity and Consistency: Linking tables helps maintain data integrity. When tables are interconnected, changes made in one table can be reflected in related tables, ensuring consistency across the database.
• Elimination of Data Redundancy: By separating data into linked tables, redundant storage of data can be significantly reduced. For instance, rather than repeating customer information in every order record, a separate customer table can be linked to the orders table.
• Flexibility in Data Retrieval: The ability to join tables through relationships allows for flexible and powerful data retrieval. Users can query multiple tables simultaneously, combining data in various ways to meet specific requirements.
• Enhanced Data Security: Linking tables also supports better data security measures. Access to sensitive data can be controlled more effectively by managing permissions at the table level.
• Scalability and Efficiency: Relational databases are scalable. As the amount of data grows, these databases can manage increasing volumes efficiently, partly due to the effective organization of data into linked tables.
• Primary and Foreign Keys: The primary key of a table uniquely identifies each record in that table. A foreign key in a table points to a primary key in another table, establishing a relationship between the two tables.
• Normalization: This process involves organizing data in the database to reduce redundancy and improve data integrity. Normalization often results in multiple, related tables.
• Complex Queries: The ability to link tables allows for the execution of complex queries that can combine data from various parts of the database.
• Data Analytics and Reporting: Linked tables are essential for comprehensive data analytics and reporting, as they allow for the aggregation and comparison of data from different sources.
• Design Complexity: While linking tables offers many benefits, it also adds complexity to database design and requires careful planning to ensure optimal performance.
• Maintenance and Performance: Proper maintenance is needed to manage the relationships between tables, and performance can be impacted if these relationships are not efficiently designed.

Linking Relational Database Tables using primary and foreign keys

• Diagram displaying the primary key and foreign key of tables.
• SuppID is the primary key for the table Suppliers
• SuppID is the foreign key for the table Products .

Primary key of the Suppliers Table

Foreign key.

• Invent a name that incorporates the source table of the foreign key in its name without using the dot (using an underscore, for instance).
• Invent a name that incorporates an abbreviation of the source table of the foreign key in its name.
• Invent a name different from its name in its source table.
• Change the name of the conflicting column.

Accessing Data

Linking a primary key from one database table to a foreign key in a separate table.

• Identify the tables and the relationship: First, determine the two tables you wish to connect and the nature of the relationship between them. The relationship is typically one-to-many or one-to-one, with one table containing a primary key (parent table) and the other table containing a foreign key (child table) referencing the primary key.
• Ensure primary key constraints: Verify that the parent table has a primary key constraint on the column(s) you intend to reference. A primary key constraint enforces uniqueness and non-null values, ensuring that every row in the table can be uniquely identified.
• Create the foreign key column: In the child table, create a column or set of columns that will store the foreign key values. The foreign key column(s) must have the same data type(s) as the primary key column(s) in the parent table, and they should ideally have a descriptive name to indicate the relationship.
• Define the foreign key constraint: In your database management system, use the appropriate SQL command or graphical user interface to create a foreign key constraint between the primary key column(s) in the parent table and the foreign key column(s) in the child table. This process will vary depending on your specific DBMS. For example, using SQL syntax in a system like MySQL or PostgreSQL, you would create a foreign key constraint as follows: ALTER TABLE child_table_name ADD CONSTRAINT constraint_name FOREIGN KEY (foreign_key_column_name) REFERENCES parent_table_name (primary_key_column_name); Replace child_table_name, constraint_name, foreign_key_column_name, parent_table_name, and primary_key_column_name with the appropriate names for your specific tables and columns.
• Enforce referential integrity: The foreign key constraint enforces referential integrity, meaning that any value entered in the foreign key column(s) of the child table must have a corresponding value in the primary key column(s) of the parent table. This ensures data consistency and helps prevent orphaned records.

CS 142: Web Applications (Fall 2010)

Introduction to relational databases, sql.

Lecture Notes for CS 142 Fall 2010 John Ousterhout

• If you would like to learn more about relational databases, a good textbook is Database Management Systems , Third Edition, by Ramakrishnan and Gehrke. Even better, take CS 145.
• Relational database: collection of tables (also called relations )
• Collection of rows (also called tuples or records ).
• Each row in a table contains a set of columns (also called fields or attributes ).
• String: VARCHAR(20)
• Integer: INTEGER
• Floating-point: FLOAT, DOUBLE
• Date/time DATE, TIME, DATETIME
• Several others...
• Primary key: provides a unique identifier for each row (need not be present, but almost always is in practice).
• The table name
• The names and types of its columns
• Various optional additional information (constraints, etc.)
• The number of rows in a table is not part of the schema
• Initially created at IBM as part of System-R
• Implemented with modifications in numerous products: Oracle, Sybase, DB-2, SQL Server, MySQL
• Vendor versions are not completely compatible
• Has been (partially) standardized
• Examples that follow use MySQL syntax
• Create a table for the students: CREATE TABLE students ( id INT AUTO_INCREMENT, name VARCHAR(30), birth DATE, gpa FLOAT, grad INT, PRIMARY KEY(id));
• Add rows to the students table: INSERT INTO students(name, birth, gpa, grad) VALUES ('Anderson', '1987-10-22', 3.9, 2009); INSERT INTO students(name, birth, gpa, grad) VALUES ('Jones', '1990-4-16', 2.4, 2012); INSERT INTO students(name, birth, gpa, grad) VALUES ('Hernandez', '1989-8-12', 3.1, 2011); INSERT INTO students(name, birth, gpa, grad) VALUES ('Chen', '1990-2-4', 3.2, 2011);
• Delete row(s): DELETE FROM students WHERE name='Anderson';
• Delete table: DROP TABLE students;
• Lots of ways to extract information
• You specify what you want
• The database system figures out how to get it efficiently
• Refer to data by contents , not just name
• Show entire contents of a table: SELECT * FROM students; +----+-----------+------------+------+------+ | id | name | birth | gpa | grad | +----+-----------+------------+------+------+ | 1 | Anderson | 1987-10-22 | 3.9 | 2009 | | 2 | Jones | 1990-04-16 | 2.4 | 2012 | | 3 | Hernandez | 1989-08-12 | 3.1 | 2011 | | 4 | Chen | 1990-02-04 | 3.2 | 2011 | +----+-----------+------------+------+------+
• Show just a few columns from a table: SELECT name, gpa FROM students; +-----------+------+ | name | gpa | +-----------+------+ | Anderson | 3.9 | | Jones | 2.4 | | Hernandez | 3.1 | | Chen | 3.2 | +-----------+------+
• Filtering: only display a subset of the rows: SELECT name, gpa FROM students WHERE gpa > 3.0; +-----------+------+ | name | gpa | +-----------+------+ | Anderson | 3.9 | | Hernandez | 3.1 | | Chen | 3.2 | +-----------+------+
• Sorting: SELECT gpa, name, grad FROM students WHERE gpa > 3.0 ORDER BY gpa DESC; +------+-----------+------+ | gpa | name | grad | +------+-----------+------+ | 3.9 | Anderson | 2009 | | 3.2 | Chen | 2011 | | 3.1 | Hernandez | 2011 | +------+-----------+------+
• Updates: UPDATE students SET gpa = 2.6, grad = 2013 WHERE id = 2;
• Join: a query that merges the contents of 2 or more tables, displays information from the results.
• Can produce the equivalent of a linked list in a programming language, and many other effects.
• Students have advisors; introduce new table describing faculty. +----+----------+-----------+ | id | name | title | +----+----------+-----------+ | 1 | Fujimura | assocprof | | 2 | Bolosky | prof | +----+----------+-----------+
• Add new column advisor_id to the students table. This is a foreign key . +----+-----------+------------+------+------+------------+ | id | name | birth | gpa | grad | advisor_id | +----+-----------+------------+------+------+------------+ | 1 | Anderson | 1987-10-22 | 3.9 | 2009 | 2 | | 2 | Jones | 1990-04-16 | 2.4 | 2012 | 1 | | 3 | Hernandez | 1989-08-12 | 3.1 | 2011 | 1 | | 4 | Chen | 1990-02-04 | 3.2 | 2011 | 1 | +----+-----------+------------+------+------+------------+
• Example query: SELECT s.name, s.gpa FROM students s, advisors p WHERE s.advisor_id = p.id AND p.student = 'Fujimura'; +-----------+------+ | name | gpa | +-----------+------+ | Jones | 2.4 | | Hernandez | 3.1 | | Chen | 3.2 | +-----------+------+
• Potentially very expensive!
• In practice, optimized carefully by the database system.
• Courses: students take many courses, courses have many students
• Add new table describing courses: +----+--------+-----------------+-------------+ | id | number | name | quarter | +----+--------+-----------------+-------------+ | 1 | CS142 | Web stuff | Winter 2009 | | 2 | ART101 | Finger painting | Fall 2008 | | 3 | ART101 | Finger painting | Winter 2009 | | 4 | PE204 | Mud wrestling | Winter 2009 | +----+--------+-----------------+-------------+
• Create a join table courses_students describing which students took which courses. +-----------+------------+ | course_id | student_id | +-----------+------------+ | 1 | 1 | | 3 | 1 | | 4 | 1 | | 1 | 2 | | 2 | 2 | | 1 | 3 | | 2 | 4 | | 4 | 4 | +-----------+------------+
• Find all students who took a particular course: SELECT s.name, c.quarter FROM students s, courses c, courses_students cs WHERE c.id = cs.course_id AND s.id = cs.student_id AND c.number = 'ART101'; +----------+-------------+ | name | quarter | +----------+-------------+ | Jones | Fall 2008 | | Chen | Fall 2008 | | Anderson | Winter 2009 | +----------+-------------+
• Indexes: used to speed up searches (exact matches, ranges)
• Transactions: used to group operations together to provide predictable behavior even when there are concurrent operations on the database.
• Many other things.

• SQL Server training
• Write for us!

Learn SQL: CREATE DATABASE & CREATE TABLE Operations

Welcome to the first article in the Learn SQL series. In this part, we’ll start with two essential commands in SQL: Create Database and Create Table. While both are pretty simple, they should be used first before you start working on anything with data (unless you use some template database).

Later in this series, I’ll try to cover everything essential for the complete beginner to jump into the magical world of SQL and databases. So, let’s start:

The goal of this article is to create a database (using the SQL Create Database command) and two tables (using the SQL Create Table command) as shown in the picture above. In the upcoming articles, we’ll insert data into these tables, update and delete data, but also add new tables and create queries.

What is a database?

Before we create a database using the SQL Create database command, I want to define what a database is. I’ll use the definition provided by Oracle:

A database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS). (source: https://www.oracle.com/database/what-is-database.html )

In this article, I’ll use the Microsoft SQL Server Express edition. So, DBMS is SQL Server, and the language we’ll use is T-SQL. Once again I’ll use a quote:

T-SQL (Transact-SQL) is a set of programming extensions from Sybase and Microsoft that add several features to the Structured Query Language (SQL), including transaction control, exception and error handling, row processing and declared variables. (source: https://searchsqlserver.techtarget.com/definition/T-SQL )

I won’t go in-depth in this article, but we can conclude this part with a statement that a database is an organized set of tables that contain data from the real-world and some additional columns needed for the system to work properly. We’ll discuss these in upcoming articles.

SQL Create Database statement

After installing and opening Microsoft SQL Server Management Studio , our screen looks something like this:

It doesn’t look fun at all. We’ll make it more fun by creating a new database. After clicking on the New Query , the new window opens and we’re able to type something in. It looks like on the picture below:

Before typing anything, we should be sure we’re typing it in the right way. T-SQL is a language and as such it has its’ words – set of rules on how to write different commands.

Luckily, one of these commands is the SQL Create Database command. You can see the full T-SQL Create Database syntax on Microsoft pages .

I’ll simplify it a lot and go only with the most basic form. In order to create a new database on our server, we need to use the following command:

Where we’ll use the desired name instead of the database_name .

SQL Create Database example

OK, let’s try it. We’ll run a command:

After running this command, our database is created, and you can see it in the databases list:

Click on the + next to the folder Databases , and besides two folders, you’ll also see that our_first_database had been created.

This is cool and you’ve just successfully created your first database. The problem is that we don’t have anything stored inside the database. Let’s change that.

SQL Create Table statement

In database theory, a table is a structure (“basic unit”) used to store data in the database.

I love to use analogies a lot, so I’ll do it here too. If you think of a library, a database is one shelf with books, and each book is a table. Each book has its own contents but is somehow related to other books on the same shelf – either by sharing some properties, either by just being close.

There is a lot of theory behind database tables, and how to decide what goes where, but the simplest you can do is following. When we look at our data and we need to decide what goes where we should group data in tables in such a manner that everything that belongs to the same real-life entity goes to the same table.

E.g. if we want to store data describing cities and countries, we’ll have two separate tables in our database – one for cities and another one for countries. We won’t mix their data but rather relate them. This goes out of the scope of this article and shall be covered in the upcoming parts of this series.

To define a table, we’ll follow the syntax. You can see full T-SQL Create Table syntax here , but I’ll once more simplify the statement:

We’ll simply choose the name for our table and list all the columns we want to have in this table. Columns are also called attributes and each column describes a property of one record in the table. The column has its type and we should choose the type based on values we expect in that column (number, text, etc.).

SQL Create Table example

Let’s take a look at the definition of our two tables:

First, we’ll define the city table.

Please notice a few things:

• NOT NULL -> This is a property telling us that this column can’t be empty (must be defined)
• IDENTITY(1, 1) -> is also a property of the column telling us that this value shall be generated automatically, starting from 1 and increasing by 1 for each new record
• CONSTRAINT city_pk PRIMARY KEY (id) -> This is not a column, but the rule, telling us that column id shall contain only UNIQUE values. So only 1 city can have id =5

Here we have 1 new CONSTRAINT and that is the UNIQUE constraining. This one tells us that this value must be UNIQUE within this table. E.g. CONSTRAINT country_ak_1 UNIQUE (country_name) defines that we can’t store 2 countries with the same name.

The last part of the script is the definition of foreign keys. We have only 1 such key and it relates city and country table ( city.county_id = country.id ).

Keys (primary and foreign) are too complex and shall be covered in a separate article. After executing these commands, the status of our database is as in the picture below:

Congratulations. You have successfully created your first database using SQL Create Database and Create Table commands. We have 2 tables in our database. Now we’re ready to populate them with data and test if we did it as expected. We’ll do it in the next article, so, stay tuned!

Table of contents

• Recent Posts

• Learn SQL: How to prevent SQL Injection attacks - May 17, 2021
• Learn SQL: Dynamic SQL - March 3, 2021
• Learn SQL: SQL Injection - November 2, 2020

Related posts:

• The benefits, costs, and documentation of database constraints
• Commonly used SQL Server Constraints: FOREIGN KEY, CHECK and DEFAULT
• SQL Foreign key
• Top SQL Server Books
• Learn SQL: Join multiple tables

Search code, repositories, users, issues, pull requests...

Provide feedback.

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly.

To see all available qualifiers, see our documentation .

• Notifications

Language SQL Database Management System - (SEM-4) - ER/EER Diagram, DDL statements, primary key and foreign key constraint., SQL queries with different functions, Views, PL/SQL, Trigger (Row level and statement level) and cursor.

shinchancode/DBMS-SQL-Lab

Folders and files, repository files navigation, ❕ dbms-sql-lab ❕.

This is a repository for DBMS Lab assignments in Second year Information Technology of SPPU! Do star ⭐ this Repository if you feel it helped & drop a follow! 💙 Happy Learning 💙

Language used : MYSQL

Database Management System (SEM-4) - ER/EER Diagram, DDL statements, primary key and foreign key constraint., SQL queries with different functions, Views, PL/SQL, Trigger (Row level and statement level) and cursor.

Assignment-1

Design any database with at least 3 entities and relationships between them. Draw suitable ER/EER diagram for the system.

Assignment-2

Design and implement a database (for assignment 1) using DDL statements and apply normalization on the.

Multinational Company Manages information of Employees. Each Employee has unique ID. Each Employee works on project in some department. Design ER Diagram for Employee Management System

Assignment-3

Create table with primary key and foreign key constraint.

[a] Alter table with add and modify

[b] Drop Table

Assignment-4

Perform following SQL queries on the database created in Assignment 1.

• Implementation of relational operators in SQL

• Boolean operators and pattern matching

• Arithmetic operations and built in functions

• Group functions

• Processing Date and Time functions

• Complex queries and set operators

Assignment-5

Execute DDL statements which demonstrate use of Views.

Assignment-6

Write and execute PL/SQL stored procedure and function to perform a suitable task on the database.

Assignment-7

Write a database trigger (Row level and statement level)

Assignment-8

Write a PL/SQL block to implement all types of Cursors

Connect with me:

DAT 210 Week 1 – Apply: Signature Assignment: Relational Database Table

$ 40.00

• Description

Assignment Content

Imagine you work for an independent grocery store with 20 employees. The business owner has tasked you with creating a relational database that will track employee names, IDs, positions (e.g., cashier, manager, clerk, or night crew), and salaries.

Create a relational database table in Microsoft® Excel® for the grocery store using the provided data points.

Apply the universal rules (unique primary keys, unique column names, no duplicate rows) to your table.

Include all necessary null values.

Display in the table the requested database queries:

INSERT INTO Account: VALUES (188820, ‘Wendall Glass’, $12/h) UPDATE Account: Set salary = $12/h WHERE ID = 128872 DELETE FROM Account WHERE ID = 244332

Calculate the grocery store’s SUM salary and the AVG salary per position.

Submit your assignment.

Center for Writing Excellence Reference and Citation Generator Grammar Assistance

DAT 210 Week 2 – Apply: Labs

Dat 210 week 1 discussion – database tables, dat 210 week 2 – using loops in python.

Post Your Question If You haven’t Found In Our Inventory

SPIN TO WIN!

• Try your lucky to get discount coupon
• 1 spin per email
• No cheating