Database Questions Help? =.=?

Question

How would I make a composite key (a composite key is like 2 keys combined right?

For example, in a database, there would be a CardID and a BookID, and if I want to join them together in a table called "loans" how do I do that (is composite key possible)?

And also, what is "Relationships" in a database between records (one record related to one record) and when to use it?

Please help me in answering all these questions asap. Thanks in advance.

Answer 1

Let's assume you are using Microsoft Access. Make sure there is currently no primary key set. Now highlight both the rows on the table that you want to use. Now find the primary key icon up in the toolbar and click it. A key symbol will appear at the start of both rows.



This is called a concatenated primary key.



Let's say you have a science class. That class has one room, and one teacher. The class also has 30 students. Now in a students table we don't record the science class, its teacher and room against each student. This is inefficient and also to later change teachers involves changing 30 records. Instead we use two tables.



Table one is about the class. ClassID teacher location etc.



Table two is about the student: Student ID, Name etc. Also we have a foreign key of class ID to show that they are in the science class. We create a relationship between the tables and then we can look up classes against students or the other way around as necessary. We can now change the teacher in one location and all records are up to date.



Hope this helps.

Answer 2

A composite key could be a key that contained many fields in it. They are very useful in hash table types of databases (outdated these days).



For example lets say that you had a loan number as a key to a loan record. Associated with that loan number was a set of individuals. 0 represented the main applicant, and 1, 2, 3, 4, .. represented the additional applicants on the loan. If each of these applicants had residential histories with 0 being the current residence, 1 being the previous residence and so on. The Composit Key for the residence record would be



loan #,applicant #, residence #.



With this type of composite key, you would know quite a bit of information by just looking at the key. You could also gather all of the parent record information from this key.



There would be 1 loan record with a loan # as the key.

there would be 1 or more applicant record that shared the loan # in it's key. For each of these applicant records, there would be 1 or more residence records associated with that applicant record and loan record.



This is possible because every residence record key contains within it, the key to the parent applicant record, and the key to the applicant record contains the key to the loan record.





I hope you get an A on your homework.



[e-mail]

Answer 3

A composite key -or a composite of any data fields- is usually done by reference in the properties of the third field which "joins" the other two.



Use Excel to practice some of the concepts. In fact, if it is possible tio use Excel instead of a database, you should do so. Often, a spreadsheet program will do the job perfectly.



Let's suppose that the CardID is the cardholders social sec number, and that the BookID is the Dewey Decimal System number of the book.



In Excel, for example, suppose we have CardID in row 1 of col A and BookID in row 1 of col B.



a1 = 221786966

b1 = 640.76.0025



In row 1 of col C we would put this formula:



A1&B1, or CONCATENATE(A1,B1)



which would return this value: 221786966640.76.0025.



If wanted to put a marker to separate the values visually, we might add a dash (-) so the code would look like this:



A1&'-'&B1, or CONCATENATE(A1,'-',B1)



and return the result: 221786966-640.76.0025





Now, as for the "relationship" part. The CardID will be associated with a name and contact infomration of the cardholder. So, let's say that 221786966 belongs to:



John Gee

314 Oak Street

Anytown, CA 99106



What is important is that John Gee has a UNIQUE CardID shared by no one else. That's one reason a social security number is good to use -there are no two people with the same number.



And so, the CardID sets the relationship between a book that has been taken out and the person who has that book. In one database (spreadhseet) we keep a record of all the cardholders, each one with a unique CardID and contact info. Call that database, "CardHolderTable"



And in another database (spreadsheet) we keep a record of books on loan to cardholders. Call that database "BooksOutTable."



(Sometimes, databases refer to their lists of info as "tables.")



What BOTH have in common is the CardID -thus, we need not repeat John's contact info on the BooksOut table because we have there the "key" to the information in the CardHolderTable -John's unique ID. And so, if we see that a book is overdue, we see the "key" to identify the borrower, and can go to the CardHolderTable to look up John's contact info. This process can also be automated, so that the BooksOutTable "looks at" the CardID part of the composite key, and displays John's contact data -so you don't need to look it up manually. Excel can do all this -no need for a database program like Access.



More on relationships:



A. There are 4 kinds: 1) "One-to-many," in which one datapoint is associated with two or more others. Example: John G has taken out 3 books. John is the "one," and the books are the "many." 2) "Many-to-one," which is the reverse, from the point of view of the books. 3) "Many-to-many," in which several datapoints are associated with several other datapoints. If, for example, the CardID was a"family membership," then several people with the same ID could each have borrowed several books, all on the same date. In general, you should AVOID using many-to-many references as they can get complicated. Finally, 4) "one-to-one" which refers to data that always belongs with some other date and appears ONLY ONCE in the entire data structure. Example: John's phone number goes with John -listed in the same table with him, and listed just ONCE.



B) Relationships may be dynamic or static. Thus, John's ID will never change, and evertime you run a report of borrowers, you'll always see John with HIS number. But the books he borrows WILL change from time to time -that's the dynamic part.



C) Relationships should be used to avoid duplicate data entry whenever possible -you want to enter borrower data and book data just ONCE for each item.



D) Relationships allow virtual data to be generated automatically. For example, if the fine for an overdue book is ten cents per day, we calculate what John owes by calculating the number of days overdue times the rate per day and generating the total owed for today. It's all done with math, "on the fly," the resulting calculation does not exist by itself in the system.



There are other features of relationships, but you get the idea.



Even if you do end using Access (and I pity you), start with Excel so you can experiment with various arrangements and clearly understand what it is you're trying to do.



If you like, I can give you small sample spreadsheet with examples -just use the email link to let me know.



Hope this helps.

Answer 4

Data entry as we have performed it so far consisted of typing the necessary value in each field. Because a regular database is made of various tables, these objects work as an ensemble to create a product as complete and efficient as possible. As there can be so many objects, one of the rules a database developer should follow is to avoid duplicate entry of data: the same piece of information should not be entered in two different objects. For example, when a person is opening a bank account at a bank, a clerk enters the customer’s information in an object such as a form or a table:







When the same customer comes back for a transaction such as to deposit money, her information should not be entered again but that information should be selected from the form used to perform transactions:







This is the basis of relational database. Relational database is based on the idea that objects of a database are connected or related so they can exchange information. This exchange of information is made possible by creating relationships among objects of a database.To know more about this click on the link below:



http://www.functionx.com/access/

Answer 5

Composite keys in Access can be achieved in the table designer. Select both columns and make them keys. You do not need to combine them into a single column (in fact you don't want to).



Relationships in access are used for underlying performance, cascading events (update, delete) and to help draw UI. Creating a relationship between BookId in your order table, and BookId in your Book table will tell the database that this is a look up to the books.

Answer 6

Well I guess we first need to find out what database system you are using. Depending on what database you are using will determine how to join those table together. Access will join the table with a relationship. Which basically tell the database that these 2 table are contain the same information in these columns. With SQL or a similar structure, you will combine the 2 table with a Join,union or similar statment within your query. something like

select * from Table1,table2 where table1.column1 = table2.cloumn1

Answer 7

What database type are you talking about?

SQL? Trans-SQL? Access? Oracle?