Primary Key:
A Primary Key in a
Example:
Students in Universities are assigned a unique registration number.
Therefore, in a STUDENT database table, the attribute “reg_no” acts as primary key.
Foreign Key:
Foreign Key is a column in a relational database table which provides a relation between two tables. It provides a cross reference between tables by pointing to primary key of another table.
Example:
In STUDENT database table, the attribute “reg_no” acts as primary key and in COURSE database table in which the student selects his or her course, the same “reg_no” acts as foreign key for the STUDENT table.
One-to-Many Relationship:
When one record in a database table is associated with more than one record in another table, the relationship between the two tables is referred as one to many relationship. It is also represented as1: M relationship. This is the opposite of many to one relationship.
One-to-One relationship:
When one record in a database table is associated with one and only one record in another table, the relationship between the two tables is referred as one to one relationship. It is also represented as1: 1 relationship.
Given database tables:
Table Name: EMPLOYEE
EMP_CODE | EMP_TITLE | EMP_LNAME | EMP_FNAME | EMP_INITIAL | EMP_DOB | STORE_CODE |
1 | Mr. | Williamson | John | W | 21-May-84 | 3 |
2 | Ms. | Ratula | Nancy | 09-Feb-89 | 2 | |
3 | Ms. | Greenboro | Lottie | R | 02-Oct-81 | 4 |
4 | Mrs. | Rumpersfro | Jennie | S | 01-Jun-71 | 5 |
5 | Mr. | Smith | Robert | L | 23-Nov-59 | 3 |
6 | Mr. | Renselear | Cary | A | 25-Dec-85 | 1 |
7 | Mr. | Ogallo | Roberto | S | 31-Jul-82 | 3 |
8 | Ms. | Johnson | Elizabeth | I | 10-Sep-88 | 1 |
9 | Mr. | Eindsmar | Jack | W | 19-Apr-55 | 2 |
10 | Mrs. | Jones | Rose | R | 06-Mar-66 | 4 |
11 | Mr. | Broderick | Tom | 21-Oct-72 | 3 | |
12 | Mr. | Washington | Alan | Y | 08-Sept-74 | 2 |
13 | Mr. | Smith | Peter | N | 25-Aug-64 | 3 |
14 | Ms. | Smith | Sherry | H | 25-May-66 | 4 |
15 | Mr. | Olenko | Howard | U | 24-May-64 | 5 |
16 | Mr. | Archialo | Barry | V | 03-Sep-60 | 5 |
17 | Ms. | Grimaldo | Jeanine | K | 12-Nov-70 | 4 |
18 | Mr. | Rosenburg | Andrew | D | 24-Jan-71 | 4 |
19 | Mr. | Rosten | Peter | F | 03-Oct-68 | 4 |
20 | Mr. | Mckee | Robert | S | 06-Mar-70 | 1 |
21 | Ms. | Baumann | Jennifer | A | 11-Dec-74 | 3 |
Table Name: STORE
STORE_CODE | STORE_NAME | STORE_YTD_SALES | REGION_CODE | EMP_CODE |
1 | Access Junction | 1003455.76 | 2 | 8 |
2 | Database Corner | 1421987.39 | 2 | 12 |
3 | Tuple Charge | 986783.22 | 1 | 7 |
4 | Attribute Alley | 944568.56 | 2 | 3 |
5 | Primary Key Point | 2930098.45 | 1 | 15 |
Trending nowThis is a popular solution!
Chapter 3 Solutions
Database Systems: Design, Implementation, & Management
- All entity names, relationship names, and attribute definitions must be correct.arrow_forwardUsing the Entity Relationship Diagram you have created, create the tables and insert the values supplied in each table. Make use of MYSQLarrow_forwardconsider the following relational schema Product (product_id, vendor_id, product_price) 1)List all vendors who have 2 or more products priced at 4 or more.arrow_forward
- The requirements shown in Question 11 have changed. The dentist’s office would like to add the date of the patient’s last payment. In which relation (table) would you place this attribute? Why?arrow_forwardHow is the word characteristic used in the relational model? What is a 'more popular attribute name?'arrow_forwardUse the XYZ Apartment Rental Company ERD shown on Figure 1 to answer questions 9 through 12: According to the XYZ Apartment Rental Company ER diagram, which of the following is true? Each building must have more than one builder Each building must have multiple apartments Each building must have at least 8 apartments Each building must have more than 2 apartments Answer: ____arrow_forward
- Using SQL data definition language, write the SQL statements to create each of the following relations. i. student(student id, name, dept id, total credits)ii. department(dept id, building, budget)iii. lecture_hall(hall id, campus, capacity)iv. course(course id, title, dept id, credits) v. takes(student id, course id, semester, year, grade)arrow_forwardGiven the ER diagram below, write SQL statements to create the corresponding relations and capture the constraints.arrow_forwardProvide a summary of customer balance characteristics for customers who made purchases. Include the minimum balance, maximum balance, and average balance, as shown in Figure P7.21.FIGURE P7.21 BALANCE SUMMARY FOR CUSTOMERS WHO MADE PURCHASESarrow_forward
- Write down the relationship type and statement of the following: Ruler/Country: ……………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………… Product/Price: ……………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………… Zoo/Animals: ……………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………… Recipes/ Ingredients: ……………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………… Doctor/Patient: ……………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………… Sim Card/Phone Number: ……………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………… Country/Capital City: ……………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………… Actors/Movies: …………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………arrow_forwardHow is a foreign key represented in relationalnotation?arrow_forwardwrite the sql queries for the following er diagram: Create all the tables and relationships (including all primary keys, foreign keys, attributes and appropriate data types) shown in the ER diagram above.and Insert 3 records into each table. In the Item table, consider the first record as your favorite dress, second record as a gift that you have recently bought to someone, and the third record as your most favorite accessory that you are currently using; then insert the appropriate data. Similarly, insert some valid data into the other three tables. and Write an SQL statement to update the third row of Item table: Item_Name as ‘Hat’, Brand as ‘Puma’, and Type as ‘Ferrari’. and Write an SQL statement to display the Branch_ID, Location_ID, Item_ID, Units_Sold and Dollars_Sold of each customer. Results should be in a descending order of Item_ID.and also Write an SQL statement that displays the number of items, the sum of units_Sold and the sum of dollars_sold in each location. (Hint: Use…arrow_forward
- A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr