Concept explainers
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.
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 |
Table Name: REGION
REGION_CODE | REGION_DESCRIPT |
1 | EAST |
2 | WEST |
Trending nowThis is a popular solution!
Chapter 3 Solutions
Database Systems: Design, Implementation, & Management
- Delete the OWNER_INDEX 3 index from the OWNER table.arrow_forwardAdd two columns to the EMPLOYEES table. One column, named EmpDate, contains the date of employment for each employee, and its default value should be the system date. The second column, named EndDate, contains employees date of termination.arrow_forwardWhich of the following characters can be used in a table name? a. b. ( c. % d. !arrow_forward
- What is the maximum number of PRIMARY KEY constraints allowed for a table? a. 1 b. 2 c. 30 d. 255arrow_forwardCUSTOMER Customer table is composed of customer number, name and phone number. Give and fill-in the appropriate attribute name for each of the column. custID 123 124 125 126 reservelD 5001 5002 5003 5004 5005 5006 5007 RESERVATION Each reservation is for one taxi. Reservation table is composed of reservation identification number, start reservation date, end reservation date, reservation days requested by customers starting from reservation date until end of reservation date, customer number that make the reservation and taxi number assigned to the reservation. Give and fill-in the appropriate attribute name for each of the column. taxill custName Ahmad Bin Abdullah Fatimah Binti Adam LAI LA2 Ruqayya Binti Idris Sulaiman Bin Daud LA3 LA4 startDate 01/10/2019 05/10/2019 05/10/2019 15/10/2019 20/10/2019 27/10/2019 02/11/2019 taxiType endDate 03/10/2019 12/10/2019 08/10/2019 17/10/2019 25/10/2019 Sedan Sedan Van Van 30/10/2019 04/11/2019 cust Phoneno TAXI Taxi table is composed of taxi…arrow_forwardTable name : tblFilm Attributes: FilmReleaseDate, FilmRunTimeMinutes (For the conditions) Write a query to increase the budget of all films released between 1933 and 1970 and whose FilmRunTimeMinutes is more than the average FilmRunTimeMinutes by 50%.arrow_forward
- List the names of all Staff members who hold a faculty position and have a salary of $45,000 or less. Show their names as their first name, followed by a space, and then their last name but sort the list alphabetically by last name and then first name. Hint: Check out the Position column in the Staff table. All staff members who do not hold a faculty position will get a bonus this year equal to 5% of their salary. List the names, current salary, and bonus amount for each Staff member (non-faculty) who is due a bonus. Show your list in order with the person receiving the highest bonus first.arrow_forwardList unique first (first_name) and last names (last_name) of customers (CUSTOMER table) who rented a movie (RENTAL table) between '2011-06-01' and '2012-01-01’ (rental_date). Sort by last_name. The common key is CUSTOMER_ID.arrow_forwardConsidering Vaccination Database, write queries to: Display the Subject CNIC, Name, Contact, Dose1 Center, and Dose2 Center as shown below: CNIC Name Contact Dose1 Center Dose2 Center 22401-6645321-1 Nasir 3409991112 Lachi Kohat 2 14301-6045321-5 Shahab 3409991112 kohat 3 Dara The subjects who have been vaccinated different types in Dose1 and Dose2. For instance, subjects who were vaccinated ‘Sinovac’ in the first dose, while Pfizer in the second dose.How many Viles of each type have been consumed so far. How many subjects are vaccinated from expired viles. Suggest the name of Incharge for the best performance award based on the highest number of subjects vaccinated on a single dayarrow_forward
- Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningA Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
- Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning