RissRoss

Project Questions

A few more project questions:

1) Is there any flexibility in the page numbers for the write-ups? When drawing up the papers we have gone over the suggested page limit, is this ok?

2) What does #5 deliverable mean when it says "drop all tables and views from your database?"

Project Write-Up

As the projects are starting to wrap up, I am curious as to how everyone is drawing up their E-R diagrams. I have heard of some recommended software to create the E-R diagrams by computer. Would creating the E-R diagrams by hand be better in order to use the same designs we have used in class, for example a diamond in a box to represent the associative entities? I am not sure that any program has similar designs to the ones we have implemented in class. Has anyone found such a software?

In addition, is there a specific functional notation we should use? I know we have seen a few different examples in class.

Foreign Key as a Primary Key?

In creating the E-R diagram for the class project, I have confused myself in understanding the role of a foreign key as a primary key. I understand that in an associate entity, the primary keys are foreign keys in reference to the tables you are connecting. However, I am curious about tables connected together through a direct relationship (a line) rather than an associative entity. In this case, can the primary key of one of the tables also be a primary key of the table it has a direct relationship with?

For example, if table Project is connected to table Organization, can the primary key of Organization (Org ID) be a part of the composite primary key of the table Project, which woud then be used as a foreign key in many other tables associated with project? For example, Project's composite primary key would be something like (Title, Org_ID).

If anyone has any insight into this question, I would appreciate some feedback. I think I have come to confuse myself.

Foreign Key Issues | MySmartChannels

Foreign Key Issues | MySmartChannels

Below is my response to Go Blue's blog on composite primary and foreign key creation issues she has been experiencing while trying to create her project's E-R diagram. My group and I had pretty much the same problem to begin with, but have since figured it out with the help of Bud.

To create a composite primary key you can not simply write primary key after each entity you wish to designate as part of the primary key. Instead, you have to go about it one of two ways:

1) Specify no primary keys, create the table, and then use the function "alter table, add primary key" to create the primary keys. For example, your creation statements would look something like this:

Create Table Blog (Author varchar(20), Title varchar(20));

Alter Table Blog
Add Primary Key (Author, Title);

(look at pg 150 of SQL book for more help)

OR

2) Write all the entities of the table following the create command, then write primary key followed by parentheses with all of the entities that make up the primary key. For example:

Create Table Blog (Author varchar(20), Title varchar(20), Category varchar(20), Primary Key (Author, Title) );

(look at pg 41 for more help)

Next, in order to create foreign keys that refer to composite primary keys of a table, you first create the new table and then use the "alter table, add forgein key" function once and put all the foreign keys into the parentheses in the SAME order as you created the primary keys of the table you are referring to.

Here is an example:

Create Table .....,

Alter Table Team
Add Foreign Key (Author, Title) References Blog

*Notice the composite primary key is added TOGETHER as a foreign key and in the SAME order.

Hope this helps.