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
- Define second normal form. What types of problems might you encounter using tables that are not in second normal form?arrow_forwardList the changes you would need to make to your answer for Question 11 if the requirements change so that you must store the year and the semester in which a student took a course and received a grade.arrow_forwardList the Product Name of each product, together with the Supplier Name of its supplier, and Category Name of its category. different tablesarrow_forward
- Which type of relationship represents the rule that "Each vendor supplies multiple parts to our company and each part can be supplied by one or more vendors"? Question 43 options: 1) 1:M 2) 1:1 3) 0:1 4) M:Narrow_forwardConsidering the un-normalized relational table PROJECT below: PROJECT (ProjectTitle, EmpeName, ManagerName, Location, HoursWork, ManagerPhone) The attributes of PROJECT table satisfy the following properties: Each project has many employees, Each employee may involve in one or more projects, Projects are managed by managers; there are many managers in the company, hence a project may be assigned to any one of the managers, but each manager manages only one project. Each project is located in a specific location, The number of hours an employee works at each project is determined by a project title and the name of an employee. Each manager has a telephone number. Normalize the relational table PROJECT into a minimal number of relational tables in BCNF. Use the functional dependencies to prove that each one of the relational tables obtained from the decomposition of the original table is in BCNF.arrow_forwardWhich type of relationship represents the rule that "Each vendor supplies multiple parts to our company and each part can be supplied by one or more vendors"? 1) 0:1 2) 1:M 3) M:N 4) 1:1arrow_forward
- Please answer with detailed explanationarrow_forwardUsing the AND or OR statement, filter the employee table for employees who live in the city Lethbridge or have IT within their title. Identify the first name of the 2nd record. Robert Laura Michael Kingarrow_forwardwrite the following queries (n) Identify flats with at least three rooms and with a monthly rent no higher than £500 in Aberdeen. (0) List the details of leases due to expire next month at a given branch. (p) List the total number of leases with rental periods that are less than one year at branches in London. (q) List the total possible daily rental for property at each branch, ordered by branch number. System Description: Branches Dream Home has branch offices in cities throughout the United Kingdom. Each branch office is allocated members of staff, including a Manager, who manages the operations of the office. The data describing a branch office includes a unique branch number, address (street, city, and postcode), telephone numbers (up to a maximum of three), and the name of the member of staff who currently manages the office. Additional data is held on each Manager, which includes the date that the Manager assumed his or her position at the current branch office, and a monthly…arrow_forward
- Database explanationarrow_forwardAfter sending your resume to an employer, he/she asked you for references. For each and every reference, you have to give the following information: Select one : a. name, place of work, and phone number b name , phone number , and email c , name , address , and email d . name , phone number , and addressarrow_forwardEntities required: Team, Player, Coach, and Parent. Attributes required: Team: Team ID number, Team name, and Team colors. Player: Player ID number, Player first name, Player last name, and Player age. Coach: Coach ID number, Coach first name, Coach last name, and Coach home phone number. Parent: Parent ID number, Parent last name, Parent first name, Home phone number, and Home Address (Street, City, State, and ZIP Code). The following relationships must be defined: • Team is related to Player. • Team is related to Coach. • Player is related to Parent. Connectivities and participations are defined as follows: • A Team may or may not have a Player. • A Player must have a Team. A Team may have many Players. A Player has only one Team. A Team may or may not have a Coach. A Coach must have a Team. A Team may have many Coaches. A Coach has only one Team. A Player must have a Parent. A Parent must have a Player. A Player may have many Parents. A Parent may have many Players. 3) Create an ERD…arrow_forward
- Np Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:CengageA Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr