CS Basic Data Definition and Modification in SQL. Modifying Relation Schemas. V 'Drop' an attribute (column). ALTER TABLE Students DROP login;. If in this case you made a linked record field where the table was linked to itself, . There's a many-to-many relationship between the students and their classes. Our app will be used to manage a library of SQL books and allow users to check out . The id column in our users table has both of these constraints, and we've.
The point to remember is that you should use a name that makes the most sense to you or to the organization. Creating a linking table produces a few noteworthy results. The original relationship has been replaced by two one-to-many relationships: As such, they help to establish the relationship between their parent tables and the linking table.
Except in rare instances, a linking table always contains a composite primary key. This rule applies to the database's logical design only. There are various reasons why you might break this rule when you transform the logical design into a physical design, but this is a discussion that is beyond the scope of this book. It's important to note that you'll occasionally have to add more fields to the linking table in order to guarantee a unique primary key value.
For example, assume the school decides to record student schedules for every term of the school year fall, winter, and spring.
Creating multiple tables and table relationships
You would have to add a new field, perhaps called TERM, and designate it as part of the composite primary key. This would enable you to enter another instance of a given student and class into the table, but for a different term; a student may need to retake a class during the spring term because he failed the class in the fall term. The linking table helps to keep redundant data to an absolute minimum. There is no superfluous data in this table at all.
In fact, the main advantage of this table structure is that it allows you to enter as few or as many classes for a single student as is necessary. Later in the database-design process, you'll learn how to create views to draw the data from these tables together in order to present it as meaningful information. The name of the linking table reflects the purpose of the relationship it helps establish.
As you work with many-to-many relationships, there will be instances in which you will need to add fields to the linking table in order to reduce data redundancy and further refine structures of the tables participating in the relationship.
Is there a problem with either of these tables? He thought that this was the best way to associate various products with a particular order.
Redundant data caused by an improperly established many-to-many relationship. You can enter only one product number, quantity ordered, and quote price for any given record; therefore, you'll have to enter a new record into the table for each item a customer places on his order. Customer numberfor example, included eight items on an order he made on May 16, so there are eight records in the table for this order alone.
Based on what you've learned earlier in this chapter, you know that this is an improper way to establish this relationship.
Establishing Each Relationship
You also know that you can establish the relationship properly by creating and using a linking table. Finally, you modify the relationship diagram to reflect the changes you made to the structures.
When you establish a many-to-many relationship between a pair of tables, make certain you check each table and determine whether there are any fields that you should transfer to the linking table. When in doubt, load all the tables with sample data; this will usually reveal any potential problems. Note You won't encounter this problem very often if you faithfully follow the design process you've learned thus far.
It will typically arise, however, when you're trying to incorporate a pair of tables from an existing or legacy database and you haven't taken the time to refine their structures properly. You'll also encounter this problem when you work with someone who has little or no database-design experience.
Self-Referencing Relationships Establishing a self-referencing relationship will be a relatively simple task now that you know how to establish a relationship between a pair of tables.
One-to-One and One-to-Many You use a primary key and a foreign key to establish these self-referencing relationships, just as you do with their dual-table counterparts. The difference here, however, is that the foreign key will reside in the same table as the primary key to which it refers.
- Many-to-many relationships
You'll often find that the foreign key is already part of the table's structure. If the foreign key does not already exist, you'll simply create one. Recall that this table has a self-referencing one-to-one relationship because a given member can sponsor only one other member within the organization; the SPONSOR ID field stores the member identification number of the member acting as a sponsor.
You may remember that this table has a self-referencing one-to-many relationship because a single staff member can manage one or more other staff members. There is currently no means of associating a given staff member to other staff members within the table; therefore, you must create a new field that will act as the foreign key and enable you to establish the relationship.
This is perfectly acceptable because a manager will manage one or more staff members, but a given staff member reports to only one manager. As you may have intuitively guessed, the "one" side of the line commonly points to the primary key and the "many" side to the foreign key. As you work with self-referencing one-to-one and one-to-many relationships, take a moment and examine each table's structure carefully.
You'll occasionally find that you can or may need to modify and improve the existing structure in order to eliminate the relationship. I know what you're wondering: A discussion of the reasons for this is, unfortunately, outside the scope of this work.
Additionally, the very presence of the relationship can indicate the need for new field and table structures. Does it occur to you that if there is a need to track staff members who are managers, there could be a need to track the departments they manage? If this is true, then there must be other facets of the departments that you need to track in the database. You should now conduct a quick interview with the appropriate staff members to answer these questions and then take the appropriate action based on their responses.
Let's assume you were right and the organization does want to track departmental data. Results of eliminating the self-referencing relationship and adding new structures to track departmental data.
These new structures and relationships enable you to track the data efficiently and will provide a wide variety of information about the departments. You will, of course, ensure that the new fields and tables conform to the various design elements that you've learned thus far.
It's important to note that self-referencing relationships do have their place within a well-designed database. You should be vigilant, however, and make certain that each self-referencing relationship does indeed serve a useful purpose. The Many-to-Many Relationship You use a linking table to establish this type of self-referencing relationship, just as you do with its dual-table counterpart.
Establishing this relationship is slightly different in that the fields you use to build the linking table come from the same parent table. Recall that this table has a self-referencing many-to-many relationship because a particular part can comprise several different component parts, and that part itself can be a component of other parts.
You establish this relationship as you would any other many-to-many relationshipwith a linking table. There is currently no way to associate a given part to other parts within the table, so you must create a new field for this purpose.
This field will store the part identification number of a part that serves as a component of a parent part. Once you've created and named the linking table, be sure to revise the relationship diagram for the PARTS table. Now, use the techniques you've just learned to establish all of the relationships you've identified among the tables in the database. Make absolutely certain you create a diagram for each relationshipyou're going to add new information to these diagrams as the design process further unfolds.
Reviewing the Structure of Each Table Review all of the table structures after you've established the relationships between tables. Now we have created our books and reviews tables, let's add some data to them. Since a column in reviews references data in books we must first ensure that the data exists in the books table for us to reference. We set up the table in this way for our example because we wanted to focus on the one-to-many relationship type.
If we had added such a Foreign Key to reviews we'd effectively be setting up a Many-to-Many relationship between books and users, which is what we'll look at next. Many-to-Many A many-to-many relationship exists between two entities if for one entity instance there may be multiple records in the other table, and vice versa. A user can check out many books. A book can be checked out by many users over time. In order to implement this sort of relationship we need to introduce a third, cross-reference, table.
Table of Contents
We already have our books and users tables, so we just need to create the cross-reference table: Each row of the checkouts table uses these two Foreign Keys to create an association between rows of users and books. We can see on the first row of checkouts, the user with an id of 1 is associated with the book with an id of 1. On the second row, the same user is also associated with the book with an id of 2. On the third row a different user, with and id of 2, is associated with the same book from the previous row.
On the fourth row, the user with an id of 5 is associated with the book with an id of 3. Don't worry if you don't completely understand this right away, we'll take a look shortly at what these associations look like in terms of the data in users and books.
First, let's create our checkouts table and add some data to it. While these aren't necessary to create the relationship between the users and books table, they can provide additional context to that relationship. Attributes like a checkout date or return date don't pertain specifically to users or specifically to books, but to the association between a user and a book.
This kind of additional context can be useful within the business logic of the application using our database. Now that we have our checkouts created, we can add the data that will create the associations between the rows in users and books. We can perhaps think of a Many-to-Many relationship as combining two One-to-Many relationships; in this case between checkouts and users, and between checkouts and books.
Summary In this chapter we covered a number of different topics regarding table relationships: We briefly covered normalization, and how this is used to reduce redundancy and improve data integrity within a database. ERDs were introduced, and we discussed how these diagrams allow us to model the relationships between different entities.
We also looked at keys, and how Primary and Foreign keys, and how these work together to create the relationships between different tables. Finally we looked at some of the different types of relationships that can exist between tables and how to implement these with SQL statements.